Description:
When running the mtr test suite tagged MySQL 8.0.33 on CentOS 7, the main.derived_condition_pushdown test failed with the error:
--- derived_condition_pushdown.result
+++ derived_condition_pushdown.reject
@@ -2114,12 +2114,7 @@
SET @b = '2023-05-06 16:49:45';
EXECUTE stmt USING @a, @b;
EXPLAIN
--> Table scan on dt (rows=2)
- -> Union materialize with deduplication (rows=2)
- -> Filter: (t1.f1 = 2) (rows=1)
- -> Table scan on t1 (rows=1)
- -> Filter: (t1.f1 = 0) (rows=1)
- -> Table scan on t1 (rows=1)
+-> Zero rows (no matching row in const table) (rows=0)
DROP TABLE t1;
#
mysqltest: Result content mismatch
It seems that the result of "Explain SELECT *
FROM (SELECT NOW() AS time FROM t1 WHERE f1 = ?
UNION
SELECT NOW() AS time FROM t1 WHERE f1 = 0) AS dt
WHERE time <= ?;" will not be "-> Table scan on dt (rows=2)
-> Union materialize with deduplication (rows=2)
-> Filter: (t1.f1 = 2) (rows=1)
-> Table scan on t1 (rows=1)
-> Filter: (t1.f1 = 0) (rows=1)
-> Table scan on t1 (rows=1)" when ? less than NOW()
How to repeat:
./mtr main.derived_condition_pushdown
Suggested fix:
set @b in derived_condition_pushdown.result/reject to a value far exceed the value of NOW()
Description: When running the mtr test suite tagged MySQL 8.0.33 on CentOS 7, the main.derived_condition_pushdown test failed with the error: --- derived_condition_pushdown.result +++ derived_condition_pushdown.reject @@ -2114,12 +2114,7 @@ SET @b = '2023-05-06 16:49:45'; EXECUTE stmt USING @a, @b; EXPLAIN --> Table scan on dt (rows=2) - -> Union materialize with deduplication (rows=2) - -> Filter: (t1.f1 = 2) (rows=1) - -> Table scan on t1 (rows=1) - -> Filter: (t1.f1 = 0) (rows=1) - -> Table scan on t1 (rows=1) +-> Zero rows (no matching row in const table) (rows=0) DROP TABLE t1; # mysqltest: Result content mismatch It seems that the result of "Explain SELECT * FROM (SELECT NOW() AS time FROM t1 WHERE f1 = ? UNION SELECT NOW() AS time FROM t1 WHERE f1 = 0) AS dt WHERE time <= ?;" will not be "-> Table scan on dt (rows=2) -> Union materialize with deduplication (rows=2) -> Filter: (t1.f1 = 2) (rows=1) -> Table scan on t1 (rows=1) -> Filter: (t1.f1 = 0) (rows=1) -> Table scan on t1 (rows=1)" when ? less than NOW() How to repeat: ./mtr main.derived_condition_pushdown Suggested fix: set @b in derived_condition_pushdown.result/reject to a value far exceed the value of NOW()