1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
|
# 2023-10-18
#
# The author disclaims copyright to this source code. In place of
# a legal notice, here is a blessing:
#
# May you do good and not evil.
# May you find forgiveness for yourself and forgive others.
# May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements tests for ORDER BY on aggregate functions.
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
do_execsql_test aggorderby-1.1 {
CREATE TABLE t1(a TEXT,b INT,c INT,d INT);
WITH RECURSIVE c(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM c WHERE x<9)
INSERT INTO t1(a,b,c,d) SELECT printf('%d',(x*7)%10),1,x,10-x FROM c;
INSERT INTO t1(a,b,c,d) SELECT a, 2, c, 10-d FROM t1;
CREATE INDEX t1b ON t1(b);
}
do_catchsql_test aggorderby-1.2 {
SELECT b, group_concat(a ORDER BY max(d)) FROM t1 GROUP BY b;
} {1 {misuse of aggregate function max()}}
do_catchsql_test aggorderby-1.3 {
SELECT abs(a ORDER BY max(d)) FROM t1;
} {1 {ORDER BY may not be used with non-aggregate abs()}}
do_execsql_test aggorderby-2.0 {
SELECT group_concat(a ORDER BY a) FROM t1 WHERE b=1;
} {0,1,2,3,4,5,6,7,8,9}
do_execsql_test aggorderby-2.1 {
SELECT group_concat(a ORDER BY c) FROM t1 WHERE b=1;
} {0,7,4,1,8,5,2,9,6,3}
do_execsql_test aggorderby-2.2 {
SELECT group_concat(a ORDER BY b, d) FROM t1;
} {3,6,9,2,5,8,1,4,7,0,0,7,4,1,8,5,2,9,6,3}
do_execsql_test aggorderby-2.3 {
SELECT string_agg(a, ',' ORDER BY b DESC, d) FROM t1;
} {0,7,4,1,8,5,2,9,6,3,3,6,9,2,5,8,1,4,7,0}
do_execsql_test aggorderby-2.4 {
SELECT b, group_concat(a ORDER BY d) FROM t1 GROUP BY b ORDER BY b;
} {1 3,6,9,2,5,8,1,4,7,0 2 0,7,4,1,8,5,2,9,6,3}
do_execsql_test aggorderby-3.0 {
SELECT group_concat(DISTINCT a ORDER BY a) FROM t1;
} {0,1,2,3,4,5,6,7,8,9}
do_execsql_test aggorderby-3.1 {
SELECT group_concat(DISTINCT a ORDER BY c) FROM t1;
} {0,7,4,1,8,5,2,9,6,3}
do_execsql_test aggorderby-4.0 {
SELECT count(ORDER BY a) FROM t1;
} 20
do_execsql_test aggorderby-4.1 {
SELECT c, max(a ORDER BY a) FROM t1;
} {7 9}
do_execsql_test aggorderby-5.0 {
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t3;
CREATE TABLE t1(a TEXT); INSERT INTO t1 VALUES('aaa'),('bbb');
CREATE TABLE t3(d TEXT); INSERT INTO t3 VALUES('/'),('-');
SELECT (SELECT string_agg(a,d) FROM t3) FROM t1;
} {aaa-aaa bbb-bbb}
do_execsql_test aggorderby-5.1 {
SELECT (SELECT group_concat(a,d ORDER BY d) FROM t3) FROM t1;
} {aaa/aaa bbb/bbb}
do_execsql_test aggorderby-5.2 {
SELECT (SELECT string_agg(a,d ORDER BY d DESC) FROM t3) FROM t1;
} {aaa-aaa bbb-bbb}
do_execsql_test aggorderby-5.3 {
SELECT (SELECT string_agg(a,'#' ORDER BY d) FROM t3) FROM t1;
} {aaa#aaa bbb#bbb}
# COLLATE works on the ORDER BY.
#
do_execsql_test aggorderby-6.0 {
WITH c(x) AS (VALUES('abc'),('DEF'),('xyz'),('ABC'),('XYZ'))
SELECT string_agg(x,',' ORDER BY x COLLATE nocase),
string_agg(x,',' ORDER BY x) FROM c;
} {abc,ABC,DEF,xyz,XYZ ABC,DEF,XYZ,abc,xyz}
do_execsql_test aggorderby-6.1 {
WITH c(x,y) AS (VALUES(1,'a'),(2,'B'),(3,'c'),(4,'D'))
SELECT group_concat(x ORDER BY y COLLATE nocase),
group_concat(x ORDER BY y COLLATE binary) FROM c;
} {1,2,3,4 2,4,1,3}
# NULLS FIRST and NULLS LAST work on the ORDER BY
#
do_execsql_test aggorderby-7.0 {
WITH c(x) AS (VALUES(1),(NULL),(2.5),(NULL),('three'))
SELECT json_group_array(x ORDER BY x NULLS FIRST),
json_group_array(x ORDER BY x NULLS LAST) FROM c;
} {[null,null,1,2.5,"three"] [1,2.5,"three",null,null]}
do_execsql_test aggorderby-7.1 {
WITH c(x,y) AS (VALUES(1,9),(2,null),(3,5),(4,null),(5,1))
SELECT json_group_array(x ORDER BY y NULLS FIRST, x),
json_group_array(x ORDER BY y NULLS LAST, x) FROM c;
} {[2,4,5,3,1] [5,3,1,2,4]}
# The DISTINCT only applies to the function arguments, not to the
# ORDER BY arguments.
#
do_execsql_test aggorderby-8.0 {
WITH c(x,y,z) AS (VALUES('a',4,5),('b',3,6),('c',2,7),('c',1,8))
SELECT group_concat(DISTINCT x ORDER BY y, z) FROM c;
} {c,b,a}
do_execsql_test aggorderby-8.1 {
WITH c(x,y,z) AS (VALUES('a',4,5),('b',3,6),('b',2,7),('c',1,8))
SELECT group_concat(DISTINCT x ORDER BY y, z) FROM c;
} {c,b,a}
do_execsql_test aggorderby-8.2 {
WITH c(x,y) AS (VALUES(1,1),(2,2),(3,3),(3,4),(3,5),(3,6))
SELECT sum(DISTINCT x ORDER BY y) FROM c;
} 6
# Subtype information is transfered through the sorter for aggregates
# that make use of subtype info.
#
do_execsql_test aggorderby-9.0 {
WITH c(x,y) AS (VALUES
('{a:3}', 3),
('[1,1]', 1),
('[4,4]', 4),
('{x:2}', 2))
SELECT json_group_array(json(x) ORDER BY y) FROM c;
} {{[[1,1],{"x":2},{"a":3},[4,4]]}}
do_execsql_test aggorderby-9.1 {
WITH c(x,y) AS (VALUES
('[4,4]', 4),
('{a:3}', 3),
('[4,4]', 4),
('[1,1]', 1),
('[4,4]', 4),
('{x:2}', 2))
SELECT json_group_array(DISTINCT json(x) ORDER BY y) FROM c;
} {{[[1,1],{"x":2},{"a":3},[4,4]]}}
do_execsql_test aggorderby-9.2 {
WITH c(x,y) AS (VALUES
('{a:3}', 3),
('[1,1]', 1),
('[4,4]', 4),
('{x:2}', 2))
SELECT json_group_array(json(x) ORDER BY json(x)) FROM c;
} {{[[1,1],[4,4],{"a":3},{"x":2}]}}
do_execsql_test aggorderby-9.3 {
WITH c(x,y) AS (VALUES
('[4,4]', 4),
('{a:3}', 3),
('[4,4]', 4),
('[1,1]', 1),
('[4,4]', 4),
('{x:2}', 2))
SELECT json_group_array(DISTINCT json(x) ORDER BY json(x)) FROM c;
} {{[[1,1],[4,4],{"a":3},{"x":2}]}}
finish_test
|
