| 2012-03-11 16:57:34 |
Elena Stepanova |
description |
#4 <signal handler called>
#5 0x081fa0b0 in Field::is_null (this=0x0, row_offset=0) at field.h:337
#6 0x08207327 in save_field_in_field (from=0x0, null_value=0x9478920, to=0x9460ae0, no_conversions=true)
at item.cc:5391
#7 0x08207466 in Item_field::save_in_field (this=0x94788e0, to=0x9460ae0, no_conversions=true)
at item.cc:5426
#8 0x0838700b in store_key_item::copy_inner (this=0x9460ac0) at sql_select.h:1583
#9 0x08287540 in store_key::copy (this=0x9460ac0) at sql_select.h:1477
#10 0x0837d7f1 in cp_buffer_from_ref (thd=0x93f04c8, table=0x94729a0, ref=0x9460184) at sql_select.cc:18918
#11 0x0837d746 in cmp_buffer_with_ref (thd=0x93f04c8, table=0x94729a0, tab_ref=0x9460184)
at sql_select.cc:18900
#12 0x0837665c in join_read_key2 (thd=0x93f04c8, tab=0x9460028, table=0x94729a0, table_ref=0x9460184)
at sql_select.cc:16020
#13 0x083765f2 in join_read_key (tab=0x9460028) at sql_select.cc:16003
#14 0x083751b7 in sub_select (join=0x94b9318, join_tab=0x9460028, end_of_records=false) at sql_select.cc:15429
#15 0x08329d3f in JOIN_CACHE::generate_full_extensions (this=0x9460988,
rec_ptr=0x94c6300 "\376\001y\376\001z") at sql_join_cache.cc:2375
#16 0x08329a99 in JOIN_CACHE::join_matching_records (this=0x9460988, skip_last=false)
at sql_join_cache.cc:2267
#17 0x08329569 in JOIN_CACHE::join_records (this=0x9460988, skip_last=false) at sql_join_cache.cc:2064
#18 0x08374d7c in sub_select_cache (join=0x94b9318, join_tab=0x945fe20, end_of_records=true)
at sql_select.cc:15211
#19 0x08374f8e in sub_select (join=0x94b9318, join_tab=0x945fc18, end_of_records=true) at sql_select.cc:15373
#20 0x08374a88 in do_select (join=0x94b9318, fields=0x0, table=0x94e8318, procedure=0x0)
at sql_select.cc:15095
#21 0x08357167 in JOIN::exec (this=0x94b9318) at sql_select.cc:2289
#22 0x08359301 in mysql_select (thd=0x93f04c8, rref_pointer_array=0x9477d20, tables=0x9478410, wild_num=0,
fields=..., conds=0x945ed00, og_num=1, order=0x0, group=0x9479538, having=0x0, proc_param=0x0,
select_options=2416200192, result=0x9479568, unit=0x9477904, select_lex=0x9477be4) at sql_select.cc:2952
#23 0x08350f7f in handle_select (thd=0x93f04c8, lex=0x94778a8, result=0x9479568, setup_tables_done_option=0)
at sql_select.cc:285
#24 0x082ec085 in execute_sqlcom_select (thd=0x93f04c8, all_tables=0x9478410) at sql_parse.cc:5151
#25 0x082e3461 in mysql_execute_command (thd=0x93f04c8) at sql_parse.cc:2284
#26 0x083a5bb6 in Prepared_statement::execute (this=0x946fe38, expanded_query=0xae85c71c, open_cursor=false)
at sql_prepare.cc:3732
#27 0x083a50cb in Prepared_statement::execute_loop (this=0x946fe38, expanded_query=0xae85c71c,
open_cursor=false, packet=0x0, packet_end=0x0) at sql_prepare.cc:3413
#28 0x083a3993 in mysql_sql_stmt_execute (thd=0x93f04c8) at sql_prepare.cc:2638
#29 0x082e3487 in mysql_execute_command (thd=0x93f04c8) at sql_parse.cc:2293
#30 0x082ee6c0 in mysql_parse (thd=0x93f04c8, rawbuf=0x945ec00 "EXECUTE ps", length=10,
found_semicolon=0xae85d234) at sql_parse.cc:6152
#31 0x082e10a1 in dispatch_command (command=COM_QUERY, thd=0x93f04c8, packet=0x9449311 "EXECUTE ps",
packet_length=10) at sql_parse.cc:1228
#32 0x082e054b in do_command (thd=0x93f04c8) at sql_parse.cc:923
#33 0x082dd4d1 in handle_one_connection (arg=0x93f04c8) at sql_connect.cc:1193
#34 0xb7672b25 in start_thread () from /lib/libpthread.so.0
bzr version-info
revision-id: igor@askmonty.org-20120306063346-alx4mtlc73caqmia
date: 2012-03-05 22:33:46 -0800
build-date: 2012-03-11 19:05:15 +0400
revno: 3455
Also reproducible on MariaDB 5.5 (revno 3316) and on the release build of 5.3.5.
Not reproducible on MySQL 5.6 (trunk, revno 3706).
EXPLAIN:
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 100.00
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) join `test`.`t2` where (`test`.`t2`.`c` = `test`.`t2`.`b`) group by `test`.`t1`.`a`
Minimal optimizer_switch: materialization=on,semijoin=on
Full optimizer_switch (default): 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
Test case:
SET optimizer_switch = 'materialization=on,semijoin=on';
CREATE TABLE t1 ( a VARCHAR(1) );
INSERT INTO t1 VALUES ('y'),('z');
CREATE TABLE t2 ( b VARCHAR(1), c VARCHAR(1) );
INSERT INTO t2 VALUES ('v','v'),('v','v');
CREATE VIEW v2 AS SELECT * FROM t2;
PREPARE ps FROM '
SELECT a FROM t1, v2
WHERE ( c, b ) IN ( SELECT b, b FROM t2 )
GROUP BY a ';
EXECUTE ps;
EXECUTE ps;
# End of test case |
See also https://bugs.launchpad.net/maria/+bug/952372, they might be related, both came from the same original query.
#4 <signal handler called>
#5 0x081fa0b0 in Field::is_null (this=0x0, row_offset=0) at field.h:337
#6 0x08207327 in save_field_in_field (from=0x0, null_value=0x9478920, to=0x9460ae0, no_conversions=true)
at item.cc:5391
#7 0x08207466 in Item_field::save_in_field (this=0x94788e0, to=0x9460ae0, no_conversions=true)
at item.cc:5426
#8 0x0838700b in store_key_item::copy_inner (this=0x9460ac0) at sql_select.h:1583
#9 0x08287540 in store_key::copy (this=0x9460ac0) at sql_select.h:1477
#10 0x0837d7f1 in cp_buffer_from_ref (thd=0x93f04c8, table=0x94729a0, ref=0x9460184) at sql_select.cc:18918
#11 0x0837d746 in cmp_buffer_with_ref (thd=0x93f04c8, table=0x94729a0, tab_ref=0x9460184)
at sql_select.cc:18900
#12 0x0837665c in join_read_key2 (thd=0x93f04c8, tab=0x9460028, table=0x94729a0, table_ref=0x9460184)
at sql_select.cc:16020
#13 0x083765f2 in join_read_key (tab=0x9460028) at sql_select.cc:16003
#14 0x083751b7 in sub_select (join=0x94b9318, join_tab=0x9460028, end_of_records=false) at sql_select.cc:15429
#15 0x08329d3f in JOIN_CACHE::generate_full_extensions (this=0x9460988,
rec_ptr=0x94c6300 "\376\001y\376\001z") at sql_join_cache.cc:2375
#16 0x08329a99 in JOIN_CACHE::join_matching_records (this=0x9460988, skip_last=false)
at sql_join_cache.cc:2267
#17 0x08329569 in JOIN_CACHE::join_records (this=0x9460988, skip_last=false) at sql_join_cache.cc:2064
#18 0x08374d7c in sub_select_cache (join=0x94b9318, join_tab=0x945fe20, end_of_records=true)
at sql_select.cc:15211
#19 0x08374f8e in sub_select (join=0x94b9318, join_tab=0x945fc18, end_of_records=true) at sql_select.cc:15373
#20 0x08374a88 in do_select (join=0x94b9318, fields=0x0, table=0x94e8318, procedure=0x0)
at sql_select.cc:15095
#21 0x08357167 in JOIN::exec (this=0x94b9318) at sql_select.cc:2289
#22 0x08359301 in mysql_select (thd=0x93f04c8, rref_pointer_array=0x9477d20, tables=0x9478410, wild_num=0,
fields=..., conds=0x945ed00, og_num=1, order=0x0, group=0x9479538, having=0x0, proc_param=0x0,
select_options=2416200192, result=0x9479568, unit=0x9477904, select_lex=0x9477be4) at sql_select.cc:2952
#23 0x08350f7f in handle_select (thd=0x93f04c8, lex=0x94778a8, result=0x9479568, setup_tables_done_option=0)
at sql_select.cc:285
#24 0x082ec085 in execute_sqlcom_select (thd=0x93f04c8, all_tables=0x9478410) at sql_parse.cc:5151
#25 0x082e3461 in mysql_execute_command (thd=0x93f04c8) at sql_parse.cc:2284
#26 0x083a5bb6 in Prepared_statement::execute (this=0x946fe38, expanded_query=0xae85c71c, open_cursor=false)
at sql_prepare.cc:3732
#27 0x083a50cb in Prepared_statement::execute_loop (this=0x946fe38, expanded_query=0xae85c71c,
open_cursor=false, packet=0x0, packet_end=0x0) at sql_prepare.cc:3413
#28 0x083a3993 in mysql_sql_stmt_execute (thd=0x93f04c8) at sql_prepare.cc:2638
#29 0x082e3487 in mysql_execute_command (thd=0x93f04c8) at sql_parse.cc:2293
#30 0x082ee6c0 in mysql_parse (thd=0x93f04c8, rawbuf=0x945ec00 "EXECUTE ps", length=10,
found_semicolon=0xae85d234) at sql_parse.cc:6152
#31 0x082e10a1 in dispatch_command (command=COM_QUERY, thd=0x93f04c8, packet=0x9449311 "EXECUTE ps",
packet_length=10) at sql_parse.cc:1228
#32 0x082e054b in do_command (thd=0x93f04c8) at sql_parse.cc:923
#33 0x082dd4d1 in handle_one_connection (arg=0x93f04c8) at sql_connect.cc:1193
#34 0xb7672b25 in start_thread () from /lib/libpthread.so.0
bzr version-info
revision-id: igor@askmonty.org-20120306063346-alx4mtlc73caqmia
date: 2012-03-05 22:33:46 -0800
build-date: 2012-03-11 19:05:15 +0400
revno: 3455
Also reproducible on MariaDB 5.5 (revno 3316) and on the release build of 5.3.5.
Not reproducible on MySQL 5.6 (trunk, revno 3706).
EXPLAIN:
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 100.00
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) join `test`.`t2` where (`test`.`t2`.`c` = `test`.`t2`.`b`) group by `test`.`t1`.`a`
Minimal optimizer_switch: materialization=on,semijoin=on
Full optimizer_switch (default): 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
Test case:
SET optimizer_switch = 'materialization=on,semijoin=on';
CREATE TABLE t1 ( a VARCHAR(1) );
INSERT INTO t1 VALUES ('y'),('z');
CREATE TABLE t2 ( b VARCHAR(1), c VARCHAR(1) );
INSERT INTO t2 VALUES ('v','v'),('v','v');
CREATE VIEW v2 AS SELECT * FROM t2;
PREPARE ps FROM '
SELECT a FROM t1, v2
WHERE ( c, b ) IN ( SELECT b, b FROM t2 )
GROUP BY a ';
EXECUTE ps;
EXECUTE ps;
# End of test case |
|
