diff options
| author | Marko Mäkelä <marko.makela@mariadb.com> | 2019-05-29 11:32:46 +0300 |
|---|---|---|
| committer | Marko Mäkelä <marko.makela@mariadb.com> | 2019-05-29 11:32:46 +0300 |
| commit | 90a91936852368774559a3ef2660e63e1e6f50e3 (patch) | |
| tree | 97004f253bea0db37606928b592ae3bc70fa6672 /mysql-test | |
| parent | fcb68ffe3dfb1c841852bd62a9aac9708888f4e9 (diff) | |
| parent | 6eefeb6fea05ff17d010d173ef244a1d92078d71 (diff) | |
| download | mariadb-git-90a91936852368774559a3ef2660e63e1e6f50e3.tar.gz | |
Merge 10.2 into 10.3
Diffstat (limited to 'mysql-test')
53 files changed, 1430 insertions, 143 deletions
diff --git a/mysql-test/main/delete_use_source.test b/mysql-test/main/delete_use_source.test index 93065c6fe23..9b7b1f8650c 100644 --- a/mysql-test/main/delete_use_source.test +++ b/mysql-test/main/delete_use_source.test @@ -1,5 +1,7 @@ --source include/have_sequence.inc --source include/have_innodb.inc +# This test is slow on buildbot. +--source include/big_test.inc create table t1(c1 integer not null,c2 integer not null, key (c1)) engine=InnoDb; create view v1 as select * from t1 where c1 in (0,1); diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result index 30831e75341..1bb48e47d8c 100644 --- a/mysql-test/main/derived_view.result +++ b/mysql-test/main/derived_view.result @@ -3019,3 +3019,404 @@ DROP VIEW v1,v2,v3; DROP TABLE t1; 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 PRIMARY t2 system NULL NULL NULL NULL 1 +1 PRIMARY t2 system NULL NULL NULL NULL 1 +1 PRIMARY t2 system NULL NULL NULL NULL 1 +1 PRIMARY t2 system NULL NULL NULL NULL 1 +1 PRIMARY t2 system NULL NULL NULL NULL 1 +1 PRIMARY t2 system NULL NULL NULL NULL 1 +1 PRIMARY p4 ALL NULL NULL NULL NULL 550 Using where +1 PRIMARY p5 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (flat, BNL join) +1 PRIMARY p6 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p7 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p8 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p9 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p10 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p4 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p5 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p6 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p7 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p8 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p9 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p10 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p1 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p3 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p4 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p5 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p6 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p7 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p8 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p9 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p10 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p1 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p3 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p4 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p5 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p6 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p7 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p8 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p9 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p10 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p1 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p3 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p4 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p5 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p6 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p7 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p8 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p9 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY p10 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived17> ALL NULL NULL NULL NULL 50328437500000 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived14> ALL NULL NULL NULL NULL 27680640625000000 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived7> ALL NULL NULL NULL NULL 7798774269472204800 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived8> ALL NULL NULL NULL NULL 7798774269472204800 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived9> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived10> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived11> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived12> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived13> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived15> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived16> ALL NULL NULL NULL NULL -3222391729959550976 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; 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; diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result index 23396d22876..256a6eda97e 100644 --- a/mysql-test/main/join_cache.result +++ b/mysql-test/main/join_cache.result @@ -2885,6 +2885,37 @@ Klaipeda Lithuania xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx Panevezys Lithuania xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx set join_cache_level=default; set join_buffer_size=default; +# +# MDEV-17752: Plan changes from hash_index_merge to index_merge with new optimizer defaults +# +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_use_stat_tables=@@use_stat_tables; +set optimizer_use_condition_selectivity=4; +set use_stat_tables='preferably'; +use world; +set join_cache_level=4; +CREATE INDEX City_Name ON City(Name); +ANALYZE TABLE City, Country; +EXPLAIN +SELECT Country.Name, Country.Population, City.Name, City.Population +FROM Country LEFT JOIN City +ON City.Country=Country.Code AND City.Population > 5000000 +WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE Country range Name Name 302 NULL 15 Using index condition; Using where; Rowid-ordered scan +1 SIMPLE City hash_range Population,Country #hash#Country:Population 3:4 world.Country.Code 25 Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join) +EXPLAIN +SELECT Country.Name, Country.Population, City.Name, City.Population +FROM Country LEFT JOIN City +ON City.Country=Country.Code AND +(City.Population > 5000000 OR City.Name LIKE 'Za%') +WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE Country range Name Name 302 NULL 15 Using index condition; Using where; Rowid-ordered scan +1 SIMPLE City hash_index_merge Population,Country,City_Name #hash#Country:Population,City_Name 3:4,35 world.Country.Code 96 Using sort_union(Population,City_Name); Using where; Using join buffer (flat, BNLH join) +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +set @@use_stat_tables=@save_use_stat_tables; +set join_cache_level=default; DROP DATABASE world; use test; CREATE TABLE t1( diff --git a/mysql-test/main/join_cache.test b/mysql-test/main/join_cache.test index df89fc30dee..ba5afafcf8b 100644 --- a/mysql-test/main/join_cache.test +++ b/mysql-test/main/join_cache.test @@ -967,6 +967,40 @@ SELECT City.Name, Country.Name, Country.PopulationBar FROM City,Country set join_cache_level=default; set join_buffer_size=default; + +--echo # +--echo # MDEV-17752: Plan changes from hash_index_merge to index_merge with new optimizer defaults +--echo # + +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_use_stat_tables=@@use_stat_tables; +set optimizer_use_condition_selectivity=4; +set use_stat_tables='preferably'; + +use world; +set join_cache_level=4; +CREATE INDEX City_Name ON City(Name); + +--disable_result_log +ANALYZE TABLE City, Country; +--enable_result_log + +EXPLAIN +SELECT Country.Name, Country.Population, City.Name, City.Population + FROM Country LEFT JOIN City + ON City.Country=Country.Code AND City.Population > 5000000 + WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; + +EXPLAIN +SELECT Country.Name, Country.Population, City.Name, City.Population + FROM Country LEFT JOIN City + ON City.Country=Country.Code AND + (City.Population > 5000000 OR City.Name LIKE 'Za%') + WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +set @@use_stat_tables=@save_use_stat_tables; +set join_cache_level=default; + DROP DATABASE world; use test; diff --git a/mysql-test/main/join_nested_jcl6.result b/mysql-test/main/join_nested_jcl6.result index 2f8e1712672..822bc064857 100644 --- a/mysql-test/main/join_nested_jcl6.result +++ b/mysql-test/main/join_nested_jcl6.result @@ -1342,7 +1342,7 @@ EXPLAIN SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON c21=c31) ON c11=c21; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 1 SIMPLE t2 hash_ALL NULL #hash#$hj 5 test.t1.c11 0 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE t3 ALL NULL NULL NULL NULL 0 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE t3 hash_ALL NULL #hash#$hj 5 test.t1.c11 0 Using where; Using join buffer (incremental, BNLH join) DROP TABLE t1,t2,t3; CREATE TABLE t1 (goods int(12) NOT NULL, price varchar(128) NOT NULL); INSERT INTO t1 VALUES (23, 2340), (26, 9900); diff --git a/mysql-test/main/join_outer.result b/mysql-test/main/join_outer.result index ecc5d603c73..040fa137fb9 100644 --- a/mysql-test/main/join_outer.result +++ b/mysql-test/main/join_outer.result @@ -2446,7 +2446,7 @@ t1.b1+'0' t2.b2 + '0' 0 0 1 1 DROP TABLE t1, t2; -set @join_cache_level= @save_join_cache_level; +set @@join_cache_level= @save_join_cache_level; # # MDEV-14779: using left join causes incorrect results with materialization and derived tables # @@ -2513,6 +2513,143 @@ v2 DROP TABLE t1,t2; # end of 5.5 tests # +# MDEV-19258: chained right joins all converted to inner joins +# +CREATE TABLE t1 ( +id int NOT NULL AUTO_INCREMENT, +timestamp bigint NOT NULL, +modifiedBy varchar(255) DEFAULT NULL, +PRIMARY KEY (id) +); +CREATE TABLE t2 ( +id int NOT NULL, +REV int NOT NULL, +REVTYPE tinyint DEFAULT NULL, +profile_id int DEFAULT NULL, +PRIMARY KEY (id,REV) +); +CREATE TABLE t3 ( +id int NOT NULL, +REV int NOT NULL, +person_id int DEFAULT NULL, +PRIMARY KEY (id,REV) +); +CREATE TABLE t4 ( +id int NOT NULL, +REV int NOT NULL, +PRIMARY KEY (id,REV) +); +INSERT INTO t1 VALUES +(1,1294391193890,'Cxqy$*9.kKeE'),(2,1294643906883,'rE4wqGV0gif@'), +(3,1294643927456,'L?3yt(%dY$Br'),(4,1294644343525,'WH&ObiZ$#2S4'), +(5,1294644616416,'YXnCbt?olUZ0'),(6,1294644954537,'8Npe4!(#lU@k'), +(7,1294645046659,'knc0GhXB1#ib'),(8,1294645183829,'w*oPpVfuS8^m'), +(9,1294645386701,'hwXR@3qVzrbU'),(10,1294645525982,'BeLW*Y9ndP0l'), +(11,1294645627723,'nTegib^)qZ$I'),(12,1294650860266,'u62C^Kzx3wH8'), +(13,1294657613745,'4&BkFjGa!qLg'),(14,1294660627161,')anpt312SCoh'), +(15,1294661023336,'LtJ2PX?*kTmx'),(16,1294662838066,'POGRr@?#ofpl'), +(17,1294663020989,'o.)1EOT2jnF7'),(18,1294663308065,'&TZ0F0LHE6.h'), +(19,1294664900039,'j)kSC%^In$9d'),(20,1294668904556,'97glN50)cAo.'), +(21,1294728056853,'lrKZxmw?I.Ek'),(22,1294728157174,'@P*SRg!pT.q?'), +(23,1294728327099,'W9gPrptF.)8n'),(24,1294728418481,'$q*c^sM&URd#'), +(25,1294728729620,'9*f4&bTPRtHo'),(26,1294728906014,')4VtTEnS7$oI'), +(27,1294732190003,'8dkNSPq2u3AQ'),(28,1294733205065,'SV2N6IoEf438'), +(29,1294741984927,'rBKj.0S^Ey%*'),(30,1294751748352,'j$2DvlBqk)Fw'), +(31,1294753902212,'C$N6OrEw8elz'),(32,1294758120598,'DCSVZw!rnxXq'), +(33,1294761769556,'OTS@QU8a6s5c'),(34,1294816845305,'IUE2stG0D3L5'), +(35,1294816966909,'Xd16yka.9nHe'),(36,1294817116302,'lOQHZpm%!8qb'), +(37,1294817374775,'^&pE3IhNf7ey'),(38,1294817538907,'oEn4#7C0Vhfp'), +(39,1294818482950,'bx54J*O0Va&?'),(40,1294819047024,'J%@a&1.qgdb?'), +(41,1294821826077,'C9kojr$L3Phz'),(42,1294825454458,'gG#BOnM80ZPi'), +(43,1294904129918,'F^!TrjM#zdvc'),(44,1294904254166,'Va&Tb)k0RvlM'), +(45,1294904414964,'dJjq0M6HvhR#'),(46,1294904505784,'nJmxg)ELqY(b'), +(47,1294904602835,'dhF#or$Vge!7'),(48,1294904684728,'?bIh5E3l!0em'), +(49,1294904877898,'Y*WflOdcxnk.'),(50,1294905002390,'*?H!lUgez5A.'), +(51,1294905096043,'wlEIY3n9uz!p'),(52,1294905404621,'T?qv3H6&hlQD'), +(53,1294905603922,'S@Bhys^Ti7bt'),(54,1294905788416,'KR?a5NVukz#l'), +(55,1294905993190,'A*&q4kWhED!o'),(56,1294906205254,'fT0%7z0DF6h*'), +(57,1294906319680,'LhzdW4?ivjR0'),(58,1294906424296,'h0KDlns%U*6T'), +(59,1294906623844,'b$CfB1noI6Ax'),(60,1294911258896,'#T1*LP!3$Oys'); +INSERT INTO t2 VALUES +(1,1,0,10209),(1,42480,1,10209),(1,61612,1,10209),(1,257545,1,10209), +(1,385332,1,10209),(1,1687999,1,10209),(3,1,0,10210),(3,617411,2,10210), +(4,11,0,14),(4,95149,1,10211),(4,607890,2,10211),(5,1,0,10212), +(6,1,0,10213),(6,93344,1,10213),(6,295578,1,10213),(6,295579,1,10213), +(6,295644,1,10213),(7,1,0,10214),(7,12,1,7),(7,688796,1,10214), +(7,1140433,1,10214),(7,1715227,1,10214),(8,1,0,10215),(8,74253,1,10215), +(8,93345,1,10215),(8,12,2,2),(9,1,0,10216),(9,93342,1,10216), +(9,122354,1,10216),(9,301499,2,10216),(10,11,0,5),(10,93343,1,10217), +(10,122355,1,10217),(10,123050,1,10217),(10,301500,2,10217),(11,1,0,10218), +(11,87852,1,10218),(11,605499,2,10218),(12,1,0,10219),(12,88024,1,10219), +(12,605892,2,10219),(13,1,0,10220); +INSERT INTO t3 VALUES +(1,1,300003),(1,117548,NULL),(2,1,300003),(2,117548,300006), +(3,1,300153),(3,117548,NULL),(4,1,300153),(4,117548,NULL), +(5,1,300153),(5,117548,NULL),(6,1,300182),(6,117548,NULL), +(7,1,300205),(7,117548,NULL),(8,1,300217),(8,117548,NULL), +(9,1,300290),(9,117548,NULL),(10,1,300290),(10,117548,NULL), +(11,1,300405),(11,117548,NULL),(12,1,300670),(12,117548,NULL), +(13,1,300670),(13,117548,NULL),(14,1,300006),(14,117548,NULL), +(15,1,300671),(15,117548,NULL),(16,1,300732),(16,117548,NULL); +INSERT INTO t4 VALUES +(300000,1),(300001,1),(300003,1),(300004,1), +(300005,1),(300005,688796),(300006,1),(300006,97697), +(300009,1),(300010,1),(300011,1),(300012,1),(300013,1), +(300014,1),(300015,1),(300016,1),(300017,1),(300018,1), +(300019,1),(300020,1),(300021,1),(300022,1),(300023,1), +(300024,1),(300025,1),(300026,1),(300027,1),(300028,1); +# This should have join order of t2,t3,t4,t1 +EXPLAIN EXTENDED SELECT * +FROM t1 INNER JOIN t2 ON t2.REV=t1.id +INNER JOIN t3 ON t3.id=t2.profile_id +INNER JOIN t4 ON t4.id=t3.person_id +WHERE t1.timestamp < 1294664900039 AND t1.timestamp > 1294644616416 AND +t2.REVTYPE=2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 42 100.00 Using where +1 SIMPLE t3 ref PRIMARY PRIMARY 4 test.t2.profile_id 1 100.00 Using where +1 SIMPLE t4 ref PRIMARY PRIMARY 4 test.t3.person_id 1 100.00 Using index +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.REV 1 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`timestamp` AS `timestamp`,`test`.`t1`.`modifiedBy` AS `modifiedBy`,`test`.`t2`.`id` AS `id`,`test`.`t2`.`REV` AS `REV`,`test`.`t2`.`REVTYPE` AS `REVTYPE`,`test`.`t2`.`profile_id` AS `profile_id`,`test`.`t3`.`id` AS `id`,`test`.`t3`.`REV` AS `REV`,`test`.`t3`.`person_id` AS `person_id`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`REV` AS `REV` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4` where `test`.`t2`.`REVTYPE` = 2 and `test`.`t4`.`id` = `test`.`t3`.`person_id` and `test`.`t3`.`id` = `test`.`t2`.`profile_id` and `test`.`t1`.`id` = `test`.`t2`.`REV` and `test`.`t1`.`timestamp` < 1294664900039 and `test`.`t1`.`timestamp` > 1294644616416 +SELECT * +FROM t1 INNER JOIN t2 ON t2.REV=t1.id +INNER JOIN t3 ON t3.id=t2.profile_id +INNER JOIN t4 ON t4.id=t3.person_id +WHERE t1.timestamp < 1294664900039 AND t1.timestamp > 1294644616416 AND +t2.REVTYPE=2; +id timestamp modifiedBy id REV REVTYPE profile_id id REV person_id id REV +12 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 1 300003 300003 1 +12 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 117548 300006 300006 1 +12 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 117548 300006 300006 97697 +# This should have join order of t2,t3,t4,t1 with the same plan as above +# because all RIGHT JOIN operations are converted into INNER JOIN +EXPLAIN EXTENDED SELECT * +FROM t1 RIGHT JOIN t2 ON t2.REV=t1.id +RIGHT JOIN t3 ON t3.id=t2.profile_id +RIGHT JOIN t4 ON t4.id=t3.person_id +WHERE t1.timestamp < 1294664900039 AND t1.timestamp > 1294644616416 +AND t2.REVTYPE=2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 42 100.00 Using where +1 SIMPLE t3 ref PRIMARY PRIMARY 4 test.t2.profile_id 1 100.00 Using where +1 SIMPLE t4 ref PRIMARY PRIMARY 4 test.t3.person_id 1 100.00 Using index +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.REV 1 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`timestamp` AS `timestamp`,`test`.`t1`.`modifiedBy` AS `modifiedBy`,`test`.`t2`.`id` AS `id`,`test`.`t2`.`REV` AS `REV`,`test`.`t2`.`REVTYPE` AS `REVTYPE`,`test`.`t2`.`profile_id` AS `profile_id`,`test`.`t3`.`id` AS `id`,`test`.`t3`.`REV` AS `REV`,`test`.`t3`.`person_id` AS `person_id`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`REV` AS `REV` from `test`.`t4` join `test`.`t3` join `test`.`t2` join `test`.`t1` where `test`.`t2`.`REVTYPE` = 2 and `test`.`t1`.`id` = `test`.`t2`.`REV` and `test`.`t3`.`id` = `test`.`t2`.`profile_id` and `test`.`t4`.`id` = `test`.`t3`.`person_id` and `test`.`t1`.`timestamp` < 1294664900039 and `test`.`t1`.`timestamp` > 1294644616416 +SELECT * +FROM t1 RIGHT JOIN t2 ON t2.REV=t1.id +RIGHT JOIN t3 ON t3.id=t2.profile_id +RIGHT JOIN t4 ON t4.id=t3.person_id +WHERE t1.timestamp < 1294664900039 AND t1.timestamp > 1294644616416 +AND t2.REVTYPE=2; +id timestamp modifiedBy id REV REVTYPE profile_id id REV person_id id REV +12 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 1 300003 300003 1 +12 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 117548 300006 300006 1 +12 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 117548 300006 300006 97697 +DROP TABLE t1,t2,t3,t4; +# end of 10.1 tests +# # MDEV-17518: Range optimization doesn't use ON expressions from nested outer joins # create table t1(a int); diff --git a/mysql-test/main/join_outer.test b/mysql-test/main/join_outer.test index d80e283215d..c9ac0224745 100644 --- a/mysql-test/main/join_outer.test +++ b/mysql-test/main/join_outer.test @@ -1977,7 +1977,7 @@ set @save_join_cache_level= @@join_cache_level; SET @@join_cache_level = 3; SELECT t1.b1+'0' , t2.b2 + '0' FROM t1 LEFT JOIN t2 ON b1 = b2; DROP TABLE t1, t2; -set @join_cache_level= @save_join_cache_level; +set @@join_cache_level= @save_join_cache_level; --echo # --echo # MDEV-14779: using left join causes incorrect results with materialization and derived tables @@ -2044,6 +2044,131 @@ DROP TABLE t1,t2; --echo # end of 5.5 tests --echo # +--echo # MDEV-19258: chained right joins all converted to inner joins +--echo # + + CREATE TABLE t1 ( + id int NOT NULL AUTO_INCREMENT, + timestamp bigint NOT NULL, + modifiedBy varchar(255) DEFAULT NULL, + PRIMARY KEY (id) +); + +CREATE TABLE t2 ( + id int NOT NULL, + REV int NOT NULL, + REVTYPE tinyint DEFAULT NULL, + profile_id int DEFAULT NULL, + PRIMARY KEY (id,REV) +); + +CREATE TABLE t3 ( + id int NOT NULL, + REV int NOT NULL, + person_id int DEFAULT NULL, + PRIMARY KEY (id,REV) +); + +CREATE TABLE t4 ( + id int NOT NULL, + REV int NOT NULL, + PRIMARY KEY (id,REV) +); + +INSERT INTO t1 VALUES +(1,1294391193890,'Cxqy$*9.kKeE'),(2,1294643906883,'rE4wqGV0gif@'), +(3,1294643927456,'L?3yt(%dY$Br'),(4,1294644343525,'WH&ObiZ$#2S4'), +(5,1294644616416,'YXnCbt?olUZ0'),(6,1294644954537,'8Npe4!(#lU@k'), +(7,1294645046659,'knc0GhXB1#ib'),(8,1294645183829,'w*oPpVfuS8^m'), +(9,1294645386701,'hwXR@3qVzrbU'),(10,1294645525982,'BeLW*Y9ndP0l'), +(11,1294645627723,'nTegib^)qZ$I'),(12,1294650860266,'u62C^Kzx3wH8'), +(13,1294657613745,'4&BkFjGa!qLg'),(14,1294660627161,')anpt312SCoh'), +(15,1294661023336,'LtJ2PX?*kTmx'),(16,1294662838066,'POGRr@?#ofpl'), +(17,1294663020989,'o.)1EOT2jnF7'),(18,1294663308065,'&TZ0F0LHE6.h'), +(19,1294664900039,'j)kSC%^In$9d'),(20,1294668904556,'97glN50)cAo.'), +(21,1294728056853,'lrKZxmw?I.Ek'),(22,1294728157174,'@P*SRg!pT.q?'), +(23,1294728327099,'W9gPrptF.)8n'),(24,1294728418481,'$q*c^sM&URd#'), +(25,1294728729620,'9*f4&bTPRtHo'),(26,1294728906014,')4VtTEnS7$oI'), +(27,1294732190003,'8dkNSPq2u3AQ'),(28,1294733205065,'SV2N6IoEf438'), +(29,1294741984927,'rBKj.0S^Ey%*'),(30,1294751748352,'j$2DvlBqk)Fw'), +(31,1294753902212,'C$N6OrEw8elz'),(32,1294758120598,'DCSVZw!rnxXq'), +(33,1294761769556,'OTS@QU8a6s5c'),(34,1294816845305,'IUE2stG0D3L5'), +(35,1294816966909,'Xd16yka.9nHe'),(36,1294817116302,'lOQHZpm%!8qb'), +(37,1294817374775,'^&pE3IhNf7ey'),(38,1294817538907,'oEn4#7C0Vhfp'), +(39,1294818482950,'bx54J*O0Va&?'),(40,1294819047024,'J%@a&1.qgdb?'), +(41,1294821826077,'C9kojr$L3Phz'),(42,1294825454458,'gG#BOnM80ZPi'), +(43,1294904129918,'F^!TrjM#zdvc'),(44,1294904254166,'Va&Tb)k0RvlM'), +(45,1294904414964,'dJjq0M6HvhR#'),(46,1294904505784,'nJmxg)ELqY(b'), +(47,1294904602835,'dhF#or$Vge!7'),(48,1294904684728,'?bIh5E3l!0em'), +(49,1294904877898,'Y*WflOdcxnk.'),(50,1294905002390,'*?H!lUgez5A.'), +(51,1294905096043,'wlEIY3n9uz!p'),(52,1294905404621,'T?qv3H6&hlQD'), +(53,1294905603922,'S@Bhys^Ti7bt'),(54,1294905788416,'KR?a5NVukz#l'), +(55,1294905993190,'A*&q4kWhED!o'),(56,1294906205254,'fT0%7z0DF6h*'), +(57,1294906319680,'LhzdW4?ivjR0'),(58,1294906424296,'h0KDlns%U*6T'), +(59,1294906623844,'b$CfB1noI6Ax'),(60,1294911258896,'#T1*LP!3$Oys'); + + +INSERT INTO t2 VALUES +(1,1,0,10209),(1,42480,1,10209),(1,61612,1,10209),(1,257545,1,10209), +(1,385332,1,10209),(1,1687999,1,10209),(3,1,0,10210),(3,617411,2,10210), +(4,11,0,14),(4,95149,1,10211),(4,607890,2,10211),(5,1,0,10212), +(6,1,0,10213),(6,93344,1,10213),(6,295578,1,10213),(6,295579,1,10213), +(6,295644,1,10213),(7,1,0,10214),(7,12,1,7),(7,688796,1,10214), +(7,1140433,1,10214),(7,1715227,1,10214),(8,1,0,10215),(8,74253,1,10215), +(8,93345,1,10215),(8,12,2,2),(9,1,0,10216),(9,93342,1,10216), +(9,122354,1,10216),(9,301499,2,10216),(10,11,0,5),(10,93343,1,10217), +(10,122355,1,10217),(10,123050,1,10217),(10,301500,2,10217),(11,1,0,10218), +(11,87852,1,10218),(11,605499,2,10218),(12,1,0,10219),(12,88024,1,10219), +(12,605892,2,10219),(13,1,0,10220); + +INSERT INTO t3 VALUES +(1,1,300003),(1,117548,NULL),(2,1,300003),(2,117548,300006), +(3,1,300153),(3,117548,NULL),(4,1,300153),(4,117548,NULL), +(5,1,300153),(5,117548,NULL),(6,1,300182),(6,117548,NULL), +(7,1,300205),(7,117548,NULL),(8,1,300217),(8,117548,NULL), +(9,1,300290),(9,117548,NULL),(10,1,300290),(10,117548,NULL), +(11,1,300405),(11,117548,NULL),(12,1,300670),(12,117548,NULL), +(13,1,300670),(13,117548,NULL),(14,1,300006),(14,117548,NULL), +(15,1,300671),(15,117548,NULL),(16,1,300732),(16,117548,NULL); + +INSERT INTO t4 VALUES +(300000,1),(300001,1),(300003,1),(300004,1), +(300005,1),(300005,688796),(300006,1),(300006,97697), +(300009,1),(300010,1),(300011,1),(300012,1),(300013,1), +(300014,1),(300015,1),(300016,1),(300017,1),(300018,1), +(300019,1),(300020,1),(300021,1),(300022,1),(300023,1), +(300024,1),(300025,1),(300026,1),(300027,1),(300028,1); + +let $q1= +SELECT * +FROM t1 INNER JOIN t2 ON t2.REV=t1.id + INNER JOIN t3 ON t3.id=t2.profile_id + INNER JOIN t4 ON t4.id=t3.person_id +WHERE t1.timestamp < 1294664900039 AND t1.timestamp > 1294644616416 AND + t2.REVTYPE=2; + +--echo # This should have join order of t2,t3,t4,t1 +eval EXPLAIN EXTENDED $q1; +eval $q1; + +let $q2= +SELECT * +FROM t1 RIGHT JOIN t2 ON t2.REV=t1.id + RIGHT JOIN t3 ON t3.id=t2.profile_id + RIGHT JOIN t4 ON t4.id=t3.person_id +WHERE t1.timestamp < 1294664900039 AND t1.timestamp > 1294644616416 + AND t2.REVTYPE=2; + +--echo # This should have join order of t2,t3,t4,t1 with the same plan as above +--echo # because all RIGHT JOIN operations are converted into INNER JOIN +eval EXPLAIN EXTENDED $q2; +eval $q2; + +DROP TABLE t1,t2,t3,t4; + +--echo # end of 10.1 tests + +--echo # --echo # MDEV-17518: Range optimization doesn't use ON expressions from nested outer joins --echo # create table t1(a int); diff --git a/mysql-test/main/join_outer_jcl6.result b/mysql-test/main/join_outer_jcl6.result index c6e08c2ec70..af4ab407f07 100644 --- a/mysql-test/main/join_outer_jcl6.result +++ b/mysql-test/main/join_outer_jcl6.result @@ -2457,7 +2457,7 @@ t1.b1+'0' t2.b2 + '0' 0 0 1 1 DROP TABLE t1, t2; -set @join_cache_level= @save_join_cache_level; +set @@join_cache_level= @save_join_cache_level; # # MDEV-14779: using left join causes incorrect results with materialization and derived tables # @@ -2524,6 +2524,143 @@ v2 DROP TABLE t1,t2; # end of 5.5 tests # +# MDEV-19258: chained right joins all converted to inner joins +# +CREATE TABLE t1 ( +id int NOT NULL AUTO_INCREMENT, +timestamp bigint NOT NULL, +modifiedBy varchar(255) DEFAULT NULL, +PRIMARY KEY (id) +); +CREATE TABLE t2 ( +id int NOT NULL, +REV int NOT NULL, +REVTYPE tinyint DEFAULT NULL, +profile_id int DEFAULT NULL, +PRIMARY KEY (id,REV) +); +CREATE TABLE t3 ( +id int NOT NULL, +REV int NOT NULL, +person_id int DEFAULT NULL, +PRIMARY KEY (id,REV) +); +CREATE TABLE t4 ( +id int NOT NULL, +REV int NOT NULL, +PRIMARY KEY (id,REV) +); +INSERT INTO t1 VALUES +(1,1294391193890,'Cxqy$*9.kKeE'),(2,1294643906883,'rE4wqGV0gif@'), +(3,1294643927456,'L?3yt(%dY$Br'),(4,1294644343525,'WH&ObiZ$#2S4'), +(5,1294644616416,'YXnCbt?olUZ0'),(6,1294644954537,'8Npe4!(#lU@k'), +(7,1294645046659,'knc0GhXB1#ib'),(8,1294645183829,'w*oPpVfuS8^m'), +(9,1294645386701,'hwXR@3qVzrbU'),(10,1294645525982,'BeLW*Y9ndP0l'), +(11,1294645627723,'nTegib^)qZ$I'),(12,1294650860266,'u62C^Kzx3wH8'), +(13,1294657613745,'4&BkFjGa!qLg'),(14,1294660627161,')anpt312SCoh'), +(15,1294661023336,'LtJ2PX?*kTmx'),(16,1294662838066,'POGRr@?#ofpl'), +(17,1294663020989,'o.)1EOT2jnF7'),(18,1294663308065,'&TZ0F0LHE6.h'), +(19,1294664900039,'j)kSC%^In$9d'),(20,1294668904556,'97glN50)cAo.'), +(21,1294728056853,'lrKZxmw?I.Ek'),(22,1294728157174,'@P*SRg!pT.q?'), +(23,1294728327099,'W9gPrptF.)8n'),(24,1294728418481,'$q*c^sM&URd#'), +(25,1294728729620,'9*f4&bTPRtHo'),(26,1294728906014,')4VtTEnS7$oI'), +(27,1294732190003,'8dkNSPq2u3AQ'),(28,1294733205065,'SV2N6IoEf438'), +(29,1294741984927,'rBKj.0S^Ey%*'),(30,1294751748352,'j$2DvlBqk)Fw'), +(31,1294753902212,'C$N6OrEw8elz'),(32,1294758120598,'DCSVZw!rnxXq'), +(33,1294761769556,'OTS@QU8a6s5c'),(34,1294816845305,'IUE2stG0D3L5'), +(35,1294816966909,'Xd16yka.9nHe'),(36,1294817116302,'lOQHZpm%!8qb'), +(37,1294817374775,'^&pE3IhNf7ey'),(38,1294817538907,'oEn4#7C0Vhfp'), +(39,1294818482950,'bx54J*O0Va&?'),(40,1294819047024,'J%@a&1.qgdb?'), +(41,1294821826077,'C9kojr$L3Phz'),(42,1294825454458,'gG#BOnM80ZPi'), +(43,1294904129918,'F^!TrjM#zdvc'),(44,1294904254166,'Va&Tb)k0RvlM'), +(45,1294904414964,'dJjq0M6HvhR#'),(46,1294904505784,'nJmxg)ELqY(b'), +(47,1294904602835,'dhF#or$Vge!7'),(48,1294904684728,'?bIh5E3l!0em'), +(49,1294904877898,'Y*WflOdcxnk.'),(50,1294905002390,'*?H!lUgez5A.'), +(51,1294905096043,'wlEIY3n9uz!p'),(52,1294905404621,'T?qv3H6&hlQD'), +(53,1294905603922,'S@Bhys^Ti7bt'),(54,1294905788416,'KR?a5NVukz#l'), +(55,1294905993190,'A*&q4kWhED!o'),(56,1294906205254,'fT0%7z0DF6h*'), +(57,1294906319680,'LhzdW4?ivjR0'),(58,1294906424296,'h0KDlns%U*6T'), +(59,1294906623844,'b$CfB1noI6Ax'),(60,1294911258896,'#T1*LP!3$Oys'); +INSERT INTO t2 VALUES +(1,1,0,10209),(1,42480,1,10209),(1,61612,1,10209),(1,257545,1,10209), +(1,385332,1,10209),(1,1687999,1,10209),(3,1,0,10210),(3,617411,2,10210), +(4,11,0,14),(4,95149,1,10211),(4,607890,2,10211),(5,1,0,10212), +(6,1,0,10213),(6,93344,1,10213),(6,295578,1,10213),(6,295579,1,10213), +(6,295644,1,10213),(7,1,0,10214),(7,12,1,7),(7,688796,1,10214), +(7,1140433,1,10214),(7,1715227,1,10214),(8,1,0,10215),(8,74253,1,10215), +(8,93345,1,10215),(8,12,2,2),(9,1,0,10216),(9,93342,1,10216), +(9,122354,1,10216),(9,301499,2,10216),(10,11,0,5),(10,93343,1,10217), +(10,122355,1,10217),(10,123050,1,10217),(10,301500,2,10217),(11,1,0,10218), +(11,87852,1,10218),(11,605499,2,10218),(12,1,0,10219),(12,88024,1,10219), +(12,605892,2,10219),(13,1,0,10220); +INSERT INTO t3 VALUES +(1,1,300003),(1,117548,NULL),(2,1,300003),(2,117548,300006), +(3,1,300153),(3,117548,NULL),(4,1,300153),(4,117548,NULL), +(5,1,300153),(5,117548,NULL),(6,1,300182),(6,117548,NULL), +(7,1,300205),(7,117548,NULL),(8,1,300217),(8,117548,NULL), +(9,1,300290),(9,117548,NULL),(10,1,300290),(10,117548,NULL), +(11,1,300405),(11,117548,NULL),(12,1,300670),(12,117548,NULL), +(13,1,300670),(13,117548,NULL),(14,1,300006),(14,117548,NULL), +(15,1,300671),(15,117548,NULL),(16,1,300732),(16,117548,NULL); +INSERT INTO t4 VALUES +(300000,1),(300001,1),(300003,1),(300004,1), +(300005,1),(300005,688796),(300006,1),(300006,97697), +(300009,1),(300010,1),(300011,1),(300012,1),(300013,1), +(300014,1),(300015,1),(300016,1),(300017,1),(300018,1), +(300019,1),(300020,1),(300021,1),(300022,1),(300023,1), +(300024,1),(300025,1),(300026,1),(300027,1),(300028,1); +# This should have join order of t2,t3,t4,t1 +EXPLAIN EXTENDED SELECT * +FROM t1 INNER JOIN t2 ON t2.REV=t1.id +INNER JOIN t3 ON t3.id=t2.profile_id +INNER JOIN t4 ON t4.id=t3.person_id +WHERE t1.timestamp < 1294664900039 AND t1.timestamp > 1294644616416 AND +t2.REVTYPE=2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 42 100.00 Using where +1 SIMPLE t3 ref PRIMARY PRIMARY 4 test.t2.profile_id 1 100.00 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t4 ref PRIMARY PRIMARY 4 test.t3.person_id 1 100.00 Using index +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.REV 1 100.00 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`timestamp` AS `timestamp`,`test`.`t1`.`modifiedBy` AS `modifiedBy`,`test`.`t2`.`id` AS `id`,`test`.`t2`.`REV` AS `REV`,`test`.`t2`.`REVTYPE` AS `REVTYPE`,`test`.`t2`.`profile_id` AS `profile_id`,`test`.`t3`.`id` AS `id`,`test`.`t3`.`REV` AS `REV`,`test`.`t3`.`person_id` AS `person_id`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`REV` AS `REV` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4` where `test`.`t2`.`REVTYPE` = 2 and `test`.`t4`.`id` = `test`.`t3`.`person_id` and `test`.`t3`.`id` = `test`.`t2`.`profile_id` and `test`.`t1`.`id` = `test`.`t2`.`REV` and `test`.`t1`.`timestamp` < 1294664900039 and `test`.`t1`.`timestamp` > 1294644616416 +SELECT * +FROM t1 INNER JOIN t2 ON t2.REV=t1.id +INNER JOIN t3 ON t3.id=t2.profile_id +INNER JOIN t4 ON t4.id=t3.person_id +WHERE t1.timestamp < 1294664900039 AND t1.timestamp > 1294644616416 AND +t2.REVTYPE=2; +id timestamp modifiedBy id REV REVTYPE profile_id id REV person_id id REV +12 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 1 300003 300003 1 +12 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 117548 300006 300006 1 +12 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 117548 300006 300006 97697 +# This should have join order of t2,t3,t4,t1 with the same plan as above +# because all RIGHT JOIN operations are converted into INNER JOIN +EXPLAIN EXTENDED SELECT * +FROM t1 RIGHT JOIN t2 ON t2.REV=t1.id +RIGHT JOIN t3 ON t3.id=t2.profile_id +RIGHT JOIN t4 ON t4.id=t3.person_id +WHERE t1.timestamp < 1294664900039 AND t1.timestamp > 1294644616416 +AND t2.REVTYPE=2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 42 100.00 Using where +1 SIMPLE t3 ref PRIMARY PRIMARY 4 test.t2.profile_id 1 100.00 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t4 ref PRIMARY PRIMARY 4 test.t3.person_id 1 100.00 Using index +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.REV 1 100.00 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`timestamp` AS `timestamp`,`test`.`t1`.`modifiedBy` AS `modifiedBy`,`test`.`t2`.`id` AS `id`,`test`.`t2`.`REV` AS `REV`,`test`.`t2`.`REVTYPE` AS `REVTYPE`,`test`.`t2`.`profile_id` AS `profile_id`,`test`.`t3`.`id` AS `id`,`test`.`t3`.`REV` AS `REV`,`test`.`t3`.`person_id` AS `person_id`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`REV` AS `REV` from `test`.`t4` join `test`.`t3` join `test`.`t2` join `test`.`t1` where `test`.`t2`.`REVTYPE` = 2 and `test`.`t1`.`id` = `test`.`t2`.`REV` and `test`.`t3`.`id` = `test`.`t2`.`profile_id` and `test`.`t4`.`id` = `test`.`t3`.`person_id` and `test`.`t1`.`timestamp` < 1294664900039 and `test`.`t1`.`timestamp` > 1294644616416 +SELECT * +FROM t1 RIGHT JOIN t2 ON t2.REV=t1.id +RIGHT JOIN t3 ON t3.id=t2.profile_id +RIGHT JOIN t4 ON t4.id=t3.person_id +WHERE t1.timestamp < 1294664900039 AND t1.timestamp > 1294644616416 +AND t2.REVTYPE=2; +id timestamp modifiedBy id REV REVTYPE profile_id id REV person_id id REV +12 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 1 300003 300003 1 +12 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 117548 300006 300006 1 +12 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 117548 300006 300006 97697 +DROP TABLE t1,t2,t3,t4; +# end of 10.1 tests +# # MDEV-17518: Range optimization doesn't use ON expressions from nested outer joins # create table t1(a int); @@ -2536,13 +2673,13 @@ insert into t3 select A.a + B.a* 10 + C.a * 100, 12345 from t1 A, t1 B, t1 C; explain select * from t1 left join t3 on t1.a=t3.b and t3.a<5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 10 -1 SIMPLE t3 range a a 5 NULL 5 Using where; Rowid-ordered scan; Using join buffer (flat, BNL join) +1 SIMPLE t3 hash_range a #hash#$hj:a 5:5 test.t1.a 5 Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join) # This must use range for table t3, too: explain select * from t1 left join (t3 join t2) on t1.a=t3.b and t3.a<5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 10 -1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) -1 SIMPLE t3 range a a 5 NULL 5 Using where; Rowid-ordered scan; Using join buffer (incremental, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 hash_range a #hash#$hj:a 5:5 test.t1.a 5 Using where; Rowid-ordered scan; Using join buffer (incremental, BNLH join) # # .. part 2: make sure condition selectivity can use the condition too. # @@ -2568,8 +2705,8 @@ Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` A explain extended select * from t1 left join (t3 join t2) on t1.a=t3.b and t3.a<5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 -1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t3 ALL NULL NULL NULL NULL 1000 0.99 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 hash_ALL NULL #hash#$hj 5 test.t1.a 1000 0.99 Using where; Using join buffer (incremental, BNLH join) Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t2`.`a` AS `a` from `test`.`t1` left join (`test`.`t3` join `test`.`t2`) on(`test`.`t3`.`b` = `test`.`t1`.`a` and `test`.`t3`.`a` < 5 and `test`.`t1`.`a` is not null) where 1 drop table t1,t2,t3; diff --git a/mysql-test/main/lowercase_table4.test b/mysql-test/main/lowercase_table4.test index 981c8a14564..24d2aa16f49 100644 --- a/mysql-test/main/lowercase_table4.test +++ b/mysql-test/main/lowercase_table4.test @@ -1,5 +1,7 @@ --source include/have_case_insensitive_file_system.inc --source include/have_innodb.inc +# This test is slow on buildbot. +--source include/big_test.inc --echo # --echo # Bug#46941 crash with lower_case_table_names=2 and diff --git a/mysql-test/main/mysql_client_test.test b/mysql-test/main/mysql_client_test.test index 2c6febd8ffa..f8aa097b415 100644 --- a/mysql-test/main/mysql_client_test.test +++ b/mysql-test/main/mysql_client_test.test @@ -2,6 +2,8 @@ -- source include/not_embedded.inc # need to have the dynamic loading turned on for the client plugin tests --source include/have_plugin_auth.inc +# This test is slow on buildbot. +--source include/big_test.inc SET @old_general_log= @@global.general_log; SET @old_slow_query_log= @@global.slow_query_log; diff --git a/mysql-test/main/mysql_client_test_nonblock.test b/mysql-test/main/mysql_client_test_nonblock.test index 31de14e4178..768a0590c30 100644 --- a/mysql-test/main/mysql_client_test_nonblock.test +++ b/mysql-test/main/mysql_client_test_nonblock.test @@ -2,6 +2,8 @@ # The non-blocking API is not supported in the embedded server. -- source include/not_embedded.inc +# This test is slow on buildbot. +--source include/big_test.inc SET @old_general_log= @@global.general_log; SET @old_slow_query_log= @@global.slow_query_log; diff --git a/mysql-test/main/mysqldump.test b/mysql-test/main/mysqldump.test index 8853002b6c0..2f1f5e38883 100644 --- a/mysql-test/main/mysqldump.test +++ b/mysql-test/main/mysqldump.test @@ -19,6 +19,9 @@ let collation=utf8_unicode_ci; # There are tables in 'mysql' database of type innodb --source include/have_innodb.inc +# This test is slow on buildbot. +--source include/big_test.inc + disable_query_log; call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); enable_query_log; diff --git a/mysql-test/main/selectivity_innodb.test b/mysql-test/main/selectivity_innodb.test index e2dba034363..f407eb052fe 100644 --- a/mysql-test/main/selectivity_innodb.test +++ b/mysql-test/main/selectivity_innodb.test @@ -1,4 +1,6 @@ --source include/have_innodb.inc +# This test is slow on buildbot. +--source include/big_test.inc SET SESSION STORAGE_ENGINE='InnoDB'; diff --git a/mysql-test/main/subselect_sj.result b/mysql-test/main/subselect_sj.result index 454a09771f6..13d5224566b 100644 --- a/mysql-test/main/subselect_sj.result +++ b/mysql-test/main/subselect_sj.result @@ -3193,6 +3193,7 @@ drop table t1,t2,t3; # # MDEV-18896: IN subquery in WHERE of a table-less query used for INSERT # +set @@optimizer_switch= @subselect_sj_tmp; create table t1 (a1 varchar(25)); create table t2 (a2 varchar(25)) ; insert into t1 select 'xxx' from dual where 'xxx' in (select a2 from t2); diff --git a/mysql-test/main/subselect_sj.test b/mysql-test/main/subselect_sj.test index e4e31691843..29211cadd9c 100644 --- a/mysql-test/main/subselect_sj.test +++ b/mysql-test/main/subselect_sj.test @@ -2878,6 +2878,8 @@ drop table t1,t2,t3; --echo # MDEV-18896: IN subquery in WHERE of a table-less query used for INSERT --echo # +set @@optimizer_switch= @subselect_sj_tmp; + create table t1 (a1 varchar(25)); create table t2 (a2 varchar(25)) ; insert into t1 select 'xxx' from dual where 'xxx' in (select a2 from t2); diff --git a/mysql-test/main/subselect_sj_jcl6.result b/mysql-test/main/subselect_sj_jcl6.result index fc279b05ac2..014c44d1181 100644 --- a/mysql-test/main/subselect_sj_jcl6.result +++ b/mysql-test/main/subselect_sj_jcl6.result @@ -3207,6 +3207,7 @@ drop table t1,t2,t3; # # MDEV-18896: IN subquery in WHERE of a table-less query used for INSERT # +set @@optimizer_switch= @subselect_sj_tmp; create table t1 (a1 varchar(25)); create table t2 (a2 varchar(25)) ; insert into t1 select 'xxx' from dual where 'xxx' in (select a2 from t2); diff --git a/mysql-test/main/trigger_null-8605.test b/mysql-test/main/trigger_null-8605.test index 34a57654b48..7645b61f5ad 100644 --- a/mysql-test/main/trigger_null-8605.test +++ b/mysql-test/main/trigger_null-8605.test @@ -149,6 +149,7 @@ EOF load data infile 'mdev8605.txt' into table t1 fields terminated by ','; select * from t1; drop table t1; +--remove_file $datadir/test/mdev8605.txt # timestamps (on NULL = NOW()) create table t1 (a timestamp, b int auto_increment primary key); @@ -218,6 +219,7 @@ load data infile 'sep8605.txt' into table t1 fields terminated by ',' (@a,a2,a3,b,c) set a1=100-@a; select 100-a1,a2,a3,b,c from t1; delete from t1; +--remove_file $datadir/test/sep8605.txt --write_file $datadir/test/fix8605.txt 00012010-11-12 01:02:030010000000000000000 @@ -231,6 +233,7 @@ set statement timestamp=777777777 for load data infile 'fix8605.txt' into table t1 fields terminated by ''; select * from t1; delete from t1; +--remove_file $datadir/test/fix8605.txt --write_file $datadir/test/xml8605.txt <data> @@ -300,6 +303,7 @@ set statement timestamp=777777777 for load xml infile 'xml8605.txt' into table t1 rows identified by '<row>'; select * from t1; drop table t1; +--remove_file $datadir/test/xml8605.txt # explicit DEFAULT create table t1 (a int not null default 5, b int, c int); diff --git a/mysql-test/suite/encryption/r/corrupted_during_recovery.result b/mysql-test/suite/encryption/r/corrupted_during_recovery.result index 41c0d7d75a8..356dce64f8d 100644 --- a/mysql-test/suite/encryption/r/corrupted_during_recovery.result +++ b/mysql-test/suite/encryption/r/corrupted_during_recovery.result @@ -9,14 +9,11 @@ INSERT INTO t2 VALUES(2); SELECT * FROM t1; ERROR 42000: Unknown storage engine 'InnoDB' SELECT * FROM t1; -a -1 -2 +ERROR 42S02: Table 'test.t1' doesn't exist in engine SELECT * FROM t2; a 2 -CHECK TABLE t1,t2; +CHECK TABLE t2; Table Op Msg_type Msg_text -test.t1 check status OK test.t2 check status OK DROP TABLE t1, t2; diff --git a/mysql-test/suite/encryption/r/innodb-force-corrupt.result b/mysql-test/suite/encryption/r/innodb-force-corrupt.result index c1145e574b7..dc88e4397f6 100644 --- a/mysql-test/suite/encryption/r/innodb-force-corrupt.result +++ b/mysql-test/suite/encryption/r/innodb-force-corrupt.result @@ -1,6 +1,7 @@ call mtr.add_suppression("InnoDB: Table `test`\\.`t[13]` (has an unreadable root page|is corrupted)"); call mtr.add_suppression("InnoDB: Encrypted page \\[page id: space=\\d+, page number=[36]\\] in file .*test.t[123]\\.ibd looks corrupted; key_version=3221342974"); -call mtr.add_suppression("InnoDB: Table `test`\\.`t[13]` is corrupted"); +call mtr.add_suppression("\\[ERROR\\] InnoDB: We detected index corruption in an InnoDB type table"); +call mtr.add_suppression("\\[ERROR\\] mysqld: Index for table 't2' is corrupt; try to repair it"); SET GLOBAL innodb_file_per_table = ON; set global innodb_compression_algorithm = 1; # Create and populate tables to be corrupted @@ -18,7 +19,7 @@ COMMIT; SELECT * FROM t1; ERROR 42S02: Table 'test.t1' doesn't exist in engine SELECT * FROM t2; -ERROR HY000: Got error 192 'Table encrypted but decryption failed. This could be because correct encryption management plugin is not loaded, used encryption key is not available or encryption method does not match.' from InnoDB +Got one of the listed errors SELECT * FROM t3; ERROR 42S02: Table 'test.t3' doesn't exist in engine # Restore the original tables diff --git a/mysql-test/suite/encryption/t/corrupted_during_recovery.test b/mysql-test/suite/encryption/t/corrupted_during_recovery.test index 5784d5775c6..94fcd5f382d 100644 --- a/mysql-test/suite/encryption/t/corrupted_during_recovery.test +++ b/mysql-test/suite/encryption/t/corrupted_during_recovery.test @@ -50,11 +50,16 @@ EOF --source include/start_mysqld.inc --error ER_UNKNOWN_STORAGE_ENGINE SELECT * FROM t1; -let $restart_parameters=--innodb_force_recovery=1; +--disable_query_log +call mtr.add_suppression("InnoDB: Encrypted page \\[page id: space=[1-9][0-9]*, page number=3\\] in file .*test.t[1].ibd looks corrupted; key_version=1786080875"); +call mtr.add_suppression("InnoDB: Table `test`\\.`t1` is corrupted. Please drop the table and recreate."); +--enable_query_log +let $restart_parameters=--innodb_force_recovery=1 --skip-innodb-buffer-pool-load-at-startup; --source include/restart_mysqld.inc +--error ER_NO_SUCH_TABLE_IN_ENGINE SELECT * FROM t1; SELECT * FROM t2; -CHECK TABLE t1,t2; +CHECK TABLE t2; DROP TABLE t1, t2; diff --git a/mysql-test/suite/encryption/t/innodb-force-corrupt.test b/mysql-test/suite/encryption/t/innodb-force-corrupt.test index ae7e5c81aa1..7fdf294f466 100644 --- a/mysql-test/suite/encryption/t/innodb-force-corrupt.test +++ b/mysql-test/suite/encryption/t/innodb-force-corrupt.test @@ -9,7 +9,8 @@ call mtr.add_suppression("InnoDB: Table `test`\\.`t[13]` (has an unreadable root page|is corrupted)"); call mtr.add_suppression("InnoDB: Encrypted page \\[page id: space=\\d+, page number=[36]\\] in file .*test.t[123]\\.ibd looks corrupted; key_version=3221342974"); -call mtr.add_suppression("InnoDB: Table `test`\\.`t[13]` is corrupted"); +call mtr.add_suppression("\\[ERROR\\] InnoDB: We detected index corruption in an InnoDB type table"); +call mtr.add_suppression("\\[ERROR\\] mysqld: Index for table 't2' is corrupt; try to repair it"); SET GLOBAL innodb_file_per_table = ON; set global innodb_compression_algorithm = 1; @@ -69,7 +70,7 @@ EOF --error ER_NO_SUCH_TABLE_IN_ENGINE SELECT * FROM t1; ---error ER_GET_ERRMSG +--error ER_GET_ERRMSG,ER_NOT_KEYFILE SELECT * FROM t2; --error ER_NO_SUCH_TABLE_IN_ENGINE SELECT * FROM t3; diff --git a/mysql-test/suite/galera/disabled.def b/mysql-test/suite/galera/disabled.def index d744e3cb811..5908ed2121b 100644 --- a/mysql-test/suite/galera/disabled.def +++ b/mysql-test/suite/galera/disabled.def @@ -15,7 +15,6 @@ MW-328A : MDEV-17847 Galera test failure on MW-328[A|B|C] MW-328B : MDEV-17847 Galera test failure on MW-328[A|B|C] MW-328C : MDEV-17847 Galera test failure on MW-328[A|B|C] MW-329 : wsrep_local_replays not stable -MW-336 : MDEV-13549 Timeout in wait_condition.inc for PROCESSLIST MW-416 : MDEV-13549 Galera test failures MW-44 : MDEV-15809 Test failure on galera.MW-44 galera_account_management : MariaDB 10.0 does not support ALTER USER @@ -26,7 +25,6 @@ galera_as_slave_replication_bundle : MDEV-15785 OPTION_GTID_BEGIN is set in Gtid galera_binlog_rows_query_log_events: MariaDB does not support binlog_rows_query_log_events galera_flush : MariaDB does not have global.thread_statistics galera_gcache_recover_manytrx : MDEV-18834 Galera test failure -galera_gcs_fc_limit : MDEV-17061 Timeout in wait_condition.inc for PROCESSLIST galera_ist_mariabackup : MDEV-18829 test leaves port open galera_ist_progress: MDEV-15236 fails when trying to read transfer status galera_kill_applier : race condition at the start of the test diff --git a/mysql-test/suite/galera/r/MW-336.result b/mysql-test/suite/galera/r/MW-336.result index 4d7d6440066..191811680e0 100644 --- a/mysql-test/suite/galera/r/MW-336.result +++ b/mysql-test/suite/galera/r/MW-336.result @@ -5,11 +5,13 @@ SET GLOBAL wsrep_slave_threads = 10; SET GLOBAL wsrep_slave_threads = 1; # Wait 10 slave threads to start 1 connection node_2; -# Generate 12 replication events +SET SESSION wsrep_sync_wait=15; +# Generate 100 replication events connection node_1; +SET SESSION wsrep_sync_wait=15; SELECT COUNT(*) FROM t1; COUNT(*) -13 +101 # Wait 9 slave threads to exit 1 SET GLOBAL wsrep_slave_threads = 10; # Wait 10 slave threads to start 2 @@ -17,23 +19,21 @@ SET GLOBAL wsrep_slave_threads = 20; # Wait 20 slave threads to start 3 SET GLOBAL wsrep_slave_threads = 1; connection node_2; -# Generate 40 replication events +# Generate 100 replication events connection node_1; SELECT COUNT(*) FROM t1; COUNT(*) -53 +201 # Wait 10 slave threads to exit 3 SET GLOBAL wsrep_slave_threads = 10; -SET GLOBAL wsrep_slave_threads = 0; -Warnings: -Warning 1292 Truncated incorrect wsrep_slave_threads value: '0' +SET GLOBAL wsrep_slave_threads = 1; # Wait 10 slave threads to start 3 connection node_2; -# Generate 12 replication events +# Generate 100 replication events connection node_1; SELECT COUNT(*) FROM t1; COUNT(*) -65 +301 # Wait 10 slave threads to exit 4 connection node_1; DROP TABLE t1; diff --git a/mysql-test/suite/galera/r/galera_gcs_fc_limit.result b/mysql-test/suite/galera/r/galera_gcs_fc_limit.result index 464a8b7ea97..bf1a82f9f90 100644 --- a/mysql-test/suite/galera/r/galera_gcs_fc_limit.result +++ b/mysql-test/suite/galera/r/galera_gcs_fc_limit.result @@ -1,23 +1,26 @@ -CREATE TABLE t1 (f1 INTEGER) ENGINE=InnoDB; -INSERT INTO t1 VALUES (1); +CREATE TABLE t1 (f1 INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,B INTEGER) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,1); connection node_2; -SELECT COUNT(*) = 1 FROM t1; -COUNT(*) = 1 +SET SESSION wsrep_sync_wait=15; +SELECT COUNT(*) FROM t1; +COUNT(*) 1 SET GLOBAL wsrep_provider_options = 'gcs.fc_limit=1'; LOCK TABLE t1 WRITE; connection node_1; -INSERT INTO t1 VALUES (2); -INSERT INTO t1 VALUES (3); -INSERT INTO t1 VALUES (4); -INSERT INTO t1 VALUES (5); +FLUSH STATUS; +INSERT INTO t1 VALUES (2,2); +INSERT INTO t1 VALUES (3,3); +INSERT INTO t1 VALUES (4,4); +INSERT INTO t1(B) SELECT B FROM t1; connection node_1a; +# In node_1 either insert or commit should be stuck connection node_2; UNLOCK TABLES; connection node_1; -INSERT INTO t1 VALUES (6); +INSERT INTO t1 VALUES (NULL,6); connection node_2; -SELECT COUNT(*) = 6 FROM t1; -COUNT(*) = 6 -1 +SELECT COUNT(*) FROM t1; +COUNT(*) +9 DROP TABLE t1; diff --git a/mysql-test/suite/galera/t/MW-336.test b/mysql-test/suite/galera/t/MW-336.test index 40d093a1a86..195418cff55 100644 --- a/mysql-test/suite/galera/t/MW-336.test +++ b/mysql-test/suite/galera/t/MW-336.test @@ -19,13 +19,14 @@ SET GLOBAL wsrep_slave_threads = 1; --source include/wait_condition.inc --connection node_2 +SET SESSION wsrep_sync_wait=15; # Wait until inserts are replicated --let $wait_condition = SELECT COUNT(*) = 1 FROM t1; --source include/wait_condition.inc ---echo # Generate 12 replication events +--echo # Generate 100 replication events --disable_query_log --disable_result_log ---let $count = 12 +--let $count = 100 while ($count) { INSERT INTO t1 VALUES (1); @@ -35,9 +36,7 @@ while ($count) --enable_query_log --connection node_1 -# Wait until inserts are replicated ---let $wait_condition = SELECT COUNT(*) = 13 FROM t1; ---source include/wait_condition.inc +SET SESSION wsrep_sync_wait=15; SELECT COUNT(*) FROM t1; @@ -61,10 +60,10 @@ SET GLOBAL wsrep_slave_threads = 20; SET GLOBAL wsrep_slave_threads = 1; --connection node_2 ---echo # Generate 40 replication events +--echo # Generate 100 replication events --disable_query_log --disable_result_log ---let $count = 40 +--let $count = 100 while ($count) { INSERT INTO t1 VALUES (1); @@ -74,11 +73,6 @@ while ($count) --enable_result_log --connection node_1 - -# Wait until inserts are replicated ---let $wait_condition = SELECT COUNT(*) = 53 FROM t1; ---source include/wait_condition.inc - SELECT COUNT(*) FROM t1; --echo # Wait 10 slave threads to exit 3 @@ -87,7 +81,7 @@ SELECT COUNT(*) FROM t1; --source include/wait_condition.inc SET GLOBAL wsrep_slave_threads = 10; -SET GLOBAL wsrep_slave_threads = 0; +SET GLOBAL wsrep_slave_threads = 1; --echo # Wait 10 slave threads to start 3 --let $wait_timeout=600 @@ -95,10 +89,10 @@ SET GLOBAL wsrep_slave_threads = 0; --source include/wait_condition.inc --connection node_2 ---echo # Generate 12 replication events +--echo # Generate 100 replication events --disable_query_log --disable_result_log ---let $count = 12 +--let $count = 100 while ($count) { INSERT INTO t1 VALUES (1); @@ -108,10 +102,6 @@ while ($count) --enable_query_log --connection node_1 -# Wait until inserts are replicated ---let $wait_condition = SELECT COUNT(*) = 65 FROM t1; ---source include/wait_condition.inc - SELECT COUNT(*) FROM t1; --echo # Wait 10 slave threads to exit 4 diff --git a/mysql-test/suite/galera/t/galera_gcs_fc_limit.test b/mysql-test/suite/galera/t/galera_gcs_fc_limit.test index 5d341533161..e0626bd8946 100644 --- a/mysql-test/suite/galera/t/galera_gcs_fc_limit.test +++ b/mysql-test/suite/galera/t/galera_gcs_fc_limit.test @@ -5,12 +5,12 @@ --source include/galera_cluster.inc --source include/have_innodb.inc -CREATE TABLE t1 (f1 INTEGER) ENGINE=InnoDB; -INSERT INTO t1 VALUES (1); +CREATE TABLE t1 (f1 INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,B INTEGER) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,1); --connection node_2 -SELECT COUNT(*) = 1 FROM t1; ---sleep 1 +SET SESSION wsrep_sync_wait=15; +SELECT COUNT(*) FROM t1; --let $wsrep_provider_options_orig = `SELECT @@wsrep_provider_options` SET GLOBAL wsrep_provider_options = 'gcs.fc_limit=1'; @@ -19,24 +19,22 @@ SET GLOBAL wsrep_provider_options = 'gcs.fc_limit=1'; LOCK TABLE t1 WRITE; --connection node_1 ---sleep 1 -INSERT INTO t1 VALUES (2); ---sleep 2 -INSERT INTO t1 VALUES (3); ---sleep 2 -INSERT INTO t1 VALUES (4); ---sleep 2 +FLUSH STATUS; +INSERT INTO t1 VALUES (2,2); +INSERT INTO t1 VALUES (3,3); +INSERT INTO t1 VALUES (4,4); # This query will hang because flow control will kick in --send -INSERT INTO t1 VALUES (5); ---sleep 2 +INSERT INTO t1(B) SELECT B FROM t1; --let $galera_connection_name = node_1a --let $galera_server_number = 1 --source include/galera_connect.inc --connection node_1a ---let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE STATE = 'Commit' AND INFO = 'INSERT INTO t1 VALUES (5)'; + +--echo # In node_1 either insert or commit should be stuck +--let $wait_condition = SELECT VARIABLE_VALUE > 0 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_flow_control_paused'; --source include/wait_condition.inc --connection node_2 @@ -46,11 +44,11 @@ UNLOCK TABLES; --connection node_1 --reap -INSERT INTO t1 VALUES (6); +INSERT INTO t1 VALUES (NULL,6); --connection node_2 # Replication catches up and continues normally -SELECT COUNT(*) = 6 FROM t1; +SELECT COUNT(*) FROM t1; --disable_query_log --eval SET GLOBAL wsrep_provider_options = '$wsrep_provider_options_orig'; diff --git a/mysql-test/suite/galera_3nodes/r/galera_evs_suspect_timeout.result b/mysql-test/suite/galera_3nodes/r/galera_evs_suspect_timeout.result index 6a7eea94077..34ef5b00033 100644 --- a/mysql-test/suite/galera_3nodes/r/galera_evs_suspect_timeout.result +++ b/mysql-test/suite/galera_3nodes/r/galera_evs_suspect_timeout.result @@ -1,26 +1,35 @@ connection node_1; +connection node_2; +connection node_3; +connection node_1; SET GLOBAL wsrep_provider_options = 'evs.inactive_timeout=PT100M; evs.suspect_timeout=PT1S'; connection node_2; SET GLOBAL wsrep_provider_options = 'evs.inactive_timeout=PT100M; evs.suspect_timeout=PT1S'; connection node_3; Suspending node ... connection node_1; -SET SESSION wsrep_sync_wait = 0; +SET SESSION wsrep_sync_wait=0; SELECT VARIABLE_VALUE = 2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size'; VARIABLE_VALUE = 2 1 -CREATE TABLE t1 (f1 INTEGER); +CREATE TABLE t1 (f1 INTEGER) engine=InnoDB; INSERT INTO t1 VALUES (1); connection node_2; -SET SESSION wsrep_sync_wait = 0; +SET SESSION wsrep_sync_wait=0; SELECT VARIABLE_VALUE = 2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size'; VARIABLE_VALUE = 2 1 -SET SESSION wsrep_sync_wait = DEFAULT; -SELECT COUNT(*) = 1 FROM t1; -COUNT(*) = 1 +SET SESSION wsrep_sync_wait = 15; +SELECT COUNT(*) FROM t1; +COUNT(*) 1 -DROP TABLE t1; Resuming node ... connection node_3; -CALL mtr.add_suppression("WSREP: gcs_caused() returned -1 \\(Operation not permitted\\)"); +CALL mtr.add_suppression("WSREP: gcs_caused.*"); +CALL mtr.add_suppression("WSREP: gcs/src/gcs_core.cpp:core_handle_uuid_msg.*"); +SET SESSION wsrep_sync_wait = 15; +SELECT COUNT(*) FROM t1; +COUNT(*) +1 +connection node_1; +DROP TABLE t1; diff --git a/mysql-test/suite/galera_3nodes/t/galera_evs_suspect_timeout.test b/mysql-test/suite/galera_3nodes/t/galera_evs_suspect_timeout.test index 4dab936c343..136fc8fd8a0 100644 --- a/mysql-test/suite/galera_3nodes/t/galera_evs_suspect_timeout.test +++ b/mysql-test/suite/galera_3nodes/t/galera_evs_suspect_timeout.test @@ -7,24 +7,28 @@ --source include/galera_cluster.inc --source include/have_innodb.inc +--let $galera_connection_name = node_3 +--let $galera_server_number = 3 +--source include/galera_connect.inc +--let $wsrep_cluster_address_node3 = `SELECT @@wsrep_cluster_address` + +# Save original auto_increment_offset values. +--let $node_1=node_1 +--let $node_2=node_2 +--let $node_3=node_3 +--source ../galera/include/auto_increment_offset_save.inc + --connection node_1 --let $wsrep_provider_options_node1 = `SELECT @@wsrep_provider_options` SET GLOBAL wsrep_provider_options = 'evs.inactive_timeout=PT100M; evs.suspect_timeout=PT1S'; --connection node_2 ---source include/wait_until_connected_again.inc --let $wsrep_provider_options_node2 = `SELECT @@wsrep_provider_options` SET GLOBAL wsrep_provider_options = 'evs.inactive_timeout=PT100M; evs.suspect_timeout=PT1S'; ---let $galera_connection_name = node_3 ---let $galera_server_number = 3 ---source include/galera_connect.inc ---connection node_3 ---source include/wait_until_connected_again.inc ---let $wsrep_cluster_address_node3 = `SELECT @@wsrep_cluster_address` - # Suspend node #3 +--connection node_3 --source include/galera_suspend.inc --sleep 5 @@ -32,27 +36,27 @@ SET GLOBAL wsrep_provider_options = 'evs.inactive_timeout=PT100M; evs.suspect_ti --connection node_1 --source include/wait_until_connected_again.inc -SET SESSION wsrep_sync_wait = 0; +SET SESSION wsrep_sync_wait=0; SELECT VARIABLE_VALUE = 2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size'; --disable_query_log --eval SET GLOBAL wsrep_provider_options = '$wsrep_provider_options_node1'; --enable_query_log - --source include/wait_until_connected_again.inc -CREATE TABLE t1 (f1 INTEGER); + +CREATE TABLE t1 (f1 INTEGER) engine=InnoDB; INSERT INTO t1 VALUES (1); --connection node_2 -SET SESSION wsrep_sync_wait = 0; +--source include/wait_until_connected_again.inc +SET SESSION wsrep_sync_wait=0; SELECT VARIABLE_VALUE = 2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size'; --disable_query_log --eval SET GLOBAL wsrep_provider_options = '$wsrep_provider_options_node2'; --enable_query_log - --source include/wait_until_connected_again.inc -SET SESSION wsrep_sync_wait = DEFAULT; -SELECT COUNT(*) = 1 FROM t1; -DROP TABLE t1; + +SET SESSION wsrep_sync_wait = 15; +SELECT COUNT(*) FROM t1; # Reconnect node #3 so that MTR's end-of-test checks can run @@ -60,9 +64,19 @@ DROP TABLE t1; --connection node_3 --source include/wait_until_connected_again.inc -CALL mtr.add_suppression("WSREP: gcs_caused() returned -1 \\(Operation not permitted\\)"); +CALL mtr.add_suppression("WSREP: gcs_caused.*"); +CALL mtr.add_suppression("WSREP: gcs/src/gcs_core.cpp:core_handle_uuid_msg.*"); --disable_query_log --eval SET GLOBAL wsrep_cluster_address = '$wsrep_cluster_address_node3'; --enable_query_log --source include/wait_until_connected_again.inc + +SET SESSION wsrep_sync_wait = 15; +SELECT COUNT(*) FROM t1; + +--connection node_1 +DROP TABLE t1; +# Restore original auto_increment_offset values. +--source ../galera/include/auto_increment_offset_restore.inc + diff --git a/mysql-test/suite/innodb/include/innodb-page-compression.inc b/mysql-test/suite/innodb/include/innodb-page-compression.inc index 0e7ae8cf029..fec0f0cf01c 100644 --- a/mysql-test/suite/innodb/include/innodb-page-compression.inc +++ b/mysql-test/suite/innodb/include/innodb-page-compression.inc @@ -1,3 +1,5 @@ +# This test is slow on buildbot. +--source include/big_test.inc create table innodb_normal (c1 int not null auto_increment primary key, b char(200)) engine=innodb; create table innodb_page_compressed1 (c1 int not null auto_increment primary key, b char(200)) engine=innodb page_compressed=1 page_compression_level=1; create table innodb_page_compressed2 (c1 int not null auto_increment primary key, b char(200)) engine=innodb page_compressed=1 page_compression_level=2; diff --git a/mysql-test/suite/innodb/r/corrupted_during_recovery.result b/mysql-test/suite/innodb/r/corrupted_during_recovery.result index 788f17e3284..ee4db08fc85 100644 --- a/mysql-test/suite/innodb/r/corrupted_during_recovery.result +++ b/mysql-test/suite/innodb/r/corrupted_during_recovery.result @@ -9,14 +9,11 @@ INSERT INTO t2 VALUES(1); SELECT * FROM t1; ERROR 42000: Unknown storage engine 'InnoDB' SELECT * FROM t1; -a -0 -2 +ERROR 42S02: Table 'test.t1' doesn't exist in engine SELECT * FROM t2; a 1 -CHECK TABLE t1,t2; +CHECK TABLE t2; Table Op Msg_type Msg_text -test.t1 check status OK test.t2 check status OK DROP TABLE t1, t2; diff --git a/mysql-test/suite/innodb/r/innodb_force_recovery.result b/mysql-test/suite/innodb/r/innodb_force_recovery.result index 0220f68a626..8e700f9489d 100644 --- a/mysql-test/suite/innodb/r/innodb_force_recovery.result +++ b/mysql-test/suite/innodb/r/innodb_force_recovery.result @@ -28,8 +28,10 @@ ERROR HY000: Error on rename of './test/t1' to './test/t3' (errno: 165 "Table is truncate table t1; ERROR HY000: Table 't1' is read only drop table t1; +ERROR HY000: Table 't1' is read only show tables; Tables_in_test +t1 t2 # Restart the server with innodb_force_recovery as 5. select * from t2; @@ -45,18 +47,21 @@ drop index idx on t2; ERROR HY000: Can't create table `test`.`t2` (errno: 165 "Table is read only") update t2 set f1=3 where f2=2; ERROR HY000: Running in read-only mode -create table t1(f1 int not null)engine=innodb; -ERROR HY000: Can't create table `test`.`t1` (errno: 165 "Table is read only") -drop table t1; -ERROR 42S02: Unknown table 'test.t1' +create table t3(f1 int not null)engine=innodb; +ERROR HY000: Can't create table `test`.`t3` (errno: 165 "Table is read only") +drop table t3; +ERROR 42S02: Unknown table 'test.t3' rename table t2 to t3; ERROR HY000: Error on rename of './test/t2' to './test/t3' (errno: 165 "Table is read only") truncate table t2; ERROR HY000: Table 't2' is read only drop table t2; ERROR HY000: Table 't2' is read only +create schema db; +drop schema db; show tables; Tables_in_test +t1 t2 # Restart the server with innodb_force_recovery as 6. select * from t2; @@ -72,10 +77,10 @@ drop index idx on t2; ERROR HY000: Table 't2' is read only update t2 set f1=3 where f2=2; ERROR HY000: Table 't2' is read only -create table t1(f1 int not null)engine=innodb; -ERROR HY000: Can't create table `test`.`t1` (errno: 165 "Table is read only") +create table t3(f1 int not null)engine=innodb; +ERROR HY000: Can't create table `test`.`t3` (errno: 165 "Table is read only") drop table t1; -ERROR 42S02: Unknown table 'test.t1' +ERROR HY000: Table 't1' is read only rename table t2 to t3; ERROR HY000: Error on rename of './test/t2' to './test/t3' (errno: 165 "Table is read only") truncate table t2; @@ -84,6 +89,7 @@ drop table t2; ERROR HY000: Table 't2' is read only show tables; Tables_in_test +t1 t2 # Restart the server with innodb_force_recovery=2 select * from t2; @@ -92,10 +98,9 @@ f1 f2 begin; update t2 set f2=3; connect con1,localhost,root,,; -create table t3(a int)engine=innodb; # Force a redo log flush of the above uncommitted UPDATE SET GLOBAL innodb_flush_log_at_trx_commit=1; -drop table t3; +drop table t1; disconnect con1; connection default; # Kill the server diff --git a/mysql-test/suite/innodb/r/leaf_page_corrupted_during_recovery.result b/mysql-test/suite/innodb/r/leaf_page_corrupted_during_recovery.result new file mode 100644 index 00000000000..37ddb0a9348 --- /dev/null +++ b/mysql-test/suite/innodb/r/leaf_page_corrupted_during_recovery.result @@ -0,0 +1,20 @@ +CREATE TABLE t1 (pk INT PRIMARY KEY, c CHAR(255))ENGINE=InnoDB STATS_PERSISTENT=0; +SET GLOBAL INNODB_LIMIT_OPTIMISTIC_INSERT_DEBUG = 2; +INSERT INTO t1 VALUES(1, 'sql'), (2, 'server'), (3, 'mariadb'), +(4, 'mariadb'), (5, 'test1'), (6, 'test2'), (7, 'test3'), +(8, 'test4'), (9, 'test5'), (10, 'test6'), (11, 'test7'), +(12, 'test8'); +SELECT COUNT(*) FROM t1; +COUNT(*) +12 +UPDATE t1 SET c='best8' WHERE pk=12; +# Kill the server +# Corrupt the pages +SELECT * FROM t1 WHERE PK = 1; +ERROR 42000: Unknown storage engine 'InnoDB' +SELECT * FROM t1 WHERE PK = 1; +pk c +1 sql +SELECT * FROM t1 WHERE pk = 12; +ERROR HY000: Index for table 't1' is corrupt; try to repair it +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/corrupted_during_recovery.opt b/mysql-test/suite/innodb/t/corrupted_during_recovery.opt index 6051f4cd1fa..ba4fa744d8f 100644 --- a/mysql-test/suite/innodb/t/corrupted_during_recovery.opt +++ b/mysql-test/suite/innodb/t/corrupted_during_recovery.opt @@ -1 +1,2 @@ ---innodb_doublewrite=0 +--skip-innodb-doublewrite +--skip-innodb-buffer-pool-load-at-startup diff --git a/mysql-test/suite/innodb/t/corrupted_during_recovery.test b/mysql-test/suite/innodb/t/corrupted_during_recovery.test index fbfb1bbe5d5..dad08645085 100644 --- a/mysql-test/suite/innodb/t/corrupted_during_recovery.test +++ b/mysql-test/suite/innodb/t/corrupted_during_recovery.test @@ -6,6 +6,8 @@ call mtr.add_suppression("Plugin 'InnoDB' init function returned error"); call mtr.add_suppression("Plugin 'InnoDB' registration as a STORAGE ENGINE failed"); call mtr.add_suppression("InnoDB: Database page corruption on disk or a failed file read of tablespace test/t1 page"); call mtr.add_suppression("InnoDB: Failed to read file '.*test.t1\\.ibd' at offset 3: Page read from tablespace is corrupted."); +call mtr.add_suppression("InnoDB: Background Page read failed to read or decrypt \\[page id: space=\\d+, page number=3\\]"); +call mtr.add_suppression("InnoDB: Table `test`.`t1` is corrupted. Please drop the table and recreate."); --enable_query_log let INNODB_PAGE_SIZE=`select @@innodb_page_size`; @@ -53,8 +55,10 @@ EOF SELECT * FROM t1; let $restart_parameters=--innodb_force_recovery=1; --source include/restart_mysqld.inc + +--error ER_NO_SUCH_TABLE_IN_ENGINE SELECT * FROM t1; SELECT * FROM t2; -CHECK TABLE t1,t2; +CHECK TABLE t2; DROP TABLE t1, t2; diff --git a/mysql-test/suite/innodb/t/doublewrite.test b/mysql-test/suite/innodb/t/doublewrite.test index 448f934558c..7c8c1180adb 100644 --- a/mysql-test/suite/innodb/t/doublewrite.test +++ b/mysql-test/suite/innodb/t/doublewrite.test @@ -7,6 +7,8 @@ --source include/innodb_page_size.inc --source include/have_debug.inc --source include/not_embedded.inc +# This test is slow on buildbot. +--source include/big_test.inc # Slow shutdown and restart to make sure ibuf merge is finished SET GLOBAL innodb_fast_shutdown = 0; diff --git a/mysql-test/suite/innodb/t/innodb-alter-table.test b/mysql-test/suite/innodb/t/innodb-alter-table.test index 0cf456ad146..ab0bd7b3353 100644 --- a/mysql-test/suite/innodb/t/innodb-alter-table.test +++ b/mysql-test/suite/innodb/t/innodb-alter-table.test @@ -1,5 +1,7 @@ --source include/innodb_page_size.inc --source include/have_partition.inc +# This test is slow on buildbot. +--source include/big_test.inc # # MMDEV-8386: MariaDB creates very big tmp file and hangs on xtradb diff --git a/mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test b/mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test index 6b52e386525..d8cc21c550c 100644 --- a/mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test +++ b/mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test @@ -9,6 +9,8 @@ --source include/not_embedded.inc # DBUG_SUICIDE() hangs under valgrind --source include/not_valgrind.inc +# This test is slow on buildbot. +--source include/big_test.inc CREATE TABLE t1( a INT AUTO_INCREMENT PRIMARY KEY, diff --git a/mysql-test/suite/innodb/t/innodb-index-debug.test b/mysql-test/suite/innodb/t/innodb-index-debug.test index e680fb719cc..2988a89c651 100644 --- a/mysql-test/suite/innodb/t/innodb-index-debug.test +++ b/mysql-test/suite/innodb/t/innodb-index-debug.test @@ -2,6 +2,8 @@ -- source include/have_innodb.inc -- source include/count_sessions.inc -- source include/have_debug_sync.inc +# This test is slow on buildbot. +--source include/big_test.inc let $MYSQLD_DATADIR= `select @@datadir`; diff --git a/mysql-test/suite/innodb/t/innodb-page-cleaners.test b/mysql-test/suite/innodb/t/innodb-page-cleaners.test index 7ed87ae1734..6f8accec30f 100644 --- a/mysql-test/suite/innodb/t/innodb-page-cleaners.test +++ b/mysql-test/suite/innodb/t/innodb-page-cleaners.test @@ -1,4 +1,6 @@ --source include/have_innodb.inc +# This test is slow on buildbot. +--source include/big_test.inc SET @saved_page_cleaners = @@GLOBAL.innodb_page_cleaners; diff --git a/mysql-test/suite/innodb/t/innodb-page_compression_zip.test b/mysql-test/suite/innodb/t/innodb-page_compression_zip.test index 45e62722972..ad78767e903 100644 --- a/mysql-test/suite/innodb/t/innodb-page_compression_zip.test +++ b/mysql-test/suite/innodb/t/innodb-page_compression_zip.test @@ -1,5 +1,7 @@ --source include/have_innodb.inc --source include/not_embedded.inc +# This test is slow on buildbot. +--source include/big_test.inc # zlib set global innodb_compression_algorithm = 1; diff --git a/mysql-test/suite/innodb/t/innodb_force_recovery.test b/mysql-test/suite/innodb/t/innodb_force_recovery.test index 926719e193c..00b888c2768 100644 --- a/mysql-test/suite/innodb/t/innodb_force_recovery.test +++ b/mysql-test/suite/innodb/t/innodb_force_recovery.test @@ -52,6 +52,7 @@ rename table t1 to t3; --error ER_OPEN_AS_READONLY truncate table t1; +--error ER_OPEN_AS_READONLY drop table t1; show tables; @@ -77,10 +78,10 @@ drop index idx on t2; update t2 set f1=3 where f2=2; --error ER_CANT_CREATE_TABLE -create table t1(f1 int not null)engine=innodb; +create table t3(f1 int not null)engine=innodb; --error ER_BAD_TABLE_ERROR -drop table t1; +drop table t3; --error ER_ERROR_ON_RENAME rename table t2 to t3; @@ -90,6 +91,9 @@ truncate table t2; --error ER_OPEN_AS_READONLY drop table t2; + +create schema db; +drop schema db; show tables; --echo # Restart the server with innodb_force_recovery as 6. @@ -114,9 +118,9 @@ drop index idx on t2; update t2 set f1=3 where f2=2; --error ER_CANT_CREATE_TABLE -create table t1(f1 int not null)engine=innodb; +create table t3(f1 int not null)engine=innodb; ---error ER_BAD_TABLE_ERROR +--error ER_OPEN_AS_READONLY drop table t1; --error ER_ERROR_ON_RENAME @@ -138,10 +142,9 @@ begin; update t2 set f2=3; connect (con1,localhost,root,,); -create table t3(a int)engine=innodb; --echo # Force a redo log flush of the above uncommitted UPDATE SET GLOBAL innodb_flush_log_at_trx_commit=1; -drop table t3; +drop table t1; disconnect con1; connection default; diff --git a/mysql-test/suite/innodb/t/leaf_page_corrupted_during_recovery.opt b/mysql-test/suite/innodb/t/leaf_page_corrupted_during_recovery.opt new file mode 100644 index 00000000000..ba4fa744d8f --- /dev/null +++ b/mysql-test/suite/innodb/t/leaf_page_corrupted_during_recovery.opt @@ -0,0 +1,2 @@ +--skip-innodb-doublewrite +--skip-innodb-buffer-pool-load-at-startup diff --git a/mysql-test/suite/innodb/t/leaf_page_corrupted_during_recovery.test b/mysql-test/suite/innodb/t/leaf_page_corrupted_during_recovery.test new file mode 100644 index 00000000000..ef84664dd6d --- /dev/null +++ b/mysql-test/suite/innodb/t/leaf_page_corrupted_during_recovery.test @@ -0,0 +1,58 @@ +--source include/have_innodb.inc +--source include/have_debug.inc + +--disable_query_log +call mtr.add_suppression("InnoDB: Database page corruption on disk or a failed file read of tablespace test/t1 page "); +call mtr.add_suppression("InnoDB: Background Page read failed to read or decrypt \\[page id: space=\\d+, page number=19\\]"); +call mtr.add_suppression("\\[ERROR\\] InnoDB: Failed to read file '.*test.t1\\.ibd' at offset 19: Page read from tablespace is corrupted\\."); +call mtr.add_suppression("\\[ERROR\\] InnoDB: Plugin initialization aborted at srv0start\\.cc.* with error Data structure corruption"); +call mtr.add_suppression("\\[ERROR\\] Plugin 'InnoDB' (init function|registration)"); +call mtr.add_suppression("\\[ERROR\\] InnoDB: We detected index corruption"); +call mtr.add_suppression("\\[ERROR\\] mysqld: Index for table 't1' is corrupt; try to repair it"); +--enable_query_log +CREATE TABLE t1 (pk INT PRIMARY KEY, c CHAR(255))ENGINE=InnoDB STATS_PERSISTENT=0; + +SET GLOBAL INNODB_LIMIT_OPTIMISTIC_INSERT_DEBUG = 2; + +INSERT INTO t1 VALUES(1, 'sql'), (2, 'server'), (3, 'mariadb'), + (4, 'mariadb'), (5, 'test1'), (6, 'test2'), (7, 'test3'), + (8, 'test4'), (9, 'test5'), (10, 'test6'), (11, 'test7'), + (12, 'test8'); + +--source include/restart_mysqld.inc + +let INNODB_PAGE_SIZE=`select @@innodb_page_size`; +let MYSQLD_DATADIR=`select @@datadir`; + +SELECT COUNT(*) FROM t1; +--source ../include/no_checkpoint_start.inc +UPDATE t1 SET c='best8' WHERE pk=12; + +--let CLEANUP_IF_CHECKPOINT=DROP TABLE t1; +--source ../include/no_checkpoint_end.inc +--echo # Corrupt the pages + +perl; +my $file = "$ENV{MYSQLD_DATADIR}/test/t1.ibd"; +open(FILE, "+<$file") || die "Unable to open $file"; +binmode FILE; +seek (FILE, $ENV{INNODB_PAGE_SIZE} * 19, SEEK_SET) or die "seek"; +print FILE "junk"; +close FILE or die "close"; +EOF + +--source include/start_mysqld.inc +--error ER_UNKNOWN_STORAGE_ENGINE +SELECT * FROM t1 WHERE PK = 1; + +let $restart_parameters=--innodb-force-recovery=1; +# Work around MDEV-19435 to avoid crash in row_purge_reset_trx_id() +let $restart_parameters=--innodb-force-recovery=2; +--source include/restart_mysqld.inc +SELECT * FROM t1 WHERE PK = 1; +--error ER_NOT_KEYFILE +SELECT * FROM t1 WHERE pk = 12; + +DROP TABLE t1; +let $restart_parameters=; +--source include/restart_mysqld.inc diff --git a/mysql-test/suite/innodb/t/log_file_size.test b/mysql-test/suite/innodb/t/log_file_size.test index 1e6c7cd4d00..6f15b830e8d 100644 --- a/mysql-test/suite/innodb/t/log_file_size.test +++ b/mysql-test/suite/innodb/t/log_file_size.test @@ -4,6 +4,8 @@ --source include/not_embedded.inc # DBUG_EXECUTE_IF is needed --source include/have_debug.inc +# This test is slow on buildbot. +--source include/big_test.inc if (`SELECT @@innodb_log_file_size = 1048576`) { --skip Test requires innodb_log_file_size>1M. diff --git a/mysql-test/suite/innodb/t/mdev-15707.test b/mysql-test/suite/innodb/t/mdev-15707.test index 46e1ee7ce57..a0446ef4f81 100644 --- a/mysql-test/suite/innodb/t/mdev-15707.test +++ b/mysql-test/suite/innodb/t/mdev-15707.test @@ -1,4 +1,6 @@ --source include/windows.inc +# This test is slow on buildbot. +--source include/big_test.inc # Deadlock in conjunction with the innodb change buffering. diff --git a/mysql-test/suite/innodb/t/table_definition_cache_debug.test b/mysql-test/suite/innodb/t/table_definition_cache_debug.test index 1f3598d68c9..70467b53435 100644 --- a/mysql-test/suite/innodb/t/table_definition_cache_debug.test +++ b/mysql-test/suite/innodb/t/table_definition_cache_debug.test @@ -1,6 +1,8 @@ --source include/have_innodb.inc --source include/have_debug.inc --source include/have_debug_sync.inc +# This test is slow on buildbot. +--source include/big_test.inc call mtr.add_suppression("InnoDB: innodb_open_files=13 is exceeded"); diff --git a/mysql-test/suite/innodb/t/update_time.test b/mysql-test/suite/innodb/t/update_time.test index 1a00e3b9f73..a95c5171e9b 100644 --- a/mysql-test/suite/innodb/t/update_time.test +++ b/mysql-test/suite/innodb/t/update_time.test @@ -8,6 +8,8 @@ -- source include/have_innodb_max_16k.inc # restart does not work with embedded -- source include/not_embedded.inc +# This test is slow on buildbot. +--source include/big_test.inc CREATE TABLE t (a INT) ENGINE=INNODB; diff --git a/mysql-test/suite/mariabackup/mdev-14447.test b/mysql-test/suite/mariabackup/mdev-14447.test index 96d12368547..799d81a8710 100644 --- a/mysql-test/suite/mariabackup/mdev-14447.test +++ b/mysql-test/suite/mariabackup/mdev-14447.test @@ -1,4 +1,6 @@ --source include/have_debug.inc +# This test is slow on buildbot. +--source include/big_test.inc call mtr.add_suppression("InnoDB: New log files created"); let $basedir=$MYSQLTEST_VARDIR/tmp/backup; diff --git a/mysql-test/suite/plugins/r/server_audit.result b/mysql-test/suite/plugins/r/server_audit.result index b8d2986feea..4088b36d440 100644 --- a/mysql-test/suite/plugins/r/server_audit.result +++ b/mysql-test/suite/plugins/r/server_audit.result @@ -271,6 +271,7 @@ TIME,HOSTNAME,root,localhost,ID,0,CONNECT,mysql,,0 TIME,HOSTNAME,root,localhost,ID,0,DISCONNECT,mysql,,0 TIME,HOSTNAME,no_such_user,localhost,ID,0,FAILED_CONNECT,,,ID TIME,HOSTNAME,no_such_user,localhost,ID,0,DISCONNECT,,,0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'set global server_audit_incl_users=\'odin, dva, tri\'',0 TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'set global server_audit_incl_users=\'odin, root, dva, tri\'',0 TIME,HOSTNAME,root,localhost,ID,ID,CREATE,test,t2, TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'create table t2 (id int)',0 @@ -381,6 +382,7 @@ TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'CREATE USER u3 IDENTIFIED BY *** TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'drop user u1, u2, u3',0 TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'insert into t1 values (1), (2)',0 TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global server_audit_logging= off',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global server_audit_logging= on',0 TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global server_audit_events=\'\'',0 TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global serv',0 TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'select (1), (2)',0 diff --git a/mysql-test/suite/plugins/r/thread_pool_server_audit.result b/mysql-test/suite/plugins/r/thread_pool_server_audit.result index cf09ccb3a51..250450e6451 100644 --- a/mysql-test/suite/plugins/r/thread_pool_server_audit.result +++ b/mysql-test/suite/plugins/r/thread_pool_server_audit.result @@ -244,6 +244,7 @@ TIME,HOSTNAME,root,localhost,ID,0,CONNECT,mysql,,0 TIME,HOSTNAME,root,localhost,ID,0,DISCONNECT,mysql,,0 TIME,HOSTNAME,no_such_user,localhost,ID,0,FAILED_CONNECT,,,ID TIME,HOSTNAME,no_such_user,localhost,ID,0,DISCONNECT,,,0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'set global server_audit_incl_users=\'odin, dva, tri\'',0 TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'set global server_audit_incl_users=\'odin, root, dva, tri\'',0 TIME,HOSTNAME,root,localhost,ID,ID,CREATE,test,t2, TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'create table t2 (id int)',0 diff --git a/mysql-test/suite/rpl/r/rpl_semi_sync_skip_repl.result b/mysql-test/suite/rpl/r/rpl_semi_sync_skip_repl.result index 811715d1439..ec9607148d4 100644 --- a/mysql-test/suite/rpl/r/rpl_semi_sync_skip_repl.result +++ b/mysql-test/suite/rpl/r/rpl_semi_sync_skip_repl.result @@ -7,24 +7,27 @@ SET @@GLOBAL.rpl_semi_sync_master_timeout=100; connection slave; include/stop_slave.inc SET @@GLOBAL.replicate_events_marked_for_skip=FILTER_ON_MASTER; -SET @@GLOBAL. rpl_semi_sync_slave_enabled = 1; +SET @@GLOBAL.rpl_semi_sync_slave_enabled = 1; include/start_slave.inc connection master; CREATE TABLE t1 (a INT) ENGINE=innodb; -SET @@GLOBAL.debug_dbug= "d,dbug_master_binlog_over_2GB"; +SET @@GLOBAL.debug_dbug="d,dbug_master_binlog_over_2GB"; SET @@SESSION.skip_replication=1; INSERT INTO t1 SET a=1; SET @@SESSION.skip_replication=0; INSERT INTO t1 SET a=0; +SET @@GLOBAL.debug_dbug=""; +FLUSH LOGS; connection slave; connection master; -SET @@GLOBAL.debug_dbug=""; -SET @@GLOBAL. rpl_semi_sync_master_timeout = 10000; -SET @@GLOBAL. rpl_semi_sync_master_enabled = 0; +SET @@GLOBAL.debug_dbug=@@GLOBAL.debug_dbug; +SET @@GLOBAL.rpl_semi_sync_master_timeout = 10000; +SET @@GLOBAL.rpl_semi_sync_master_enabled = 0; connection master; DROP TABLE t1; connection slave; include/stop_slave.inc -SET @@GLOBAL. rpl_semi_sync_slave_enabled = 0; +SET @@GLOBAL.rpl_semi_sync_slave_enabled = 0; SET @@GLOBAL.replicate_events_marked_for_skip = REPLICATE; +include/start_slave.inc include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_semi_sync_skip_repl.test b/mysql-test/suite/rpl/t/rpl_semi_sync_skip_repl.test index f5c423741a6..8dd30b8a161 100644 --- a/mysql-test/suite/rpl/t/rpl_semi_sync_skip_repl.test +++ b/mysql-test/suite/rpl/t/rpl_semi_sync_skip_repl.test @@ -15,29 +15,34 @@ source include/master-slave.inc; # Suppress warnings that might be generated during the test call mtr.add_suppression("Timeout waiting for reply of binlog"); ---let $sav_enabled_master=`SELECT @@GLOBAL.rpl_semi_sync_master_enabled ` ---let $sav_timeout_master=`SELECT @@GLOBAL.rpl_semi_sync_master_timeout ` +--let $sav_enabled_master=`SELECT @@GLOBAL.rpl_semi_sync_master_enabled` +--let $sav_timeout_master=`SELECT @@GLOBAL.rpl_semi_sync_master_timeout` SET @@GLOBAL.rpl_semi_sync_master_enabled = 1; SET @@GLOBAL.rpl_semi_sync_master_timeout=100; --connection slave +--let $sav_skip_marked_slave=`SELECT @@GLOBAL.replicate_events_marked_for_skip` +--let $sav_enabled_slave=`SELECT @@GLOBAL.rpl_semi_sync_slave_enabled` source include/stop_slave.inc; ---let $sav_skip_marked_slave=`SELECT @@GLOBAL.replicate_events_marked_for_skip ` SET @@GLOBAL.replicate_events_marked_for_skip=FILTER_ON_MASTER; ---let $sav_enabled_slave=`SELECT @@GLOBAL.rpl_semi_sync_slave_enabled ` -SET @@GLOBAL. rpl_semi_sync_slave_enabled = 1; - +SET @@GLOBAL.rpl_semi_sync_slave_enabled = 1; source include/start_slave.inc; --connection master CREATE TABLE t1 (a INT) ENGINE=innodb; # Make the following events as if they offset over 2GB from the beginning of binlog -SET @@GLOBAL.debug_dbug= "d,dbug_master_binlog_over_2GB"; +--let $sav_debug_dbug=@@GLOBAL.debug_dbug +SET @@GLOBAL.debug_dbug="d,dbug_master_binlog_over_2GB"; SET @@SESSION.skip_replication=1; INSERT INTO t1 SET a=1; SET @@SESSION.skip_replication=0; INSERT INTO t1 SET a=0; +SET @@GLOBAL.debug_dbug=""; + +# The current binlog is inconsistent so let's rotate it away +# to clean up simulation results. +FLUSH LOGS; --sync_slave_with_master @@ -45,17 +50,17 @@ INSERT INTO t1 SET a=0; # Clean up # --connection master -SET @@GLOBAL.debug_dbug=""; ---eval SET @@GLOBAL. rpl_semi_sync_master_timeout = $sav_timeout_master ---eval SET @@GLOBAL. rpl_semi_sync_master_enabled = $sav_enabled_master +--eval SET @@GLOBAL.debug_dbug=$sav_debug_dbug +--eval SET @@GLOBAL.rpl_semi_sync_master_timeout = $sav_timeout_master +--eval SET @@GLOBAL.rpl_semi_sync_master_enabled = $sav_enabled_master --connection master DROP TABLE t1; --sync_slave_with_master source include/stop_slave.inc; ---eval SET @@GLOBAL. rpl_semi_sync_slave_enabled = $sav_enabled_slave +--eval SET @@GLOBAL.rpl_semi_sync_slave_enabled = $sav_enabled_slave --eval SET @@GLOBAL.replicate_events_marked_for_skip = $sav_skip_marked_slave ---let $rpl_only_running_threads= 1 +source include/start_slave.inc; --source include/rpl_end.inc |