bk commit into 4.1 tree (gkodinov:1.2532) BUG#21180

[kgeorge]

Below is the list of changes that have just been committed into a local
4.1 repository of kgeorge. When kgeorge does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2006-08-03 19:20:30+03:00, gkodinov@stripped +4 -0
  Bug #21180: Subselect with index for both WHERE and ORDER BY produces empty result
   Reseting subqueries with "quick" access methods was incomplete.
   Partially backported the correct reseting of QUICK_SELECTs from 5.x.

  mysql-test/r/subselect.result@stripped, 2006-08-03 19:20:21+03:00, gkodinov@stripped +29
-0
    Bug #21180: Subselect with index for both WHERE and ORDER BY produces empty result
     - test case

  mysql-test/t/subselect.test@stripped, 2006-08-03 19:20:22+03:00, gkodinov@stripped +26
-0
    Bug #21180: Subselect with index for both WHERE and ORDER BY produces empty result
     - test case

  sql/opt_range.cc@stripped, 2006-08-03 19:20:23+03:00, gkodinov@stripped +13 -1
    Bug #21180: Subselect with index for both WHERE and ORDER BY produces empty result
     - partially backported the correct reseting of QUICK_SELECTs from 5.x.

  sql/opt_range.h@stripped, 2006-08-03 19:20:23+03:00, gkodinov@stripped +2 -2
    Bug #21180: Subselect with index for both WHERE and ORDER BY produces empty result
     - partially backported the correct reseting of QUICK_SELECTs from 5.x.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	gkodinov
# Host:	macbook.gmz
# Root:	/Users/kgeorge/mysql/work/B21180-4.1-opt

--- 1.147/sql/opt_range.cc	2006-08-03 19:20:42 +03:00
+++ 1.148/sql/opt_range.cc	2006-08-03 19:20:42 +03:00
@@ -2980,6 +2980,14 @@ int QUICK_SELECT::get_next()
   }
 }
 
+void QUICK_SELECT::reset(void) 
+{
+  next= 0; 
+  it.rewind();
+  range= 0; 
+  if (file->inited == handler::NONE) 
+    file->ha_index_init(index);
+}
 
 /* Get next for geometrical indexes */
 
@@ -3201,7 +3209,11 @@ bool QUICK_SELECT_DESC::test_if_null_ran
   return 0;
 }
 #endif
-
+void QUICK_SELECT_DESC::reset(void)
+{ 
+  rev_it.rewind();
+  QUICK_SELECT::reset();
+}
 
 /*****************************************************************************
 ** Print a quick range for debugging

--- 1.40/sql/opt_range.h	2006-08-03 19:20:42 +03:00
+++ 1.41/sql/opt_range.h	2006-08-03 19:20:42 +03:00
@@ -86,7 +86,7 @@ public:
 
   QUICK_SELECT(THD *thd, TABLE *table,uint index_arg,bool no_alloc=0);
   virtual ~QUICK_SELECT();
-  void reset(void) { next=0; it.rewind(); }
+  virtual void reset(void);
   int init()
   {
     key_part_info= head->key_info[index].key_part;
@@ -120,7 +120,7 @@ private:
 #ifdef NOT_USED
   bool test_if_null_range(QUICK_RANGE *range, uint used_key_parts);
 #endif
-  void reset(void) { next=0; rev_it.rewind(); }
+  void reset(void);
   List<QUICK_RANGE> rev_ranges;
   List_iterator<QUICK_RANGE> rev_it;
 };

--- 1.180/mysql-test/r/subselect.result	2006-08-03 19:20:43 +03:00
+++ 1.181/mysql-test/r/subselect.result	2006-08-03 19:20:43 +03:00
@@ -2895,3 +2895,32 @@ select * from t1 where NOT(s1 = ALL (sel
 s1
 2
 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
[phone]
+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;

--- 1.157/mysql-test/t/subselect.test	2006-08-03 19:20:43 +03:00
+++ 1.158/mysql-test/t/subselect.test	2006-08-03 19:20:43 +03:00
@@ -1861,4 +1861,30 @@ select * from t1 where NOT(s1+1 = ANY (s
 select * from t1 where (s1 = ALL (select s1/s1 from t1));
 select * from t1 where NOT(s1 = ALL (select s1/s1 from t1));
 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