WEEK, WEEKDAY and YEARDAY do not work when entered in the Query Design dialog. Entering the following using the Query Design dialog: EXTRACT( WEEK FROM "DateVisit" ) Returns the following error: Expression, field name 'EXTRACT( WEEK FROM "DateVisit" )' does not exist However, the following statement works (for WEEK, WEEKDAY and YEARDAY) when entered in Tools -> SQL SELECT EXTRACT( WEEK FROM "DateVisit" ) "WeekNo" FROM "iCare" WHERE EXTRACT( WEEK FROM "DateVisit" ) = 1
Confirmed in today's build of 6.1 RC and 6.2Alpha0 The extract function will work without SQL Direct enabled but is not recognized in the designer UI. For example: 1 Download this example file https://nextcloud.documentfoundation.org/s/ePAmqMj3N2Ywms4 2 Create a new query with the SQL view and enter the following Select statement. SELECT "Country", COALESCE("FirstName", ' ', "LastName") As "Name", EXTRACT(YEAR FROM "OrderDate") As "Year", COUNT(*) As "Count" FROM "Employees" INNER JOIN "Orders" ON ("Employees"."EmployeeID"="Orders"."EmployeeID") GROUP BY "Country", "Name", "Year" 3 Run the query (zero records because one of the tables is empty) 4 switch views to the designer UI 5 Unable to run the query or even switch back to SQL view.
Dup of tdf#104986?
Hi. It was reported as fixed in that build, but it isn't for fixed for me. I texted it again in RC2. Still not working. Interestingly, I can no longer create a FIREBIRD db in RC2.
Just to add, that you may be pointing out that I should've replied to that previous thread. Didn't find it - sorry1
(In reply to Vilos from comment #3) > Hi. It was reported as fixed in that build, but it isn't for fixed for me. I > texted it again in RC2. Still not working. Interestingly, I can no longer > create a FIREBIRD db in RC2. You need to enable the experimental features first. Tools - Options - Advanced - Enable experimental features.. I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' if the bug is still present in the latest version.
Created attachment 143720 [details] Querydesigner screen shot Simple case is working for me on Ubuntu 18.04 and build (QueryDesigner Screenshot) Version: 6.1.1.0.0+ Build ID: 2466ea26c4bef1e002a24f6845084633e5a058c4 CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk2; TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:libreoffice-6-1, Time: 2018-07-21_22:43:36 Locale: en-US (en_US.UTF-8); Calc: group threaded and 6.2 Master
Created attachment 143721 [details] Design View for WEEKDAY and YEARDAY error and LO Version Tools - Options - Advanced - Enable experimental features set. WEEK works. WEEKDAY and YEARDAY don't. See attached image. Just to re-iterate, the SQL equivalent statements work in Tools->SQL.
Sorry, twice in two days I couldn't see the tree for the forest or some such. MILLISECOND is also not recognized by the Base SQL parser.
I think the EXTRACT function needs to be added to the SQL grammar of LibreOffice
Dear Vilos, 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 the same in LO 7.1.5.1 on OpenSUSE 15.2 64bit rpm Linux. EXTRACT will work well with YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, because HSQLDB will work with this strings. EXTRACT won't work in GUI with WEEK, WEEKDAY, YEARDAY, MILLISECOND because only Firebird will work with this strings.
Dear Vilos, 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://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
Still the same buggy behavior in LO 7.5.5.2 on OpenSUSE 15.4 64bit rpm Linux.
(In reply to Lionel Elie Mamane from comment #9) > I think the EXTRACT function needs to be added to the SQL grammar of > LibreOffice it seems EXTRACT is known by SQL grammar, sqlbison.y:156:%token <pParseNode> SQL_TOKEN_DAYNAME SQL_TOKEN_DAYOFMONTH SQL_TOKEN_DAYOFWEEK SQL_TOKEN_DAYOFYEAR SQL_TOKEN_EXTRACT sqlbison.y:1774: SQL_TOKEN_EXTRACT '(' extract_field SQL_TOKEN_FROM value_exp ')' sqlbison.y:3877: | SQL_TOKEN_EXTRACT sqlflex.l:218:EXTRACT {SQL_NEW_KEYWORD(SQL_TOKEN_EXTRACT); } the rule which is concerned is: 2958 non_second_datetime_field: 2959 SQL_TOKEN_YEAR 2960 | SQL_TOKEN_MONTH 2961 | SQL_TOKEN_DAY 2962 | SQL_TOKEN_HOUR 2963 | SQL_TOKEN_MINUTE 2964 ; (see https://opengrok.libreoffice.org/xref/core/connectivity/source/parse/sqlbison.y?r=e469a171#2958) We could add SQL_TOKEN_WEEK, SQL_TOKEN_WEEKDAY, SQL_TOKEN_YEARDAY and SQL_TOKEN_MILLISECOND but it would be wrong for HSQLDB. I gave a try, just for "WEEK", with this patch: diff --git a/connectivity/source/parse/sqlbison.y b/connectivity/source/parse/sqlbison.y index 0c5d359d083e..f50d136782ab 100644 --- a/connectivity/source/parse/sqlbison.y +++ b/connectivity/source/parse/sqlbison.y @@ -2958,6 +2958,7 @@ interval_value_exp: non_second_datetime_field: SQL_TOKEN_YEAR | SQL_TOKEN_MONTH + | SQL_TOKEN_WEEK | SQL_TOKEN_DAY | SQL_TOKEN_HOUR | SQL_TOKEN_MINUTE in hsqldb, the query can be saved but when executing it, we got this: SQL Status: 37000 Error code: -11 Unexpected token: WEEK in statement [SELECT EXTRACT( WEEK FROM "test" ) AS "Year" FROM "Table2"] at /home/julien/lo/libreoffice/connectivity/source/drivers/jdbc/Object.cxx:172 as expected of course. With Firebird example from Drew, it works. So should we go ahead with this patch (+ adding the other missing Extract elements) or is there some other way?
(In reply to Julien Nabet from comment #14) > @@ -2958,6 +2958,7 @@ interval_value_exp: > non_second_datetime_field: > SQL_TOKEN_YEAR > | SQL_TOKEN_MONTH > + | SQL_TOKEN_WEEK > | SQL_TOKEN_DAY > | SQL_TOKEN_HOUR > | SQL_TOKEN_MINUTE > > > So should we go ahead with this patch (+ adding the other missing Extract > elements) or is there some other way? I would do it this way for all missing tokens: WEEK, WEEKDAY, YEARDAY, MILLISECOND
patch for master here: https://gerrit.libreoffice.org/c/core/+/154524
Julien Nabet committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/77a2fe5a649d089d7c09c5558c42c2ea38116fd7 tdf#118817: add WEEK, WEEKDAY, YEARDAY AND MILLISECOND for EXTRACT 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.
I'm getting this after this commit: /home/bogdan/Documente/LibreOffice7.6/core/workdir/YaccTarget/connectivity/source/parse/sqlbison.cxx: In static member function ‘static rtl::OString connectivity::OSQLParser::TokenIDToStr(sal_uInt32, const connectivity::IParseContext*)’: /home/bogdan/Documente/LibreOffice7.6/core/workdir/YaccTarget/connectivity/source/parse/sqlbison.cxx:1142:6: warning: comparison of unsigned expression in ‘>= 0’ is always true [-Wtype-limits] 1142 | (0 <= (YYX) && (YYX) <= YYMAXUTOK \ | ~~^~~~~~~~ /home/bogdan/Documente/LibreOffice7.6/core/workdir/YaccTarget/connectivity/source/parse/sqlbison.cxx:10802:32: note: in expansion of macro ‘YYTRANSLATE’ 10802 | aStr = yytname[YYTRANSLATE(nTokenID)]; | ^~~~~~~~~~~
(In reply to BogdanB from comment #18) > I'm getting this after this commit: > /home/bogdan/Documente/LibreOffice7.6/core/workdir/YaccTarget/connectivity/ > source/parse/sqlbison.cxx: In static member function ‘static rtl::OString > connectivity::OSQLParser::TokenIDToStr(sal_uInt32, const > connectivity::IParseContext*)’: > /home/bogdan/Documente/LibreOffice7.6/core/workdir/YaccTarget/connectivity/ > source/parse/sqlbison.cxx:1142:6: warning: comparison of unsigned expression > in ‘>= 0’ is always true [-Wtype-limits] > 1142 | (0 <= (YYX) && (YYX) <= YYMAXUTOK \ > | ~~^~~~~~~~ > /home/bogdan/Documente/LibreOffice7.6/core/workdir/YaccTarget/connectivity/ > source/parse/sqlbison.cxx:10802:32: note: in expansion of macro ‘YYTRANSLATE’ > 10802 | aStr = yytname[YYTRANSLATE(nTokenID)]; > | ^~~~~~~~~~~ At which moment, do you get this, when building locally or when testing the case with the build or with a daily build containing my patch (but would be quite astonished it's already released)? Also, noticing "LibreOffice7.6", do you confirm you're actually testing 24.02 (where my commit is now present)? Because concerning 7.6, the commit is still waiting for some review. Moreover, all TBs are green except the last one which is red but it was already the case before my patch. https://tinderbox.libreoffice.org/MASTER/status.html Also all Jenkins from master were OK during the build and the automatic tests, see https://gerrit.libreoffice.org/c/core/+/154524 idem for 7.6 one, see https://gerrit.libreoffice.org/c/core/+/154535
I build daily from master locally. Was 7.6, but I keeped the folder updated with 24.2, even if it is named 7.6.
(In reply to BogdanB from comment #20) > I build daily from master locally. Was 7.6, but I keeped the folder updated > with 24.2, even if it is named 7.6. I gave a new try locally, with or without the patch, I get the same thing: /home/julien/lo/libreoffice/connectivity/source/parse/sqlbison.y: avertissement: 13 conflits par décalage/réduction [-Wconflicts-sr] /home/julien/lo/libreoffice/connectivity/source/parse/sqlbison.y: avertissement: 15 conflits par réduction/réduction [-Wconflicts-rr] /home/julien/lo/libreoffice/connectivity/source/parse/sqlbison.y: note: exécutez avec l'option « -Wcounterexamples » pour générer des contre-exemples de conflits ... /home/julien/lo/libreoffice/workdir/LexTarget/connectivity/source/parse/sqlflex.cxx:6108:14: warning: code will never be executed [-Wunreachable-code] if ( ! (yy_did_buffer_switch_on_eof) ) ^~~~~~~~~~~~~~~~~~~~~~~~~~~ /home/julien/lo/libreoffice/workdir/LexTarget/connectivity/source/parse/sqlflex.cxx:6436:14: warning: code will never be executed [-Wunreachable-code] if ( ! (yy_did_buffer_switch_on_eof) ) ^~~~~~~~~~~~~~~~~~~~~~~~~~~ /home/julien/lo/libreoffice/workdir/LexTarget/connectivity/source/parse/sqlflex.cxx:6431:5: warning: unannotated fall-through between switch labels [-Wimplicit-fallthrough] case EOB_ACT_END_OF_FILE: ^ /home/julien/lo/libreoffice/workdir/LexTarget/connectivity/source/parse/sqlflex.cxx:6431:5: note: insert '[[fallthrough]];' to silence this warning case EOB_ACT_END_OF_FILE: ^ [[fallthrough]]; /home/julien/lo/libreoffice/workdir/LexTarget/connectivity/source/parse/sqlflex.cxx:6431:5: note: insert 'break;' to avoid fall-through case EOB_ACT_END_OF_FILE: ^ break; 3 warnings generated. I don't have yours so I'm a bit stuck here :-(
Unassign myself, feel free to revert my patch if you think it wrong.
Tested with Version: 24.2.0.0.alpha1 (X86_64) / LibreOffice Community Build ID: 06946980c858649160c634007e5fac9a5aa81f38 CPU threads: 6; OS: Linux 5.14; UI render: default; VCL: kf5 (cairo+xcb) Locale: de-DE (de_DE.UTF-8); UI: de-DE Calc: threaded Works in GUI without any problem. I will set this one to RESOLVED → FIXED