Bug 91324 - Embedded Firebird - Current_Date gives wrong date back
Summary: Embedded Firebird - Current_Date gives wrong date back
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.2.0.1 rc
Hardware: Other All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:5.3.0 target:5.2.1 target:5.1.6
Keywords:
: 91353 (view as bug list)
Depends on:
Blocks: Database-Firebird
  Show dependency treegraph
 
Reported: 2015-05-16 15:43 UTC by Ulf
Modified: 2016-09-07 17:43 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
screen shot (60.25 KB, image/png)
2015-05-16 15:43 UTC, Ulf
Details
ample db (43.27 KB, application/vnd.oasis.opendocument.database)
2015-05-16 15:44 UTC, Ulf
Details
Firebird isql returns correct values (994 bytes, text/plain)
2016-01-28 01:50 UTC, Terrence Enger
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Ulf 2015-05-16 15:43:07 UTC
Created attachment 115657 [details]
screen shot

I tried these functions in the query-designer with the embedded Firebird Database.

CURRENT_DATE
CURRENT_TIMESTAMP
DATE 'Now'

Instead to get today, the date is on day in the future but one month in the past. Please take a look at the attached screen shot or sample DB

please open sample DB
-> run query  current_date_wrong

I use Win 8.1 and attached the Base file

The same behavior is in 4.4.3.1
Comment 1 Ulf 2015-05-16 15:44:32 UTC
Created attachment 115658 [details]
ample db
Comment 2 robert 2015-05-16 17:25:54 UTC
I could confirm the date CURRENT_DATE would show in Base-queries is totally wrong. Today it's 2015-05-16, but embedded Firebird shows 0115-04-17. I changed this to numbers: correct is 42140, but with embedded Firebird it shows -651851

Another buggy behavior appears, when I start the query in Tools-SQL: There I got 0115-04-16 at the same time when the query gets 0115-04-17.
Comment 3 robert 2015-05-16 17:30:41 UTC
It is the same buggy behavior in all versions, which support internal Firebird as experimental feature. I have tested with 4.2.0.1 on OpenSUSE 13.2. CurrentDate is totally wrong. So I set version to 4.2.0.1 as earliest version with this bug.
Comment 4 Terrence Enger 2016-01-28 01:50:52 UTC
Created attachment 122243 [details]
Firebird isql returns correct values

Just a couple more observations ...

(*) Embedded HSQLDB seems not to have this affliction.

(*) Firebird isql returns correct values for current_date,
    current_time, and current_timestamp.  I conclude that the problem
    is in LibreOffice.
