diff options
| author | unknown <bell@sanja.is.com.ua> | 2003-08-09 17:10:58 +0300 |
|---|---|---|
| committer | unknown <bell@sanja.is.com.ua> | 2003-08-09 17:10:58 +0300 |
| commit | d9b38c02be763d2e0fa8602e3e4f531ee50acebf (patch) | |
| tree | 39242281f1e59ede180c04e015d94a571d7ffbfd /mysql-test/t/derived.test | |
| parent | 2d7fb67e8fcbbce907c94a72db41b2f16e5aabed (diff) | |
| download | mariadb-git-d9b38c02be763d2e0fa8602e3e4f531ee50acebf.tar.gz | |
test for BUG#969
Diffstat (limited to 'mysql-test/t/derived.test')
| -rw-r--r-- | mysql-test/t/derived.test | 16 |
1 files changed, 16 insertions, 0 deletions
diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index 4f0af1edbaa..c998de7ae6e 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -74,3 +74,19 @@ create table t1 (id int); insert into t1 values (1),(2),(3); describe select * from (select * from t1 group by id) bar; drop table t1; + +# +# test->used_keys test for derived tables +# +create table t1 (mat_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, matintnum CHAR(6) NOT NULL, test MEDIUMINT UNSIGNED NULL); +create table t2 (mat_id MEDIUMINT UNSIGNED NOT NULL, pla_id MEDIUMINT UNSIGNED NOT NULL); +insert into t1 values (NULL, 'a', 1), (NULL, 'b', 2), (NULL, 'c', 3), (NULL, 'd', 4), (NULL, 'e', 5), (NULL, 'f', 6), (NULL, 'g', 7), (NULL, 'h', 8), (NULL, 'i', 9); +insert into t2 values (1, 100), (1, 101), (1, 102), (2, 100), (2, 103), (2, 104), (3, 101), (3, 102), (3, 105); + +SELECT d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; +SELECT d.pla_id, m2.test FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; + +explain SELECT d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; +explain SELECT d.pla_id, m2.test FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; + +drop table t1,t2 |