Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7823

Server crashes in next_depth_first_tab on nested IN clauses with SQ inside

    Details

    • Sprint:
      5.5.48-0

      Description

      Test case

      CREATE TABLE t1 (f1 INT);
      INSERT INTO t1 VALUES (1);
       
      CREATE TABLE t2 (f2 INT, KEY(f2));
      INSERT INTO t2 VALUES (8),(0);
       
      CREATE TABLE t3 (f3 INT);
      INSERT INTO t3 VALUES (1),(2);
       
      CREATE TABLE t4 (f4 INT);
      INSERT INTO t4 VALUES (0),(5);
       
      SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) );

      Stack trace from 5.5 commit 86f46a3da4a6d82cb510dc4c270d46cfd6a8965b

      #3  <signal handler called>
      #4  0x0000000000677d89 in next_depth_first_tab (join=0x7f1f53969d28, tab=0x7f1f53940c10) at 5.5/sql/sql_select.cc:7672
      #5  0x0000000000675df8 in JOIN::get_partial_cost_and_fanout (this=0x7f1f53969d28, end_tab_idx=61, filter_map=18446744073709551615, read_time_arg=0x7f1f543b3518, record_count_arg=0x7f1f543b3520) at 5.5/sql/sql_select.cc:6734
      #6  0x000000000077bf81 in JOIN::choose_subquery_plan (this=0x7f1f5396a6b8, join_tables=1) at 5.5/sql/opt_subselect.cc:5454
      #7  0x000000000066ed4a in make_join_statistics (join=0x7f1f5396a6b8, tables_list=..., conds=0x0, keyuse_array=0x7f1f5396a9b8) at 5.5/sql/sql_select.cc:3826
      #8  0x0000000000665bf2 in JOIN::optimize (this=0x7f1f5396a6b8) at 5.5/sql/sql_select.cc:1229
      #9  0x0000000000884178 in subselect_single_select_engine::exec (this=0x7f1f53a88d70) at 5.5/sql/item_subselect.cc:3143
      #10 0x000000000087d342 in Item_subselect::exec (this=0x7f1f53a88bd0) at 5.5/sql/item_subselect.cc:655
      #11 0x000000000087d86e in Item_in_subselect::exec (this=0x7f1f53a88bd0) at 5.5/sql/item_subselect.cc:828
      #12 0x000000000087fc93 in Item_in_subselect::val_bool (this=0x7f1f53a88bd0) at 5.5/sql/item_subselect.cc:1654
      #13 0x000000000059ebe1 in Item::val_bool_result (this=0x7f1f53a88bd0) at 5.5/sql/item.h:981
      #14 0x000000000081c289 in Item_in_optimizer::val_int (this=0x7f1f5396ac68) at 5.5/sql/item_cmpfunc.cc:1747
      #15 0x0000000000805914 in Item::save_in_field (this=0x7f1f5396ac68, field=0x7f1f53940f50, no_conversions=true) at 5.5/sql/item.cc:6059
      #16 0x00000000006a3dba in store_key_item::copy_inner (this=0x7f1f543b4170) at 5.5/sql/sql_select.h:1662
      #17 0x00000000006a39ad in store_key::copy (this=0x7f1f543b4170) at 5.5/sql/sql_select.h:1552
      #18 0x00000000006794b1 in create_ref_for_key (join=0x7f1f53969d28, j=0x7f1f539408f0, org_keyuse=0x7f1f53890660, allow_full_scan=true, used_tables=4611686018427387907) at 5.5/sql/sql_select.cc:8145
      #19 0x000000000067845b in get_best_combination (join=0x7f1f53969d28) at 5.5/sql/sql_select.cc:7817
      #20 0x000000000066edc5 in make_join_statistics (join=0x7f1f53969d28, tables_list=..., conds=0x7f1f53a88db0, keyuse_array=0x7f1f5396a028) at 5.5/sql/sql_select.cc:3832
      #21 0x0000000000665bf2 in JOIN::optimize (this=0x7f1f53969d28) at 5.5/sql/sql_select.cc:1229
      #22 0x000000000066c45b in mysql_select (thd=0x7f1f54950060, rref_pointer_array=0x7f1f54953cd0, tables=0x7f1f53a872c0, wild_num=1, fields=..., conds=0x7f1f53a88db0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f1f53a88fd0, unit=0x7f1f54953380, select_lex=0x7f1f54953a60) at 5.5/sql/sql_select.cc:3080
      #23 0x0000000000662fbd in handle_select (thd=0x7f1f54950060, lex=0x7f1f549532d0, result=0x7f1f53a88fd0, setup_tables_done_option=0) at 5.5/sql/sql_select.cc:319
      #24 0x000000000063c1fc in execute_sqlcom_select (thd=0x7f1f54950060, all_tables=0x7f1f53a872c0) at 5.5/sql/sql_parse.cc:4689
      #25 0x00000000006353de in mysql_execute_command (thd=0x7f1f54950060) at 5.5/sql/sql_parse.cc:2234
      #26 0x000000000063ece2 in mysql_parse (thd=0x7f1f54950060, rawbuf=0x7f1f53a87078 "SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) )", length=68, parser_state=0x7f1f543b5620) at 5.5/sql/sql_parse.cc:5909
      #27 0x0000000000632925 in dispatch_command (command=COM_QUERY, thd=0x7f1f54950060, packet=0x7f1f54a09061 "SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) )", packet_length=68) at 5.5/sql/sql_parse.cc:1079
      #28 0x0000000000631ab1 in do_command (thd=0x7f1f54950060) at 5.5/sql/sql_parse.cc:793
      #29 0x0000000000734122 in do_handle_one_connection (thd_arg=0x7f1f54950060) at 5.5/sql/sql_connect.cc:1266
      #30 0x0000000000733be1 in handle_one_connection (arg=0x7f1f54950060) at 5.5/sql/sql_connect.cc:1181
      #31 0x0000000000b6c629 in pfs_spawn_thread (arg=0x7f1f54971fc0) at 5.5/storage/perfschema/pfs.cc:1015
      #32 0x00007f1f5ac7cb50 in start_thread (arg=<optimized out>) at pthread_create.c:304
      #33 0x00007f1f58f3270d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112

      EXPLAIN EXTENDED
      SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) );
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	100.00	
      1	PRIMARY	t2	ref	f2	f2	5	const	0	0.00	Using where; Using index
      1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer (flat, BNL join)
      2	MATERIALIZED	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	
      Warnings:
      Note	1003	select 1 AS `f1`,`test`.`t2`.`f2` AS `f2`,`test`.`t3`.`f3` AS `f3` from `test`.`t2` join `test`.`t3` where (`test`.`t2`.`f2` = <cache>(<in_optimizer>(1,1 in ( <materialize> (select `test`.`t4`.`f4` from `test`.`t4` ), <primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `<subquery2>`.`f4`)))))))

      The problem appeared in 5.3 tree with the following revision:

      revno: 3765
      revision-id: igor@askmonty.org-20140221052733-ypg4dpfcmy5l0b3q
      parent: psergey@askmonty.org-20140219143412-rp3flx1pmhw6zawn
      committer: Igor Babaev <igor@askmonty.org>
      branch nick: maria-5.3
      timestamp: Thu 2014-02-20 21:27:33 -0800
      message:
        After constant row substitution the optimizer should call the method
        update_used_tables for the the where condition to update cached
        indicators of constant subexpressions. It should be done before further
        possible simplification of the where condition.
        
        This change caused simplification of the executed where conditions 
        in many test cases.

        Attachments

          Activity

            People

            • Assignee:
              psergey Sergei Petrunia
              Reporter:
              elenst Elena Stepanova
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: