Debian Codegraph @

test/select5.test

raw
1 # 2001 September 15
2 #
3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
5 #
6 # May you do good and not evil.
7 # May you find forgiveness for yourself and forgive others.
8 # May you share freely, never taking more than you give.
9 #
10 #***********************************************************************
11 # This file implements regression tests for SQLite library. The
12 # focus of this file is testing aggregate functions and the
13 # GROUP BY and HAVING clauses of SELECT statements.
14 #
15
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
18
19 # Build some test data
20 #
21 execsql {
22 CREATE TABLE t1(x int, y int);
23 BEGIN;
24 }
25 for {set i 1} {$i<32} {incr i} {
26 for {set j 0} {(1<<$j)<$i} {incr j} {}
27 execsql "INSERT INTO t1 VALUES([expr {32-$i}],[expr {10-$j}])"
28 }
29 execsql {
30 COMMIT
31 }
32
33 do_test select5-1.0 {
34 execsql {SELECT DISTINCT y FROM t1 ORDER BY y}
35 } {5 6 7 8 9 10}
36
37 # Sort by an aggregate function.
38 #
39 do_test select5-1.1 {
40 execsql {SELECT y, count(*) FROM t1 GROUP BY y ORDER BY y}
41 } {5 15 6 8 7 4 8 2 9 1 10 1}
42 do_test select5-1.2 {
43 execsql {SELECT y, count(*) FROM t1 GROUP BY y ORDER BY count(*), y}
44 } {9 1 10 1 8 2 7 4 6 8 5 15}
45 do_test select5-1.3 {
46 execsql {SELECT count(*), y FROM t1 GROUP BY y ORDER BY count(*), y}
47 } {1 9 1 10 2 8 4 7 8 6 15 5}
48
49 # Some error messages associated with aggregates and GROUP BY
50 #
51 do_test select5-2.1.1 {
52 catchsql {
53 SELECT y, count(*) FROM t1 GROUP BY z ORDER BY y
54 }
55 } {1 {no such column: z}}
56 do_test select5-2.1.2 {
57 catchsql {
58 SELECT y, count(*) FROM t1 GROUP BY temp.t1.y ORDER BY y
59 }
60 } {1 {no such column: temp.t1.y}}
61 do_test select5-2.2 {
62 set v [catch {execsql {
63 SELECT y, count(*) FROM t1 GROUP BY z(y) ORDER BY y
64 }} msg]
65 lappend v $msg
66 } {1 {no such function: z}}
67 do_test select5-2.3 {
68 set v [catch {execsql {
69 SELECT y, count(*) FROM t1 GROUP BY y HAVING count(*)<3 ORDER BY y
70 }} msg]
71 lappend v $msg
72 } {0 {8 2 9 1 10 1}}
73 do_test select5-2.4 {
74 set v [catch {execsql {
75 SELECT y, count(*) FROM t1 GROUP BY y HAVING z(y)<3 ORDER BY y
76 }} msg]
77 lappend v $msg
78 } {1 {no such function: z}}
79 do_test select5-2.5 {
80 set v [catch {execsql {
81 SELECT y, count(*) FROM t1 GROUP BY y HAVING count(*)<z ORDER BY y
82 }} msg]
83 lappend v $msg
84 } {1 {no such column: z}}
85
86 # Get the Agg function to rehash in vdbe.c
87 #
88 do_test select5-3.1 {
89 execsql {
90 SELECT x, count(*), avg(y) FROM t1 GROUP BY x HAVING x<4 ORDER BY x
91 }
92 } {1 1 5.0 2 1 5.0 3 1 5.0}
93
94 # Run various aggregate functions when the count is zero.
95 #
96 do_test select5-4.1 {
97 execsql {
98 SELECT avg(x) FROM t1 WHERE x>100
99 }
100 } {{}}
101 do_test select5-4.2 {
102 execsql {
103 SELECT count(x) FROM t1 WHERE x>100
104 }
105 } {0}
106 do_test select5-4.3 {
107 execsql {
108 SELECT min(x) FROM t1 WHERE x>100
109 }
110 } {{}}
111 do_test select5-4.4 {
112 execsql {
113 SELECT max(x) FROM t1 WHERE x>100
114 }
115 } {{}}
116 do_test select5-4.5 {
117 execsql {
118 SELECT sum(x) FROM t1 WHERE x>100
119 }
120 } {{}}
121
122 # Some tests for queries with a GROUP BY clause but no aggregate functions.
123 #
124 # Note: The query in test cases 5.1 through 5.5 are not legal SQL. So if the
125 # implementation changes in the future and it returns different results,
126 # this is not such a big deal.
127 #
128 do_test select5-5.1 {
129 execsql {
130 CREATE TABLE t2(a, b, c);
131 INSERT INTO t2 VALUES(1, 2, 3);
132 INSERT INTO t2 VALUES(1, 4, 5);
133 INSERT INTO t2 VALUES(6, 4, 7);
134 CREATE INDEX t2_idx ON t2(a);
135 }
136 } {}
137 do_test select5-5.2 {
138 execsql {
139 SELECT a FROM t2 GROUP BY a;
140 }
141 } {1 6}
142 do_test select5-5.3 {
143 execsql {
144 SELECT a FROM t2 WHERE a>2 GROUP BY a;
145 }
146 } {6}
147 do_test select5-5.4 {
148 execsql {
149 SELECT a, b FROM t2 GROUP BY a, b;
150 }
151 } {1 2 1 4 6 4}
152 do_test select5-5.5 {
153 execsql {
154 SELECT a, b FROM t2 GROUP BY a;
155 }
156 } {1 2 6 4}
157
158 # Test rendering of columns for the GROUP BY clause.
159 #
160 do_test select5-5.11 {
161 execsql {
162 SELECT max(c), b*a, b, a FROM t2 GROUP BY b*a, b, a
163 }
164 } {3 2 2 1 5 4 4 1 7 24 4 6}
165
166 # NULL compare equal to each other for the purposes of processing
167 # the GROUP BY clause.
168 #
169 do_test select5-6.1 {
170 execsql {
171 CREATE TABLE t3(x,y);
172 INSERT INTO t3 VALUES(1,NULL);
173 INSERT INTO t3 VALUES(2,NULL);
174 INSERT INTO t3 VALUES(3,4);
175 SELECT count(x), y FROM t3 GROUP BY y ORDER BY 1
176 }
177 } {1 4 2 {}}
178 do_test select5-6.2 {
179 execsql {
180 CREATE TABLE t4(x,y,z);
181 INSERT INTO t4 VALUES(1,2,NULL);
182 INSERT INTO t4 VALUES(2,3,NULL);
183 INSERT INTO t4 VALUES(3,NULL,5);
184 INSERT INTO t4 VALUES(4,NULL,6);
185 INSERT INTO t4 VALUES(4,NULL,6);
186 INSERT INTO t4 VALUES(5,NULL,NULL);
187 INSERT INTO t4 VALUES(5,NULL,NULL);
188 INSERT INTO t4 VALUES(6,7,8);
189 SELECT max(x), count(x), y, z FROM t4 GROUP BY y, z ORDER BY 1
190 }
191 } {1 1 2 {} 2 1 3 {} 3 1 {} 5 4 2 {} 6 5 2 {} {} 6 1 7 8}
192
193 do_test select5-7.2 {
194 execsql {
195 SELECT count(*), count(x) as cnt FROM t4 GROUP BY y ORDER BY cnt;
196 }
197 } {1 1 1 1 1 1 5 5}
198
199 # See ticket #3324.
200 #
201 do_test select5-8.1 {
202 execsql {
203 CREATE TABLE t8a(a,b);
204 CREATE TABLE t8b(x);
205 INSERT INTO t8a VALUES('one', 1);
206 INSERT INTO t8a VALUES('one', 2);
207 INSERT INTO t8a VALUES('two', 3);
208 INSERT INTO t8a VALUES('one', NULL);
209 INSERT INTO t8b(rowid,x) VALUES(1,111);
210 INSERT INTO t8b(rowid,x) VALUES(2,222);
211 INSERT INTO t8b(rowid,x) VALUES(3,333);
212 SELECT a, count(b) FROM t8a, t8b WHERE b=t8b.rowid GROUP BY a ORDER BY a;
213 }
214 } {one 2 two 1}
215 do_test select5-8.2 {
216 execsql {
217 SELECT a, count(b) FROM t8a, t8b WHERE b=+t8b.rowid GROUP BY a ORDER BY a;
218 }
219 } {one 2 two 1}
220 do_test select5-8.3 {
221 execsql {
222 SELECT t8a.a, count(t8a.b) FROM t8a, t8b WHERE t8a.b=t8b.rowid
223 GROUP BY 1 ORDER BY 1;
224 }
225 } {one 2 two 1}
226 do_test select5-8.4 {
227 execsql {
228 SELECT a, count(*) FROM t8a, t8b WHERE b=+t8b.rowid GROUP BY a ORDER BY a;
229 }
230 } {one 2 two 1}
231 do_test select5-8.5 {
232 execsql {
233 SELECT a, count(b) FROM t8a, t8b WHERE b<x GROUP BY a ORDER BY a;
234 }
235 } {one 6 two 3}
236 do_test select5-8.6 {
237 execsql {
238 SELECT a, count(t8a.b) FROM t8a, t8b WHERE b=t8b.rowid
239 GROUP BY a ORDER BY 2;
240 }
241 } {two 1 one 2}
242 do_test select5-8.7 {
243 execsql {
244 SELECT a, count(b) FROM t8a, t8b GROUP BY a ORDER BY 2;
245 }
246 } {two 3 one 6}
247 do_test select5-8.8 {
248 execsql {
249 SELECT a, count(*) FROM t8a, t8b GROUP BY a ORDER BY 2;
250 }
251 } {two 3 one 9}
252
253 # 2021-04-26 forum https://sqlite.org/forum/forumpost/74330094d8
254 reset_db
255 do_execsql_test select5-9.1 {
256 CREATE TABLE t1(a INT, b INT);
257 INSERT INTO t1(a,b) VALUES(1,null),(null,null),(1,null);
258 CREATE UNIQUE INDEX t1b ON t1(abs(b));
259 SELECT quote(a), quote(b), '|' FROM t1 GROUP BY a, abs(b);
260 } {NULL NULL | 1 NULL |}
261
262 finish_test