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