Bug 146757 - Embedded Firebird ignores parameterized query input
Summary: Embedded Firebird ignores parameterized query input
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
7.3.0.1 rc
Hardware: All All
: high major
Assignee: Eike Rathke
URL:
Whiteboard: target:7.4.0 target:7.3.1 target:7.3.0
Keywords: bibisected, bisected, regression
Depends on:
Blocks:
 
Reported: 2022-01-14 09:38 UTC by Alex Thurgood
Modified: 2022-01-17 19:35 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
Embedded Firebird test file (3.71 KB, application/vnd.oasis.opendocument.database)
2022-01-14 09:39 UTC, Alex Thurgood
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Alex Thurgood 2022-01-14 09:38:11 UTC
Description:
A simple embedded Firebird database file containing a table with time defined fields is attached to this report.

Open the ODB file in LO Base.
Open the query "DepartureLaterThan"
Enter a time in the "dep" parameter field, e.g. 08:00
Press OK

The resulting grid view should display all entries with a departure time later than 08:00 hours.

This parameterized query works correctly on 
Version: 7.2.5.2 / LibreOffice Community
Build ID: 499f9727c189e6ef3471021d6132d4c694f357e5
CPU threads: 8; OS: Mac OS X 12.1; UI render: default; VCL: osx
Locale: fr-FR (fr_FR.UTF-8); UI: fr-FR
Calc: threaded

However, when testing with 
Version: 7.3.0.1 / LibreOffice Community
Build ID: 840fe2f57ae5ad80d62bfa6e25550cb10ddabd1d
CPU threads: 8; OS: Mac OS X 12.1; UI render: Skia/Metal; VCL: osx
Locale: fr-FR (fr_FR.UTF-8); UI: fr-FR
Calc: threaded

The query fails completely, and instead displays all of the results in the table on which the query operates. 

The second query in the test ODB file contains no parameter, but rather a direct value in the SQL against which to compare - this query executes correctly and displays the correct resultset.

Steps to Reproduce:
See description above

Actual Results:
The parameter is ignored when executing the query on macOS Arm version.

Expected Results:
The parameter should execute/be parsed normally and return a resultset in accordance with the parameter provided.


Reproducible: Always


User Profile Reset: Yes



Additional Info:
Version: 7.3.0.1 / LibreOffice Community
Build ID: 840fe2f57ae5ad80d62bfa6e25550cb10ddabd1d
CPU threads: 8; OS: Mac OS X 12.1; UI render: Skia/Metal; VCL: osx
Locale: fr-FR (fr_FR.UTF-8); UI: fr-FR
Calc: threaded
Comment 1 Alex Thurgood 2022-01-14 09:39:03 UTC
Created attachment 177541 [details]
Embedded Firebird test file
Comment 2 Alex Thurgood 2022-01-14 09:46:23 UTC
Also works correctly in 

Collabora Office
Version: 21.06.9.1
Build ID: 0579e4dd91e7fd2e0574e3919b921e930642f8dd
CPU threads: 8; OS: Mac OS X 12.1; UI render: default; VCL: osx
Locale: fr-FR (fr_FR.UTF-8); UI: fr-FR
Calc: threaded

and

LibreOffice Vanilla
Version: 7.2.4.1 / LibreOffice Community
Build ID: 27d75539669ac387bb498e35313b970b7fe9c4f9
CPU threads: 8; OS: Mac OS X 12.1; UI render: default; VCL: osx
Locale: fr-FR (fr_FR.UTF-8); UI: fr-FR
Calc: threaded
Comment 3 Julien Nabet 2022-01-14 21:25:13 UTC
On pc Debian x86-64 with master sources updated today, I could reproduce this.
Comment 4 Julien Nabet 2022-01-14 23:09:59 UTC
I could reproduce this on an hsqldb odb file.
However, not sure my test was ok since typing this in Execute SQL statement
SELECT * FROM "Timetable" WHERE "departure" > '08:00'
failed with:
Wrong data type: java.lang.IllegalArgumentException /home/julien/lo/libreoffice/connectivity/source/drivers/jdbc/Object.cxx:172

