diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/union.result | 33 | ||||
| -rw-r--r-- | mysql-test/t/union.test | 17 |
2 files changed, 50 insertions, 0 deletions
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index cf166f47f35..44d75e54771 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -230,3 +230,36 @@ id_master id text1 text2 1 3 NULL bar3 1 4 foo4 bar4 drop table if exists t1,t2; +create table t1 (a int not null primary key auto_increment, b int, key(b)); +create table t2 (a int not null primary key auto_increment, b int); +insert into t1 (b) values (1),(2),(2),(3); +insert into t2 (b) values (10),(11),(12),(13); +explain (select * from t1 where a=1) union (select * from t2 where a=1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 +2 UNION t2 const PRIMARY PRIMARY 4 const 1 +(select * from t1 where a=5) union (select * from t2 where a=1); +a b +1 10 +(select * from t1 where a=5 and a=6) union (select * from t2 where a=1); +a b +1 10 +(select t1.a,t1.b from t1,t2 where t1.a=5) union (select * from t2 where a=1); +a b +1 10 +(select * from t1 where a=1) union (select t1.a,t2.a from t1,t2 where t1.a=t2.a); +a b +1 1 +2 2 +3 3 +4 4 +explain (select * from t1 where a=1 and b=10) union (select t1.a,t2.a from t1,t2 where t1.a=t2.a); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 UNION t1 index PRIMARY PRIMARY 4 NULL 4 Using index +2 UNION t2 index PRIMARY PRIMARY 4 NULL 4 Using where; Using index +explain (select * from t1 where a=1) union (select * from t1 where b=1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 +2 UNION t1 ref b b 5 const 1 Using where +drop table t1,t2; diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index af2dd154974..b73d9b5fdfc 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -124,3 +124,20 @@ INSERT INTO t2 (id, id_master, text1, text2) VALUES("4", "1", SELECT 1 AS id_master, 1 AS id, NULL AS text1, 'ABCDE' AS text2 UNION SELECT id_master, t2.id, text1, text2 FROM t1 LEFT JOIN t2 ON t1.id = t2.id_master; SELECT 1 AS id_master, 1 AS id, 'ABCDE' AS text1, 'ABCDE' AS text2 UNION SELECT id_master, t2.id, text1, text2 FROM t1 LEFT JOIN t2 ON t1.id = t2.id_master; drop table if exists t1,t2; + +# +# Test of bug when using the same table multiple times +# +create table t1 (a int not null primary key auto_increment, b int, key(b)); +create table t2 (a int not null primary key auto_increment, b int); +insert into t1 (b) values (1),(2),(2),(3); +insert into t2 (b) values (10),(11),(12),(13); + +explain (select * from t1 where a=1) union (select * from t2 where a=1); +(select * from t1 where a=5) union (select * from t2 where a=1); +(select * from t1 where a=5 and a=6) union (select * from t2 where a=1); +(select t1.a,t1.b from t1,t2 where t1.a=5) union (select * from t2 where a=1); +(select * from t1 where a=1) union (select t1.a,t2.a from t1,t2 where t1.a=t2.a); +explain (select * from t1 where a=1 and b=10) union (select t1.a,t2.a from t1,t2 where t1.a=t2.a); +explain (select * from t1 where a=1) union (select * from t1 where b=1); +drop table t1,t2; |