diff options
author | Marko Mäkelä <marko.makela@mariadb.com> | 2018-08-03 15:57:23 +0300 |
---|---|---|
committer | Marko Mäkelä <marko.makela@mariadb.com> | 2018-08-03 15:57:23 +0300 |
commit | 05459706f22b1d9d22abd24e6de3b505d787beb3 (patch) | |
tree | 3e850e0cbf5489fbf36f5c1579248e344bfe2395 /mysql-test/main/join_outer_jcl6.result | |
parent | 8ae2a2dbe6fc52d40ec88c325b9a44de52f83f2f (diff) | |
parent | e6a808bec790fdbbf1dc0a6b03fb6c8b6e41dc01 (diff) | |
download | mariadb-git-05459706f22b1d9d22abd24e6de3b505d787beb3.tar.gz |
Merge 10.2 into 10.3
Diffstat (limited to 'mysql-test/main/join_outer_jcl6.result')
-rw-r--r-- | mysql-test/main/join_outer_jcl6.result | 50 |
1 files changed, 50 insertions, 0 deletions
diff --git a/mysql-test/main/join_outer_jcl6.result b/mysql-test/main/join_outer_jcl6.result index 9ff501baf6a..cb582b1399f 100644 --- a/mysql-test/main/join_outer_jcl6.result +++ b/mysql-test/main/join_outer_jcl6.result @@ -2471,6 +2471,56 @@ id sid id 1 NULL NULL 2 NULL NULL drop table t1, t2; +# +# MDEV-16726: SELECT with STRAGHT JOIN containing NESTED RIGHT JOIN +# converted to INNER JOIN with first constant inner table +# +CREATE TABLE t1 ( +pk int PRIMARY KEY, i1 int, v1 varchar(1), v2 varchar(1), KEY v1 (v1,i1) +) engine=MyISAM; +INSERT INTO t1 VALUES +(8,3,'c','c'),(9,4,'z','z'),(10,3,'i','i'),(11,186,'x','x'), +(14,226,'m','m'),(15,133,'p','p'); +CREATE TABLE t2 ( +pk int PRIMARY KEY, i1 int, v1 varchar(1), v2 varchar(1) +) engine=MyISAM; +INSERT INTO t2 VALUES (10,6,'p','p'); +EXPLAIN EXTENDED +SELECT STRAIGHT_JOIN t2.v2 +FROM +(t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2) +RIGHT JOIN +(t2,t1) +ON t1.pk = t2.pk AND t2.v2 = tb1.v1 +WHERE tb1.pk = 40 +ORDER BY tb1.i1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Note 1003 select straight_join 'p' AS `v2` from `test`.`t1` join `test`.`t1` `tb1` left join `test`.`t1` `tb2` on(multiple equal(NULL, NULL)) where 0 order by NULL +EXPLAIN EXTENDED +SELECT STRAIGHT_JOIN t2.v2 +FROM +(t2,t1) +LEFT JOIN +(t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2) +ON t1.pk = t2.pk AND t2.v2 = tb1.v1 +WHERE tb1.pk = 40 +ORDER BY tb1.i1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Note 1003 select straight_join 'p' AS `v2` from `test`.`t1` join `test`.`t1` `tb1` left join `test`.`t1` `tb2` on(multiple equal(NULL, NULL)) where 0 order by NULL +SELECT STRAIGHT_JOIN DISTINCT t2.v2 +FROM +(t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2) +RIGHT JOIN +(t2,t1) +ON t1.pk = t2.pk AND t2.v2 = tb1.v1 +WHERE tb1.pk = 40 +ORDER BY tb1.i1; +v2 +DROP TABLE t1,t2; # end of 5.5 tests SET optimizer_switch=@save_optimizer_switch; set join_cache_level=default; |