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 > comm...@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 _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
