diff options
author | Monty <monty@mariadb.org> | 2019-04-01 19:42:26 +0300 |
---|---|---|
committer | Monty <monty@mariadb.org> | 2019-04-01 19:47:24 +0300 |
commit | 884004502134d881af0bcb59f97a838784ab5bbe (patch) | |
tree | 499c76eb6ad251e1f62d464f146b614dc4ae393e | |
parent | 48810a00144481b7fec4ce8e23024e9f899626a7 (diff) | |
download | mariadb-git-884004502134d881af0bcb59f97a838784ab5bbe.tar.gz |
index_merge_innodb did sometimes give wrong results
Fixed by adding more rows to a table
Other things:
- Speed up index_merge tests 20% by adding begin/commit around loops that
generated rows.
-rw-r--r-- | mysql-test/include/index_merge1.inc | 4 | ||||
-rw-r--r-- | mysql-test/include/index_merge2.inc | 8 | ||||
-rw-r--r-- | mysql-test/include/index_merge_2sweeps.inc | 2 | ||||
-rw-r--r-- | mysql-test/include/index_merge_ror.inc | 8 | ||||
-rw-r--r-- | mysql-test/include/index_merge_ror_cpk.inc | 4 | ||||
-rw-r--r-- | mysql-test/main/index_merge_innodb.result | 5 | ||||
-rw-r--r-- | mysql-test/main/index_merge_innodb.test | 2 | ||||
-rw-r--r-- | mysql-test/main/index_merge_myisam.result | 3 |
8 files changed, 34 insertions, 2 deletions
diff --git a/mysql-test/include/index_merge1.inc b/mysql-test/include/index_merge1.inc index a5362b5c819..5498a607c3c 100644 --- a/mysql-test/include/index_merge1.inc +++ b/mysql-test/include/index_merge1.inc @@ -37,12 +37,14 @@ insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8); let $1=7; set @d=8; +begin; while ($1) { eval insert into t0 select key1+@d from t0; eval set @d=@d*2; dec $1; } +commit; --enable_query_log alter table t0 add key2 int not null, add index i2(key2); @@ -365,6 +367,7 @@ INSERT INTO t1 VALUES ('foo','bar', 'ZZ'),('fuz','baz', 'ZZ'); --disable_query_log let $1=9; +begin; while ($1) { eval INSERT INTO t1 SELECT * from t1 WHERE cola = 'foo'; @@ -377,6 +380,7 @@ while ($1) eval INSERT INTO t1 SELECT * from t1 WHERE cola <> 'foo'; dec $1; } +commit; --enable_query_log diff --git a/mysql-test/include/index_merge2.inc b/mysql-test/include/index_merge2.inc index 03afa49d323..ae9adac882f 100644 --- a/mysql-test/include/index_merge2.inc +++ b/mysql-test/include/index_merge2.inc @@ -34,11 +34,13 @@ create table t1 --disable_query_log let $1=200; +begin; while ($1) { eval insert into t1 values (200-$1, $1); dec $1; } +commit; --enable_query_log # No primary key @@ -80,11 +82,13 @@ create table t1 ( show warnings; --disable_query_log let $1=30; +begin; while ($1) { eval insert into t1 (key1, key2, filler) values ($1/4, $1/8, 'filler-data'); dec $1; } +commit; --enable_query_log explain select pk from t1 where key1 = 1 and key2 = 1; select pk from t1 where key2 = 1 and key1 = 1; @@ -331,16 +335,20 @@ insert into t1(key1) values (1),(2),(3),(4),(5),(6),(7),(8); let $1=7; set @d=8; +begin; while ($1) { eval insert into t1 (key1) select key1+@d from t1; eval set @d=@d*2; dec $1; } +commit; alter table t1 add index i2(key2); alter table t1 add index i3(key3); update t1 set key2=key1,key3=key1; + +insert into t1 select 10000+key1, 10000+key2,10000+key3 from t1; analyze table t1; # to test the bug, the following must use "sort_union": diff --git a/mysql-test/include/index_merge_2sweeps.inc b/mysql-test/include/index_merge_2sweeps.inc index 3ae7e5b3c09..ef356e12969 100644 --- a/mysql-test/include/index_merge_2sweeps.inc +++ b/mysql-test/include/index_merge_2sweeps.inc @@ -32,12 +32,14 @@ create table t1 ( --disable_query_log +begin; let $1=1000; while ($1) { eval insert into t1 values($1, $1, $1, 'filler-data','filler-data-2'); dec $1; } +commit; --enable_query_log select * from t1 where (key1 >= 2 and key1 <= 10) or (pk >= 4 and pk <=8 ); diff --git a/mysql-test/include/index_merge_ror.inc b/mysql-test/include/index_merge_ror.inc index fdb966e6885..3ec6e342994 100644 --- a/mysql-test/include/index_merge_ror.inc +++ b/mysql-test/include/index_merge_ror.inc @@ -67,18 +67,21 @@ create table t1 create table t0 as select * from t1; --disable_query_log --echo # Printing of many insert into t0 values (....) disabled. +begin; let $cnt=1000; while ($cnt) { eval insert into t0 values (1, 2, 3, 1, 2, 3, 0, 0, 0, 0, 'data1', 'data2', 'data3', 'data4', 'data5', 'data6'); dec $cnt; } +commit; --enable_query_log alter table t1 disable keys; --disable_query_log --echo # Printing of many insert into t1 select .... from t0 disabled. let $1=4; +begin; while ($1) { let $2=4; @@ -94,6 +97,7 @@ while ($1) } dec $1; } +commit; --echo # Printing of many insert into t1 (...) values (....) disabled. # Row retrieval tests @@ -101,6 +105,7 @@ while ($1) # insert enough rows for index intersection to be used for (key1,key2) insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 100, 100,'key1-key2-key3-key4'); let $cnt=400; +begin; while ($cnt) { eval insert into t1 (key1, key2, key3, key4, filler1) values (100, -1, 100, -1,'key1-key3'); @@ -112,6 +117,7 @@ while ($cnt) eval insert into t1 (key1, key2, key3, key4, filler1) values (-1, 100, -1, 100,'key2-key4'); dec $cnt; } +commit; --enable_query_log alter table t1 enable keys; select count(*) from t1; @@ -249,6 +255,7 @@ create table t2 ( --disable_query_log let $1=8; +begin; while ($1) { eval insert into t2 values (repeat(char($1+64), 8),repeat(char($1+64), 8),'filler1', 'filler2'); @@ -256,6 +263,7 @@ while ($1) } insert into t2 select * from t2; insert into t2 select * from t2; +commit; --enable_query_log # The table row buffer is reused. Fill it with rows that don't match. diff --git a/mysql-test/include/index_merge_ror_cpk.inc b/mysql-test/include/index_merge_ror_cpk.inc index df42745b4fc..0a307aa4fdb 100644 --- a/mysql-test/include/index_merge_ror_cpk.inc +++ b/mysql-test/include/index_merge_ror_cpk.inc @@ -56,14 +56,14 @@ create table t1 ); --disable_query_log -set autocommit=0; +begin; let $1=10000; while ($1) { eval insert into t1 values ($1 div 10,$1 mod 100, $1/100,$1/100, $1/100,$1/100,$1/100,$1/100,$1/100, $1 mod 100, $1/1000,'filler-data-$1','filler2'); dec $1; } -set autocommit=1; +commit; --enable_query_log # Verify that range scan on CPK is ROR diff --git a/mysql-test/main/index_merge_innodb.result b/mysql-test/main/index_merge_innodb.result index 1562fa84c34..a25fe928a24 100644 --- a/mysql-test/main/index_merge_innodb.result +++ b/mysql-test/main/index_merge_innodb.result @@ -296,6 +296,7 @@ key3 int not null default 0 ); insert into t1(key1) values (1),(2),(3),(4),(5),(6),(7),(8); set @d=8; +begin; insert into t1 (key1) select key1+@d from t1; set @d=@d*2; insert into t1 (key1) select key1+@d from t1; @@ -310,9 +311,11 @@ insert into t1 (key1) select key1+@d from t1; set @d=@d*2; insert into t1 (key1) select key1+@d from t1; set @d=@d*2; +commit; alter table t1 add index i2(key2); alter table t1 add index i3(key3); update t1 set key2=key1,key3=key1; +insert into t1 select 10000+key1, 10000+key2,10000+key3 from t1; analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected @@ -675,6 +678,7 @@ a int, b int, INDEX idx(a)) ENGINE=INNODB; +begin; INSERT INTO t1(a,b) VALUES (11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500), (3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800), @@ -694,6 +698,7 @@ INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1 VALUES (1000000, 0, 0); +commit; SET SESSION sort_buffer_size = 1024*36; set @tmp_optimizer_switch=@@optimizer_switch; set optimizer_switch='derived_merge=off,derived_with_keys=off'; diff --git a/mysql-test/main/index_merge_innodb.test b/mysql-test/main/index_merge_innodb.test index 2581cccced0..7f9d6ac6eb1 100644 --- a/mysql-test/main/index_merge_innodb.test +++ b/mysql-test/main/index_merge_innodb.test @@ -47,6 +47,7 @@ CREATE TABLE t1 ( INDEX idx(a)) ENGINE=INNODB; +begin; INSERT INTO t1(a,b) VALUES (11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500), (3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800), @@ -66,6 +67,7 @@ INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1 VALUES (1000000, 0, 0); +commit; SET SESSION sort_buffer_size = 1024*36; set @tmp_optimizer_switch=@@optimizer_switch; diff --git a/mysql-test/main/index_merge_myisam.result b/mysql-test/main/index_merge_myisam.result index e1f7c72bc4a..3d31f8d3dfa 100644 --- a/mysql-test/main/index_merge_myisam.result +++ b/mysql-test/main/index_merge_myisam.result @@ -1135,6 +1135,7 @@ key3 int not null default 0 ); insert into t1(key1) values (1),(2),(3),(4),(5),(6),(7),(8); set @d=8; +begin; insert into t1 (key1) select key1+@d from t1; set @d=@d*2; insert into t1 (key1) select key1+@d from t1; @@ -1149,9 +1150,11 @@ insert into t1 (key1) select key1+@d from t1; set @d=@d*2; insert into t1 (key1) select key1+@d from t1; set @d=@d*2; +commit; alter table t1 add index i2(key2); alter table t1 add index i3(key3); update t1 set key2=key1,key3=key1; +insert into t1 select 10000+key1, 10000+key2,10000+key3 from t1; analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected |