Bug report #7380

Different behavior for "Field" = NULL in Query builder and Advanced search

Added by Maxim Dubinin over 7 years ago. Updated almost 5 years ago.

Status:Closed
Priority:Normal
Assignee:-
Category:-
Affected QGIS version:master Regression?:No
Operating System:Windows Easy fix?:No
Pull Request or Patch supplied:No Resolution:up/downstream
Crashes QGIS or corrupts data:No Copied to github as #:16352

Description

(may be not really a bug, but inconsistent behavior indeed)

In Query builder (Layer props\\General\\Query builder) the following works:

"AMENITY" = NULL

For the same layer, Table\\Advanced search, form is the same, but above won't work and need to modified to

"AMENITY" IS NULL

note that "IS" is not among the form operators.


Related issues

Related to QGIS Application - Bug report #5102: query ["field" != 'NULL'] works while ["field" = 'NULL'] ... Rejected 2012-02-29
Related to QGIS Application - Feature request #7510: NULL string should be valid in label expressions Closed 2013-04-04
Duplicated by QGIS Application - Bug report #8129: Vector feature subset query uses ilike as like for non-la... Closed 2013-06-21
Duplicated by QGIS Application - Bug report #13878: Same SQL expression behaving differently Closed 2015-11-25

History

#1 Updated by Matthias Kuhn over 7 years ago

  • Status changed from Open to Feedback

The "Subset" in the layer properties actually forwards the clause to the back-end, so a full compatibility with QGIS Expressions is not guaranteed.
But: Assuming compliance with standard sql:2003, "AMENITY" = NULL is not supposed to return TRUE [1].

Most likely, you should report this bug upstream to the appropriate data provider. So the question is, what data source your layer is using?

Nevertheless, a message in the General layer properties tab, which clarifies, that this query will be parsed by the data provider would be good IMHO.

[1] http://en.wikipedia.org/wiki/SQL#Null_and_three-valued_logic_.283VL.29

#2 Updated by Maxim Dubinin over 7 years ago

It is ESRI Shapefile.

But again, it works with Query builder, but does not with Advanced search query. Same source, same form, different behavior.

#3 Updated by Jürgen Fischer over 7 years ago

  • Resolution set to wontfix
  • Status changed from Feedback to Closed

Maxim Dubinin wrote:

It is ESRI Shapefile.

But again, it works with Query builder, but does not with Advanced search query. Same source, same form, different behavior.

In the "Advanced search" you are using QGIS expression and in the query builder (if used to define filter layers) you are creating provider subset strings - the latter are provider specific and in your case (shapefiles through OGR) are OGR SQL (where apparently comparision with NULL is handled differently than in "normal" SQL).

#4 Updated by Jürgen Fischer almost 5 years ago

  • Resolution changed from wontfix to up/downstream

BTW this is not reproducable with current OGR (1.11.3 here). QGIS returns no rows filtering for empty=NULL where empty is a all-NULL column. Same for ogrinfo:

ogrinfo -so -where "empty=NULL" airports.shp airports
INFO: Open of `airports.shp'
      using driver `ESRI Shapefile' successful.

Layer name: airports
Geometry: Point
Feature Count: 0
[...]
empty: String (20.0)

While filtering for empty IS NULL retrieves all rows - consistent with:

ogrinfo -so -where "empty IS NULL" airports.shp airports
INFO: Open of `airports.shp'
      using driver `ESRI Shapefile' successful.

Layer name: airports
Geometry: Point
Feature Count: 77
[...]
empty: String (20.0)

Also available in: Atom PDF