diff options
| author | Igor Babaev <igor@askmonty.org> | 2011-07-01 21:53:47 -0700 |
|---|---|---|
| committer | Igor Babaev <igor@askmonty.org> | 2011-07-01 21:53:47 -0700 |
| commit | 006dedf537a5647106dab6242af261224a4d53a2 (patch) | |
| tree | 4630bf946c14b7d2177c8218db6bbdcb24bfce14 /mysql-test/t/derived_view.test | |
| parent | 8318ef14aff37bf88a38e7770f4811e4de2f339c (diff) | |
| download | mariadb-git-006dedf537a5647106dab6242af261224a4d53a2.tar.gz | |
Fixed LP bug #804515.
If no index is used to access a materialized derived table or view
then the value of TABLE_REF::key for this table must be (-1).
Diffstat (limited to 'mysql-test/t/derived_view.test')
| -rw-r--r-- | mysql-test/t/derived_view.test | 29 |
1 files changed, 29 insertions, 0 deletions
diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test index 8348885c745..33ba752cc2f 100644 --- a/mysql-test/t/derived_view.test +++ b/mysql-test/t/derived_view.test @@ -385,3 +385,32 @@ SET SESSION optimizer_switch=default; DROP VIEW v1; DROP TABLE t1,t2,t3; + +--echo # +--echo # LP bug #804515: materialized derived + ORDER BY +--echo # + +CREATE TABLE t1 (f1 varchar(1), f2 varchar(1), KEY (f2)); +INSERT INTO t1 VALUES + ('r','x'), ('x','d'), ('x','r'), ('r','f'), ('x','x'); + +CREATE TABLE t2 (f1 varchar(1), f2 varchar(1)); +INSERT INTO t2 VALUES ('s','x'); + +CREATE TABLE t3 (f1 varchar(1), f2 varchar(1), KEY (f2)); +INSERT INTO t3 VALUES + (NULL,'x'), (NULL,'f'), ('t','p'), (NULL,'j'), ('g','c'); + +CREATE TABLE t4 (f1 int, f2 varchar(1), KEY (f2,f1)) ; +INSERT INTO t4 VALUES (1,'x'), (5,'r'); + +EXPLAIN +SELECT t.f1 AS f + FROM (SELECT DISTINCT t1.* FROM t1,t2 WHERE t2.f2 = t1.f2) t,t3,t4 + WHERE t4.f2 = t3.f2 AND t4.f2 = t.f1 ORDER BY f; +SELECT t.f1 AS f + FROM (SELECT DISTINCT t1.* FROM t1,t2 WHERE t2.f2 = t1.f2) t,t3,t4 + WHERE t4.f2 = t3.f2 AND t4.f2 = t.f1 ORDER BY f; + +DROP TABLE t1,t2,t3,t4; + |