[MDEV-25630] Crash with window function in left expr of IN subquery Created: 2021-05-10  Updated: 2022-03-22  Resolved: 2021-06-09

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - Window functions
Affects Version/s: 10.5.9, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.2.40, 10.3.31, 10.4.21, 10.5.12, 10.6.3

Type: Bug Priority: Major
Reporter: Zuming Jiang Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: crash, fuzzer
Environment:

Ubuntu 18.04
MariaDB 10.5.9

Attachments: Text File abortion_report.txt     File fuzz.sql    
Issue Links:
Relates
relates to MDEV-14791 Crash with order by expression contai... Closed
relates to MDEV-28094 Window function in expression in ORDE... Closed

 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).

 Comments   
Comment by Alice Sherepa [ 2021-05-10 ]

Thank you very much! I repeated on 10.2-10.5:

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;

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

Comment by Alice Sherepa [ 2021-05-10 ] 

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

Comment by Sergei Petrunia [ 2021-05-21 ] 

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

Comment by Sergei Petrunia [ 2021-05-21 ] 

(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

Comment by Sergei Petrunia [ 2021-05-22 ] 

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]);

Comment by Sergei Petrunia [ 2021-05-22 ]

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.

Comment by Sergei Petrunia [ 2021-05-22 ]

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

Comment by Sergei Petrunia [ 2021-05-22 ]

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.

Comment by Sergei Petrunia [ 2021-05-22 ]

... 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.

Comment by Sergei Petrunia [ 2021-05-22 ]

http://lists.askmonty.org/pipermail/commits/2021-May/014627.html

Comment by Sergei Petrunia [ 2021-05-22 ]

bb-10.2-mdev25630

Comment by Sergei Petrunia [ 2021-05-22 ]

sanja, please review

Comment by Oleksandr Byelkin [ 2021-06-08 ]

OK to push

Generated at Thu Feb 08 09:39:09 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.