Bug 59978 - EDITING: Query-Wizard locale/date-format mixup
Summary: EDITING: Query-Wizard locale/date-format mixup
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
(earliest affected)
3.3.4 release
Hardware: All All
: high major
Assignee: Lionel Elie Mamane
Depends on:
Blocks: 67664
  Show dependency treegraph
Reported: 2013-01-28 16:16 UTC by Joel Quinqueton
Modified: 2016-01-18 13:50 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:

sample database (3.64 KB, application/vnd.oasis.opendocument.database)
2013-02-20 13:30 UTC, Joel Quinqueton

Note You need to log in before you can comment on or make changes to this bug.
Description Joel Quinqueton 2013-01-28 16:16:34 UTC
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?
Comment 1 Robert Großkopf 2013-02-09 18:50:45 UTC
Seems to be a special Mac-Bug. I have tried the same with the Query-wizard (you have written "assistant") in LO under OpenSuSE Linux 32bit rpm. The wizards produces the right code under Linux.
Comment 2 Julien Nabet 2013-02-19 21:46:41 UTC
Joel: did you check with a more recent LO version? There are 3.6.5 and 4.0
Comment 3 Joel Quinqueton 2013-02-20 13:30:17 UTC
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'
Comment 4 Joel Quinqueton 2013-02-20 13:54:51 UTC
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.
Comment 5 Julien Nabet 2013-03-07 20:53:36 UTC
Alexander/Roman: would one of you have some time to give it a try?
Comment 6 Alex Thurgood 2013-03-08 10:21:03 UTC
1) Downloaded ODB file submitted by initial reporter.

2) Opened file in LibreOfficeDev :
Version (Build ID: 9cae1dc5311c09168fbe1f04bea3d4ee33a04bb)

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 :


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.


Adding Lionel to CC

Comment 7 Alex Thurgood 2013-03-08 10:24:53 UTC
So, what recent changes might have caused this ?

- more RTL/OUString (good/bad)ness ?

I don't think the wizard has been touched per se ?

Comment 8 Alex Thurgood 2013-03-08 10:29:41 UTC
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 :


which is obviously wrong.

Comment 9 Alex Thurgood 2013-03-08 10:31:30 UTC
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)
Comment 10 Alex Thurgood 2013-03-08 10:39:08 UTC
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.

Comment 11 Alex Thurgood 2013-03-08 10:40:21 UTC
(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'

Comment 12 Alex Thurgood 2013-03-08 10:50:48 UTC
Comparisons :

LO 334 : Wizard fails with :

Statut SQL: HY000
Code d'erreur: 1000


Statut SQL: HY000
Code d'erreur: 1000

syntax error, unexpected INVALIDSYMBOL, Invalid symbol:
Comment 13 Alex Thurgood 2013-03-08 11:00:44 UTC
Also fails to work in LO357:

Statut SQL: HY000
Code d'erreur: 1000


Statut SQL: HY000
Code d'erreur: 1000

syntax error, unexpected ')'
Comment 14 Alex Thurgood 2013-03-08 11:06:04 UTC
And fails in LO364 :

Statut SQL: HY000
Code d'erreur: 1000


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)

Comment 15 Alex Thurgood 2013-03-08 11:07:57 UTC
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.

Comment 16 Alex Thurgood 2013-03-08 11:16:21 UTC
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'   )]
Comment 17 Alex Thurgood 2013-03-08 11:23:21 UTC
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)


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.

Comment 18 Lionel Elie Mamane 2013-05-14 05:52:26 UTC
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...
Comment 19 Lionel Elie Mamane 2013-05-14 05:58:40 UTC
On LibreOffice (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.
Comment 20 Lionel Elie Mamane 2013-05-14 06:02:16 UTC
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.)
Comment 21 Alex Thurgood 2015-01-03 17:41:05 UTC
Adding self to CC if not already on
Comment 22 QA Administrators 2016-01-17 20:04:33 UTC
** 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)


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
Comment 23 Joel Quinqueton 2016-01-18 13:50:11 UTC
I tested on LibreOffice 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.