summaryrefslogtreecommitdiff
path: root/mysql-test/main/derived_view.test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main/derived_view.test')
-rw-r--r--mysql-test/main/derived_view.test260
1 files changed, 260 insertions, 0 deletions
diff --git a/mysql-test/main/derived_view.test b/mysql-test/main/derived_view.test
index 68f334e1ac4..2b89d3e4be6 100644
--- a/mysql-test/main/derived_view.test
+++ b/mysql-test/main/derived_view.test
@@ -1976,3 +1976,263 @@ DROP TABLE t1;
# The following command must be the last one the file
set optimizer_switch=@exit_optimizer_switch;
set join_cache_level=@exit_join_cache_level;
+
+--echo #
+--echo # Bug mdev-12812: EXPLAIN for query with many expensive derived
+--echo #
+
+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 ;
+
+DROP TABLE t1, t2;
View Lorry Controller Status