summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
authorunknown <timour@askmonty.org>2013-03-29 17:53:21 +0200
committerunknown <timour@askmonty.org>2013-03-29 17:53:21 +0200
commit599a1384af7d38e4319bd6258c6954750f5b9ba4 (patch)
tree59be14d43461e4b717eb07c8e865e471756691b7 /mysql-test
parentfa01b76be7b22b457e2f53fbceaaa371b7790491 (diff)
downloadmariadb-git-599a1384af7d38e4319bd6258c6954750f5b9ba4.tar.gz
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.
Diffstat (limited to 'mysql-test')
-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
10 files changed, 57 insertions, 30 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;
View Lorry Controller Status