diff options
Diffstat (limited to 'mysql-test/r/join_outer.result')
| -rw-r--r-- | mysql-test/r/join_outer.result | 200 |
1 files changed, 200 insertions, 0 deletions
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index c448bddd3c9..490ef12535d 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1308,4 +1308,204 @@ WHERE (COALESCE(t1.f1, t2.f1), f3) IN ((1, 3), (2, 2)); f1 f2 f3 f1 f2 1 NULL 3 NULL NULL DROP TABLE t1, t2; +# +# Bug#52357: Assertion failed: join->best_read in greedy_search +# optimizer_search_depth=0 +# +CREATE TABLE t1( a INT ); +INSERT INTO t1 VALUES (1),(2); +SET optimizer_search_depth = 0; +# Should not core dump on query preparation +EXPLAIN +SELECT 1 +FROM t1 tt3 LEFT OUTER JOIN t1 tt4 ON 1 +LEFT OUTER JOIN t1 tt5 ON 1 +LEFT OUTER JOIN t1 tt6 ON 1 +LEFT OUTER JOIN t1 tt7 ON 1 +LEFT OUTER JOIN t1 tt8 ON 1 +RIGHT OUTER JOIN t1 tt2 ON 1 +RIGHT OUTER JOIN t1 tt1 ON 1 +STRAIGHT_JOIN t1 tt9 ON 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE tt1 ALL NULL NULL NULL NULL 2 +1 SIMPLE tt2 ALL NULL NULL NULL NULL 2 +1 SIMPLE tt3 ALL NULL NULL NULL NULL 2 +1 SIMPLE tt4 ALL NULL NULL NULL NULL 2 +1 SIMPLE tt5 ALL NULL NULL NULL NULL 2 +1 SIMPLE tt6 ALL NULL NULL NULL NULL 2 +1 SIMPLE tt7 ALL NULL NULL NULL NULL 2 +1 SIMPLE tt8 ALL NULL NULL NULL NULL 2 +1 SIMPLE tt9 ALL NULL NULL NULL NULL 2 Using join buffer +SET optimizer_search_depth = DEFAULT; +DROP TABLE t1; +# +# Bug#46091 STRAIGHT_JOIN + RIGHT JOIN returns different result +# +CREATE TABLE t1 (f1 INT NOT NULL); +INSERT INTO t1 VALUES (9),(0); +CREATE TABLE t2 (f1 INT NOT NULL); +INSERT INTO t2 VALUES +(5),(3),(0),(3),(1),(0),(1),(7),(1),(0),(0),(8),(4),(9),(0),(2),(0),(8),(5),(1); +SELECT STRAIGHT_JOIN COUNT(*) FROM t1 TA1 +RIGHT JOIN t2 TA2 JOIN t2 TA3 ON TA2.f1 ON TA3.f1; +COUNT(*) +476 +EXPLAIN SELECT STRAIGHT_JOIN COUNT(*) FROM t1 TA1 +RIGHT JOIN t2 TA2 JOIN t2 TA3 ON TA2.f1 ON TA3.f1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE TA2 ALL NULL NULL NULL NULL 20 Using where +1 SIMPLE TA3 ALL NULL NULL NULL NULL 20 Using join buffer +1 SIMPLE TA1 ALL NULL NULL NULL NULL 2 +DROP TABLE t1, t2; +# +# Bug#48971 Segfault in add_found_match_trig_cond () at sql_select.cc:5990 +# +CREATE TABLE t1(f1 INT, PRIMARY KEY (f1)); +INSERT INTO t1 VALUES (1),(2); +EXPLAIN EXTENDED SELECT STRAIGHT_JOIN jt1.f1 FROM t1 AS jt1 +LEFT JOIN t1 AS jt2 +RIGHT JOIN t1 AS jt3 +JOIN t1 AS jt4 ON 1 +LEFT JOIN t1 AS jt5 ON 1 +ON 1 +RIGHT JOIN t1 AS jt6 ON jt6.f1 +ON 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE jt1 index NULL PRIMARY 4 NULL 2 100.00 Using index +1 SIMPLE jt6 index NULL PRIMARY 4 NULL 2 100.00 Using index +1 SIMPLE jt3 index NULL PRIMARY 4 NULL 2 100.00 Using index +1 SIMPLE jt4 index NULL PRIMARY 4 NULL 2 100.00 Using index +1 SIMPLE jt5 index NULL PRIMARY 4 NULL 2 100.00 Using index +1 SIMPLE jt2 index NULL PRIMARY 4 NULL 2 100.00 Using index +Warnings: +Note 1003 select straight_join `test`.`jt1`.`f1` AS `f1` from `test`.`t1` `jt1` left join (`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))) on(1) where 1 +EXPLAIN EXTENDED SELECT STRAIGHT_JOIN jt1.f1 FROM t1 AS jt1 +RIGHT JOIN t1 AS jt2 +RIGHT JOIN t1 AS jt3 +JOIN t1 AS jt4 ON 1 +LEFT JOIN t1 AS jt5 ON 1 +ON 1 +RIGHT JOIN t1 AS jt6 ON jt6.f1 +ON 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE jt6 index NULL PRIMARY 4 NULL 2 100.00 Using index +1 SIMPLE jt3 index NULL PRIMARY 4 NULL 2 100.00 Using index +1 SIMPLE jt4 index NULL PRIMARY 4 NULL 2 100.00 Using index +1 SIMPLE jt5 index NULL PRIMARY 4 NULL 2 100.00 Using index +1 SIMPLE jt2 index NULL PRIMARY 4 NULL 2 100.00 Using index +1 SIMPLE jt1 index NULL PRIMARY 4 NULL 2 100.00 Using index +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#49600: outer join of two single-row tables with joining attributes +# evaluated to nulls +create table t1 (a int, b int); +create table t2 (a int, b int); +insert into t1 values (1, NULL); +insert into t2 values (2, NULL); +select * from t1 left join t2 on t1.b=t2.b; +a b a b +1 NULL NULL NULL +select * from t1 left join t2 on t1.b=t2.b where 1=1; +a b a b +1 NULL NULL NULL +drop table t1,t2; +# +# Bug#53161: outer join in the derived table is erroneously converted +# into an inner join for a query with a group by clause +# +create table t1 (pk int not null primary key, a int not null); +create table t2 like t1; +create table t3 like t1; +create table t4 (pk int not null primary key); +insert into t1 values (1000, 1), (1001, 1); +insert into t2 values (2000, 2), (2001, 2); +insert into t3 values (3000, 3), (3001, 2); +insert into t4 values (4000), (4001); +explain extended +select t2.pk, +(select t3.pk+if(isnull(t4.pk),0,t4.pk) +from t3 left join t4 on t4.pk=t3.pk +where t3.pk=t2.pk+1000 limit 1 ) as t +from t1,t2 +where t2.pk=t1.pk+1000 and t1.pk>1000 +group by t2.pk; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 100.00 Using where; Using index; Using temporary; Using filesort +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using where; Using index +2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using where; Using index +2 DEPENDENT SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t3.pk 1 100.00 Using where; Using index +Warnings: +Note 1276 Field or reference 'test.t2.pk' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t2`.`pk` AS `pk`,(select (`test`.`t3`.`pk` + if(isnull(`test`.`t4`.`pk`),0,`test`.`t4`.`pk`)) from `test`.`t3` left join `test`.`t4` on((`test`.`t4`.`pk` = `test`.`t3`.`pk`)) where (`test`.`t3`.`pk` = (`test`.`t2`.`pk` + 1000)) limit 1) AS `t` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`pk` = (`test`.`t1`.`pk` + 1000)) and (`test`.`t1`.`pk` > 1000)) group by `test`.`t2`.`pk` +select t2.pk, +(select t3.pk+if(isnull(t4.pk),0,t4.pk) +from t3 left join t4 on t4.pk=t3.pk +where t3.pk=t2.pk+1000 limit 1 ) as t +from t1,t2 +where t2.pk=t1.pk+1000 and t1.pk>1000 +group by t2.pk; +pk t +2001 3001 +drop table t1,t2,t3,t4; +# +# Bug#57024: Poor performance when conjunctive condition over the outer +# table is used in the on condition of an outer join +# +create table t1 (a int); +insert into t1 values (NULL), (NULL), (NULL), (NULL); +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 values (4), (2), (1), (3); +create table t2 like t1; +insert into t2 select if(t1.a is null, 10, t1.a) from t1; +create table t3 (a int, b int, index idx(a)); +insert into t3 values (1, 100), (3, 301), (4, 402), (1, 102), (1, 101); +analyze table t1,t2,t3; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +test.t3 analyze status OK +flush status; +select sum(t3.b) from t1 left join t3 on t3.a=t1.a and t1.a is not null; +sum(t3.b) +1006 +show status like "handler_read%"; +Variable_name Value +Handler_read_first 0 +Handler_read_key 4 +Handler_read_next 5 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 1048581 +flush status; +select sum(t3.b) from t2 left join t3 on t3.a=t2.a and t2.a <> 10; +sum(t3.b) +1006 +show status like "handler_read%"; +Variable_name Value +Handler_read_first 0 +Handler_read_key 4 +Handler_read_next 5 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 1048581 +drop table t1,t2,t3; End of 5.1 tests |