1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/select3.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,264 @@
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: select3.test,v 1.23 2008/01/16 18:20:42 danielk1977 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 +do_test select3-1.0 {
1.26 + execsql {
1.27 + CREATE TABLE t1(n int, log int);
1.28 + BEGIN;
1.29 + }
1.30 + for {set i 1} {$i<32} {incr i} {
1.31 + for {set j 0} {(1<<$j)<$i} {incr j} {}
1.32 + execsql "INSERT INTO t1 VALUES($i,$j)"
1.33 + }
1.34 + execsql {
1.35 + COMMIT
1.36 + }
1.37 + execsql {SELECT DISTINCT log FROM t1 ORDER BY log}
1.38 +} {0 1 2 3 4 5}
1.39 +
1.40 +# Basic aggregate functions.
1.41 +#
1.42 +do_test select3-1.1 {
1.43 + execsql {SELECT count(*) FROM t1}
1.44 +} {31}
1.45 +do_test select3-1.2 {
1.46 + execsql {
1.47 + SELECT min(n),min(log),max(n),max(log),sum(n),sum(log),avg(n),avg(log)
1.48 + FROM t1
1.49 + }
1.50 +} {1 0 31 5 496 124 16.0 4.0}
1.51 +do_test select3-1.3 {
1.52 + execsql {SELECT max(n)/avg(n), max(log)/avg(log) FROM t1}
1.53 +} {1.9375 1.25}
1.54 +
1.55 +# Try some basic GROUP BY clauses
1.56 +#
1.57 +do_test select3-2.1 {
1.58 + execsql {SELECT log, count(*) FROM t1 GROUP BY log ORDER BY log}
1.59 +} {0 1 1 1 2 2 3 4 4 8 5 15}
1.60 +do_test select3-2.2 {
1.61 + execsql {SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log}
1.62 +} {0 1 1 2 2 3 3 5 4 9 5 17}
1.63 +do_test select3-2.3.1 {
1.64 + execsql {SELECT log, avg(n) FROM t1 GROUP BY log ORDER BY log}
1.65 +} {0 1.0 1 2.0 2 3.5 3 6.5 4 12.5 5 24.0}
1.66 +do_test select3-2.3.2 {
1.67 + execsql {SELECT log, avg(n)+1 FROM t1 GROUP BY log ORDER BY log}
1.68 +} {0 2.0 1 3.0 2 4.5 3 7.5 4 13.5 5 25.0}
1.69 +do_test select3-2.4 {
1.70 + execsql {SELECT log, avg(n)-min(n) FROM t1 GROUP BY log ORDER BY log}
1.71 +} {0 0.0 1 0.0 2 0.5 3 1.5 4 3.5 5 7.0}
1.72 +do_test select3-2.5 {
1.73 + execsql {SELECT log*2+1, avg(n)-min(n) FROM t1 GROUP BY log ORDER BY log}
1.74 +} {1 0.0 3 0.0 5 0.5 7 1.5 9 3.5 11 7.0}
1.75 +do_test select3-2.6 {
1.76 + execsql {
1.77 + SELECT log*2+1 as x, count(*) FROM t1 GROUP BY x ORDER BY x
1.78 + }
1.79 +} {1 1 3 1 5 2 7 4 9 8 11 15}
1.80 +do_test select3-2.7 {
1.81 + execsql {
1.82 + SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY y, x
1.83 + }
1.84 +} {1 1 3 1 5 2 7 4 9 8 11 15}
1.85 +do_test select3-2.8 {
1.86 + execsql {
1.87 + SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY 10-(x+y)
1.88 + }
1.89 +} {11 15 9 8 7 4 5 2 3 1 1 1}
1.90 +#do_test select3-2.9 {
1.91 +# catchsql {
1.92 +# SELECT log, count(*) FROM t1 GROUP BY 'x' ORDER BY log;
1.93 +# }
1.94 +#} {1 {GROUP BY terms must not be non-integer constants}}
1.95 +do_test select3-2.10 {
1.96 + catchsql {
1.97 + SELECT log, count(*) FROM t1 GROUP BY 0 ORDER BY log;
1.98 + }
1.99 +} {1 {1st GROUP BY term out of range - should be between 1 and 2}}
1.100 +do_test select3-2.11 {
1.101 + catchsql {
1.102 + SELECT log, count(*) FROM t1 GROUP BY 3 ORDER BY log;
1.103 + }
1.104 +} {1 {1st GROUP BY term out of range - should be between 1 and 2}}
1.105 +do_test select3-2.12 {
1.106 + catchsql {
1.107 + SELECT log, count(*) FROM t1 GROUP BY 1 ORDER BY log;
1.108 + }
1.109 +} {0 {0 1 1 1 2 2 3 4 4 8 5 15}}
1.110 +
1.111 +# Cannot have an empty GROUP BY
1.112 +do_test select3-2.13 {
1.113 + catchsql {
1.114 + SELECT log, count(*) FROM t1 GROUP BY ORDER BY log;
1.115 + }
1.116 +} {1 {near "ORDER": syntax error}}
1.117 +do_test select3-2.14 {
1.118 + catchsql {
1.119 + SELECT log, count(*) FROM t1 GROUP BY;
1.120 + }
1.121 +} {1 {near ";": syntax error}}
1.122 +
1.123 +# Cannot have a HAVING without a GROUP BY
1.124 +#
1.125 +do_test select3-3.1 {
1.126 + set v [catch {execsql {SELECT log, count(*) FROM t1 HAVING log>=4}} msg]
1.127 + lappend v $msg
1.128 +} {1 {a GROUP BY clause is required before HAVING}}
1.129 +
1.130 +# Toss in some HAVING clauses
1.131 +#
1.132 +do_test select3-4.1 {
1.133 + execsql {SELECT log, count(*) FROM t1 GROUP BY log HAVING log>=4 ORDER BY log}
1.134 +} {4 8 5 15}
1.135 +do_test select3-4.2 {
1.136 + execsql {
1.137 + SELECT log, count(*) FROM t1
1.138 + GROUP BY log
1.139 + HAVING count(*)>=4
1.140 + ORDER BY log
1.141 + }
1.142 +} {3 4 4 8 5 15}
1.143 +do_test select3-4.3 {
1.144 + execsql {
1.145 + SELECT log, count(*) FROM t1
1.146 + GROUP BY log
1.147 + HAVING count(*)>=4
1.148 + ORDER BY max(n)+0
1.149 + }
1.150 +} {3 4 4 8 5 15}
1.151 +do_test select3-4.4 {
1.152 + execsql {
1.153 + SELECT log AS x, count(*) AS y FROM t1
1.154 + GROUP BY x
1.155 + HAVING y>=4
1.156 + ORDER BY max(n)+0
1.157 + }
1.158 +} {3 4 4 8 5 15}
1.159 +do_test select3-4.5 {
1.160 + execsql {
1.161 + SELECT log AS x FROM t1
1.162 + GROUP BY x
1.163 + HAVING count(*)>=4
1.164 + ORDER BY max(n)+0
1.165 + }
1.166 +} {3 4 5}
1.167 +
1.168 +do_test select3-5.1 {
1.169 + execsql {
1.170 + SELECT log, count(*), avg(n), max(n+log*2) FROM t1
1.171 + GROUP BY log
1.172 + ORDER BY max(n+log*2)+0, avg(n)+0
1.173 + }
1.174 +} {0 1 1.0 1 1 1 2.0 4 2 2 3.5 8 3 4 6.5 14 4 8 12.5 24 5 15 24.0 41}
1.175 +do_test select3-5.2 {
1.176 + execsql {
1.177 + SELECT log, count(*), avg(n), max(n+log*2) FROM t1
1.178 + GROUP BY log
1.179 + ORDER BY max(n+log*2)+0, min(log,avg(n))+0
1.180 + }
1.181 +} {0 1 1.0 1 1 1 2.0 4 2 2 3.5 8 3 4 6.5 14 4 8 12.5 24 5 15 24.0 41}
1.182 +
1.183 +# Test sorting of GROUP BY results in the presence of an index
1.184 +# on the GROUP BY column.
1.185 +#
1.186 +do_test select3-6.1 {
1.187 + execsql {
1.188 + SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log;
1.189 + }
1.190 +} {0 1 1 2 2 3 3 5 4 9 5 17}
1.191 +do_test select3-6.2 {
1.192 + execsql {
1.193 + SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log DESC;
1.194 + }
1.195 +} {5 17 4 9 3 5 2 3 1 2 0 1}
1.196 +do_test select3-6.3 {
1.197 + execsql {
1.198 + SELECT log, min(n) FROM t1 GROUP BY log ORDER BY 1;
1.199 + }
1.200 +} {0 1 1 2 2 3 3 5 4 9 5 17}
1.201 +do_test select3-6.4 {
1.202 + execsql {
1.203 + SELECT log, min(n) FROM t1 GROUP BY log ORDER BY 1 DESC;
1.204 + }
1.205 +} {5 17 4 9 3 5 2 3 1 2 0 1}
1.206 +do_test select3-6.5 {
1.207 + execsql {
1.208 + CREATE INDEX i1 ON t1(log);
1.209 + SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log;
1.210 + }
1.211 +} {0 1 1 2 2 3 3 5 4 9 5 17}
1.212 +do_test select3-6.6 {
1.213 + execsql {
1.214 + SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log DESC;
1.215 + }
1.216 +} {5 17 4 9 3 5 2 3 1 2 0 1}
1.217 +do_test select3-6.7 {
1.218 + execsql {
1.219 + SELECT log, min(n) FROM t1 GROUP BY log ORDER BY 1;
1.220 + }
1.221 +} {0 1 1 2 2 3 3 5 4 9 5 17}
1.222 +do_test select3-6.8 {
1.223 + execsql {
1.224 + SELECT log, min(n) FROM t1 GROUP BY log ORDER BY 1 DESC;
1.225 + }
1.226 +} {5 17 4 9 3 5 2 3 1 2 0 1}
1.227 +
1.228 +# Sometimes an aggregate query can return no rows at all.
1.229 +#
1.230 +do_test select3-7.1 {
1.231 + execsql {
1.232 + CREATE TABLE t2(a,b);
1.233 + INSERT INTO t2 VALUES(1,2);
1.234 + SELECT a, sum(b) FROM t2 WHERE b=5 GROUP BY a;
1.235 + }
1.236 +} {}
1.237 +do_test select3-7.2 {
1.238 + execsql {
1.239 + SELECT a, sum(b) FROM t2 WHERE b=5;
1.240 + }
1.241 +} {{} {}}
1.242 +
1.243 +# If a table column is of type REAL but we are storing integer values
1.244 +# in it, the values are stored as integers to take up less space. The
1.245 +# values are converted by to REAL as they are read out of the table.
1.246 +# Make sure the GROUP BY clause does this conversion correctly.
1.247 +# Ticket #2251.
1.248 +#
1.249 +do_test select3-8.1 {
1.250 + execsql {
1.251 + CREATE TABLE A (
1.252 + A1 DOUBLE,
1.253 + A2 VARCHAR COLLATE NOCASE,
1.254 + A3 DOUBLE
1.255 + );
1.256 + INSERT INTO A VALUES(39136,'ABC',1201900000);
1.257 + INSERT INTO A VALUES(39136,'ABC',1207000000);
1.258 + SELECT typeof(sum(a3)) FROM a;
1.259 + }
1.260 +} {real}
1.261 +do_test select3-8.2 {
1.262 + execsql {
1.263 + SELECT typeof(sum(a3)) FROM a GROUP BY a1;
1.264 + }
1.265 +} {real}
1.266 +
1.267 +finish_test