diff options
Diffstat (limited to 'mysql-test/main/join_outer_jcl6.result')
| -rw-r--r-- | mysql-test/main/join_outer_jcl6.result | 22 |
1 files changed, 11 insertions, 11 deletions
diff --git a/mysql-test/main/join_outer_jcl6.result b/mysql-test/main/join_outer_jcl6.result index ff5e76b78ad..a34bf96a0ef 100644 --- a/mysql-test/main/join_outer_jcl6.result +++ b/mysql-test/main/join_outer_jcl6.result @@ -2230,8 +2230,8 @@ SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3 WHERE d3 IS NULL; 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 t2 hash_ALL NULL #hash#$hj 5 test.t1.i1 2 100.00 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE t3 hash_ALL NULL #hash#$hj 5 test.t1.i1 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t2 hash_ALL NULL #hash#$hj 5 test.t1.i1 2 10.00 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t3 hash_ALL NULL #hash#$hj 5 test.t1.i1 2 10.00 Using where; Using join buffer (incremental, BNLH join) Warnings: Note 1003 select `test`.`t1`.`i1` AS `i1`,`test`.`t2`.`i2` AS `i2`,`test`.`t3`.`i3` AS `i3`,`test`.`t3`.`d3` AS `d3` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t2`.`i2` = `test`.`t1`.`i1` and `test`.`t3`.`i3` = `test`.`t1`.`i1` and `test`.`t1`.`i1` is not null and `test`.`t1`.`i1` is not null) where `test`.`t3`.`d3` = 0 or `test`.`t3`.`d3` is null DROP TABLE t1,t2,t3; @@ -2251,7 +2251,7 @@ WHERE b IN (1,2,3) OR b = d; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t3 hash_ALL NULL #hash#$hj 5 const 2 100.00 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t3 hash_ALL NULL #hash#$hj 5 const 2 10.00 Using where; Using join buffer (flat, BNLH join) Warnings: Note 1003 select 10 AS `a`,8 AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t3`.`d` AS `d` from `test`.`t2` left join `test`.`t3` on(`test`.`t3`.`d` = 10 and 10 is not null) where `test`.`t2`.`c` = 8 and `test`.`t3`.`d` = 8 SELECT * FROM t1 INNER JOIN t2 ON c = b LEFT JOIN t3 ON d = a @@ -2368,7 +2368,7 @@ ON t1.x = t2.x WHERE IFNULL(t2.x,0)=0; 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 t2 hash_ALL NULL #hash#$hj 5 test.t1.x 2 100.00 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL NULL #hash#$hj 5 test.t1.x 2 10.00 Using where; Using join buffer (flat, BNLH join) Warnings: Note 1003 select `test`.`t1`.`x` AS `x`,`test`.`t2`.`x` AS `x`,ifnull(`test`.`t2`.`x`,0) AS `IFNULL(t2.x,0)`,`f`(`test`.`t2`.`x`,0) AS `f(t2.x,0)` from `test`.`t` `t1` left join `test`.`t` `t2` on(`test`.`t2`.`x` = `test`.`t1`.`x` and `test`.`t1`.`x` is not null) where ifnull(`test`.`t2`.`x`,0) = 0 SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0) @@ -2384,7 +2384,7 @@ ON t1.x = t2.x WHERE f(t2.x,0)=0; 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 t2 hash_ALL NULL #hash#$hj 5 test.t1.x 2 100.00 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL NULL #hash#$hj 5 test.t1.x 2 10.00 Using where; Using join buffer (flat, BNLH join) Warnings: Note 1003 select `test`.`t1`.`x` AS `x`,`test`.`t2`.`x` AS `x`,ifnull(`test`.`t2`.`x`,0) AS `IFNULL(t2.x,0)`,`f`(`test`.`t2`.`x`,0) AS `f(t2.x,0)` from `test`.`t` `t1` left join `test`.`t` `t2` on(`test`.`t2`.`x` = `test`.`t1`.`x` and `test`.`t1`.`x` is not null) where `f`(`test`.`t2`.`x`,0) = 0 drop function f; @@ -2424,7 +2424,7 @@ FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2 WHERE IFNULL(t2.col3,0) = 0; 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 t2 hash_ALL NULL #hash#$hj 17 test.t1.col1 2 100.00 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL NULL #hash#$hj 17 test.t1.col1 2 10.00 Using where; Using join buffer (flat, BNLH join) Warnings: Note 1003 select `test`.`t1`.`col1` AS `col1`,`test`.`t2`.`col1` AS `col1`,`test`.`t2`.`col3` AS `col3` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`col2` = `test`.`t1`.`col1` and `test`.`t1`.`col1` is not null) where ifnull(`test`.`t2`.`col3`,0) = 0 SELECT t1.col1, t2.col1, t2.col3 @@ -2438,7 +2438,7 @@ FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2 WHERE f1(t2.col3,0) = 0; 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 t2 hash_ALL NULL #hash#$hj 17 test.t1.col1 2 100.00 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL NULL #hash#$hj 17 test.t1.col1 2 10.00 Using where; Using join buffer (flat, BNLH join) Warnings: Note 1003 select `test`.`t1`.`col1` AS `col1`,`test`.`t2`.`col1` AS `col1`,`test`.`t2`.`col3` AS `col3` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`col2` = `test`.`t1`.`col1` and `test`.`t1`.`col1` is not null) where `f1`(`test`.`t2`.`col3`,0) = 0 DROP FUNCTION f1; @@ -2542,7 +2542,7 @@ a b explain extended select * from t1 left join t2 on a=b where (b > 3) is not true; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 -1 SIMPLE t2 hash_ALL NULL #hash#$hj 5 test.t1.a 3 100.00 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL NULL #hash#$hj 5 test.t1.a 3 10.00 Using where; Using join buffer (flat, BNLH join) Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`b` = `test`.`t1`.`a` and `test`.`t1`.`a` is not null) where `test`.`t2`.`b` > 3 is not true select * from t1 left join t2 on a=b where (b > 3) is not false; @@ -2552,7 +2552,7 @@ a b explain extended select * from t1 left join t2 on a=b where (b > 3) is not false; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 -1 SIMPLE t2 hash_ALL NULL #hash#$hj 5 test.t1.a 3 100.00 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL NULL #hash#$hj 5 test.t1.a 3 10.00 Using where; Using join buffer (flat, BNLH join) Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`b` = `test`.`t1`.`a` and `test`.`t1`.`a` is not null) where `test`.`t2`.`b` > 3 is not false drop table t1,t2; @@ -2783,8 +2783,8 @@ id select_type table type possible_keys key key_len ref rows Extra explain select * from t1 left join (t3 join t2) on t1.a=t3.b and t3.a<5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 10 -1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t3 hash_range a #hash#$hj:a 5:5 test.t1.a 5 Using where; Rowid-ordered scan; Using join buffer (incremental, BNLH join) +1 SIMPLE t3 hash_range a #hash#$hj:a 5:5 test.t1.a 5 Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using join buffer (incremental, BNL join) # # .. part 2: make sure condition selectivity can use the condition too. # |