Bug 85707 - NOT BETWEEN query not working (in query design view) when LibreOffice user interface is in Spanish
Summary: NOT BETWEEN query not working (in query design view) when LibreOffice user in...
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Localization (show other bugs)
Version:
(earliest affected)
4.3.1.2 release
Hardware: All All
: high major
Assignee: Adolfo Jayme Barrientos
URL: https://www.youtube.com/watch?v=pAEtZ...
Whiteboard: target:4.4.0 target:4.3.4
Keywords:
Depends on:
Blocks:
 
Reported: 2014-10-31 17:24 UTC by mrmister001
Modified: 2014-11-15 00:09 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
sample database (10.43 KB, application/vnd.oasis.opendocument.base)
2014-10-31 17:24 UTC, mrmister001
Details

Note You need to log in before you can comment on or make changes to this bug.
Description mrmister001 2014-10-31 17:24:40 UTC
Created attachment 108736 [details]
sample database

Hello, I want to report a severe bug that need to be fixed urgently.
The problem occur when designing a query in the design view.
If you put in criteria NOT BETWEEN, it won't work. However, if you change the user interface to English, then NOT BETWEEN works fine.
This probably is happening due a bad programming, because when you use LibreOffice in Spanish, it translate the operators, so LIKE is translated to COMO and so on... (COMO = LIKE in Spanish).
I guess that there is a defective programming in NOT BETWEEN because when you apply it in Spanish the result you get is this.
Exactly you get this error:

Estado de SQL: HY000
Código de error: 1000

syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE

The steps to reproduce the bug are as follows:

1. Start LibreOffice

2. Change the interface settings to Spanish, go to Tools... Options... Languaje Settings... select for User interface: Spanish (Spain) select for Locale setting: Spanish (Spain)

3. Open Base

4. Create a table with 2 fields for example: Name and Age, where Name is a TEXT VARCHAR field and AGE is an INTEGER field.

5. Introduce some data... for example: John 20, Mike 10, David 12... etc. etc.

6. Finally, go to Queries and in the Design View add that table, drag and drop the Age field and try to apply a NOT BETWEEN command in Criteria. You'll see it is translated to NO ENTRE... when you run the query, you'll get for sure the SQL error...
However if the Interface is set up as English USA and Locale Setting as English USA you won't get the result and NOT BETWEEN work fine...

As a result of this very critical bug, any user from the Spanish community can create queries using NOT BETWEEN

I ask please, for an urgent revision of this bug, people cannot work... because they cannot filter data in queries...

Please, fix it.

I am attaching also a sample database if you want to make some tests
Please, remember always to set up the user interface and locale settings to Spanish (Spain)
Comment 1 mrmister001 2014-10-31 17:34:04 UTC
Here you can get also the database I uploaded...

http://bitshare.com/files/9blbxisz/Base-de-datos-nueva13.odb.html
Comment 2 Robert Großkopf 2014-10-31 19:22:53 UTC
I could confirm the bug. Open a query with NOT BETWEEN ... AND ... (in the attached database for example: SELECT * FROM "tabla_EMPLEADOS" WHERE "Experiencia" NOT BETWEEN 5 AND 10). It works right in every language.
Now open this for editing in the GUI. The default-error apperas if you want to change anything in the query, if using "Spanish" as language. With "German" it works right. So there must be something been translated wrong from the code in the GUI to the SQL-code.

I have only installed the Spanish language-package for LO 4.3.3.2. So I don't know if there has anything been changed in the Query-GUI in Spanish for this special command.

