1--source include/big_test.inc
2--source include/have_sequence.inc
3--source include/have_64bit.inc
4
5set @save_rand_seed1=  @@RAND_SEED1;
6set @save_rand_seed2=  @@RAND_SEED2;
7set @@RAND_SEED1=810763568, @@RAND_SEED2=600681772;
8
9create table t1(a int);
10insert into t1 select seq from seq_1_to_10000 order by rand(),seq;
11delimiter |;
12
13--echo #
14--echo # parameters:
15--echo # mean           mean for the column to be considered
16--echo # max_val        max_value for the column to be considered
17--echo #
18--echo # This function generate a sample of a normal distribution
19--echo # This function return a point
20--echo # of the normal distribution with a given mean.
21--echo #
22
23CREATE FUNCTION
24generate_normal_distribution_sample(mean DOUBLE, max_val DOUBLE)RETURNS DOUBLE
25BEGIN
26  DECLARE z DOUBLE DEFAULT 0;
27  SET z= (rand() + rand() + rand() + rand() + rand() + rand())/6;
28  SET z= 2*(max_val-mean)*z;
29  SET z= z + mean - (max_val-mean);
30  return z;
31END|
32
33--echo #
34--echo # parameters:
35--echo # len      length of the random string to be generated
36--echo #
37--echo # This function generates a random string for the length passed
38--echo # as an argument with characters in the range of [A,Z]
39--echo #
40
41CREATE FUNCTION generate_random_string(len INT) RETURNS varchar(128)
42BEGIN
43  DECLARE str VARCHAR(256) DEFAULT '';
44  DECLARE x INT DEFAULT 0;
45  WHILE (len > 0) DO
46    SET x =round(rand()*25);
47    SET str= CONCAT(str, CHAR(65 + x));
48    SET len= len-1;
49  END WHILE;
50RETURN str;
51END|
52
53--echo #
54--echo # parameters:
55--echo # mean      mean for the column to be considered
56--echo # min_val   min_value for the column to be considered
57--echo # max_val   max_value for the column to be considered
58--echo #
59--echo # This function generate a normal distribution sample in the range of
60--echo # [min_val, max_val]
61--echo #
62
63CREATE FUNCTION
64clipped_normal_distribution(mean DOUBLE, min_val DOUBLE, max_val DOUBLE)
65RETURNS INT
66BEGIN
67  DECLARE r DOUBLE DEFAULT 0;
68  WHILE 1=1 DO
69    set r= generate_normal_distribution_sample(mean, max_val);
70    IF (r >= min_val AND r <= max_val) THEN
71      RETURN round(r);
72    end if;
73  END WHILE;
74 RETURN 0;
75END|
76
77delimiter ;|
78
79create table t2 (id INT NOT NULL, a INT, b int);
80insert into t2
81select a, clipped_normal_distribution(12, 0, 64),
82       clipped_normal_distribution(32, 0, 128)
83from t1;
84
85CREATE TABLE t3(
86  id INT NOT NULL,
87  names VARCHAR(64),
88  address VARCHAR(128),
89  PRIMARY KEY (id)
90);
91
92--echo #
93--echo # table t3 stores random strings calculated from the length stored in
94--echo # table t2
95--echo #
96
97insert into t3
98select id, generate_random_string(a), generate_random_string(b) from t2;
99
100
101let $query= select id DIV 100 as x,
102            MD5(group_concat(substring(names,1,3), substring(address,1,3)
103                order by id))
104            FROM t3
105            GROUP BY x;
106
107--echo #
108--echo # All records fit in memory
109--echo #
110
111set sort_buffer_size=262144*10;
112--source include/analyze-format.inc
113eval analyze format=json $query;
114flush status;
115eval $query;
116show status like '%sort%';
117set sort_buffer_size=default;
118
119--echo #
120--echo # Test for merge_many_buff
121--echo #
122
123set sort_buffer_size=32768;
124--source include/analyze-format.inc
125eval analyze format=json $query;
126flush status;
127eval $query;
128show status like '%sort%';
129set sort_buffer_size=default;
130
131--echo #
132--echo # CASE #1 Packed sort keys with addon fields
133--echo #
134
135ALTER TABLE t3 ADD INDEX idx(names, address);
136
137let $file1 = `SELECT CONCAT(@@datadir, "t1.txt")`;
138let $file2 = `SELECT CONCAT(@@datadir, "t2.txt")`;
139
140set sort_buffer_size= 2097152;
141--source include/analyze-format.inc
142eval ANALYZE FORMAT=JSON  SELECT id, names, address FROM t3 ORDER BY names, address;
143flush status;
144evalp SELECT id, names, address INTO OUTFILE '$file1' FROM t3 ORDER BY names, address;
145
146--echo # Sort_merge_passes should be 0
147show status like '%sort%';
148
149evalp SELECT id, names, address INTO OUTFILE '$file2' FROM t3 FORCE INDEX(idx) ORDER BY names, address;
150
151diff_files $file1 $file2;
152
153--remove_file $file1
154
155--echo #
156--echo # CASE #2 Packed sort keys and ROW_ID
157--echo #
158
159set @save_max_length_for_sort_data=@@max_length_for_sort_data;
160set max_length_for_sort_data= 300;
161
162set sort_buffer_size= 1097152;
163--source include/analyze-format.inc
164eval ANALYZE FORMAT=JSON  SELECT id, names, address FROM t3 ORDER BY names, address;
165flush status;
166evalp SELECT id, names, address INTO OUTFILE '$file1' FROM t3 ORDER BY names, address;
167
168--echo # Sort_merge_passes should be 0
169show status like '%sort%';
170
171diff_files $file1 $file2;
172
173--remove_file $file1
174--remove_file $file2
175
176set @@max_length_for_sort_data=@save_max_length_for_sort_data;
177set @@sort_buffer_size=default;
178
179set @@RAND_SEED1= @save_rand_seed1;
180set @@RAND_SEED2= @save_rand_seed2;
181
182drop function generate_normal_distribution_sample;
183drop function generate_random_string;
184drop function clipped_normal_distribution;
185drop table t1, t2, t3;
186