maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #12054
Re: [Commits] afdd6191d5d: Big Test added for sorting
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@xxxxxxxxxxx
> 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
