MDEV-18479 Assertion `join->best_read < double(1.79769313486231570815e+308L)'

or server crashes in JOIN::fix_all_splittings_in_plan after EXPLAIN

This patch resolves the problem of overflowing when performing
calculations to estimate the cost of an evaluated query execution plan.
The overflowing in a non-debug build could cause different kind of
problems uncluding crashes of the server.

1 files changed, 401 insertions, 0 deletions

diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index 12811ebc6b3..ab363934e8f 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -2640,3 +2640,404 @@ Note	1003	select straight_join `test`.`t1`.`c1` AS `c1` from `test`.`t1` where <
 DROP TABLE t1, t2;
 set optimizer_switch=@exit_optimizer_switch;
 set join_cache_level=@exit_join_cache_level;
+#
+# Bug mdev-12812: EXPLAIN for query with many expensive derived
+#
+CREATE TABLE t1
+(id int auto_increment primary key,
+uid int NOT NULL,
+gp_id int NOT NULL,
+r int NOT NULL
+);
+INSERT INTO t1(uid,gp_id,r)  VALUES
+(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),
+(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),
+(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),
+(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),
+(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),
+(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),
+(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),
+(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),
+(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),
+(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),
+(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),
+(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),
+(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),
+(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),
+(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),
+(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),
+(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),
+(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),
+(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),
+(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),
+(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),
+(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),
+(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),
+(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),
+(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),
+(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),
+(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),
+(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),
+(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),
+(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),
+(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),
+(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),
+(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),
+(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),
+(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),
+(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),
+(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),
+(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),
+(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),
+(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),
+(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),
+(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),
+(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),
+(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),
+(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),
+(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),
+(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),
+(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),
+(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),
+(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),
+(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),
+(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),
+(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),
+(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),
+(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),
+(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),
+(1,11,1);
+CREATE TABLE t2 (id int) ;
+INSERT INTO t2 VALUES (1);
+explain SELECT 1 FROM t2 JOIN
+(SELECT t2.id
+FROM t2
+JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+)   gp_1 ON gp_1.id=t2.id
+JOIN
+(SELECT t2.id
+FROM t2
+JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+)   gp_2 ON gp_2.id=t2.id
+JOIN
+(SELECT t2.id
+FROM t2
+JOIN t1 p1 ON p1.r=1 AND p1.uid=t2.id
+JOIN t1 p3 ON p3.r=3 AND p3.uid=t2.id
+JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+) gp_3 ON gp_3.id=t2.id
+JOIN
+(SELECT t2.id
+FROM t2
+JOIN t1 p1 ON p1.r=1 AND p1.uid=t2.id
+JOIN t1 p3 ON p3.r=3 AND p3.uid=t2.id
+JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+)   gp_4 ON gp_4.id=t2.id
+JOIN
+(SELECT t2.id
+FROM t2
+JOIN t1 p1 ON p1.r=1 AND p1.uid=t2.id
+JOIN t1 p3 ON p3.r=3 AND p3.uid=t2.id
+JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+)   gp_5 ON gp_5.id=t2.id
+JOIN
+(SELECT t2.id
+FROM t2
+JOIN t1 p1 ON p1.r=1 AND p1.uid=t2.id
+JOIN t1 p3 ON p3.r=3 AND p3.uid=t2.id
+JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+)   gp_6 ON gp_6.id=t2.id
+JOIN
+(SELECT t2.id
+FROM t2
+JOIN t1 p1 ON p1.r=1 AND p1.uid=t2.id
+JOIN t1 p3 ON p3.r=3 AND p3.uid=t2.id
+JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+WHERE p1.gp_id=7)   gp_7 ON gp_7.id=t2.id
+JOIN
+(SELECT t2.id
+FROM t2
+JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+)   gp_8 ON gp_8.id=t2.id
+JOIN
+(SELECT t2.id
+FROM t2
+JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+)   gp_9 ON gp_9.id=t2.id
+JOIN
+(SELECT t2.id
+FROM t2
+JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+)   gp_14 ON gp_14.id=t2.id
+JOIN
+(SELECT t2.id
+FROM t2
+JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+)   gp_15 ON gp_15.id=t2.id
+JOIN
+(SELECT t2.id
+FROM t2
+JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+)  gp_16 ON gp_16.id=t2.id
+JOIN
+(SELECT t2.id
+FROM t2
+JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+)gp_17 ON gp_17.id=t2.id
+JOIN
+(SELECT t2.id
+FROM t2
+JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+)gp_18 ON gp_18.id=t2.id
+JOIN
+(SELECT t2.id
+FROM t2
+JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id
+JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id
+)gp_19 ON gp_19.id=t2.id
+JOIN
+(SELECT t2.id
+FROM t2
+JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id
+JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id
+JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id
+JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id
+JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id
+) gp_20 ON gp_20.id=t2.id ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	p4	ALL	NULL	NULL	NULL	NULL	550	Using where
+1	SIMPLE	p5	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	p6	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p7	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p8	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p9	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p10	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p4	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p5	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p6	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p7	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p8	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p9	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p10	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p1	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p3	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p4	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p5	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p6	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p7	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p8	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p9	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p10	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p1	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p3	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p4	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p5	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p6	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p7	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p8	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p9	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p10	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p1	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p3	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p4	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p5	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p6	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p7	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p8	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p9	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	p10	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	<derived17>	ALL	NULL	NULL	NULL	NULL	50328437500000	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	<derived14>	ALL	NULL	NULL	NULL	NULL	27680640625000000	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	<derived7>	ALL	NULL	NULL	NULL	NULL	7798774269472204288	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	<derived8>	ALL	NULL	NULL	NULL	NULL	7798774269472204288	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	<derived9>	ALL	NULL	NULL	NULL	NULL	-3222391729959551616	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	<derived10>	ALL	NULL	NULL	NULL	NULL	-3222391729959551616	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	<derived11>	ALL	NULL	NULL	NULL	NULL	-3222391729959551616	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	<derived12>	ALL	NULL	NULL	NULL	NULL	-3222391729959551616	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	<derived13>	ALL	NULL	NULL	NULL	NULL	-3222391729959551616	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	<derived15>	ALL	NULL	NULL	NULL	NULL	-3222391729959551616	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	<derived16>	ALL	NULL	NULL	NULL	NULL	-3222391729959551616	Using where; Using join buffer (incremental, BNL join)
+17	DERIVED	t2	system	NULL	NULL	NULL	NULL	1	
+17	DERIVED	p4	ALL	NULL	NULL	NULL	NULL	550	Using where
+17	DERIVED	p5	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (flat, BNL join)
+17	DERIVED	p6	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+17	DERIVED	p7	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+17	DERIVED	p8	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+16	DERIVED	t2	system	NULL	NULL	NULL	NULL	1	
+16	DERIVED	p4	ALL	NULL	NULL	NULL	NULL	550	Using where
+16	DERIVED	p5	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (flat, BNL join)
+16	DERIVED	p6	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+16	DERIVED	p7	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+16	DERIVED	p8	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+16	DERIVED	p9	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+16	DERIVED	p10	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+15	DERIVED	t2	system	NULL	NULL	NULL	NULL	1	
+15	DERIVED	p4	ALL	NULL	NULL	NULL	NULL	550	Using where
+15	DERIVED	p5	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (flat, BNL join)
+15	DERIVED	p6	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+15	DERIVED	p7	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+15	DERIVED	p8	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+15	DERIVED	p9	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+15	DERIVED	p10	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+14	DERIVED	t2	system	NULL	NULL	NULL	NULL	1	
+14	DERIVED	p4	ALL	NULL	NULL	NULL	NULL	550	Using where
+14	DERIVED	p5	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (flat, BNL join)
+14	DERIVED	p7	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+14	DERIVED	p8	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+14	DERIVED	p9	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+14	DERIVED	p10	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+13	DERIVED	t2	system	NULL	NULL	NULL	NULL	1	
+13	DERIVED	p4	ALL	NULL	NULL	NULL	NULL	550	Using where
+13	DERIVED	p5	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (flat, BNL join)
+13	DERIVED	p6	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+13	DERIVED	p7	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+13	DERIVED	p8	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+13	DERIVED	p9	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+13	DERIVED	p10	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+12	DERIVED	t2	system	NULL	NULL	NULL	NULL	1	
+12	DERIVED	p4	ALL	NULL	NULL	NULL	NULL	550	Using where
+12	DERIVED	p5	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (flat, BNL join)
+12	DERIVED	p6	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+12	DERIVED	p7	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+12	DERIVED	p8	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+12	DERIVED	p9	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+12	DERIVED	p10	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+11	DERIVED	t2	system	NULL	NULL	NULL	NULL	1	
+11	DERIVED	p4	ALL	NULL	NULL	NULL	NULL	550	Using where
+11	DERIVED	p5	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (flat, BNL join)
+11	DERIVED	p6	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+11	DERIVED	p7	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+11	DERIVED	p8	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+11	DERIVED	p9	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+11	DERIVED	p10	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+10	DERIVED	t2	system	NULL	NULL	NULL	NULL	1	
+10	DERIVED	p4	ALL	NULL	NULL	NULL	NULL	550	Using where
+10	DERIVED	p5	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (flat, BNL join)
+10	DERIVED	p6	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+10	DERIVED	p7	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+10	DERIVED	p8	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+10	DERIVED	p9	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+10	DERIVED	p10	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+9	DERIVED	t2	system	NULL	NULL	NULL	NULL	1	
+9	DERIVED	p4	ALL	NULL	NULL	NULL	NULL	550	Using where
+9	DERIVED	p5	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (flat, BNL join)
+9	DERIVED	p6	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+9	DERIVED	p7	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+9	DERIVED	p8	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+9	DERIVED	p9	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+9	DERIVED	p10	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+8	DERIVED	t2	system	NULL	NULL	NULL	NULL	1	
+8	DERIVED	p1	ALL	NULL	NULL	NULL	NULL	550	Using where
+8	DERIVED	p3	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (flat, BNL join)
+8	DERIVED	p4	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+8	DERIVED	p5	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+8	DERIVED	p6	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+8	DERIVED	p7	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+8	DERIVED	p8	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+8	DERIVED	p9	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+8	DERIVED	p10	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+7	DERIVED	t2	system	NULL	NULL	NULL	NULL	1	
+7	DERIVED	p1	ALL	NULL	NULL	NULL	NULL	550	Using where
+7	DERIVED	p3	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (flat, BNL join)
+7	DERIVED	p4	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+7	DERIVED	p5	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+7	DERIVED	p6	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+7	DERIVED	p7	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+7	DERIVED	p8	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+7	DERIVED	p9	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+7	DERIVED	p10	ALL	NULL	NULL	NULL	NULL	550	Using where; Using join buffer (incremental, BNL join)
+DROP TABLE  t1, t2;