Bug Hunting Session
Bug 104918 - LibreOffice SQL parser chokes on Firebird's DATEDIFF syntax, making use impossible in normal (non-direct SQL) queries
Summary: LibreOffice SQL parser chokes on Firebird's DATEDIFF syntax, making use impos...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
5.3.0.3 release
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Firebird-Default
  Show dependency treegraph
 
Reported: 2016-12-25 09:13 UTC by Robert Großkopf
Modified: 2019-03-28 08:14 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Open the query "Datediff". Changing of data impossible. Works only with direct SQL. (4.58 KB, application/vnd.oasis.opendocument.database)
2016-12-25 09:13 UTC, Robert Großkopf
Details
Shows difference between editable and uneditable data in queries. (49.50 KB, application/pdf)
2018-01-03 12:33 UTC, Robert Großkopf
Details
Sample embedded hsqldb for comparison (4.08 KB, application/vnd.oasis.opendocument.database)
2018-01-04 12:53 UTC, Alex Thurgood
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Robert Großkopf 2016-12-25 09:13:12 UTC
Created attachment 129934 [details]
Open the query "Datediff". Changing of data impossible. Works only with direct SQL.

Open the attached Firebird-database.
Start the query "Datediff".
It shows a datediff between two dates. There couldn't be changed data in this query, because datediff runs only with direct SQL.

With internal HSQLDB datediff runs in the GUI. You could change data, add new rows ...

Tested with
Version: 5.4.0.0.alpha0+
Build ID: 2a4cd80abcf9e515d1ce3b3a944b573bdc42bff2
CPU Threads: 4; OS Version: Linux 4.1; UI Render: default; VCL: kde4; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2016-12-22_00:18:04
Locale: de-DE (de_DE.UTF-8); Calc: group
Comment 1 m.a.riosv 2016-12-25 22:06:31 UTC
Reproducible.
Version: 5.4.0.0.alpha0+
Build ID: 7a1add76d542e9929c1feab9e06949990e236616
CPU Threads: 4; OS Version: Windows 6.19; UI Render: default; 
TinderBox: Win-x86@39, Branch:master, Time: 2016-12-22_23:56:14
Locale: es-ES (es_ES); Calc: CL
Comment 2 Gerhard Schaber 2018-01-02 15:04:22 UTC
The same with Version: 6.1.0.0.alpha0+, Build ID: 864ab0502a4d3506413451e8c545144c6c15d777.

"SQL Status: HY000
Error code: 1000

syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE
"

