diff options
| -rw-r--r-- | mysql-test/r/subselect.result | 29 | ||||
| -rw-r--r-- | mysql-test/t/subselect.test | 26 | ||||
| -rw-r--r-- | sql/opt_range.cc | 14 | ||||
| -rw-r--r-- | sql/opt_range.h | 4 |
4 files changed, 70 insertions, 3 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 diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 57903ffe7b9..85125a4521a 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -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_range(QUICK_RANGE *range_arg, return 0; } #endif - +void QUICK_SELECT_DESC::reset(void) +{ + rev_it.rewind(); + QUICK_SELECT::reset(); +} /***************************************************************************** ** Print a quick range for debugging diff --git a/sql/opt_range.h b/sql/opt_range.h index d2f4452a762..367a85dc6f2 100644 --- a/sql/opt_range.h +++ b/sql/opt_range.h @@ -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(); range= NULL;} + 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; }; |