From e8b0b51966ae8b112c61650814af78530e8e7624 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Thu, 11 Jul 2013 19:27:39 +0400 Subject: MDEV-4042: Assertion `table->key_read == 0' fails in close_thread_table on EXPLAIN MDEV-4536: ...sql/sql_base.cc:1598: bool close_thread_table(THD*, TABLE**): Assertion - Make JOIN::cleanup(full=true) always free join optimization tabs. --- mysql-test/r/subselect_innodb.result | 44 ++++++++++++++++++++++++++++++++++++ mysql-test/t/subselect_innodb.test | 41 +++++++++++++++++++++++++++++++++ sql/sql_select.cc | 17 +++++++++++--- 3 files changed, 99 insertions(+), 3 deletions(-) diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result index 6f8350c791f..e52a9078fca 100644 --- a/mysql-test/r/subselect_innodb.result +++ b/mysql-test/r/subselect_innodb.result @@ -409,4 +409,48 @@ a d2 select distinct (1 + (select 1 from `t2` where `a`)) `d2` from `t1`; d2 drop table t1,t2; +# +# MDEV-4042: Assertion `table->key_read == 0' fails in close_thread_table on EXPLAIN with GROUP BY and HAVING in EXISTS SQ, +# MDEV-4536: ...sql/sql_base.cc:1598: bool close_thread_table(THD*, TABLE**): Assertion `table->key_read == 0' failed. +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +CREATE TABLE t2 (b INT PRIMARY KEY, c INT) ENGINE=InnoDB; +CREATE TABLE t3 (d INT) ENGINE=InnoDB; +EXPLAIN +SELECT * FROM t1 WHERE EXISTS ( SELECT b FROM t2, t3 GROUP BY b HAVING b != 3 ); +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 SUBQUERY t2 index NULL PRIMARY 4 NULL 1 Using index; Using temporary; Using filesort +2 SUBQUERY t3 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) +SELECT * FROM t1 WHERE EXISTS ( SELECT b FROM t2, t3 GROUP BY b HAVING b != 3 ); +a +DROP TABLE t1,t2,t3; +CREATE TABLE t1 ( +pk int auto_increment primary key, +col_int_key int(11), +key col_int_key (col_int_key),col_varchar_key varchar(128), +key (col_varchar_key) +) engine=innodb; +EXPLAIN +SELECT 1 FROM t1 AS alias1 +WHERE EXISTS ( SELECT SQ2_alias1 . col_int_key AS SQ2_field1 +FROM ( t1 AS SQ2_alias1 RIGHT OUTER JOIN +t1 AS SQ2_alias2 ON (SQ2_alias2 . col_int_key = SQ2_alias1 . col_int_key ) +) +GROUP BY SQ2_field1 +HAVING SQ2_alias1 . col_int_key >= 7 +); +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 SUBQUERY SQ2_alias2 index NULL col_int_key 5 NULL 1 Using index; Using temporary; Using filesort +2 SUBQUERY SQ2_alias1 ref col_int_key col_int_key 5 test.SQ2_alias2.col_int_key 1 Using where; Using index +SELECT 1 FROM t1 AS alias1 +WHERE EXISTS ( SELECT SQ2_alias1 . col_int_key AS SQ2_field1 +FROM ( t1 AS SQ2_alias1 RIGHT OUTER JOIN +t1 AS SQ2_alias2 ON (SQ2_alias2 . col_int_key = SQ2_alias1 . col_int_key ) +) +GROUP BY SQ2_field1 +HAVING SQ2_alias1 . col_int_key >= 7 +); +1 +drop table t1; set optimizer_switch=@subselect_innodb_tmp; diff --git a/mysql-test/t/subselect_innodb.test b/mysql-test/t/subselect_innodb.test index 83c36b16163..bcd95e02180 100644 --- a/mysql-test/t/subselect_innodb.test +++ b/mysql-test/t/subselect_innodb.test @@ -391,4 +391,45 @@ select distinct (1 + (select 1 from `t2` where `a`)) `d2` from `t1`; drop table t1,t2; +--echo # +--echo # MDEV-4042: Assertion `table->key_read == 0' fails in close_thread_table on EXPLAIN with GROUP BY and HAVING in EXISTS SQ, +--echo # MDEV-4536: ...sql/sql_base.cc:1598: bool close_thread_table(THD*, TABLE**): Assertion `table->key_read == 0' failed. +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +CREATE TABLE t2 (b INT PRIMARY KEY, c INT) ENGINE=InnoDB; +CREATE TABLE t3 (d INT) ENGINE=InnoDB; + +EXPLAIN +SELECT * FROM t1 WHERE EXISTS ( SELECT b FROM t2, t3 GROUP BY b HAVING b != 3 ); +SELECT * FROM t1 WHERE EXISTS ( SELECT b FROM t2, t3 GROUP BY b HAVING b != 3 ); + +DROP TABLE t1,t2,t3; + +CREATE TABLE t1 ( + pk int auto_increment primary key, + col_int_key int(11), + key col_int_key (col_int_key),col_varchar_key varchar(128), + key (col_varchar_key) +) engine=innodb; + +EXPLAIN +SELECT 1 FROM t1 AS alias1 +WHERE EXISTS ( SELECT SQ2_alias1 . col_int_key AS SQ2_field1 + FROM ( t1 AS SQ2_alias1 RIGHT OUTER JOIN + t1 AS SQ2_alias2 ON (SQ2_alias2 . col_int_key = SQ2_alias1 . col_int_key ) + ) + GROUP BY SQ2_field1 + HAVING SQ2_alias1 . col_int_key >= 7 + ); + +SELECT 1 FROM t1 AS alias1 +WHERE EXISTS ( SELECT SQ2_alias1 . col_int_key AS SQ2_field1 + FROM ( t1 AS SQ2_alias1 RIGHT OUTER JOIN + t1 AS SQ2_alias2 ON (SQ2_alias2 . col_int_key = SQ2_alias1 . col_int_key ) + ) + GROUP BY SQ2_field1 + HAVING SQ2_alias1 . col_int_key >= 7 + ); +drop table t1; + + set optimizer_switch=@subselect_innodb_tmp; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index e26a8c43489..06a0ceaa69f 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -10634,11 +10634,22 @@ void JOIN::cleanup(bool full) else clean_pre_sort_join_tab(); } + /* + Call cleanup() on join tabs used by the join optimization + (join->join_tab may now be pointing to result of make_simple_join + reading from the temporary table) - for (tab= first_linear_tab(this, WITH_CONST_TABLES); tab; - tab= next_linear_tab(this, tab, WITH_BUSH_ROOTS)) + We also need to check table_count to handle various degenerate joins + w/o tables: they don't have some members initialized and + WALK_OPTIMIZATION_TABS may not work correctly for them. + */ + if (table_count) { - tab->cleanup(); + for (tab= first_breadth_first_tab(this, WALK_OPTIMIZATION_TABS); tab; + tab= next_breadth_first_tab(this, WALK_OPTIMIZATION_TABS, tab)) + { + tab->cleanup(); + } } cleaned= true; } -- cgit v1.2.1