From 75d8f60017fb47293190f48d5ae172fb54c3fdb3 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Wed, 14 Oct 2020 09:21:10 +0300 Subject: MDEV-23955 main.multi_update_big times out Copying generated data from the sequence engine should be faster than copying from MyISAM to MyISAM. Let us hope that this fixes the test failure on Travis CI ARMv8. --- mysql-test/main/multi_update_big.result | 3 +-- mysql-test/main/multi_update_big.test | 21 +++---------------- mysql-test/main/sum_distinct-big.result | 37 +++++++-------------------------- mysql-test/main/sum_distinct-big.test | 32 +++++++--------------------- 4 files changed, 20 insertions(+), 73 deletions(-) diff --git a/mysql-test/main/multi_update_big.result b/mysql-test/main/multi_update_big.result index 3712e638f40..746a158f21f 100644 --- a/mysql-test/main/multi_update_big.result +++ b/mysql-test/main/multi_update_big.result @@ -1,6 +1,5 @@ -DROP TABLE IF EXISTS t1,t2; CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL) ; -# The protocolling of many inserts into t1 is suppressed. +INSERT INTO t1 SELECT seq,seq FROM seq_1_to_2097152; ALTER TABLE t1 ADD INDEX i1(a); DELETE FROM t1 WHERE a > 2000000; CREATE TABLE t2 LIKE t1; diff --git a/mysql-test/main/multi_update_big.test b/mysql-test/main/multi_update_big.test index a0f17fabec4..d6fec704822 100644 --- a/mysql-test/main/multi_update_big.test +++ b/mysql-test/main/multi_update_big.test @@ -1,4 +1,5 @@ --source include/long_test.inc +--source include/have_sequence.inc # # Test of update statement that uses many tables. @@ -40,25 +41,9 @@ if (`SELECT '$BIG_TEST' = '' AND $need_big = 1`) # Bug#1820 Rows not deleted from second table on multi-table delete # ---disable_warnings -DROP TABLE IF EXISTS t1,t2; ---enable_warnings - CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL) ; ---echo # The protocolling of many inserts into t1 is suppressed. ---disable_query_log -INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4); -let $1=19; -set @d=4; -begin; -while ($1) -{ - eval INSERT INTO t1 SELECT a+@d,b+@d FROM t1; - eval SET @d=@d*2; - dec $1; -} -commit; ---enable_query_log +INSERT INTO t1 SELECT seq,seq FROM seq_1_to_2097152; + ALTER TABLE t1 ADD INDEX i1(a); DELETE FROM t1 WHERE a > 2000000; CREATE TABLE t2 LIKE t1; diff --git a/mysql-test/main/sum_distinct-big.result b/mysql-test/main/sum_distinct-big.result index 0556fd9a7c3..086dd33fad2 100644 --- a/mysql-test/main/sum_distinct-big.result +++ b/mysql-test/main/sum_distinct-big.result @@ -3,27 +3,7 @@ set @save_max_heap_table_size=@@max_heap_table_size; set default_storage_engine=MYISAM; CREATE TABLE t1 (id INTEGER); CREATE TABLE t2 (id INTEGER); -INSERT INTO t1 (id) VALUES (1), (1), (1),(1); -INSERT INTO t1 (id) SELECT id FROM t1; -/* 8 */ -INSERT INTO t1 (id) SELECT id FROM t1; -/* 12 */ -INSERT INTO t1 (id) SELECT id FROM t1; -/* 16 */ -INSERT INTO t1 (id) SELECT id FROM t1; -/* 20 */ -INSERT INTO t1 (id) SELECT id FROM t1; -/* 24 */ -INSERT INTO t1 SELECT id+1 FROM t1; -INSERT INTO t1 SELECT id+2 FROM t1; -INSERT INTO t1 SELECT id+4 FROM t1; -INSERT INTO t1 SELECT id+8 FROM t1; -INSERT INTO t1 SELECT id+16 FROM t1; -INSERT INTO t1 SELECT id+32 FROM t1; -INSERT INTO t1 SELECT id+64 FROM t1; -INSERT INTO t1 SELECT id+128 FROM t1; -INSERT INTO t1 SELECT id+256 FROM t1; -INSERT INTO t1 SELECT id+512 FROM t1; +INSERT INTO t1 SELECT b.seq FROM seq_1_to_128,seq_1_to_1024 b; SELECT AVG(DISTINCT id) FROM t1 GROUP BY id % 13; AVG(DISTINCT id) 513.5000 @@ -54,11 +34,9 @@ SUM(DISTINCT id)/COUNT(DISTINCT id) 517.0000 511.5000 512.5000 -INSERT INTO t1 SELECT id+1024 FROM t1; -INSERT INTO t1 SELECT id+2048 FROM t1; -INSERT INTO t1 SELECT id+4096 FROM t1; -INSERT INTO t1 SELECT id+8192 FROM t1; -INSERT INTO t2 SELECT id FROM t1 ORDER BY id*rand(); +INSERT INTO t1 SELECT b.seq FROM seq_1_to_128,seq_1025_to_16384 b; +INSERT INTO t2 SELECT b.seq FROM seq_1_to_128 a,seq_1_to_16384 b +ORDER by (a.seq*0+b.seq)*rand(); SELECT SUM(DISTINCT id) sm FROM t1; sm 134225920 @@ -110,9 +88,10 @@ sm # (bug #56927) # SET max_heap_table_size=default; -INSERT INTO t1 SELECT id+16384 FROM t1; -DELETE FROM t2; -INSERT INTO t2 SELECT id FROM t1 ORDER BY id*rand(); +INSERT INTO t1 SELECT b.seq FROM seq_1_to_128,seq_16385_to_32768 b; +TRUNCATE t2; +INSERT INTO t2 SELECT b.seq FROM seq_1_to_128 a,seq_1_to_32768 b +ORDER BY (a.seq*0+b.seq)*rand(); SELECT SUM(DISTINCT id) sm FROM t2; sm 536887296 diff --git a/mysql-test/main/sum_distinct-big.test b/mysql-test/main/sum_distinct-big.test index 42eeae743b1..41aa5a44c19 100644 --- a/mysql-test/main/sum_distinct-big.test +++ b/mysql-test/main/sum_distinct-big.test @@ -21,32 +21,15 @@ set default_storage_engine=MYISAM; CREATE TABLE t1 (id INTEGER); CREATE TABLE t2 (id INTEGER); -INSERT INTO t1 (id) VALUES (1), (1), (1),(1); -INSERT INTO t1 (id) SELECT id FROM t1; /* 8 */ -INSERT INTO t1 (id) SELECT id FROM t1; /* 12 */ -INSERT INTO t1 (id) SELECT id FROM t1; /* 16 */ -INSERT INTO t1 (id) SELECT id FROM t1; /* 20 */ -INSERT INTO t1 (id) SELECT id FROM t1; /* 24 */ -INSERT INTO t1 SELECT id+1 FROM t1; -INSERT INTO t1 SELECT id+2 FROM t1; -INSERT INTO t1 SELECT id+4 FROM t1; -INSERT INTO t1 SELECT id+8 FROM t1; -INSERT INTO t1 SELECT id+16 FROM t1; -INSERT INTO t1 SELECT id+32 FROM t1; -INSERT INTO t1 SELECT id+64 FROM t1; -INSERT INTO t1 SELECT id+128 FROM t1; -INSERT INTO t1 SELECT id+256 FROM t1; -INSERT INTO t1 SELECT id+512 FROM t1; +INSERT INTO t1 SELECT b.seq FROM seq_1_to_128,seq_1_to_1024 b; # Just test that AVG(DISTINCT) is there SELECT AVG(DISTINCT id) FROM t1 GROUP BY id % 13; SELECT SUM(DISTINCT id)/COUNT(DISTINCT id) FROM t1 GROUP BY id % 13; -INSERT INTO t1 SELECT id+1024 FROM t1; -INSERT INTO t1 SELECT id+2048 FROM t1; -INSERT INTO t1 SELECT id+4096 FROM t1; -INSERT INTO t1 SELECT id+8192 FROM t1; -INSERT INTO t2 SELECT id FROM t1 ORDER BY id*rand(); +INSERT INTO t1 SELECT b.seq FROM seq_1_to_128,seq_1025_to_16384 b; +INSERT INTO t2 SELECT b.seq FROM seq_1_to_128 a,seq_1_to_16384 b +ORDER by (a.seq*0+b.seq)*rand(); # SELECT '++++++++++++++++++++++++++++++++++++++++++++++++++'; @@ -76,9 +59,10 @@ SELECT SUM(DISTINCT id) sm FROM t1 GROUP BY id % 13; SET max_heap_table_size=default; -INSERT INTO t1 SELECT id+16384 FROM t1; -DELETE FROM t2; -INSERT INTO t2 SELECT id FROM t1 ORDER BY id*rand(); +INSERT INTO t1 SELECT b.seq FROM seq_1_to_128,seq_16385_to_32768 b; +TRUNCATE t2; +INSERT INTO t2 SELECT b.seq FROM seq_1_to_128 a,seq_1_to_32768 b +ORDER BY (a.seq*0+b.seq)*rand(); SELECT SUM(DISTINCT id) sm FROM t2; -- cgit v1.2.1