Hi Varun, As disucssed: - Please fix function names - Please fix the comments to explain what the function is generating - Please don't use session variables. Ok to push after this is addressed On Fri, Jan 03, 2020 at 02:28:25AM +0530, Varun wrote:

revision-id: afdd6191d5dcb004ec9ac0b908871ad8a370da34 (mariadb-10.4.11-18-gafdd6191d5d) parent(s): 59d4f2a373a7960a533e653877ab69a97e91444a author: Varun Gupta committer: Varun Gupta timestamp: 2020-01-03 02:26:58 +0530 message:

Big Test added for sorting

--- mysql-test/main/order_by_pack_big.result | 194 +++++++++++++++++++++++++++++++ mysql-test/main/order_by_pack_big.test | 107 +++++++++++++++++ 2 files changed, 301 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..66aad449c38 --- /dev/null +++ b/mysql-test/main/order_by_pack_big.result @@ -0,0 +1,194 @@ +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(); +# +# parameters: +# mean mean for the column to be considered +# max_val max_value for the column to be considered +# +# This function also calculates the standard deviation +# which is required to convert standard normal distribution +# to normal distribution

I cannot make any sense of this. The intent of this function is to generate random numbers with the mean of `mean` and standard deviation of ... (max_val - mean) /6 ?

+# +CREATE FUNCTION f1(mean DOUBLE, max_val DOUBLE) RETURNS DOUBLE +BEGIN +DECLARE std_dev DOUBLE DEFAULT 0; +SET @z= (rand() + rand() + rand() + rand() + rand() + rand() + +rand() + rand() + rand() + rand() + rand() + rand() - 6);

Here we get mean=1, stddev=1.

+SET std_dev= (max_val - mean)/6;

+SET @z= std_dev*@z + mean;

ok so we have generated a random number with the mean 'mean' and std_dev as shown above.

+return @z;

Please do not use session variables for function-local computations. The variables are in user-session scope, that is, this function will eventually cause a surprise by overwriting user's @z.

