[MDEV-25629] Crash in get_sort_by_table() in subquery with order by having outer ref Created: 2021-05-10  Updated: 2022-02-09  Resolved: 2021-05-19

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5, 10.0, 10.1, 10.5.9, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.6.2, 10.2.39, 10.3.30, 10.4.20, 10.5.11

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

Ubuntu 18.04
MariaDB 10.5.9

Attachments: Text File abortion_report.txt     File fuzz.sql    

 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 ]

Thanks!
Repeatable on 5.5-10.5:

--source include/have_innodb.inc
 
CREATE TABLE t1 (i1 int)engine=innodb;
 
 
 
SELECT 1 
FROM (t1 JOIN t1 AS ref_t1 ON 
	(t1.i1 > (SELECT ref_t1.i1 AS c0 FROM t1 b ORDER BY -c0)));
 
 
 
DROP TABLE t1;

10.2 d0785f773188b5f0eebb313

 
#3  <signal handler called>
 
#4  0x000055c7fcc4d17b in get_sort_by_table (a=0x0, b=0x0, tables=..., const_tables=0) at /10.2/src/sql/sql_select.cc:23132
 
#5  0x000055c7fcc1dd65 in make_join_statistics (join=0x7f5a68016418, tables_list=..., keyuse_array=0x7f5a68016708) at /10.2/src/sql/sql_select.cc:4362
 
#6  0x000055c7fcc1462b in JOIN::optimize_inner (this=0x7f5a68016418) at /10.2/src/sql/sql_select.cc:1597
 
#7  0x000055c7fcc12b22 in JOIN::optimize (this=0x7f5a68016418) at /10.2/src/sql/sql_select.cc:1127
 
#8  0x000055c7fcbc15ff in st_select_lex::optimize_unflattened_subqueries (this=0x7f5a680050c8, const_only=false) at /10.2/src/sql/sql_lex.cc:3865
 
#9  0x000055c7fcd85cbc in JOIN::optimize_unflattened_subqueries (this=0x7f5a68015db0) at /10.2/src/sql/opt_subselect.cc:5326
 
#10 0x000055c7fcc16296 in JOIN::optimize_inner (this=0x7f5a68015db0) at /10.2/src/sql/sql_select.cc:2089
 
#11 0x000055c7fcc12b22 in JOIN::optimize (this=0x7f5a68015db0) at /10.2/src/sql/sql_select.cc:1127
 
#12 0x000055c7fcc1c078 in mysql_select (thd=0x7f5a68000d90, tables=0x7f5a68012fa8, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f5a68015d90, unit=0x7f5a68004988, select_lex=0x7f5a680050c8) at /10.2/src/sql/sql_select.cc:3835
 
#13 0x000055c7fcc1025c in handle_select (thd=0x7f5a68000d90, lex=0x7f5a680048c8, result=0x7f5a68015d90, setup_tables_done_option=0) at /10.2/src/sql/sql_select.cc:361
 
#14 0x000055c7fcbda771 in execute_sqlcom_select (thd=0x7f5a68000d90, all_tables=0x7f5a68012fa8) at /10.2/src/sql/sql_parse.cc:6274
 
#15 0x000055c7fcbd12e5 in mysql_execute_command (thd=0x7f5a68000d90) at /10.2/src/sql/sql_parse.cc:3585
 
#16 0x000055c7fcbde52c in mysql_parse (thd=0x7f5a68000d90, rawbuf=0x7f5a680126f8 "SELECT 1 \nFROM (t1 JOIN t1 AS ref_t1 ON \n(t1.i1 > (SELECT ref_t1.i1 AS c0 FROM t1 b ORDER BY -c0)))", length=99, parser_state=0x7f5abb4a3570, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:7796
 
#17 0x000055c7fcbcc756 in dispatch_command (command=COM_QUERY, thd=0x7f5a68000d90, packet=0x7f5a68008b51 "SELECT 1 \nFROM (t1 JOIN t1 AS ref_t1 ON \n(t1.i1 > (SELECT ref_t1.i1 AS c0 FROM t1 b ORDER BY -c0)))", packet_length=99, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:1827
 
#18 0x000055c7fcbcb251 in do_command (thd=0x7f5a68000d90) at /10.2/src/sql/sql_parse.cc:1381
 