The goal was to find if it was specific to Firebird.
Comment 5 raal 2022-01-15 14:40:31 UTC
This seems to have begun at the below commit.
Adding Cc: to Eike Rathke ; Could you possibly take a look at this one?
Thanks
 c8ece95d00e558f56c1d970745337bbf9254d2fc is the first bad commit
commit c8ece95d00e558f56c1d970745337bbf9254d2fc
Author: Jenkins Build User <tdf@pollux.tdf>
Date:   Thu Sep 23 20:33:51 2021 +0200

    source sha:f7d0c6b42a0a5ec98876006cd5048589219be741

https://git.libreoffice.org/core/+/f7d0c6b42a0a5ec98876006cd5048589219be741
    Sort ZF_STANDARD_NEWEXTENDED_DATE_... into ZF_STANDARD_DATE category
Comment 6 Julien Nabet 2022-01-15 19:23:26 UTC
(In reply to raal from comment #5)
> This seems to have begun at the below commit.
> ...
> https://git.libreoffice.org/core/+/f7d0c6b42a0a5ec98876006cd5048589219be741
>     Sort ZF_STANDARD_NEWEXTENDED_DATE_... into ZF_STANDARD_DATE category

I confirm, I don't reproduce the pb when reverting this commit locally.

Let's increase a bit importance since:
- it's a regression
- it impacts all env

It's possible not only Firebird (which is still experimental).
Comment 7 Eike Rathke 2022-01-17 11:37:57 UTC
If that commit affects time queries in Base then Base somewhere makes serious wrong assumptions about number formatter internal constants.
Comment 9 Eike Rathke 2022-01-17 13:48:59 UTC
That for this case 0.33333... (the 08:00 converted to double) results in being converted to date string "30 December 1899" which when converted to numeric is 0, hence all records match departure>0.
Comment 10 Julien Nabet 2022-01-17 17:28:25 UTC
(In reply to Eike Rathke from comment #9)
> That for this case 0.33333... (the 08:00 converted to double) results in
> being converted to date string "30 December 1899" which when converted to
> numeric is 0, hence all records match departure>0.

Thank you for the analysis and for the patch, just taking a look at git history, it was like this since initial import in 2000 (!!)
Comment 11 Commit Notification 2022-01-17 17:55:45 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/0d6ab8d4ea7226db53d07dc158e176fe962fd8d9

Resolves: tdf#146757 Use correct format index keys instead of wrong hack

It will be available in 7.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 12 Eike Rathke 2022-01-17 17:59:51 UTC
(In reply to Julien Nabet from comment #10)
> taking a look at git
> history, it was like this since initial import in 2000 (!!)
Yes, it was mere luck that the internal index keys had to be stable for the StarOffice binary file formats, a requirement that is gone for quite some years now..
Comment 13 Eike Rathke 2022-01-17 18:00:57 UTC
Pending review
https://gerrit.libreoffice.org/c/core/+/128527 for 7-3
https://gerrit.libreoffice.org/c/core/+/128528 for 7-3-0
Comment 14 Julien Nabet 2022-01-17 18:15:03 UTC
After having updated my local repo (included Eike's patch) I confirm I don't reproduce the bug anymore.
Thank you again Eike! :-)
Comment 15 Commit Notification 2022-01-17 19:35:17 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-7-3":

https://git.libreoffice.org/core/commit/2492429199670e30d89a81e0e227ae894764baee

Resolves: tdf#146757 Use correct format index keys instead of wrong hack

It will be available in 7.3.1.

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

Affected users are encouraged to test the fix and report feedback.
Comment 16 Commit Notification 2022-01-17 19:35:29 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-7-3-0":

https://git.libreoffice.org/core/commit/691f47959df0260ccee3a54e8d43df33e8aae9b5

Resolves: tdf#146757 Use correct format index keys instead of wrong hack

It will be available in 7.3.0.

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

Affected users are encouraged to test the fix and report feedback.