Created attachment 90158 [details] Demo database Prior to 4.2 Beta (e.g. in 4.1.3.2) the following SQL is valid: SELECT "id", "txt", "num" FROM "Table1" WHERE "num" IS NULL In 4.2 Beta this results in: "Syntax error in SQL statement syntax is ambiguous" See the attached demonstration odb, which includes the definition of Table1 (id is the integer primary key, txt is mandatory text, num is an optional integer), and the above query is Query1. The same error occurs using an external database with much more complex queries (I came across it with a MariaDB 10 database connected using MySql(JDBC)). I produced this stand-alone example to make it easy to reproduce the problem. This is a non-trivial issue, since checking if a field is NULL is particularly useful in some situations (eg where it may be a foreign key).
Reproducible. Win7x64 Version: 4.2.0.0.beta1 Build ID: f4ca7b35f580827ad2c69ea6d29f7c9b48ebbac7 Select "Run SQL command directly" icon, works fine for me. NEW for All systems.
In any form where you need to filter, sort, or link from a subform, and so on, LO needs to inspect the SQL. So whilst it is true that running SQL directly avoids the problem, sadly it doesn't work for many of my forms.
Hi tim, it was for information about the bug, as you can see the bug status was changed to NEW for All Operating Systems. Now depends on developers.
Hi Mario, Sorry if I appeared ungrateful for your info. I too was just trying to make sure the developers had a complete picture of the problem. I am continually amazed by the speed and quality of response from the likes of yourself and the others involved in LibreOffice. It really encourages ordinary users such as myself to spend a bit of time trying to pin problems down and then reporting them :-)
No problem Tim, thanks, I think all are looking in the same direction.
Reproduce also with OpenSUSE 12.3 64bit rpm. Same query works right with LO 4.1.3.2, so a regression.
@Lionel : any chance that this is an unwanted side effect to the changes in "IS NULL" parsing ? Alex
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=ee712dd6964daafa463febea8a948ea277ad16c7 fdo#72267 boolean_test is subsumed by general case "foo IS [NOT] bar" The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
(In reply to comment #7) > @Lionel : any chance that this is an unwanted side effect to the changes in > "IS NULL" parsing ? Yes and no. The changes to "IS NULL" parsing are far older than that, but what happened more recently is that the parser what switched to a mode where it is more powerful (it recognises more expressions), but more strict. Previously, in the case there was an ambiguity in the grammar, the parser would just make a more-or-less not semantically justified choice (that is, a choice that has nothing to do with the grammer / semantics, just linked to implementation details). Now, it throws an error saying "there is an ambiguity", and the ambiguity needs to be resolved in the grammar (either by making it non-ambiguous or by assigning priorities to the different cases so that exactly one case "wins"). So, in short, if testers could throw their most varied SQL at the parser and stress-test it, it would be really nice. So that we "catch" regressions as these as early as possible.
The "far older changes to parsing IS NULL" have introduced the ambiguity that this case triggers, but since before an ambiguity was not an error (but a subtle bug waiting to be discovered...), we get this "regression" now that the parser is strict about ambiguities.
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "libreoffice-4-2": http://cgit.freedesktop.org/libreoffice/core/commit/?id=962cdfb1370b983fbc7f463edfab1dad53b5a8e7&h=libreoffice-4-2 fdo#72267 boolean_test is subsumed by general case "foo IS [NOT] bar" It will be available in LibreOffice 4.2. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Verified fixed in 4.2.0.1. Thanks.