| Bug #112557 | Inconsistent results of SELECT statement with EXISTS clause | ||
|---|---|---|---|
| Submitted: | 28 Sep 2023 7:40 | Modified: | 5 Aug 2024 16:17 |
| Reporter: | Zuming Jiang | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S2 (Serious) |
| Version: | 8.0.27,8.0.34 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | regression | ||
[28 Sep 2023 9:34]
MySQL Verification Team
Hello Zuming Jiang, Thank you for the report and test case. regards, Umesh
[28 Sep 2023 9:37]
MySQL Verification Team
Looks like regression as 8.0.11 not affected ./mtr --nocheck-testcases bug112557 Logging: ./mtr --nocheck-testcases bug112557 2023-09-28T09:37:11.294492Z 0 [System] [MY-010116] [Server] /export/home/tmp/ushastry/mysql-8.0.11/bin/mysqld (mysqld 8.0.11) starting as process 9399 MySQL Version 8.0.11 Checking supported features... - SSL connections supported Collecting tests... Checking leftover processes... Removing old var directory... Creating var directory '/export/home/tmp/ushastry/mysql-8.0.11/mysql-test/var'... Installing system database... Using parallel: 1 ============================================================================== TEST RESULT TIME (ms) or COMMENT -------------------------------------------------------------------------- worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009 create table t1 (vkey int, pkey int, c5 int, c7 varchar(200)); insert into t1 values (20, 30000, 59, null); insert into t1 values (59, 69000, null, null); select ref_0.c5 >= (case when (true and exists ( select ref_2.pkey as c_3 from t1 as ref_2 where ref_2.vkey between ref_0.c5 and ref_0.c5 )) then null else (case when (FIRST_VALUE('') over (partition by ref_0.c7 order by ref_0.vkey asc, ref_0.pkey desc) >= null) then null else ref_0.pkey end ) end ) as c_6 from t1 as ref_0; c_6 NULL NULL select ref_0.c5 >= (case when (exists ( select ref_2.pkey as c_3 from t1 as ref_2 where ref_2.vkey between ref_0.c5 and ref_0.c5 )) then null else (case when (FIRST_VALUE('') over (partition by ref_0.c7 order by ref_0.vkey asc, ref_0.pkey desc) >= null) then null else ref_0.pkey end ) end ) as c_6 from t1 as ref_0; c_6 NULL NULL main.bug112557 [ pass ] 44 --------------------------------------------------------------------------
[31 Oct 2023 9:56]
huahua xu
Hi All,
For Test Case 2, I tracked its execution in version 8.0.30:
step 1: buffer all the rows in the same window.
copy_funcs(m_temp_table_param, thd(), CFT_HAS_NO_WF)
param->items_to_copy = 0x00000250d04cde98 {...}
innodb(ref0.*) --> temptable(<temporary>.*) // copy the fields from the input table's buffer to the out table's fields
save_in_field_no_error_check // execute the non-window functions which do not rely on window functions, and copy the result into the out table's fields
buffer_windowing_record // buffer the current row to the frame buffer
bool copy_fields(Temp_table_param *param, const THD *thd, bool reverse_copy)
param->copy_fields = { size=5 }
param 0x00000250d066bd60 {copy_fields={ size=5 } group_buff=0x0000000000000000 <NULL> items_to_copy=0x00000250d04ceea0 {...} ...} Temp_table_param *
temptable(<temporary>.*) --> temptable(<>.*) // copy the fields from the out table's buffer to the window frame buffer
buffer_record_somewhere
t->file->ha_write_row(record) // save the window frame buffer to the frame buffer temporary table
step 2: process the buffered rows by evaluating/aggregating the window functions defined over this window on the current frame.
process_buffered_windowing_record
bring_back_frame_row
read_frame_buffer_row
t->file->ha_rnd_pos // read row from the frame buffer temporary table
bool copy_fields(Temp_table_param *param, const THD *thd, bool reverse_copy)
param->copy_fields = { size=5 }
param 0x00000250d066bd60 {copy_fields={ size=5 } group_buff=0x0000000000000000 <NULL> items_to_copy=0x00000250d04ceea0 {...} ...} Temp_table_param *
temptable(<>.*) --> temptable(<temporary>.*) // copy the fields from the window frame buffer to the out table's buffer
copy_funcs(param, thd, CFT_WF_NON_FRAMING) // execute the non framing window function, and copy the result into the out table's fields
copy_funcs(param, thd, CFT_WF_FRAMING) // execute the framing window function, and copy the result into the out table's fields (the function Item_first_last_value `FIRST_VALUE('') over (partition by ref_0.c7 order by ref_0.vkey asc, ref_0.pkey desc)` would be executed)
step 3: execute all non-wf functions, and prepare the output row.
bring_back_frame_row
read_frame_buffer_row
t->file->ha_rnd_pos // read row from the frame buffer temporary table
bool copy_fields(Temp_table_param *param, const THD *thd, bool reverse_copy)
param->copy_fields = { size=5 }
param 0x00000250d066bd60 {copy_fields={ size=5 } group_buff=0x0000000000000000 <NULL> items_to_copy=0x00000250d04ceea0 {...} ...} Temp_table_param *
temptable(<>.*) --> temptable(<temporary>.*) // copy the fields from the window frame buffer to the out table's buffer
copy_funcs(param, thd, CFT_HAS_WF) // execute the non-wf functions, and copy the result into the out table's fields (the function Item_func_ge `ref_0.c5 >= (case when ... )` would be executed)
Obviously, the result could be incorrect when the function Item_func_ge `ref_0.c5 >= (case when ... )` is executed, because the values of its referenced fields comes from the last record in the input table for each window.
[31 Oct 2023 15:12]
Dag Wanvik
Posted by developer:
Bisect points to this commit:
16054f827f57ffbd2e044141d5e5f0c1aaeb35af is the first bad commit
commit 16054f827f57ffbd2e044141d5e5f0c1aaeb35af
Author: Steinar H. Gunderson <steinar.gunderson@oracle.com>
Date: Thu Apr 15 22:52:53 2021 +0200
Bug #32644631: PRELIMINARY FIXES FOR WL #14419 [wf framebuffer, noclose]
Bug #32802301: REGRESSION: TYPE_CONVERSION_STATUS FIELD_VARSTRING::STORE: ASSERTION `!TABLE || (!TABLE->WRITE_SET || BITMAP_IS_SET(TABLE->WRITE_SET, FIELD_INDEX()))' FAILED.
[1 Nov 2023 13:12]
huahua xu
Hi Dag Wanvik,
I agree with your opinion. For Test Case 2, I also tracked its execution in version 8.0.19:
```
step 1: buffer all the rows in the same window.
copy_fields_and_funcs(m_temp_table_param, thd(), CFT_HAS_NO_WF)
param 0x000002374c66e448 {grouped_expressions={ size=0 } copy_fields={ size=5 } group_buff=0x0000000000000000 <NULL> ...} Temp_table_param *
bool copy_fields(Temp_table_param *param, const THD *thd, bool reverse_copy)
param->copy_fields = { size=5 }
innodb(ref0.*) --> temptable(<temporary>.*) // copy the fields from the input table's buffer to the out table's fields
bool copy_funcs(Temp_table_param *param, const THD *thd, Copy_func_type type)
param->items_to_copy // execute the non-window functions which do not rely on window functions, and copy the result into the out table's fields
buffer_windowing_record // buffer the current row to the frame buffer
param = 0x000002374c66d888 {grouped_expressions={ size=0 } copy_fields={ size=6 } group_buff=0x0000000000000000 <NULL> ...}
bool copy_fields(Temp_table_param *param, const THD *thd, bool reverse_copy)
param->copy_fields = { size=6 }
innodb(ref0.*) --> temptable(<>.*) // copy the fields from the input table's buffer to the window frame buffer
buffer_record_somewhere
t->file->ha_write_row(record) // save the window frame buffer to the frame buffer temporary table
step 2: process the buffered rows by evaluating/aggregating the window functions defined over this window on the current frame.
process_buffered_windowing_record
bool bring_back_frame_row(THD *thd, Window *w, Temp_table_param *out_param, ...)
param 0x000002374c66d888 {grouped_expressions={ size=0 } copy_fields={ size=6 } group_buff=0x0000000000000000 <NULL> ...} Temp_table_param *
read_frame_buffer_row
t->file->ha_rnd_pos // read row from the frame buffer temporary table
bool copy_fields(Temp_table_param *param, const THD *thd, bool reverse_copy)
param->copy_fields = { size=6 }
temptable(<>.*) --> innodb<ref0.*> // copy the fields from the window frame buffer to the input table's buffer
bool copy_fields(Temp_table_param *param, const THD *thd, bool reverse_copy)
param 0x000002374c66e448 {grouped_expressions={ size=0 } copy_fields={ size=5 } group_buff=0x0000000000000000 <NULL> ...} Temp_table_param *
param->copy_fields = { size=5 }
innodb(ref0.*) --> temptable(<temporary>.*) // copy the fields from the input table's buffer to the out table's fields
copy_funcs(param, thd, CFT_WF_NON_FRAMING) // execute the non framing window function, and copy the result into the out table's fields
copy_funcs(param, thd, CFT_WF_FRAMING) // execute the framing window function, and copy the result into the out table's fields (the function Item_first_last_value `FIRST_VALUE('') over (partition by ref_0.c7 order by ref_0.vkey asc, ref_0.pkey desc)` would be executed)
step 3: execute all non-wf functions, and prepare the output row.
bool bring_back_frame_row(THD *thd, Window *w, Temp_table_param *out_param, ...)
read_frame_buffer_row
t->file->ha_rnd_pos // read row from the frame buffer temporary table
bool copy_fields(Temp_table_param *param, const THD *thd, bool reverse_copy)
param 0x000002374c66d888 {grouped_expressions={ size=0 } copy_fields={ size=6 } group_buff=0x0000000000000000 <NULL> ...} Temp_table_param *
param->copy_fields = { size=6 }
temptable(<>.*) --> innodb(ref0.*) // copy the fields from the window frame buffer to the input table's buffer
bool copy_fields(Temp_table_param *param, const THD *thd, bool reverse_copy)
param 0x000002374c66e448 {grouped_expressions={ size=0 } copy_fields={ size=5 } group_buff=0x0000000000000000 <NULL> ...} Temp_table_param *
param->copy_fields = { size=5 }
innodb(ref0.*) --> temptable(<temporary>.*) // copy the fields from the input table's buffer to the out table's fields
copy_funcs(param, thd, CFT_HAS_WF) // execute the non-wf functions, and copy the result into the out table's fields (the function Item_func_ge `ref_0.c5 >= (case when ... )` would be executed)
```
In the commit https://github.com/mysql/mysql-server/commit/1407269ba68f5a4f07431f6b6010d8aaf1b81510, a related modification is:
```
@@ -492,11 +491,11 @@ bool bring_back_frame_row(THD *thd, Window *w, Temp_table_param *out_param,
if (!rc) {
if (out_param) {
if (copy_funcs(out_param, thd, CFT_FIELDS)) return true;
// fields are in IN and in OUT
// fields are in OUT
if (rowno >= 1) w->set_row_has_fields_in_out_table(rowno);
} else
// we only wrote IN record, so OUT and IN are inconsistent
// TODO(sgunders): Is this still needed?
w->set_row_has_fields_in_out_table(0);
}
```
[13 Dec 2023 11:53]
shan he
Could you please confirm whether the simplification process we applied has been helpful for developers in diagnosing and addressing this issue?
Although not obvious for this example, it removes " partition by ref_0.c7 order by ref_0.vkey asc, ref_0.pkey desc) >= null".
Here's our output
*****
create table t1 (vkey int, pkey int, c5 int, c7 varchar(200));
insert into t1 values (20, 30000, 59, null);
insert into t1 values (59, 69000, null, null);
select
(case when (true and exists (
select
ref_2.pkey as c_3
from
t1 as ref_2
where ref_2.vkey between ref_0.c5 and ref_0.c5
)) then null else (case when (FIRST_VALUE('') over () >= null) then null else ref_0.pkey end
) end
) as c_6
from
t1 as ref_0;
+-------+
| c_6 |
+-------+
| NULL |
| 69000 |
+-------+
select
(case when (exists (
select
ref_2.pkey as c_3
from
t1 as ref_2
where ref_2.vkey between ref_0.c5 and ref_0.c5
)) then null else (case when (FIRST_VALUE('') over () >= null) then null else ref_0.pkey end
) end
) as c_6
from
t1 as ref_0;
+-------+
| c_6 |
+-------+
| 30000 |
| 69000 |
+-------+
[5 Aug 2024 16:17]
Jon Stephens
Documented fix as follows in the MySQL 9.1.0 changelog:
Queries using CASE (WHEN TRUE AND EXISTS (/subquery/)) sometimes
did not return the same result as with CASE (EXISTS (/subquery/)).
Closed.
Description: Dear MySQL developers, I used my fuzzer to test MySQL and found a logic bug that made the MySQL server output inconsistent results. How to repeat: *** Set up the database *** create table t1 (vkey int, pkey int, c5 int, c7 varchar(200)); insert into t1 values (20, 30000, 59, null); insert into t1 values (59, 69000, null, null); *** Test Case 1 *** select ref_0.c5 >= (case when (true and exists ( select ref_2.pkey as c_3 from t1 as ref_2 where ref_2.vkey between ref_0.c5 and ref_0.c5 )) then null else (case when (FIRST_VALUE('') over (partition by ref_0.c7 order by ref_0.vkey asc, ref_0.pkey desc) >= null) then null else ref_0.pkey end ) end ) as c_6 from t1 as ref_0; I simplified "true and exists(...)" to "exists(...)", and got Test Case 2: *** Test Case 2 *** select ref_0.c5 >= (case when (exists ( select ref_2.pkey as c_3 from t1 as ref_2 where ref_2.vkey between ref_0.c5 and ref_0.c5 )) then null else (case when (FIRST_VALUE('') over (partition by ref_0.c7 order by ref_0.vkey asc, ref_0.pkey desc) >= null) then null else ref_0.pkey end ) end ) as c_6 from t1 as ref_0; *** 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_6 | +------+ | NULL | | NULL | +------+ 2 rows in set (0.00 sec) Test Case 2 return: +------+ | c_6 | +------+ | 0 | | NULL | +------+ 2 rows in set (0.00 sec) *** Note *** The bug can be reproduced in version 8.0.27 - 8.0.34. In version 8.0.0 - 8.0.26, both Test Case 1 and 2 return the same results: +------+ | c_6 | +------+ | NULL | | NULL | +------+ 2 rows in set (0.00 sec)