revision-id: de5f7348bddc1b885a6554ce38cd8017386f4106 (mariadb-10.4.7-49-gde5f7348bdd) parent(s): b1e377997e987b66c999713bdb7e6cfdb87797ae author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-09-02 14:22:19 +0300 message: Make main.subselect_sj2* tests stable Use EITS statistics to avoid changing query plans --- mysql-test/main/subselect_sj2.result | 32 +++++++++++++++++++++++-------- mysql-test/main/subselect_sj2.test | 3 +++ mysql-test/main/subselect_sj2_jcl6.result | 32 +++++++++++++++++++++++-------- mysql-test/main/subselect_sj2_mat.result | 32 +++++++++++++++++++++++-------- 4 files changed, 75 insertions(+), 24 deletions(-) diff --git a/mysql-test/main/subselect_sj2.result b/mysql-test/main/subselect_sj2.result index 31453801220..bab21da8243 100644 --- a/mysql-test/main/subselect_sj2.result +++ b/mysql-test/main/subselect_sj2.result @@ -813,6 +813,14 @@ CREATE TABLE t2 (f14 int(11) NOT NULL, f12 varchar(1) NOT NULL, KEY (f12,f14)) E INSERT INTO t2 VALUES (6,'y'); CREATE TABLE t3 (f12 varchar(1) NOT NULL) ENGINE=InnoDB; INSERT INTO t3 VALUES ('r'),('s'),('t'),('v'),('w'),('x'),('y'); +analyze table t1,t2,t3 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status OK # The following must use LooseScan but not join buffering explain SELECT * FROM t3 @@ -1102,6 +1110,14 @@ INSERT INTO t2 VALUES (9,'d','d'),(10,'s','s'),(11,'r','r'),(12,'m','m'), (13,'b','b'),(14,'x','x'),(15,'g','g'),(16,'p','p'), (17,'q','q'),(18,'w','w'),(19,'d','d'); +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +analyze table t2 persistent for all; +Table Op Msg_type Msg_text +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK EXPLAIN SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 WHERE alias3.d IN ( @@ -1110,11 +1126,11 @@ WHERE alias5.b = alias4.b AND ( alias5.b >= alias3.b OR alias5.c != alias3.c ) ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL # -1 PRIMARY alias5 index PRIMARY c 4 NULL # Using where; Using index -1 PRIMARY alias4 eq_ref PRIMARY,c PRIMARY 4 test.alias5.b # Using where; FirstMatch(alias3) -1 PRIMARY alias1 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join) +1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL # Using where +1 PRIMARY alias4 ref PRIMARY,c c 4 test.alias3.d # Using index +1 PRIMARY alias5 eq_ref PRIMARY PRIMARY 4 test.alias4.b # Using where; FirstMatch(alias3) 1 PRIMARY alias2 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join) +1 PRIMARY alias1 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join) SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 WHERE alias3.d IN ( SELECT alias4.c FROM t2 AS alias4, t2 AS alias5 @@ -1131,11 +1147,11 @@ WHERE alias5.b = alias4.b AND ( alias5.b >= alias3.b OR alias3.c != alias5.c ) ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL # -1 PRIMARY alias5 index PRIMARY c 4 NULL # Using where; Using index -1 PRIMARY alias4 eq_ref PRIMARY,c PRIMARY 4 test.alias5.b # Using where; FirstMatch(alias3) -1 PRIMARY alias1 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join) +1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL # Using where +1 PRIMARY alias4 ref PRIMARY,c c 4 test.alias3.d # Using index +1 PRIMARY alias5 eq_ref PRIMARY PRIMARY 4 test.alias4.b # Using where; FirstMatch(alias3) 1 PRIMARY alias2 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join) +1 PRIMARY alias1 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join) SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 WHERE alias3.d IN ( SELECT alias4.c FROM t2 AS alias4, t2 AS alias5 diff --git a/mysql-test/main/subselect_sj2.test b/mysql-test/main/subselect_sj2.test index 2b4f619a615..8886c42eb55 100644 --- a/mysql-test/main/subselect_sj2.test +++ b/mysql-test/main/subselect_sj2.test @@ -994,6 +994,7 @@ INSERT INTO t2 VALUES (6,'y'); CREATE TABLE t3 (f12 varchar(1) NOT NULL) ENGINE=InnoDB; INSERT INTO t3 VALUES ('r'),('s'),('t'),('v'),('w'),('x'),('y'); +analyze table t1,t2,t3 persistent for all; --echo # The following must use LooseScan but not join buffering --replace_column 9 # @@ -1225,6 +1226,8 @@ INSERT INTO t2 VALUES (13,'b','b'),(14,'x','x'),(15,'g','g'),(16,'p','p'), (17,'q','q'),(18,'w','w'),(19,'d','d'); +analyze table t1 persistent for all; +analyze table t2 persistent for all; --replace_column 9 # EXPLAIN SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 diff --git a/mysql-test/main/subselect_sj2_jcl6.result b/mysql-test/main/subselect_sj2_jcl6.result index 3321ba221f3..ac2d12fc5df 100644 --- a/mysql-test/main/subselect_sj2_jcl6.result +++ b/mysql-test/main/subselect_sj2_jcl6.result @@ -826,6 +826,14 @@ CREATE TABLE t2 (f14 int(11) NOT NULL, f12 varchar(1) NOT NULL, KEY (f12,f14)) E INSERT INTO t2 VALUES (6,'y'); CREATE TABLE t3 (f12 varchar(1) NOT NULL) ENGINE=InnoDB; INSERT INTO t3 VALUES ('r'),('s'),('t'),('v'),('w'),('x'),('y'); +analyze table t1,t2,t3 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status OK # The following must use LooseScan but not join buffering explain SELECT * FROM t3 @@ -1115,6 +1123,14 @@ INSERT INTO t2 VALUES (9,'d','d'),(10,'s','s'),(11,'r','r'),(12,'m','m'), (13,'b','b'),(14,'x','x'),(15,'g','g'),(16,'p','p'), (17,'q','q'),(18,'w','w'),(19,'d','d'); +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +analyze table t2 persistent for all; +Table Op Msg_type Msg_text +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK EXPLAIN SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 WHERE alias3.d IN ( @@ -1123,11 +1139,11 @@ WHERE alias5.b = alias4.b AND ( alias5.b >= alias3.b OR alias5.c != alias3.c ) ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL # -1 PRIMARY alias5 index PRIMARY c 4 NULL # Using where; Using index; Using join buffer (flat, BNL join) -1 PRIMARY alias4 eq_ref PRIMARY,c PRIMARY 4 test.alias5.b # Using where; FirstMatch(alias3); Using join buffer (incremental, BKA join); Key-ordered scan +1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL # Using where +1 PRIMARY alias4 ref PRIMARY,c c 4 test.alias3.d # Using index +1 PRIMARY alias5 eq_ref PRIMARY PRIMARY 4 test.alias4.b # Using where; FirstMatch(alias3) +1 PRIMARY alias2 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join) 1 PRIMARY alias1 ALL NULL NULL NULL NULL # Using join buffer (incremental, BNL join) -1 PRIMARY alias2 ALL NULL NULL NULL NULL # Using join buffer (incremental, BNL join) SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 WHERE alias3.d IN ( SELECT alias4.c FROM t2 AS alias4, t2 AS alias5 @@ -1144,11 +1160,11 @@ WHERE alias5.b = alias4.b AND ( alias5.b >= alias3.b OR alias3.c != alias5.c ) ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL # -1 PRIMARY alias5 index PRIMARY c 4 NULL # Using where; Using index; Using join buffer (flat, BNL join) -1 PRIMARY alias4 eq_ref PRIMARY,c PRIMARY 4 test.alias5.b # Using where; FirstMatch(alias3); Using join buffer (incremental, BKA join); Key-ordered scan +1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL # Using where +1 PRIMARY alias4 ref PRIMARY,c c 4 test.alias3.d # Using index +1 PRIMARY alias5 eq_ref PRIMARY PRIMARY 4 test.alias4.b # Using where; FirstMatch(alias3) +1 PRIMARY alias2 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join) 1 PRIMARY alias1 ALL NULL NULL NULL NULL # Using join buffer (incremental, BNL join) -1 PRIMARY alias2 ALL NULL NULL NULL NULL # Using join buffer (incremental, BNL join) SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 WHERE alias3.d IN ( SELECT alias4.c FROM t2 AS alias4, t2 AS alias5 diff --git a/mysql-test/main/subselect_sj2_mat.result b/mysql-test/main/subselect_sj2_mat.result index 7245e74b242..e55025f4fa8 100644 --- a/mysql-test/main/subselect_sj2_mat.result +++ b/mysql-test/main/subselect_sj2_mat.result @@ -815,6 +815,14 @@ CREATE TABLE t2 (f14 int(11) NOT NULL, f12 varchar(1) NOT NULL, KEY (f12,f14)) E INSERT INTO t2 VALUES (6,'y'); CREATE TABLE t3 (f12 varchar(1) NOT NULL) ENGINE=InnoDB; INSERT INTO t3 VALUES ('r'),('s'),('t'),('v'),('w'),('x'),('y'); +analyze table t1,t2,t3 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status OK # The following must use LooseScan but not join buffering explain SELECT * FROM t3 @@ -1104,6 +1112,14 @@ INSERT INTO t2 VALUES (9,'d','d'),(10,'s','s'),(11,'r','r'),(12,'m','m'), (13,'b','b'),(14,'x','x'),(15,'g','g'),(16,'p','p'), (17,'q','q'),(18,'w','w'),(19,'d','d'); +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +analyze table t2 persistent for all; +Table Op Msg_type Msg_text +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK EXPLAIN SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 WHERE alias3.d IN ( @@ -1112,11 +1128,11 @@ WHERE alias5.b = alias4.b AND ( alias5.b >= alias3.b OR alias5.c != alias3.c ) ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL # -1 PRIMARY alias5 index PRIMARY c 4 NULL # Using where; Using index -1 PRIMARY alias4 eq_ref PRIMARY,c PRIMARY 4 test.alias5.b # Using where; FirstMatch(alias3) -1 PRIMARY alias1 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join) +1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL # Using where +1 PRIMARY alias4 ref PRIMARY,c c 4 test.alias3.d # Using index +1 PRIMARY alias5 eq_ref PRIMARY PRIMARY 4 test.alias4.b # Using where; FirstMatch(alias3) 1 PRIMARY alias2 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join) +1 PRIMARY alias1 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join) SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 WHERE alias3.d IN ( SELECT alias4.c FROM t2 AS alias4, t2 AS alias5 @@ -1133,11 +1149,11 @@ WHERE alias5.b = alias4.b AND ( alias5.b >= alias3.b OR alias3.c != alias5.c ) ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL # -1 PRIMARY alias5 index PRIMARY c 4 NULL # Using where; Using index -1 PRIMARY alias4 eq_ref PRIMARY,c PRIMARY 4 test.alias5.b # Using where; FirstMatch(alias3) -1 PRIMARY alias1 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join) +1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL # Using where +1 PRIMARY alias4 ref PRIMARY,c c 4 test.alias3.d # Using index +1 PRIMARY alias5 eq_ref PRIMARY PRIMARY 4 test.alias4.b # Using where; FirstMatch(alias3) 1 PRIMARY alias2 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join) +1 PRIMARY alias1 ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join) SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 WHERE alias3.d IN ( SELECT alias4.c FROM t2 AS alias4, t2 AS alias5