Could be a better idee to put this bug to the component "Localization", because it is a special Spanish bug.
Comment 3 mrmister001 2014-10-31 19:41:45 UTC
Please, don't do the query in SQL, in SQL the query will work. The bug occur only when you design the query in the DESIGN MODE VIEW, please, do not use SQL commands to reproduce the bug, the bug only happen in DESIGN MODE VIEW and when the interface is set up to Spanish.
Comment 4 Robert Großkopf 2014-10-31 20:44:07 UTC
(In reply to mrmister001 from comment #3)
> Please, don't do the query in SQL, in SQL the query will work. The bug occur
> only when you design the query in the DESIGN MODE VIEW, please, do not use
> SQL commands to reproduce the bug, the bug only happen in DESIGN MODE VIEW
> and when the interface is set up to Spanish.

I have confirmed the bug. I don't understand this comment. Have only created a workig query and then wanted to change this in the GUI. I couldn't create a query in Spanish in the GUI, because I don't understand any Spanish. So I wouldn't be able to confirm the the bug - or I have to create a query in another way.

Better you could write something down about:
Is there a difference to older versions of LO, special the input in the Spanish GUI for «NOT BETWEEN ... AND ...».
Wouldn't it be better to change component to "Localization"?
Comment 5 mrmister001 2014-10-31 20:47:01 UTC
HERE YOU HAVE A STEP BY STEP VIDEO (9 MINUTES) IN WHICH I SHOW YOU HOW TO REPRODUCE THE BUG

https://www.youtube.com/watch?v=pAEtZ_Y_xCc

The video is hidden, that mean only people knowing this link will be able to watch it. Please, share the video with the community as much as you can, so they can see the problem in action

This is affecting to the whole Spanish community (all south America and Spain), so I guess this is very serious
Comment 6 mrmister001 2014-10-31 20:49:14 UTC
I've reproduced in video all the steps to create the bug, so you can see it reproduced and confirm it, or please, try to find someone that can have LibreOffice in Spanish to test this and finally confirm what is demonstrated in the video.
Comment 7 Robert Großkopf 2014-11-01 08:05:15 UTC
Seems you couldn't understand what I am writing.

I have confirmed the bug, but you won't give the information I have asked for. So I remove myself from the CCList.
Comment 8 mrmister001 2014-11-01 11:31:35 UTC
Is there a difference to older versions of LO, special the input in the Spanish GUI for «NOT BETWEEN ... AND ...».

REPLY: I don't know if the older versions of LibreOffice were having this error or not, I've discovered it in the current version.

Wouldn't it be better to change component to "Localization"?

* REPLY: * I am sorry, I don't understand what you mean about "component to "Localization"?, absolutely, you are right, I don't understand what you are saying. 

If you could explain so in detail (as I did to confirm the error) it would be greatly appreciated.

* In the meantime, we still having a CRITICAL issue that affect 400 million of potential users from the hispanic community. *

Summarizing: In Spanish, LibreOffice cannot tackle queries based in NOT BETWEEN ... AND when the queries are accomplished in the *** Design View Mode ***
Comment 9 Adolfo Jayme Barrientos 2014-11-01 12:23:31 UTC
> As a result of this very critical bug
> for an urgent revision of this bug
> a CRITICAL issue that affect 400 million

Gotcha, no need for more shoutin’ and bitchin’. All bugs are critical, of course.

Changing importance to a reasonable value, and changing this report’s component to Localization.
Comment 10 Adolfo Jayme Barrientos 2014-11-01 12:32:18 UTC
OK, I’ve fixed it. Please try to reproduce the error again when the next LibreOffice build comes out (4.3.4 RC1), just to confirm it’s actually fixed.

Thanks for the report and the (excellent) video!
Comment 11 Adolfo Jayme Barrientos 2014-11-01 12:45:06 UTC
And sorry if I sounded harsh in the first post, but, you know, there are thousands of reports to be handled and many people is setting their own bugs to “Critical”. Luckily this one was silly enough to be tackled immediately. Sorry for the inconvenience!

Here you have the release calendar so to know the estimated date of release for the next bug-fix version: https://wiki.documentfoundation.org/ReleasePlan/4.3#4.3.4_release
Comment 12 mrmister001 2014-11-01 16:43:17 UTC
Hi Adolfo! thanks for fixing it !
Don't worry everything is fine. I understand you when you say that everyone put everything as critical and perhaps you are a bit reluctant about new reports. Well, perhaps we have different opinions, but I still thinking this is critical.
Imagine the situation. Imagine a company need to filter data to present that data to new customers and they cannot filter those data. That will result in losing customers, or even losing orders and then money.
Let's focus this in other way. Imagine a database that cannot retrieve information properly from tables and cannot accomplish queries? if a database cannot retrieve information, is like if I remove the wheels to a car.
That was my judgement to select it as critical.
However, the bug is solved. So thanks a lot for that.
I will test this at the end of November, which is the expected date of release for the new version.
Again THANKS :)
Comment 13 mrmister001 2014-11-01 16:46:40 UTC
Sorry, forgot to ask you this? when I reproduce the bug next month, where may I inform you everything is fine? do I have to report you to an email? or directly here?
Comment 14 Jouni Järvinen 2014-11-01 17:22:34 UTC
(In reply to mrmister001 from comment #13)
> Sorry, forgot to ask you this? when I reproduce the bug next month, where
> may I inform you everything is fine? do I have to report you to an email? or
> directly here?

Here of course.
Comment 15 mrmister001 2014-11-14 23:53:36 UTC
*** THE PROBLEM WAS FIXED AND NOW WE DON'T HAVE IT ANYMORE ***

I don't know who solved it, I guess it was Adolfo?
Well, he did a fine job (or the people that solved the error did a nice job), because now NOT BETWEEN is working fine, and... I observe there are no more translations for the query database commands. That is the right way.
When someone is programming, don't need translations of the programming language, a Spanish programmer or a Russian programmer, don't need Java or C++ translated to russian or spanish... in the same way NOT BETWEEN is exactly that NOT BETWEEN and don't need to be translated like NO ENTRE or other things, so the error was solved smoothly and perfectly.

Summarizing: THANKS A LOT AND GREAT JOB !
Comment 16 Jouni Järvinen 2014-11-14 23:59:46 UTC
(In reply to mrmister001 from comment #15)
> *** THE PROBLEM WAS FIXED AND NOW WE DON'T HAVE IT ANYMORE ***
> 
> I don't know who solved it, I guess it was Adolfo?
> Well, he did a fine job (or the people that solved the error did a nice
> job), because now NOT BETWEEN is working fine, and... I observe there are no
> more translations for the query database commands. That is the right way.
> When someone is programming, don't need translations of the programming
> language, a Spanish programmer or a Russian programmer, don't need Java or
> C++ translated to russian or spanish... in the same way NOT BETWEEN is
> exactly that NOT BETWEEN and don't need to be translated like NO ENTRE or
> other things, so the error was solved smoothly and perfectly.
> 
> Summarizing: THANKS A LOT AND GREAT JOB !

Worse yet, most languages don't support direct translation, at least not in exactly sensible sense, like Finnish. It's possible, but sometimes impossibly tricky.