diff options
Diffstat (limited to 'mysql-test/r/join_outer.result')
| -rw-r--r-- | mysql-test/r/join_outer.result | 59 |
1 files changed, 58 insertions, 1 deletions
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 918ed1f21e6..49ea07c5dff 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1685,7 +1685,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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` +Note 1003 select `test`.`t2`.`pk` AS `pk`,<expr_cache><`test`.`t2`.`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 @@ -1725,6 +1725,8 @@ 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); +insert into t3 values (11, 100), (33, 301), (44, 402), (11, 102), (11, 101); +insert into t3 values (22, 100), (53, 301), (64, 402), (22, 102), (22, 101); analyze table t1,t2,t3; Table Op Msg_type Msg_text test.t1 analyze status OK @@ -1842,3 +1844,58 @@ FROM t1 LEFT JOIN t2 ON (t2.b) IN (SELECT c2 from t3) AND t2.a = 1; b NULL DROP TABLE t1,t2,t3; +# +# lp:825035 second execution of PS with outer join +# +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (1),(2),(3),(4); +CREATE TABLE t2 (a int); +PREPARE stmt FROM +"SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a"; +EXECUTE stmt; +a a +1 NULL +2 NULL +3 NULL +4 NULL +EXECUTE stmt; +a a +1 NULL +2 NULL +3 NULL +4 NULL +DEALLOCATE PREPARE stmt; +DROP TABLE t1,t2; +# +# lp:838633 second execution of PS with outer join +# converted to inner join +# +CREATE TABLE t1 ( b int NOT NULL ) ; +INSERT INTO t1 VALUES (9),(10); +CREATE TABLE t2 ( b int NOT NULL, PRIMARY KEY (b)) ; +INSERT INTO t2 VALUES +(75),(76),(77),(78),(79),(80),(81),(82),(83),(84),(85),(86),(87),(88),(89), +(10), (90),(91),(92),(93),(94),(95),(96),(97),(98),(99),(100); +CREATE TABLE t3 ( a int, b int NOT NULL , PRIMARY KEY (b)) ; +INSERT INTO t3 VALUES +(0,6),(0,7),(0,8),(2,9),(0,10),(2,21),(0,22),(2,23),(2,24),(2,25); +SET SESSION join_cache_level=4; +EXPLAIN EXTENDED +SELECT * FROM (t2 LEFT JOIN t1 ON t1.b = t2.b) JOIN t3 ON t1.b = t3.b; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t3 hash_ALL PRIMARY #hash#PRIMARY 4 test.t1.b 10 10.00 Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_index PRIMARY #hash#PRIMARY:PRIMARY 4:4 test.t1.b 27 3.70 Using join buffer (incremental, BNLH join) +Warnings: +Note 1003 select `test`.`t2`.`b` AS `b`,`test`.`t1`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t2` join `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t1`.`b`)) +PREPARE stmt FROM +'SELECT * FROM (t2 LEFT JOIN t1 ON t1.b = t2.b) JOIN t3 ON t1.b = t3.b'; +EXECUTE stmt; +b b a b +10 10 0 10 +EXECUTE stmt; +b b a b +10 10 0 10 +DEALLOCATE PREPARE stmt; +SET SESSION join_cache_level=default; +DROP TABLE t1,t2,t3; |