summaryrefslogtreecommitdiff
path: root/mysql-test/main/order_by.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main/order_by.result')
-rw-r--r--mysql-test/main/order_by.result302
1 files changed, 299 insertions, 3 deletions
diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result
index a1b167d6189..0744b32139c 100644
--- a/mysql-test/main/order_by.result
+++ b/mysql-test/main/order_by.result
@@ -3471,14 +3471,13 @@ drop table t1,t2,t3,t4;
#
set @save_sql_mode= @@sql_mode;
set sql_mode= 'PAD_CHAR_TO_FULL_LENGTH';
-CREATE TABLE t1 ( a CHAR(255) charset utf8,
-b CHAR(255) charset utf8, c TEXT);
+CREATE TABLE t1 ( a CHAR(255), b CHAR(255), c TEXT);
INSERT INTO t1 VALUES
('1','a', 'a'), ('2','b', 'b'), ('3','c', 'c'), ('4','d','d'),
('5','e', 'e'), ('6','f', 'f'), ('7','g','g'), ('8','h','h'),
('9','i', 'i'), ('10','j','j'), ('11','k','k'), ('12','l','l'),
('13','m','m'), ('14','n','n'), ('15','o','o');
-set sort_buffer_size=517*30;
+set sort_buffer_size=524*15;
select c from t1 order by a,b;
c
a
@@ -3496,5 +3495,302 @@ f
g
h
i
+set sort_buffer_size= default;
set sql_mode= @save_sql_mode;
drop table t1;
+#
+# MDEV-21580: Allow packed sort keys in sort buffer
+#
+#
+# This example should not pack sort keys
+# all fields are fixed-size fields in the ORDER BY clause
+#
+create table t1 (a bigint, b bigint, c bigint);
+insert into t1 select seq, seq, seq from seq_1_to_100;
+# in r_sort_mode it should show sort_key and not packed_sort_key
+ANALYZE FORMAT=JSON select * from t1 order by a,b,c;
+ANALYZE
+{
+ "query_block": {
+ "select_id": 1,
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "read_sorted_file": {
+ "r_rows": 100,
+ "filesort": {
+ "sort_key": "t1.a, t1.b, t1.c",
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "r_used_priority_queue": false,
+ "r_output_rows": 100,
+ "r_buffer_size": "REPLACED",
+ "r_sort_mode": "sort_key,packed_addon_fields",
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "r_loops": 1,
+ "rows": 100,
+ "r_rows": 100,
+ "r_table_time_ms": "REPLACED",
+ "r_other_time_ms": "REPLACED",
+ "filtered": 100,
+ "r_filtered": 100
+ }
+ }
+ }
+ }
+}
+select * from t1 order by a,b,c;
+a b c
+1 1 1
+2 2 2
+3 3 3
+4 4 4
+5 5 5
+6 6 6
+7 7 7
+8 8 8
+9 9 9
+10 10 10
+11 11 11
+12 12 12
+13 13 13
+14 14 14
+15 15 15
+16 16 16
+17 17 17
+18 18 18
+19 19 19
+20 20 20
+21 21 21
+22 22 22
+23 23 23
+24 24 24
+25 25 25
+26 26 26
+27 27 27
+28 28 28
+29 29 29
+30 30 30
+31 31 31
+32 32 32
+33 33 33
+34 34 34
+35 35 35
+36 36 36
+37 37 37
+38 38 38
+39 39 39
+40 40 40
+41 41 41
+42 42 42
+43 43 43
+44 44 44
+45 45 45
+46 46 46
+47 47 47
+48 48 48
+49 49 49
+50 50 50
+51 51 51
+52 52 52
+53 53 53
+54 54 54
+55 55 55
+56 56 56
+57 57 57
+58 58 58
+59 59 59
+60 60 60
+61 61 61
+62 62 62
+63 63 63
+64 64 64
+65 65 65
+66 66 66
+67 67 67
+68 68 68
+69 69 69
+70 70 70
+71 71 71
+72 72 72
+73 73 73
+74 74 74
+75 75 75
+76 76 76
+77 77 77
+78 78 78
+79 79 79
+80 80 80
+81 81 81
+82 82 82
+83 83 83
+84 84 84
+85 85 85
+86 86 86
+87 87 87
+88 88 88
+89 89 89
+90 90 90
+91 91 91
+92 92 92
+93 93 93
+94 94 94
+95 95 95
+96 96 96
+97 97 97
+98 98 98
+99 99 99
+100 100 100
+drop table t1;
+#
+# Test with Binary columns (using suffix length to determine ordering)
+# Should show packed_sortkey in the r_sort_mode
+#
+create table t1 (a int, b blob);
+set @save_max_sort_length= @@max_sort_length;
+insert into t1 select 1, CONCAT(repeat('a', @save_max_sort_length), 'A');
+insert into t1 select 2, CONCAT(repeat('a', @save_max_sort_length), 'AB');
+insert into t1 select 3, CONCAT(repeat('a', @save_max_sort_length), 'ABE');
+insert into t1 select 4, CONCAT(repeat('a', @save_max_sort_length), 'APBX');
+insert into t1 select 5, CONCAT(repeat('a', @save_max_sort_length), 'ABAAX');
+show variables like '%sort_buffer_size';
+Variable_name Value
+aria_sort_buffer_size 268434432
+myisam_sort_buffer_size 134216704
+sort_buffer_size 262144
+select a, substr(b, @save_max_sort_length+1) from t1 order by b desc;
+a substr(b, @save_max_sort_length+1)
+5 ABAAX
+4 APBX
+3 ABE
+2 AB
+1 A
+analyze format=json
+select a, substr(b, @save_max_sort_length+1) from t1 order by b desc;
+ANALYZE
+{
+ "query_block": {
+ "select_id": 1,
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "read_sorted_file": {
+ "r_rows": 5,
+ "filesort": {
+ "sort_key": "t1.b desc",
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "r_used_priority_queue": false,
+ "r_output_rows": 5,
+ "r_buffer_size": "REPLACED",
+ "r_sort_mode": "packed_sort_key,rowid",
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "r_loops": 1,
+ "rows": 5,
+ "r_rows": 5,
+ "r_table_time_ms": "REPLACED",
+ "r_other_time_ms": "REPLACED",
+ "filtered": 100,
+ "r_filtered": 100
+ }
+ }
+ }
+ }
+}
+drop table t1;
+create table t1(a varchar(255) charset utf8, b int, c decimal);
+insert into t1 values ('abc', 1, 1) , ('bcd', 2, 2), ('cde',3, 3);
+insert into t1 values ('def', 4, 4) , ('efg', 5, 5), ('fgh', 6, 6);
+#
+# Should show packed_sortkey in the r_sort_mode
+#
+ANALYZE FORMAT=JSON select a, b, c from t1 order by a, b;
+ANALYZE
+{
+ "query_block": {
+ "select_id": 1,
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "read_sorted_file": {
+ "r_rows": 6,
+ "filesort": {
+ "sort_key": "t1.a, t1.b",
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "r_used_priority_queue": false,
+ "r_output_rows": 6,
+ "r_buffer_size": "REPLACED",
+ "r_sort_mode": "packed_sort_key,rowid",
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "r_loops": 1,
+ "rows": 6,
+ "r_rows": 6,
+ "r_table_time_ms": "REPLACED",
+ "r_other_time_ms": "REPLACED",
+ "filtered": 100,
+ "r_filtered": 100
+ }
+ }
+ }
+ }
+}
+select a, b, c from t1 order by a, b;
+a b c
+abc 1 1
+bcd 2 2
+cde 3 3
+def 4 4
+efg 5 5
+fgh 6 6
+set @save_max_sort_length= @@max_sort_length;
+set max_sort_length=5;
+#
+# should show sortkey in r_sort_mode as the collation is complex and
+# truncation is not possible
+#
+ANALYZE FORMAT=JSON select a, b, c from t1 order by a, b;
+ANALYZE
+{
+ "query_block": {
+ "select_id": 1,
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "read_sorted_file": {
+ "r_rows": 6,
+ "filesort": {
+ "sort_key": "t1.a, t1.b",
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "r_used_priority_queue": false,
+ "r_output_rows": 6,
+ "r_buffer_size": "REPLACED",
+ "r_sort_mode": "sort_key,packed_addon_fields",
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "r_loops": 1,
+ "rows": 6,
+ "r_rows": 6,
+ "r_table_time_ms": "REPLACED",
+ "r_other_time_ms": "REPLACED",
+ "filtered": 100,
+ "r_filtered": 100
+ }
+ }
+ }
+ }
+}
+select a, b, c from t1 order by a, b;
+a b c
+abc 1 1
+bcd 2 2
+cde 3 3
+def 4 4
+efg 5 5
+fgh 6 6
+set max_sort_length= @save_max_sort_length;
+drop table t1;
View Lorry Controller Status