diff options
| author | Igor Babaev <igor@askmonty.org> | 2011-10-20 08:02:31 -0700 |
|---|---|---|
| committer | Igor Babaev <igor@askmonty.org> | 2011-10-20 08:02:31 -0700 |
| commit | b117a3c453a9a8a67906aa2bb06648c213a85b35 (patch) | |
| tree | b1f98a256e21b36ad667dc586f0bfc178cad1a92 /mysql-test/r/derived_view.result | |
| parent | e7a7e2a036ada1f3b9715fc6e889dbc29768feea (diff) | |
| download | mariadb-git-b117a3c453a9a8a67906aa2bb06648c213a85b35.tar.gz | |
Added a test case for LP bug #873263 fixed by the patch for bug 874006.
Diffstat (limited to 'mysql-test/r/derived_view.result')
| -rw-r--r-- | mysql-test/r/derived_view.result | 20 |
1 files changed, 20 insertions, 0 deletions
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index 5611e1ecea3..9bf778f397b 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -1505,6 +1505,26 @@ a b c DROP VIEW v1; DROP TABLE t1,t2,t3; # +# LP bug #873263: materialized view used in correlated IN subquery +# +CREATE TABLE t1 (a int, b int) ; +INSERT INTO t1 VALUES (5,4), (9,8); +CREATE TABLE t2 (a int, b int) ; +INSERT INTO t2 VALUES (4,5), (5,1); +CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2; +SET SESSION optimizer_switch='derived_with_keys=on'; +EXPLAIN +SELECT * FROM t1 WHERE t1.b IN (SELECT v2.a FROM v2 WHERE v2.b = t1.a); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY <derived3> ref key0 key0 5 test.t1.a 2 Using where +3 DERIVED t2 ALL NULL NULL NULL NULL 2 +SELECT * FROM t1 WHERE t1.b IN (SELECT v2.a FROM v2 WHERE v2.b = t1.a); +a b +5 4 +DROP VIEW v2; +DROP TABLE t1,t2; +# # LP bug #877316: query over a view with correlated subquery in WHERE # CREATE TABLE t1 (a int, b int, PRIMARY KEY (a)) ; |