| Bug #109032 | Result set is incorrect. | ||
|---|---|---|---|
| Submitted: | 9 Nov 2022 3:18 | Modified: | 6 Dec 2022 1:20 |
| Reporter: | Xiong Wang | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 8.0.30, 8.0.31 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | regression | ||
[9 Nov 2022 8:04]
MySQL Verification Team
Hello Xiong Wang, Thank you for the report and feedback. regards, Umesh
[6 Dec 2022 1:20]
Jon Stephens
Documented fix as follows in the MySQL 8.0.33 changelog:
A view reference whose underlying field is a constant is not
marked as constant when the reference is part of an inner table
of an outer join. It was found that, when pushing a condition
down to a derived table, the reference was stripped off and only
the underlying field was cloned, which made it a constant, and
led to wrong results.
To fix this, we ensure that we do not push such a condition down
to the derived table by adding a check to see first whether the
table used by the condition matches the derived table or is a
constant expression; only when it is one or the other of these
do we push the condition down.
Closed.
Description: mysql> select version(); +--------------+ | version() | +--------------+ | 8.0.30-debug | +--------------+ 1 row in set (0.00 sec) mysql> select -> 1 -> from -> (select 1 where false) as subq_0 -> right join (select 1) as subq_1 -> right join (select 1 from (`t` `ref_1` left join (select 1 AS `c2`,a from `t` `ref_0`) `subq_0` on((`subq_0`.a = `ref_1`.`b`))) where (`subq_0`.`c2` is not null)) as ref_8 -> on (false) -> on (true); Empty set (0.00 sec) mysql> select -> 1 -> from -> (select 1 where false) as subq_0 -> right join (select 1) as subq_1 -> right join (select 1 from (`t` `ref_1` left join (select 1 AS `c2`,a from `t` `ref_0`) `subq_0` on((`subq_0`.a = `ref_1`.`b`))) where (`subq_0`.`c2` is not null)) as ref_8 -> on (false) -> on (true), -> (select -> 1 -> from -> (select 1 from t union all select 2 from t ) as ref_16 -> ) as subq_5; +---+ | 1 | +---+ | 1 | | 1 | +---+ 2 rows in set (0.00 sec) How to repeat: CREATE TABLE `t` ( `a` char(4) DEFAULT NULL, `b` char(4) DEFAULT NULL ); insert into t values(null, null); select 1 from (select 1 where false) as subq_0 right join (select 1) as subq_1 right join (select 1 from (`t` `ref_1` left join (select 1 AS `c2`,a from `t` `ref_0`) `subq_0` on((`subq_0`.a = `ref_1`.`b`))) where (`subq_0`.`c2` is not null)) as ref_8 on (false) on (true), (select 1 from (select 1 from t union all select 2 from t ) as ref_16 ) as subq_5;