|
#4 0x00000000007a85b6 in JOIN::print_explain (this=0x1d40278, result=0x1cb3ab8,
|
explain_flags=0 '\000', on_the_fly=true, need_tmp_table=false, need_order=false,
|
distinct=false, message=0x0) at sql_select.cc:21247
|
#5 0x00000000005d6106 in st_select_lex::print_explain (this=0x1cf24b0, output=0x1cb3ab8,
|
explain_flags=0 '\000') at sql_lex.cc:3769
|
#6 0x00000000005d6279 in st_select_lex_unit::print_explain (this=0x1d3baf0, output=0x1cb3ab8,
|
explain_flags=0 '\000') at sql_lex.cc:3820
|
#7 0x00000000005d6172 in st_select_lex::print_explain (this=0x1cf14a0, output=0x1cb3ab8,
|
explain_flags=0 '\000') at sql_lex.cc:3784
|
#8 0x00000000005d6279 in st_select_lex_unit::print_explain (this=0x1cf17f0, output=0x1cb3ab8,
|
explain_flags=0 '\000') at sql_lex.cc:3820
|
#9 0x00000000005d6172 in st_select_lex::print_explain (this=0x1ce4c68, output=0x1cb3ab8,
|
explain_flags=0 '\000') at sql_lex.cc:3784
|
#10 0x00000000005d6279 in st_select_lex_unit::print_explain (this=0x1ce4760, output=0x1cb3ab8,
|
explain_flags=0 '\000') at sql_lex.cc:3820
|
#11 0x00000000006ccbb1 in Show_explain_request::get_explain_data (arg=0x7f093ae47ba0)
|
at sql_class.cc:3042
|
#12 0x000000000095b261 in Apc_target::process_apc_requests (this=0x1ce44d0) at my_apc.cc:255
|
#13 0x00000000006c8a1c in THD::check_killed (this=0x1ce21b8) at sql_class.cc:1904
|
#14 0x000000000079a59f in evaluate_join_record (join=0x1d3d488, join_tab=0x1d42f58, error=0)
|
at sql_select.cc:15829
|
#15 0x000000000079a204 in sub_select (join=0x1d3d488, join_tab=0x1d42f58, end_of_records=false)
|
at sql_select.cc:15744
|
#16 0x00000000007999d5 in do_select (join=0x1d3d488, fields=0x1d3d818, table=0x0, procedure=0x0)
|
at sql_select.cc:15405
|
#17 0x000000000077aa3a in JOIN::exec_inner (this=0x1d3d488) at sql_select.cc:2810
|
#18 0x0000000000777e1d in JOIN::exec (this=0x1d3d488) at sql_select.cc:2125
|
#19 0x0000000000685dbb in subselect_single_select_engine::exec (this=0x1d3c900)
|
at item_subselect.cc:3006
|
#20 0x000000000067eae9 in Item_subselect::exec (this=0x1d3c7e8) at item_subselect.cc:587
|
#21 0x0000000000680143 in Item_singlerow_subselect::val_int (this=0x1d3c7e8)
|
at item_subselect.cc:1101
|
#22 0x000000000063a6a6 in Arg_comparator::compare_int_signed (this=0x1d3ca00)
|
at item_cmpfunc.cc:1167
|
#23 0x000000000061b590 in Arg_comparator::compare (this=0x1d3ca00) at item_cmpfunc.h:72
|
#24 0x000000000063c5a6 in Item_func_eq::val_int (this=0x1d3c940) at item_cmpfunc.cc:1851
|
#25 0x000000000079a63e in evaluate_join_record (join=0x1d3cb78, join_tab=0x1d415a8, error=0)
|
at sql_select.cc:15840
|
#26 0x000000000079a204 in sub_select (join=0x1d3cb78, join_tab=0x1d415a8, end_of_records=false)
|
at sql_select.cc:15744
|
#27 0x00000000007999d5 in do_select (join=0x1d3cb78, fields=0x1ce4d70, table=0x0, procedure=0x0)
|
at sql_select.cc:15405
|
#28 0x000000000077aa3a in JOIN::exec_inner (this=0x1d3cb78) at sql_select.cc:2810
|
#29 0x0000000000777e1d in JOIN::exec (this=0x1d3cb78) at sql_select.cc:2125
|
#30 0x000000000077b2d3 in mysql_select (thd=0x1ce21b8, rref_pointer_array=0x1ce4ec0,
|
tables=0x1cf0f88, wild_num=1, fields=..., conds=0x1d3c940, og_num=0, order=0x0, group=0x0,
|
having=0x0, proc_param=0x0, select_options=2147764736, result=0x1d3cb58, unit=0x1ce4760,
|
select_lex=0x1ce4c68) at sql_select.cc:3031
|
#31 0x0000000000771d4e in handle_select (thd=0x1ce21b8, lex=0x1ce46c0, result=0x1d3cb58,
|
setup_tables_done_option=0) at sql_select.cc:332
|
#32 0x00000000007002c5 in execute_sqlcom_select (thd=0x1ce21b8, all_tables=0x1cf0f88)
|
at sql_parse.cc:5178
|
#33 0x00000000006f720b in mysql_execute_command (thd=0x1ce21b8) at sql_parse.cc:2285
|
#34 0x0000000000702ca0 in mysql_parse (thd=0x1ce21b8,
|
rawbuf=0x1cf0d00 "SELECT * FROM C WHERE a = ( SELECT MAX(a) FROM C\nWHERE pk = ( SELECT MAX(pk) FROM C WHERE pk = 3 ) )", length=100, found_semicolon=0x7f093ae00c98) at sql_parse.cc:6179
|
#35 0x00000000006f49ba in dispatch_command (command=COM_QUERY, thd=0x1ce21b8, packet=0x1ce6359 "",
|
packet_length=101) at sql_parse.cc:1229
|
#36 0x00000000006f3cee in do_command (thd=0x1ce21b8) at sql_parse.cc:924
|
#37 0x00000000006f0b32 in handle_one_connection (arg=0x1ce21b8) at sql_connect.cc:1218
|
bzr version-info
revision-id: psergey@askmonty.org-20120514183900-rvddjradoeiynwif
|
date: 2012-05-14 22:39:00 +0400
|
revno: 3510
|
Normal EXPLAIN from the before executing the query:
id select_type table type possible_keys key key_len ref rows filtered
|
Extra
|
1 PRIMARY C ALL NULL NULL NULL NULL 32768 100.00 Using where
|
2 SUBQUERY C const PRIMARY PRIMARY 4 const 1 100.00 Using where
|
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables
|
optimized away
|
Warnings:
|
Note 1003 select `test`.`C`.`pk` AS `pk`,`test`.`C`.`a` AS `a` from `test`.`C` where (`test`.`C`.`a` = (select max(`test`.`C`.`a`) from `test`.`C` where (`test`.`C`.`pk` = (select max(`test`.`C`.`pk`) from `test`.`C` where multiple equal(3, `test`.`C`.`pk`)))))
|
Reproducible with the default optimizer_switch as well as with all OFFs except for in_to_exists which is required to execute the query.
MTR test case:
|
CREATE TABLE C (pk INT PRIMARY KEY AUTO_INCREMENT, a INT ) ENGINE=MyISAM ;
|
INSERT INTO C (a) VALUES
|
(4),(6),(7),(1),(0),(7),(7),(1),
|
(5),(2),(0),(1),(8),(1),(1),(9);
|
INSERT INTO C SELECT NULL, a FROM C;
|
INSERT INTO C SELECT NULL, a FROM C;
|
INSERT INTO C SELECT NULL, a FROM C;
|
INSERT INTO C SELECT NULL, a FROM C;
|
INSERT INTO C SELECT NULL, a FROM C;
|
INSERT INTO C SELECT NULL, a FROM C;
|
INSERT INTO C SELECT NULL, a FROM C;
|
INSERT INTO C SELECT NULL, a FROM C;
|
INSERT INTO C SELECT NULL, a FROM C;
|
INSERT INTO C SELECT NULL, a FROM C;
|
INSERT INTO C SELECT NULL, a FROM C;
|
|
--connect (con1,localhost,root,,)
|
--let $con_id = `SELECT CONNECTION_ID()`
|
--send
|
SELECT * FROM C WHERE a = ( SELECT MAX(a) FROM C
|
WHERE pk = ( SELECT MAX(pk) FROM C WHERE pk = 3 ) ) ;
|
|
--connection default
|
--let $run = 100
|
|
while ($run)
|
{
|
--error 0,1220
|
--eval SHOW EXPLAIN FOR $con_id
|
--dec $run
|
}
|
|
--connection con1
|
--reap
|
In case you cannot reproduce it with the MTR case, you might want to try RQG.
RQG grammar (test.yy):
query_init:
|
CREATE TABLE C (`pk` INT PRIMARY KEY, `a` INT ) ENGINE=MyISAM ; INSERT INTO C
|
VALUES (1,4),(2,62),(3,7),(4,1),(5,0),(6,7),(7,7),(8,1),(9,7),(10,1),
|
(11,5),(12,2),(13,0),(14,1),(15,8),(16,1),(17,1),(18,9),(19,1),(20,5) ;
|
|
thread2:
|
SHOW EXPLAIN FOR @con_id ;
|
|
thread2_init:
|
SET @con_id = CONNECTION_ID()-1;
|
|
query:
|
SELECT * FROM C WHERE `a` = ( SELECT MAX(`a`) FROM C
|
WHERE `pk` = ( SELECT MAX(`pk`) FROM C WHERE `pk` = 3 ) ) ;
|
Run as:
perl runall.pl \
|
--threads=2 \
|
--duration=600 \
|
--queries=100M \
|
--skip-gendata \
|
--grammar=test.yy \
|
--basedir1=<your basedir> \
|
--vardir1=<your vardir>
|
|