Debian Codegraph @

test/select3.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 # $Id: select3.test,v 1.23 2008/01/16 18:20:42 danielk1977 Exp $
16
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
19
20 # Build some test data
21 #
22 do_test select3-1.0 {
23 execsql {
24 CREATE TABLE t1(n int, log int);
25 BEGIN;
26 }
27 for {set i 1} {$i<32} {incr i} {
28 for {set j 0} {(1<<$j)<$i} {incr j} {}
29 execsql "INSERT INTO t1 VALUES($i,$j)"
30 }
31 execsql {
32 COMMIT
33 }
34 execsql {SELECT DISTINCT log FROM t1 ORDER BY log}
35 } {0 1 2 3 4 5}
36
37 # Basic aggregate functions.
38 #
39 do_test select3-1.1 {
40 execsql {SELECT count(*) FROM t1}
41 } {31}
42 do_test select3-1.2 {
43 execsql {
44 SELECT min(n),min(log),max(n),max(log),sum(n),sum(log),avg(n),avg(log)
45 FROM t1
46 }
47 } {1 0 31 5 496 124 16.0 4.0}
48 do_test select3-1.3 {
49 execsql {SELECT max(n)/avg(n), max(log)/avg(log) FROM t1}
50 } {1.9375 1.25}
51
52 # Try some basic GROUP BY clauses
53 #
54 do_test select3-2.1 {
55 execsql {SELECT log, count(*) FROM t1 GROUP BY log ORDER BY log}
56 } {0 1 1 1 2 2 3 4 4 8 5 15}
57 do_test select3-2.2 {
58 execsql {SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log}
59 } {0 1 1 2 2 3 3 5 4 9 5 17}
60 do_test select3-2.3.1 {
61 execsql {SELECT log, avg(n) FROM t1 GROUP BY log ORDER BY log}
62 } {0 1.0 1 2.0 2 3.5 3 6.5 4 12.5 5 24.0}
63 do_test select3-2.3.2 {
64 execsql {SELECT log, avg(n)+1 FROM t1 GROUP BY log ORDER BY log}
65 } {0 2.0 1 3.0 2 4.5 3 7.5 4 13.5 5 25.0}
66 do_test select3-2.4 {
67 execsql {SELECT log, avg(n)-min(n) FROM t1 GROUP BY log ORDER BY log}
68 } {0 0.0 1 0.0 2 0.5 3 1.5 4 3.5 5 7.0}
69 do_test select3-2.5 {
70 execsql {SELECT log*2+1, avg(n)-min(n) FROM t1 GROUP BY log ORDER BY log}
71 } {1 0.0 3 0.0 5 0.5 7 1.5 9 3.5 11 7.0}
72 do_test select3-2.6 {
73 execsql {
74 SELECT log*2+1 as x, count(*) FROM t1 GROUP BY x ORDER BY x
75 }
76 } {1 1 3 1 5 2 7 4 9 8 11 15}
77 do_test select3-2.7 {
78 execsql {
79 SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY y, x
80 }
81 } {1 1 3 1 5 2 7 4 9 8 11 15}
82 do_test select3-2.8 {
83 execsql {
84 SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY 10-(x+y)
85 }
86 } {11 15 9 8 7 4 5 2 3 1 1 1}
87 #do_test select3-2.9 {
88 # catchsql {
89 # SELECT log, count(*) FROM t1 GROUP BY 'x' ORDER BY log;
90 # }
91 #} {1 {GROUP BY terms must not be non-integer constants}}
92 do_test select3-2.10 {
93 catchsql {
94 SELECT log, count(*) FROM t1 GROUP BY 0 ORDER BY log;
95 }
96 } {1 {1st GROUP BY term out of range - should be between 1 and 2}}
97 do_test select3-2.11 {
98 catchsql {
99 SELECT log, count(*) FROM t1 GROUP BY 3 ORDER BY log;
100 }
101 } {1 {1st GROUP BY term out of range - should be between 1 and 2}}
102 do_test select3-2.12 {
103 catchsql {
104 SELECT log, count(*) FROM t1 GROUP BY 1 ORDER BY log;
105 }
106 } {0 {0 1 1 1 2 2 3 4 4 8 5 15}}
107
108 # Cannot have an empty GROUP BY
109 do_test select3-2.13 {
110 catchsql {
111 SELECT log, count(*) FROM t1 GROUP BY ORDER BY log;
112 }
113 } {1 {near "ORDER": syntax error}}
114 do_test select3-2.14 {
115 catchsql {
116 SELECT log, count(*) FROM t1 GROUP BY;
117 }
118 } {1 {near ";": syntax error}}
119
120 # Cannot have a HAVING without a GROUP BY
121 #
122 # Update: As of 3.39.0, you can.
123 #
124 do_execsql_test select3-3.1 {
125 SELECT log, count(*) FROM t1 HAVING log>=4
126 } {}
127 do_execsql_test select3-3.2 {
128 SELECT count(*) FROM t1 HAVING log>=4
129 } {}
130 do_execsql_test select3-3.3 {
131 SELECT count(*) FROM t1 HAVING log!=400
132 } {31}
133
134 # Toss in some HAVING clauses
135 #
136 do_test select3-4.1 {
137 execsql {SELECT log, count(*) FROM t1 GROUP BY log HAVING log>=4 ORDER BY log}
138 } {4 8 5 15}
139 do_test select3-4.2 {
140 execsql {
141 SELECT log, count(*) FROM t1
142 GROUP BY log
143 HAVING count(*)>=4
144 ORDER BY log
145 }
146 } {3 4 4 8 5 15}
147 do_test select3-4.3 {
148 execsql {
149 SELECT log, count(*) FROM t1
150 GROUP BY log
151 HAVING count(*)>=4
152 ORDER BY max(n)+0
153 }
154 } {3 4 4 8 5 15}
155 do_test select3-4.4 {
156 execsql {
157 SELECT log AS x, count(*) AS y FROM t1
158 GROUP BY x
159 HAVING y>=4
160 ORDER BY max(n)+0
161 }
162 } {3 4 4 8 5 15}
163 do_test select3-4.5 {
164 execsql {
165 SELECT log AS x FROM t1
166 GROUP BY x
167 HAVING count(*)>=4
168 ORDER BY max(n)+0
169 }
170 } {3 4 5}
171
172 do_test select3-5.1 {
173 execsql {
174 SELECT log, count(*), avg(n), max(n+log*2) FROM t1
175 GROUP BY log
176 ORDER BY max(n+log*2)+0, avg(n)+0
177 }
178 } {0 1 1.0 1 1 1 2.0 4 2 2 3.5 8 3 4 6.5 14 4 8 12.5 24 5 15 24.0 41}
179 do_test select3-5.2 {
180 execsql {
181 SELECT log, count(*), avg(n), max(n+log*2) FROM t1
182 GROUP BY log
183 ORDER BY max(n+log*2)+0, min(log,avg(n))+0
184 }
185 } {0 1 1.0 1 1 1 2.0 4 2 2 3.5 8 3 4 6.5 14 4 8 12.5 24 5 15 24.0 41}
186
187 # Test sorting of GROUP BY results in the presence of an index
188 # on the GROUP BY column.
189 #
190 do_test select3-6.1 {
191 execsql {
192 SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log;
193 }
194 } {0 1 1 2 2 3 3 5 4 9 5 17}
195 do_test select3-6.2 {
196 execsql {
197 SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log DESC;
198 }
199 } {5 17 4 9 3 5 2 3 1 2 0 1}
200 do_test select3-6.3 {
201 execsql {
202 SELECT log, min(n) FROM t1 GROUP BY log ORDER BY 1;
203 }
204 } {0 1 1 2 2 3 3 5 4 9 5 17}
205 do_test select3-6.4 {
206 execsql {
207 SELECT log, min(n) FROM t1 GROUP BY log ORDER BY 1 DESC;
208 }
209 } {5 17 4 9 3 5 2 3 1 2 0 1}
210 do_test select3-6.5 {
211 execsql {
212 CREATE INDEX i1 ON t1(log);
213 SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log;
214 }
215 } {0 1 1 2 2 3 3 5 4 9 5 17}
216 do_test select3-6.6 {
217 execsql {
218 SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log DESC;
219 }
220 } {5 17 4 9 3 5 2 3 1 2 0 1}
221 do_test select3-6.7 {
222 execsql {
223 SELECT log, min(n) FROM t1 GROUP BY log ORDER BY 1;
224 }
225 } {0 1 1 2 2 3 3 5 4 9 5 17}
226 do_test select3-6.8 {
227 execsql {
228 SELECT log, min(n) FROM t1 GROUP BY log ORDER BY 1 DESC;
229 }
230 } {5 17 4 9 3 5 2 3 1 2 0 1}
231
232 # Sometimes an aggregate query can return no rows at all.
233 #
234 do_test select3-7.1 {
235 execsql {
236 CREATE TABLE t2(a,b);
237 INSERT INTO t2 VALUES(1,2);
238 SELECT a, sum(b) FROM t2 WHERE b=5 GROUP BY a;
239 }
240 } {}
241 do_test select3-7.2 {
242 execsql {
243 SELECT a, sum(b) FROM t2 WHERE b=5;
244 }
245 } {{} {}}
246
247 # If a table column is of type REAL but we are storing integer values
248 # in it, the values are stored as integers to take up less space. The
249 # values are converted by to REAL as they are read out of the table.
250 # Make sure the GROUP BY clause does this conversion correctly.
251 # Ticket #2251.
252 #
253 do_test select3-8.1 {
254 execsql {
255 CREATE TABLE A (
256 A1 DOUBLE,
257 A2 VARCHAR COLLATE NOCASE,
258 A3 DOUBLE
259 );
260 INSERT INTO A VALUES(39136,'ABC',1201900000);
261 INSERT INTO A VALUES(39136,'ABC',1207000000);
262 SELECT typeof(sum(a3)) FROM a;
263 }
264 } {real}
265 do_test select3-8.2 {
266 execsql {
267 SELECT typeof(sum(a3)) FROM a GROUP BY a1;
268 }
269 } {real}
270
271 # 2019-05-09 ticket https://sqlite.org/src/tktview/6c1d3febc00b22d457c7
272 #
273 unset -nocomplain x
274 foreach {id x} {
275 100 127
276 101 128
277 102 -127
278 103 -128
279 104 -129
280 110 32767
281 111 32768
282 112 -32767
283 113 -32768
284 114 -32769
285 120 2147483647
286 121 2147483648
287 122 -2147483647
288 123 -2147483648
289 124 -2147483649
290 130 140737488355327
291 131 140737488355328
292 132 -140737488355327
293 133 -140737488355328
294 134 -140737488355329
295 140 9223372036854775807
296 141 -9223372036854775807
297 142 -9223372036854775808
298 143 9223372036854775806
299 144 9223372036854775805
300 145 -9223372036854775806
301 146 -9223372036854775805
302
303 } {
304 set x [expr {$x+0}]
305 do_execsql_test select3-8.$id {
306 DROP TABLE IF EXISTS t1;
307 CREATE TABLE t1 (c0, c1 REAL PRIMARY KEY);
308 INSERT INTO t1(c0, c1) VALUES (0, $x), (0, 0);
309 UPDATE t1 SET c0 = NULL;
310 UPDATE OR REPLACE t1 SET c1 = 1;
311 SELECT DISTINCT * FROM t1 WHERE (t1.c0 IS NULL);
312 PRAGMA integrity_check;
313 } {{} 1.0 ok}
314 }
315
316 # 2020-03-10 ticket e0c2ad1aa8a9c691
317 reset_db
318 do_execsql_test select3-9.100 {
319 CREATE TABLE t0(c0 REAL, c1 REAL GENERATED ALWAYS AS (c0));
320 INSERT INTO t0(c0) VALUES (1);
321 SELECT * FROM t0 GROUP BY c0;
322 } {1.0 1.0}
323
324 reset_db
325 do_execsql_test select3.10.100 {
326 CREATE TABLE t1(a, b);
327 CREATE TABLE t2(c, d);
328 SELECT max(t1.a),
329 (SELECT 'xyz' FROM (SELECT * FROM t2 WHERE 0) WHERE t1.b=1)
330 FROM t1;
331 } {{} {}}
332
333 #-------------------------------------------------------------------------
334 # dbsqlfuzz crash-8e17857db2c5a9294c975123ac807156a6559f13.txt
335 # Associated with the flatten-left-join branch circa 2022-06-23.
336 #
337 foreach {tn sql} {
338 1 {
339 CREATE TABLE t1(a TEXT);
340 CREATE TABLE t2(x INT);
341 CREATE INDEX t2x ON t2(x);
342 INSERT INTO t1 VALUES('abc');
343 }
344 2 {
345 CREATE TABLE t1(a TEXT);
346 CREATE TABLE t2(x INT);
347 INSERT INTO t1 VALUES('abc');
348 }
349 3 {
350 CREATE TABLE t1(a TEXT);
351 CREATE TABLE t2(x INT);
352 INSERT INTO t1 VALUES('abc');
353 PRAGMA automatic_index=OFF;
354 }
355 } {
356 reset_db
357 do_execsql_test select3-11.$tn.1 $sql
358 do_execsql_test select3.11.$tn.2 {
359 SELECT max(a), val FROM t1 LEFT JOIN (
360 SELECT 'constant' AS val FROM t2 WHERE x=1234
361 )
362 } {abc {}}
363 do_execsql_test select3.11.$tn.3 {
364 INSERT INTO t2 VALUES(123);
365 SELECT max(a), val FROM t1 LEFT JOIN (
366 SELECT 'constant' AS val FROM t2 WHERE x=1234
367 )
368 } {abc {}}
369 do_execsql_test select3.11.$tn.4 {
370 INSERT INTO t2 VALUES(1234);
371 SELECT max(a), val FROM t1 LEFT JOIN (
372 SELECT 'constant' AS val FROM t2 WHERE x=1234
373 )
374 } {abc constant}
375 }
376
377 reset_db
378 do_execsql_test 12.0 {
379 CREATE TABLE t1(a);
380 CREATE TABLE t2(x);
381 }
382 do_execsql_test 12.1 {
383 SELECT count(x), m FROM t1 LEFT JOIN (SELECT x, 59 AS m FROM t2) GROUP BY a;
384 }
385 do_execsql_test 12.2 {
386 INSERT INTO t1 VALUES(1), (1), (2), (3);
387 SELECT count(x), m FROM t1 LEFT JOIN (SELECT x, 59 AS m FROM t2) GROUP BY a;
388 } {
389 0 {}
390 0 {}
391 0 {}
392 }
393 do_execsql_test 12.3 {
394 INSERT INTO t2 VALUES(45);
395 SELECT count(x), m FROM t1 LEFT JOIN (SELECT x, 59 AS m FROM t2) GROUP BY a;
396 } {
397 2 59
398 1 59
399 1 59
400 }
401 do_execsql_test 12.4 {
402 INSERT INTO t2 VALUES(210);
403 SELECT count(x), m FROM t1 LEFT JOIN (SELECT x, 59 AS m FROM t2) GROUP BY a;
404 } {
405 4 59
406 2 59
407 2 59
408 }
409 do_execsql_test 12.5 {
410 INSERT INTO t2 VALUES(NULL);
411 SELECT count(x), m FROM t1 LEFT JOIN (SELECT x, 59 AS m FROM t2) GROUP BY a;
412 } {
413 4 59
414 2 59
415 2 59
416 }
417 do_execsql_test 12.6 {
418 DELETE FROM t2;
419 DELETE FROM t1;
420 INSERT INTO t1 VALUES('value');
421 INSERT INTO t2 VALUES('hello');
422 } {}
423 do_execsql_test 12.7 {
424 SELECT group_concat(x), m FROM t1
425 LEFT JOIN (SELECT x, 59 AS m FROM t2) GROUP BY a;
426 } {
427 hello 59
428 }
429 do_execsql_test 12.8 {
430 SELECT group_concat(x), m, n FROM t1
431 LEFT JOIN (SELECT x, 59 AS m, 60 AS n FROM t2) GROUP BY a;
432 } {
433 hello 59 60
434 }
435
436 finish_test