Bug #60931 Comparaison on column with some NULL values

• Submitted: 20 Apr 2011 8:13
• Modified: 20 Apr 2011 11:17
• Reporter: Stéphane Lambert
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• OS: Linux
• CPU Architecture: Any

[20 Apr 2011 8:13] Stéphane Lambert

Description:
When filtering a column inside a SQL statement (WHERE) , data are not selected if their value is NULL for this column.

How to repeat:
CREATE TABLE IF NOT EXISTS `testNull` (
  `idTestNull` int(11) NOT NULL AUTO_INCREMENT,
  `truc` int(11) DEFAULT NULL,
  PRIMARY KEY (`idTestNull`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `testNull` (`idTestNull`, `truc`) VALUES
(1, 2),
(2, NULL);

********************

SELECT * FROM `testNull` WHERE `truc` !=2 
=> returns nothing, when we should have the line 2

To have to expected behaviour, we need to use 
SELECT * FROM `testNull` WHERE (`truc` !=2 OR truc IS NULL )

This is huge for our old application. We think this behaviour changed one day and was not like this since the beginning of the MySql engine.

Suggested fix:
SELECT * FROM `testNull` WHERE `truc` !=2  should retrieve lines where 'truc' is NULL. Even if the value is not affected, the condition !=2 means something explicit enough to expect a coherent result.

[20 Apr 2011 10:51] Valeriy Kravchuk

Sorry, but this is NOT a bug. NULL means that the value is unknown, so we do not know if it equals to 2 or not.

Look:

mysql> select NULL = 2;
+----------+
| NULL = 2 |
+----------+
|     NULL |
+----------+
1 row in set (0.08 sec)

mysql> select NULL != 2;
+-----------+
| NULL != 2 |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)

You should better use NULL-safe comparison operator (http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html#operator_equal-to):

mysql> select NULL <=> 2;
+------------+
| NULL <=> 2 |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

mysql> select NOT(NULL <=> 2);
+-----------------+
| NOT(NULL <=> 2) |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)

[20 Apr 2011 11:17] Stéphane Lambert

Hmm... This is quite unexpected. For me, null was a way to indicate the var was declared but nothing was affected, like in a programming language. So it has no value, and it is different than '' which is a value. [I hope I am clear :) ]

But what is sure is NULL is not 2 for our software. This is quite pro0blematic for our development. Are you sure this behaviour was always like that in the mysql history, since the beginning?

In that case, how can I retrieve my lines with my test, if the NULL values are always wrong with any comparaison?