Fixed bug mdev-6292.

Avoided exponential recursive calls of JOIN_CACHE::join_records() in the case
of non-nested outer joins.
A different solution is required to resolve this performance problem for
nested outer joins.

4 files changed, 282 insertions, 4 deletions

diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result
index 2c634c3d92b..be1086b1afc 100644
--- a/mysql-test/r/join_cache.result
+++ b/mysql-test/r/join_cache.result
@@ -5636,4 +5636,153 @@ c
 set join_buffer_size=default;
 set optimizer_switch=@tmp_optimizer_switch;
 DROP table t1,t2,t3;
+#
+# mdev-6292: huge performance degradation for a sequence
+#            of LEFT JOIN operations when using join buffer
+#
+CREATE TABLE t1 (
+id int(11) NOT NULL AUTO_INCREMENT,
+col1 varchar(255) NOT NULL DEFAULT '',
+PRIMARY KEY (id)
+) ENGINE=INNODB;
+CREATE TABLE t2 (
+id int(11) NOT NULL AUTO_INCREMENT,
+parent_id smallint(3) NOT NULL DEFAULT '0',
+col2 varchar(25) NOT NULL DEFAULT '',
+PRIMARY KEY (id)
+) ENGINE=INNODB;
+set join_buffer_size=8192;
+set join_cache_level=0;
+set @init_time:=now();
+SELECT t.* 
+FROM 
+t1 t 
+LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val"
+  LEFT JOIN t2 c2 ON c2.parent_id = t.id AND c2.col2 = "val"
+  LEFT JOIN t2 c3 ON c3.parent_id = t.id AND c3.col2 = "val"
+  LEFT JOIN t2 c4 ON c4.parent_id = t.id AND c4.col2 = "val"
+  LEFT JOIN t2 c5 ON c5.parent_id = t.id AND c5.col2 = "val"
+  LEFT JOIN t2 c6 ON c6.parent_id = t.id AND c6.col2 = "val"
+  LEFT JOIN t2 c7 ON c7.parent_id = t.id AND c7.col2 = "val"
+  LEFT JOIN t2 c8 ON c8.parent_id = t.id AND c8.col2 = "val"
+  LEFT JOIN t2 c9 ON c9.parent_id = t.id AND c9.col2 = "val"
+  LEFT JOIN t2 c10 ON c10.parent_id = t.id AND c10.col2 = "val"
+  LEFT JOIN t2 c11 ON c11.parent_id = t.id AND c11.col2 = "val"
+  LEFT JOIN t2 c12 ON c12.parent_id = t.id AND c12.col2 = "val"
+  LEFT JOIN t2 c13 ON c13.parent_id = t.id AND c13.col2 = "val"
+  LEFT JOIN t2 c14 ON c14.parent_id = t.id AND c14.col2 = "val"
+  LEFT JOIN t2 c15 ON c15.parent_id = t.id AND c15.col2 = "val"
+  LEFT JOIN t2 c16 ON c16.parent_id = t.id AND c16.col2 = "val"
+  LEFT JOIN t2 c17 ON c17.parent_id = t.id AND c17.col2 = "val"
+  LEFT JOIN t2 c18 ON c18.parent_id = t.id AND c18.col2 = "val"
+  LEFT JOIN t2 c19 ON c19.parent_id = t.id AND c19.col2 = "val"
+  LEFT JOIN t2 c20 ON c20.parent_id = t.id AND c20.col2 = "val"
+  LEFT JOIN t2 c21 ON c21.parent_id = t.id AND c21.col2 = "val"
+  LEFT JOIN t2 c22 ON c22.parent_id = t.id AND c22.col2 = "val"
+  LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val"
+  LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val"
+  LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val"
+ORDER BY 
+col1;
+id	col1
+select timestampdiff(second, @init_time, now()) <= 1;
+timestampdiff(second, @init_time, now()) <= 1
+1
+set join_cache_level=2;
+set @init_time:=now();
+SELECT t.* 
+FROM 
+t1 t 
+LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val"
+  LEFT JOIN t2 c2 ON c2.parent_id = t.id AND c2.col2 = "val"
+  LEFT JOIN t2 c3 ON c3.parent_id = t.id AND c3.col2 = "val"
+  LEFT JOIN t2 c4 ON c4.parent_id = t.id AND c4.col2 = "val"
+  LEFT JOIN t2 c5 ON c5.parent_id = t.id AND c5.col2 = "val"
+  LEFT JOIN t2 c6 ON c6.parent_id = t.id AND c6.col2 = "val"
+  LEFT JOIN t2 c7 ON c7.parent_id = t.id AND c7.col2 = "val"
+  LEFT JOIN t2 c8 ON c8.parent_id = t.id AND c8.col2 = "val"
+  LEFT JOIN t2 c9 ON c9.parent_id = t.id AND c9.col2 = "val"
+  LEFT JOIN t2 c10 ON c10.parent_id = t.id AND c10.col2 = "val"
+  LEFT JOIN t2 c11 ON c11.parent_id = t.id AND c11.col2 = "val"
+  LEFT JOIN t2 c12 ON c12.parent_id = t.id AND c12.col2 = "val"
+  LEFT JOIN t2 c13 ON c13.parent_id = t.id AND c13.col2 = "val"
+  LEFT JOIN t2 c14 ON c14.parent_id = t.id AND c14.col2 = "val"
+  LEFT JOIN t2 c15 ON c15.parent_id = t.id AND c15.col2 = "val"
+  LEFT JOIN t2 c16 ON c16.parent_id = t.id AND c16.col2 = "val"
+  LEFT JOIN t2 c17 ON c17.parent_id = t.id AND c17.col2 = "val"
+  LEFT JOIN t2 c18 ON c18.parent_id = t.id AND c18.col2 = "val"
+  LEFT JOIN t2 c19 ON c19.parent_id = t.id AND c19.col2 = "val"
+  LEFT JOIN t2 c20 ON c20.parent_id = t.id AND c20.col2 = "val"
+  LEFT JOIN t2 c21 ON c21.parent_id = t.id AND c21.col2 = "val"
+  LEFT JOIN t2 c22 ON c22.parent_id = t.id AND c22.col2 = "val"
+  LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val"
+  LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val"
+  LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val"
+ORDER BY 
+col1;
+id	col1
+select timestampdiff(second, @init_time, now()) <= 1;
+timestampdiff(second, @init_time, now()) <= 1
+1
+EXPLAIN
+SELECT t.* 
+FROM 
+t1 t 
+LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val"
+  LEFT JOIN t2 c2 ON c2.parent_id = t.id AND c2.col2 = "val"
+  LEFT JOIN t2 c3 ON c3.parent_id = t.id AND c3.col2 = "val"
+  LEFT JOIN t2 c4 ON c4.parent_id = t.id AND c4.col2 = "val"
+  LEFT JOIN t2 c5 ON c5.parent_id = t.id AND c5.col2 = "val"
+  LEFT JOIN t2 c6 ON c6.parent_id = t.id AND c6.col2 = "val"
+  LEFT JOIN t2 c7 ON c7.parent_id = t.id AND c7.col2 = "val"
+  LEFT JOIN t2 c8 ON c8.parent_id = t.id AND c8.col2 = "val"
+  LEFT JOIN t2 c9 ON c9.parent_id = t.id AND c9.col2 = "val"
+  LEFT JOIN t2 c10 ON c10.parent_id = t.id AND c10.col2 = "val"
+  LEFT JOIN t2 c11 ON c11.parent_id = t.id AND c11.col2 = "val"
+  LEFT JOIN t2 c12 ON c12.parent_id = t.id AND c12.col2 = "val"
+  LEFT JOIN t2 c13 ON c13.parent_id = t.id AND c13.col2 = "val"
+  LEFT JOIN t2 c14 ON c14.parent_id = t.id AND c14.col2 = "val"
+  LEFT JOIN t2 c15 ON c15.parent_id = t.id AND c15.col2 = "val"
+  LEFT JOIN t2 c16 ON c16.parent_id = t.id AND c16.col2 = "val"
+  LEFT JOIN t2 c17 ON c17.parent_id = t.id AND c17.col2 = "val"
+  LEFT JOIN t2 c18 ON c18.parent_id = t.id AND c18.col2 = "val"
+  LEFT JOIN t2 c19 ON c19.parent_id = t.id AND c19.col2 = "val"
+  LEFT JOIN t2 c20 ON c20.parent_id = t.id AND c20.col2 = "val"
+  LEFT JOIN t2 c21 ON c21.parent_id = t.id AND c21.col2 = "val"
+  LEFT JOIN t2 c22 ON c22.parent_id = t.id AND c22.col2 = "val"
+  LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val"
+  LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val"
+  LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val"
+ORDER BY 
+col1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t	ALL	NULL	NULL	NULL	NULL	1	Using temporary; Using filesort
+1	SIMPLE	c1	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	c2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	c3	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	c4	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	c5	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	c6	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	c7	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	c8	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	c9	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	c10	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	c11	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	c12	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	c13	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	c14	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	c15	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	c16	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	c17	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	c18	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	c19	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	c20	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	c21	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	c22	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	c23	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	c24	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	c25	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
+set join_buffer_size=default;
+set join_cache_level = default;
+DROP TABLE t1,t2;
 set @@optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/r/join_nested_jcl6.result b/mysql-test/r/join_nested_jcl6.result
