summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/func_group.result4
-rw-r--r--mysql-test/r/ps_11bugs.result2
-rw-r--r--mysql-test/r/subselect.result8
-rw-r--r--mysql-test/r/subselect4.result24
-rw-r--r--mysql-test/r/subselect_innodb.result2
-rw-r--r--mysql-test/r/subselect_no_mat.result10
-rw-r--r--mysql-test/r/subselect_no_opts.result8
-rw-r--r--mysql-test/r/subselect_no_scache.result8
-rw-r--r--mysql-test/r/subselect_no_semijoin.result8
-rw-r--r--mysql-test/t/subselect4.test13
-rw-r--r--sql/item_subselect.cc15
11 files changed, 70 insertions, 32 deletions
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index 55afba245bd..c6fa040246a 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -1837,10 +1837,10 @@ INSERT INTO t2 VALUES
EXPLAIN EXTENDED
SELECT MAX(a) FROM t1 WHERE (1,2) IN (SELECT 3,4) AND a<10;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 range a a 4 NULL 4 100.00 Using where; Using index
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 select max(`test`.`t1`.`a`) AS `MAX(a)` from `test`.`t1` where (<cache>(<in_optimizer>((1,2),<exists>(select 3,4 having (((1 = 3) or isnull(3)) and ((2 = 4) or isnull(4)) and <is_not_null_test>(3) and <is_not_null_test>(4))))) and (`test`.`t1`.`a` < 10))
+Note 1003 select max(`test`.`t1`.`a`) AS `MAX(a)` from `test`.`t1` where 0
SELECT MAX(a) FROM t1 WHERE (1,2) IN (SELECT 3,4) AND a<10;
MAX(a)
NULL
diff --git a/mysql-test/r/ps_11bugs.result b/mysql-test/r/ps_11bugs.result
index 56894302505..3bc7039583b 100644
--- a/mysql-test/r/ps_11bugs.result
+++ b/mysql-test/r/ps_11bugs.result
@@ -120,7 +120,7 @@ create table t1 (a int primary key);
insert into t1 values (1);
explain select * from t1 where 3 in (select (1+1) union select 1);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 system NULL NULL NULL NULL 1
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 1977fbaccee..8273bee6117 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -429,7 +429,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 select 1 AS `1` from `test`.`t1` where (1 = (select 1 union select 1))
+Note 1003 select 1 AS `1` from `test`.`t1` where 1
drop table t1;
CREATE TABLE `t1` (
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
@@ -558,10 +558,10 @@ Warnings:
Note 1003 select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using where; Using index
+1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
Warnings:
-Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where (`test`.`t1`.`numeropost` = '1'))))
+Note 1003 select 3 AS `numreponse` from `test`.`t1` where ((1 = '1'))
drop table t1;
CREATE TABLE t1 (a int(1));
INSERT INTO t1 VALUES (1);
@@ -1371,7 +1371,7 @@ create table t1 (id int not null auto_increment primary key, salary int, key(sal
insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ref salary salary 5 const 0 0.00 Using where
+1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using where
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
Warnings:
Note 1003 select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index 2814e5a6dcd..11b27e76782 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -869,7 +869,7 @@ NULL
EXPLAIN
SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No matching min/max row
SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2);
f1 f2
@@ -960,7 +960,7 @@ NULL
EXPLAIN
SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No matching min/max row
SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2);
f1 f2
@@ -1055,7 +1055,7 @@ NULL
EXPLAIN
SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2 WHERE f3 > 10);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 2
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No matching min/max row
SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2 WHERE f3 > 10);
f1 f2
@@ -1146,7 +1146,7 @@ NULL
EXPLAIN
SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2 WHERE f3 > 10);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 2
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No matching min/max row
SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2 WHERE f3 > 10);
f1 f2
@@ -2012,7 +2012,7 @@ FROM t2 JOIN t3 ON t3.f4 = t2.f4
WHERE t3.f1 = 8
GROUP BY 1, 2;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 system NULL NULL NULL NULL 1 Using temporary; Using filesort
+1 PRIMARY t3 system NULL NULL NULL NULL 1 Using filesort
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
3 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL no matching row in const table
@@ -2325,5 +2325,19 @@ ORDER BY alias1.b;
pk b pk b
1 1 1 1
drop table t1, t2, t3;
+#
+# MDEV-4144 simple subquery causes full scan instead of range scan
+#
+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;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 3 Using where; Using filesort
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+SELECT x FROM t1 WHERE id > (SELECT MAX(id) - 1000 FROM t1) ORDER BY x LIMIT 1;
+x
+0
+drop table t1;
SET optimizer_switch= @@global.optimizer_switch;
set @@tmp_table_size= @@global.tmp_table_size;
diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result
index a6967527a2d..6f8350c791f 100644
--- a/mysql-test/r/subselect_innodb.result
+++ b/mysql-test/r/subselect_innodb.result
@@ -331,7 +331,7 @@ SELECT SUM( c )
FROM t2
WHERE (SELECT DISTINCT b FROM t3) > 0);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using where; Using index
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
3 SUBQUERY t3 ALL NULL NULL NULL NULL 1 Using temporary
SELECT *
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index feaeff50f7d..882a4da9854 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -436,7 +436,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 select 1 AS `1` from `test`.`t1` where (1 = (select 1 union select 1))
+Note 1003 select 1 AS `1` from `test`.`t1` where 1
drop table t1;
CREATE TABLE `t1` (
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
@@ -565,10 +565,10 @@ Warnings:
Note 1003 select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using where; Using index
+1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
Warnings:
-Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where (`test`.`t1`.`numeropost` = '1'))))
+Note 1003 select 3 AS `numreponse` from `test`.`t1` where ((1 = '1'))
drop table t1;
CREATE TABLE t1 (a int(1));
INSERT INTO t1 VALUES (1);
@@ -1378,7 +1378,7 @@ create table t1 (id int not null auto_increment primary key, salary int, key(sal
insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ref salary salary 5 const 0 0.00 Using where
+1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using where
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
Warnings:
Note 1003 select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))
@@ -6913,7 +6913,7 @@ INSERT INTO t2 VALUES (1),(2);
EXPLAIN
SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2 WHERE EXISTS (SELECT * FROM t1));
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 2
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
3 SUBQUERY t1 ALL NULL NULL NULL NULL 2
SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2 WHERE EXISTS (SELECT * FROM t1));
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index d1590b0df51..afff6acccef 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -432,7 +432,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 select 1 AS `1` from `test`.`t1` where (1 = (select 1 union select 1))
+Note 1003 select 1 AS `1` from `test`.`t1` where 1
drop table t1;
CREATE TABLE `t1` (
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
@@ -561,10 +561,10 @@ Warnings:
Note 1003 select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using where; Using index
+1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
Warnings:
-Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where (`test`.`t1`.`numeropost` = '1'))))
+Note 1003 select 3 AS `numreponse` from `test`.`t1` where ((1 = '1'))
drop table t1;
CREATE TABLE t1 (a int(1));
INSERT INTO t1 VALUES (1);
@@ -1374,7 +1374,7 @@ create table t1 (id int not null auto_increment primary key, salary int, key(sal
insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ref salary salary 5 const 0 0.00 Using where
+1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using where
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
Warnings:
Note 1003 select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 2fd58c075d2..8c589bbb669 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -435,7 +435,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 select 1 AS `1` from `test`.`t1` where (1 = (select 1 union select 1))
+Note 1003 select 1 AS `1` from `test`.`t1` where 1
drop table t1;
CREATE TABLE `t1` (
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
@@ -564,10 +564,10 @@ Warnings:
Note 1003 select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using where; Using index
+1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
Warnings:
-Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where (`test`.`t1`.`numeropost` = '1'))))
+Note 1003 select 3 AS `numreponse` from `test`.`t1` where ((1 = '1'))
drop table t1;
CREATE TABLE t1 (a int(1));
INSERT INTO t1 VALUES (1);
@@ -1377,7 +1377,7 @@ create table t1 (id int not null auto_increment primary key, salary int, key(sal
insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ref salary salary 5 const 0 0.00 Using where
+1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using where
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
Warnings:
Note 1003 select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index 34cdb17e23e..eb92936e85d 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -432,7 +432,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 select 1 AS `1` from `test`.`t1` where (1 = (select 1 union select 1))
+Note 1003 select 1 AS `1` from `test`.`t1` where 1
drop table t1;
CREATE TABLE `t1` (
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
@@ -561,10 +561,10 @@ Warnings:
Note 1003 select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using where; Using index
+1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
Warnings:
-Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where (`test`.`t1`.`numeropost` = '1'))))
+Note 1003 select 3 AS `numreponse` from `test`.`t1` where ((1 = '1'))
drop table t1;
CREATE TABLE t1 (a int(1));
INSERT INTO t1 VALUES (1);
@@ -1374,7 +1374,7 @@ create table t1 (id int not null auto_increment primary key, salary int, key(sal
insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ref salary salary 5 const 0 0.00 Using where
+1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using where
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
Warnings:
Note 1003 select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))
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;
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 9042e92f1da..43855286d4f 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -547,8 +547,19 @@ bool Item_subselect::is_expensive()
if (!cur_join)
continue;
- /* If a subquery is not optimized we cannot estimate its cost. */
- if (!cur_join->join_tab)
+ /*
+ Subqueries whose result is known after optimization are not expensive.
+ Such subqueries have all tables optimized away, thus have no join plan.
+ */
+ if (cur_join->optimized &&
+ (cur_join->zero_result_cause || !cur_join->tables_list))
+ return false;
+
+ /*
+ If a subquery is not optimized we cannot estimate its cost. A subquery is
+ considered optimized if it has a join plan.
+ */
+ if (!(cur_join->optimized && cur_join->join_tab))
return true;
if (sl->first_inner_unit())
View Lorry Controller Status