From e0a5319db3f09d2f7b1a5caf358ca2f6e296df74 Mon Sep 17 00:00:00 2001
From: Igor Babaev <igor@askmonty.org>
Date: Wed, 22 Feb 2012 00:10:39 -0800
Subject: Back-ported the fix and test cases for bugs #59487 and #43368 from
 the mysql-5.6 code line.

---
 mysql-test/r/join_outer_innodb.result | 127 ++++++++++++++++++++++++++++++++++
 1 file changed, 127 insertions(+)

(limited to 'mysql-test/r/join_outer_innodb.result')

diff --git a/mysql-test/r/join_outer_innodb.result b/mysql-test/r/join_outer_innodb.result
index c8bc9758408..80505be79be 100644
--- a/mysql-test/r/join_outer_innodb.result
+++ b/mysql-test/r/join_outer_innodb.result
@@ -25,3 +25,130 @@ 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
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer
+1	SIMPLE	t6b	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	
+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
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	t6b	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer
+drop table t1,t2,t3,t4,t5,t6;
-- 
cgit v1.2.1