Details
Major
Description
Running the following test
SET optimizer_switch = 'materialization=on,semijoin=on'; CREATE TABLE t1 (pk INT, a INT, b INT, PRIMARY KEY(pk)) ENGINE=MyISAM; INSERT INTO t1 VALUES (1,3,5),(2,4,6); SELECT * FROM t1 WHERE 8 IN (SELECT MIN(pk) FROM t1) AND (pk = a OR pk = b); DROP TABLE t1;
produces a warning:
130912 10:35:46 [Warning] Plugin 'MEMORY' will be forced to shutdown 130912 10:35:46 [ERROR] Plugin 'MEMORY' has ref_count=1 after shutdown.
Gliffy Diagrams
Attachments
Issue Links
- is part of
-
MDEV-4908 Assertion `((Item_cond *) cond)->functype() == ((Item_cond *) new_item)->functype()' fails on a query with IN and equal conditions, AND/OR, materialization+semijoin
-
- Closed
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
I was experimenting with this testcase:
SET optimizer_switch = 'materialization=on,semijoin=on';
create table t1 (a int, b int) engine=myisam;
create table t2 (a int, b int) engine=myisam;
insert into t1 values (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
insert into t2 select * from t1;
explain
select * from t2
where
t2.a in (select max(b)
from t1 where a=3 and a=4
group by a) and b=10 and b=11 ;
DROP TABLE t1,t2;
The warning is produced when both the subquery and the upper query have "Impossible WHERE" :
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
2 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
If I comment out either "a=3 and a=4" or "b=10 and b=11", the warning goes away.
Let's see where the temp.table is supposed to be freed.
I run the EXPLAIN for query with "and b=10 and b=11" commented out, and I
observe:
Breakpoint 2, setup_jtbm_semi_joins (join=0x7fff10026ae0, join_list=0xa33cc80, join_where=0x7fff10026ec8) at opt_subselect.cc:5173
(gdb) c
Continuing.
Breakpoint 1, create_tmp_table (thd=0xa33a078, param=0x7ffff4195310, fields=..., group=0x0, distinct=true, save_sum_fields=true, select_options=2147768832, rows_limit=18446744073709551615, table_alias=0xd23c12 "dummy", do_not_open=true) at sql_select.cc:14181
(gdb) c
Continuing.
Breakpoint 5, JOIN::cleanup (this=0x7fff10026ae0, full=true) at sql_select.cc:10426
(gdb) c
Continuing.
Breakpoint 3, free_tmp_table (thd=0xa33a078, entry=0x7fff1002b350) at sql_select.cc:15605
(gdb) wher
#0 free_tmp_table (thd=0xa33a078, entry=0x7fff1002b350) at sql_select.cc:15605
#1 0x0000000000718c30 in st_join_table::cleanup (this=0x7fff100299f0) at sql_select.cc:10128
#2 0x0000000000719774 in JOIN::cleanup (this=0x7fff10026ae0, full=true) at sql_select.cc:10444
#3 0x0000000000706c11 in JOIN::destroy (this=0x7fff10026ae0) at sql_select.cc:2838
#4 0x0000000000886b4b in st_select_lex::cleanup (this=0xa33cb10) at sql_union.cc:963
#5 0x0000000000886708 in st_select_lex_unit::cleanup (this=0xa33c608) at sql_union.cc:828
#6 0x000000000068f88e in mysql_parse (thd=0xa33a078, rawbuf=0x7fff100094f0 "explain \nselect * from t2 \nwhere \n t2.a in (select max(b) \n", ' ' <repeats 11 times>, "from t1 where a=3 and a=4 \n", ' ' <repeats 11 times>, "group by a)", length=120, found_semicolon=0x7ffff4196c98) at sql_parse.cc:6190
#7 0x0000000000681740 in dispatch_command (command=COM_QUERY, thd=0xa33a078, packet=0xa3b50c9 "", packet_length=120) at sql_parse.cc:1243
#8 0x0000000000680a2c in do_command (thd=0xa33a078) at sql_parse.cc:923
#9 0x000000000067d8c6 in handle_one_connection (arg=0xa33a078) at sql_connect.cc:1231
#10 0x0000003509c07d90 in start_thread (arg=0x7ffff4197700) at pthread_create.c:309
#11 0x00000035098f0f5d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:115
(gdb)
If I run SELECT (not EXPLAIN), I see:
Breakpoint 3, free_tmp_table (thd=0xa33a078, entry=0x7fff1002b350) at sql_select.cc:15605
(gdb) wher
#0 free_tmp_table (thd=0xa33a078, entry=0x7fff1002b350) at sql_select.cc:15605
#1 0x0000000000718c30 in st_join_table::cleanup (this=0x7fff10029370) at sql_select.cc:10128
#2 0x0000000000719774 in JOIN::cleanup (this=0x7fff10026430, full=true) at sql_select.cc:10444
#3 0x0000000000719471 in JOIN::join_free (this=0x7fff10026430) at sql_select.cc:10364
#4 0x000000000071a57d in return_zero_rows (join=0x7fff10026430, result=0x7fff10026410, tables=..., fields=..., send_row=false, select_options=2147764736, info=0xcffcc8 "no matching row in const table", having=0x0, all_fields=...) at sql_select.cc:10777
#5 0x0000000000704601 in JOIN::exec (this=0x7fff10026430) at sql_select.cc:2215
#6 0x00000000007071c5 in mysql_select (thd=0xa33a078, rref_pointer_array=0xa33cd68, tables=0x7fff10009788, wild_num=1, fields=..., conds=0x7fff10026200, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x7fff10026410, unit=0xa33c608, select_lex=0xa33cb10) at sql_select.cc:2999
#7 0x00000000006fdd39 in handle_select (thd=0xa33a078, lex=0xa33c568, result=0x7fff10026410, setup_tables_done_option=0) at sql_select.cc:288
...
The EXPLAIN for the above query was:
---------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---------------------------------------------------------------------------------+
| 1 | PRIMARY | <subquery2> | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 10 | |
| 2 | MATERIALIZED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
---------------------------------------------------------------------------------+
It shows that the subquery has been converted to a non-merged semi-join. The stack traces show that the temp.table is cleaned up when parent join's JOIN_TABs are cleaned up.
If I run the query with both "a=3 and a=4" and "b=10 and b=11" present, I observe that
- setup_jtbm_semi_joins is called, it calls create[dummy]tmp_table().
- JOIN::cleanup (this=
{parent_join}
, full=true) is called. However, inside JOIN::cleanup() we have JOIN::table==NULL, and tab->cleanup() is not called for the join tabs.
In the case where the parent query has "Impossible WHERE" and the subquery
hasn't, the following happens:
Breakpoint 1, create_tmp_table (thd=0xa33a078, param=0x7fff1800f630, fields=..., group=0x0, distinct=true, save_sum_fields=true, select_options=2147768832, rows_limit=18446744073709551615, table_alias=0x7fff1800f720 "<subquery2>", do_not_open=false) at sql_select.cc:14181
(gdb) wher
#0 create_tmp_table (thd=0xa33a078, param=0x7fff1800f630, fields=..., group=0x0, distinct=true, save_sum_fields=true, select_options=2147768832, rows_limit=18446744073709551615, table_alias=0x7fff1800f720 "<subquery2>", do_not_open=false) at sql_select.cc:14181
#1 0x000000000065ab0c in select_materialize_with_stats::create_result_table (this=0x7fff1800f610, thd_arg=0xa33a078, column_types=0x7fff18009dd0, is_union_distinct=true, options=2147768832, table_alias=0x7fff1800f720 "<subquery2>", bit_fields_as_long=true, create_table=true) at sql_class.cc:3166
#2 0x0000000000616e31 in subselect_hash_sj_engine::init (this=0x7fff1800f548, tmp_columns=0x7fff18009dd0, subquery_id=2) at item_subselect.cc:4211
#3 0x00000000006137e9 in Item_in_subselect::setup_mat_engine (this=0x7fff1800ad18) at item_subselect.cc:2679
#4 0x00000000007ef2a0 in JOIN::choose_subquery_plan (this=0x7fff1800d7f8, join_tables=1) at opt_subselect.cc:5409
#5 0x00000000007094f2 in make_join_statistics (join=0x7fff1800d7f8, tables_list=..., conds=0x0, keyuse_array=0x7fff1800dad0) at sql_select.cc:3685
#6 0x0000000000700777 in JOIN::optimize (this=0x7fff1800d7f8) at sql_select.cc:1158
#7 0x000000000060d73f in Item_in_subselect::optimize (this=0x7fff1800ad18, out_rows=0x7ffff41954b0, cost=0x7ffff41954a8) at item_subselect.cc:617
#8 0x00000000007ee73a in setup_jtbm_semi_joins (join=0x7fff1800cf18, join_list=0xa33cc80, join_where=0x7fff1800d300) at opt_subselect.cc:5105
#9 0x00000000006ffedb in JOIN::optimize (this=0x7fff1800cf18) at sql_select.cc:1015
Breakpoint 3, free_tmp_table (thd=0xa33a078, entry=0x7fff18060e80) at sql_select.cc:15605
(gdb) wher
#0 free_tmp_table (thd=0xa33a078, entry=0x7fff18060e80) at sql_select.cc:15605
#1 0x000000000061794f in subselect_hash_sj_engine::cleanup (this=0x7fff1800f548) at item_subselect.cc:4440
#2 0x000000000060c352 in Item_subselect::cleanup (this=0x7fff1800ad18) at item_subselect.cc:130
#3 0x000000000060c52d in Item_in_subselect::cleanup (this=0x7fff1800ad18) at item_subselect.cc:167
#4 0x000000000065df05 in Item::delete_self (this=0x7fff1800ad18) at item.h:1249
#5 0x0000000000659b53 in Query_arena::free_items (this=0xa33a090) at sql_class.cc:2809
#6 0x00000000006554dd in THD::cleanup_after_query (this=0xa33a078) at sql_class.cc:1532
#7 0x000000000068f8d0 in mysql_parse (t
That is,
1. the tmp table is created by subselect_hash_sj_engine() (as opposed to the
case of the subquery having "Imposisble where". In that case,
setup_jtbm_semi_joins() creates a dummy tmp. table.
2. When the parent subquery calls JOIN::cleanup(), it has JOIN::table==NULL and
doesn't perform cleanup for the subquery. The subquery is cleaned up through
Item::cleanup() mechanism.
"Regular" joins with SJM query plans produce no warning. The above example is somehow special.