diff options
Diffstat (limited to 'mysql-test/r/join_outer_jcl6.result')
| -rw-r--r-- | mysql-test/r/join_outer_jcl6.result | 302 |
1 files changed, 272 insertions, 30 deletions
diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result index b4a9bc7c4c2..1aff97ed4af 100644 --- a/mysql-test/r/join_outer_jcl6.result +++ b/mysql-test/r/join_outer_jcl6.result @@ -43,11 +43,11 @@ NULL NULL NULL 4 7 D 7 select t1.*,t2.* from t1 left join t2 using (a); grp a c id a c d 1 1 a 1 1 a 1 +2 2 b NULL NULL NULL NULL +2 3 c NULL NULL NULL NULL 3 4 E 3 4 A 4 3 5 C 3 5 B 5 3 6 D 3 6 C 6 -2 2 b NULL NULL NULL NULL -2 3 c NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL select t1.*,t2.* from t1 left join t2 using (a) where t1.a=t2.a; grp a c id a c d @@ -70,9 +70,9 @@ grp a c id a c d 1 1 a 3 4 A 4 2 2 b 3 5 B 5 2 3 c 3 6 C 6 +3 4 E NULL NULL NULL NULL 3 5 C 3 6 C 6 3 6 D 4 7 D 7 -3 4 E NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL select t1.*,t2.* from t1 natural left outer join t2; grp a c id a c d @@ -103,11 +103,11 @@ id select_type table type possible_keys key key_len ref rows Extra 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); grp a c id a c d a 1 1 a 1 1 a 1 1 +2 2 b NULL NULL NULL NULL NULL +2 3 c NULL NULL NULL NULL NULL 3 4 E 3 4 A 4 4 3 5 C 3 5 B 5 5 3 6 D 3 6 C 6 6 -2 2 b NULL NULL NULL NULL NULL -2 3 c NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL explain select t1.*,t2.*,t3.a from t1 left join t2 on (t3.a=t2.a) left join t1 as t3 on (t1.a=t3.a); ERROR 42S22: Unknown column 't3.a' in 'on clause' @@ -264,16 +264,16 @@ INSERT INTO t4 VALUES (2,-1,10361,1); INSERT INTO t4 VALUES (3,-1,10362,1); 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; fill desc_larga_cat cred_total Grup Places PlacesOcupades +10360 asdfggfg 6.0 0 55 0 10360 asdfggfg 6.0 12 333 0 -10361 Components i Circuits Electronics I 6.0 30 2 0 -10361 Components i Circuits Electronics I 6.0 40 3 0 -10360 asdfggfg 6.0 45 10 0 -10362 Laboratori d`Ordinadors 4.5 10 12 0 -10360 asdfggfg 6.0 55 2 0 10360 asdfggfg 6.0 32767 7 0 10360 asdfggfg 6.0 33 8 0 +10360 asdfggfg 6.0 45 10 0 +10360 asdfggfg 6.0 55 2 0 10360 asdfggfg 6.0 7887 85 0 -10360 asdfggfg 6.0 0 55 0 +10361 Components i Circuits Electronics I 6.0 30 2 0 +10361 Components i Circuits Electronics I 6.0 40 3 0 +10362 Laboratori d`Ordinadors 4.5 10 12 0 10362 Laboratori d`Ordinadors 4.5 11 111 0 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 ; fill idPla @@ -308,8 +308,8 @@ select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner); name name id Antonio Paz El Gato 1 Antonio Paz Perrito 2 -Thimble Smith Happy 3 Lilliana Angelovska NULL NULL +Thimble Smith Happy 3 select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.id is null; name name id Lilliana Angelovska NULL NULL @@ -328,8 +328,8 @@ select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner); name name id Antonio Paz El Gato 1 Antonio Paz Perrito 2 -Thimble Smith Happy 3 Lilliana Angelovska NULL NULL +Thimble Smith Happy 3 select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.id is null; name name id Lilliana Angelovska NULL NULL @@ -348,20 +348,20 @@ select t1.name, t2.name, t2.id,t3.id from t2 right join t1 on (t1.id = t2.owner) name name id id Antonio Paz El Gato 1 1 Antonio Paz Perrito 2 1 -Thimble Smith Happy 3 3 Lilliana Angelovska NULL NULL NULL +Thimble Smith Happy 3 3 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; name name id id Antonio Paz El Gato 1 1 Antonio Paz Perrito 2 1 -Thimble Smith Happy 3 3 NULL NULL NULL 2 +Thimble Smith Happy 3 3 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; name name id owner id Antonio Paz El Gato 1 1 1 Antonio Paz Perrito 2 1 1 -Thimble Smith Happy 3 3 3 NULL NULL NULL NULL 2 +Thimble Smith Happy 3 3 3 drop table t1,t2; create table t1 (id int not null, str char(10), index(str)); insert into t1 values (1, null), (2, null), (3, "foo"), (4, "bar"); @@ -642,9 +642,9 @@ fooID barID fooID 30 3 30 select * from t2 left join t1 ignore index(primary) on t1.fooID = t2.fooID and t1.fooID = 30; fooID barID fooID -30 3 30 10 1 NULL 20 2 NULL +30 3 30 drop table t1,t2; create table t1 (i int); create table t2 (i int); @@ -654,21 +654,21 @@ insert into t2 values(2),(3); insert into t3 values(2),(4); select * from t1 natural left join t2 natural left join t3; i -2 1 +2 select * from t1 natural left join t2 where (t2.i is not null)=0; i 1 select * from t1 natural left join t2 where (t2.i is not null) is not null; i -2 1 +2 select * from t1 natural left join t2 where (i is not null)=0; i select * from t1 natural left join t2 where (i is not null) is not null; i -2 1 +2 drop table t1,t2,t3; create table t1 (f1 integer,f2 integer,f3 integer); create table t2 (f2 integer,f4 integer); @@ -1027,40 +1027,40 @@ a b a b 7 8 7 5 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b > t1.a OR t1.a > t1.b; a b a b +2 1 NULL NULL 3 2 3 0 4 3 4 1 6 5 6 4 -2 1 NULL NULL 8 7 NULL NULL SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT BETWEEN t2.b AND t1.b; a b a b +2 1 NULL NULL 3 2 3 0 4 3 4 1 6 5 6 4 -2 1 NULL NULL 8 7 NULL NULL SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a BETWEEN t2.b AND t1.b); a b a b +2 1 NULL NULL 3 2 3 0 4 3 4 1 6 5 6 4 -2 1 NULL NULL 8 7 NULL NULL 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; a b a b +2 1 NULL NULL 3 2 3 0 4 3 4 1 6 5 6 4 7 8 7 5 -2 1 NULL NULL 8 7 NULL NULL 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); a b a b +2 1 NULL NULL 3 2 3 0 4 3 4 1 6 5 6 4 7 8 7 5 -2 1 NULL NULL 8 7 NULL NULL 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); a b a b @@ -1173,33 +1173,33 @@ INSERT INTO t1 VALUES (1), (2), (3), (4); INSERT INTO t2 VALUES (2), (3); SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1); a b +1 NULL 2 2 3 3 -1 NULL 4 NULL SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1 OR 1); a b +1 NULL 2 2 3 3 -1 NULL 4 NULL SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (0 OR 1); a b +1 NULL 2 2 3 3 -1 NULL 4 NULL SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 2=2); a b +1 NULL 2 2 3 3 -1 NULL 4 NULL SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 1=0); a b +1 NULL 2 2 3 3 -1 NULL 4 NULL DROP TABLE t1,t2; CREATE TABLE t1 ( @@ -1407,6 +1407,248 @@ Warnings: Note 1003 select straight_join `test`.`jt1`.`f1` AS `f1` from `test`.`t1` `jt6` left join (`test`.`t1` `jt3` join `test`.`t1` `jt4` left join `test`.`t1` `jt5` on(1) left join `test`.`t1` `jt2` on(1)) on((`test`.`jt6`.`f1` and 1)) left join `test`.`t1` `jt1` on(1) where 1 DROP TABLE t1; # +# Bug#57688 Assertion `!table || (!table->write_set || bitmap_is_set(table->write_set, field +# +CREATE TABLE t1 (f1 INT NOT NULL, PRIMARY KEY (f1)); +CREATE TABLE t2 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY (f1, f2)); +INSERT INTO t1 VALUES (4); +INSERT INTO t2 VALUES (3, 3); +INSERT INTO t2 VALUES (7, 7); +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 +WHERE t1.f1 = 4 +GROUP BY t2.f1, t2.f2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 Using temporary; Using filesort +1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 1 Using index +SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 +WHERE t1.f1 = 4 +GROUP BY t2.f1, t2.f2; +f1 f1 f2 +4 NULL NULL +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 +WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL +GROUP BY t2.f1, t2.f2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 Using filesort +1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 1 Using where; Using index +SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 +WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL +GROUP BY t2.f1, t2.f2; +f1 f1 f2 +DROP TABLE t1,t2; +# +# Bug#57034 incorrect OUTER JOIN result when joined on unique key +# +CREATE TABLE t1 (pk INT PRIMARY KEY, +col_int INT, +col_int_unique INT UNIQUE KEY); +INSERT INTO t1 VALUES (1,NULL,2), (2,0,0); +CREATE TABLE t2 (pk INT PRIMARY KEY, +col_int INT, +col_int_unique INT UNIQUE KEY); +INSERT INTO t2 VALUES (1,0,1), (2,0,2); +EXPLAIN +SELECT * FROM t1 LEFT JOIN t2 +ON t1.col_int_unique = t2.col_int_unique AND t1.col_int = t2.col_int +WHERE t1.pk=1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 +1 SIMPLE t2 const col_int_unique col_int_unique 5 const 1 +SELECT * FROM t1 LEFT JOIN t2 +ON t1.col_int_unique = t2.col_int_unique AND t1.col_int = t2.col_int +WHERE t1.pk=1; +pk col_int col_int_unique pk col_int col_int_unique +1 NULL 2 NULL NULL NULL +DROP TABLE t1,t2; +# +# Bug#48046 Server incorrectly processing JOINs on NULL values +# +CREATE TABLE `BB` ( +`pk` int(11) NOT NULL AUTO_INCREMENT, +`time_key` time DEFAULT NULL, +`varchar_key` varchar(1) DEFAULT NULL, +`varchar_nokey` varchar(1) DEFAULT NULL, +PRIMARY KEY (`pk`), +KEY `time_key` (`time_key`), +KEY `varchar_key` (`varchar_key`) +) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1; +INSERT INTO `BB` VALUES (10,'18:27:58',NULL,NULL); +SELECT table1.time_key AS field1, table2.pk +FROM BB table1 LEFT JOIN BB table2 +ON table2.varchar_nokey = table1.varchar_key +HAVING field1; +field1 pk +18:27:58 NULL +DROP TABLE BB; +# +# Bug#49600 Server incorrectly processing RIGHT JOIN with +# constant WHERE clause and no index +# +CREATE TABLE `BB` ( +`col_datetime_key` datetime DEFAULT NULL, +`col_varchar_key` varchar(1) DEFAULT NULL, +`col_varchar_nokey` varchar(1) DEFAULT NULL, +KEY `col_datetime_key` (`col_datetime_key`), +KEY `col_varchar_key` (`col_varchar_key`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +INSERT INTO `BB` VALUES ('1900-01-01 00:00:00',NULL,NULL); +SELECT table1.col_datetime_key +FROM BB table1 RIGHT JOIN BB table2 +ON table2 .col_varchar_nokey = table1.col_varchar_key +WHERE 7; +col_datetime_key +NULL +ALTER TABLE BB DISABLE KEYS; +SELECT table1.col_datetime_key +FROM BB table1 RIGHT JOIN BB table2 +ON table2 .col_varchar_nokey = table1.col_varchar_key +WHERE 7; +col_datetime_key +NULL +DROP TABLE BB; +# +# Bug#58490: Incorrect result in multi level OUTER JOIN +# in combination with IS NULL +# +CREATE TABLE t1 (i INT NOT NULL); +INSERT INTO t1 VALUES (0), (2),(3),(4); +CREATE TABLE t2 (i INT NOT NULL); +INSERT INTO t2 VALUES (0),(1), (3),(4); +CREATE TABLE t3 (i INT NOT NULL); +INSERT INTO t3 VALUES (0),(1),(2), (4); +CREATE TABLE t4 (i INT NOT NULL); +INSERT INTO t4 VALUES (0),(1),(2),(3) ; +SELECT * FROM +t1 LEFT JOIN +( t2 LEFT JOIN +( t3 LEFT JOIN +t4 +ON t4.i = t3.i +) +ON t3.i = t2.i +) +ON t2.i = t1.i +; +i i i i +0 0 0 0 +2 NULL NULL NULL +3 3 NULL NULL +4 4 4 NULL +SELECT * FROM +t1 LEFT JOIN +( t2 LEFT JOIN +( t3 LEFT JOIN +t4 +ON t4.i = t3.i +) +ON t3.i = t2.i +) +ON t2.i = t1.i +WHERE t4.i IS NULL; +i i i i +2 NULL NULL NULL +3 3 NULL NULL +4 4 4 NULL +SELECT * FROM +t1 LEFT JOIN +( ( t2 LEFT JOIN +t3 +ON t3.i = t2.i +) +) +ON t2.i = t1.i +WHERE t3.i IS NULL; +i i i +2 NULL NULL +3 3 NULL +SELECT * FROM +t1 LEFT JOIN +( ( t2 LEFT JOIN +t3 +ON t3.i = t2.i +) +JOIN t4 +ON t4.i=t2.i +) +ON t2.i = t1.i +WHERE t3.i IS NULL; +i i i i +2 NULL NULL NULL +3 3 NULL 3 +4 NULL NULL NULL +SELECT * FROM +t1 LEFT JOIN +( ( t2 LEFT JOIN +t3 +ON t3.i = t2.i +) +JOIN (t4 AS t4a JOIN t4 AS t4b ON t4a.i=t4b.i) +ON t4a.i=t2.i +) +ON t2.i = t1.i +WHERE t3.i IS NULL; +i i i i i +2 NULL NULL NULL NULL +3 3 NULL 3 3 +4 NULL NULL NULL NULL +SELECT * FROM +t1 LEFT JOIN +( ( t2 LEFT JOIN +t3 +ON t3.i = t2.i +) +JOIN (t4 AS t4a, t4 AS t4b) +ON t4a.i=t2.i +) +ON t2.i = t1.i +WHERE t3.i IS NULL; +i i i i i +2 NULL NULL NULL NULL +3 3 NULL 3 0 +3 3 NULL 3 1 +3 3 NULL 3 2 +3 3 NULL 3 3 +4 NULL NULL NULL NULL +DROP TABLE t1,t2,t3,t4; +# +# Bug#49322(Duplicate): Server is adding extra NULL row +# on processing a WHERE clause +# +CREATE TABLE h (pk INT NOT NULL, col_int_key INT); +INSERT INTO h VALUES (1,NULL),(4,2),(5,2),(3,4),(2,8); +CREATE TABLE m (pk INT NOT NULL, col_int_key INT); +INSERT INTO m VALUES (1,2),(2,7),(3,5),(4,7),(5,5),(6,NULL),(7,NULL),(8,9); +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); +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; +pk +2 +2 +4 +4 +NULL +NULL +NULL +NULL +NULL +NULL +NULL +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 +WHERE TABLE1.pk IS NULL; +pk +NULL +NULL +NULL +NULL +NULL +NULL +NULL +DROP TABLE h,m,k; +# # Bug#49600: outer join of two single-row tables with joining attributes # evaluated to nulls create table t1 (a int, b int); |