diff options
Diffstat (limited to 'mysql-test/r/join_outer_innodb.result')
| -rw-r--r-- | mysql-test/r/join_outer_innodb.result | 135 |
1 files changed, 135 insertions, 0 deletions
diff --git a/mysql-test/r/join_outer_innodb.result b/mysql-test/r/join_outer_innodb.result index 56e557ec881..3138701fc76 100644 --- a/mysql-test/r/join_outer_innodb.result +++ b/mysql-test/r/join_outer_innodb.result @@ -17,3 +17,138 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL fkey 5 NULL 5 Using index 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.fkey 1 Using where DROP TABLE t1,t2; +CREATE TABLE t1(a int, b int, KEY (a), PRIMARY KEY (b)) ENGINE=InnoDB; +CREATE TABLE t2 (b int, PRIMARY KEY (b)); +INSERT INTO t2 VALUES (4),(9); +SELECT STRAIGHT_JOIN t1.a FROM t1 RIGHT JOIN t2 ON t1.b = t2.b +WHERE (t1.b NOT BETWEEN 1 AND 7 OR t1.a IS NULL AND t1.b = t2.b) AND t2.b = 4 +GROUP BY 1; +a +DROP TABLE t1,t2; +# +Bug #59487: WRONG RESULT WITH STRAIGHT_JOIN AND RIGHT JOIN +# +CREATE TABLE t1 ( +pk int(11) NOT NULL, +col_varchar_10_latin1_key varchar(10) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t1 VALUES (1,'1'); +CREATE TABLE t2 ( +pk int(11) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t2 VALUES (1); +CREATE TABLE t3 ( +pk int(11) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t3 VALUES (1); +CREATE TABLE t4 ( +pk int(11) NOT NULL, +col_int int(11) DEFAULT NULL, +col_int_key int(11) DEFAULT NULL, +col_varchar_10_latin1_key varchar(10) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t4 VALUES (1,1,1,'1'); +CREATE TABLE t5 ( +col_int int(11) DEFAULT NULL, +col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t5 VALUES (1,'1'); +CREATE TABLE t6 ( +col_int_key int(11) DEFAULT NULL, +col_varchar_10_latin1_key varchar(10) DEFAULT NULL, +pk int(11) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t6 VALUES (1,'1',1); +SELECT STRAIGHT_JOIN t6a.pk, t2.pk +FROM t6 AS t6a +LEFT JOIN +( +t2 +RIGHT JOIN +( +(t1 LEFT JOIN (t4 JOIN t3 ON t4.col_int) ON t4.col_int_key = t1.pk) +LEFT JOIN +(t5 JOIN t6 AS t6b +ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key) +ON t1.pk = t5.col_int +) +ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key +AND t5.col_varchar_10_utf8_key = 0 +) +ON t6a.pk IS TRUE +WHERE t6b.col_int_key IS TRUE; +pk pk +1 NULL +EXPLAIN SELECT STRAIGHT_JOIN t6a.pk, t2.pk +FROM t6 AS t6a +LEFT JOIN +( +t2 +RIGHT JOIN +( +(t1 LEFT JOIN (t4 JOIN t3 ON t4.col_int) ON t4.col_int_key = t1.pk) +LEFT JOIN +(t5 JOIN t6 AS t6b +ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key) +ON t1.pk = t5.col_int +) +ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key +AND t5.col_varchar_10_utf8_key = 0 +) +ON t6a.pk IS TRUE +WHERE t6b.col_int_key IS TRUE; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t6a ALL NULL NULL NULL NULL 1 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 1 Using join buffer (incremental, BNL join) +1 SIMPLE t5 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t6b ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) +SELECT t6a.pk, t2.pk +FROM t6 AS t6a +LEFT JOIN +( +t2 +RIGHT JOIN +( +(t1 LEFT JOIN (t4 JOIN t3 ON t4.col_int) ON t4.col_int_key = t1.pk) +LEFT JOIN +(t5 JOIN t6 AS t6b +ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key) +ON t1.pk = t5.col_int +) +ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key +AND t5.col_varchar_10_utf8_key = 0 +) +ON t6a.pk IS TRUE +WHERE t6b.col_int_key IS TRUE; +pk pk +1 NULL +EXPLAIN SELECT t6a.pk, t2.pk +FROM t6 AS t6a +LEFT JOIN +( +t2 +RIGHT JOIN +( +(t1 LEFT JOIN (t4 JOIN t3 ON t4.col_int) ON t4.col_int_key = t1.pk) +LEFT JOIN +(t5 JOIN t6 AS t6b +ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key) +ON t1.pk = t5.col_int +) +ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key +AND t5.col_varchar_10_utf8_key = 0 +) +ON t6a.pk IS TRUE +WHERE t6b.col_int_key IS TRUE; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t6a ALL NULL NULL NULL NULL 1 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 1 Using join buffer (incremental, BNL join) +1 SIMPLE t5 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t6b ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) +drop table t1,t2,t3,t4,t5,t6; |