Bug #109032 Result set is incorrect.

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;

Hello Xiong Wang,

Thank you for the report and feedback.

regards,
Umesh

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.