| Bug #114383 | Incorrect query result may caused by DATE and window function | ||
|---|---|---|---|
| Submitted: | 17 Mar 2024 14:11 | Modified: | 18 Mar 2024 7:01 |
| Reporter: | Ye Shiyang | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S2 (Serious) |
| Version: | 8.0.23,8.0.35 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[18 Mar 2024 7:01]
MySQL Verification Team
Hello Ye Shiyang, Thank you for the report and feedback regards, Umesh
[20 Mar 2024 11:19]
shan he
I tried to simplify the repeat, it omitted the " 'OVER (PARTITION BY t0.c0)' " part.
CREATE TABLE IF NOT EXISTS t0(c0 INT) ;
REPLACE DELAYED INTO t0(c0) VALUES(-1);
SELECT ALL f1 FROM (SELECT ALL (MAX(DATE('2024-01-01 ')) ) AS f1 FROM t0) as t WHERE (f1) > (1.105003755E9);
+------------+
| f1 |
+------------+
| 2024-01-01 |
+------------+
1 row in set, 2 warnings (0.00 sec)
SELECT f1 FROM (SELECT MAX(DATE('2024-01-01 ') ) AS f1, (MAX(DATE('2024-01-01 ') ) > (1.105003755E9)) IS TRUE AS flag FROM t0) as t WHERE flag=1;
Empty set, 2 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 1 while query2 return nothing. -- query1 mysql> SELECT ALL f1 FROM (SELECT ALL (MAX(DATE('2024-01-01 ')) OVER (PARTITION BY t0.c0)) AS f1 FROM t0) as t WHERE (f1) > (1.105003755E9); +------------+ | f1 | +------------+ | 2024-01-01 | +------------+ 1 row in set, 2 warnings (0.00 sec) --query2 mysql> SELECT f1 FROM (SELECT (MAX(DATE('2024-01-01 ')) OVER (PARTITION BY t0.c0)) AS f1, ((MAX(DATE('2024-01-01 ')) OVER (PARTITION BY t0.c0)) > (1.105003755E9)) IS TRUE AS flag FROM t0) as t WHERE flag=1; Empty set, 2 warnings (0.00 sec) How to repeat: The bug can be reproduced from 8.0.23 CREATE TABLE IF NOT EXISTS t0(c0 INT) ; REPLACE DELAYED INTO t0(c0) VALUES(-1); SELECT ALL f1 FROM (SELECT ALL (MAX(DATE('2024-01-01 ')) OVER (PARTITION BY t0.c0)) AS f1 FROM t0) as t WHERE (f1) > (1.105003755E9); SELECT f1 FROM (SELECT (MAX(DATE('2024-01-01 ')) OVER (PARTITION BY t0.c0)) AS f1, ((MAX(DATE('2024-01-01 ')) OVER (PARTITION BY t0.c0)) > (1.105003755E9)) IS TRUE AS flag FROM t0) as t WHERE flag=1;