Bug report #4098

Problems with "Advanced Search" query results

Added by Alister Hood about 9 years ago. Updated about 9 years ago.

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

Description

I expected these two queries to give me the same results:
not DESCRIPT = '1'
DESCRIPT != '1'
But the second query gave me the results of the first query minus any items with NULL values in the DESCRIPT column.
Is this a bug, or is this a standard "feature" of SQL?
I guess the good thing is that I could use this to select rows with NULL values in a numeric column ;) (I can't find any other way to do that - see below)

Similarly, I expected these two queries to both select the rows with NULL values:
DESCRIPT ILIKE NULL
DESCRIPT = NULL
But actually only the first query does, and since ILIKE is not allowed on a numeric column we have the problem mentioned above.

Also, if DESCRIPT is a numeric column, why does this query select all the rows where DESCRIPT = 0?:
DESCRIPT='hello'
Wouldn't it make more sense to have an error message stating that you can't search for a string in a numeric column?

History

#1 Updated by Jürgen Fischer about 9 years ago

all operations on NULL values - except IS NULL and IS NOT NULL - yield NULL results. That's "normal" SQL.

Trying to convert non-numeric strings to number produces a 0, hence the outcome of your query. Letting that produce an error message seems right to me too.

#2 Updated by Alister Hood about 9 years ago

Alister Hood wrote:

I guess the good thing is that I could use this to select rows with NULL values in a numeric column ;) (I can't find any other way to do that - see below)

Ah, thanks:
DESCRIPT IS NULL
I thought I tried that, but I must have been confused.

#3 Updated by Jürgen Fischer about 9 years ago

  • Status changed from Open to Closed

conversion error reporting included in 6c26773f9bd689bfe3002c4cdb349301419a8daa

Also available in: Atom PDF