+END| +# +# parameters: +# len length of the random string to be generated +# +# This function generates a random string for the length passed +# as an argument with characters in the range of [A,Z] +# +CREATE function f2(len INT) RETURNS varchar(128) +BEGIN +DECLARE str VARCHAR(256) DEFAULT ''; +DECLARE x INT DEFAULT 0; +WHILE (len > 0) DO +SET x =round(rand()*25); +SET str= CONCAT(str, CHAR(65 + x)); +SET len= len-1; +END WHILE; +RETURN str; +END| +# +# parameters: +# mean mean for the column to be considered +# min_val min_value for the column to be considered +# max_val max_value for the column to be considered +# +CREATE function f3(mean DOUBLE, min_val DOUBLE, max_val DOUBLE) RETURNS INT +BEGIN +DECLARE r DOUBLE DEFAULT 0; +WHILE 1=1 DO +set r= f1(mean, max_val); +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, 0, 64), f3(32, 0, 128) from t1; +CREATE TABLE t3( +id INT NOT NULL, +names VARCHAR(64), +address VARCHAR(128), +PRIMARY KEY (id) +); +# +# table t3 stores string calculated from the length stored in +# table t2 +# +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 351239227a41de08388ea422f928cc29 +149 67299eb34e363edabe31576890087e97 +232 7ac931ef07a24ebe1293093ec6fa8f3d +311 8625cade62c8b45c63d8978f8968ebb5 +430 362761f4180d40372667c8dd7cdcc436 +502 5380af74db071a35fb1d2491368e641b +665 d3e3e2a2cb4e0de17c4f12e5b7745802 +719 5d93632d4c30ec99802f7be7582f4f2d +883 27747ef400898c7eeeba3ebea8c42fb1 +942 d1e4ae80ca57b99ee49201b658a7b040 +1007 fceb25160237c8a3c262735b81d027ac +1134 cfa9c86c901aaace0e9e94dc6a837468 +1226 4fb8e9ab9acdd251e7bc51db9e4d2f3b +1367 e17fa4948562b3411f0b64084de0c605 +1486 85dd0f507e660600820f106dc8887edf +1502 5bf6015f936908eed31f5769ad4b0d72 +1674 01f6c54ea21c4acd26f6c1df6abd793c +1781 6d38cd061db1f30e2e37cd7d9ac600ad +1803 2ac17a3853677ffde105735c92a9f2ea +1969 e1e2e39e9d26baebe23232a429783feb +2087 af67a443d21665bbb425a783f4e434fa +2111 1906e379e9ae0b3b580fa134d2a5a146 +2268 2afaf9091f92fb8e409142552724a85e +2328 5a8fd5d24c9f7c7bcfbcde84a5b0cfe2 +2416 d9a69c46523f71fce606c6d6c92ca516 +2599 55a436a6fb744eefd6878473c34fa41e +2602 98317430fe15bcc9bb5968b5052c9106 +2777 8b5c30ae940ff7f31839309b535e3a15 +2858 0db2f3bcb138c2f91445c4205374a3b4 +2922 fed051b9185591bc0aaebd1e1471944d +3027 f0cff102210e7fa32db222ac3444e4cf +3131 c2f3f5a92d4c2b45cadd9c8cbf04d1be +3220 8db6dfcca0461654dcb963fe2e1d8f41 +3331 42031ed42643c755dfd936eb96b28ed5 +3452 09f418c82012ff6789a6429be0c10f98 +3519 7d26aac1dbbcff68b528b8c1d80a2c7b +3680 0ff5b4295168db71b997f6001bba7015 +3799 3460724c5fc7271a0a3189bf275b9b89 +3876 13f21a3dfc2bad54c12fffae7cdf8326 +3937 a240132ca8905b8165bf6e16fa6e7b3a +4029 5fabf8408215c5bf896eda4e173a8a98 +4158 c7829b1eeda97ff8c9b2a24ead3f6df6 +4291 0d24e7e9da38dc44ffb43976560c4730 +4355 bc804d019300149cb891b8fe8afbe445 +4461 bb5a658677030b64ca3fd095f8a054fd +4544 e04f6bfc8dcb8d8014ce39e1b707ed0b +4646 06af0dd12faee32a07e785c4d56856b8 +4714 d0c99cc1aead7d06e5323867867d4b00 +4848 208d1ca5ade34053d92f96937f76380b +4935 3b62eb6129970e714bdc74565183e183 +5014 9e19c021b79e32ea6fceb7ced26a3a68 +5184 41fa16423738302b2fdd6cda8e52f2c9 +5219 3ab8090c30c0206c1e30ce6cd76cb617 +5349 bd3e73dd60fbd1819aa468d3d0e6999c +5400 80dc0e71fcbd2abfec9b585cc04a7545 +5507 96ed16d40a9e6a1231bc88bd6b3f9c3e +5672 764347fc7e265a1478c890fa38d8c892 +5725 6767ae39fec9b789b8b542080162af46 +5849 41df99caa43ee3f3b162c66c3eb61a44 +5941 0725e779ca53da50461ef0d3758d819d +6064 06d28bf28138d5726ab61e51a2e87edc +6135 b2567b682dd449e358e11c4fb7f7bb72 +6289 8aa8131d32436add670fed1e7628b297 +6329 127b1600d2a9f857501f0263536d200b +6404 266b87348831b9cc5b570e2b16c3006a +6580 f70b98a00f6adb163c0f89bb6bb6d1ad +6653 a13a591ba0c88985040c51fda2af7a72 +6773 ee4306ceb6a3266617707a1ca637c328 +6822 a8c368cc486b650f6254614535b5b051 +6938 a7c160cec86018b78942b60b62b5b7fd +7085 eb360d65bc8080cd5879fb8ddee830cd +7180 c54bebbb560d9e9196a6f986022d4253 +7290 4d1820f520483d785ba4e1c89b938f20 +7390 0d3cd69b8e02fde232df802f3e9fc7a2 +7449 7328ee3fe9383f891b9af5244c63a0e0 +7589 467169481de385077ebcad083dd36b0b +7686 ae22b711e21ba0e0fe20ba713408263a +7713 e20cd84a1ee8bd1d743947c9c381731d +7844 bc3f0534e283616d6a4dbb0902c03fa6 +7935 146ea350d8f1cfef44aa7470cf9e02f8 +8059 3a88201a77ccbd8ce651eeb555c29fe5 +8153 9db1e67ef602768b7182401905bacc26 +8245 c5e6c51763b0bbc1a7e72fe1615f9440 +8310 ee37ab957141c733350e21a6ed2176f5 +8432 34ae43ecbfa6c96e12a8c315937d511f +8596 710f7c0bc4fadbdd859352b584b19d66 +8647 df6f807e47599027749e1b09b04f6083 +8742 5efcaddfa993721074a1691947ca611e +8856 40ad2459d26129770ac6ac2da757ad7e +8967 344f6b2c8242b9b3bbd09898a80ba4ee +9057 3084c365110820be5bbfc721f4b2f37d +9148 13b2a5aa09a1f107f656e848a963e8ea +9275 908187dba9416102a566b955b29f709e +9311 d6c8096f5763c6ebdaccb3e2cc3ae686 +9488 62deb4d1a8900ea7cd7daa1909917490 +9518 730ecae84924d86922c82152c191d0f6 +9696 0a15d3446ba3d4b7ca8224633fbab666 +9752 a74f840a4e599466799d4e0879533da0 +9887 a7c29b0e5edfcd20572e0fda12a9e9aa +9903 e89c3ab708646a5d73683ea68c4e366a +10000 9cc0d2b033602eaea73fa9b2201b01b6 +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..021edfee13f --- /dev/null +++ b/mysql-test/main/order_by_pack_big.test @@ -0,0 +1,107 @@ +--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 # parameters: +--echo # mean mean for the column to be considered +--echo # max_val max_value for the column to be considered +--echo # +--echo # This function also calculates the standard deviation +--echo # which is required to convert standard normal distribution +--echo # to normal distribution +--echo # + +CREATE FUNCTION f1(mean DOUBLE, max_val DOUBLE) RETURNS DOUBLE +BEGIN + DECLARE std_dev DOUBLE DEFAULT 0; + SET @z= (rand() + rand() + rand() + rand() + rand() + rand() + + rand() + rand() + rand() + rand() + rand() + rand() - 6); + SET std_dev= (max_val - mean)/6; + SET @z= std_dev*@z + mean; + return @z; +END| + +--echo # +--echo # parameters: +--echo # len length of the random string to be generated +--echo # +--echo # This function generates a random string for the length passed +--echo # as an argument with characters in the range of [A,Z] +--echo # + +CREATE function f2(len INT) RETURNS varchar(128) +BEGIN + DECLARE str VARCHAR(256) DEFAULT ''; + DECLARE x INT DEFAULT 0; + WHILE (len > 0) DO + SET x =round(rand()*25); + SET str= CONCAT(str, CHAR(65 + x)); + SET len= len-1; + END WHILE; +RETURN str; +END| + +--echo # +--echo # parameters: +--echo # mean mean for the column to be considered +--echo # min_val min_value for the column to be considered +--echo # max_val max_value for the column to be considered +--echo # + +CREATE function f3(mean DOUBLE, min_val DOUBLE, max_val DOUBLE) RETURNS INT +BEGIN + DECLARE r DOUBLE DEFAULT 0; + WHILE 1=1 DO + set r= f1(mean, max_val); + 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, 0, 64), f3(32, 0, 128) from t1; + +CREATE TABLE t3( + id INT NOT NULL, + names VARCHAR(64), + address VARCHAR(128), + PRIMARY KEY (id) +); + +--echo # +--echo # table t3 stores string calculated from the length stored in +--echo # table t2 +--echo # + +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; _______________________________________________ commits mailing list commits@mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

-- BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog