| Bug #118831 | !(expression1 or expression2) return a different result from !(expression1), while expression2 is 0. | ||
|---|---|---|---|
| Submitted: | 14 Aug 2:33 | Modified: | 19 Aug 8:36 |
| Reporter: | Alice Alice | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.41, 9.4.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[19 Aug 8:36]
MySQL Verification Team
Thank you for the report. Verified as described.
mysql> select * from t1;
+----------+------+------------+------+------+
| c0 | c1 | c2 | c3 | c4 |
+----------+------+------------+------+------+
| 0.164443 | NULL | 0000000000 | NULL | NULL |
| 0.239807 | NULL | 0000000000 | NULL | NULL |
| 0.218501 | NULL | 0000000000 | NULL | NULL |
+----------+------+------------+------+------+
3 rows in set (0.000 sec)
mysql> SELECT ALL t1.c0 AS ref0, t1.c1 AS ref1, t1.c2 AS ref2, t1.c3 AS ref3, t1.c4 AS ref4 FROM t1 WHERE ! ((GREATEST("", t1.c0, t1.c0, t1.c2)) OR (t1.c2));
Empty set, 1 warning (0.001 sec)
mysql> SELECT * FROM t1 WHERE ! ((GREATEST("", t1.c0, t1.c0, t1.c2)) OR (t1.c2));
Empty set, 1 warning (0.001 sec)
mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------------+
| Warning | 1287 | '!' is deprecated and will be removed in a future release. Please use NOT instead |
+---------+------+-----------------------------------------------------------------------------------+
1 row in set (0.000 sec)
mysql> SELECT * FROM t1 WHERE ! ((GREATEST("", t1.c0, t1.c0, t1.c2)) );
+----------+------+------------+------+------+
| c0 | c1 | c2 | c3 | c4 |
+----------+------+------------+------+------+
| 0.164443 | NULL | 0000000000 | NULL | NULL |
| 0.239807 | NULL | 0000000000 | NULL | NULL |
| 0.218501 | NULL | 0000000000 | NULL | NULL |
+----------+------+------------+------+------+
3 rows in set, 1 warning (0.001 sec)
mysql> select ! ((GREATEST("", t1.c0, t1.c0, t1.c2)) OR (t1.c2)), ! ((GREATEST("", t1.c0, t1.c0, t1.c2)) ) from t1;
+----------------------------------------------------+------------------------------------------+
| ! ((GREATEST("", t1.c0, t1.c0, t1.c2)) OR (t1.c2)) | ! ((GREATEST("", t1.c0, t1.c0, t1.c2)) ) |
+----------------------------------------------------+------------------------------------------+
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
+----------------------------------------------------+------------------------------------------+
3 rows in set, 2 warnings (0.000 sec)
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 9.4.0 |
+-----------+
1 row in set (0.000 sec)
mysql>
Description: the first query includes a where subclause !(expression1 or expression2). the second query includes a where subclause !(expression1). The returned results of two queries with the same semantics are inconsistent while expression2 is 0. How to repeat: CREATE DATABASE /*!32312 IF NOT EXISTS*/ `tlp2025052611`; USE `tlp2025052611`; CREATE TABLE `t1` ( `c0` float /*!50606 STORAGE MEMORY */ DEFAULT NULL, `c1` float /*!50606 STORAGE MEMORY */ /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL, `c2` int(10) unsigned zerofill DEFAULT NULL, `c3` decimal(10,0) /*!50606 STORAGE MEMORY */ DEFAULT NULL COMMENT 'asdf', `c4` decimal(10,0) DEFAULT NULL, UNIQUE KEY `c0` (`c0`), UNIQUE KEY `c1` (`c1`), KEY `i0` (`c3`,`c0`,`c4` DESC) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; INSERT INTO `t1` VALUES (0.164443,NULL,0000000000,NULL,NULL),(0.239807,NULL,0000000000,NULL,NULL),(0.218501,NULL,0000000000,NULL,NULL); first query(with wrong result) mysql> SELECT ALL t1.c0 AS ref0, t1.c1 AS ref1, t1.c2 AS ref2, t1.c3 AS ref3, t1.c4 AS ref4 FROM t1 WHERE ! ((GREATEST("", t1.c0, t1.c0, t1.c2)) OR (t1.c2)); Empty set, 1 warning (0.00 sec) second query(with right result) mysql> SELECT ALL t1.c0 AS ref0, t1.c1 AS ref1, t1.c2 AS ref2, t1.c3 AS ref3, t1.c4 AS ref4 FROM t1 WHERE ! ((GREATEST("", t1.c0, t1.c0, t1.c2)) ); +----------+------+------------+------+------+ | ref0 | ref1 | ref2 | ref3 | ref4 | +----------+------+------------+------+------+ | 0.164443 | NULL | 0000000000 | NULL | NULL | | 0.239807 | NULL | 0000000000 | NULL | NULL | | 0.218501 | NULL | 0000000000 | NULL | NULL | +----------+------+------------+------+------+ 3 rows in set, 1 warning (0.00 sec)