Let the following table: COUREUR(NUMLICENCE, NOM, PRENOM, DATENAISSANCE), the last field being a DATE. We created a query in QBE or SQL mode (both work): select * from COUREUR where DATENAISSANCE > '1952-06-30' When we try to create the same query using the assistant, it ignores the content of the field in which we typed '1952-06-30' and gives the error "unexpected )". The query understood by the assistant is: select * from COUREUR where (DATENAISSANCE > ) And the same happens whatever we type in the field: 06/30/1952 or 30/06/1952 ou #30/06/1952#, even {D '1952-06-30'} as it appears in some SQL editing of QBE-made queries. It appears to be a bug of the assistant, isn't it? Sincerely
Seems to be a special Mac-Bug. I have tried the same with the Query-wizard (you have written "assistant") in LO 3.5.7.1 under OpenSuSE Linux 32bit rpm. The wizards produces the right code under Linux.
Joel: did you check with a more recent LO version? There are 3.6.5 and 4.0
Created attachment 75171 [details] sample database try to use the wizard to make the query whose SQL syntax is: select * from COUREUR where DATENAISSANCE >= '1952-06-30'
The bug seems to occur on the LO version 4.0.0 It seems to be Mac specific (I tried on OO 3.2 on ubuntu and it does not occur), and linked to the date formats, as if the filtering condition is on a 'integer' field, the wizard works perfectly well.
Alexander/Roman: would one of you have some time to give it a try?
1) Downloaded ODB file submitted by initial reporter. 2) Opened file in LibreOfficeDev : Version 4.1.0.0.alpha0+ (Build ID: 9cae1dc5311c09168fbe1f04bea3d4ee33a04bb) 3) In SQL mode : query works and returns correct result In Query Design mode : query works and returns correct result, date criteria string is automatically represented as > #30/06/1952# in the UI In the Query Wizard, after entering the date as filter criteria, and proceeding to Next until the query synopsis page, the following is displayed : Fields in the Query: NUMLICENCE (COUREUR.NUMLICENCE), NOM (COUREUR.NOM), PRENOM (COUREUR.PRENOM), DATENAISSANCE (COUREUR.DATENAISSANCE) No sorting fields were assigned. Search conditions: DATENAISSANCE is greater than #30/12/1999# No Groups were assigned. No grouping conditions were assigned. Note that under Search Conditions, DATENAISSANCE was set to greater than '1952-06-30' and yet from the synopsis, we can see that the value wasn't parsed correctly or at all, since it has set some completely unrelated other date. CONFIRMING Adding Lionel to CC Alex
So, what recent changes might have caused this ? - more RTL/OUString (good/bad)ness ? - YACC/FLEX ? I don't think the wizard has been touched per se ? Alex
If the date is entered in the wizard as '1952-06-30', it appears correctly in the synopsis, but clicking on Next causes an error message to be displayed that the SQL is incorrect. Further information from this message gives the query as : SELECT "COUREUR"."NUMLICENCE" AS "NUMLICENCE", "COUREUR"."NOM" AS "NOM", "COUREUR"."PRENOM" AS "PRENOM", "COUREUR"."DATENAISSANCE" AS "DATENAISSANCE" FROM "COUREUR" "COUREUR" WHERE (DATENAISSANCE > ) which is obviously wrong. Alex
gdb output when the error message is displayed : com.sun.star.sdbc.SQLException: Syntax error in SQL expression at com.sun.star.bridges.jni_uno.JNI_proxy.dispatch_call(Native Method) at com.sun.star.bridges.jni_uno.JNI_proxy.invoke(JNI_proxy.java:183) at com.sun.proxy.$Proxy57.setStructuredFilter(Unknown Source) at com.sun.star.wizards.db.SQLQueryComposer.setQueryCommand(SQLQueryComposer.java:306) at com.sun.star.wizards.db.SQLQueryComposer.setQueryCommand(SQLQueryComposer.java:289) at com.sun.star.wizards.db.SQLQueryComposer.setQueryCommand(SQLQueryComposer.java:284) at com.sun.star.wizards.query.Finalizer.finish(Finalizer.java:164) at com.sun.star.wizards.query.QueryWizard.finishWizard(QueryWizard.java:264) at com.sun.star.wizards.ui.WizardDialog.finishWizard_1(WizardDialog.java:687) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at com.sun.star.wizards.ui.event.MethodInvocation.invoke(MethodInvocation.java:76) at com.sun.star.wizards.ui.event.AbstractListener.invoke(AbstractListener.java:80) at com.sun.star.wizards.ui.event.CommonListener.actionPerformed(CommonListener.java:36) at com.sun.star.bridges.jni_uno.JNI_proxy.dispatch_call(Native Method) at com.sun.star.bridges.jni_uno.JNI_proxy.invoke(JNI_proxy.java:183) at com.sun.proxy.$Proxy55.execute(Unknown Source) at com.sun.star.wizards.ui.UnoDialog.executeDialog(UnoDialog.java:698) at com.sun.star.wizards.query.QueryWizard.start(QueryWizard.java:122) at com.sun.star.wizards.query.CallQueryWizard$QueryWizardImplementation.trigger(CallQueryWizard.java:80)
Attempting to set the Date in the filter step of the wizard with anything other than 06/30/52 fails to set the date correctly in the synopsis page, but this doesn't make any difference, as the query still doesn't run. Alex
(In reply to comment #10) > Attempting to set the Date in the filter step of the wizard with anything > other than 06/30/52 fails to set the date correctly in the synopsis page, > but this doesn't make any difference, as the query still doesn't run. > By "anything other", I meant any other variation on the date string representation '1952-06-30' Alex
Comparisons : LO 334 : Wizard fails with : Statut SQL: HY000 Code d'erreur: 1000 SELECT "COUREUR"."NUMLICENCE" AS "NUMLICENCE", "COUREUR"."NOM" AS "NOM", "COUREUR"."PRENOM" AS "PRENOM", "COUREUR"."DATENAISSANCE" AS "DATENAISSANCE" FROM "COUREUR" "COUREUR" WHERE ("COUREUR"."DATENAISSANCE" > #30/06/1952#) Statut SQL: HY000 Code d'erreur: 1000 syntax error, unexpected INVALIDSYMBOL, Invalid symbol:
Also fails to work in LO357: Statut SQL: HY000 Code d'erreur: 1000 SELECT "COUREUR"."NUMLICENCE" AS "NUMLICENCE", "COUREUR"."NOM" AS "NOM", "COUREUR"."PRENOM" AS "PRENOM", "COUREUR"."DATENAISSANCE" AS "DATENAISSANCE" FROM "COUREUR" "COUREUR" WHERE (DATENAISSANCE > ) Statut SQL: HY000 Code d'erreur: 1000 syntax error, unexpected ')'
And fails in LO364 : Statut SQL: HY000 Code d'erreur: 1000 SELECT "COUREUR"."NUMLICENCE" AS "NUMLICENCE", "COUREUR"."NOM" AS "NOM", "COUREUR"."PRENOM" AS "PRENOM", "COUREUR"."DATENAISSANCE" AS "DATENAISSANCE" FROM "COUREUR" "COUREUR" WHERE (DATENAISSANCE > ) Statut SQL: HY000 Code d'erreur: 1000 syntax error, unexpected ')' so, all in all, this has been there since the beginning of the existence of LO and was probably introduced during OOo 3.3.x development. Setting version to earliest known to show problem (3.3.4 production release) Alex
Have upped importance/priority for what its worth, as the wizard is supposed to work as designed and is often the starting point for most beginner users with Base. Alex
More comparison : NeoOffice 3.1.2 : With date string condition '1952-06-30' the following error message is produced : Statut SQL: 37000 Code d'erreur: -16 Wrong data type: java.lang.NumberFormatException: For input string: "YYYY" in statement [SELECT "COUREUR"."NUMLICENCE" AS "NUMLICENCE", "COUREUR"."NOM" AS "NOM", "COUREUR"."PRENOM" AS "PRENOM", "COUREUR"."DATENAISSANCE" AS "DATENAISSANCE" FROM "COUREUR" "COUREUR" WHERE ( "COUREUR"."DATENAISSANCE" > 'YYYY-06-DD 00:00:00' )]
NeoOffice 3.3 patch 4 With date condition : 30/06/52 (it doesn't seem to matter how you enter the string, it is still misinterpreted) Champs dans la requête : NUMLICENCE (COUREUR.NUMLICENCE), NOM (COUREUR.NOM), PRENOM (COUREUR.PRENOM), DATENAISSANCE (COUREUR.DATENAISSANCE) Aucun champ de tri n'a été assigné. Conditions de recherche : DATENAISSANCE est supérieur à {D 'YYYY-06-DD 00:00:00' } Aucun groupe n'a été assigné. Aucune condition de groupement n'a été assignée. Same problem. Alex
Reproduced on Debian GNU/Linux master commit ba26c5e6330f5f1f38aab698b2b2c32cac7b5df3 (Mon May 13 08:24:49 2013 +0200). The reason is "simply" that different parts of the wizard disagree on the locale and thus on the date format... Which means that you will not see the problem in a fully en-US locale (or any locale that has mm/dd/yyyy date-format). 1) The 'search conditions' step "clearly" uses a en_US locale; it expects the date as mm/dd/(yy)yy. In this bug's example, it displays the value as 06/30/52. 2) The 'Overview' step uses the user's locale, in my case fr_LU; Search conditions: DATENAISSANCE is equal to #30/06/1952# (not sure that's a good idea... aren't #DATE# strings supposed to be the MS-Access compatible "fixed" format mm/dd/yyyy?) 3) But when one clicks on "Finish", the SQL parser launched on the string "#30/06/1952#" is created without context, and thus defaults to en_US, and fails to parse that date because there is no month "30", only 1 to 12. By the way, second bug: the entered date in 'seach condition' step is formatted as mm/dd/yy, and then the century is LOST!!! Type 1/1/1515, it will understand 1/1/15, that is 1/1/2015. Sigh...
On LibreOffice 3.5.4.2 (debian x86-64 package), still wrong but slightly different: 1) The 'search conditions' step uses the user's locale (good!) So we have a regression here :( The rest is the same.
This bug disappears (in 4.1 and 3.5) with the following: Edit / Database / Advanced Settings Uncheck "Use ODBC conformant date/time literals" This forces ISO8601 dates, which are locale-independent. (The century still disappears, though.)
Adding self to CC if not already on
** Please read this message in its entirety before responding ** 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 on a currently supported version of LibreOffice (5.0.4 or later) https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System 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) http://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: http://webchat.freenode.net/?channels=libreoffice-qa Thank you for your help! -- The LibreOffice QA Team This NEW Message was generated on: 2016-01-17
I tested on LibreOffice 5.0.3.2 on MacOS X 10.10.5. simple filtering query: select * from COUREUR where (DATENAISSANCE > '1952-11-29') using the query wizard, the result depends on what we type in the date field of the wizard: typing 1952-11-29 works OK typing '1952-11-29' inserts > #30/12/1899#, then do not filter typing 29/11/1952 works OK typing #29/11/1952# inserts > #30/12/1899#, then do not filter I check that it works OK by looking at the query in QBE mode and SQL mode: * QBE mode: > #29/11/1952# appears in the criterion cell * SQL mode: > {d '1952-11-29' } appears in the SQL query Then, as our main purpose is to teach Database using OpenOffice Base, the behavior is OK now.