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
Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.