Bug #119994 Incorrect result with LEFT/RIGHT JOIN using functional index and boolean expression comparison

• Submitted: 6 Mar 8:20
• Modified: 9 Mar 8:17
• Reporter: Weipeng Wang
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• OS: Linux
• CPU Architecture: x86

[6 Mar 8:20] Weipeng Wang

Description:
A query using LEFT or RIGHT JOIN with a functional index on a boolean expression and a comparison in the WHERE clause can produce incorrect results.

This issue is similar to Bug #119991, but uses a functional index with IS NULL.

How to repeat:
CREATE TABLE IF NOT EXISTS t0(c0 INT) ;
CREATE TABLE t1 LIKE t0; 

CREATE INDEX i0 ON t0(((t0.c0) IS NULL));
 
INSERT INTO t1(c0) VALUES(1);

SELECT ALL t0.c0 AS ref0, t1.c0 AS ref1 FROM t0 RIGHT JOIN t1 ON (t0.c0) > (t1.c0) WHERE 2 > ((t0.c0) IS NULL);
/* Actual: empty set ; Expected 1 row: (NULL, 1) */

[9 Mar 8:17] Roy Lyseng

Thank you for the bug report.
However, I think this is the same underlying issue as bug#119991, so I am closing this as a duplicate report.