index c744df9e2fe..1414f2d5bfd 100644
--- a/mysql-test/r/join_nested_jcl6.result
+++ b/mysql-test/r/join_nested_jcl6.result
@@ -705,18 +705,18 @@ t0.b=t1.b AND
 (t8.b=t9.b OR t8.c IS NULL) AND
 (t9.a=1);
 a	b	a	b	a	b	a	b	a	b	a	b	a	b	a	b	a	b	a	b
-1	2	3	2	4	2	1	2	3	2	2	2	6	2	2	2	0	2	1	2
-1	2	3	2	4	2	1	2	4	2	2	2	6	2	2	2	0	2	1	2
 1	2	3	2	4	2	1	2	3	2	3	1	6	2	1	1	NULL	NULL	1	1
 1	2	3	2	4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL	1	1
+1	2	3	2	4	2	1	2	3	2	2	2	6	2	2	2	0	2	1	2
+1	2	3	2	4	2	1	2	4	2	2	2	6	2	2	2	0	2	1	2
 1	2	3	2	4	2	1	2	3	2	3	1	6	2	1	1	NULL	NULL	1	2
 1	2	3	2	4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL	1	2
 1	2	3	2	4	2	1	2	3	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	1
 1	2	3	2	4	2	1	2	4	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	1
 1	2	3	2	4	2	1	2	3	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	2
 1	2	3	2	4	2	1	2	4	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	2
