Bug #119993 Incorrect result with LEFT/RIGHT JOIN when using functional index and boolean expression comparison
Submitted: 6 Mar 7:57 Modified: 9 Mar 8:14
Reporter: Weipeng Wang Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version: OS:Linux
Assigned to: CPU Architecture:x86

[6 Mar 7:57] Weipeng Wang 
Description:
A RIGHT or LEFT JOIN query involving a functional index on a boolean expression combined with a comparison in the WHERE clause can produce incorrect results. 

This issue is similar to Bug #119991.

How to repeat:
CREATE TABLE t0(c0 INT) ;
CREATE TABLE t1 LIKE t0;
INSERT INTO t0(c0) VALUES(1);
CREATE INDEX i0 ON t1((((t1.c0) IS TRUE) IS FALSE));
SELECT t1.c0 AS ref0, t0.c0 AS ref1 FROM t1 RIGHT JOIN t0 ON TRUE WHERE (t1.c0 IS NOT TRUE) < 0;    
/* Actually returns 1 row: (NULL, 1) */
/* Except returns empty row */
[9 Mar 8:14] 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.