| Bug #114380 | Incorrect query result may caused by TIMESTAMP | ||
|---|---|---|---|
| Submitted: | 17 Mar 13:19 | Modified: | 18 Mar 7:38 |
| Reporter: | Ye Shiyang | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 8.0.13, 8.0.35, 8.0.36 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[18 Mar 7:38]
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(c1 FLOAT) ;
INSERT INTO t0(c1) VALUES(0.1);
SELECT f1 FROM (SELECT (LEAST('-2', EXISTS (SELECT 1 ), CAST(t0.c1 AS SIGNED), TIMESTAMP('2024-03-17'))) AS f1 FROM t0) as t WHERE f1;
+------+
| f1 |
+------+
| -2 |
+------+
1 row in set, 3 warnings (0.00 sec)
SELECT f1 FROM (SELECT (LEAST('-2', EXISTS (SELECT 1 ), CAST(t0.c1 AS SIGNED), TIMESTAMP('2024-03-17'))) AS f1, (LEAST('-2', EXISTS (SELECT 1 ), CAST(t0.c1 AS SIGNED), TIMESTAMP('2024-03-17'))) IS TRUE AS flag FROM t0) as t WHERE flag=1;
Empty set, 6 warnings (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!
Description: Dear devps, Our fuzzer may found a bug. Consider the following statements, qeury1 and query2 should reutrn the same reuslt. However, qeury1 return -2 while query2 return nothing. -- query1 mysql> SELECT f1 FROM (SELECT (LEAST('-2', EXISTS (SELECT 1 wHERE FALSE), CAST(t0.c1 AS SIGNED), TIMESTAMP('2024-03-17'))) AS f1 FROM t0) as t WHERE f1; +------+ | f1 | +------+ | -2 | +------+ 1 row in set, 5 warnings (0.00 sec) --query2 SELECT f1 FROM (SELECT (LEAST('-2', EXISTS (SELECT 1 WHERE FALSE), CAST(t0.c1 AS SIGNED), TIMESTAMP('2024-03-17'))) AS f1, (LEAST('-2', EXISTS (SELECT 1 WHERE FALSE), CAST(t0.c1 AS SIGNED), TIMESTAMP('2024-03-17'))) IS TRUE AS flag FROM t0) as t WHERE flag=1; How to repeat: It could be reproduced from 8.0.13 CREATE TABLE t0(c1 FLOAT) ; INSERT INTO t0(c1) VALUES(0.1); SELECT f1 FROM (SELECT (LEAST('-2', EXISTS (SELECT 1 WHERE FALSE), CAST(t0.c1 AS SIGNED), TIMESTAMP('2024-03-17'))) AS f1 FROM t0) as t WHERE f1; SELECT f1 FROM (SELECT (LEAST('-2', EXISTS (SELECT 1 WHERE FALSE), CAST(t0.c1 AS SIGNED), TIMESTAMP('2024-03-17'))) AS f1, (LEAST('-2', EXISTS (SELECT 1 WHERE FALSE), CAST(t0.c1 AS SIGNED), TIMESTAMP('2024-03-17'))) IS TRUE AS flag FROM t0) as t WHERE flag=1;