-1	2	3	2	5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	0	2	1	2
 1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL	1	1
+1	2	3	2	5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	0	2	1	2
 1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL	1	2
 1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	1
 1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	2
diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result
index 459fb62b8b2..ae81535a171 100644
--- a/mysql-test/r/join_outer_jcl6.result
+++ b/mysql-test/r/join_outer_jcl6.result
@@ -696,9 +696,9 @@ insert into t2 values (1,3), (2,3);
 insert into t3 values (2,4),        (3,4);
 select * from t1 left join t2  on  b1 = a1 left join t3  on  c1 = a1  and  b1 is null;
 a1	a2	b1	b2	c1	c2
+3	2	NULL	NULL	3	4
 1	2	1	3	NULL	NULL
 2	2	2	3	NULL	NULL
-3	2	NULL	NULL	3	4
 explain select * from t1 left join t2  on  b1 = a1 left join t3  on  c1 = a1  and  b1 is null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test
index eabab4891ce..940ba212bbe 100644
--- a/mysql-test/t/join_cache.test
+++ b/mysql-test/t/join_cache.test
@@ -3636,5 +3636,134 @@ set optimizer_switch=@tmp_optimizer_switch;
 
 DROP table t1,t2,t3;
 
+--echo #
+--echo # mdev-6292: huge performance degradation for a sequence
+--echo #            of LEFT JOIN operations when using join buffer
+--echo #
+
+--source include/have_innodb.inc
+
+CREATE TABLE t1 (
+  id int(11) NOT NULL AUTO_INCREMENT,
+  col1 varchar(255) NOT NULL DEFAULT '',
+  PRIMARY KEY (id)
+) ENGINE=INNODB;
+
+CREATE TABLE t2 (
+  id int(11) NOT NULL AUTO_INCREMENT,
+  parent_id smallint(3) NOT NULL DEFAULT '0',
+  col2 varchar(25) NOT NULL DEFAULT '',
+  PRIMARY KEY (id)
+) ENGINE=INNODB;
+
+set join_buffer_size=8192;
+
+set join_cache_level=0;
+
+set @init_time:=now(); 
+SELECT t.* 
+FROM 
+  t1 t 
+  LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val"
+  LEFT JOIN t2 c2 ON c2.parent_id = t.id AND c2.col2 = "val"
+  LEFT JOIN t2 c3 ON c3.parent_id = t.id AND c3.col2 = "val"
+  LEFT JOIN t2 c4 ON c4.parent_id = t.id AND c4.col2 = "val"
+  LEFT JOIN t2 c5 ON c5.parent_id = t.id AND c5.col2 = "val"
+  LEFT JOIN t2 c6 ON c6.parent_id = t.id AND c6.col2 = "val"
+  LEFT JOIN t2 c7 ON c7.parent_id = t.id AND c7.col2 = "val"
+  LEFT JOIN t2 c8 ON c8.parent_id = t.id AND c8.col2 = "val"
+  LEFT JOIN t2 c9 ON c9.parent_id = t.id AND c9.col2 = "val"
+  LEFT JOIN t2 c10 ON c10.parent_id = t.id AND c10.col2 = "val"
+  LEFT JOIN t2 c11 ON c11.parent_id = t.id AND c11.col2 = "val"
+  LEFT JOIN t2 c12 ON c12.parent_id = t.id AND c12.col2 = "val"
+  LEFT JOIN t2 c13 ON c13.parent_id = t.id AND c13.col2 = "val"
+  LEFT JOIN t2 c14 ON c14.parent_id = t.id AND c14.col2 = "val"
+  LEFT JOIN t2 c15 ON c15.parent_id = t.id AND c15.col2 = "val"
+  LEFT JOIN t2 c16 ON c16.parent_id = t.id AND c16.col2 = "val"
+  LEFT JOIN t2 c17 ON c17.parent_id = t.id AND c17.col2 = "val"
+  LEFT JOIN t2 c18 ON c18.parent_id = t.id AND c18.col2 = "val"
+  LEFT JOIN t2 c19 ON c19.parent_id = t.id AND c19.col2 = "val"
+  LEFT JOIN t2 c20 ON c20.parent_id = t.id AND c20.col2 = "val"
+  LEFT JOIN t2 c21 ON c21.parent_id = t.id AND c21.col2 = "val"
+  LEFT JOIN t2 c22 ON c22.parent_id = t.id AND c22.col2 = "val"
+  LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val"
+  LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val"
+  LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val"
+ORDER BY 
+  col1;
+select timestampdiff(second, @init_time, now()) <= 1;
+
+set join_cache_level=2;
+
+set @init_time:=now(); 
+SELECT t.* 
+FROM 
+  t1 t 
+  LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val"
+  LEFT JOIN t2 c2 ON c2.parent_id = t.id AND c2.col2 = "val"
+  LEFT JOIN t2 c3 ON c3.parent_id = t.id AND c3.col2 = "val"
+  LEFT JOIN t2 c4 ON c4.parent_id = t.id AND c4.col2 = "val"
+  LEFT JOIN t2 c5 ON c5.parent_id = t.id AND c5.col2 = "val"
+  LEFT JOIN t2 c6 ON c6.parent_id = t.id AND c6.col2 = "val"
+  LEFT JOIN t2 c7 ON c7.parent_id = t.id AND c7.col2 = "val"
+  LEFT JOIN t2 c8 ON c8.parent_id = t.id AND c8.col2 = "val"
+  LEFT JOIN t2 c9 ON c9.parent_id = t.id AND c9.col2 = "val"
+  LEFT JOIN t2 c10 ON c10.parent_id = t.id AND c10.col2 = "val"
+  LEFT JOIN t2 c11 ON c11.parent_id = t.id AND c11.col2 = "val"
+  LEFT JOIN t2 c12 ON c12.parent_id = t.id AND c12.col2 = "val"
+  LEFT JOIN t2 c13 ON c13.parent_id = t.id AND c13.col2 = "val"
+  LEFT JOIN t2 c14 ON c14.parent_id = t.id AND c14.col2 = "val"
+  LEFT JOIN t2 c15 ON c15.parent_id = t.id AND c15.col2 = "val"
+  LEFT JOIN t2 c16 ON c16.parent_id = t.id AND c16.col2 = "val"
+  LEFT JOIN t2 c17 ON c17.parent_id = t.id AND c17.col2 = "val"
+  LEFT JOIN t2 c18 ON c18.parent_id = t.id AND c18.col2 = "val"
+  LEFT JOIN t2 c19 ON c19.parent_id = t.id AND c19.col2 = "val"
+  LEFT JOIN t2 c20 ON c20.parent_id = t.id AND c20.col2 = "val"
+  LEFT JOIN t2 c21 ON c21.parent_id = t.id AND c21.col2 = "val"
+  LEFT JOIN t2 c22 ON c22.parent_id = t.id AND c22.col2 = "val"
+  LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val"
+  LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val"
+  LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val"
+ORDER BY 
+  col1;
+select timestampdiff(second, @init_time, now()) <= 1;
+
+EXPLAIN
+SELECT t.* 
+FROM 
+  t1 t 
+  LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val"
+  LEFT JOIN t2 c2 ON c2.parent_id = t.id AND c2.col2 = "val"
+  LEFT JOIN t2 c3 ON c3.parent_id = t.id AND c3.col2 = "val"
+  LEFT JOIN t2 c4 ON c4.parent_id = t.id AND c4.col2 = "val"
+  LEFT JOIN t2 c5 ON c5.parent_id = t.id AND c5.col2 = "val"
+  LEFT JOIN t2 c6 ON c6.parent_id = t.id AND c6.col2 = "val"
+  LEFT JOIN t2 c7 ON c7.parent_id = t.id AND c7.col2 = "val"
+  LEFT JOIN t2 c8 ON c8.parent_id = t.id AND c8.col2 = "val"
+  LEFT JOIN t2 c9 ON c9.parent_id = t.id AND c9.col2 = "val"
+  LEFT JOIN t2 c10 ON c10.parent_id = t.id AND c10.col2 = "val"
+  LEFT JOIN t2 c11 ON c11.parent_id = t.id AND c11.col2 = "val"
+  LEFT JOIN t2 c12 ON c12.parent_id = t.id AND c12.col2 = "val"
+  LEFT JOIN t2 c13 ON c13.parent_id = t.id AND c13.col2 = "val"
+  LEFT JOIN t2 c14 ON c14.parent_id = t.id AND c14.col2 = "val"
+  LEFT JOIN t2 c15 ON c15.parent_id = t.id AND c15.col2 = "val"
+  LEFT JOIN t2 c16 ON c16.parent_id = t.id AND c16.col2 = "val"
+  LEFT JOIN t2 c17 ON c17.parent_id = t.id AND c17.col2 = "val"
+  LEFT JOIN t2 c18 ON c18.parent_id = t.id AND c18.col2 = "val"
+  LEFT JOIN t2 c19 ON c19.parent_id = t.id AND c19.col2 = "val"
+  LEFT JOIN t2 c20 ON c20.parent_id = t.id AND c20.col2 = "val"
+  LEFT JOIN t2 c21 ON c21.parent_id = t.id AND c21.col2 = "val"
+  LEFT JOIN t2 c22 ON c22.parent_id = t.id AND c22.col2 = "val"
+  LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val"
+  LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val"
+  LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val"
+ORDER BY 
+  col1;
+
+set join_buffer_size=default;
+set join_cache_level = default;
+
+DROP TABLE t1,t2;
+
 # this must be the last command in the file
 set @@optimizer_switch=@save_optimizer_switch;