1 files changed, 82 insertions, 0 deletions
diff --git a/mysql-test/t/join_outer_innodb.test b/mysql-test/t/join_outer_innodb.test
index 40add7f488f..8d6fa71bc8f 100644
--- a/mysql-test/t/join_outer_innodb.test
+++ b/mysql-test/t/join_outer_innodb.test
@@ -24,3 +24,85 @@ SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
   WHERE t1.name LIKE 'A%' OR FALSE;
 
 DROP TABLE t1,t2;
+
+#
+# Bug #848652: crash with RIGHT JOIN and GROUP BY
+# 
+
+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;
+
+DROP TABLE t1,t2;
+
+--echo #
+--echo Bug #59487: WRONG RESULT WITH STRAIGHT_JOIN AND RIGHT JOIN
+--echo #
+
+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);
+
+# EXPLAIN of query above (t2 is before t5 in plan)
+
+let $rest_of_query=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;
+
+eval SELECT STRAIGHT_JOIN $rest_of_query;
+eval EXPLAIN SELECT STRAIGHT_JOIN $rest_of_query;
+
+# right result (same query, just remove STRAIGHT_JOIN):
+
+eval SELECT $rest_of_query;
+eval EXPLAIN SELECT $rest_of_query;
+
+drop table t1,t2,t3,t4,t5,t6;