MySQL Bugs: #95900: Applying NOT twice on an integer results in wrong result in WHERE condition

Bug #95900	Applying NOT twice on an integer results in wrong result in WHERE condition
Submitted:	20 Jun 2019 15:31	Modified:	23 Jul 2019 11:04
Reporter:	Manuel Rigger	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.6/5.7	OS:	Ubuntu
Assigned to:		CPU Architecture:	x86

Description:
Negating an integer twice seems to result in the original integer value when used in the WHERE clause.

How to repeat:
CREATE TABLE t0(c0 INT);
INSERT INTO t0(c0) VALUES(1);
SELECT * FROM t0 WHERE 123 != (NOT (NOT 123)); -- expected: row is fetched, actual: row is not fetched

The right hand side of the comparison should evaluate to 1:

SELECT (NOT (NOT 123)); -- 1

However, it seems that when the expression is used in the WHERE clause it evaluates to 123:

SELECT * FROM t0 WHERE 123 = (NOT (NOT 123)); -- expected: row is not fetched, actual: row is fetched

When evaluating the expression after the SELECT statement rather than in the WHERE clause, the result is as expected:

SELECT 123 != (NOT (NOT 123)) FROM t0; -- 1

Thank you for the bug report. Testing against most recent source server, it's the result are you expected :
c:\dbs>c:\dbs\8.0\bin\mysql -uroot --port=3580 -p --prompt="mysql 8.0 > " --default-character-set=utf8mb4
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.17-debug Source distribution BUILT: 2019-MAY-17

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql 8.0 > CREATE DATABASE o;
Query OK, 1 row affected (0.01 sec)

mysql 8.0 > USE o;
Database changed
mysql 8.0 > CREATE TABLE t0(c0 INT);
Query OK, 0 rows affected (0.10 sec)

mysql 8.0 > INSERT INTO t0(c0) VALUES(1);
Query OK, 1 row affected (0.02 sec)

mysql 8.0 > SELECT * FROM t0 WHERE 123 != (NOT (NOT 123)); -- expected: row is fetched, actual: row is not fetched
+------+
| c0   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql 8.0 >

-------------------------------------------------------------------------
Current result with 8.0.16:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> USE TEST
Database changed
mysql> CREATE TABLE t0(c0 INT);
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO t0(c0) VALUES(1);
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM t0 WHERE 123 != (NOT (NOT 123)); -- expected: row is fetched, actual: row is not fetched
Empty set (0.01 sec)

mysql>

Thank you for the bug report. Version 5.6/5.7/8.0.16 affected but not anymore 8.0 source server built.

c:\dbs>c:\dbs\5.6\bin\mysql -uroot --local-infile --port=3560 -p --ssl --ssl-ca=c:\dbs\ssl\ca.pem --ssl-cert=c:\dbs\ssl\client-cert.pem --ssl-key=c:\dbs\ssl\client-key.pem --prompt="mysql 5.6 > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.45-log Source distribution BUILT: 2019-MAY-17

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql 5.6 > USE test
Database changed
mysql 5.6 > CREATE TABLE t0(c0 INT);
ERROR 1050 (42S01): Table 't0' already exists
mysql 5.6 > INSERT INTO t0(c0) VALUES(1);
Query OK, 1 row affected (0.01 sec)

mysql 5.6 > SELECT * FROM t0 WHERE 123 != (NOT (NOT 123)); -- expected: row is fetched, actual: row is not fetched
Empty set (0.00 sec)

mysql 5.6 >
-------------------------------------------------------------------------------------------------------------------------
c:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 -p  --prompt="mysql 5.7 > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.27-log Source distribution BUILT: 2019-MAY-17

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql 5.7 > USE test
Database changed
mysql 5.7 > CREATE TABLE t0(c0 INT);
Query OK, 0 rows affected (0.02 sec)

mysql 5.7 > INSERT INTO t0(c0) VALUES(1);
Query OK, 1 row affected (0.01 sec)

mysql 5.7 > SELECT * FROM t0 WHERE 123 != (NOT (NOT 123)); -- expected: row is fetched, actual: row is not fetched
Empty set (0.00 sec)

mysql 5.7 >
----------------------------------------------------------------------------------------------------------------------
Enter password: **********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> USE TEST
Database changed
mysql> CREATE TABLE t0(c0 INT);
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO t0(c0) VALUES(1);
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM t0 WHERE 123 != (NOT (NOT 123)); -- expected: row is fetched, actual: row is not fetched
Empty set (0.01 sec)

mysql>
--------------------------------------------------------------------------------------------------------------
c:\dbs>c:\dbs\8.0\bin\mysql -uroot --port=3580 -p --prompt="mysql 8.0 > " --default-character-set=utf8mb4
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.17 Source distribution BUILT: 2019-MAY-17

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql 8.0 > USE test
Database changed
mysql 8.0 > CREATE TABLE t0(c0 INT);
Query OK, 0 rows affected (0.04 sec)

mysql 8.0 > INSERT INTO t0(c0) VALUES(1);
Query OK, 1 row affected (0.01 sec)

mysql 8.0 > SELECT * FROM t0 WHERE 123 != (NOT (NOT 123)); -- expected: row is fetched, actual: row is not fetched
+------+
| c0   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql 8.0 >

This problem is fixed in version 8.0.17, due to WL#12358.

Thanks for fixing this! Since 8.0.17 is now public, I could verify that the bug has indeed been fixed.