Merge bk-internal:/home/bk/mysql-4.1-opt

into  dl145s.mysql.com:/data/bk/team_tree_merge/MERGE/mysql-4.1-opt

2 files changed, 55 insertions, 0 deletions

diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index aeca9e0c2e5..ba4de7f6406 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -2917,3 +2917,32 @@ retailerID	statusID	changed
 0048	1	2006-01-06 12:37:50
 0059	1	2006-01-06 12:37:50
 drop table t1;
+create table t1(a int, primary key (a));
+insert into t1 values (10);
+create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b));
+insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989');
+explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
+ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' 
+ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	r	const	PRIMARY	PRIMARY	4	const	1	
+2	DEPENDENT SUBQUERY	t2	range	b	b	38	NULL	2	Using where
+SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
+ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' 
+ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
+a	a	b
+10	3	35989
+explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
+ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' 
+ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	r	const	PRIMARY	PRIMARY	4	const	1	
+2	DEPENDENT SUBQUERY	t2	range	b	b	38	NULL	2	Using where
+SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
+ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' 
+ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
+a	a	b
+10	1	359
+drop table t1,t2;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 10dfb788c10..1018b5de005 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -1885,4 +1885,30 @@ select * from t1 r1
           group by r2.retailerId);
 drop table t1;
 
+#
+# Bug #21180: Subselect with index for both WHERE and ORDER BY 
+#             produces empty result
+#
+create table t1(a int, primary key (a));
+insert into t1 values (10);
+
+create table t2 (a int primary key, b varchar(32), c int, unique key b(c, b));
+insert into t2(a, c, b) values (1,10,'359'), (2,10,'35988'), (3,10,'35989');
+
+explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
+  ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' 
+             ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
+SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
+  ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' 
+            ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10;
+
+explain SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
+  ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' 
+            ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
+SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r 
+  ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' 
+            ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;
+
+drop table t1,t2;
+
 # End of 4.1 tests