| Bug #118840 | a query including where (NOT XX NOT IN ()) returns a wrong nonempty result while (NOT XX NOT IN ()) returns 0. | ||
|---|---|---|---|
| Submitted: | 14 Aug 3:33 | Modified: | 19 Aug 7:49 |
| Reporter: | Alice Alice | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 8.0.41 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[18 Aug 11:14]
MySQL Verification Team
Hi, Why are you storing and comparing numbers as strings? You do understand that '50' is bigger than '300'? Please don't compare strings and numbers and you will have proper results: mysql> SELECT t0.c4,((t0.c4) NOT IN(LEAST(-1, 1761785550))),(NOT ((t0.c4) NOT IN(LEAST(-1, 1761785550)))) FROM t0 WHERE (not ((t0.c4) NOT IN(LEAST(-1, 1761785550)))); Empty set (0.001 sec) mysql> SELECT t0.c4,((t0.c4) NOT IN(LEAST(-1, 1761785550))),(NOT ((t0.c4) NOT IN(LEAST(-1, 1761785550)))) FROM t0 WHERE ( ((t0.c4) NOT IN(LEAST(-1, 1761785550)))); +------------------------+-----------------------------------------+-----------------------------------------------+ | c4 | ((t0.c4) NOT IN(LEAST(-1, 1761785550))) | (NOT ((t0.c4) NOT IN(LEAST(-1, 1761785550)))) | +------------------------+-----------------------------------------+-----------------------------------------------+ | 0000000000000000000000 | 1 | 0 | | 0000000000000000000005 | 1 | 0 | +------------------------+-----------------------------------------+-----------------------------------------------+ 2 rows in set (0.000 sec) mysql> select * from t0; +--------------+------+------+-----------+------------------------+ | c0 | c1 | c2 | c3 | c4 | +--------------+------+------+-----------+------------------------+ | 000000000000 | | NULL | 896771624 | 0000000000000000000000 | | 000000000001 | | 1 | 896771644 | 0000000000000000000005 | +--------------+------+------+-----------+------------------------+ 2 rows in set (0.000 sec) mysql>
[19 Aug 7:49]
Alice Alice
I insist this is a bug.
because the where subclause return 0 actually ,just same with the last part of the select fields.
and the query with "where 0" shall return an empty result.
please have a check again.
mysql> SELECT t0.c4,((t0.c4) NOT IN(LEAST('-1415217693', "1761785550"))),(NOT ((t0.c4) NOT IN(LEAST('-1415217693', "1761785550")))) FROM t0 WHERE (NOT ((t0.c4) NOT IN(LEAST('-1415217693', "1761785550"))));
+------------------------+------------------------------------------------------+------------------------------------------------------------+
| c4 | ((t0.c4) NOT IN(LEAST('-1415217693', "1761785550"))) | (NOT ((t0.c4) NOT IN(LEAST('-1415217693', "1761785550")))) |
+------------------------+------------------------------------------------------+------------------------------------------------------------+
| 0000000000000000000000 | 1 | 0 |
+------------------------+------------------------------------------------------+------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT t0.c4,((t0.c4) NOT IN(LEAST('-1415217693', "1761785550"))),(NOT ((t0.c4) NOT IN(LEAST('-1415217693', "1761785550")))) FROM t0 WHERE 0;
Empty set (0.00 sec)
Description: a query including where (NOT XX NOT IN ()) returns a wrong nonempty result while (NOT XX NOT IN ()) returns 0. mysql> SELECT t0.c4,((t0.c4) NOT IN(LEAST('-1415217693', "1761785550"))),(NOT ((t0.c4) NOT IN(LEAST('-1415217693', "1761785550")))) FROM t0 WHERE (NOT ((t0.c4) NOT IN(LEAST('-1415217693', "1761785550")))); +------------------------+------------------------------------------------------+------------------------------------------------------------+ | c4 | ((t0.c4) NOT IN(LEAST('-1415217693', "1761785550"))) | (NOT ((t0.c4) NOT IN(LEAST('-1415217693', "1761785550")))) | +------------------------+------------------------------------------------------+------------------------------------------------------------+ | 0000000000000000000000 | 1 | 0 | +------------------------+------------------------------------------------------+------------------------------------------------------------+ 1 row in set (0.00 sec) How to repeat: CREATE DATABASE /*!32312 IF NOT EXISTS*/ `tlp2025060615` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */; USE `tlp2025060615`; DROP TABLE IF EXISTS `t0`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `t0` ( `c0` float unsigned zerofill /*!50606 STORAGE MEMORY */ /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL, `c1` varchar(500) /*!50606 STORAGE MEMORY */ /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL COMMENT 'asdf', `c2` varchar(500) /*!50606 STORAGE MEMORY */ DEFAULT NULL COMMENT 'asdf', `c3` bigint /*!50606 STORAGE MEMORY */ DEFAULT NULL COMMENT 'asdf', `c4` double unsigned zerofill /*!50606 STORAGE MEMORY */ DEFAULT NULL COMMENT 'asdf', UNIQUE KEY `c3` (`c3`), UNIQUE KEY `i0` (`c4`,`c0`,`c1` DESC,`c2`(3)), UNIQUE KEY `i2` (`c0` DESC,`c1`(2),`c3` DESC,`c4` DESC,`c2`) USING BTREE, UNIQUE KEY `i3` (`c3`,`c1`(3),`c4` DESC,`c2`), KEY `i1` (`c0`) /*!80000 INVISIBLE */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; INSERT INTO `t0` VALUES (000000000000,'',NULL,896771624,0000000000000000000000); mysql> SELECT t0.c4,((t0.c4) NOT IN(LEAST('-1415217693', "1761785550"))),(NOT ((t0.c4) NOT IN(LEAST('-1415217693', "1761785550")))) FROM t0 WHERE (NOT ((t0.c4) NOT IN(LEAST('-1415217693', "1761785550")))); +------------------------+------------------------------------------------------+------------------------------------------------------------+ | c4 | ((t0.c4) NOT IN(LEAST('-1415217693', "1761785550"))) | (NOT ((t0.c4) NOT IN(LEAST('-1415217693', "1761785550")))) | +------------------------+------------------------------------------------------+------------------------------------------------------------+ | 0000000000000000000000 | 1 | 0 | +------------------------+------------------------------------------------------+------------------------------------------------------------+ 1 row in set (0.00 sec)