From 2df1914791030714196c3d829187891a97be54dc Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 27 Aug 2011 00:40:29 +0300 Subject: Fix bug lp:827416 Analysis: Constant table optimization of the outer query finds that the right side of the equality is a constant that can be used for an eq_ref access to fetch one row from t1, and substitute t1 with a constant. Thus constant optimization triggers evaluation of the subquery during the optimize phase of the outer query. The innermost subquery requires a plan with a temporary table because with InnoDB tables the exact count of rows is not known, and the empty tables cannot be optimzied way. JOIN::exec for the innermost subquery substitutes the subquery tables with a temporary table. When EXPLAIN gets to print the tables in the innermost subquery, EXPLAIN needs to print the name of each table through the corresponding TABLE_LIST object. However, the temporary table created during execution doesn't have a corresponding TABLE_LIST, so we get a null pointer exception. Solution: The solution is to forbid using expensive constant expressions for eq_ref access for contant table optimization. Notice that eq_ref with a subquery providing the value is still possible during regular execution. --- mysql-test/t/subselect_innodb.test | 24 +++++++++++++++++++++++- 1 file changed, 23 insertions(+), 1 deletion(-) (limited to 'mysql-test/t/subselect_innodb.test') diff --git a/mysql-test/t/subselect_innodb.test b/mysql-test/t/subselect_innodb.test index da7524c1427..e108aecfd36 100644 --- a/mysql-test/t/subselect_innodb.test +++ b/mysql-test/t/subselect_innodb.test @@ -241,5 +241,27 @@ call p1(); drop procedure p1; drop tables t1,t2,t3; -set optimizer_switch=@subselect_innodb_tmp; +--echo # +--echo # LP BUG#827416: Crash in select_describe() on EXPLAIN with DISTINCT in nested subqueries +--echo # + +CREATE TABLE t3 ( b int) ENGINE=InnoDB; +CREATE TABLE t2 ( c int) ENGINE=InnoDB; +CREATE TABLE t1 ( a int NOT NULL , PRIMARY KEY (a)) ENGINE=InnoDB; + +EXPLAIN SELECT * +FROM t1 +WHERE t1.a = ( + SELECT SUM( c ) + FROM t2 + WHERE (SELECT DISTINCT b FROM t3) > 0); +SELECT * +FROM t1 +WHERE t1.a = ( + SELECT SUM( c ) + FROM t2 + WHERE (SELECT DISTINCT b FROM t3) > 0); +DROP TABLE t1, t2, t3; + +set optimizer_switch=@subselect_innodb_tmp; -- cgit v1.2.1 From b53744b79ebb58dfc242638bfb4d9b8c01bb8251 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 28 Sep 2011 17:20:43 +0300 Subject: Fix bug lp:858148. Analysis: The crash is a result of the same cause as all similar bugs (lp:827416, lp:718763, lp:778413, lp:806943, lp:611690). The general pattern is that some optimization requires the evaluation of some condition (e.g. the WHERE clause), and this condition contains a subquery, such that the subquery itself requires a temporary table for its execution. During the subquery execution the original tables in the FROM clause are replaced by the temporary table needed for the final GROUP or ORDER operation. All this happens during optimization of the outer query. Later when EXPLAIN is run for the subquery, explain attempts to print the name of the tables in the FROM clause, but it finds there a temporary table without a corresponding TABLE_LIST object. The attempt to print the name of a NULL table list results in a crash. Solution: This patch extends the fix to bug lp:702301, and dissalows constant substitution of aggregate functions if the filter condition used to check MIN/MAX keys is an expensive condition. --- mysql-test/t/subselect_innodb.test | 34 ++++++++++++++++++++++++++++++++++ 1 file changed, 34 insertions(+) (limited to 'mysql-test/t/subselect_innodb.test') diff --git a/mysql-test/t/subselect_innodb.test b/mysql-test/t/subselect_innodb.test index e108aecfd36..af0e4f58e6b 100644 --- a/mysql-test/t/subselect_innodb.test +++ b/mysql-test/t/subselect_innodb.test @@ -264,4 +264,38 @@ WHERE t1.a = ( DROP TABLE t1, t2, t3; + +--echo # +--echo # LP BUG#858148 Fourth crash in select_describe() with nested subqueries +--echo # + +CREATE TABLE t1 ( f1 int(11)) ENGINE=InnoDB; +CREATE TABLE t2 ( f1 int(11), f2 int(11), PRIMARY KEY (f1)) ; +CREATE TABLE t3 ( f3 int(11)) ENGINE=InnoDB; + +EXPLAIN +SELECT MAX( f1 ) FROM t2 +WHERE f2 >= ( + SELECT SUM( f1 ) + FROM t1 + WHERE EXISTS ( + SELECT f3 + FROM t3 + GROUP BY 1 + ) +); + +SELECT MAX( f1 ) FROM t2 +WHERE f2 >= ( + SELECT SUM( f1 ) + FROM t1 + WHERE EXISTS ( + SELECT f3 + FROM t3 + GROUP BY 1 + ) +); + +drop table t1, t2, t3; + set optimizer_switch=@subselect_innodb_tmp; -- cgit v1.2.1 From 47861a657762feeb45ee6b8edea00033bbd0e8ca Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Wed, 2 Nov 2011 13:48:41 +0400 Subject: Change the default @@optimizer_switch settings: - semijoin=on - firstmatch=on - loosescan=on --- mysql-test/t/subselect_innodb.test | 2 ++ 1 file changed, 2 insertions(+) (limited to 'mysql-test/t/subselect_innodb.test') diff --git a/mysql-test/t/subselect_innodb.test b/mysql-test/t/subselect_innodb.test index af0e4f58e6b..4b511b45e03 100644 --- a/mysql-test/t/subselect_innodb.test +++ b/mysql-test/t/subselect_innodb.test @@ -1,5 +1,7 @@ -- source include/have_innodb.inc +# Note: the tests uses only non-semijoin subqueries so semi-join switch +# settings are not relevant. set @subselect_innodb_tmp=@@optimizer_switch; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; --disable_warnings -- cgit v1.2.1