We're updating the issue view to help you get more done. 

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

Description

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 #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)

1 2 3 4 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:

1 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:

1 2 3 4 5 6 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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 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;

Environment

None

Status

Assignee

Sergei Petrunia

Reporter

Elena Stepanova

Fix versions

Priority

Major