diff options
Diffstat (limited to 'mysql-test/t/join_outer.test')
| -rw-r--r-- | mysql-test/t/join_outer.test | 30 |
1 files changed, 30 insertions, 0 deletions
diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index 3a6c09f9ba7..54d3b8d997d 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -19,9 +19,11 @@ select t1.*,t2.* from t1 JOIN t2 where t1.a=t2.a; select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) order by t1.grp,t1.a,t2.c; select t1.*,t2.* from { oj t2 left outer join t1 on (t1.a=t2.a) }; select t1.*,t2.* from t1 as t0,{ oj t2 left outer join t1 on (t1.a=t2.a) } WHERE t0.a=2; +--sorted_result select t1.*,t2.* from t1 left join t2 using (a); select t1.*,t2.* from t1 left join t2 using (a) where t1.a=t2.a; select t1.*,t2.* from t1 left join t2 using (a,c); +--sorted_result select t1.*,t2.* from t1 left join t2 using (c); select t1.*,t2.* from t1 natural left outer join t2; @@ -31,6 +33,7 @@ select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) where t2.id is null; explain select t1.*,t2.* from t1,t2 where t1.a=t2.a and isnull(t2.a)=1; explain select t1.*,t2.* from t1 left join t2 on t1.a=t2.a where isnull(t2.a)=1; +--sorted_result select t1.*,t2.*,t3.a from t1 left join t2 on (t1.a=t2.a) left join t1 as t3 on (t2.a=t3.a); # The next query should rearange the left joins to get this to work @@ -201,6 +204,7 @@ INSERT INTO t4 VALUES (1,-1,10360,1); INSERT INTO t4 VALUES (2,-1,10361,1); INSERT INTO t4 VALUES (3,-1,10362,1); +--sorted_result SELECT DISTINCT fill,desc_larga_cat,cred_total,Grup,Places,PlacesOcupades FROM t4 LEFT JOIN t3 ON t3.cod_asig=fill AND estat='S' AND dni_pasaporte='11111111' AND t3.idPla=1 , t2,t1 WHERE fill=t1.cod_asig AND Places>PlacesOcupades AND fill=idAssignatura AND t4.idPla=1 AND papa=-1; SELECT DISTINCT fill,t3.idPla FROM t4 LEFT JOIN t3 ON t3.cod_asig=t4.fill AND t3.estat='S' AND t3.dni_pasaporte='1234' AND t3.idPla=1 ; @@ -233,20 +237,25 @@ INSERT INTO t2 VALUES (1,1,'El Gato'); INSERT INTO t2 VALUES (2,1,'Perrito'); INSERT INTO t2 VALUES (3,3,'Happy'); +--sorted_result select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner); select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.id is null; explain select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.id is null; explain select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.name is null; select count(*) from t1 left join t2 on (t1.id = t2.owner); +--sorted_result select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner); select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.id is null; explain select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.id is null; explain select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.name is null; select count(*) from t2 right join t1 on (t1.id = t2.owner); +--sorted_result select t1.name, t2.name, t2.id,t3.id from t2 right join t1 on (t1.id = t2.owner) left join t1 as t3 on t3.id=t2.owner; +--sorted_result select t1.name, t2.name, t2.id,t3.id from t1 right join t2 on (t1.id = t2.owner) right join t1 as t3 on t3.id=t2.owner; +--sorted_result select t1.name, t2.name, t2.id, t2.owner, t3.id from t1 left join t2 on (t1.id = t2.owner) right join t1 as t3 on t3.id=t2.owner; drop table t1,t2; @@ -421,6 +430,7 @@ insert into t1 (fooID) values (10),(20),(30); insert into t2 values (10,1),(20,2),(30,3); explain select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30; select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30; +--sorted_result select * from t2 left join t1 ignore index(primary) on t1.fooID = t2.fooID and t1.fooID = 30; drop table t1,t2; @@ -430,10 +440,13 @@ create table t3 (i int); insert into t1 values(1),(2); insert into t2 values(2),(3); insert into t3 values(2),(4); +--sorted_result select * from t1 natural left join t2 natural left join t3; select * from t1 natural left join t2 where (t2.i is not null)=0; +--sorted_result select * from t1 natural left join t2 where (t2.i is not null) is not null; select * from t1 natural left join t2 where (i is not null)=0; +--sorted_result select * from t1 natural left join t2 where (i is not null) is not null; drop table t1,t2,t3; @@ -730,11 +743,16 @@ SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b <= t1.a AND t1.a <= t1.b SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a BETWEEN t2.b AND t1.b; SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT BETWEEN t2.b AND t1.b); +--sorted_result SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b > t1.a OR t1.a > t1.b; +--sorted_result SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT BETWEEN t2.b AND t1.b; +--sorted_result SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a BETWEEN t2.b AND t1.b); +--sorted_result SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t2.b > t1.a OR t1.a > t1.b; +--sorted_result SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a != t2.a AND t1.a BETWEEN t2.b AND t1.b); SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a AND (t2.b > t1.a OR t1.a > t1.b); @@ -801,11 +819,16 @@ CREATE TABLE t2 (b int); INSERT INTO t1 VALUES (1), (2), (3), (4); INSERT INTO t2 VALUES (2), (3); +--sorted_result SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1); +--sorted_result SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1 OR 1); +--sorted_result SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (0 OR 1); +--sorted_result SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 2=2); +--sorted_result SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 1=0); DROP TABLE t1,t2; @@ -1108,6 +1131,7 @@ INSERT INTO t3 VALUES (0),(1),(2), (4); CREATE TABLE t4 (i INT NOT NULL); INSERT INTO t4 VALUES (0),(1),(2),(3) ; +--sorted_result SELECT * FROM t1 LEFT JOIN ( t2 LEFT JOIN @@ -1120,6 +1144,7 @@ SELECT * FROM ON t2.i = t1.i ; +--sorted_result SELECT * FROM t1 LEFT JOIN ( t2 LEFT JOIN @@ -1135,6 +1160,7 @@ SELECT * FROM # Most simplified testcase to reproduce the bug. # (Has to be at least a two level nested outer join) +--sorted_result SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN @@ -1150,6 +1176,7 @@ SELECT * FROM # We then add some equi-join inside the query above: # (There Used to be some problems here with first # proposed patch for this bug) +--sorted_result SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN @@ -1162,6 +1189,7 @@ SELECT * FROM ON t2.i = t1.i WHERE t3.i IS NULL; +--sorted_result SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN @@ -1174,6 +1202,7 @@ SELECT * FROM ON t2.i = t1.i WHERE t3.i IS NULL; +--sorted_result SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN @@ -1205,6 +1234,7 @@ CREATE TABLE k (pk INT NOT NULL, col_int_key INT); INSERT INTO k VALUES (1,9),(2,2),(3,5),(4,2),(5,7),(6,0),(7,5); # Baseline query wo/ 'WHERE ... IS NULL' - was correct +--sorted_result SELECT TABLE1.pk FROM k TABLE1 RIGHT JOIN h TABLE2 ON TABLE1.col_int_key=TABLE2.col_int_key RIGHT JOIN m TABLE4 ON TABLE2.col_int_key=TABLE4.col_int_key; |