MySQL Bugs: #114381: Incorrect query result may caused by EXISTS and TIMEDIFF

Bug #114381	Incorrect query result may caused by EXISTS and TIMEDIFF
Submitted:	17 Mar 2024 13:47	Modified:	18 Mar 2024 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

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;

Modify the Synopsis

Hello Ye Shiyang,

Thank you for the report and feedback

regards,
Umesh

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!