Bug 38667 - Query-Wizard produces syntax error: 9.99 vs 9,99 (SQL Status: HY000)
Summary: Query-Wizard produces syntax error: 9.99 vs 9,99 (SQL Status: HY000)
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
3.3.3 release
Hardware: Other All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2011-06-25 04:12 UTC by Edmund Laugasson
Modified: 2016-01-30 09:46 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Query creation wizard final step (51.80 KB, image/png)
2011-06-25 04:12 UTC, Edmund Laugasson
Details
Message: "Syntax error in SQL expression" (15.11 KB, image/png)
2011-06-25 04:14 UTC, Edmund Laugasson
Details
error list window: first message "Syntax error in SQL expression" (12.79 KB, image/png)
2011-06-25 04:15 UTC, Edmund Laugasson
Details
error list window: second message "SELECT "Customers"."CustomerID"..." (23.64 KB, image/png)
2011-06-25 04:17 UTC, Edmund Laugasson
Details
error list window: third message "syntax error, unexpected..." (17.68 KB, image/png)
2011-06-25 04:18 UTC, Edmund Laugasson
Details
Database file itself with entered table and query. (7.33 KB, application/vnd.oasis.opendocument.database)
2011-06-25 04:41 UTC, Edmund Laugasson
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Edmund Laugasson 2011-06-25 04:12:24 UTC
Created attachment 48394 [details]
Query creation wizard final step

Using Ubuntu 10.04 LTS with all updates at 25th June 2011.
Using LibreOffice 3.3.3 32-bit version downloaded and installed manually from LibreOffice website. LibreOffice is configured so, that UI is in English but all other locales are Estonian (Locale setting: Estonian, Default currency: EUR € Estonian, Default languages for documents: Estonian), because I'm Estonian.

How to reproduce the error?
1. Create table in design view with following data:
Field Name: CustomerID (set it also as Primary Key)
Field type: BigInt [BIGINT] (language: Estonian)
--
Field Name: FirstName
Field type: Text [VARCHAR_IGNORECASE] (language: Estonian)
--
Field Name: LastName
Field type: Text [VARCHAR_IGNORECASE] (language: Estonian)
--
Field Name: AmountDue
Field type: Decimal [DECIMAL] (format: EEK kr Estonian; language: Estonian)
--
Field Name: DateJoined
Field type: Date [DATE] (language: Estonian)
--
Field Name: StoreID
Field type: BigInt [BIGINT] (language: Estonian)

Give the name Customers to the table and save the database.

2. Enter some data into that table so, that some of AmountDue values are more than 9,99 and some are less. It means 9 EEK and 99 cents. Yes, I know, that in Estonia is current currency euro (since 01.01.2011) but this table I created with previous currency. Regardless of currency the following query should work and LibreOffice 3.3.3 supports both of them.

3. Create query using the previously created Customers table using wizard:
 - choose table Customers
 - enter all available fields into Field in the Query column
 - sort by Customers.AmountDue using Descending
 - Match all of the following:
     - Fields: Customers.AmountDue
     - Condition: is greater than
     - Value: 9,99
When I hit TAB, then it adds automatically "kr", so it looks like 9,99 kr
 - Detailed qquery
 - Assign aliases - I didn't change them
 - Name of the query: AmountDue and Display query and then Finish

4. Then I got 3 errors:
 1) SQL Status: HY000
    Error code: 1000
    Syntax error in SQL expression
 2) SQL Status: HY000
    Error code: 1000
    SELECT "Customers"."CustomerID" AS "CustomerID", "Customers"."FirstName" AS "FirstName", "Customers"."LastName" AS "LastName", "Customers"."AmountDue" AS "AmountDue", "Customers"."DateJoined" AS "DateJoined", "Customers"."StoreID" AS "StoreID" FROM "Customers" "Customers" WHERE ("Customers"."AmountDue" > 9,99)
 3) SQL Status: HY000
    Error code: 1000
    syntax error, unexpected ',', expecting ')' or OR
    syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE
Comment 1 Edmund Laugasson 2011-06-25 04:14:07 UTC
Created attachment 48395 [details]
Message: "Syntax error in SQL expression"
Comment 2 Edmund Laugasson 2011-06-25 04:15:31 UTC
Created attachment 48396 [details]
error list window: first message "Syntax error in SQL expression"
Comment 3 Edmund Laugasson 2011-06-25 04:17:30 UTC
Created attachment 48397 [details]
error list window: second message "SELECT "Customers"."CustomerID"..."
Comment 4 Edmund Laugasson 2011-06-25 04:18:38 UTC
Created attachment 48398 [details]
error list window: third message "syntax error, unexpected..."
Comment 5 Edmund Laugasson 2011-06-25 04:28:53 UTC
....but when to enter query without any validation, then it goes through.

