Bug #120094 Query returns incorrect results due to incorrect handling of predicate logic

• Submitted: 18 Mar 8:29
• Modified: 18 Mar 9:35
• Reporter: Guo Yuxiao
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 8.0.45
• OS: Any
• CPU Architecture: Any

[18 Mar 8:29] Guo Yuxiao

Description:
Hi, I found a logic bug in MySQL 9.6.0.

Here is the PoC:

-- query1, expect result:{NULL}, actual result:{NULL}
SELECT
  *
FROM (
  SELECT
    t3.c2 AS _col_21
  FROM (
    SELECT
      22 AS c2
    FROM t0 AS t5
  ) AS t3
  RIGHT JOIN t2 AS t4 ON FALSE
) AS t2;

-- query2, expect result:{NULL}, actual result:{}
SELECT
  *
FROM (
  SELECT
    t3.c2 AS _col_21
  FROM (
    SELECT
      22 AS c2
    FROM t0 AS t5
  ) AS t3
  RIGHT JOIN t2 AS t4 ON FALSE
) AS t2
WHERE
  -(
      CAST(FALSE AS DECIMAL(65, 30)) + CAST(t2._col_21 AS DECIMAL(65, 30))
  ) IS NULL;

How to repeat:
-- create table
DROP TABLE IF EXISTS t0;
CREATE TABLE t0 ( 
    c1 int DEFAULT NULL COMMENT 'asdf', 
    UNIQUE KEY c1 (c1), 
    KEY i15 (c1) USING BTREE 
) CHARSET=utf8mb4;
INSERT INTO t0 VALUES (1);

DROP TABLE IF EXISTS t2;
CREATE TABLE t2 ( 
    c1 int DEFAULT NULL COMMENT 'asdf', 
    UNIQUE KEY c1 (c1) 
) CHARSET=utf8mb4;
INSERT INTO t2 VALUES (NULL);

-- query, expect result:{NULL}, actual result:{}
SELECT
  *
FROM (
  SELECT
    t3.c2 AS _col_21
  FROM (
    SELECT
      22 AS c2
    FROM t0 AS t5
  ) AS t3
  RIGHT JOIN t2 AS t4 ON FALSE
) AS t2
WHERE
  -(
      CAST(FALSE AS DECIMAL(65, 30)) + CAST(t2._col_21 AS DECIMAL(65, 30))
  ) IS NULL;

[18 Mar 9:35] Roy Lyseng

Thank you for the bug report.
Verified as described.