1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/select5.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,257 @@
1.4 +# 2001 September 15
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library. The
1.15 +# focus of this file is testing aggregate functions and the
1.16 +# GROUP BY and HAVING clauses of SELECT statements.
1.17 +#
1.18 +# $Id: select5.test,v 1.20 2008/08/21 14:15:59 drh Exp $
1.19 +
1.20 +set testdir [file dirname $argv0]
1.21 +source $testdir/tester.tcl
1.22 +
1.23 +# Build some test data
1.24 +#
1.25 +execsql {
1.26 + CREATE TABLE t1(x int, y int);
1.27 + BEGIN;
1.28 +}
1.29 +for {set i 1} {$i<32} {incr i} {
1.30 + for {set j 0} {(1<<$j)<$i} {incr j} {}
1.31 + execsql "INSERT INTO t1 VALUES([expr {32-$i}],[expr {10-$j}])"
1.32 +}
1.33 +execsql {
1.34 + COMMIT
1.35 +}
1.36 +
1.37 +do_test select5-1.0 {
1.38 + execsql {SELECT DISTINCT y FROM t1 ORDER BY y}
1.39 +} {5 6 7 8 9 10}
1.40 +
1.41 +# Sort by an aggregate function.
1.42 +#
1.43 +do_test select5-1.1 {
1.44 + execsql {SELECT y, count(*) FROM t1 GROUP BY y ORDER BY y}
1.45 +} {5 15 6 8 7 4 8 2 9 1 10 1}
1.46 +do_test select5-1.2 {
1.47 + execsql {SELECT y, count(*) FROM t1 GROUP BY y ORDER BY count(*), y}
1.48 +} {9 1 10 1 8 2 7 4 6 8 5 15}
1.49 +do_test select5-1.3 {
1.50 + execsql {SELECT count(*), y FROM t1 GROUP BY y ORDER BY count(*), y}
1.51 +} {1 9 1 10 2 8 4 7 8 6 15 5}
1.52 +
1.53 +# Some error messages associated with aggregates and GROUP BY
1.54 +#
1.55 +do_test select5-2.1.1 {
1.56 + catchsql {
1.57 + SELECT y, count(*) FROM t1 GROUP BY z ORDER BY y
1.58 + }
1.59 +} {1 {no such column: z}}
1.60 +do_test select5-2.1.2 {
1.61 + catchsql {
1.62 + SELECT y, count(*) FROM t1 GROUP BY temp.t1.y ORDER BY y
1.63 + }
1.64 +} {1 {no such column: temp.t1.y}}
1.65 +do_test select5-2.2 {
1.66 + set v [catch {execsql {
1.67 + SELECT y, count(*) FROM t1 GROUP BY z(y) ORDER BY y
1.68 + }} msg]
1.69 + lappend v $msg
1.70 +} {1 {no such function: z}}
1.71 +do_test select5-2.3 {
1.72 + set v [catch {execsql {
1.73 + SELECT y, count(*) FROM t1 GROUP BY y HAVING count(*)<3 ORDER BY y
1.74 + }} msg]
1.75 + lappend v $msg
1.76 +} {0 {8 2 9 1 10 1}}
1.77 +do_test select5-2.4 {
1.78 + set v [catch {execsql {
1.79 + SELECT y, count(*) FROM t1 GROUP BY y HAVING z(y)<3 ORDER BY y
1.80 + }} msg]
1.81 + lappend v $msg
1.82 +} {1 {no such function: z}}
1.83 +do_test select5-2.5 {
1.84 + set v [catch {execsql {
1.85 + SELECT y, count(*) FROM t1 GROUP BY y HAVING count(*)<z ORDER BY y
1.86 + }} msg]
1.87 + lappend v $msg
1.88 +} {1 {no such column: z}}
1.89 +
1.90 +# Get the Agg function to rehash in vdbe.c
1.91 +#
1.92 +do_test select5-3.1 {
1.93 + execsql {
1.94 + SELECT x, count(*), avg(y) FROM t1 GROUP BY x HAVING x<4 ORDER BY x
1.95 + }
1.96 +} {1 1 5.0 2 1 5.0 3 1 5.0}
1.97 +
1.98 +# Run various aggregate functions when the count is zero.
1.99 +#
1.100 +do_test select5-4.1 {
1.101 + execsql {
1.102 + SELECT avg(x) FROM t1 WHERE x>100
1.103 + }
1.104 +} {{}}
1.105 +do_test select5-4.2 {
1.106 + execsql {
1.107 + SELECT count(x) FROM t1 WHERE x>100
1.108 + }
1.109 +} {0}
1.110 +do_test select5-4.3 {
1.111 + execsql {
1.112 + SELECT min(x) FROM t1 WHERE x>100
1.113 + }
1.114 +} {{}}
1.115 +do_test select5-4.4 {
1.116 + execsql {
1.117 + SELECT max(x) FROM t1 WHERE x>100
1.118 + }
1.119 +} {{}}
1.120 +do_test select5-4.5 {
1.121 + execsql {
1.122 + SELECT sum(x) FROM t1 WHERE x>100
1.123 + }
1.124 +} {{}}
1.125 +
1.126 +# Some tests for queries with a GROUP BY clause but no aggregate functions.
1.127 +#
1.128 +# Note: The query in test cases 5.1 through 5.5 are not legal SQL. So if the
1.129 +# implementation changes in the future and it returns different results,
1.130 +# this is not such a big deal.
1.131 +#
1.132 +do_test select5-5.1 {
1.133 + execsql {
1.134 + CREATE TABLE t2(a, b, c);
1.135 + INSERT INTO t2 VALUES(1, 2, 3);
1.136 + INSERT INTO t2 VALUES(1, 4, 5);
1.137 + INSERT INTO t2 VALUES(6, 4, 7);
1.138 + CREATE INDEX t2_idx ON t2(a);
1.139 + }
1.140 +} {}
1.141 +do_test select5-5.2 {
1.142 + execsql {
1.143 + SELECT a FROM t2 GROUP BY a;
1.144 + }
1.145 +} {1 6}
1.146 +do_test select5-5.3 {
1.147 + execsql {
1.148 + SELECT a FROM t2 WHERE a>2 GROUP BY a;
1.149 + }
1.150 +} {6}
1.151 +do_test select5-5.4 {
1.152 + execsql {
1.153 + SELECT a, b FROM t2 GROUP BY a, b;
1.154 + }
1.155 +} {1 2 1 4 6 4}
1.156 +do_test select5-5.5 {
1.157 + execsql {
1.158 + SELECT a, b FROM t2 GROUP BY a;
1.159 + }
1.160 +} {1 4 6 4}
1.161 +
1.162 +# Test rendering of columns for the GROUP BY clause.
1.163 +#
1.164 +do_test select5-5.11 {
1.165 + execsql {
1.166 + SELECT max(c), b*a, b, a FROM t2 GROUP BY b*a, b, a
1.167 + }
1.168 +} {3 2 2 1 5 4 4 1 7 24 4 6}
1.169 +
1.170 +# NULL compare equal to each other for the purposes of processing
1.171 +# the GROUP BY clause.
1.172 +#
1.173 +do_test select5-6.1 {
1.174 + execsql {
1.175 + CREATE TABLE t3(x,y);
1.176 + INSERT INTO t3 VALUES(1,NULL);
1.177 + INSERT INTO t3 VALUES(2,NULL);
1.178 + INSERT INTO t3 VALUES(3,4);
1.179 + SELECT count(x), y FROM t3 GROUP BY y ORDER BY 1
1.180 + }
1.181 +} {1 4 2 {}}
1.182 +do_test select5-6.2 {
1.183 + execsql {
1.184 + CREATE TABLE t4(x,y,z);
1.185 + INSERT INTO t4 VALUES(1,2,NULL);
1.186 + INSERT INTO t4 VALUES(2,3,NULL);
1.187 + INSERT INTO t4 VALUES(3,NULL,5);
1.188 + INSERT INTO t4 VALUES(4,NULL,6);
1.189 + INSERT INTO t4 VALUES(4,NULL,6);
1.190 + INSERT INTO t4 VALUES(5,NULL,NULL);
1.191 + INSERT INTO t4 VALUES(5,NULL,NULL);
1.192 + INSERT INTO t4 VALUES(6,7,8);
1.193 + SELECT max(x), count(x), y, z FROM t4 GROUP BY y, z ORDER BY 1
1.194 + }
1.195 +} {1 1 2 {} 2 1 3 {} 3 1 {} 5 4 2 {} 6 5 2 {} {} 6 1 7 8}
1.196 +
1.197 +do_test select5-7.2 {
1.198 + execsql {
1.199 + SELECT count(*), count(x) as cnt FROM t4 GROUP BY y ORDER BY cnt;
1.200 + }
1.201 +} {1 1 1 1 1 1 5 5}
1.202 +
1.203 +# See ticket #3324.
1.204 +#
1.205 +do_test select5-8.1 {
1.206 + execsql {
1.207 + CREATE TABLE t8a(a,b);
1.208 + CREATE TABLE t8b(x);
1.209 + INSERT INTO t8a VALUES('one', 1);
1.210 + INSERT INTO t8a VALUES('one', 2);
1.211 + INSERT INTO t8a VALUES('two', 3);
1.212 + INSERT INTO t8a VALUES('one', NULL);
1.213 + INSERT INTO t8b(rowid,x) VALUES(1,111);
1.214 + INSERT INTO t8b(rowid,x) VALUES(2,222);
1.215 + INSERT INTO t8b(rowid,x) VALUES(3,333);
1.216 + SELECT a, count(b) FROM t8a, t8b WHERE b=t8b.rowid GROUP BY a ORDER BY a;
1.217 + }
1.218 +} {one 2 two 1}
1.219 +do_test select5-8.2 {
1.220 + execsql {
1.221 + SELECT a, count(b) FROM t8a, t8b WHERE b=+t8b.rowid GROUP BY a ORDER BY a;
1.222 + }
1.223 +} {one 2 two 1}
1.224 +do_test select5-8.3 {
1.225 + execsql {
1.226 + SELECT t8a.a, count(t8a.b) FROM t8a, t8b WHERE t8a.b=t8b.rowid
1.227 + GROUP BY 1 ORDER BY 1;
1.228 + }
1.229 +} {one 2 two 1}
1.230 +do_test select5-8.4 {
1.231 + execsql {
1.232 + SELECT a, count(*) FROM t8a, t8b WHERE b=+t8b.rowid GROUP BY a ORDER BY a;
1.233 + }
1.234 +} {one 2 two 1}
1.235 +do_test select5-8.5 {
1.236 + execsql {
1.237 + SELECT a, count(b) FROM t8a, t8b WHERE b<x GROUP BY a ORDER BY a;
1.238 + }
1.239 +} {one 6 two 3}
1.240 +do_test select5-8.6 {
1.241 + execsql {
1.242 + SELECT a, count(t8a.b) FROM t8a, t8b WHERE b=t8b.rowid
1.243 + GROUP BY a ORDER BY 2;
1.244 + }
1.245 +} {two 1 one 2}
1.246 +do_test select5-8.7 {
1.247 + execsql {
1.248 + SELECT a, count(b) FROM t8a, t8b GROUP BY a ORDER BY 2;
1.249 + }
1.250 +} {two 3 one 6}
1.251 +do_test select5-8.8 {
1.252 + execsql {
1.253 + SELECT a, count(*) FROM t8a, t8b GROUP BY a ORDER BY 2;
1.254 + }
1.255 +} {two 3 one 9}
1.256 +
1.257 +
1.258 +
1.259 +
1.260 +finish_test