Summary: | Query-Wizard produces syntax error: 9.99 vs 9,99 (SQL Status: HY000) | ||
---|---|---|---|
Product: | LibreOffice | Reporter: | Edmund Laugasson <edmund.laugasson> |
Component: | Database | Assignee: | Not Assigned <libreoffice-bugs> |
Status: | NEW --- | QA Contact: | |
Severity: | normal | ||
Priority: | medium | CC: | dr, iplaw67, robert, serval2412, zreizinger |
Version: | 3.3.3 release | ||
Hardware: | Other | ||
OS: | All | ||
Whiteboard: | |||
i915 platform: | i915 features: | ||
Attachments: |
Query creation wizard final step
Message: "Syntax error in SQL expression" error list window: first message "Syntax error in SQL expression" error list window: second message "SELECT "Customers"."CustomerID"..." error list window: third message "syntax error, unexpected..." Database file itself with entered table and query. |
Created attachment 48395 [details]
Message: "Syntax error in SQL expression"
Created attachment 48396 [details]
error list window: first message "Syntax error in SQL expression"
Created attachment 48397 [details]
error list window: second message "SELECT "Customers"."CustomerID"..."
Created attachment 48398 [details]
error list window: third message "syntax error, unexpected..."
....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. 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...
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. [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 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? @Robert, pleas have a look on this bugreport. What is your opinion? 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. (In reply to comment #11) Hi Robert, > I would change the title of this bug. IMHO you have changed. Right? 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. 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.
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