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.
*** This bug has been marked as a duplicate of bug 33194 ***
Andrew, 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. Alex
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".
Ah OK, so notabug ? Alex
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.
Let us have a look at the help of LO (and OOo, since all the years): "Query Design ... _LO command_ IS EMPTY IS NOT EMPTY LIKE (placeholder * for any number of characters placeholder ? for exactly one character) NOT LIKE BETWEEN x AND y NOT BETWEEN x AND y IN (a; b; c...) Note that the semicolons are used as separators in all value lists! NOT IN (a; b; c...) = TRUE = FALSE" 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".
(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.
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
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.
Norbert Thiebaud committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=a62a046df3302e5763b7a568ac25032bb1501d44 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: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.