← Back to team overview

maria-developers team mailing list archive

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