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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.050 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.