From: Date: August 3 2006 6:20pm Subject: bk commit into 4.1 tree (gkodinov:1.2532) BUG#21180 List-Archive: http://lists.mysql.com/commits/10017 X-Bug: 21180 Message-Id: <20060803162043.95B6C302CC3@macbook.gmz> 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 rev_ranges; List_iterator 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 +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; --- 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