Bug #114381 Incorrect query result may caused by EXISTS and TIMEDIFF
Submitted: 17 Mar 13:47 Modified: 18 Mar 7:10
Reporter: Ye Shiyang Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.19,8.0.35 OS:Any
Assigned to: CPU Architecture:Any

[17 Mar 13:47] 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
mysql> SELECT t1.c0 AS _c0 FROM t1, t0 WHERE (( EXISTS (SELECT 1 WHERE FALSE)) < ((NOT t0.c0))) IN (((t1.c0) XOR ("")) / (TIMEDIFF('3939-09-13 16:49:10.309835', '4722-09-08 23:55:52.675528')));
+------+
| _c0  |
+------+
|    1 |
+------+
1 row in set, 1 warning (0.00 sec)

--query2
mysql> SELECT _c0 FROM (SELECT t1.c0 AS _c0, ((( EXISTS (SELECT 1 WHERE FALSE)) < ((NOT t0.c0))) IN (((t1.c0) XOR ("")) / (TIMEDIFF('3939-09-13 16:49:10.309835', '4722-09-08 23:55:52.675528')))) IS TRUE AS flag FROM t1, t0) as t WHERE flag=1;
Empty set, 1 warning (0.00 sec)

How to repeat:
The bug can be reproduced from 8.0.19

CREATE TABLE t0(c0 DOUBLE) ;
CREATE TABLE t1 LIKE t0;
INSERT IGNORE INTO t1(c0) VALUES(1);
INSERT INTO t0(c0) VALUES(0.1);

SELECT t1.c0 AS _c0 FROM t1, t0 WHERE (( EXISTS (SELECT 1 WHERE FALSE)) < ((NOT t0.c0))) IN (((t1.c0) XOR ("")) / (TIMEDIFF('3939-09-13 16:49:10.309835', '4722-09-08 23:55:52.675528')));
SELECT _c0 FROM (SELECT t1.c0 AS _c0, ((( EXISTS (SELECT 1 WHERE FALSE)) < ((NOT t0.c0))) IN (((t1.c0) XOR ("")) / (TIMEDIFF('3939-09-13 16:49:10.309835', '4722-09-08 23:55:52.675528')))) IS TRUE AS flag FROM t1, t0) as t WHERE flag=1;
[17 Mar 13:48] Ye Shiyang
Modify the Synopsis
[18 Mar 7:10] MySQL Verification Team
Hello Ye Shiyang,

Thank you for the report and feedback

regards,
Umesh
[20 Mar 11:21] shan he
I tried to simplify the repeat, it omitted the    'WHERE FALSE'

CREATE TABLE t0(c0 DOUBLE) ;
CREATE TABLE t1 LIKE t0;
INSERT IGNORE INTO t1(c0) VALUES(1);
INSERT INTO t0(c0) VALUES(0.1);

SELECT t1.c0 AS _c0 FROM t1, t0 WHERE (( EXISTS (SELECT 1 )) < ((NOT t0.c0))) IN (((t1.c0) XOR ("")) / (TIMEDIFF('3939-09-13 16:49:10.309835', '4722-09-08 23:55:52.675528')));

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

SELECT _c0 FROM (SELECT t1.c0 AS _c0, ((( EXISTS (SELECT 1 )) < ((NOT t0.c0))) IN (((t1.c0) XOR ("")) / (TIMEDIFF('3939-09-13 16:49:10.309835', '4722-09-08 23:55:52.675528')))) IS TRUE AS flag FROM t1, t0) as t WHERE flag=1;

Empty set, 1 warning (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!