| Bug #118268 | The returned results of two queries with the same semantics are inconsistent. | ||
|---|---|---|---|
| Submitted: | 26 May 7:47 | Modified: | 27 May 3:30 |
| Reporter: | SamonBing SamonBing | Email Updates: | |
| Status: | Analyzing | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 8.0.41 | OS: | Any |
| Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
[27 May 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)
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)