diff options
| author | unknown <gkodinov/kgeorge@macbook.gmz> | 2007-01-22 12:51:21 +0200 |
|---|---|---|
| committer | unknown <gkodinov/kgeorge@macbook.gmz> | 2007-01-22 12:51:21 +0200 |
| commit | 2c6e678dd9343a905ea6b7d32cf70fe43c9f9dbe (patch) | |
| tree | e86a04bb2bb93a9f16e6a4731cc2e20749258fcd /mysql-test | |
| parent | e84b4600b808025d6515c4fd079604b8114425ae (diff) | |
| download | mariadb-git-2c6e678dd9343a905ea6b7d32cf70fe43c9f9dbe.tar.gz | |
BUG#16590: Optimized does not do right "const" table pre-read
st_table::const_key_parts member is used in determining if
certain key has a prefix that is compared to constant(s) in
the query predicates.
If there's such prefix the index can be used to get the data
from the remaining suffix columns in sorted order.
However if a field is compared to another field from a "const"
table the const_key_parts is not amended.
This makes the optimizer unable to detect that the key can be
used for sorting and adds an extra filesort.
Fixed by updating const_key_parts after reading in the "const"
table.
mysql-test/r/order_by.result:
BUG#16590: Optimized does not do right "const" table pre-read
- test case
mysql-test/t/order_by.test:
BUG#16590: Optimized does not do right "const" table pre-read
- test case
sql/sql_select.cc:
BUG#16590: Optimized does not do right "const" table pre-read
- fill up the const_key_parts structure
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/order_by.result | 9 | ||||
| -rw-r--r-- | mysql-test/t/order_by.test | 13 |
2 files changed, 22 insertions, 0 deletions
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index e5cc47fd9d2..646390317d0 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -918,3 +918,12 @@ NULL 2 3 DROP TABLE t1,t2,t3,t4; +CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), UNIQUE KEY b (b)); +INSERT INTO t1 VALUES (1,1),(2,2); +CREATE TABLE t2 (a INT, b INT, KEY a (a,b)); +INSERT INTO t2 VALUES (1,1),(1,2),(2,1),(2,2); +EXPLAIN SELECT 1 FROM t1,t2 WHERE t1.b=2 AND t1.a=t2.a ORDER BY t2.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY,b b 5 const 1 +1 SIMPLE t2 ref a a 5 const 2 Using where; Using index +DROP TABLE t1,t2; diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index d7cf0e2a375..40d2d745cc7 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -625,3 +625,16 @@ SELECT t2.b FROM t1 LEFT JOIN (t2, t3 LEFT JOIN t4 ON t3.a=t4.a) ON (t1.a=t2.a AND t1.b=t3.b) order by t2.b; DROP TABLE t1,t2,t3,t4; + +# +# BUG#16590: Optimized does not do right "const" table pre-read +# +CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), UNIQUE KEY b (b)); +INSERT INTO t1 VALUES (1,1),(2,2); + +CREATE TABLE t2 (a INT, b INT, KEY a (a,b)); +INSERT INTO t2 VALUES (1,1),(1,2),(2,1),(2,2); + +EXPLAIN SELECT 1 FROM t1,t2 WHERE t1.b=2 AND t1.a=t2.a ORDER BY t2.b; + +DROP TABLE t1,t2; |