diff options
| -rw-r--r-- | mysql-test/r/view.result | 22 | ||||
| -rw-r--r-- | mysql-test/t/view.test | 20 | ||||
| -rw-r--r-- | sql/sql_select.cc | 15 |
3 files changed, 57 insertions, 0 deletions
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index e52ec950c8c..3503e885aa8 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -2298,3 +2298,25 @@ a 3 DROP VIEW v1; DROP TABLE t1; +CREATE TABLE t1 (a INT, b INT, INDEX(a,b)); +CREATE TABLE t2 LIKE t1; +CREATE TABLE t3 (a INT); +INSERT INTO t1 VALUES (1,1),(2,2),(3,3); +INSERT INTO t2 VALUES (1,1),(2,2),(3,3); +INSERT INTO t3 VALUES (1),(2),(3); +CREATE VIEW v1 AS SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a AND t1.b=t2.b; +CREATE VIEW v2 AS SELECT t3.* FROM t1,t3 WHERE t1.a=t3.a; +EXPLAIN SELECT t1.* FROM t1 JOIN t2 WHERE t1.a=t2.a AND t1.b=t2.b AND t1.a=1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a a 5 const 1 Using where; Using index +1 SIMPLE t2 ref a a 10 const,test.t1.b 2 Using where; Using index +EXPLAIN SELECT * FROM v1 WHERE a=1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ref a a 5 const 1 Using where; Using index +1 PRIMARY t2 ref a a 10 const,test.t1.b 2 Using where; Using index +EXPLAIN SELECT * FROM v2 WHERE a=1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ref a a 5 const 1 Using where; Using index +1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where +DROP VIEW v1,v2; +DROP TABLE t1,t2,t3; diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 15c8cccf69c..5f483401cff 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -2167,3 +2167,23 @@ SELECT v_1.a FROM v1 AS v_1 GROUP BY v_1.a HAVING v_1.a IN (1,2,3); DROP VIEW v1; DROP TABLE t1; + +# +# Bug #13327 view wasn't using index for const condition +# + +CREATE TABLE t1 (a INT, b INT, INDEX(a,b)); +CREATE TABLE t2 LIKE t1; +CREATE TABLE t3 (a INT); +INSERT INTO t1 VALUES (1,1),(2,2),(3,3); +INSERT INTO t2 VALUES (1,1),(2,2),(3,3); +INSERT INTO t3 VALUES (1),(2),(3); +CREATE VIEW v1 AS SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a AND t1.b=t2.b; +CREATE VIEW v2 AS SELECT t3.* FROM t1,t3 WHERE t1.a=t3.a; +EXPLAIN SELECT t1.* FROM t1 JOIN t2 WHERE t1.a=t2.a AND t1.b=t2.b AND t1.a=1; +EXPLAIN SELECT * FROM v1 WHERE a=1; +EXPLAIN SELECT * FROM v2 WHERE a=1; +DROP VIEW v1,v2; +DROP TABLE t1,t2,t3; + + diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 1b1a35d2584..4b69e2c6987 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -6254,6 +6254,21 @@ static bool check_equality(Item *item, COND_EQUAL *cond_equal) { Item *left_item= ((Item_func*) item)->arguments()[0]; Item *right_item= ((Item_func*) item)->arguments()[1]; + + if (left_item->type() == Item::REF_ITEM && + ((Item_ref*)left_item)->ref_type() == Item_ref::VIEW_REF) + { + if (((Item_ref*)left_item)->depended_from) + return FALSE; + left_item= left_item->real_item(); + } + if (right_item->type() == Item::REF_ITEM && + ((Item_ref*)right_item)->ref_type() == Item_ref::VIEW_REF) + { + if (((Item_ref*)right_item)->depended_from) + return FALSE; + right_item= right_item->real_item(); + } if (left_item->type() == Item::FIELD_ITEM && right_item->type() == Item::FIELD_ITEM && !((Item_field*)left_item)->depended_from && |