diff options
Diffstat (limited to 'mysql-test/r')
| -rw-r--r-- | mysql-test/r/derived_view.result | 22 |
1 files changed, 22 insertions, 0 deletions
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index be6050ab48e..c78b2a41d19 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -703,3 +703,25 @@ a NULL DROP VIEW v1; DROP TABLE t1,t2,t3; +# +# LP bug #803410: materialized view/dt accessed by two-component key +# +CREATE TABLE t1 (a varchar(1)); +INSERT INTO t1 VALUES ('c'); +CREATE TABLE t2 (a varchar(1) , KEY (a)) ; +INSERT INTO t2 VALUES ('c'), (NULL), ('r'); +CREATE TABLE t3 (a varchar(1), b varchar(1)); +INSERT INTO t3 VALUES ('e', 'c'), ('c', 'c'), ('c', 'r'); +CREATE VIEW v1 AS SELECT a, MIN(b) AS b FROM t3 GROUP BY a; +EXPLAIN +SELECT * FROM t1, t2, v1 WHERE t2.a=t1.a AND t2.a=v1.a AND t2.a=v1.b; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 ref a a 4 const 1 Using index +1 PRIMARY <derived2> ref key1 key1 10 test.t1.a,test.t1.a 2 Using where +2 DERIVED t3 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort +SELECT * FROM t1, t2, v1 WHERE t2.a=t1.a AND t2.a=v1.a AND t2.a=v1.b; +a a a b +c c c c +DROP VIEW v1; +DROP TABLE t1,t2,t3; |