Comment 5 Julien Nabet 2016-02-20 08:26:18 UTC
I wonder if it could be due to the use of isc_vax_integer (which is btw deprecated and should be replaced by isc_portable_integer (see http://docwiki.embarcadero.com/InterBase/XE7/en/Isc_vax_integer%28%29).
Indeed, this kind of symptom makes me think to some wrong byte manipulations.
See http://opengrok.libreoffice.org/search?q=isc_vax_integer&project=core&defs=&refs=&path=&hist=
Of course, it's completely a guess.

Andrzej: thought you might have some ideas since you did a great work on Firebird implementation in LO.
Comment 6 Gerhard Schaber 2016-06-28 12:05:07 UTC
I can confirm this. And Extract ( Year FROM x ) also does not work, if this is relevant for this (it seems so). If x is 2015, it returns 3915.
Comment 7 Gerhard Schaber 2016-06-28 12:05:56 UTC
By the way, I tested with 5.1.4 (32bit) on Windows 8.
Comment 8 Gerhard Schaber 2016-06-28 12:15:24 UTC
It is like it ignores the first 2 digits of the year completely.
Comment 9 Tamas Bunth 2016-07-10 20:39:10 UTC
(In reply to Ulf from comment #0)
> Instead to get today, the date is on day in the future but one month in the
> past. Please take a look at the attached screen shot or sample DB

- The bad value of month is because the structure "tm" counts month (tm_month) from 0 to 11 ( see http://www.cplusplus.com/reference/ctime/tm/ ), but we expect it to be from 1 to 12 I suppose.
So a +1 in ResultSet.cxx:retrieveValue will solve it.

- I couldn't figure out yet where the bonus day come from.

(In reply to Terrence Enger from comment #4)
> Embedded HSQLDB seems not to have this affliction.

How can I test it using embedded HSQLDB? I tried
"select current_timestamp from tablename", where tablename is a random table. It gave me no output (I tried with tools->sql and "Create Query in SQL View").
Comment 10 Tamas Bunth 2016-07-10 20:43:38 UTC
> (In reply to Terrence Enger from comment #4)
> > Embedded HSQLDB seems not to have this affliction.
> 
> How can I test it using embedded HSQLDB? I tried
> "select current_timestamp from tablename", where tablename is a random
> table. It gave me no output (I tried with tools->sql and "Create Query in
> SQL View").

Oh, That was because my table was empty. Indeed, the problem does not occur with HSQLDB.
Comment 11 Commit Notification 2016-07-15 11:16:37 UTC
Wastack committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=88346f606a16865bcf7492f67a7207d0078787a1

tdf#91324 GSoC adapt struct tm correctly

It will be available in 5.3.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 12 Commit Notification 2016-07-15 18:26:17 UTC
Wastack committed a patch related to this issue.
It has been pushed to "libreoffice-5-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=dc0a20cc9b7781b821778d69d2be75e60621e5bc&h=libreoffice-5-2

tdf#91324 GSoC adapt struct tm correctly

It will be available in 5.2.1.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 13 Commit Notification 2016-07-15 18:27:40 UTC
Wastack committed a patch related to this issue.
It has been pushed to "libreoffice-5-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=f9393f84d539814574b594e173367335fe0c42e8&h=libreoffice-5-1

tdf#91324 GSoC adapt struct tm correctly

It will be available in 5.1.6.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 14 robert 2016-07-17 09:06:04 UTC
Couldn't test this because of https://bugs.documentfoundation.org/show_bug.cgi?id=100964

No tables, queries, forms and reports will appear in the daily build 
Version: 5.3.0.0.alpha0+
Build ID: a2c557d80ac68c06ea59586245a7431e061938f0
CPU Threads: 4; OS Version: Linux 4.1; UI Render: default; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2016-07-15_23:35:01
Locale: de-DE (de_DE.UTF-8); Calc: group
Comment 15 Terrence Enger 2016-07-27 15:40:05 UTC
I am sorry to report that the problem persists in daily Linux dbgutil
bibisect repository version 2016-07-22, source-hash f045d14.

On debian-stretch, at local time as reported for CURRENT_TIMESTAMP, I
am executing query ...

    SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP FROM "table_dee"

and seeing result ...

    CURRENT_DATE  CURRENT_TIME  CURRENT_TIMESTAMP
    ------------  ------------  -----------------
    0116-06-28    11:26:50      2016-07-27 11:26
Comment 16 robert 2016-07-28 09:56:31 UTC
Running query directly from Tools > SQL shows

CURRENT_DATE    CURRENT_TIMESTAMP
0116-06-28      2016-07-28 11:44:44.000000000

So the date shows the totally wrong year and month 1 month ago. Timestamp is right.

Running the same query from the query-editor of the GUI, also direct SQL, shows also a wrong day for the current date:

CURRENT_DATE    CURRENT_TIMESTAMP
0116-06-29      2016-07-28 11:44:44.000000000
Comment 17 Commit Notification 2016-08-11 12:49:37 UTC
Wastack committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=70a295ea33c8a8f3f3643733388f9a37dab9f967

tdf#91324 GSoC adapt struct tm correctly for date

It will be available in 5.3.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 18 Commit Notification 2016-08-17 13:17:37 UTC
Wastack committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=3828e10f7c3918f89312228739b428dc5046b52e

tdf#91324 correct firebird date format

It will be available in 5.3.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 19 robert 2016-08-23 18:04:36 UTC
Could confirm CURRENT_DATE gives right value now with the new internal Firebird 3.0 database of 
Version: 5.3.0.0.alpha0+
Build ID: 5553041f4d9b5d676cb82bc1953f7334b201d9da
CPU Threads: 4; OS Version: Linux 4.1; UI Render: default; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2016-08-23_13:10:42

Tested it in GUI and with direct SQL.

I will set this as RESOLVED and FIXED.
Comment 20 Lionel Elie Mamane 2016-09-07 17:43:43 UTC
*** Bug 91353 has been marked as a duplicate of this bug. ***