summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/subselect_sj2.result17
-rw-r--r--mysql-test/r/subselect_sj2_jcl6.result17
-rw-r--r--mysql-test/r/subselect_sj2_mat.result17
-rw-r--r--mysql-test/t/subselect_sj2.test19
-rw-r--r--sql/opt_subselect.cc8
-rw-r--r--sql/opt_subselect.h2
-rw-r--r--sql/sql_select.cc2
7 files changed, 78 insertions, 4 deletions
diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result
index cfbec56427f..b7509c1cda3 100644
--- a/mysql-test/r/subselect_sj2.result
+++ b/mysql-test/r/subselect_sj2.result
@@ -1112,5 +1112,22 @@ t3 AS t3a INNER JOIN t3 AS t3b ON ( t3a.i3 = t3b.i3 )
);
i1
DROP TABLE t1,t2,t3;
+#
+# MDEV-5582: Plugin 'MEMORY' has ref_count=1 after shutdown with materialization+semijoin
+#
+CREATE TABLE t1 (a INT) engine=innodb;
+INSERT INTO t1 VALUES (8),(9);
+CREATE TABLE t2 (b INT) engine=innodb;
+INSERT INTO t2 VALUES (2),(3);
+CREATE TABLE t3 (c INT, INDEX(c)) engine=innodb;
+INSERT INTO t2 VALUES (4),(5);
+explain
+SELECT * FROM t1 WHERE 9 IN ( SELECT b FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) );
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+3 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL No matching min/max row
+SELECT * FROM t1 WHERE 9 IN ( SELECT b FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) );
+a
+DROP TABLE t1,t2,t3;
# This must be the last in the file:
set optimizer_switch=@subselect_sj2_tmp;
diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result
index 4f1beb6cac1..b0f508abab6 100644
--- a/mysql-test/r/subselect_sj2_jcl6.result
+++ b/mysql-test/r/subselect_sj2_jcl6.result
@@ -1127,6 +1127,23 @@ t3 AS t3a INNER JOIN t3 AS t3b ON ( t3a.i3 = t3b.i3 )
);
i1
DROP TABLE t1,t2,t3;
+#
+# MDEV-5582: Plugin 'MEMORY' has ref_count=1 after shutdown with materialization+semijoin
+#
+CREATE TABLE t1 (a INT) engine=innodb;
+INSERT INTO t1 VALUES (8),(9);
+CREATE TABLE t2 (b INT) engine=innodb;
+INSERT INTO t2 VALUES (2),(3);
+CREATE TABLE t3 (c INT, INDEX(c)) engine=innodb;
+INSERT INTO t2 VALUES (4),(5);
+explain
+SELECT * FROM t1 WHERE 9 IN ( SELECT b FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) );
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+3 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL No matching min/max row
+SELECT * FROM t1 WHERE 9 IN ( SELECT b FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) );
+a
+DROP TABLE t1,t2,t3;
# This must be the last in the file:
set optimizer_switch=@subselect_sj2_tmp;
#
diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result
index d3cc8d08be2..bd0afd5a730 100644
--- a/mysql-test/r/subselect_sj2_mat.result
+++ b/mysql-test/r/subselect_sj2_mat.result
@@ -1114,6 +1114,23 @@ t3 AS t3a INNER JOIN t3 AS t3b ON ( t3a.i3 = t3b.i3 )
);
i1
DROP TABLE t1,t2,t3;
+#
+# MDEV-5582: Plugin 'MEMORY' has ref_count=1 after shutdown with materialization+semijoin
+#
+CREATE TABLE t1 (a INT) engine=innodb;
+INSERT INTO t1 VALUES (8),(9);
+CREATE TABLE t2 (b INT) engine=innodb;
+INSERT INTO t2 VALUES (2),(3);
+CREATE TABLE t3 (c INT, INDEX(c)) engine=innodb;
+INSERT INTO t2 VALUES (4),(5);
+explain
+SELECT * FROM t1 WHERE 9 IN ( SELECT b FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) );
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+3 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL No matching min/max row
+SELECT * FROM t1 WHERE 9 IN ( SELECT b FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) );
+a
+DROP TABLE t1,t2,t3;
# This must be the last in the file:
set optimizer_switch=@subselect_sj2_tmp;
set optimizer_switch=default;
diff --git a/mysql-test/t/subselect_sj2.test b/mysql-test/t/subselect_sj2.test
index 91db113d35a..bf7e0d4d3af 100644
--- a/mysql-test/t/subselect_sj2.test
+++ b/mysql-test/t/subselect_sj2.test
@@ -1249,5 +1249,24 @@ SELECT * FROM t1 WHERE ( 1, 1 ) IN (
DROP TABLE t1,t2,t3;
+--echo #
+--echo # MDEV-5582: Plugin 'MEMORY' has ref_count=1 after shutdown with materialization+semijoin
+--echo #
+CREATE TABLE t1 (a INT) engine=innodb;
+INSERT INTO t1 VALUES (8),(9);
+
+CREATE TABLE t2 (b INT) engine=innodb;
+INSERT INTO t2 VALUES (2),(3);
+
+CREATE TABLE t3 (c INT, INDEX(c)) engine=innodb;
+INSERT INTO t2 VALUES (4),(5);
+
+explain
+SELECT * FROM t1 WHERE 9 IN ( SELECT b FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) );
+SELECT * FROM t1 WHERE 9 IN ( SELECT b FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) );
+
+DROP TABLE t1,t2,t3;
+
+
--echo # This must be the last in the file:
set optimizer_switch=@subselect_sj2_tmp;
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 07dbb3991b0..60eee3fd48f 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -5234,9 +5234,9 @@ bool setup_jtbm_semi_joins(JOIN *join, List<TABLE_LIST> *join_list,
through Item::cleanup() calls).
*/
-void cleanup_empty_jtbm_semi_joins(JOIN *join)
+void cleanup_empty_jtbm_semi_joins(JOIN *join, List<TABLE_LIST> *join_list)
{
- List_iterator<TABLE_LIST> li(*join->join_list);
+ List_iterator<TABLE_LIST> li(*join_list);
TABLE_LIST *table;
while ((table= li++))
{
@@ -5248,6 +5248,10 @@ void cleanup_empty_jtbm_semi_joins(JOIN *join)
table->table= NULL;
}
}
+ else if (table->nested_join && table->sj_subq_pred)
+ {
+ cleanup_empty_jtbm_semi_joins(join, &table->nested_join->join_list);
+ }
}
}
diff --git a/sql/opt_subselect.h b/sql/opt_subselect.h
index 88ecc6cb405..444b3840950 100644
--- a/sql/opt_subselect.h
+++ b/sql/opt_subselect.h
@@ -12,7 +12,7 @@ int pull_out_semijoin_tables(JOIN *join);
bool optimize_semijoin_nests(JOIN *join, table_map all_table_map);
bool setup_jtbm_semi_joins(JOIN *join, List<TABLE_LIST> *join_list,
Item **join_where);
-void cleanup_empty_jtbm_semi_joins(JOIN *join);
+void cleanup_empty_jtbm_semi_joins(JOIN *join, List<TABLE_LIST> *join_list);
// used by Loose_scan_opt
ulonglong get_bound_sj_equalities(TABLE_LIST *sj_nest,
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 3e5652f2a16..e5fa81c1457 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -10549,7 +10549,7 @@ void JOIN::cleanup(bool full)
}
if (full)
{
- cleanup_empty_jtbm_semi_joins(this);
+ cleanup_empty_jtbm_semi_joins(this, join_list);
/*
Ensure that the following delete_elements() would not be called
twice for the same list.
View Lorry Controller Status