Bug report #7380
Different behavior for "Field" = NULL in Query builder and Advanced search
| 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
History
#1
Updated by Matthias Kuhn almost 12 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 almost 12 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 almost 12 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 about 9 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)