From 599a1384af7d38e4319bd6258c6954750f5b9ba4 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 29 Mar 2013 17:53:21 +0200 Subject: Fix for MDEV-4144 Analysis: The reason for the inefficent plan was that Item_subselect::is_expensive() didn't detect the special case when a subquery was optimized, but had no join plan because it either has no table, or its tables have been optimized away, or the optimizer detected that the result set is empty. Solution: Identify the special cases above in the Item_subselect::is_expensive(), and consider such degenerate subqueries inexpensive. --- mysql-test/t/subselect4.test | 13 +++++++++++++ 1 file changed, 13 insertions(+) (limited to 'mysql-test/t/subselect4.test') diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index 37f660d6682..e427253f65f 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -1865,5 +1865,18 @@ ORDER BY alias1.b; drop table t1, t2, t3; +--echo # +--echo # MDEV-4144 simple subquery causes full scan instead of range scan +--echo # + +CREATE TABLE t1 (id int not null auto_increment, x int not null, primary key(id)); +INSERT INTO t1 (x) VALUES (0),(0),(0); + +EXPLAIN +SELECT x FROM t1 WHERE id > (SELECT MAX(id) - 1000 FROM t1) ORDER BY x LIMIT 1; +SELECT x FROM t1 WHERE id > (SELECT MAX(id) - 1000 FROM t1) ORDER BY x LIMIT 1; + +drop table t1; + SET optimizer_switch= @@global.optimizer_switch; set @@tmp_table_size= @@global.tmp_table_size; -- cgit v1.2.1