Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.5.9, 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
-
Ubuntu 18.04
MariaDB 10.5.9
Description
I used my fuzzing tool to test Mariadb , and found a bug that can result in an abortion.
Mariadb installation：
1) cd mariadb-10.5.9
2) mkdir build; cd build
3) cmake -DWITH_ASAN=ON -DWITH_ASAN_SCOPE=ON -DWITH_DEBUG=ON ../
4) make -j8 && sudo make install
How to Repeat:
export ASAN_OPTIONS=detect_leaks=0
/usr/local/mysql/bin/mysqld_safe &
/usr/local/mysql/bin/mysql -uroot -p123456(your password)
MariaDB> drop database if exists test_db;
MariaDB> create database test_db;
MariaDB> source fuzz.sql;
I have simplified the content of fuzz.sql, and I hope fuzz.sql can help you reproduce the bug and fix it. In addition, I attach the abortion report (which has its stack trace).
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Closed
-
- links to
Activity
CREATE TABLE t1 (i int); |
INSERT INTO t1 VALUES (1),(2),(3); |
SELECT sum(i) over () IN ( SELECT 1 FROM t1 a) FROM t1; |
|
10.2 d0785f773188b5f0eebb313 |
#3 <signal handler called>
|
#4 0x0000559326e111c4 in Field::set_notnull (this=0x0, row_offset=0) at /10.2/src/sql/field.h:1152
|
#5 0x000055932718a63c in Item::save_in_field (this=0x7f1d5c012b30, field=0x0, no_conversions=true) at /10.2/src/sql/item.cc:6423
|
#6 0x00005593270b4b82 in save_window_function_values (window_functions=..., tbl=0x7f1d5c18f1d8, rowid_buf=0x7f1d5c173d50 "") at /10.2/src/sql/sql_window.cc:2534
|
#7 0x00005593270b5047 in compute_window_func (thd=0x7f1d5c000d90, window_functions=..., cursor_managers=..., tbl=0x7f1d5c18f1d8, filesort_result=0x7f1d5c1931e0) at /10.2/src/sql/sql_window.cc:2679
|
#8 0x00005593270b5424 in Window_func_runner::exec (this=0x7f1d5c0a02e8, thd=0x7f1d5c000d90, tbl=0x7f1d5c18f1d8, filesort_result=0x7f1d5c1931e0) at /10.2/src/sql/sql_window.cc:2782
|
#9 0x00005593270b554a in Window_funcs_sort::exec (this=0x7f1d5c0a02e0, join=0x7f1d5c0148a0, keep_filesort_result=true) at /10.2/src/sql/sql_window.cc:2810
|
#10 0x00005593270b5a44 in Window_funcs_computation::exec (this=0x7f1d5c0a02c0, join=0x7f1d5c0148a0, keep_last_filesort_result=true) at /10.2/src/sql/sql_window.cc:2937
|
#11 0x0000559326f66992 in AGGR_OP::end_send (this=0x7f1d5c0a01a0) at /10.2/src/sql/sql_select.cc:26865
|
#12 0x0000559326f513e7 in sub_select_postjoin_aggr (join=0x7f1d5c0148a0, join_tab=0x7f1d5c016708, end_of_records=true) at /10.2/src/sql/sql_select.cc:18589
|
#13 0x0000559326f5171b in sub_select (join=0x7f1d5c0148a0, join_tab=0x7f1d5c016358, end_of_records=true) at /10.2/src/sql/sql_select.cc:18825
|
#14 0x0000559326f50eff in do_select (join=0x7f1d5c0148a0, procedure=0x0) at /10.2/src/sql/sql_select.cc:18420
|
#15 0x0000559326f2aa87 in JOIN::exec_inner (this=0x7f1d5c0148a0) at /10.2/src/sql/sql_select.cc:3651
|
#16 0x0000559326f29f2e in JOIN::exec (this=0x7f1d5c0148a0) at /10.2/src/sql/sql_select.cc:3446
|
#17 0x0000559326f2b108 in mysql_select (thd=0x7f1d5c000d90, tables=0x7f1d5c014168, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f1d5c014880, unit=0x7f1d5c004988, select_lex=0x7f1d5c0050c8) at /10.2/src/sql/sql_select.cc:3849
|
#18 0x0000559326f1f25c in handle_select (thd=0x7f1d5c000d90, lex=0x7f1d5c0048c8, result=0x7f1d5c014880, setup_tables_done_option=0) at /10.2/src/sql/sql_select.cc:361
|
#19 0x0000559326ee9771 in execute_sqlcom_select (thd=0x7f1d5c000d90, all_tables=0x7f1d5c014168) at /10.2/src/sql/sql_parse.cc:6274
|
#20 0x0000559326ee02e5 in mysql_execute_command (thd=0x7f1d5c000d90) at /10.2/src/sql/sql_parse.cc:3585
|
#21 0x0000559326eed52c in mysql_parse (thd=0x7f1d5c000d90, rawbuf=0x7f1d5c0126f8 "SELECT sum(i) over () IN ( SELECT 1 FROM t1 a) FROM t1", length=54, parser_state=0x7f1da7307570, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:7796
|
#22 0x0000559326edb756 in dispatch_command (command=COM_QUERY, thd=0x7f1d5c000d90, packet=0x7f1d5c008b51 "", packet_length=54, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:1827
|
#23 0x0000559326eda251 in do_command (thd=0x7f1d5c000d90) at /10.2/src/sql/sql_parse.cc:1381
|
#24 0x000055932703588e in do_handle_one_connection (connect=0x5593295797c0) at /10.2/src/sql/sql_connect.cc:1336
|
#25 0x00005593270355f3 in handle_one_connection (arg=0x5593295797c0) at /10.2/src/sql/sql_connect.cc:1241
|
#26 0x00005593278611a8 in pfs_spawn_thread (arg=0x55932955cbc0) at /10.2/src/storage/perfschema/pfs.cc:1869
|
#27 0x00007f1db151d609 in start_thread (arg=<optimized out>) at pthread_create.c:477
|
#28 0x00007f1db10f7293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
|
|
SELECT sum(i) over () IN ( SELECT 1 FROM t1 a) FROM t1; |
It crashes, because the temporary table for computing Window Functions doesn't have a field for the window function.
Debugging a similar query: let's use +1 instead of IN-subquery:
SELECT lag(i) over (ORDER BY 1) +1 FROM t1; |
In create_tmp_table() call, I can see
(gdb) p fields.elem(0)
|
$66 = (Item_int *) 0x7fff70013958
|
(gdb) p fields.elem(1)
|
$68 = (Item_window_func *) 0x7fff70013ab8
|
(gdb) p fields.elem(2)
|
$70 = (Item_field *) 0x7fff70013688
|
(gdb) p fields.elem(3)
|
$72 = (Item_func_plus *) 0x7fff70013c30
|
The last element for "LAG(...) + 1", and there's also its Item_window_func object.
In the crashing query, create_tmp_table() is invoked with these fields:
(gdb) p fields.elem(0)
|
$87 = (Item_int *) 0x7fff70013980
|
(gdb) p fields.elem(1)
|
$89 = (Item_cache_wrapper *) 0x7fff70028f70
|
(gdb) wher
|
#0 setup_fields (thd=0x7fff70000d50, ref_pointer_array=..., fields=..., mark_used_columns=MARK_COLUMNS_READ, sum_func_list=0x7fff70015b10, pre_fix=0x7fff700051c8, allow_sum_func=true) at /home/psergey/dev-git/10.2-cl/sql/sql_base.cc:7306
|
#1 0x0000555555b80fcf in JOIN::prepare (this=0x7fff700157f0, tables_init=0x7fff700150d8, wild_num=0, conds_init=0x0, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7fff70005088, unit_arg=0x7fff70004948) at /home/psergey/dev-git/10.2-cl/sql/sql_select.cc:807
|
#2 0x0000555555b8ba8a in mysql_select (thd=0x7fff70000d50, tables=0x7fff700150d8, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fff700157d0, unit=0x7fff70004948, select_lex=0x7fff70005088) at /home/psergey/dev-git/10.2-cl/sql/sql_select.cc:3829
|
#3 0x0000555555b7fc91 in handle_select (thd=0x7fff70000d50, lex=0x7fff70004888, result=0x7fff700157d0, setup_tables_done_option=0) at /home/psergey/dev-git/10.2-cl/sql/sql_select.cc:361
|
#4 0x0000555555b4adb3 in execute_sqlcom_select (thd=0x7fff70000d50, all_tables=0x7fff700150d8) at /home/psergey/dev-git/10.2-cl/sql/sql_parse.cc:6274
|
#5 0x0000555555b418a5 in mysql_execute_command (thd=0x7fff70000d50) at /home/psergey/dev-git/10.2-cl/sql/sql_parse.cc:3585
|
#6 0x0000555555b4eb2a in mysql_parse (thd=0x7fff70000d50, rawbuf=0x7fff700135b8 "SELECT lag(i) over (ORDER BY 1) IN ( SELECT 1 FROM t1 a) FROM t1", length=64, parser_state=0x7ffff43a6640, is_com_multi=false, is_next_command=false) at /home/psergey/dev-git/10.2-cl/sql/sql_parse.cc:7796
|
#7 0x0000555555b3cd55 in dispatch_command (command=COM_QUERY, thd=0x7fff70000d50, packet=0x7fff70008c11 "SELECT lag(i) over (ORDER BY 1) IN ( SELECT 1 FROM t1 a) FROM t1", packet_length=64, is_com_multi=false, is_next_command=false) at /home/psergey/dev-git/10.2-cl/sql/sql_parse.cc:1827
|
#8 0x0000555555b3b7d2 in do_command (thd=0x7fff70000d50) at /home/psergey/dev-git/10.2-cl/sql/sql_parse.cc:1381
|
#9 0x0000555555c93790 in do_handle_one_connection (connect=0x5555579d41f0) at /home/psergey/dev-git/10.2-cl/sql/sql_connect.cc:1336
|
#10 0x0000555555c934fb in handle_one_connection (arg=0x5555579d41f0) at /home/psergey/dev-git/10.2-cl/sql/sql_connect.cc:1241
|
#11 0x00007ffff61406db in ?? ()
|
(gdb) print item
|
$166 = (Item_in_optimizer *) 0x7fff70016568
|
(gdb) p item->with_sum_func
|
$167 = false
|
(gdb) p item->with_window_func
|
$168 = false
|
In the LAG+1 query, the Item_func_plus had with_window_func=true
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
|
index 7b7604053e3..8a2c532f621 100644
|
--- a/sql/item_cmpfunc.cc
|
+++ b/sql/item_cmpfunc.cc
|
@@ -1416,6 +1416,9 @@ bool Item_in_optimizer::fix_fields(THD *thd, Item **ref)
|
maybe_null=1;
|
with_subselect= 1;
|
with_sum_func= with_sum_func || args[1]->with_sum_func;
|
+ with_window_func= args[0]->with_window_func;
|
+ // The subquery cannot have window functions aggregated in this select
|
+ DBUG_ASSERT(!args[1]->with_window_func);
|
with_field= with_field || args[1]->with_field;
|
with_param= args[0]->with_param || args[1]->with_param;
|
used_tables_and_const_cache_join(args[1]); |
With the above patch, the crash goes away. but the queries now return wrong results: the subquery always returns NULL:
CREATE TABLE t1 (i int); |
INSERT INTO t1 VALUES (1),(2),(3); |
SELECT lag(i) over (ORDER BY 1) IN ( SELECT 1 FROM t1 a) FROM t1; |
lag(i) over (ORDER BY 1) IN ( SELECT 1 FROM t1 a) |
NULL
|
NULL
|
NULL
|
|
|
SELECT lag(i) over (ORDER BY 1) FROM t1; |
lag(i) over (ORDER BY 1) |
NULL
|
3
|
1
|
SELECT sum(i) over () IN ( SELECT 1 FROM t1 a) FROM t1; |
sum(i) over () IN ( SELECT 1 FROM t1 a) |
NULL
|
NULL
|
NULL
|
SELECT sum(i) over () FROM t1; |
sum(i) over () |
6
|
6
|
6
|
If I do
set optimizer_switch='subquery_cache=off';
|
the results become correct.
I see the subquery is computed before we know the value of window functions, here:
wher
|
#0 Item_in_optimizer::val_int (this=0x7fff740143a8) at /home/psergey/dev-git/10.2-cl/sql/item_cmpfunc.cc:1591
|
#1 0x0000555555a6a45f in Item::val_int_result (this=0x7fff740143a8) at /home/psergey/dev-git/10.2-cl/sql/item.h:1274
|
#2 0x0000555555dedc70 in Item_cache_int::cache_value (this=0x7fff7401aeb0) at /home/psergey/dev-git/10.2-cl/sql/item.cc:9702
|
#3 0x0000555555df7e12 in Item_cache_wrapper::cache (this=0x7fff7401adf8) at /home/psergey/dev-git/10.2-cl/sql/item.cc:8363
|
#4 0x0000555555de9b52 in Item_cache_wrapper::save_val (this=0x7fff7401adf8, to=0x7fff74024c70) at /home/psergey/dev-git/10.2-cl/sql/item.cc:8389
|
#5 0x0000555555df517a in Item_cache_wrapper::save_in_result_field (this=0x7fff7401adf8, no_conversions=true) at /home/psergey/dev-git/10.2-cl/sql/item.h:4820
|
#6 0x0000555555bbebcb in copy_funcs (func_ptr=0x7fff74026fa8, thd=0x7fff74000d50) at /home/psergey/dev-git/10.2-cl/sql/sql_select.cc:23923
|
#7 0x0000555555bb5a1f in end_write (join=0x7fff74013630, join_tab=0x7fff74015770, end_of_records=false) at /home/psergey/dev-git/10.2-cl/sql/sql_select.cc:20278
|
#8 0x0000555555bc6f5a in AGGR_OP::put_record (this=0x7fff7401b340, end_of_records=false) at /home/psergey/dev-git/10.2-cl/sql/sql_select.cc:26820
|
#9 0x0000555555bccc21 in AGGR_OP::put_record (this=0x7fff7401b340) at /home/psergey/dev-git/10.2-cl/sql/sql_select.h:973
|
#10 0x0000555555bb1d62 in sub_select_postjoin_aggr (join=0x7fff74013630, join_tab=0x7fff74015770, end_of_records=false) at /home/psergey/dev-git/10.2-cl/sql/sql_select.cc:18595
|
#11 0x0000555555bb2996 in evaluate_join_record (join=0x7fff74013630, join_tab=0x7fff740153c0, error=0) at /home/psergey/dev-git/10.2-cl/sql/sql_select.cc:19094
|
#12 0x0000555555bb2282 in sub_select (join=0x7fff74013630, join_tab=0x7fff740153c0, end_of_records=false) at /home/psergey/dev-git/10.2-cl/sql/sql_select.cc:18874
|
#13 0x0000555555bb17f5 in do_select (join=0x7fff74013630, procedure=0x0) at /home/psergey/dev-git/10.2-cl/sql/sql_select.cc:18418
|
#14 0x0000555555b8b4b2 in JOIN::exec_inner (this=0x7fff74013630) at /home/psergey/dev-git/10.2-cl/sql/sql_select.cc:3651
|
#15 0x0000555555b8a96a in JOIN::exec (this=0x7fff74013630) at /home/psergey/dev-git/10.2-cl/sql/sql_select.cc:3446
|
#16 0x0000555555b8bb3c in mysql_select (thd=0x7fff74000d50, tables=0x7fff74012f18, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fff74013610, unit=0x7fff74004948, select_lex=0x7fff74005088) at /home/psergey/dev-git/10.2-cl/sql/sql_select.cc:3849
|
Frame #6 above is copy_funcs(). That function has the code to avoid computing expressions containing window functions:
copy_funcs(Item **func_ptr, const THD *thd) |
{
|
Item *func;
|
for (; (func = *func_ptr) ; func_ptr++) |
{
|
if (func->type() == Item::FUNC_ITEM && |
((Item_func *) func)->with_window_func)
|
continue; |
... but it doesn't fire because "func" is
(gdb) print func
|
$130 = (Item_cache_wrapper *) 0x7fff7401adf8
|
and it has func->with_window_func=FALSE.
... and save_window_function_values() has the reverse logic:
for (; (func = *func_ptr) ; func_ptr++) |
{
|
if (func->with_window_func && func->type() != Item::WINDOW_FUNC_ITEM) |
func->save_in_result_field(true); |
}
|
It recomputes the values of items that have with_window_func=true, so it does
not recompute the in-subquery.
Thank you very much! I repeated on 10.2-10.5:
10.2 d0785f773188b5f0eebb313
#3 <signal handler called>#4 0x0000559ff0189f92 in set_field_to_null_with_conversions (field=0x0, no_conversions=true) at /10.2/src/sql/field_conv.cc:204#5 0x0000559ff01ba6da in Item::save_in_field (this=0x7fbeac012c20, field=0x0, no_conversions=true) at /10.2/src/sql/item.cc:6430#6 0x0000559ff00e4b82 in save_window_function_values (window_functions=..., tbl=0x7fbeac191a08, rowid_buf=0x7fbeac134cf0 "p\333\f\254\276\177") at /10.2/src/sql/sql_window.cc:2534#7 0x0000559ff00e5047 in compute_window_func (thd=0x7fbeac000d90, window_functions=..., cursor_managers=..., tbl=0x7fbeac191a08, filesort_result=0x7fbeac193280) at /10.2/src/sql/sql_window.cc:2679#8 0x0000559ff00e5424 in Window_func_runner::exec (this=0x7fbeac0a1718, thd=0x7fbeac000d90, tbl=0x7fbeac191a08, filesort_result=0x7fbeac193280) at /10.2/src/sql/sql_window.cc:2782#9 0x0000559ff00e554a in Window_funcs_sort::exec (this=0x7fbeac0a1710, join=0x7fbeac0149b0, keep_filesort_result=true) at /10.2/src/sql/sql_window.cc:2810#10 0x0000559ff00e5a44 in Window_funcs_computation::exec (this=0x7fbeac0a16f0, join=0x7fbeac0149b0, keep_last_filesort_result=true) at /10.2/src/sql/sql_window.cc:2937#11 0x0000559feff96992 in AGGR_OP::end_send (this=0x7fbeac0a15c0) at /10.2/src/sql/sql_select.cc:26865#12 0x0000559feff813e7 in sub_select_postjoin_aggr (join=0x7fbeac0149b0, join_tab=0x7fbeac0168f0, end_of_records=true) at /10.2/src/sql/sql_select.cc:18589#13 0x0000559feff8171b in sub_select (join=0x7fbeac0149b0, join_tab=0x7fbeac016540, end_of_records=true) at /10.2/src/sql/sql_select.cc:18825#14 0x0000559feff80eff in do_select (join=0x7fbeac0149b0, procedure=0x0) at /10.2/src/sql/sql_select.cc:18420#15 0x0000559feff5aa87 in JOIN::exec_inner (this=0x7fbeac0149b0) at /10.2/src/sql/sql_select.cc:3651#16 0x0000559feff59f2e in JOIN::exec (this=0x7fbeac0149b0) at /10.2/src/sql/sql_select.cc:3446#17 0x0000559feff5b108 in mysql_select (thd=0x7fbeac000d90, tables=0x7fbeac014278, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fbeac014990, unit=0x7fbeac004988, select_lex=0x7fbeac0050c8) at /10.2/src/sql/sql_select.cc:3849#18 0x0000559feff4f25c in handle_select (thd=0x7fbeac000d90, lex=0x7fbeac0048c8, result=0x7fbeac014990, setup_tables_done_option=0) at /10.2/src/sql/sql_select.cc:361#19 0x0000559feff19771 in execute_sqlcom_select (thd=0x7fbeac000d90, all_tables=0x7fbeac014278) at /10.2/src/sql/sql_parse.cc:6274#20 0x0000559feff102e5 in mysql_execute_command (thd=0x7fbeac000d90) at /10.2/src/sql/sql_parse.cc:3585#21 0x0000559feff1d52c in mysql_parse (thd=0x7fbeac000d90, rawbuf=0x7fbeac0126f8 "SELECT lag(i) over (ORDER BY 1) IN ( SELECT 1 FROM t1 a) FROM t1", length=64, parser_state=0x7fbf02903570, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:7796#22 0x0000559feff0b756 in dispatch_command (command=COM_QUERY, thd=0x7fbeac000d90, packet=0x7fbeac008b51 "", packet_length=64, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:1827#23 0x0000559feff0a251 in do_command (thd=0x7fbeac000d90) at /10.2/src/sql/sql_parse.cc:1381#24 0x0000559ff006588e in do_handle_one_connection (connect=0x559ff22a0a40) at /10.2/src/sql/sql_connect.cc:1336#25 0x0000559ff00655f3 in handle_one_connection (arg=0x559ff22a0a40) at /10.2/src/sql/sql_connect.cc:1241#26 0x0000559ff08911a8 in pfs_spawn_thread (arg=0x559ff2283e40) at /10.2/src/storage/perfschema/pfs.cc:1869#27 0x00007fbf08b08609 in start_thread (arg=<optimized out>) at pthread_create.c:477#28 0x00007fbf086e2293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95