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
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
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.
(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.
No problem. I assumed that those might be related.
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?
(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.
That didn't really help, but oh well.
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.
Robert, perhaps you can post one or two screenshots? That would help a lot. Thanks.
Created attachment 138847 [details] Shows difference between editable and uneditable data in queries.
@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.
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) ?
Changed title to better reflect situation
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).
"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).
(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?
Ah, my bad. Two files same name here. Now, using the real sample ODB nope datediff still requires sql direct set true.
The same happens with DATEADD, by the way.
It would be great to have this working.
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"
Dear Robert Großkopf, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
Bug is still there in LO 7.1.2.1 on OpenSUSE 15.2 64bit rpm Linux. GUI doesn't know anything about the only allowed parameters for Firebird together with DATEDIFF
Confirmed in : Version: 7.4.2.3 / LibreOffice Community Build ID: 382eef1f22670f7f4118c8c2dd222ec7ad009daf CPU threads: 4; OS: Linux 5.15; UI render: default; VCL: gtk3 Locale: fr-FR (fr_FR.UTF-8); UI: fr-FR Calc: threaded I tried : select "field1","field2", datediff(year,"field3",current_date) from "Table1" is good in Menu/Tools/SQL... SQL direct is wrong in query module return : syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE at /home/buildslave/source/libo-core/dbaccess/source/core/api/SingleSelectQueryComposer.cxx:108 It is incredible that this bug has not been fixed since 2018!
(In reply to TISSENDIER Pierre from comment #23) > Confirmed in : > Version: 7.4.2.3 / LibreOffice Community > Build ID: 382eef1f22670f7f4118c8c2dd222ec7ad009daf > CPU threads: 4; OS: Linux 5.15; UI render: default; VCL: gtk3 > Locale: fr-FR (fr_FR.UTF-8); UI: fr-FR > Calc: threaded > > I tried : > select "field1","field2", datediff(year,"field3",current_date) from "Table1" > > is good in Menu/Tools/SQL... SQL direct > > is wrong in query module return : > > syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE at > /home/buildslave/source/libo-core/dbaccess/source/core/api/ > SingleSelectQueryComposer.cxx:108 > > It is incredible that this bug has not been fixed since 2018! SORRY ! It's good in the GUI, but you have to activate the icon "execute the SQL instruction directly". ;0)
For those who need a workaround for this: Create a query like SELECT "ID", DATEDIFF(DAY, "StartDate", "EndDate") AS "Date_Diff" FROM "Table" Save this query and create a view of this query ("viw_Datediff"). Now you could start the query where you could edit content with a correlating subquery: SELECT "ID", "StartDate", "EndDate", ( SELECT "Date_Diff" FROM "viw_Datediff" WHERE "ID" = "a"."ID" ) AS "Datediff" FROM "Table" AS "a"
jucasaca committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/42364fbfafaa95773c073cc080142b64ec1786fb tdf#104918 Add Firebird's DATEDIFF syntax to the sql parser It will be available in 24.2.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.
jucasaca committed a patch related to this issue. It has been pushed to "libreoffice-7-6": https://git.libreoffice.org/core/commit/462f2849d3279164dcec5416e55313f86de663fc tdf#104918 Add Firebird's DATEDIFF syntax to the sql parser It will be available in 7.6.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.
Tested with Version: 24.2.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: a2b6be8af9be3237efc3ed1244302cf899680e97 CPU threads: 6; OS: Linux 5.14; UI render: default; VCL: kf5 (cairo+xcb) Locale: de-DE (de_DE.UTF-8); UI: en-US Calc: threaded Works now as expected. Thanks!