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
Triage: Triaged: D2 (Serious)

[7 Apr 2010 10:25] Michael Kolesnikov
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
[7 Apr 2010 10:38] Peter Laursen
observation: 5.0.90 is not affected, but 5.5.2 is too.
[7 Apr 2010 10:43] Valeriy Kravchuk
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)
[7 Apr 2010 10:46] Valeriy Kravchuk
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 :)
[7 Apr 2013 8:13] Hartmut Holzgraefe
still reproducible in 5.6.10