Bug #114379 Incorrect query result caused by index and optimizer

• Submitted: 17 Mar 13:01
• Modified: 18 Mar 7:42
• Reporter: Ye Shiyang
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.27, 8.0.35, 8.0.21
• OS: Any
• CPU Architecture: Any

[17 Mar 13:01] Ye Shiyang

Description:
Dear devps, Our fuzzer may found a bug.

Consider the following statements, qeury1 and query2 should reutrn the same reuslt. However, qeury1 return 1 while query2 return nothing.

-- query1
SELECT t1.c0 AS _c0 FROM t1 LEFT OUTER JOIN t0 ON 1 & "" WHERE t0.c0 IS NOT TRUE; 
+------+
| _c0  |
+------+
|    1 |
+------+
1 row in set, 1 warning (0.00 sec)

-- query2
SELECT _c0 FROM (SELECT  t1.c0 AS _c0, (t0.c0 IS NOT TRUE) IS TRUE AS flag FROM t1 LEFT OUTER JOIN t0 ON 1 & "") as t WHERE flag=1;
Empty set, 1 warning (0.00 sec)

How to repeat:
CREATE TABLE t0(c0 FLOAT) ;
CREATE TABLE t1(c0 DECIMAL);
INSERT INTO t1(c0) VALUES(1);
CREATE INDEX i1 ON t0((t0.c0 IS NOT TRUE), ((t0.c0 IS NOT FALSE) & (NULL XOR t0.c0)));

SELECT t1.c0 AS _c0 FROM t1 LEFT OUTER JOIN t0 ON 1 & "" WHERE t0.c0 IS NOT TRUE;
SELECT _c0 FROM (SELECT  t1.c0 AS _c0, (t0.c0 IS NOT TRUE) IS TRUE AS flag FROM t1 LEFT OUTER JOIN t0 ON 1 & "") as t WHERE flag=1;

[17 Mar 13:02] Ye Shiyang

The bug could be reproduced from v8.0.27 to 8.0.35

[17 Mar 13:03] Ye Shiyang

Modify the Synopsis

[18 Mar 7:42] MySQL Verification Team

Hello Ye Shiyang,

Thank you for the report and feedback

regards,
Umesh

[20 Mar 11:22] shan he

I tried to simplify the repeat, it omitted the   '((t0.c0 IS NOT FALSE) & (NULL XOR t0.c0)))' and  ' & "" '

CREATE TABLE t0(c0 FLOAT) ;
CREATE TABLE t1(c0 DECIMAL);
INSERT INTO t1(c0) VALUES(1);
CREATE INDEX i1 ON t0((t0.c0 IS NOT TRUE));

SELECT t1.c0 AS _c0 FROM t1 LEFT OUTER JOIN t0 ON 1 WHERE t0.c0 IS NOT TRUE;

+------+
| _c0  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

SELECT _c0 FROM (SELECT  t1.c0 AS _c0, (t0.c0 IS NOT TRUE) IS TRUE AS flag FROM t1 LEFT OUTER JOIN t0 ON 1 ) as t WHERE flag=1;

Empty set (0.00 sec)

I kindly request the development team to examine this  simplified query to identify the root cause of the BUG and fix the  issue as soon as possible. By the way, please let me know if  this  simplified query is useful.I believe that by analyzing and fixing this simplified query, you can more easily locate the problem in the original  complex query.

Thank you for your hard work and support!