When to edit this query and add > 9.99 to the Criterion field and save - everything looks normal.

When to edit that query in SQL-view then I see the following query:
SELECT "CustomerID" AS "Customer ID", "FirstName" AS "First Name", "LastName" AS "Last Name", "AmountDue" AS "Amount Due", "DateJoined" AS "Date Joined", "StoreID" AS "Store ID" FROM "Customers" WHERE "AmountDue" > 9.99

.. so, it wrote itself the 9.99 instead of 9,99

Through the wizard Base did not accept the value 9.99 because AmountDue field format is set to EEK kr Estonian and language is set to Estonian - it means that only 9,99 will be accepted and when hit to TAB after entering 9,99 - it recognizes it and puts the kr after the number. But somehow the wizard gives the error messages and therefore I created that bug report. It is quite annoying and doing lessons or exams using LibreOffice Base is quite problematic...

But if to enter 9.99 and hit TAB, then it replaces the number with 0,00 because . is not acceptable separator for Estonian currency format.

If you know it - you can enter the correct query directly using SQL view mode but this is not acceptable for beginners and regular everyday usage.

Current only one work-around is, that DO NOT ENTER the criteria during wizard but edit AFTER creating query and enter then the Criterion. Then you can use GUI and don't need to know SQL commands.
Comment 6 Edmund Laugasson 2011-06-25 04:41:54 UTC
Created attachment 48399 [details]
Database file itself with entered table and query.

Entered query using wizard and left the value field empty. After saving query, edited it and added > 9,99 to the Criterion field in the AmountDue column. Saved and everything works. The problem is, that the wizard does not accept the 9,99 value - only editing query AFTER its creation works...
Comment 7 Zoltán Reizinger 2011-06-27 05:30:24 UTC
It works same in OOo 3.3, 3.4Beta and LibO 3.4 under win7, with locale settings which use comma as separator, in my case Hungarian.
Comment 8 Björn Michaelsen 2011-12-23 12:23:45 UTC
[This is an automated message.]
This bug was filed before the changes to Bugzilla on 2011-10-16. Thus it
started right out as NEW without ever being explicitly confirmed. The bug is
changed to state NEEDINFO for this reason. To move this bug from NEEDINFO back
to NEW please check if the bug still persists with the 3.5.0 beta1 or beta2 prereleases.
Details on how to test the 3.5.0 beta1 can be found at:
http://wiki.documentfoundation.org/QA/BugHunting_Session_3.5.0.-1

more detail on this bulk operation: http://nabble.documentfoundation.org/RFC-Operation-Spamzilla-tp3607474p3607474.html
Comment 9 Jochen 2012-07-27 14:45:22 UTC
Hi Edmund,

are you using LO 3.4 or 3.5? The last stable version is 3.5.5. Is bug still in 3.4 or 3.5?
Comment 10 Jochen 2012-08-03 19:15:51 UTC
@Robert,
pleas have a look on this bugreport. What is your opinion?
Comment 11 Robert Großkopf 2012-08-04 07:01:08 UTC
The bug already exists. But it is only a bug in the query-wizard. When you enter 9,99 in "Create Query in Design-View" it works.
The query-wizard doesn't work correctly, when there is given a decimal-separator with a comma. And you could not set this to a decimal-separator with a point - then the wizard sets this to 0,00.
I would change the title of this bug. Everybody has to see, that it's a problem of the wizard, not of the GUI.
Comment 12 Jochen 2012-08-04 07:38:11 UTC
(In reply to comment #11)
Hi Robert,

> I would change the title of this bug.
IMHO you have changed. Right?
Comment 13 Julien Nabet 2014-12-26 21:25:11 UTC
On pc Debian x86-64 with French UI, I can reproduce the problem with LO Debian 4.3.3 package, 4.3 sources updated some days ago.
However, I don't reproduce this with 4.4 sources updated yesterday.
Comment 14 Alex Thurgood 2015-01-03 17:39:15 UTC
Adding self to CC if not already on
Comment 15 QA Administrators 2016-01-17 20:04:23 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)

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
Comment 16 Robert Großkopf 2016-01-30 09:46:04 UTC
Have tested this one with LO 5.1.0.3, OpenSUSE 42.1 Leap 64bit rpm Linux.
Started the query-wizard as described. The wizard creates the right query. '9,99' has been changed to '9.99'. Tested a little bit more:
Wizards fails with all LO-versions up to 4.3.7.
Bug doesn't appear with LO 4.4.7 and all newer versions (haven't installed the versions between 4.3.7 and 4.4.7).
So I set this bug as RESOLVED and WORKSFORME.