MySQL Bugs: #52650: select !!0 = 1 returns 1

Bug #52650	select !!0 = 1 returns 1
Submitted:	7 Apr 2010 10:25	Modified:	7 Apr 2010 10:43
Reporter:	Michael Kolesnikov	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.1.45, 5.1.47-bzr	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
The statement "select !!0 = 1" returns true but if add space between "!" it works fine and returns false as expected

How to repeat:
"select !!0 = 1" returns 1
"select ! !0 = 1" returns 0
"select !(!0) = 1" returns 0

observation: 5.0.90 is not affected, but 5.5.2 is too.

This is easy to verify:

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

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.1.47-debug |
+--------------+
1 row in set (0.00 sec)

Note that NOT NOT both provides a workaround.

Here is the bug in a clear form:

mysql> explain extended select !0, !!0;
+----+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select (not(0)) AS `!0`,(not(0)) AS `!!0`
1 row in set (0.00 sec)

Yes, 5.0.x gives correct results:

77-52-28-202:5.0 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.91-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

So this is a regression, but I doubt that it interprets !!0 properly:

mysql> explain extended select !0, !!0;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used | 
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select (not(0)) AS `!0`,(0 <> 0) AS `!!0`
1 row in set (0.00 sec)

It just happens to produce expected result :)

still reproducible in 5.6.10