mysql-test/main/order_by_pack_big.result

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(),seq;
#
# parameters:
# mean           mean for the column to be considered
# max_val        max_value for the column to be considered
#
# This function generate a sample of a normal distribution
# This function return a point
# of the normal distribution with a given mean.
#
CREATE FUNCTION
generate_normal_distribution_sample(mean DOUBLE, max_val DOUBLE)RETURNS DOUBLE
BEGIN
DECLARE z DOUBLE DEFAULT 0;
SET z= (rand() + rand() + rand() + rand() + rand() + rand())/6;
SET z= 2*(max_val-mean)*z;
SET z= z + mean - (max_val-mean);
return 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 generate_random_string(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
#
# This function generate a normal distribution sample in the range of
# [min_val, max_val]
#
CREATE FUNCTION
clipped_normal_distribution(mean DOUBLE, min_val DOUBLE, max_val DOUBLE)
RETURNS INT
BEGIN
DECLARE r DOUBLE DEFAULT 0;
WHILE 1=1 DO
set r= generate_normal_distribution_sample(mean, max_val);
IF (r >= min_val AND r <= max_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, clipped_normal_distribution(12, 0, 64),
clipped_normal_distribution(32, 0, 128)
from t1;
CREATE TABLE t3(
id INT NOT NULL,
names VARCHAR(64),
address VARCHAR(128),
PRIMARY KEY (id)
);
#
# table t3 stores random strings calculated from the length stored in
# table t2
#
insert into t3
select id, generate_random_string(a), generate_random_string(b) from t2;
#
# All records fit in memory
#
set sort_buffer_size=262144*10;
analyze format=json select id DIV 100 as x,
MD5(group_concat(substring(names,1,3), substring(address,1,3)
order by id))
FROM t3
GROUP BY x;
ANALYZE
{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": "REPLACED",
    "read_sorted_file": {
      "r_rows": 10000,
      "filesort": {
        "sort_key": "t3.`id` DIV 100",
        "r_loops": 1,
        "r_total_time_ms": "REPLACED",
        "r_used_priority_queue": false,
        "r_output_rows": 10000,
        "r_buffer_size": "REPLACED",
        "r_sort_mode": "sort_key,packed_addon_fields",
        "table": {
          "table_name": "t3",
          "access_type": "ALL",
          "r_loops": 1,
          "rows": 10000,
          "r_rows": 10000,
          "r_table_time_ms": "REPLACED",
          "r_other_time_ms": "REPLACED",
          "filtered": 100,
          "r_filtered": 100
        }
      }
    }
  }
}
flush status;
select id DIV 100 as x,
MD5(group_concat(substring(names,1,3), substring(address,1,3)
order by id))
FROM t3
GROUP BY x;
x	MD5(group_concat(substring(names,1,3), substring(address,1,3)
order by id))
0	3d24cb0237caf81aa74a2dddf367ac23
1	618f9b8b6cefaa268dcb5477eece5e90
2	fbfe93cc7713f852852f66e578d999aa
3	34802712af9c95664f41e789d19fb6e7
4	2d9a4d628013b45cea5301143ec559c5
5	324b8e682ad2233498f36979fddddf30
6	31ccc1ab69c00dcade71f7fda2fad599
7	e059e0279a5654a1c5f6596d220691bf
8	a21e5021cfb19be280f7fa2c2e70608e
9	6230b389a5bb0f9cd464b839e8824d0d
10	f2db66b6c4ad49e406f965a80ff5d224
11	75f39127d968cad28d30707889191d81
12	2e75191c0f32966a9e7675d0ddcf1c1c
13	808b4ecabb9cbb40ff3549c81b21d2f5
14	c7c0c0bbf0ab7558793a86eaca714a3f
15	3065d81035dc9c596bc235ab553f92de
16	13b1b17e93649fe49d6da356c2072c63
17	e3ce0af10608e6467ba4d38b135ff873
18	0f172b310ad772e13630b647e09ef79a
19	26bbfb77888fa151215fe8ff75e448e7
20	fcd6d5113e8178766dc370b470eb053a
21	d3860a17be6da02e17432f2ff80a261f
22	1d0cb7cd3f54c15bd41491e6fa449f12
23	15f95be2574a94677fa7e76ead23e025
24	924abdbb21c79cc7fd11d4d5b87ac339
25	7493978704b5223a4f5dc6d8beb51d90
26	f4ac4696b05577ba4a05334cd131cf94
27	cfd45d4915f342978fb6982a825f6cc2
28	2bd8981c6245c32ea1dc27ef394df94e
29	0e47838fe4be239939424fcef1289002
30	18e291b42da10b04434bdad010a5b66c
31	cbd1bf2554378ef7edd9087d4a8562d4
32	ddb1e7282bb637f7fe3aa3aed149b482
33	1963f2400c8870d67f99c391bc5040c2
34	d2b44d3c13a7a2c83de840892a370f47
35	7ff20517acf5ea901757262eae1edf5e
36	685fbfc47faad4243944d7331a2dee36
37	9d58b82b5f9d369a16c4f2bcc424b412
38	be15aa080dda65c9489892c34a330c30
39	4b52ddf7b599c9a00d094008a464c18c
40	441b03789e58aec883138d9c657a005e
41	e76cc7b1cadcb4f31e0232a0a8458620
42	3581a040fbf210f6942fe591621f0e41
43	acc552d3cba6a900054093c3c6876fc5
44	fd7fa7a0a4a58485a1aa30e804f024af
45	56afedd4f2e8101fbb4402051e9857e8
46	019e4178eccbdd84150f196668e40888
47	132c8e314dd7c9064e1cb4980856531c
48	dcc08184bf2f7cfb640af0c603ff7506
49	691b29183f64480003f8a1a74cd3ac13
50	105da23f1b4a63d755414beca9df20d5
51	30b2e973a0186721f9a7720be6be09e5
52	b307d14c06fd6f27173d683a5596b542
53	2125a788f107a4ba6a364984f7145156
54	aa0793982ae401cd2c1bea949d726ae1
55	e1797ba6b7ecf5f84cda2703f79c878c
56	78fc3f94127c0d5c6b3fa82ac3a8c167
57	b8c6d1e26cfd94aade5e32cf89e05da6
58	1e3481d6ff3b22f6ad739b93afe4c341
59	59e9d5be4a87ee707578a3a8e5d624e9
60	7a00e357826e950ffbe688c3f9a99a97
61	e5a8c0d404f6fb59d9e3dd88b13ba5a7
62	19d22c20fbf76b5b9ac746679ade26e7
63	c96f912f54b9b862532ce959398c76ca
64	86c167171b777a150356f1f083203d24
65	e1bca8d6cc1904a919cce1090e62f34d
66	f5dd3f6267376875239222dbefac87b1
67	d6a2aa26124dd42a9df9fa21b7eb8bce
68	ecd36ba9a56f89d4bf6fa83c2b800985
69	87b64475fd3f4db6dd68a45fb9a825dc
70	f5f7aa5569558b3e6556c72c9dfc429b
71	9e1cc67e0c13d00c771b6bed2fd566cf
72	3a6e7c6ce76a04fa2503d094121d84d5
73	5fd8ba2dd8d36814d06a1d278002e437
74	10db883700dde7289eb640df3a6d6676
75	3ebd59ed119d6dfda3eedc5bf05fd425
76	5008d3ce585f3b2dda2b313c57ebcb0a
77	ac7cee33e39827de02acedb7584acae5
78	3f440facefbd95f905942c75bfe60bbb
79	89639b3145c988c775d9d997c9d356ff
80	a6f8b29a83b2305188c241f2724ea998
81	d79f9bd47505cee69627e8044f7745c2
82	5f8d8b55bc9d5b44f817ada0b9bab2f7
83	411a99ef2e2af45640f0224274979fb5
84	60706cd1eb7dffae3d7a34133c5cb67e
85	272a5d09070b5a0569fb18e144100f8b
86	0f969fd16dd186e9eb099213a82fb119
87	6c48f63ec50e26ba42d8ca074daee9a6
88	83512539cc5a2561426564590d307a58
89	c57cf49bbeaa12aaba2106278681c9cd
90	d248507181561f0aba09182c4326fb11
91	a1e153dd8bb1334bccd73890fafc5ba9
92	d7a0ba2d1241a07abf3691074cecff76
93	466034794d577c41a9716bc9eb903ee2
94	b5e2158feb4290b04a7bdfdec0a17976
95	8a16a27008d033dd6e9074b042021e60
96	ccaa12e89f6de3058b3a2baddc4eff00
97	7fdbf02f369546d0fde2b7db85752e33
98	63b286bb777298397f3acf03fc3e9deb
99	b271f751a4952f86b002b9730bd02c8b
100	cbd5cef546acc0bfa33117d7c1ae58a8
show status like '%sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	10000
Sort_scan	1
set sort_buffer_size=default;
#
# Test for merge_many_buff
#
set sort_buffer_size=32768;
analyze format=json select id DIV 100 as x,
MD5(group_concat(substring(names,1,3), substring(address,1,3)
order by id))
FROM t3
GROUP BY x;
ANALYZE
{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": "REPLACED",
    "read_sorted_file": {
      "r_rows": 10000,
      "filesort": {
        "sort_key": "t3.`id` DIV 100",
        "r_loops": 1,
        "r_total_time_ms": "REPLACED",
        "r_used_priority_queue": false,
        "r_output_rows": 10000,
        "r_sort_passes": 4,
        "r_buffer_size": "REPLACED",
        "r_sort_mode": "sort_key,packed_addon_fields",
        "table": {
          "table_name": "t3",
          "access_type": "ALL",
          "r_loops": 1,
          "rows": 10000,
          "r_rows": 10000,
          "r_table_time_ms": "REPLACED",
          "r_other_time_ms": "REPLACED",
          "filtered": 100,
          "r_filtered": 100
        }
      }
    }
  }
}
flush status;
select id DIV 100 as x,
MD5(group_concat(substring(names,1,3), substring(address,1,3)
order by id))
FROM t3
GROUP BY x;
x	MD5(group_concat(substring(names,1,3), substring(address,1,3)
order by id))
0	3d24cb0237caf81aa74a2dddf367ac23
1	618f9b8b6cefaa268dcb5477eece5e90
2	fbfe93cc7713f852852f66e578d999aa
3	34802712af9c95664f41e789d19fb6e7
4	2d9a4d628013b45cea5301143ec559c5
5	324b8e682ad2233498f36979fddddf30
6	31ccc1ab69c00dcade71f7fda2fad599
7	e059e0279a5654a1c5f6596d220691bf
8	a21e5021cfb19be280f7fa2c2e70608e
9	6230b389a5bb0f9cd464b839e8824d0d
10	f2db66b6c4ad49e406f965a80ff5d224
11	75f39127d968cad28d30707889191d81
12	2e75191c0f32966a9e7675d0ddcf1c1c
13	808b4ecabb9cbb40ff3549c81b21d2f5
14	c7c0c0bbf0ab7558793a86eaca714a3f
15	3065d81035dc9c596bc235ab553f92de
16	13b1b17e93649fe49d6da356c2072c63
17	e3ce0af10608e6467ba4d38b135ff873
18	0f172b310ad772e13630b647e09ef79a
19	26bbfb77888fa151215fe8ff75e448e7
20	fcd6d5113e8178766dc370b470eb053a
21	d3860a17be6da02e17432f2ff80a261f
22	1d0cb7cd3f54c15bd41491e6fa449f12
23	15f95be2574a94677fa7e76ead23e025
24	924abdbb21c79cc7fd11d4d5b87ac339
25	7493978704b5223a4f5dc6d8beb51d90
26	f4ac4696b05577ba4a05334cd131cf94
27	cfd45d4915f342978fb6982a825f6cc2
28	2bd8981c6245c32ea1dc27ef394df94e
29	0e47838fe4be239939424fcef1289002
30	18e291b42da10b04434bdad010a5b66c
31	cbd1bf2554378ef7edd9087d4a8562d4
32	ddb1e7282bb637f7fe3aa3aed149b482
33	1963f2400c8870d67f99c391bc5040c2
34	d2b44d3c13a7a2c83de840892a370f47
35	7ff20517acf5ea901757262eae1edf5e
36	685fbfc47faad4243944d7331a2dee36
37	9d58b82b5f9d369a16c4f2bcc424b412
38	be15aa080dda65c9489892c34a330c30
39	4b52ddf7b599c9a00d094008a464c18c
40	441b03789e58aec883138d9c657a005e
41	e76cc7b1cadcb4f31e0232a0a8458620
42	3581a040fbf210f6942fe591621f0e41
43	acc552d3cba6a900054093c3c6876fc5
44	fd7fa7a0a4a58485a1aa30e804f024af
45	56afedd4f2e8101fbb4402051e9857e8
46	019e4178eccbdd84150f196668e40888
47	132c8e314dd7c9064e1cb4980856531c
48	dcc08184bf2f7cfb640af0c603ff7506
49	691b29183f64480003f8a1a74cd3ac13
50	105da23f1b4a63d755414beca9df20d5
51	30b2e973a0186721f9a7720be6be09e5
52	b307d14c06fd6f27173d683a5596b542
53	2125a788f107a4ba6a364984f7145156
54	aa0793982ae401cd2c1bea949d726ae1
55	e1797ba6b7ecf5f84cda2703f79c878c
56	78fc3f94127c0d5c6b3fa82ac3a8c167
57	b8c6d1e26cfd94aade5e32cf89e05da6
58	1e3481d6ff3b22f6ad739b93afe4c341
59	59e9d5be4a87ee707578a3a8e5d624e9
60	7a00e357826e950ffbe688c3f9a99a97
61	e5a8c0d404f6fb59d9e3dd88b13ba5a7
62	19d22c20fbf76b5b9ac746679ade26e7
63	c96f912f54b9b862532ce959398c76ca
64	86c167171b777a150356f1f083203d24
65	e1bca8d6cc1904a919cce1090e62f34d
66	f5dd3f6267376875239222dbefac87b1
67	d6a2aa26124dd42a9df9fa21b7eb8bce
68	ecd36ba9a56f89d4bf6fa83c2b800985
69	87b64475fd3f4db6dd68a45fb9a825dc
70	f5f7aa5569558b3e6556c72c9dfc429b
71	9e1cc67e0c13d00c771b6bed2fd566cf
72	3a6e7c6ce76a04fa2503d094121d84d5
73	5fd8ba2dd8d36814d06a1d278002e437
74	10db883700dde7289eb640df3a6d6676
75	3ebd59ed119d6dfda3eedc5bf05fd425
76	5008d3ce585f3b2dda2b313c57ebcb0a
77	ac7cee33e39827de02acedb7584acae5
78	3f440facefbd95f905942c75bfe60bbb
79	89639b3145c988c775d9d997c9d356ff
80	a6f8b29a83b2305188c241f2724ea998
81	d79f9bd47505cee69627e8044f7745c2
82	5f8d8b55bc9d5b44f817ada0b9bab2f7
83	411a99ef2e2af45640f0224274979fb5
84	60706cd1eb7dffae3d7a34133c5cb67e
85	272a5d09070b5a0569fb18e144100f8b
86	0f969fd16dd186e9eb099213a82fb119
87	6c48f63ec50e26ba42d8ca074daee9a6
88	83512539cc5a2561426564590d307a58
89	c57cf49bbeaa12aaba2106278681c9cd
90	d248507181561f0aba09182c4326fb11
91	a1e153dd8bb1334bccd73890fafc5ba9
92	d7a0ba2d1241a07abf3691074cecff76
93	466034794d577c41a9716bc9eb903ee2
94	b5e2158feb4290b04a7bdfdec0a17976
95	8a16a27008d033dd6e9074b042021e60
96	ccaa12e89f6de3058b3a2baddc4eff00
97	7fdbf02f369546d0fde2b7db85752e33
98	63b286bb777298397f3acf03fc3e9deb
99	b271f751a4952f86b002b9730bd02c8b
100	cbd5cef546acc0bfa33117d7c1ae58a8
show status like '%sort%';
Variable_name	Value
Sort_merge_passes	4
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	10000
Sort_scan	1
set sort_buffer_size=default;
#
# CASE #1 Packed sort keys with addon fields
#
ALTER TABLE t3 ADD INDEX idx(names, address);
set sort_buffer_size= 2097152;
ANALYZE FORMAT=JSON  SELECT id, names, address FROM t3 ORDER BY names, address;
ANALYZE
{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": "REPLACED",
    "read_sorted_file": {
      "r_rows": 10000,
      "filesort": {
        "sort_key": "t3.`names`, t3.address",
        "r_loops": 1,
        "r_total_time_ms": "REPLACED",
        "r_used_priority_queue": false,
        "r_output_rows": 10000,
        "r_buffer_size": "REPLACED",
        "r_sort_mode": "packed_sort_key,packed_addon_fields",
        "table": {
          "table_name": "t3",
          "access_type": "ALL",
          "r_loops": 1,
          "rows": 10000,
          "r_rows": 10000,
          "r_table_time_ms": "REPLACED",
          "r_other_time_ms": "REPLACED",
          "filtered": 100,
          "r_filtered": 100
        }
      }
    }
  }
}
flush status;
SELECT id, names, address INTO OUTFILE '$file1' FROM t3 ORDER BY names, address;
# Sort_merge_passes should be 0
show status like '%sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	10000
Sort_scan	1
SELECT id, names, address INTO OUTFILE '$file2' FROM t3 FORCE INDEX(idx) ORDER BY names, address;
#
# CASE #2 Packed sort keys and ROW_ID
#
set @save_max_length_for_sort_data=@@max_length_for_sort_data;
set max_length_for_sort_data= 300;
set sort_buffer_size= 1097152;
ANALYZE FORMAT=JSON  SELECT id, names, address FROM t3 ORDER BY names, address;
ANALYZE
{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": "REPLACED",
    "read_sorted_file": {
      "r_rows": 10000,
      "filesort": {
        "sort_key": "t3.`names`, t3.address",
        "r_loops": 1,
        "r_total_time_ms": "REPLACED",
        "r_used_priority_queue": false,
        "r_output_rows": 10000,
        "r_buffer_size": "REPLACED",
        "r_sort_mode": "packed_sort_key,rowid",
        "table": {
          "table_name": "t3",
          "access_type": "ALL",
          "r_loops": 1,
          "rows": 10000,
          "r_rows": 10000,
          "r_table_time_ms": "REPLACED",
          "r_other_time_ms": "REPLACED",
          "filtered": 100,
          "r_filtered": 100
        }
      }
    }
  }
}
flush status;
SELECT id, names, address INTO OUTFILE '$file1' FROM t3 ORDER BY names, address;
# Sort_merge_passes should be 0
show status like '%sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	10000
Sort_scan	1
set @@max_length_for_sort_data=@save_max_length_for_sort_data;
set @@sort_buffer_size=default;
set @@RAND_SEED1= @save_rand_seed1;
set @@RAND_SEED2= @save_rand_seed2;
drop function generate_normal_distribution_sample;
drop function generate_random_string;
drop function clipped_normal_distribution;
drop table t1, t2, t3;