From 072073c09e0308ac58f1dbd2ee8f0fbc53e11467 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 19 Dec 2011 23:05:44 +0200 Subject: Backport of WL#5953 from MySQL 5.6 The patch differs from the original MySQL patch as follows: - All test case differences have been reviewed one by one, and care has been taken to restore the original plan so that each test case executes the code path it was designed for. - A bug was found and fixed in MariaDB 5.3 in Item_allany_subselect::cleanup(). - ORDER BY is not removed because we are unsure of all effects, and it would prevent enabling ORDER BY ... LIMIT subqueries. - ref_pointer_array.m_size is not adjusted because we don't do array bounds checking, and because it looks risky. Original comment by Jorgen Loland: ------------------------------------------------------------- WL#5953 - Optimize away useless subquery clauses For IN/ALL/ANY/SOME/EXISTS subqueries, the following clauses are meaningless: * ORDER BY (since we don't support LIMIT in these subqueries) * DISTINCT * GROUP BY if there is no HAVING clause and no aggregate functions This WL detects and optimizes away these useless parts of the query during JOIN::prepare() --- mysql-test/include/mix1.inc | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'mysql-test/include') diff --git a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc index a86e4818d15..65e488c538f 100644 --- a/mysql-test/include/mix1.inc +++ b/mysql-test/include/mix1.inc @@ -1184,9 +1184,9 @@ DROP TABLE t1; create table t1 (a bit(1) not null,b int) engine=myisam; create table t2 (c int) engine=innodb; set @save_optimizer_switch=@@optimizer_switch; -set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=off'; +set @@optimizer_switch='in_to_exists=on,materialization=off'; explain -select b from t1 where a not in (select b from t1,t2 group by a) group by a; +select b from t1 where a not in (select max(b) from t1,t2 group by a) group by a; set optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t2; -- cgit v1.2.1