Fixed bug mdev-5135.

The patch for bug mdev-5105 incorrectly counted conditions in nested joins.

4 files changed, 65 insertions, 0 deletions

diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result
index 267ed06d89b..cfbec56427f 100644
--- a/mysql-test/r/subselect_sj2.result
+++ b/mysql-test/r/subselect_sj2.result
@@ -1097,5 +1097,20 @@ COUNT(*)
 3724
 set optimizer_prune_level=@tmp_951283;
 DROP TABLE t1,t2;
+#
+# Bug mdev-5135: crash on semijoin with nested outer joins
+#
+CREATE TABLE t1 (i1 int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (i2 int, INDEX(i2)) ENGINE=MyISAM;
+CREATE TABLE t3 (i3 int, c varchar(1), INDEX(i3), INDEX(c)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (3,'x'),(4,'y');
+SELECT * FROM t1 WHERE ( 1, 1 ) IN ( 
+SELECT i2, i2 FROM t2 LEFT OUTER JOIN ( 
+t3 AS t3a INNER JOIN t3 AS t3b ON ( t3a.i3 = t3b.i3 ) 
+) ON ( t3a.c = t3b.c ) 
+);
+i1
+DROP TABLE t1,t2,t3;
 # This must be the last in the file:
 set optimizer_switch=@subselect_sj2_tmp;
diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result
index 33bf5e9859d..4f1beb6cac1 100644
--- a/mysql-test/r/subselect_sj2_jcl6.result
+++ b/mysql-test/r/subselect_sj2_jcl6.result
@@ -1112,6 +1112,21 @@ COUNT(*)
 3724
 set optimizer_prune_level=@tmp_951283;
 DROP TABLE t1,t2;
+#
+# Bug mdev-5135: crash on semijoin with nested outer joins
+#
+CREATE TABLE t1 (i1 int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (i2 int, INDEX(i2)) ENGINE=MyISAM;
+CREATE TABLE t3 (i3 int, c varchar(1), INDEX(i3), INDEX(c)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (3,'x'),(4,'y');
+SELECT * FROM t1 WHERE ( 1, 1 ) IN ( 
+SELECT i2, i2 FROM t2 LEFT OUTER JOIN ( 
+t3 AS t3a INNER JOIN t3 AS t3b ON ( t3a.i3 = t3b.i3 ) 
+) ON ( t3a.c = t3b.c ) 
+);
+i1
+DROP TABLE t1,t2,t3;
 # This must be the last in the file:
 set optimizer_switch=@subselect_sj2_tmp;
 #
diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result
index bf5b95d2d8f..d3cc8d08be2 100644
--- a/mysql-test/r/subselect_sj2_mat.result
+++ b/mysql-test/r/subselect_sj2_mat.result
@@ -1099,6 +1099,21 @@ COUNT(*)
 3724
 set optimizer_prune_level=@tmp_951283;
 DROP TABLE t1,t2;
+#
+# Bug mdev-5135: crash on semijoin with nested outer joins
+#
+CREATE TABLE t1 (i1 int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (i2 int, INDEX(i2)) ENGINE=MyISAM;
+CREATE TABLE t3 (i3 int, c varchar(1), INDEX(i3), INDEX(c)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (3,'x'),(4,'y');
+SELECT * FROM t1 WHERE ( 1, 1 ) IN ( 
+SELECT i2, i2 FROM t2 LEFT OUTER JOIN ( 
+t3 AS t3a INNER JOIN t3 AS t3b ON ( t3a.i3 = t3b.i3 ) 
+) ON ( t3a.c = t3b.c ) 
+);
+i1
+DROP TABLE t1,t2,t3;
 # This must be the last in the file:
 set optimizer_switch=@subselect_sj2_tmp;
 set optimizer_switch=default;
diff --git a/mysql-test/t/subselect_sj2.test b/mysql-test/t/subselect_sj2.test
index 1afc39264ea..91db113d35a 100644
--- a/mysql-test/t/subselect_sj2.test
+++ b/mysql-test/t/subselect_sj2.test
@@ -1229,5 +1229,25 @@ WHERE alias3.d IN (
 set optimizer_prune_level=@tmp_951283;
 DROP TABLE t1,t2;
 
+--echo #
+--echo # Bug mdev-5135: crash on semijoin with nested outer joins
+--echo #
+
+CREATE TABLE t1 (i1 int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+
+CREATE TABLE t2 (i2 int, INDEX(i2)) ENGINE=MyISAM;
+
+CREATE TABLE t3 (i3 int, c varchar(1), INDEX(i3), INDEX(c)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (3,'x'),(4,'y');
+
+SELECT * FROM t1 WHERE ( 1, 1 ) IN ( 
+  SELECT i2, i2 FROM t2 LEFT OUTER JOIN ( 
+    t3 AS t3a INNER JOIN t3 AS t3b ON ( t3a.i3 = t3b.i3 ) 
+  ) ON ( t3a.c = t3b.c ) 
+);
+
+DROP TABLE t1,t2,t3;
+
 --echo # This must be the last in the file:
 set optimizer_switch=@subselect_sj2_tmp;