When I run something like this:
SELECT DATEDIFF (DAY FROM DATE '2017-03-30' to DATE '2018-03-31') FROM RDB$DATABASE
or this:
SELECT DATEDIFF (DAY, CAST('2018-03-30' AS DATE), CAST('2018-03-31' AS DATE)) FROM RDB$DATABASE
in Tools > SQL..., LO freezes.
Comment 3 Robert Großkopf 2018-01-02 17:57:08 UTC
(In reply to Gerhard Schaber from comment #2)
> 
> When I run something like this:
> SELECT DATEDIFF (DAY FROM DATE '2017-03-30' to DATE '2018-03-31') FROM
> RDB$DATABASE
> or this:
> SELECT DATEDIFF (DAY, CAST('2018-03-30' AS DATE), CAST('2018-03-31' AS
> DATE)) FROM RDB$DATABASE
> in Tools > SQL..., LO freezes.

Please don't add new bugs to this bug. Tools > SQL seems to have another bug here. Feel free to report it separately.

Have tested this once more:
First Version with Firebird 3 doesn't support "Datediff" in GUI, works only through direct SQL in a query.
Tested with LO 5.3.0.3, OpenSUSE 64bit rpm Linux.
Comment 4 Gerhard Schaber 2018-01-02 18:00:02 UTC
No problem. I assumed that those might be related.
Comment 5 Tor Lillqvist 2018-01-02 22:27:34 UTC
Sorry, but could I have some more detailed reproduction instructions for what the initial comment describes please? What does "There couldn't be changed data in this query" mean? What should I try to change, and what is the expected error?
Comment 6 Robert Großkopf 2018-01-03 08:44:25 UTC
(In reply to Tor Lillqvist from comment #5)
> Sorry, but could I have some more detailed reproduction instructions for
> what the initial comment describes please? What does "There couldn't be
> changed data in this query" mean? What should I try to change, and what is
> the expected error?

If you start this query with HSQLDB and don't switch to direct SQL in the query-editor you could delete, update and insert data in the query:
SELECT "Table3".*, DATEDIFF(DAY, "StartDate", "EndDate") FROM "Table3"

You couldn't start this query with Firebird without switching to direct SQL, because the GUI doesn't produce a right SQL-code of DATEDIFF(DAY, "StartDate", "EndDate"). So you couldn't delete, update or insert data.

SELECT "Table3".* FROM "Table3"
will work without switching to direct SQL with Firebird. You could update, insert or delete data.
Comment 7 Tor Lillqvist 2018-01-03 09:00:31 UTC
That didn't really help, but oh well.
Comment 8 Tor Lillqvist 2018-01-03 09:05:03 UTC
For instance, you say "If you start this query with HSQLDB". I don't understand what that means. The attached .odb document has a Firebird database, not HSQLDB. Please elaborate.
Comment 9 Noel Grandin 2018-01-03 09:06:08 UTC
Robert, perhaps you can post one or two screenshots? That would help a lot. Thanks.
Comment 10 Robert Großkopf 2018-01-03 12:33:56 UTC
Created attachment 138847 [details]
Shows difference between editable and uneditable data in queries.
Comment 11 Alex Thurgood 2018-01-04 12:34:19 UTC
@Tor : what Robert means is that when you launch the query so that the results are displayed in the GUI, the result set should be modifiable via UI interaction. In other words, it should be possible to click in one of the fields of the UI display of the query window and change the data.

I can confirming that this is currently not possible with

Version: 6.1.0.0.alpha0+
Build ID: 5b7ec49f453c3732e2482b26bce4243eb1a10f40
CPU threads: 4; OS: Mac OS X 10.13.2; UI render: default; 
Locale: fr-FR (fr_FR.UTF-8); Calc: group threaded

and the sample database provided.

A corresponding hsqldb-embedded ODB file (not provided) allows the user to change the data returned in the query window directly via the GUI.
Comment 12 Alex Thurgood 2018-01-04 12:48:49 UTC
Confirming on

Version: 6.1.0.0.alpha0+
Build ID: 5b7ec49f453c3732e2482b26bce4243eb1a10f40
CPU threads: 4; OS: Mac OS X 10.13.2; UI render: default; 
Locale: fr-FR (fr_FR.UTF-8); Calc: group threaded


although I am unsure why a user should be allowed to change the result of a function calculation, be it directly via SQL or the UI, unless Robert you mean that the other field data should be editable (in this instance the ID field) ?
Comment 13 Alex Thurgood 2018-01-04 12:50:01 UTC
Changed title to better reflect situation
Comment 14 Alex Thurgood 2018-01-04 12:53:51 UTC
Created attachment 138873 [details]
Sample embedded hsqldb for comparison

In the sample embedded hsqldb provided for comparison, if you launch the query by double-clicking it, you can see that the resultset is editable directly via the UI (with direct SQL parsing turned off).
Comment 15 Lionel Elie Mamane 2018-01-04 13:03:42 UTC
"direct SQL" queries are *never* editable. The discussion in this bug got sidetracked into this consequence.

The problem, described concisely, is that the LibreOffice SQL parser doesn't accept the syntax that Firebird requires for the DATEDIFF function, that is the first 'argument' is the DAY token instead of a value/expression (in HSQLDB, it is a *string* and thus a valid value/expression).
Comment 16 Drew Jensen 2018-04-19 22:24:30 UTC
(In reply to robert from comment #0)
> Created attachment 129934 [details]
> Open the query "Datediff". Changing of data impossible. Works only with
> direct SQL.
> 
> Open the attached Firebird-database.
> Start the query "Datediff".
> It shows a datediff between two dates. There couldn't be changed data in
> this query, because datediff runs only with direct SQL.
> 
> With internal HSQLDB datediff runs in the GUI. You could change data, add
> new rows ...
> 

Using Libo6.1Alpha0 latest build (fc5d185bdb0f27bc6014d47338740f741ac05d21)

Downloaded the original bug report attachment (ODB from 2016-12-25) This (datediff in querys) is working now as expected and the result set is editable in the GUI.

So, close this as working?
Comment 17 Drew Jensen 2018-04-19 22:39:56 UTC
Ah, my bad. Two files same name here.

Now, using the real sample ODB nope datediff still requires sql direct set true.
Comment 18 Gerhard Schaber 2018-06-19 11:18:52 UTC
The same happens with DATEADD, by the way.
Comment 19 Gerhard Schaber 2019-03-28 08:10:29 UTC
It would be great to have this working.
Comment 20 Gerhard Schaber 2019-03-28 08:14:27 UTC
For now, this is a workaround, at least to get a result as number of days, if that helps anyone:
SELECT "ID", ("EndDate"-"StartDate") "DDIFF" FROM "Table3"