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