Bug 72267 - "Is Null" is invalid SQL
Summary: "Is Null" is invalid SQL
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.2.0.0.beta1
Hardware: x86-64 (AMD64) All
: highest blocker
Assignee: Lionel Elie Mamane
URL:
Whiteboard: target:4.3.0 target:4.2.0.1
Keywords: regression
Depends on:
Blocks: mab4.2
  Show dependency treegraph
 
Reported: 2013-12-03 13:44 UTC by tim
Modified: 2013-12-21 12:59 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments
Demo database (3.66 KB, application/vnd.oasis.opendocument.base)
2013-12-03 13:44 UTC, tim
Details

Note You need to log in before you can comment on or make changes to this bug.
Description tim 2013-12-03 13:44:39 UTC
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).
Comment 1 m_a_riosv 2013-12-03 15:06:33 UTC
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.
Comment 2 tim 2013-12-03 15:24:15 UTC
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.
Comment 3 m_a_riosv 2013-12-03 17:23:05 UTC
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.
Comment 4 tim 2013-12-03 18:04:40 UTC
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 :-)
Comment 5 m_a_riosv 2013-12-03 18:24:06 UTC
No problem Tim, thanks, I think all are looking in the same direction.
Comment 6 Robert Großkopf 2013-12-03 18:39:13 UTC
Reproduce also with OpenSUSE 12.3 64bit rpm. Same query works right with LO 4.1.3.2, so a regression.
Comment 7 Alex Thurgood 2013-12-07 15:50:12 UTC
@Lionel : any chance that this is an unwanted side effect to the changes in "IS NULL" parsing ?


Alex
Comment 8 Commit Notification 2013-12-07 20:37:35 UTC
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.
Comment 9 Lionel Elie Mamane 2013-12-07 20:58:10 UTC
(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.
Comment 10 Lionel Elie Mamane 2013-12-07 20:59:52 UTC
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.
Comment 11 Commit Notification 2013-12-09 19:52:44 UTC
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.
Comment 12 tim 2013-12-21 12:59:36 UTC
Verified fixed in 4.2.0.1.  Thanks.