#19 0x000055c7fcd2688e in do_handle_one_connection (connect=0x55c8008d7a70) at /10.2/src/sql/sql_connect.cc:1336
 
#20 0x000055c7fcd265f3 in handle_one_connection (arg=0x55c8008d7a70) at /10.2/src/sql/sql_connect.cc:1241
 
#21 0x000055c7fd5521a8 in pfs_spawn_thread (arg=0x55c8008bae70) at /10.2/src/storage/perfschema/pfs.cc:1869
 
#22 0x00007f5ac56b4609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#23 0x00007f5ac528e293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95


210510 14:10:12 [ERROR] mysqld got signal 11 ;
 
 
 
Server version: 10.5.10-MariaDB
 
 
 
stack_bottom = 0x7f94842e0dd8 thread_stack 0x49000
 
mysys/stacktrace.c:213(my_print_stacktrace)[0x560bdfb64dce]
 
sql/signal_handler.cc:224(handle_fatal_signal)[0x560bdf627705]
 
??:0(__restore_rt)[0x7f948524b730]
 
sql/sql_select.cc:25122(get_sort_by_table)[0x560bdf2eccfc]
 
sql/sql_select.cc:1666(JOIN::optimize())[0x560bdf497583]
 
sql/sql_lex.cc:4884(st_select_lex::optimize_unflattened_subqueries(bool))[0x560bdf41bd1d]
 
sql/sql_select.cc:2867(JOIN::optimize_stage2())[0x560bdf493158]
 
sql/sql_select.cc:2318(JOIN::optimize_inner())[0x560bdf4956cb]
 
sql/sql_select.cc:1666(JOIN::optimize())[0x560bdf497583]
 
sql/sql_select.cc:4745(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x560bdf497647]
 
sql/sql_select.cc:443(handle_select(THD*, LEX*, select_result*, unsigned long))[0x560bdf497ffe]
 
sql/sql_parse.cc:6313(execute_sqlcom_select(THD*, TABLE_LIST*))[0x560bdf438b39]
 
sql/sql_parse.cc:6055(mysql_execute_command(THD*))[0x560bdf4422ce]
 
sql/sql_parse.cc:8116(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x560bdf4341be]
 
sql/sql_parse.cc:1894(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x560bdf43f7ea]
 
sql/sql_parse.cc:1370(do_command(THD*))[0x560bdf440724]
 
sql/sql_connect.cc:1410(do_handle_one_connection(CONNECT*, bool))[0x560bdf5291a0]
 
sql/sql_connect.cc:1312(handle_one_connection)[0x560bdf52957d]
 
perfschema/pfs.cc:2204(pfs_spawn_thread)[0x560bdf85a2eb]
 
nptl/pthread_create.c:487(start_thread)[0x7f9485240fa3]
 
x86_64/clone.S:97(clone)[0x7f9484e4b4cf]
 
 
 
Query (0x7f93a809bda0): SELECT 1 
FROM (t1 JOIN t1 AS ref_t1 ON 
(t1.i1 > (SELECT ref_t1.i1 AS c0 FROM t1 b ORDER BY -c0)))

Comment by Sergei Petrunia [ 2021-05-14 ]

Look at the query:

SELECT 1
 
FROM 
  (t1 JOIN 
   t1 AS ref_t1 ON  (t1.i1 > (SELECT ref_t1.i1 AS c0 
                              FROM t1 b 
                              ORDER BY -c0)
 
                    )
 
  );

the subquery has an outer reference: SELECT ref_t1.i1 AS c0 and then the
ORDER BY clause uses it ORDER BY -c0.

Comment by Sergei Petrunia [ 2021-05-14 ]

The crash happens when get_sort_by_table() is called for the subquery.
This is because it computes map=2 (while the select has only one table with
map=1)

map=2 comes from

(gdb) p a->item[0]
 
  $204 = (Item_func_neg *) 0x7fff84019298
 
(gdb) p a->item[0]->used_tables()
 
  $205 = 2

which comes from

(gdb) p a->item[0]->args[0]
 
  $206 = (Item_field *) 0x7fff84019158
 
(gdb) p a->item[0]->args[0]->used_tables()
 
  $207 = 2

