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