OLD | NEW |
| (Empty) |
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 do_test select3-3.1 { | |
123 set v [catch {execsql {SELECT log, count(*) FROM t1 HAVING log>=4}} msg] | |
124 lappend v $msg | |
125 } {1 {a GROUP BY clause is required before HAVING}} | |
126 | |
127 # Toss in some HAVING clauses | |
128 # | |
129 do_test select3-4.1 { | |
130 execsql {SELECT log, count(*) FROM t1 GROUP BY log HAVING log>=4 ORDER BY log} | |
131 } {4 8 5 15} | |
132 do_test select3-4.2 { | |
133 execsql { | |
134 SELECT log, count(*) FROM t1 | |
135 GROUP BY log | |
136 HAVING count(*)>=4 | |
137 ORDER BY log | |
138 } | |
139 } {3 4 4 8 5 15} | |
140 do_test select3-4.3 { | |
141 execsql { | |
142 SELECT log, count(*) FROM t1 | |
143 GROUP BY log | |
144 HAVING count(*)>=4 | |
145 ORDER BY max(n)+0 | |
146 } | |
147 } {3 4 4 8 5 15} | |
148 do_test select3-4.4 { | |
149 execsql { | |
150 SELECT log AS x, count(*) AS y FROM t1 | |
151 GROUP BY x | |
152 HAVING y>=4 | |
153 ORDER BY max(n)+0 | |
154 } | |
155 } {3 4 4 8 5 15} | |
156 do_test select3-4.5 { | |
157 execsql { | |
158 SELECT log AS x FROM t1 | |
159 GROUP BY x | |
160 HAVING count(*)>=4 | |
161 ORDER BY max(n)+0 | |
162 } | |
163 } {3 4 5} | |
164 | |
165 do_test select3-5.1 { | |
166 execsql { | |
167 SELECT log, count(*), avg(n), max(n+log*2) FROM t1 | |
168 GROUP BY log | |
169 ORDER BY max(n+log*2)+0, avg(n)+0 | |
170 } | |
171 } {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} | |
172 do_test select3-5.2 { | |
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, min(log,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 | |
180 # Test sorting of GROUP BY results in the presence of an index | |
181 # on the GROUP BY column. | |
182 # | |
183 do_test select3-6.1 { | |
184 execsql { | |
185 SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log; | |
186 } | |
187 } {0 1 1 2 2 3 3 5 4 9 5 17} | |
188 do_test select3-6.2 { | |
189 execsql { | |
190 SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log DESC; | |
191 } | |
192 } {5 17 4 9 3 5 2 3 1 2 0 1} | |
193 do_test select3-6.3 { | |
194 execsql { | |
195 SELECT log, min(n) FROM t1 GROUP BY log ORDER BY 1; | |
196 } | |
197 } {0 1 1 2 2 3 3 5 4 9 5 17} | |
198 do_test select3-6.4 { | |
199 execsql { | |
200 SELECT log, min(n) FROM t1 GROUP BY log ORDER BY 1 DESC; | |
201 } | |
202 } {5 17 4 9 3 5 2 3 1 2 0 1} | |
203 do_test select3-6.5 { | |
204 execsql { | |
205 CREATE INDEX i1 ON t1(log); | |
206 SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log; | |
207 } | |
208 } {0 1 1 2 2 3 3 5 4 9 5 17} | |
209 do_test select3-6.6 { | |
210 execsql { | |
211 SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log DESC; | |
212 } | |
213 } {5 17 4 9 3 5 2 3 1 2 0 1} | |
214 do_test select3-6.7 { | |
215 execsql { | |
216 SELECT log, min(n) FROM t1 GROUP BY log ORDER BY 1; | |
217 } | |
218 } {0 1 1 2 2 3 3 5 4 9 5 17} | |
219 do_test select3-6.8 { | |
220 execsql { | |
221 SELECT log, min(n) FROM t1 GROUP BY log ORDER BY 1 DESC; | |
222 } | |
223 } {5 17 4 9 3 5 2 3 1 2 0 1} | |
224 | |
225 # Sometimes an aggregate query can return no rows at all. | |
226 # | |
227 do_test select3-7.1 { | |
228 execsql { | |
229 CREATE TABLE t2(a,b); | |
230 INSERT INTO t2 VALUES(1,2); | |
231 SELECT a, sum(b) FROM t2 WHERE b=5 GROUP BY a; | |
232 } | |
233 } {} | |
234 do_test select3-7.2 { | |
235 execsql { | |
236 SELECT a, sum(b) FROM t2 WHERE b=5; | |
237 } | |
238 } {{} {}} | |
239 | |
240 # If a table column is of type REAL but we are storing integer values | |
241 # in it, the values are stored as integers to take up less space. The | |
242 # values are converted by to REAL as they are read out of the table. | |
243 # Make sure the GROUP BY clause does this conversion correctly. | |
244 # Ticket #2251. | |
245 # | |
246 do_test select3-8.1 { | |
247 execsql { | |
248 CREATE TABLE A ( | |
249 A1 DOUBLE, | |
250 A2 VARCHAR COLLATE NOCASE, | |
251 A3 DOUBLE | |
252 ); | |
253 INSERT INTO A VALUES(39136,'ABC',1201900000); | |
254 INSERT INTO A VALUES(39136,'ABC',1207000000); | |
255 SELECT typeof(sum(a3)) FROM a; | |
256 } | |
257 } {real} | |
258 do_test select3-8.2 { | |
259 execsql { | |
260 SELECT typeof(sum(a3)) FROM a GROUP BY a1; | |
261 } | |
262 } {real} | |
263 | |
264 finish_test | |
OLD | NEW |