1 2#pragma ident "%Z%%M% %I% %E% SMI" 3 4# 2001 September 15 5# 6# The author disclaims copyright to this source code. In place of 7# a legal notice, here is a blessing: 8# 9# May you do good and not evil. 10# May you find forgiveness for yourself and forgive others. 11# May you share freely, never taking more than you give. 12# 13#*********************************************************************** 14# This file implements regression tests for SQLite library. The 15# focus of this file is testing aggregate functions and the 16# GROUP BY and HAVING clauses of SELECT statements. 17# 18# $Id: select5.test,v 1.6 2001/10/15 00:44:36 drh Exp $ 19 20set testdir [file dirname $argv0] 21source $testdir/tester.tcl 22 23# Build some test data 24# 25set fd [open data1.txt w] 26for {set i 1} {$i<32} {incr i} { 27 for {set j 0} {pow(2,$j)<$i} {incr j} {} 28 puts $fd "[expr {32-$i}]\t[expr {10-$j}]" 29} 30close $fd 31execsql { 32 CREATE TABLE t1(x int, y int); 33 COPY t1 FROM 'data1.txt' 34} 35file delete data1.txt 36 37do_test select5-1.0 { 38 execsql {SELECT DISTINCT y FROM t1 ORDER BY y} 39} {5 6 7 8 9 10} 40 41# Sort by an aggregate function. 42# 43do_test select5-1.1 { 44 execsql {SELECT y, count(*) FROM t1 GROUP BY y ORDER BY y} 45} {5 15 6 8 7 4 8 2 9 1 10 1} 46do_test select5-1.2 { 47 execsql {SELECT y, count(*) FROM t1 GROUP BY y ORDER BY count(*), y} 48} {9 1 10 1 8 2 7 4 6 8 5 15} 49do_test select5-1.3 { 50 execsql {SELECT count(*), y FROM t1 GROUP BY y ORDER BY count(*), y} 51} {1 9 1 10 2 8 4 7 8 6 15 5} 52 53# Some error messages associated with aggregates and GROUP BY 54# 55do_test select5-2.1 { 56 set v [catch {execsql { 57 SELECT y, count(*) FROM t1 GROUP BY z ORDER BY y 58 }} msg] 59 lappend v $msg 60} {1 {no such column: z}} 61do_test select5-2.2 { 62 set v [catch {execsql { 63 SELECT y, count(*) FROM t1 GROUP BY z(y) ORDER BY y 64 }} msg] 65 lappend v $msg 66} {1 {no such function: z}} 67do_test select5-2.3 { 68 set v [catch {execsql { 69 SELECT y, count(*) FROM t1 GROUP BY y HAVING count(*)<3 ORDER BY y 70 }} msg] 71 lappend v $msg 72} {0 {8 2 9 1 10 1}} 73do_test select5-2.4 { 74 set v [catch {execsql { 75 SELECT y, count(*) FROM t1 GROUP BY y HAVING z(y)<3 ORDER BY y 76 }} msg] 77 lappend v $msg 78} {1 {no such function: z}} 79do_test select5-2.5 { 80 set v [catch {execsql { 81 SELECT y, count(*) FROM t1 GROUP BY y HAVING count(*)<z ORDER BY y 82 }} msg] 83 lappend v $msg 84} {1 {no such column: z}} 85 86# Get the Agg function to rehash in vdbe.c 87# 88do_test select5-3.1 { 89 execsql { 90 SELECT x, count(*), avg(y) FROM t1 GROUP BY x HAVING x<4 ORDER BY x 91 } 92} {1 1 5 2 1 5 3 1 5} 93 94# Run various aggregate functions when the count is zero. 95# 96do_test select5-4.1 { 97 execsql { 98 SELECT avg(x) FROM t1 WHERE x>100 99 } 100} {{}} 101do_test select5-4.2 { 102 execsql { 103 SELECT count(x) FROM t1 WHERE x>100 104 } 105} {0} 106do_test select5-4.3 { 107 execsql { 108 SELECT min(x) FROM t1 WHERE x>100 109 } 110} {{}} 111do_test select5-4.4 { 112 execsql { 113 SELECT max(x) FROM t1 WHERE x>100 114 } 115} {{}} 116do_test select5-4.5 { 117 execsql { 118 SELECT sum(x) FROM t1 WHERE x>100 119 } 120} {0} 121 122finish_test 123