Bug #71032 !NULL and NOT NULL
Submitted: 28 Nov 2013 12:18 Modified: 28 Nov 2013 14:52
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:any OS:Any
Assigned to: CPU Architecture:Any

[28 Nov 2013 12:18] Peter Laursen
Description:
This is one more 'spin-off' from dicussion in 
http://bugs.mysql.com/bug.php?id=67732

http://dev.mysql.com/doc/refman/5.6/en/operator-precedence.html

"Operator precedences are shown in the following list, from highest precedence to the lowest.
..
!
..
IS
..
NOT
..
"

1: SELECT NOT NULL; -- returns NULL
2: SELECT (NOT NULL); -- returns NULL
3: SELECT NOT NULL IS NULL; -- returns "0" (false)
4: SELECT (NOT NULL) IS NULL; -- returns "1" (true)

5: SELECT !NULL; -- returns NULL
6: SELECT (!NULL); -- returns syntax error
7: SELECT !NULL IS NULL; -- returns "1" (true)
8: SELECT (!NULL) IS NULL; -- returns "1" (true)

I don't understand why (6:) fails when (5:) and (8:) don't. 

How to repeat:
1: SELECT NOT NULL; -- returns NULL
2: SELECT (NOT NULL); -- returns NULL
3: SELECT NOT NULL IS NULL; -- returns "0" (false)
4: SELECT (NOT NULL) IS NULL; -- returns "1" (true)

5: SELECT !NULL; -- returns NULL
6: SELECT (!NULL); -- returns syntax error
7: SELECT !NULL IS NULL; -- returns "1" (true)
8: SELECT (!NULL) IS NULL; -- returns "1" (true)

I don't understand why (6:) fails when (5:) and (8:) don't. The docs quote does not explain this (but it does explain the difference between (3:) and (7:))

Suggested fix:
No idea! It may have little or no practical importance. And there may be an explanation. I am not sure at all it is a bug - but then a 'gotcha' at least.

I am aware that the manual nowhere recommends the use of !NULL (not as far as I know, at least).

In any case http://dev.mysql.com/doc/refman/5.6/en/working-with-null.html could detail why !NULL and NOT NULL is not the same.
[28 Nov 2013 14:42] Valeriy Kravchuk
Can you be more specific about version(s)? Look:

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

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

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.33    |
+-----------+
1 row in set (0.03 sec)
[28 Nov 2013 14:52] Peter Laursen
I can't reproduce the syntax error now with 5.6.14.

Probaly some other bad syntax. I will close this.