Comment by Sergei Petrunia [ 2021-05-14 ]

One obvious question is, why ORDER BY has not been removed? It is redundant in a scalar-context subquery and should have been removed. Let's get back to this question after the crash is gone.

Comment by Sergei Petrunia [ 2021-05-14 ]

Looking at how Name Resolution is performed for the "ORDER BY -c0"

We are here, in fix_fields for the Item_field object representing the "c0":

(gdb) wher
 
  #0  find_item_in_list (find=0x7fff7c019158, items=..., counter=0x7ffff41328c0, report_error=REPORT_EXCEPT_NOT_FOUND, resolution=0x7ffff41328c4, limit=0) at /home/psergey/dev-git2/10.5-cp2/sql/sql_base.cc:6699
 
  #1  0x00005555561fc3c8 in Item_field::fix_fields (this=0x7fff7c019158, thd=0x7fff7c000d78, reference=0x7fff7c019330) at /home/psergey/dev-git2/10.5-cp2/sql/item.cc:5873
 
  #2  0x0000555555d48c11 in Item::fix_fields_if_needed (this=0x7fff7c019158, thd=0x7fff7c000d78, ref=0x7fff7c019330) at /home/psergey/dev-git2/10.5-cp2/sql/item.h:988
 
  #3  0x0000555556252f65 in Item_func::fix_fields (this=0x7fff7c019298, thd=0x7fff7c000d78, ref=0x7fff7c019390) at /home/psergey/dev-git2/10.5-cp2/sql/item_func.cc:352
 
  #4  0x0000555555d48c11 in Item::fix_fields_if_needed (this=0x7fff7c019298, thd=0x7fff7c000d78, ref=0x7fff7c019390) at /home/psergey/dev-git2/10.5-cp2/sql/item.h:988
 
  #5  0x0000555555d48c3f in Item::fix_fields_if_needed_for_scalar (this=0x7fff7c019298, thd=0x7fff7c000d78, ref=0x7fff7c019390) at /home/psergey/dev-git2/10.5-cp2/sql/item.h:992
 
  #6  0x0000555555f05e7d in Item::fix_fields_if_needed_for_order_by (this=0x7fff7c019298, thd=0x7fff7c000d78, ref=0x7fff7c019390) at /home/psergey/dev-git2/10.5-cp2/sql/item.h:1000
 
  #7  0x0000555555ef5a39 in find_order_in_list (thd=0x7fff7c000d78, ref_pointer_array=..., tables=0x7fff7c018258, order=0x7fff7c019380, fields=..., all_fields=..., is_group_field=false, add_to_all_fields=true, from_window_spec=false) at /home/psergey/dev-git2/10.5-cp2/sql/sql_select.cc:24662
 
  #8  0x0000555555ef5c1b in setup_order (thd=0x7fff7c000d78, ref_pointer_array=..., tables=0x7fff7c018258, fields=..., all_fields=..., order=0x7fff7c019380, from_window_spec=false) at /home/psergey/dev-git2/10.5-cp2/sql/sql_select.cc:24709
 
  #9  0x0000555555eaf7af in setup_without_group (thd=0x7fff7c000d78, ref_pointer_array=..., tables=0x7fff7c018258, leaves=..., fields=..., all_fields=..., conds=0x7fff7c01b3f8, order=0x7fff7c019380, group=0x0, win_specs=..., win_funcs=..., hidden_group_fields=0x7fff7c01b2cf, reserved=0x7fff7c017f24) at /home/psergey/dev-git2/10.5-cp2/sql/sql_select.cc:747
 
  #10 0x0000555555eb25d2 in JOIN::prepare (this=0x7fff7c01afe8, tables_init=0x7fff7c018258, conds_init=0x0, og_num=1, order_init=0x7fff7c019380, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7fff7c017c18, unit_arg=0x7fff7c018950) at /home/psergey/dev-git2/10.5-cp2/sql/sql_select.cc:1285
 
  #11 0x00005555562bfb90 in subselect_single_select_engine::prepare (this=0x7fff7c0195b0, thd=0x7fff7c000d78) at /home/psergey/dev-git2/10.5-cp2/sql/item_subselect.cc:3863
 
  #12 0x00005555562b1e55 in Item_subselect::fix_fields (this=0x7fff7c019408, thd_param=0x7fff7c000d78, ref=0x7fff7c019690) at /home/psergey/dev-git2/10.5-cp2/sql/item_subselect.cc:289
 
  #13 0x0000555555d48c11 in Item::fix_fields_if_needed (this=0x7fff7c019408, thd=0x7fff7c000d78, ref=0x7fff7c019690) at /home/psergey/dev-git2/10.5-cp2/sql/item.h:988
 
  #14 0x0000555556252f65 in Item_func::fix_fields (this=0x7fff7c0195f0, thd=0x7fff7c000d78, ref=0x7fff7c0173c0) at /home/psergey/dev-git2/10.5-cp2/sql/item_func.cc:352
 
  #15 0x0000555555d48c11 in Item::fix_fields_if_needed (this=0x7fff7c0195f0, thd=0x7fff7c000d78, ref=0x7fff7c0173c0) at /home/psergey/dev-git2/10.5-cp2/sql/item.h:988
 
  #16 0x0000555555d48c3f in Item::fix_fields_if_needed_for_scalar (this=0x7fff7c0195f0, thd=0x7fff7c000d78, ref=0x7fff7c0173c0) at /home/psergey/dev-git2/10.5-cp2/sql/item.h:992
 
  #17 0x0000555555dd0c19 in Item::fix_fields_if_needed_for_bool (this=0x7fff7c0195f0, thd=0x7fff7c000d78, ref=0x7fff7c0173c0) at /home/psergey/dev-git2/10.5-cp2/sql/item.h:996
 
  #18 0x0000555555dcccff in setup_on_expr (thd=0x7fff7c000d78, table=0x7fff7c017360, is_update=false) at /home/psergey/dev-git2/10.5-cp2/sql/sql_base.cc:8284
 
  #19 0x0000555555dcd17f in setup_conds (thd=0x7fff7c000d78, tables=0x7fff7c016c40, leaves=..., conds=0x7fff7c01ad60) at /home/psergey/dev-git2/10.5-cp2/sql/sql_base.cc:8401
 
  #20 0x0000555555eaf69e in setup_without_group (thd=0x7fff7c000d78, ref_pointer_array=..., tables=0x7fff7c016c40, leaves=..., fields=..., all_fields=..., conds=0x7fff7c01ad60, order=0x0, group=0x0, win_specs=..., win_funcs=..., hidden_group_fields=0x7fff7c01ac37, reserved=0x7fff7c0169dc) at /home/psergey/dev-git2/10.5-cp2/sql/sql_select.cc:731
 
  #21 0x0000555555eb25d2 in JOIN::prepare (this=0x7fff7c01a950, tables_init=0x7fff7c016c40, 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=0x7fff7c0166d0, unit_arg=0x7fff7c004f30) at /home/psergey/dev-git2/10.5-cp2/sql/sql_select.cc:1285
 
  #22 0x0000555555ebf28f in mysql_select (thd=0x7fff7c000d78, tables=0x7fff7c016c40, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fff7c01a928, unit=0x7fff7c004f30, select_lex=0x7fff7c0166d0) at /home/psergey/dev-git2/10.5-cp2/sql/sql_select.cc:4738

After Item **res= find_item_in_list() call we have

(gdb) p (*res)
 
  $250 = (Item_field *) 0x7fff7c0180b0
 
(gdb) p/x  (*res)->used_tables()
 
  $253 = 0x4000000000000000
 
(gdb) p (*res)->depended_from
 
  $254 = (st_select_lex *) 0x7fff7c0166d0

Then we execute:

            set_field(new_field);
 
            return 0;

This sets the pointer to Field but doesn't set depdended_from. get_depended_from() will still return NULL.
Because of that, used_tables() will not return OUTER_TABLE_REF for this object:

table_map Item_field::used_tables() const
 
{
 
  if (field->table->const_table)
 
    return 0;					// const item
 
  return (get_depended_from() ? OUTER_REF_TABLE_BIT : field->table->map);
 
}

Comment by Sergei Petrunia [ 2021-05-14 ]

http://lists.askmonty.org/pipermail/commits/2021-May/014604.html. sanja, please review.

Comment by Oleksandr Byelkin [ 2021-05-19 ]

OK to push

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