[MDEV-5011] ERROR Plugin 'MEMORY' has ref_count=1 after shutdown for SJM queries. - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 5.5.33, 5.3.13
Fix Version/s: 5.5.33, 5.3.13
Component/s: None
Labels:
None

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.

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

Ascending order - Click to sort in descending order

Sergei Petrunia added a comment - 2013-09-12 10:51

"Regular" joins with SJM query plans produce no warning. The above example is somehow special.

Sergei Petrunia added a comment - 2013-09-12 10:51 "Regular" joins with SJM query plans produce no warning. The above example is somehow special.

Sergei Petrunia added a comment - 2013-09-12 11:00

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.

Sergei Petrunia added a comment - 2013-09-12 11:00 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.

Sergei Petrunia added a comment - 2013-09-12 11:32

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
...

Sergei Petrunia added a comment - 2013-09-12 11:32 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 ...

Sergei Petrunia added a comment - 2013-09-12 11:36

The EXPLAIN for the above query was:

---------------------------------------------------------------------------------+

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.

Sergei Petrunia added a comment - 2013-09-12 11:36 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.

Sergei Petrunia added a comment - 2013-09-12 12:27

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.

Sergei Petrunia added a comment - 2013-09-12 12:27 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.

Sergei Petrunia added a comment - 2013-09-12 13:32

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.

Sergei Petrunia added a comment - 2013-09-12 13:32 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.

People

Assignee:: Sergei Petrunia

Reporter:: Sergei Petrunia

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2013-09-12 10:39

Updated:: 2013-09-12 17:30

Resolved:: 2013-09-12 17:30

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration