| Bug #118268 | The returned results of two queries with the same semantics are inconsistent. | ||
|---|---|---|---|
| Submitted: | 26 May 2025 7:47 | Modified: | 6 Feb 13:22 |
| Reporter: | SamonBing SamonBing | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.41 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[27 May 2025 3:30]
SamonBing SamonBing
reduce query scene:
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)
[6 Feb 13:22]
Roy Lyseng
Thank you for the bug report. Verified as described.
Description: The returned results of two queries with the same semantics are inconsistent. 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); #the first query SELECT ALL t1.c0 AS ref0, t1.c1 AS ref1, t1.c2 AS ref2, t1.c3 AS ref3, t1.c4 AS ref4 FROM t1; 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; +----------+------+------------+------+------+ | 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 (0.00 sec) #the second query 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) UNION ALL 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))) UNION ALL 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)) IS UNKNOWN; 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) -> UNION ALL 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))) -> UNION ALL 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)) IS UNKNOWN; Empty set, 1 warning (0.00 sec)