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
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
** 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
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.