Bug #120106 Optimizer incorrectly handles constant derived table in RIGHT JOIN

• Submitted: 19 Mar 7:49
• Modified: 19 Mar 8:33
• Reporter: Guo Yuxiao
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 8.0.45
• OS: Any
• CPU Architecture: Any

[19 Mar 7:49] Guo Yuxiao

Description:
Hi, I found a logic bug in MySQL 9.6.0.
A query that should have returned {NULL, 1} actually returned an empty set.

How to repeat:
-- create table
DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` ( `c0` decimal(10,0) DEFAULT NULL );
INSERT INTO `t2` VALUES (1);

-- query, expect:{NULL, 1}, actual:empty set
SELECT
  *
FROM (
  SELECT
    t_lhs_0.c0 AS _col_0,
    t_rhs_0.c0 AS _col_1
  FROM (
    SELECT
      45 AS `c0`
    FROM `t2` AS `t3`
  ) AS t_lhs_0
  RIGHT JOIN `t2` AS t_rhs_0
    ON (
      t_lhs_0.c0 = t_rhs_0.c0
    )
) AS t_filter_sub_0
WHERE
  t_filter_sub_0._col_0 IS NULL;

[19 Mar 8:33] Roy Lyseng

Thank you for the bug report.
Verified as described.