get_recordset_select ... NULL = -1 ?

get_recordset_select ... NULL = -1 ?

by Nicolas JOURDAIN -
Number of replies: 1

Hi there,

In my code, I query a table 'MYTABLE' and filter on the column 'STATUS <> -1'

$DB->get_recordset_select('mytable', 'status <> ?', array(-1));

I was very surprised that none of the records from the database where STATUS = NULL are returned.

Note: Definition on the field 'status'

install.xml : ...field name="status" type="int" length="1" notnull="false" sequence="false" ...

Question: Does it mean NULL = -1 in MySQL?

Please help me to understand this behaviour? is it correct or wrong?

Thanks.

Average of ratings: Useful (1)
In reply to Nicolas JOURDAIN

Re: get_recordset_select ... NULL = -1 ?

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

http://sqlfiddle.com/#!9/9eecb/36398/0

NULL <> -1 

evaluates to NULL, which is not true. This is in line with the SQL standard. NULL is not a value. A reasonably interpretation to help develop you intuition is that NULL means 'unknown value'. "Is an unknown different from -1?", well that is also unknown.

To get the logic you want, you would need to say (status <> -1 OR status IS NULL)

Average of ratings: Useful (1)