3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
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.
15 # $Id: select5.test,v 1.9 2005/01/26 03:58:36 danielk1977 Exp $
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
20 # Build some test data
23 CREATE TABLE t1(x int, y int);
26 for {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}])"
35 execsql {SELECT DISTINCT y FROM t1 ORDER BY y}
38 # Sort by an aggregate function.
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}
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}
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}
50 # Some error messages associated with aggregates and GROUP BY
53 set v [catch {execsql {
54 SELECT y, count(*) FROM t1 GROUP BY z ORDER BY y
57 } {1 {no such column: z}}
59 set v [catch {execsql {
60 SELECT y, count(*) FROM t1 GROUP BY z(y) ORDER BY y
63 } {1 {no such function: z}}
65 set v [catch {execsql {
66 SELECT y, count(*) FROM t1 GROUP BY y HAVING count(*)<3 ORDER BY y
71 set v [catch {execsql {
72 SELECT y, count(*) FROM t1 GROUP BY y HAVING z(y)<3 ORDER BY y
75 } {1 {no such function: z}}
77 set v [catch {execsql {
78 SELECT y, count(*) FROM t1 GROUP BY y HAVING count(*)<z ORDER BY y
81 } {1 {no such column: z}}
83 # Get the Agg function to rehash in vdbe.c
87 SELECT x, count(*), avg(y) FROM t1 GROUP BY x HAVING x<4 ORDER BY x
89 } {1 1 5.0 2 1 5.0 3 1 5.0}
91 # Run various aggregate functions when the count is zero.
95 SELECT avg(x) FROM t1 WHERE x>100
100 SELECT count(x) FROM t1 WHERE x>100
103 do_test select5-4.3 {
105 SELECT min(x) FROM t1 WHERE x>100
108 do_test select5-4.4 {
110 SELECT max(x) FROM t1 WHERE x>100
113 do_test select5-4.5 {
115 SELECT sum(x) FROM t1 WHERE x>100
119 # Some tests for queries with a GROUP BY clause but no aggregate functions.
121 # Note: The query in test case 5-5.5 are not legal SQL. So if the
122 # implementation changes in the future and it returns different results,
123 # this is not such a big deal.
125 do_test select5-5.1 {
127 CREATE TABLE t2(a, b, c);
128 INSERT INTO t2 VALUES(1, 2, 3);
129 INSERT INTO t2 VALUES(1, 4, 5);
130 INSERT INTO t2 VALUES(6, 4, 7);
131 CREATE INDEX t2_idx ON t2(a);
134 do_test select5-5.2 {
136 SELECT a FROM t2 GROUP BY a;
139 do_test select5-5.3 {
141 SELECT a FROM t2 WHERE a>2 GROUP BY a;
144 do_test select5-5.4 {
146 SELECT a, b FROM t2 GROUP BY a, b;
149 do_test select5-5.5 {
151 SELECT a, b FROM t2 GROUP BY a;