MySQL Bugs: #15663: 'NULL != 10' is always FALSE

Bug #15663	'NULL != 10' is always FALSE
Submitted:	11 Dec 2005 8:12	Modified:	12 Dec 2005 14:37
Reporter:	Alexander Novitsky	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	4	OS:	Any (Any)
Assigned to:		CPU Architecture:	Any

Description:
When I use comparison like 'NULL != 10' (10 is taken as an example, any non-NULL value could be here), it returns NULL as a result.

NULL is treated as FALSE in MySQL, so this comparison is always FALSE, what mean that NULL is equal to anything!

How to repeat:
select if(NULL != 10, 'NULL is not equal to 10', 'NULL is equal to 10');

will give an answer 'NULL is equal to 10'

Suggested fix:
Comparison where operator != 'not equal' is used and at least one of compared values is NULL should return 1, or TRUE, or something that could be treated as TRUE, but definetely not NULL.

Alexander, according to sql specs, any comparision or ariphmetic operations with NULL _must_ return NULL (only valid operation with NULL  is  'somevalue is NULL'  operation)

Yes, from the point of view of standarts I agree with you.

But this stupid following of standarts leads to strange behaviour.

This means that NULL value is always equal to anything (if I use NULL != anything), and is more then anything (if NULL <= anything), and is less then anything (if NULL >= anything) and so on.

There could be 2 solutions for this:
1. Raise some kind of warning or error when comrare some value with NULL
2. Use 3-value logic instead of 2-value (right now MySQL do the first step in 3-value logic usage: it returns NULL for any operation when NULL is one of values involved; but the next step is absolutely stupid: it treats NULL as FALSE. NULL should be 3-rd value in logic in addition to TRUE and FALSE)

---
WBR,
Alexander Novitsky

In that case would you mind if I change severity to 'feature request'?

>  [12 Dec 13:28] Aleksey Kishkin

> In that case would you mind if I change severity to 'feature request'?

Yes, I think this make sense, I'm doing it

I do not agree with this "feature request". NULL should be treated as it is in MySQL now. 3-valued logic does not (usually) mean that you can compare with NULL and get anything similar to TRUE. Please, check Oracle's documentation, for example. 

'NULL != 10' is always NULL (not FALSE), but we should select only those rows that have WHERE clause evaluated to TRUE!

Valeriy,

Did you try my example? 

I may repeat it here:
>>>>
select if(NULL != 10, 'NULL is not equal to 10', 'NULL is equal to 10');

will give an answer 'NULL is equal to 10'
<<<<
This means that the result of 'NULL != 10' is treated as FALSE!

The result you got is clearly described in the manual (http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html):

"IF(expr1,expr2,expr3)

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3."

That is what you got:

mysql> select if(NULL != 10, 'NULL is not equal to 10', 'NULL is equal to 10') c1;
+---------------------+
| c1                  |
+---------------------+
| NULL is equal to 10 |
+---------------------+
1 row in set (0,00 sec)

You got "otherwise", not TRUE. It is a right treatment of NULL value, and it will never be changed, I believe. Even if you provide a more convincing phrase as expr3...

Then it's not a bug in MySQL server itself, it's a bug in SQL standart?

Look at it again and THINK on it : if it's not true that 'NULL is not equal to 10' then NULL is equal to 10 - that's what we get from it!