[Libreoffice-bugs] [Bug 91324] New: Embedded Firebird - Current_Date gives wrong date back

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=91324

Bug ID: 91324
Summary: Embedded Firebird - Current_Date gives wrong date back
Product: LibreOffice
Version: 5.0.0.0.alpha0+ Master
Hardware: Other
OS: Windows (All)
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Base
Assignee: libreoffice-***@lists.freedesktop.org
Reporter: ***@freenet.de

Created attachment 115657
--> https://bugs.documentfoundation.org/attachment.cgi?id=115657&action=edit
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

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=91324

--- Comment #1 from Ulf <***@freenet.de> ---
Created attachment 115658
--> https://bugs.documentfoundation.org/attachment.cgi?id=115658&action=edit
ample db

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=91324

***@familiegrosskopf.de changed:

What |Removed |Added
----------------------------------------------------------------------------
Status|UNCONFIRMED |NEW
CC| |***@familiegrosskopf.de
Ever confirmed|0 |1

--- Comment #2 from ***@familiegrosskopf.de ---
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.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=91324

***@familiegrosskopf.de changed:

What |Removed |Added
----------------------------------------------------------------------------
Version|5.0.0.0.alpha0+ Master |4.2.0.1 rc
OS|Windows (All) |All

--- Comment #3 from ***@familiegrosskopf.de ---
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.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=91324

--- Comment #4 from Terrence Enger <***@iseries-guru.com> ---
Created attachment 122243
--> https://bugs.documentfoundation.org/attachment.cgi?id=122243&action=edit
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.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=91324

Julien Nabet <***@yahoo.fr> changed:

What |Removed |Added
----------------------------------------------------------------------------
CC| |***@ahunt.org,
| |***@yahoo.fr

--- Comment #5 from Julien Nabet <***@yahoo.fr> ---
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.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=91324

--- Comment #6 from Gerhard Schaber <***@gmx.at> ---
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.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=91324

--- Comment #7 from Gerhard Schaber <***@gmx.at> ---
By the way, I tested with 5.1.4 (32bit) on Windows 8.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=91324

--- Comment #8 from Gerhard Schaber <***@gmx.at> ---
It is like it ignores the first 2 digits of the year completely.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=91324

Lionel Elie Mamane <***@mamane.lu> changed:

What |Removed |Added
----------------------------------------------------------------------------
Blocks| |51780


Referenced Bugs:

https://bugs.documentfoundation.org/show_bug.cgi?id=51780
[Bug 51780] default to Firebird not HSQLDB in Base

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=91324

--- Comment #9 from Tamas Bunth <***@gmail.com> ---
(In reply to Ulf from comment #0)

Post by b***@bugs.documentfoundation.org
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)

Post by b***@bugs.documentfoundation.org
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").

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=91324

Post by b***@bugs.documentfoundation.org
(In reply to Terrence Enger from comment #4)

Post by b***@bugs.documentfoundation.org
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.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=91324

Commit Notification <libreoffice-***@lists.freedesktop.org> changed:

What |Removed |Added
----------------------------------------------------------------------------
Whiteboard| |target:5.3.0

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=91324

--- Comment #11 from Commit Notification <libreoffice-***@lists.freedesktop.org> ---
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.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=91324

Commit Notification <libreoffice-***@lists.freedesktop.org> changed:

What |Removed |Added
----------------------------------------------------------------------------
Whiteboard|target:5.3.0 |target:5.3.0 target:5.2.1

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=91324

--- Comment #12 from Commit Notification <libreoffice-***@lists.freedesktop.org> ---
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.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=91324

Commit Notification <libreoffice-***@lists.freedesktop.org> changed:

What |Removed |Added
----------------------------------------------------------------------------
Whiteboard|target:5.3.0 target:5.2.1 |target:5.3.0 target:5.2.1
| |target:5.1.6

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=91324

--- Comment #13 from Commit Notification <libreoffice-***@lists.freedesktop.org> ---
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.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=91324

--- Comment #14 from ***@familiegrosskopf.de ---
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-***@70-TDF, Branch:master, Time:
2016-07-15_23:35:01
Locale: de-DE (de_DE.UTF-8); Calc: group

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=91324

--- Comment #15 from Terrence Enger <***@iseries-guru.com> ---
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

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=91324

--- Comment #16 from ***@familiegrosskopf.de ---
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

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=91324

--- Comment #17 from Commit Notification <libreoffice-***@lists.freedesktop.org> ---
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.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=91324

--- Comment #18 from Commit Notification <libreoffice-***@lists.freedesktop.org> ---
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.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=91324

***@familiegrosskopf.de changed:

What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |RESOLVED
Resolution|--- |FIXED

--- Comment #19 from ***@familiegrosskopf.de ---
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-***@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.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=91324

--- Comment #20 from Lionel Elie Mamane <***@mamane.lu> ---
*** Bug 91353 has been marked as a duplicate of this bug. ***