Bug#37548: result value erronously reported being NULL in certain subqueries

      
When switching to indexed ORDER BY we must be sure to reset the index read
flag if we are switching from a covering index to non-covering.

1 files changed, 39 insertions, 0 deletions

diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index ce4ac00b791..b4c70816eb6 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -4391,3 +4391,42 @@ SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);
 s1
 a
 DROP TABLE t1;
+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY b (b));
+INSERT INTO t1 VALUES (1,NULL), (9,NULL);
+CREATE TABLE t2 (
+a int,
+b int,
+c int,
+d int,
+PRIMARY KEY (a),
+UNIQUE KEY b (b,c,d),
+KEY b_2 (b),
+KEY c (c),
+KEY d (d)
+);
+INSERT INTO t2 VALUES 
+(43, 2, 11 ,30),
+(44, 2, 12 ,30),
+(45, 1, 1  ,10000),
+(46, 1, 2  ,10000),
+(556,1, 32 ,10000);
+CREATE TABLE t3 (
+a int,
+b int,
+c int,
+PRIMARY KEY (a),
+UNIQUE KEY b (b,c),
+KEY c (c),
+KEY b_2 (b)
+);
+INSERT INTO t3 VALUES (1,1,1), (2,32,1);
+explain 
+SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	index	b,b_2	b	10	NULL	2	Using index
+1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.t3.b	1	Using index
+2	DEPENDENT SUBQUERY	t2	index	b,b_2,c	d	5	NULL	1	Using where
+SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a;
+a	incorrect
+1	1
+DROP TABLE t1,t2,t3;