Bug report #5102

query ["field" != 'NULL'] works while ["field" = 'NULL'] doesn't

Added by Giovanni Manghi over 12 years ago. Updated over 12 years ago.

Status:Rejected
Priority:Normal
Assignee:-
Category:Vectors
Affected QGIS version:master Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:worksforme
Crashes QGIS or corrupts data:No Copied to github as #:14864

Description

Note: not sure this is a bug, just looking for feedback and see what it is the overall opinion

I understand that the correct way to select NULL values is

"field" IS NULL

but as user I find it counter intuitive as

"field" != 'NULL'

works (but only if "NULL" is between " ' ") so I would expect also

"field" = 'NULL'

to work.

In any case I would also support

"field" != NULL

and

"field" = NULL

without the need of " ' " around "NULL".

Tested on qgis master.

Related issues

Related to QGIS Application - Bug report #7380: Different behavior for "Field" = NULL in Query builder an... Closed 2013-03-17

History

#1 Updated by Jürgen Fischer over 12 years ago

Looks like the inconsistency is a bug in OGR - I assume you tried the expression as "Query..." string on an OGR layer. "field"!='NULL' shouldn't match the rows with null values.

Any operation on a null value except IS/IS NOT is also null (like comparing a null field value with a four-letter-string as in "field"='NULL'). Therefore the rows with null values in field should not match either expression. The internal QGIS expressions (as in advanced search from the attribute table) work that way and I bet filtering with PostgreSQL and SpatiaLite layer won't show those rows either.

I'd consider anything else a bug.

#2 Updated by Giovanni Manghi over 12 years ago

Hi Jürgen,

Jürgen Fischer wrote:

Looks like the inconsistency is a bug in OGR - I assume you tried the expression as "Query..." string on an OGR layer. "field"!='NULL' shouldn't match the rows with null values.

I made a test on a PostGIS layer

the query

"field"!= NULL

fails (no matches) if the column is numeric, but

"field"!= 'NULL'

works if the column is text (it indeed matches the rows that are not NULL).

To note that the QGIS query builder never places the " ' " around "NULL" even if the column is text (as expected?), so to get the working

"field"!= 'NULL'

I had to place manually the " ' ".

Any operation on a null value except IS/IS NOT is also null (like comparing a null field value with a four-letter-string as in "field"='NULL'). Therefore the rows with null values in field should not match either expression. The internal QGIS expressions (as in advanced search from the attribute table) work that way and I bet filtering with PostgreSQL and SpatiaLite layer won't show those rows either.

I'd consider anything else a bug.

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

Giovanni Manghi wrote:

I made a test on a PostGIS layer. the query

"field"!=NULL

fails (no matches) if the column is numeric, but

"field"!='NULL'

works if the column is text (it indeed matches the rows that are not NULL).

'NULL' and NULL are different things - one is a four-letter-string and the other is the (special) null value (ie. no value, "n/a", unknown). "field"!='NULL' finds all values that have a value (i.e. are not NULL) that is different from the four-letter-string ('NULL'). You could also say "field"!='Some other value that doesn''t actually appear in the database' and get the same result.

To note that the QGIS query builder never places the " ' " around "NULL" even if the column is text (as expected?), so to get the working

"field"!='NULL'

If it would it would also change the expression's semantics (s.above). "field"!=NULL is always NULL - so there's not a big problem that the builder doesn't handle that special case.

#4 Updated by Giovanni Manghi over 12 years ago

Hi Jurgen

'NULL' and NULL are different things - one is a four-letter-string and the other is the (special) null value (ie. no value, "n/a", unknown). "field"!='NULL' finds all values that have a value (i.e. are not NULL) that is different from the four-letter-string ('NULL'). You could also say "field"!='Some other value that doesn''t actually appear in the database' and get the same result.

actually I knew the difference :) and understand perfectly the situation, the ticket was open after a user feedback that was trying to select all the records that are different than NULL in that specific (text) column. He has done a perfectly understandable series of steps:

he tested

"field"!= NULL

that returned 0 results, then he placed NULL aroud " ' " because he knew that the column was text and this way got what he needed. He then tested the opposite and noticed that is not working.

If it would it would also change the expression's semantics (s.above). "field"!=NULL is always NULL - so there's not a big problem that the builder doesn't handle that special case.

for me is ok, from the users point of view they will get a consistent behaviour and to select what is NULL they will have to learn to use IS/IS NOT ;)

#5 Updated by Jürgen Fischer over 12 years ago

  • Status changed from Open to Rejected
  • Resolution set to worksforme

Giovanni Manghi wrote:

that returned 0 results, then he placed NULL aroud " ' " because he knew that the column was text and this way got what he needed. He then tested the opposite and noticed that is not working.

...the way he expected.

#6 Updated by Pietro Rossin over 12 years ago

Hi
in my case (qgis 1.7.4.5 8058b22) I was trying to select non null features, values coming from a joined table field.
No way to perform the query, every time all features were selected or an ogr error
If I select the field from the joined table and I click on the "sample" button the value NULL isn't listed.

"field" != NULL returns all rows
"field" != 'NULL' returns all rows
"field" IS NOT 'NULL' return ogr syntax error
"field" IS NOT NULL returns all rows

What else..

If I save the volatile vector layer to shp then
"field" != NULL works

Tyhanks
Pietro

Also available in: Atom PDF