revision-id: d3a2c173b63e784513e67502eb91345f7620bd7d (mariadb-10.4.11-18-gd3a2c173b63) parent(s): 59d4f2a373a7960a533e653877ab69a97e91444a author: Varun Gupta committer: Varun Gupta timestamp: 2019-12-31 03:18:09 +0530 message: Big Test added for sorting --- mysql-test/main/order_by_pack_big.result | 170 +++++++++++++++++++++++++++++++ mysql-test/main/order_by_pack_big.test | 81 +++++++++++++++ 2 files changed, 251 insertions(+) diff --git a/mysql-test/main/order_by_pack_big.result b/mysql-test/main/order_by_pack_big.result new file mode 100644 index 00000000000..b1b7b7e5940 --- /dev/null +++ b/mysql-test/main/order_by_pack_big.result @@ -0,0 +1,170 @@ +set @save_rand_seed1= @@RAND_SEED1; +set @save_rand_seed2= @@RAND_SEED2; +set @@RAND_SEED1=810763568, @@RAND_SEED2=600681772; +create table t1(a int); +insert into t1 select seq from seq_1_to_10000 order by rand(); +# +# function f1 has parameters mean(peak) and std_dev (standard deviation) +# for a normal distribution +# +CREATE FUNCTION f1(mean DOUBLE, std_dev DOUBLE) RETURNS DOUBLE +BEGIN +set @z= (rand() + rand() + rand() + rand() + rand() + rand() + +rand() + rand() + rand() + rand() + rand() + rand() - 6); +set @z= std_dev*@z + mean; +return @z; +END| +CREATE function f2(len INT) RETURNS varchar(256) +BEGIN +DECLARE str VARCHAR(256) DEFAULT ''; +DECLARE x INT DEFAULT 0; +WHILE (len > 0 AND len < 256) DO +SET x =round(rand()*25); +SET str= CONCAT(str, CHAR(65 + x)); +SET len= len-1; +END WHILE; +RETURN str; +END| +CREATE function f3(mean DOUBLE, std_dev DOUBLE, min_val INT) RETURNS INT +BEGIN +DECLARE r DOUBLE DEFAULT 0; +WHILE 1=1 DO +set r= f1(mean, std_dev); +IF (r >= min_val) THEN +RETURN round(r); +end if; +END WHILE; +RETURN 0; +END| +create table t2 (id INT NOT NULL, a INT, b int); +insert into t2 select a, f3(12, 8.667, 0), f3(32, 16, 0) from t1; +CREATE TABLE t3( +id INT NOT NULL, +names VARCHAR(64), +address VARCHAR(132), +PRIMARY KEY (id) +); +insert into t3 select id, f2(a) , f2(b) from t2; +set sort_buffer_size=262144*10; +flush status; +select id, +MD5(group_concat(substring(names,1,3), substring(address,1,3))) +FROM t3 +GROUP BY id DIV 100 +ORDER BY id; +id MD5(group_concat(substring(names,1,3), substring(address,1,3))) +10 4e2909ba6af73a6f2331c332272ebe6f +149 2591aa134a03c0baedb1f2e09d37fc01 +232 779fe09e8677c88c17dab1c2c5c54a9e +311 6f66733f6f2fee55853a4081dac362bf +430 b7431e4e30f7ce214350649a9c1877fa +502 5a39b69003d083e81ffc5ff7d1840ae0 +665 796aafa79e47c3f99a2f83c2fe87e9af +719 1adaa36769a3ec1aaf61cc67ee035bf2 +883 558686327dba2531104dc32497f13806 +942 68c2c6be36ad81da00f875e05678ac1a +1007 c9fe7a2a3b1e77f3512afa96edcdbd0f +1134 f51a946d3bcefb2ef0bb9a816654993f +1226 968710fb0ee891bf5efedac64daa7f59 +1367 e84b491129560c4a31176ec17c7e4651 +1486 f13314fa07ab1786256544206f552126 +1502 723fa7da3db0cfc61bce43af13bd7733 +1674 9296d3b0d959e7c7bfb0536c0a680003 +1781 dd9dc3ee5356e90a7279d2a7e09ab067 +1803 a0787eb53c2ddfe4dc501cbd62cd6ef5 +1969 d0d2d4c9a33b5e7c7ba517b21bbb225c +2087 0491191a92ecef7813b491c651c3017a +2111 be7737ae27e517dee4d2c2490dd08fe5 +2268 13387d22efac2835387acb5fdf466847 +2328 0da4eaaa6d40790e83426adc88c6b867 +2416 c6376f9b85a6247e4bb4b7fed7019654 +2599 07c0494dc58e02c9e4d476ed4e828999 +2602 3f2b2cd46fdbcb9f4de5d22af1ddf332 +2777 cf080c656cf2c72c6c40df9522c4a444 +2858 dd6f033f165c3764e5eee748b6a4b306 +2922 d69118e72d77dc35d437a23bc620600a +3027 d7b6c3a6cd712c99fa428d40d7d14e8f +3131 8765144e43704034b8bde8195c3bd566 +3220 4da111ce557f3c27ed0413f5ced46e66 +3331 c4b80698fd39a7124e5bbac98d949980 +3452 db6e2ab7247a49a5a03ae9eb5d197c50 +3519 271a797d3251ea00339f72d18ccb6d2f +3680 7c04e35481d3d64878330db84158bb67 +3799 15febc7ee00f06dbf96a961d90840571 +3876 936394440bf45ba95fcb237c106f0ad4 +3937 1468b953f59d3d9124646cb9a62a066c +4029 a2d060975fe98313cc4d6d0967134862 +4158 3ef575890dc439f165a6c0e7cecef3a0 +4291 ad24580ca5589c22e66ee9b2507bffe2 +4355 469e098eb29d367bf52e8dcb626af560 +4461 985a0571305b940cf9cdddc0bd992868 +4544 b596b41cced9a72635ebb621990a9884 +4646 530f8884df8f69e89a422dccf15dde4e +4714 e4c39cbe81b717e5dc55c3d8a5f958bd +4848 0441809790e48eae3e3451982d6e9685 +4935 5bf7c598b37c14c1c667c7a3e765ef4b +5014 52af0e4bcdcaa9798efa5208fc14160f +5184 fb35e60e92eb7d77d24d74fca72f1d74 +5219 3b7efa9a63a7c2abc6242706bd4e178b +5349 e34bcfd71d15658df22d985d7cfd4f46 +5400 8567ac3ba9b56908b1df46b72b44e91d +5507 b437451c7e0d0b0be72f7bea8cbad4ff +5672 071a6b393989a88074bf31f780d7d6ee +5725 eced78afcf11ac492c2e14682ca097dd +5849 376cd2d795fb1cc25ea9832e37b193fd +5941 c25cbc66d609b9beee3c948cb0339f74 +6064 4b0eaec74cb4c0234b30373493749360 +6135 a5108f050da716c3f2b030587c1ce615 +6289 b19351d2e4e58a77f1f33f15c90c6181 +6329 b213caf63af61795ac76e7c56d1d1314 +6404 a6128a481c3c23141b200f0693bba3a7 +6580 06dca934fccedb081f18d418fc7a3909 +6653 20dd771bd5239beb009b81853f5adc72 +6773 cb107d95104e7ed5f9796a3fc8103251 +6822 ed49d107938a7fb5058d8e6f719b97fb +6938 a638345003ca5ee6ea677446193c4af2 +7085 1a5ed3ccde9927cb1f4dfc8b2b71f25f +7180 7275a16515755a6b789da7c2fdb2f096 +7290 aa916ee2a7b4f6aff0592309e890d6c0 +7390 040861544798bccfaebec94c719e2fa9 +7449 58ddd09159a92cd3fee0c241b8629a8c +7589 3244004b56a66402cde3c60a41d99dfc +7686 960d350f442e03aa745d4740b1dee8f1 +7713 e43df84b0b6a40d26a4e36bb371af6c1 +7844 b3ac24da8fdba43ba00ad2772cfeedd8 +7935 a7d07f684c0c18946695a5fa369139f0 +8059 81523df1da525bb0e5490aec44809552 +8153 a34469e694de9c3e3d7d2cb171df691a +8245 3bdcc5556a474a7a67be2134804987a8 +8310 10ed2c9f70315df533c94d5a79200452 +8432 3f7273962e617832452163b98058791a +8596 2eec8a128e031b31e890fd545aef9c20 +8647 1ae5260b3db571fcfdf36e17b9286d73 +8742 da8e83a01b7e17d4b28de7275b83ccbc +8856 426253c28af8a7364a0c4795355590f9 +8967 1cad1040530e52c64e4f5ba5bf640fdd +9057 7c265622653013e1bfa47dc4b1f3289f +9148 dd25afe1e73889c5810e2fa91c31ca6c +9275 5e8b0a1ecfde14c6fa8d4cba12aa8ac1 +9311 b1f7571deb95309671533b657b5392ab +9488 324bbfc66eaf7254c7b1d99b23cd33f0 +9518 5f514525711e5c42e067776a3139d011 +9696 10f135781fe4bfffc5b1feb18818e592 +9752 87c432d39312dcc756ea49ae3a63bfe1 +9887 2e8c5ee85ee68afdf25985e9b1c01bd6 +9903 4e4c6529cb69a85c095381d0991dedcf +10000 a849cf46ea623ee01daa24e77a05d671 +show status like '%sort%'; +Variable_name Value +Sort_merge_passes 0 +Sort_priority_queue_sorts 0 +Sort_range 0 +Sort_rows 10101 +Sort_scan 2 +set sort_buffer_size=default; +set @@RAND_SEED1= @save_rand_seed1; +set @@RAND_SEED2= @save_rand_seed2; +drop function f1; +drop function f2; +drop function f3; +drop table t1, t2, t3; diff --git a/mysql-test/main/order_by_pack_big.test b/mysql-test/main/order_by_pack_big.test new file mode 100644 index 00000000000..3840e023e2c --- /dev/null +++ b/mysql-test/main/order_by_pack_big.test @@ -0,0 +1,81 @@ +--source include/big_test.inc +--source include/have_sequence.inc +--source include/have_64bit.inc + +set @save_rand_seed1= @@RAND_SEED1; +set @save_rand_seed2= @@RAND_SEED2; +set @@RAND_SEED1=810763568, @@RAND_SEED2=600681772; + +create table t1(a int); +insert into t1 select seq from seq_1_to_10000 order by rand(); +delimiter |; + +--echo # +--echo # function f1 has parameters mean(peak) and std_dev (standard deviation) +--echo # for a normal distribution +--echo # + +CREATE FUNCTION f1(mean DOUBLE, std_dev DOUBLE) RETURNS DOUBLE +BEGIN + set @z= (rand() + rand() + rand() + rand() + rand() + rand() + + rand() + rand() + rand() + rand() + rand() + rand() - 6); + set @z= std_dev*@z + mean; + return @z; +END| + +CREATE function f2(len INT) RETURNS varchar(256) +BEGIN + DECLARE str VARCHAR(256) DEFAULT ''; + DECLARE x INT DEFAULT 0; + WHILE (len > 0 AND len < 256) DO + SET x =round(rand()*25); + SET str= CONCAT(str, CHAR(65 + x)); + SET len= len-1; + END WHILE; +RETURN str; +END| + +CREATE function f3(mean DOUBLE, std_dev DOUBLE, min_val INT) RETURNS INT +BEGIN + DECLARE r DOUBLE DEFAULT 0; + WHILE 1=1 DO + set r= f1(mean, std_dev); + IF (r >= min_val) THEN + RETURN round(r); + end if; + END WHILE; + RETURN 0; +END| + +delimiter ;| + + +create table t2 (id INT NOT NULL, a INT, b int); +insert into t2 select a, f3(12, 8.667, 0), f3(32, 16, 0) from t1; + +CREATE TABLE t3( + id INT NOT NULL, + names VARCHAR(64), + address VARCHAR(132), + PRIMARY KEY (id) +); + +insert into t3 select id, f2(a) , f2(b) from t2; + +set sort_buffer_size=262144*10; +flush status; +select id, + MD5(group_concat(substring(names,1,3), substring(address,1,3))) +FROM t3 +GROUP BY id DIV 100 +ORDER BY id; +show status like '%sort%'; +set sort_buffer_size=default; + +set @@RAND_SEED1= @save_rand_seed1; +set @@RAND_SEED2= @save_rand_seed2; + +drop function f1; +drop function f2; +drop function f3; +drop table t1, t2, t3;