diff options
author | unknown <timour@askmonty.org> | 2011-12-19 23:05:44 +0200 |
---|---|---|
committer | unknown <timour@askmonty.org> | 2011-12-19 23:05:44 +0200 |
commit | 072073c09e0308ac58f1dbd2ee8f0fbc53e11467 (patch) | |
tree | 911303f982b3ef2f46cb003acbedcb269fedfb3b /mysql-test/t/subselect.test | |
parent | 15ea7238e42ea62da32c926c0a1667802f7646d9 (diff) | |
download | mariadb-git-072073c09e0308ac58f1dbd2ee8f0fbc53e11467.tar.gz |
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()
Diffstat (limited to 'mysql-test/t/subselect.test')
-rw-r--r-- | mysql-test/t/subselect.test | 9 |
1 files changed, 5 insertions, 4 deletions
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index d53ba706388..2540232bd3b 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -3418,8 +3418,8 @@ drop table t1, t2; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2); -EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 GROUP BY a); -EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a); +EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 GROUP BY a); +EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 WHERE a > 3 GROUP BY a); DROP TABLE t1; --echo # @@ -4442,7 +4442,8 @@ DROP table t1,t2; CREATE TABLE t1 (a int, b int) ; INSERT INTO t1 VALUES (0,0),(0,0); - +set @optimizer_switch_save=@@optimizer_switch; +set @@optimizer_switch='semijoin=off,materialization=on,in_to_exists=on'; EXPLAIN SELECT b FROM t1 WHERE ('0') IN ( SELECT a FROM t1 GROUP BY a ) @@ -4451,7 +4452,7 @@ SELECT b FROM t1 SELECT b FROM t1 WHERE ('0') IN ( SELECT a FROM t1 GROUP BY a ) GROUP BY b; - +set @@optimizer_switch=@optimizer_switch_save; DROP TABLE t1; --echo # |