diff options
author | Marko Mäkelä <marko.makela@mariadb.com> | 2022-11-08 09:00:58 +0200 |
---|---|---|
committer | Marko Mäkelä <marko.makela@mariadb.com> | 2022-11-08 09:00:58 +0200 |
commit | 314ed9f5eccc527654b0a63064561749a7b842f5 (patch) | |
tree | 2298829929d9fc5281a2b5e60dc25ba7e328e559 /mysql-test/main | |
parent | 456d4a508c510f4cb3798145944a47c8f3954465 (diff) | |
download | mariadb-git-314ed9f5eccc527654b0a63064561749a7b842f5.tar.gz |
Work around MDEV-24813 in some tests
Not creating explicit record locks will speed up the test.
Also, disable the use of InnoDB persistent statistics in the test of
MDEV-27270 to avoid intermittent failures in 10.6 or later
(after commit 9608773f75e2ca21491ef6825c3616cdc96d1ca5)
due to the nondeterministic scheduling of STATS_AUTO_PERSISTENT.
Diffstat (limited to 'mysql-test/main')
-rw-r--r-- | mysql-test/main/order_by_innodb.result | 30 | ||||
-rw-r--r-- | mysql-test/main/order_by_innodb.test | 35 | ||||
-rw-r--r-- | mysql-test/main/range_innodb.result | 15 | ||||
-rw-r--r-- | mysql-test/main/range_innodb.test | 22 |
4 files changed, 38 insertions, 64 deletions
diff --git a/mysql-test/main/order_by_innodb.result b/mysql-test/main/order_by_innodb.result index 28922ef65f2..17d39eb12e6 100644 --- a/mysql-test/main/order_by_innodb.result +++ b/mysql-test/main/order_by_innodb.result @@ -1,8 +1,8 @@ -drop table if exists t0,t1,t2,t3; # # MDEV-6434: Wrong result (extra rows) with ORDER BY, multiple-column index, InnoDB # -CREATE TABLE t1 (a INT, b INT, c INT, d TEXT, KEY idx(a,b,c)) ENGINE=InnoDB; +CREATE TABLE t1 (a INT, b INT, c INT, d TEXT, KEY idx(a,b,c)) ENGINE=InnoDB +STATS_PERSISTENT=0; INSERT INTO t1 (a,c) VALUES (8, 9),(8, 10),(13, 15),(16, 17),(16, 18),(16, 19),(20, 21), (20, 22),(20, 24),(20, 25),(20, 26),(20, 27),(20, 28); @@ -14,8 +14,6 @@ DROP TABLE t1; # # MDEV-9457: Poor query plan chosen for ORDER BY query by a recent 10.1 # -create table t0 (a int); -insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 ( pk int primary key, key1 int, @@ -23,15 +21,9 @@ key2 int, col1 char(255), key(key1), key(key2) -) engine=innodb; -set @a=-1; +) engine=innodb stats_persistent=0; insert into t1 -select -@a:=@a+1, -@a, -@a, -repeat('abcd', 63) -from t0 A, t0 B, t0 C, t0 D; +select seq,seq,seq,repeat('abcd', 63) from seq_0_to_9999; # The following must NOT use 'index' on PK. # It should use index_merge(key1,key2) + filesort explain @@ -47,7 +39,7 @@ from t1 where key1<3 or key2<3; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL # Using sort_union(key1,key2); Using where -drop table t0, t1; +drop table t1; # # MDEV-18094: Query with order by limit picking index scan over filesort # @@ -78,9 +70,12 @@ drop table t1,t0; # MDEV-14071: wrong results with orderby_uses_equalities=on # (duplicate of MDEV-13994) # -CREATE TABLE t1 (i int, j int, z int,PRIMARY KEY (i,j), KEY (z)) ENGINE=InnoDB; -CREATE TABLE t2 (i int, j int, PRIMARY KEY (i,j)) ENGINE=InnoDB; -CREATE TABLE t3 (j int, n varchar(5), PRIMARY KEY (j)) ENGINE=InnoDB; +CREATE TABLE t1 (i int, j int, z int,PRIMARY KEY (i,j), KEY (z)) ENGINE=InnoDB +STATS_PERSISTENT=0; +CREATE TABLE t2 (i int, j int, PRIMARY KEY (i,j)) ENGINE=InnoDB +STATS_PERSISTENT=0; +CREATE TABLE t3 (j int, n varchar(5), PRIMARY KEY (j)) ENGINE=InnoDB +STATS_PERSISTENT=0; INSERT INTO t1 VALUES (127,0,1),(188,0,1),(206,0,1),(218,0,1),(292,0,1),(338,0,1),(375,0,1), (381,0,1),(409,0,1),(466,0,1),(469,0,1),(498,0,1),(656,0,1); @@ -150,7 +145,8 @@ DROP TABLE t1,t2,t3; # # MDEV-25858: Query results are incorrect when indexes are added # -CREATE TABLE t1 (id int NOT NULL PRIMARY KEY) engine=innodb; +CREATE TABLE t1 (id int NOT NULL PRIMARY KEY) engine=innodb +STATS_PERSISTENT=0; insert into t1 values (1),(2),(3); CREATE TABLE t2 ( id int NOT NULL PRIMARY KEY, diff --git a/mysql-test/main/order_by_innodb.test b/mysql-test/main/order_by_innodb.test index af12644c073..29b796f67bc 100644 --- a/mysql-test/main/order_by_innodb.test +++ b/mysql-test/main/order_by_innodb.test @@ -2,16 +2,14 @@ # ORDER BY handling (e.g. filesort) tests that require innodb # -- source include/have_innodb.inc - ---disable_warnings -drop table if exists t0,t1,t2,t3; ---enable_warnings +-- source include/have_sequence.inc --echo # --echo # MDEV-6434: Wrong result (extra rows) with ORDER BY, multiple-column index, InnoDB --echo # -CREATE TABLE t1 (a INT, b INT, c INT, d TEXT, KEY idx(a,b,c)) ENGINE=InnoDB; +CREATE TABLE t1 (a INT, b INT, c INT, d TEXT, KEY idx(a,b,c)) ENGINE=InnoDB +STATS_PERSISTENT=0; INSERT INTO t1 (a,c) VALUES (8, 9),(8, 10),(13, 15),(16, 17),(16, 18),(16, 19),(20, 21), @@ -24,9 +22,6 @@ DROP TABLE t1; --echo # --echo # MDEV-9457: Poor query plan chosen for ORDER BY query by a recent 10.1 --echo # -create table t0 (a int); -insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); - create table t1 ( pk int primary key, key1 int, @@ -34,16 +29,10 @@ create table t1 ( col1 char(255), key(key1), key(key2) -) engine=innodb; +) engine=innodb stats_persistent=0; -set @a=-1; insert into t1 -select - @a:=@a+1, - @a, - @a, - repeat('abcd', 63) -from t0 A, t0 B, t0 C, t0 D; +select seq,seq,seq,repeat('abcd', 63) from seq_0_to_9999; --echo # The following must NOT use 'index' on PK. --echo # It should use index_merge(key1,key2) + filesort @@ -60,7 +49,7 @@ select * from t1 where key1<3 or key2<3; -drop table t0, t1; +drop table t1; --echo # --echo # MDEV-18094: Query with order by limit picking index scan over filesort @@ -93,9 +82,12 @@ drop table t1,t0; --echo # (duplicate of MDEV-13994) --echo # -CREATE TABLE t1 (i int, j int, z int,PRIMARY KEY (i,j), KEY (z)) ENGINE=InnoDB; -CREATE TABLE t2 (i int, j int, PRIMARY KEY (i,j)) ENGINE=InnoDB; -CREATE TABLE t3 (j int, n varchar(5), PRIMARY KEY (j)) ENGINE=InnoDB; +CREATE TABLE t1 (i int, j int, z int,PRIMARY KEY (i,j), KEY (z)) ENGINE=InnoDB +STATS_PERSISTENT=0; +CREATE TABLE t2 (i int, j int, PRIMARY KEY (i,j)) ENGINE=InnoDB +STATS_PERSISTENT=0; +CREATE TABLE t3 (j int, n varchar(5), PRIMARY KEY (j)) ENGINE=InnoDB +STATS_PERSISTENT=0; INSERT INTO t1 VALUES (127,0,1),(188,0,1),(206,0,1),(218,0,1),(292,0,1),(338,0,1),(375,0,1), @@ -139,7 +131,8 @@ DROP TABLE t1,t2,t3; --echo # MDEV-25858: Query results are incorrect when indexes are added --echo # -CREATE TABLE t1 (id int NOT NULL PRIMARY KEY) engine=innodb; +CREATE TABLE t1 (id int NOT NULL PRIMARY KEY) engine=innodb +STATS_PERSISTENT=0; insert into t1 values (1),(2),(3); CREATE TABLE t2 ( diff --git a/mysql-test/main/range_innodb.result b/mysql-test/main/range_innodb.result index df111bc05be..cbe763bcf6e 100644 --- a/mysql-test/main/range_innodb.result +++ b/mysql-test/main/range_innodb.result @@ -1,15 +1,11 @@ # # Range optimizer (and related) tests that need InnoDB. # -drop table if exists t0, t1, t2; # # MDEV-6735: Range checked for each record used with key # create table t0(a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -create table t1(a int); -insert into t1 select A.a + B.a* 10 + C.a * 100 + D.a * 1000 -from t0 A, t0 B, t0 C, t0 D; create table t2 ( a int, b int, @@ -22,12 +18,12 @@ key(b) ) engine=innodb; insert into t2 select -a,a, +seq,seq, repeat('0123456789', 10), repeat('0123456789', 10), repeat('0123456789', 10), repeat('0123456789', 10) -from t1; +from seq_0_to_9999; analyze table t2; Table Op Msg_type Msg_text test.t2 analyze status OK @@ -36,7 +32,7 @@ explain select * from t0 left join t2 on t2.a <t0.a and t2.b between 50 and 250; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 ALL NULL NULL NULL NULL 10 1 SIMPLE t2 range a,b b 5 NULL 201 Using where; Using join buffer (flat, BNL join) -drop table t0,t1,t2; +drop table t0,t2; # # MDEV-10466: constructing an invalid SEL_ARG # @@ -88,15 +84,14 @@ drop table t1,t2; # set @optimizer_switch_save= @@optimizer_switch; set optimizer_switch='index_merge_sort_intersection=off'; -create table t0 (a int)engine=innodb; +create table t0(a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 ( a int, b int, c int, key(a),key(b),key(c) )engine=innodb; insert into t1 -select A.a+10*B.a, A.a+10*B.a, A.a+10*B.a+100*C.a -from t0 A, t0 B, t0 C, t0 D where D.a<5; +select a.seq/10, a.seq/10, a.seq from seq_0_to_499 a, seq_0_to_4 b; SET @saved_dbug = @@GLOBAL.debug_dbug; set @@global.debug_dbug="+d,ha_index_init_fail"; explain select * from t1 where a=10 and b=10; diff --git a/mysql-test/main/range_innodb.test b/mysql-test/main/range_innodb.test index b8a6a7c960c..f0c9173a31c 100644 --- a/mysql-test/main/range_innodb.test +++ b/mysql-test/main/range_innodb.test @@ -4,10 +4,7 @@ --source include/have_innodb.inc --source include/have_debug.inc - ---disable_warnings -drop table if exists t0, t1, t2; ---enable_warnings +--source include/have_sequence.inc --echo # --echo # MDEV-6735: Range checked for each record used with key @@ -16,10 +13,6 @@ drop table if exists t0, t1, t2; create table t0(a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -create table t1(a int); -insert into t1 select A.a + B.a* 10 + C.a * 100 + D.a * 1000 -from t0 A, t0 B, t0 C, t0 D; - create table t2 ( a int, b int, @@ -33,18 +26,18 @@ create table t2 ( insert into t2 select - a,a, + seq,seq, repeat('0123456789', 10), repeat('0123456789', 10), repeat('0123456789', 10), repeat('0123456789', 10) -from t1; +from seq_0_to_9999; analyze table t2; --echo # The following must not use "Range checked for each record": explain select * from t0 left join t2 on t2.a <t0.a and t2.b between 50 and 250; -drop table t0,t1,t2; +drop table t0,t2; --echo # @@ -97,15 +90,14 @@ drop table t1,t2; set @optimizer_switch_save= @@optimizer_switch; set optimizer_switch='index_merge_sort_intersection=off'; -create table t0 (a int)engine=innodb; +create table t0(a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 ( a int, b int, c int, key(a),key(b),key(c) )engine=innodb; insert into t1 -select A.a+10*B.a, A.a+10*B.a, A.a+10*B.a+100*C.a -from t0 A, t0 B, t0 C, t0 D where D.a<5; +select a.seq/10, a.seq/10, a.seq from seq_0_to_499 a, seq_0_to_4 b; SET @saved_dbug = @@GLOBAL.debug_dbug; set @@global.debug_dbug="+d,ha_index_init_fail"; explain select * from t1 where a=10 and b=10; @@ -121,8 +113,6 @@ set @@optimizer_switch= @optimizer_switch_save; --echo # MDEV-27262: Index intersection with full scan over an index --echo # ---source include/have_sequence.inc - CREATE TABLE t1 ( id int(10) unsigned NOT NULL AUTO_INCREMENT, p char(32) DEFAULT NULL, |