xref: /sqlite-3.40.0/test/select5.test (revision 74e4352a) 
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.16 2006/01/21 12:08:55 danielk1977 Exp $
16
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19
20# Build some test data
21#
22execsql {
23  CREATE TABLE t1(x int, y int);
24  BEGIN;
25}
26for {set i 1} {$i<32} {incr i} {
27  for {set j 0} {pow(2,$j)<$i} {incr j} {}
28  execsql "INSERT INTO t1 VALUES([expr {32-$i}],[expr {10-$j}])"
29}
30execsql {
31  COMMIT
32}
33
34do_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#
40do_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}
43do_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}
46do_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#
52do_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}}
57do_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}}
62do_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}}
68do_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}}
74do_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}}
80do_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#
89do_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#
97do_test select5-4.1 {
98  execsql {
99    SELECT avg(x) FROM t1 WHERE x>100
100  }
101} {{}}
102do_test select5-4.2 {
103  execsql {
104    SELECT count(x) FROM t1 WHERE x>100
105  }
106} {0}
107do_test select5-4.3 {
108  execsql {
109    SELECT min(x) FROM t1 WHERE x>100
110  }
111} {{}}
112do_test select5-4.4 {
113  execsql {
114    SELECT max(x) FROM t1 WHERE x>100
115  }
116} {{}}
117do_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 case 5-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#
129do_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} {}
138do_test select5-5.2 {
139  execsql {
140    SELECT a FROM t2 GROUP BY a;
141  }
142} {1 6}
143do_test select5-5.3 {
144  execsql {
145    SELECT a FROM t2 WHERE a>2 GROUP BY a;
146  }
147} {6}
148do_test select5-5.4 {
149  execsql {
150    SELECT a, b FROM t2 GROUP BY a, b;
151  }
152} {1 2 1 4 6 4}
153do_test select5-5.5 {
154  execsql {
155    SELECT a, b FROM t2 GROUP BY a;
156  }
157} {1 4 6 4}
158
159# NULL compare equal to each other for the purposes of processing
160# the GROUP BY clause.
161#
162do_test select5-6.1 {
163  execsql {
164    CREATE TABLE t3(x,y);
165    INSERT INTO t3 VALUES(1,NULL);
166    INSERT INTO t3 VALUES(2,NULL);
167    INSERT INTO t3 VALUES(3,4);
168    SELECT count(x), y FROM t3 GROUP BY y ORDER BY 1
169  }
170} {1 4 2 {}}
171do_test select5-6.2 {
172  execsql {
173    CREATE TABLE t4(x,y,z);
174    INSERT INTO t4 VALUES(1,2,NULL);
175    INSERT INTO t4 VALUES(2,3,NULL);
176    INSERT INTO t4 VALUES(3,NULL,5);
177    INSERT INTO t4 VALUES(4,NULL,6);
178    INSERT INTO t4 VALUES(4,NULL,6);
179    INSERT INTO t4 VALUES(5,NULL,NULL);
180    INSERT INTO t4 VALUES(5,NULL,NULL);
181    INSERT INTO t4 VALUES(6,7,8);
182    SELECT max(x), count(x), y, z FROM t4 GROUP BY y, z ORDER BY 1
183  }
184} {1 1 2 {} 2 1 3 {} 3 1 {} 5 4 2 {} 6 5 2 {} {} 6 1 7 8}
185
186do_test select5.7.2 {
187  execsql {
188    SELECT count(*), count(x) as cnt FROM t4 GROUP BY y ORDER BY cnt;
189  }
190} {1 1 1 1 1 1 5 5}
191
192finish_test
193