| Bug #112460 | Inconsistent results of SELECT statement with window functions | ||
|---|---|---|---|
| Submitted: | 26 Sep 2023 7:22 | Modified: | 3 Jan 2024 16:18 |
| Reporter: | Zuming Jiang | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 8.0.33, 8.0.34 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | regression | ||
[26 Sep 2023 8:55]
MySQL Verification Team
Hello Zuming Jiang, Thank you for the report and test case. regards, Umesh
[26 Sep 2023 8:57]
MySQL Verification Team
- 8.0.33/8.0.34 ./mtr --nocheck-testcases bug112460 Logging: ./mtr --nocheck-testcases bug112460 MySQL Version 8.0.34 Checking supported features Using 'all' suites Collecting tests Checking leftover processes Removing old var directory Creating var directory '/export/home/tmp/ushastry/mysql-8.0.34/mysql-test/var' Installing system database Using parallel: 1 ============================================================================== TEST NAME RESULT TIME (ms) COMMENT ------------------------------------------------------------------------------ create table t2 (vkey int, pkey int, c19 int, c20 double, c22 int); insert into t2 values (135, 145000, -13, 17.61, 0); select case when true then null else (DENSE_RANK() over (partition by ref_0.pkey order by ref_0.vkey desc, ref_0.pkey asc)) end as c_0, case when ((LAST_VALUE(ref_0.pkey) over (partition by ref_0.c19 order by ref_0.vkey desc, ref_0.pkey desc)) in (select ref_0.c22 as c_0 from t2 as ref_8 )) then ref_0.c20 else ref_0.c20 end as c_1 from t2 as ref_0 order by c_1 desc; c_0 c_1 NULL NULL select case when true then null else (DENSE_RANK() over (partition by ref_0.pkey order by ref_0.vkey desc, ref_0.pkey asc)) end as c_0, ref_0.c20 as c_1 from t2 as ref_0 order by c_1 desc; c_0 c_1 NULL 17.61 - 8.0.32 ./mtr --nocheck-testcases bug112460 Logging: ./mtr --nocheck-testcases bug112460 MySQL Version 8.0.32 Checking supported features Using 'all' suites Collecting tests Checking leftover processes Removing old var directory - WARNING: Using the 'mysql-test/var' symlink Creating var directory '/export/home/tmp/ushastry/mysql-8.0.32/mysql-test/var' Installing system database Using parallel: 1 ============================================================================== TEST NAME RESULT TIME (ms) COMMENT ------------------------------------------------------------------------------ create table t2 (vkey int, pkey int, c19 int, c20 double, c22 int); insert into t2 values (135, 145000, -13, 17.61, 0); select case when true then null else (DENSE_RANK() over (partition by ref_0.pkey order by ref_0.vkey desc, ref_0.pkey asc)) end as c_0, case when ((LAST_VALUE(ref_0.pkey) over (partition by ref_0.c19 order by ref_0.vkey desc, ref_0.pkey desc)) in (select ref_0.c22 as c_0 from t2 as ref_8 )) then ref_0.c20 else ref_0.c20 end as c_1 from t2 as ref_0 order by c_1 desc; c_0 c_1 NULL 17.61 select case when true then null else (DENSE_RANK() over (partition by ref_0.pkey order by ref_0.vkey desc, ref_0.pkey asc)) end as c_0, ref_0.c20 as c_1 from t2 as ref_0 order by c_1 desc; c_0 c_1 NULL 17.61
[13 Dec 2023 11:25]
shan he
Could you please confirm whether the simplification process we applied has been helpful for developers in diagnosing and addressing this issue?
Here's our output
******
create table t2 (vkey int, pkey int, c19 int, c20 double, c22 int);
insert into t2 values (135, 145000, -13, 17.61, 0);
select
(DENSE_RANK() over ()) as c_0,
case when ((LAST_VALUE(ref_0.pkey) over ( )) in (
select
ref_0.c22
from
t2
)) then ref_0.c20 else ref_0.c20 end
as c_1
from
t2 as ref_0
order by c_1 desc;
+-----+------+
| c_0 | c_1 |
+-----+------+
| 1 | NULL |
+-----+------+
select
(DENSE_RANK() over ()) as c_0,
ref_0.c20 as c_1
from
t2 as ref_0
order by c_1 desc;
+-----+-------+
| c_0 | c_1 |
+-----+-------+
| 1 | 17.61 |
+-----+-------+
[3 Jan 2024 16:18]
Jon Stephens
Fixed in MySQL 8.0.36 and 8.3.0 by BUG#35710179, see same for changelog entry. Closed.
Description: Dear MySQL developers, I used my new fuzzer to fuzz MySQL and found a logic bug that make MySQL server output inconsistent results. How to repeat: *** Set up the database *** create table t2 (vkey int, pkey int, c19 int, c20 double, c22 int); insert into t2 values (135, 145000, -13, 17.61, 0); *** Test Case 1 *** select case when true then null else (DENSE_RANK() over (partition by ref_0.pkey order by ref_0.vkey desc, ref_0.pkey asc)) end as c_0, case when ((LAST_VALUE(ref_0.pkey) over (partition by ref_0.c19 order by ref_0.vkey desc, ref_0.pkey desc)) in (select ref_0.c22 as c_0 from t2 as ref_8 )) then ref_0.c20 else ref_0.c20 end as c_1 from t2 as ref_0 order by c_1 desc; Because the true branch and the false branch are the same (i.e., ref_0.c20) in "case when ((LAST_VALUE(ref_0.pkey) over (partition by ref_0.c19 order by ref_0.vkey desc, ref_0.pkey desc)) in (select ref_0.c22 as c_0 from t2 as ref_8)) then ref_0.c20 else ref_0.c20 end", I changed it to "ref_0.c20" and got Test Case 2: *** Test Case 2 *** select case when true then null else (DENSE_RANK() over (partition by ref_0.pkey order by ref_0.vkey desc, ref_0.pkey asc)) end as c_0, ref_0.c20 as c_1 from t2 as ref_0 order by c_1 desc; *** Expected results *** Test Case 1 and Test Case 2 return the same results. *** Actual results *** Test Case 1 and Test Case 2 return inconsistent results. Test Case 1 return: +------+------+ | c_0 | c_1 | +------+------+ | NULL | NULL | +------+------+ 1 row in set (0.00 sec) Test Case 2 return: +------+-------+ | c_0 | c_1 | +------+-------+ | NULL | 17.61 | +------+-------+ 1 row in set (0.00 sec) *** Note *** The bug can be reproduced in version 8.0.33 and 8.0.34. In version 8.0.0 - 8.0.33, both Test Case 1 and 2 return the same results: +------+-------+ | c_0 | c_1 | +------+-------+ | NULL | 17.61 | +------+-------+ 1 row in set (0.00 sec)