Bug #114751 Incorrect query result

• Submitted: 24 Apr 2024 2:53
• Modified: 24 Apr 2024 7:24
• Reporter: John Jove
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 8.0.36
• OS: Any
• CPU Architecture: Any

[24 Apr 2024 2:53] John Jove

Description:
Run the following statements, in which the query returns an incorrect query result, since the WHERE condition is expected to be false.

How to repeat:
CREATE TABLE t1 (c1 int PRIMARY KEY);
CREATE TABLE t2 (c1 decimal UNIQUE);
CREATE TABLE t3 (c1 decimal PRIMARY KEY);
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (NULL);
INSERT INTO t2 VALUES (NULL);
INSERT INTO t2 VALUES (NULL);
INSERT INTO t2 VALUES (NULL);
INSERT INTO t2 VALUES (NULL);
INSERT INTO t2 VALUES (NULL);
INSERT INTO t3 VALUES (2);

SELECT /*+ SET_VAR(optimizer_switch='materialization=off') */ ca8 FROM (SELECT c1 AS ca8 FROM t2) AS ta1 WHERE ((NULL + ca8) != ANY (SELECT c1 FROM t3)) IN (SELECT c1 FROM t1) IS NOT UNKNOWN; -- {NULL, NULL, NULL, NULL, NULL, NULL}

[24 Apr 2024 7:24] MySQL Verification Team

Hello John,

Thank you for the report and test case.
Verified as described.

regards,
Umesh