1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145
|
# 2001 September 15
#
# The author disclaims copyright to this source code. In place of
# a legal notice, here is a blessing:
#
# May you do good and not evil.
# May you find forgiveness for yourself and forgive others.
# May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library. The
# focus of this file is testing aggregate functions and the
# GROUP BY and HAVING clauses of SELECT statements.
#
# $Id: select3.test,v 1.5 2002/01/22 14:11:30 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl
# Build some test data
#
do_test select3-1.0 {
set fd [open data1.txt w]
for {set i 1} {$i<32} {incr i} {
for {set j 0} {pow(2,$j)<$i} {incr j} {}
puts $fd "$i\t$j"
}
close $fd
execsql {
CREATE TABLE t1(n int, log int);
COPY t1 FROM 'data1.txt'
}
file delete data1.txt
execsql {SELECT DISTINCT log FROM t1 ORDER BY log}
} {0 1 2 3 4 5}
# Basic aggregate functions.
#
do_test select3-1.1 {
execsql {SELECT count(*) FROM t1}
} {31}
do_test select3-1.2 {
execsql {
SELECT min(n),min(log),max(n),max(log),sum(n),sum(log),avg(n),avg(log)
FROM t1
}
} {1 0 31 5 496 124 16 4}
do_test select3-1.3 {
execsql {SELECT max(n)/avg(n), max(log)/avg(log) FROM t1}
} {1.9375 1.25}
# Try some basic GROUP BY clauses
#
do_test select3-2.1 {
execsql {SELECT log, count(*) FROM t1 GROUP BY log ORDER BY log}
} {0 1 1 1 2 2 3 4 4 8 5 15}
do_test select3-2.2 {
execsql {SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log}
} {0 1 1 2 2 3 3 5 4 9 5 17}
do_test select3-2.3 {
execsql {SELECT log, avg(n) FROM t1 GROUP BY log ORDER BY log}
} {0 1 1 2 2 3.5 3 6.5 4 12.5 5 24}
do_test select3-2.3 {
execsql {SELECT log, avg(n)+1 FROM t1 GROUP BY log ORDER BY log}
} {0 2 1 3 2 4.5 3 7.5 4 13.5 5 25}
do_test select3-2.4 {
execsql {SELECT log, avg(n)-min(n) FROM t1 GROUP BY log ORDER BY log}
} {0 0 1 0 2 0.5 3 1.5 4 3.5 5 7}
do_test select3-2.5 {
execsql {SELECT log*2+1, avg(n)-min(n) FROM t1 GROUP BY log ORDER BY log}
} {1 0 3 0 5 0.5 7 1.5 9 3.5 11 7}
do_test select3-2.6 {
execsql {
SELECT log*2+1 as x, count(*) FROM t1 GROUP BY x ORDER BY x
}
} {1 1 3 1 5 2 7 4 9 8 11 15}
do_test select3-2.7 {
execsql {
SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY y
}
} {1 1 3 1 5 2 7 4 9 8 11 15}
do_test select3-2.8 {
execsql {
SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY 10-(x+y)
}
} {11 15 9 8 7 4 5 2 3 1 1 1}
do_test select3-2.9 {
catchsql {
SELECT log, count(*) FROM t1 GROUP BY 8 ORDER BY log;
}
} {1 {GROUP BY expressions should not be constant}}
# Cannot have a HAVING without a GROUP BY
#
do_test select3-3.1 {
set v [catch {execsql {SELECT log, count(*) FROM t1 HAVING log>=4}} msg]
lappend v $msg
} {1 {a GROUP BY clause is required before HAVING}}
# Toss in some HAVING clauses
#
do_test select3-4.1 {
execsql {SELECT log, count(*) FROM t1 GROUP BY log HAVING log>=4 ORDER BY log}
} {4 8 5 15}
do_test select3-4.2 {
execsql {
SELECT log, count(*) FROM t1
GROUP BY log
HAVING count(*)>=4
ORDER BY log
}
} {3 4 4 8 5 15}
do_test select3-4.3 {
execsql {
SELECT log, count(*) FROM t1
GROUP BY log
HAVING count(*)>=4
ORDER BY max(n)
}
} {3 4 4 8 5 15}
do_test select3-4.4 {
execsql {
SELECT log AS x, count(*) AS y FROM t1
GROUP BY x
HAVING y>=4
ORDER BY max(n)
}
} {3 4 4 8 5 15}
do_test select3-5.1 {
execsql {
SELECT log, count(*), avg(n), max(n+log*2) FROM t1
GROUP BY log
ORDER BY max(n+log*2), avg(n)
}
} {0 1 1 1 1 1 2 4 2 2 3.5 8 3 4 6.5 14 4 8 12.5 24 5 15 24 41}
do_test select3-5.2 {
execsql {
SELECT log, count(*), avg(n), max(n+log*2) FROM t1
GROUP BY log
ORDER BY max(n+log*2), min(log,avg(n))
}
} {0 1 1 1 1 1 2 4 2 2 3.5 8 3 4 6.5 14 4 8 12.5 24 5 15 24 41}
finish_test
|