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

SHOW EXPLAIN: Server crashes in JOIN::print_explain on a query with inner join and ORDER BY the same column twice

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects versions: None
    • Fix versions: 10.0.0
    • Components: None
    • Labels:
      None
    • Sprint:

      Description

      Query:
      SELECT b AS field1, b AS field2 FROM t1, t2, t3 WHERE d = b ORDER BY field1, field2

      #3  <signal handler called>
      #4  __strlen_sse2 () at ../sysdeps/x86_64/multiarch/../strlen.S:32
      #5  0x0000000000671d86 in JOIN::print_explain (this=0x298de48, result=0x29eaca0, explain_flags=0 '\000', on_the_fly=true, need_tmp_table=false, need_order=true, distinct=false, message=0x0) at sql/sql_select.cc:21744
      #6  0x0000000000604d0c in st_select_lex::print_explain (this=0x297f7b8, output=0x29eaca0, explain_flags=0 '\000', printed_anything=0x7f7d3e1293ef) at sql/sql_lex.cc:4106
      #7  0x0000000000604f3b in st_select_lex_unit::print_explain (this=0x297f0e0, output=0x29eaca0, explain_flags=0 '\000', printed_anything=0x7f7d3e1293ef) at sql/sql_lex.cc:4168
      #8  0x0000000000682bf9 in Show_explain_request::call_in_target_thread (this=0x7f7d3e171360) at sql/sql_show.cc:2026
      #9  0x000000000075b1cc in Apc_target::process_apc_requests (this=0x297eff8) at sql/my_apc.cc:260
      #10 0x000000000067896c in THD::check_killed (this=0x297cd40) at sql/sql_class.h:2199
      #11 0x000000000066425c in evaluate_join_record (join=0x298de48, join_tab=0x2a09050, error=0) at sql/sql_select.cc:16235
      #12 0x00000000006640be in sub_select (join=0x298de48, join_tab=0x2a09050, end_of_records=false) at sql/sql_select.cc:16190
      #13 0x00000000006637e8 in do_select (join=0x298de48, fields=0x297f8c8, table=0x0, procedure=0x0) at sql/sql_select.cc:15821
      #14 0x00000000006443f4 in JOIN::exec_inner (this=0x298de48) at sql/sql_select.cc:2895
      #15 0x00000000006418eb in JOIN::exec (this=0x298de48) at sql/sql_select.cc:2216
      #16 0x0000000000644bf1 in mysql_select (thd=0x297cd40, rref_pointer_array=0x297fa18, tables=0x298b0d0, wild_num=0, fields=..., conds=0x298c4f0, og_num=2, order=0x298c768, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x298c998, unit=0x297f0e0, select_lex=0x297f7b8) at sql/sql_select.cc:3115
      #17 0x000000000063b980 in handle_select (thd=0x297cd40, lex=0x297f030, result=0x298c998, setup_tables_done_option=0) at sql/sql_select.cc:362
      #18 0x0000000000614b24 in execute_sqlcom_select (thd=0x297cd40, all_tables=0x298b0d0) at sql/sql_parse.cc:4643
      #19 0x000000000060d663 in mysql_execute_command (thd=0x297cd40) at sql/sql_parse.cc:2211
      #20 0x000000000061738b in mysql_parse (thd=0x297cd40, rawbuf=0x298ad28 "SELECT b AS field1, b AS field2 FROM t1, t2, t3 \nWHERE d = b ORDER BY field1, field2", length=84, parser_state=0x7f7d3e12a500) at sql/sql_parse.cc:5758
      #21 0x000000000060aab4 in dispatch_command (command=COM_QUERY, thd=0x297cd40, packet=0x29824e1 "", packet_length=84) at sql/sql_parse.cc:1056
      #22 0x0000000000609d6b in do_command (thd=0x297cd40) at sql/sql_parse.cc:795
      #23 0x000000000070bb4f in do_handle_one_connection (thd_arg=0x297cd40) at sql/sql_connect.cc:1253
      #24 0x000000000070b53a in handle_one_connection (arg=0x297cd40) at sql/sql_connect.cc:1168
      #25 0x0000000000bfcebf in pfs_spawn_thread (arg=0x29ab100) at storage/perfschema/pfs.cc:1015
      #26 0x00007f7d497cfefc in start_thread (arg=0x7f7d3e12b700) at pthread_create.c:304
      #27 0x00007f7d48b4259d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
      

      bzr version-info (5.5-show-explain-test1)

      revision-id: psergey@askmonty.org-20120724100253-2hl3p2am2v6hl65z
      date: 2012-07-24 14:02:53 +0400
      build-date: 2012-07-25 01:46:49 +0400
      revno: 3457
      

      Reproducible with the default optimizer_switch (below) as well as with all OFF values.
      Default optimizer_switch:

      index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off
      

      EXPLAIN with the default optimizer_switch:

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	100.00	Using filesort
      1	SIMPLE	t1	index	b	b	6	NULL	107	100.00	Using where; Using index
      1	SIMPLE	t3	ref	PRIMARY	PRIMARY	5	test.t1.b	1	100.00	Using index
      Warnings:
      Note	1003	select `test`.`t1`.`b` AS `field1`,`test`.`t1`.`b` AS `field2` from `test`.`t1` join `test`.`t3` where (`test`.`t3`.`d` = `test`.`t1`.`b`) order by `test`.`t1`.`b`,`test`.`t1`.`b`
      

      Test case:

      CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(3), KEY(b)) ENGINE=MyISAM;
      INSERT INTO t1 VALUES 
      (3795,'USA'),(3913,'USA'),(3846,'ITA'),(4021,'USA'),(4005,'RUS'),(4038,'USA'),
      (3825,'USA'),(3840,'USA'),(3987,'USA'),(3807,'USA'),(3896,'USA'),(4052,'USA'),
      (3973,'USA'),(3982,'ITA'),(3965,'USA'),(3852,'RUS'),(4006,'USA'),(3800,'USA'),
      (4020,'USA'),(4040,'USA'),(3916,'USA'),(3817,'USA'),(3885,'USA'),(3802,'USA'),
      (4009,'ITA'),(3895,'USA'),(3963,'RUS'),(4045,'USA'),(3988,'USA'),(3815,'USA'),
      (4063,'USA'),(3978,'USA'),(4019,'USA'),(3954,'USA'),(3950,'USA'),(3974,'ITA'),
      (4054,'USA'),(4061,'RUS'),(3976,'USA'),(3966,'USA'),(3957,'USA'),(3981,'USA'),
      (3923,'USA'),(3876,'USA'),(3819,'USA'),(3877,'USA'),(3829,'ITA'),(3964,'USA'),
      (4053,'RUS'),(3917,'USA'),(3874,'USA'),(4023,'USA'),(4001,'USA'),(3872,'USA'),
      (3890,'USA'),(3962,'USA'),(3886,'USA'),(4026,'ITA'),(3869,'USA'),(3937,'RUS'),
      (3975,'USA'),(3944,'USA'),(3908,'USA'),(3867,'USA'),(3947,'USA'),(3838,'USA'),
      (3796,'USA'),(3893,'USA'),(3920,'ITA'),(3994,'USA'),(3875,'RUS'),(4011,'USA'),
      (4013,'USA'),(3810,'USA'),(3834,'USA'),(3968,'USA'),(3931,'USA'),(3839,'USA'),
      (4042,'USA'),(4039,'ITA'),(3811,'USA'),(3837,'RUS'),(4041,'USA'),(3884,'USA'),
      (3894,'USA'),(3879,'USA'),(3942,'USA'),(3959,'USA'),(3814,'USA'),(4044,'USA'),
      (3971,'ITA'),(3823,'USA'),(3793,'RUS'),(3855,'USA'),(3905,'USA'),(3865,'USA'),
      (4046,'USA'),(3990,'USA'),(4022,'USA'),(3833,'USA'),(3918,'USA'),(4064,'ITA'),
      (3821,'USA'),(3836,'RUS'),(3921,'USA'),(3914,'USA'),(3888,'USA');
      
      CREATE TABLE t2 (c VARCHAR(3) PRIMARY KEY) ENGINE=MyISAM;
      INSERT INTO t2 VALUES ('USA');
      
      CREATE TABLE t3 (d VARCHAR(3), e VARCHAR(52), PRIMARY KEY (d,e)) ENGINE=MyISAM;
      INSERT INTO t3 VALUES 
      ('JPN','Japanese'),('KOR','Korean'),('POL','Polish'),('PRT','Portuguese'),
      ('ESP','Spanish'),('FRA','French'),('VNM','Vietnamese');
      
      --connect (con1,localhost,root,,)
      --let $con_id = `SELECT CONNECTION_ID()`
      
      --let $trials = 50
      
      --disable_query_log
      --disable_result_log
      
      while ($trials)
      {
        --dec $trials
        --let $run = 100
      
        send 
        SELECT b AS field1, b AS field2 FROM t1, t2, t3 
        WHERE d = b ORDER BY field1, field2;
      
        --connection default
        while ($run)
        {
          --error 0,1932
          eval SHOW EXPLAIN FOR $con_id;
          --dec $run
        }
      
        --connection con1
        --reap
      }
      
      --echo # Survived
      
      DROP TABLE t1, t2, t3;
      
      

        Attachments

          Issue links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 2 hours
                  2h