Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.3.3, 5.3.4
-
None
-
None
-
None
-
bzr version-info
revision-id: igor@askmonty.org-20120103040636-nc6o55vsxqadd1n0
date: 2012-01-02 20:06:36 -0800
build-date: 2012-01-05 15:17:09 +0200
revno: 3376
branch-nick: maria-5.3
Description
I'm leaving it 'Major' but not 'Critical' because the query in the test case is not very smart (due to the mix of aggregate and non-aggregate without a group by), although quite simple.
|
|
#2 0x00000000006e1cc0 in handle_segfault (sig=11) at mysqld.cc:2838
|
#3 <signal handler called>
|
#4 0x00000000008a7a20 in do_copy_not_null (copy=0x1cef128) at field_conv.cc:255
|
#5 0x00000000007ab08e in store_key_field::copy_inner (this=0x1cef100) at sql_select.h:1521
|
#6 0x000000000068cd2f in store_key::copy (this=0x1cef100) at sql_select.h:1462
|
#7 0x00000000007a083a in cp_buffer_from_ref (thd=0x1c2cbd8, table=0x1ce0608, ref=0x1cf1000)
|
at sql_select.cc:18791
|
#8 0x0000000000798ee8 in join_read_always_key (tab=0x1cf0df0) at sql_select.cc:15984
|
#9 0x000000000079747c in sub_select (join=0x1d37350, join_tab=0x1cf0df0, end_of_records=false)
|
at sql_select.cc:15301
|
#10 0x0000000000797c8e in evaluate_join_record (join=0x1d37350, join_tab=0x1cf0ad0, error=0)
|
at sql_select.cc:15501
|
#11 0x00000000007974d8 in sub_select (join=0x1d37350, join_tab=0x1cf0ad0, end_of_records=false)
|
at sql_select.cc:15304
|
#12 0x0000000000797c8e in evaluate_join_record (join=0x1d37350, join_tab=0x1cf07b0, error=0)
|
at sql_select.cc:15501
|
#13 0x00000000007974d8 in sub_select (join=0x1d37350, join_tab=0x1cf07b0, end_of_records=false)
|
at sql_select.cc:15304
|
#14 0x0000000000796ca6 in do_select (join=0x1d37350, fields=0x1d3f2b8, table=0x0, procedure=0x0)
|
at sql_select.cc:14965
|
#15 0x0000000000778872 in JOIN::exec (this=0x1d37350) at sql_select.cc:2704
|
#16 0x0000000000779109 in mysql_select (thd=0x1c2cbd8, rref_pointer_array=0x1c2f7a8,
|
tables=0x1cb1438, wild_num=0, fields=..., conds=0x1ced8b0, og_num=0, order=0x0, group=0x0,
|
having=0x0, proc_param=0x0, select_options=2147764736, result=0x1cedad8, unit=0x1c2f070,
|
select_lex=0x1c2f558) at sql_select.cc:2925
|
#17 0x000000000076fd8d in handle_select (thd=0x1c2cbd8, lex=0x1c2efd0, result=0x1cedad8,
|
setup_tables_done_option=0) at sql_select.cc:283
|
#18 0x00000000006feb20 in execute_sqlcom_select (thd=0x1c2cbd8, all_tables=0x1cb1438)
|
at sql_parse.cc:5148
|
#19 0x00000000006f5bdd in mysql_execute_command (thd=0x1c2cbd8) at sql_parse.cc:2281
|
#20 0x00000000007014fb in mysql_parse (thd=0x1c2cbd8,
|
rawbuf=0x1cb0ff0 "SELECT a, COUNT(*) FROM t1\nWHERE a IN ( \nSELECT b FROM t2, t3 \nWHERE c = b AND b = a\n)", length=86, found_semicolon=0x7f8de4905c98) at sql_parse.cc:6149
|
#21 0x00000000006f33b2 in dispatch_command (command=COM_QUERY, thd=0x1c2cbd8, packet=0x1ca7b89 "",
|
packet_length=86) at sql_parse.cc:1227
|
#22 0x00000000006f26e6 in do_command (thd=0x1c2cbd8) at sql_parse.cc:922
|
#23 0x00000000006ef60e in handle_one_connection (arg=0x1c2cbd8) at sql_connect.cc:1193
|
#24 0x00007f8dee0a3a4f in start_thread () from /lib64/libpthread.so.0
|
#25 0x00007f8ded48d82d in clone () from /lib64/libc.so.6
|
|
|
Minimal optimizer_switch: firstmatch=on,semijoin=on (current defaults)
Full optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=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
EXPLAIN:
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
|
1 PRIMARY t2 ref b b 3 test.t1.a 2 100.00 Using index
|
1 PRIMARY t3 ref c c 3 test.t1.a 2 100.00 Using index; FirstMatch(t1)
|
Warnings:
|
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
|
Note 1003 select `test`.`t1`.`a` AS `a`,count(0) AS `COUNT(*)` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where ((`test`.`t2`.`b` = `test`.`t1`.`a`) and (`test`.`t3`.`c` = `test`.`t1`.`a`))
|
SELECT a, COUNT(*) FROM t1
|
WHERE a IN (
|
SELECT b FROM t2, t3
|
WHERE c = b AND b = a
|
);
|
|
|
Reproducible on 5.3.3 release binaries.
Not reproducible on 5.2.10.
Could not reproduce on current 5.5, it chooses a different plan.
Test case:
SET optimizer_switch = 'firstmatch=on,semijoin=on';
|
|
|
CREATE TABLE t1 ( a VARCHAR(1) NOT NULL );
|
INSERT INTO t1 VALUES ('k'),('l');
|
|
|
CREATE TABLE t2 ( b VARCHAR(1) NOT NULL, KEY(b) );
|
INSERT INTO t2 VALUES ('k'),('l');
|
|
|
CREATE TABLE t3 ( c VARCHAR(1) NOT NULL, KEY(c) );
|
INSERT INTO t3 VALUES ('m'),('n');
|
|
|
SELECT a, COUNT(*) FROM t1
|
WHERE a IN (
|
SELECT b FROM t2, t3
|
WHERE c = b AND b = a
|
);
|