Bug 55703 - Query Design SELECT condition: should automatically use "=" or "IS" operator depending on constant value
Summary: Query Design SELECT condition: should automatically use "=" or "IS" operator ...
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
(earliest affected)
3.5.4 release
Hardware: All All
: medium enhancement
Assignee: Not Assigned
Whiteboard: target:4.4.0
Depends on:
Reported: 2012-10-06 20:59 UTC by Andrew
Modified: 2014-09-04 13:41 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Note You need to log in before you can comment on or make changes to this bug.
Description Andrew 2012-10-06 20:59:38 UTC
In the design view, when "null" is entered as a selection criterion, it is forced to "EMPTY". Similarly, "empty" is forced to uppercase "EMPTY".
So, you think the software has understood what you want.

However, this leads to generation of the SQL code "= NULL", which is syntactically correct but logicially incorrect and returns no rows.

It should be "IS NULL".
When the corrected query is edited back in the design view, "IS EMPTY" is displayed.
Conclusion : "null" and "empty" should be forced to "IS EMPTY".

All "" quotes are mine.
Comment 1 Alex Thurgood 2012-10-08 17:05:07 UTC

*** This bug has been marked as a duplicate of bug 33194 ***
Comment 2 Alex Thurgood 2012-10-08 17:07:08 UTC

Apparently this is a duplicate of 33194 which is observed to be fixed in 3.5.6 or a later 3.6.x version.

Try again with one of the latest stable production versions and re-open 33194 if the bug is still there.

Comment 3 Lionel Elie Mamane 2012-10-08 20:49:35 UTC
Nope, not duplicate of bug 33194.

However, I'm not convinced the behaviour is a bug; the default/implicit operator is "=". If one wants another operator, such as "IS", one needs to type it. That is, type "IS NULL" or "IS EMPTY" instead of just "NULL".
Comment 4 Alex Thurgood 2012-10-08 21:11:53 UTC
Ah OK, so notabug ?

Comment 5 Andrew 2012-10-08 21:40:15 UTC
Hi all,

Lionel may be strictly correct that the default operator is being used, but I think in this case the default is inappropriate.

The query design view is there to help people who don't know SQL, or not well. It appears to understand what you want to do but then generates SQL code which is not what you want, probably leaving many a user baffled about why the query is not returning any records.

The difference between = NULL and IS NULL is a bit subtle for a lot of people.
It's a feature of SQL but, in my humble opinion, the design view should be giving them what they want i.e. all records where the given field is empty.
Comment 6 Robert Großkopf 2012-10-10 06:26:02 UTC
Let us have a look at the help of LO (and OOo, since all the years):
"Query Design 
_LO command_
(placeholder * for any number of characters
placeholder ? for exactly one character)
IN (a; b; c...)
Note that the semicolons are used as separators in all value lists!
NOT IN (a; b; c...)

There is also explained, wht this means in SQL-Code. The LO command is translated to other languages. In Germany we use for "IS NULL" "IST LEER".

This bug could be a feature-request. All works like explained. Could be it is more user-friendly, when there are combofields, which show all commands listed above.

So I set this bug to "New" and the importance to "enhancement".
Comment 7 Lionel Elie Mamane 2012-10-14 15:59:03 UTC
(In reply to comment #5)

> The query design view is there to help people who don't know SQL, (...)

> The difference between = NULL and IS NULL is a bit subtle for a lot of
> people.

On the other hand, if we hide the difference from them, they will be baffled why comparison to a constant NULL works one way (NULL->true), but comparison to another field (that possibly contains NULL) works another way (NULL->false).

Maybe a good solution would be to fill in the "IS" in the UI. That is when the user enters just "NULL", don't let just "EMPTY" standing in the condition line and do "IS NULL" behind the scene, but replace the user's NULL by "IS EMPTY" instead of replacing it by only "EMPTY".

Maybe for extra clarity we could also always fill in the implicit "="? Not sure what would be the most user-friendly.
Comment 8 Joel Madero 2014-02-27 22:55:09 UTC
In order to limit the confusion between ProposedEasyHack and EasyHack and to make queries much easier we are changing ProposedEasyHack to NeedsDevEval.

Thank you and apologies for the noise
Comment 9 Graham Horner 2014-08-15 09:41:51 UTC
The parser should never generate " = NULL " since nobody should want this.  If users type NULL or EMPTY in the field, they should either want IS NULL or = 'NULL'.  Not easy to decide what the default behaviour should be but IMVHO entering NULL or EMPTY alone should generate IS NULL and display IS EMPTY in the field.  Similarly NOT NULL should generate IS NOT NULL and display IS NOT EMPTY.

I don't think the alternative of interpreting NULL as "= 'NULL'" is a good idea - let it behave similarly to the way TRUE and FALSE are handled.
Comment 10 Commit Notification 2014-09-04 13:41:46 UTC
Norbert Thiebaud committed a patch related to this issue.
It has been pushed to "master":


fdo#55703 automagically use IS NULL fo 'null' in query design.

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:
Affected users are encouraged to test the fix and report feedback.