1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/minmax2.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,387 @@
1.4 +# 2007 July 17
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 SELECT statements that contain
1.16 +# aggregate min() and max() functions and which are handled as
1.17 +# as a special case. This file makes sure that the min/max
1.18 +# optimization works right in the presence of descending
1.19 +# indices. Ticket #2514.
1.20 +#
1.21 +# $Id: minmax2.test,v 1.2 2008/01/05 17:39:30 danielk1977 Exp $
1.22 +
1.23 +set testdir [file dirname $argv0]
1.24 +source $testdir/tester.tcl
1.25 +
1.26 +do_test minmax2-1.0 {
1.27 + execsql {
1.28 + PRAGMA legacy_file_format=0;
1.29 + BEGIN;
1.30 + CREATE TABLE t1(x, y);
1.31 + INSERT INTO t1 VALUES(1,1);
1.32 + INSERT INTO t1 VALUES(2,2);
1.33 + INSERT INTO t1 VALUES(3,2);
1.34 + INSERT INTO t1 VALUES(4,3);
1.35 + INSERT INTO t1 VALUES(5,3);
1.36 + INSERT INTO t1 VALUES(6,3);
1.37 + INSERT INTO t1 VALUES(7,3);
1.38 + INSERT INTO t1 VALUES(8,4);
1.39 + INSERT INTO t1 VALUES(9,4);
1.40 + INSERT INTO t1 VALUES(10,4);
1.41 + INSERT INTO t1 VALUES(11,4);
1.42 + INSERT INTO t1 VALUES(12,4);
1.43 + INSERT INTO t1 VALUES(13,4);
1.44 + INSERT INTO t1 VALUES(14,4);
1.45 + INSERT INTO t1 VALUES(15,4);
1.46 + INSERT INTO t1 VALUES(16,5);
1.47 + INSERT INTO t1 VALUES(17,5);
1.48 + INSERT INTO t1 VALUES(18,5);
1.49 + INSERT INTO t1 VALUES(19,5);
1.50 + INSERT INTO t1 VALUES(20,5);
1.51 + COMMIT;
1.52 + SELECT DISTINCT y FROM t1 ORDER BY y;
1.53 + }
1.54 +} {1 2 3 4 5}
1.55 +
1.56 +do_test minmax2-1.1 {
1.57 + set sqlite_search_count 0
1.58 + execsql {SELECT min(x) FROM t1}
1.59 +} {1}
1.60 +do_test minmax2-1.2 {
1.61 + set sqlite_search_count
1.62 +} {19}
1.63 +do_test minmax2-1.3 {
1.64 + set sqlite_search_count 0
1.65 + execsql {SELECT max(x) FROM t1}
1.66 +} {20}
1.67 +do_test minmax2-1.4 {
1.68 + set sqlite_search_count
1.69 +} {19}
1.70 +do_test minmax2-1.5 {
1.71 + execsql {CREATE INDEX t1i1 ON t1(x DESC)}
1.72 + set sqlite_search_count 0
1.73 + execsql {SELECT min(x) FROM t1}
1.74 +} {1}
1.75 +do_test minmax2-1.6 {
1.76 + set sqlite_search_count
1.77 +} {1}
1.78 +do_test minmax2-1.7 {
1.79 + set sqlite_search_count 0
1.80 + execsql {SELECT max(x) FROM t1}
1.81 +} {20}
1.82 +do_test minmax2-1.8 {
1.83 + set sqlite_search_count
1.84 +} {0}
1.85 +do_test minmax2-1.9 {
1.86 + set sqlite_search_count 0
1.87 + execsql {SELECT max(y) FROM t1}
1.88 +} {5}
1.89 +do_test minmax2-1.10 {
1.90 + set sqlite_search_count
1.91 +} {19}
1.92 +
1.93 +do_test minmax2-2.0 {
1.94 + execsql {
1.95 + CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
1.96 + INSERT INTO t2 SELECT * FROM t1;
1.97 + }
1.98 + set sqlite_search_count 0
1.99 + execsql {SELECT min(a) FROM t2}
1.100 +} {1}
1.101 +do_test minmax2-2.1 {
1.102 + set sqlite_search_count
1.103 +} {0}
1.104 +do_test minmax2-2.2 {
1.105 + set sqlite_search_count 0
1.106 + execsql {SELECT max(a) FROM t2}
1.107 +} {20}
1.108 +do_test minmax2-2.3 {
1.109 + set sqlite_search_count
1.110 +} {0}
1.111 +
1.112 +do_test minmax2-3.0 {
1.113 + ifcapable subquery {
1.114 + execsql {INSERT INTO t2 VALUES((SELECT max(a) FROM t2)+1,999)}
1.115 + } else {
1.116 + db function max_a_t2 {execsql {SELECT max(a) FROM t2}}
1.117 + execsql {INSERT INTO t2 VALUES(max_a_t2()+1,999)}
1.118 + }
1.119 + set sqlite_search_count 0
1.120 + execsql {SELECT max(a) FROM t2}
1.121 +} {21}
1.122 +do_test minmax2-3.1 {
1.123 + set sqlite_search_count
1.124 +} {0}
1.125 +do_test minmax2-3.2 {
1.126 + ifcapable subquery {
1.127 + execsql {INSERT INTO t2 VALUES((SELECT max(a) FROM t2)+1,999)}
1.128 + } else {
1.129 + db function max_a_t2 {execsql {SELECT max(a) FROM t2}}
1.130 + execsql {INSERT INTO t2 VALUES(max_a_t2()+1,999)}
1.131 + }
1.132 + set sqlite_search_count 0
1.133 + ifcapable subquery {
1.134 + execsql { SELECT b FROM t2 WHERE a=(SELECT max(a) FROM t2) }
1.135 + } else {
1.136 + execsql { SELECT b FROM t2 WHERE a=max_a_t2() }
1.137 + }
1.138 +} {999}
1.139 +do_test minmax2-3.3 {
1.140 + set sqlite_search_count
1.141 +} {0}
1.142 +
1.143 +ifcapable {compound && subquery} {
1.144 + do_test minmax2-4.1 {
1.145 + execsql {
1.146 + SELECT coalesce(min(x+0),-1), coalesce(max(x+0),-1) FROM
1.147 + (SELECT * FROM t1 UNION SELECT NULL as 'x', NULL as 'y')
1.148 + }
1.149 + } {1 20}
1.150 + do_test minmax2-4.2 {
1.151 + execsql {
1.152 + SELECT y, coalesce(sum(x),0) FROM
1.153 + (SELECT null AS x, y+1 AS y FROM t1 UNION SELECT * FROM t1)
1.154 + GROUP BY y ORDER BY y;
1.155 + }
1.156 + } {1 1 2 5 3 22 4 92 5 90 6 0}
1.157 + do_test minmax2-4.3 {
1.158 + execsql {
1.159 + SELECT y, count(x), count(*) FROM
1.160 + (SELECT null AS x, y+1 AS y FROM t1 UNION SELECT * FROM t1)
1.161 + GROUP BY y ORDER BY y;
1.162 + }
1.163 + } {1 1 1 2 2 3 3 4 5 4 8 9 5 5 6 6 0 1}
1.164 +} ;# ifcapable compound
1.165 +
1.166 +# Make sure the min(x) and max(x) optimizations work on empty tables
1.167 +# including empty tables with indices. Ticket #296.
1.168 +#
1.169 +do_test minmax2-5.1 {
1.170 + execsql {
1.171 + CREATE TABLE t3(x INTEGER UNIQUE NOT NULL);
1.172 + SELECT coalesce(min(x),999) FROM t3;
1.173 + }
1.174 +} {999}
1.175 +do_test minmax2-5.2 {
1.176 + execsql {
1.177 + SELECT coalesce(min(rowid),999) FROM t3;
1.178 + }
1.179 +} {999}
1.180 +do_test minmax2-5.3 {
1.181 + execsql {
1.182 + SELECT coalesce(max(x),999) FROM t3;
1.183 + }
1.184 +} {999}
1.185 +do_test minmax2-5.4 {
1.186 + execsql {
1.187 + SELECT coalesce(max(rowid),999) FROM t3;
1.188 + }
1.189 +} {999}
1.190 +do_test minmax2-5.5 {
1.191 + execsql {
1.192 + SELECT coalesce(max(rowid),999) FROM t3 WHERE rowid<25;
1.193 + }
1.194 +} {999}
1.195 +
1.196 +# Make sure the min(x) and max(x) optimizations work when there
1.197 +# is a LIMIT clause. Ticket #396.
1.198 +#
1.199 +do_test minmax2-6.1 {
1.200 + execsql {
1.201 + SELECT min(a) FROM t2 LIMIT 1
1.202 + }
1.203 +} {1}
1.204 +do_test minmax2-6.2 {
1.205 + execsql {
1.206 + SELECT max(a) FROM t2 LIMIT 3
1.207 + }
1.208 +} {22}
1.209 +do_test minmax2-6.3 {
1.210 + execsql {
1.211 + SELECT min(a) FROM t2 LIMIT 0,100
1.212 + }
1.213 +} {1}
1.214 +do_test minmax2-6.4 {
1.215 + execsql {
1.216 + SELECT max(a) FROM t2 LIMIT 1,100
1.217 + }
1.218 +} {}
1.219 +do_test minmax2-6.5 {
1.220 + execsql {
1.221 + SELECT min(x) FROM t3 LIMIT 1
1.222 + }
1.223 +} {{}}
1.224 +do_test minmax2-6.6 {
1.225 + execsql {
1.226 + SELECT max(x) FROM t3 LIMIT 0
1.227 + }
1.228 +} {}
1.229 +do_test minmax2-6.7 {
1.230 + execsql {
1.231 + SELECT max(a) FROM t2 LIMIT 0
1.232 + }
1.233 +} {}
1.234 +
1.235 +# Make sure the max(x) and min(x) optimizations work for nested
1.236 +# queries. Ticket #587.
1.237 +#
1.238 +do_test minmax2-7.1 {
1.239 + execsql {
1.240 + SELECT max(x) FROM t1;
1.241 + }
1.242 +} 20
1.243 +ifcapable subquery {
1.244 + do_test minmax2-7.2 {
1.245 + execsql {
1.246 + SELECT * FROM (SELECT max(x) FROM t1);
1.247 + }
1.248 + } 20
1.249 +}
1.250 +do_test minmax2-7.3 {
1.251 + execsql {
1.252 + SELECT min(x) FROM t1;
1.253 + }
1.254 +} 1
1.255 +ifcapable subquery {
1.256 + do_test minmax2-7.4 {
1.257 + execsql {
1.258 + SELECT * FROM (SELECT min(x) FROM t1);
1.259 + }
1.260 + } 1
1.261 +}
1.262 +
1.263 +# Make sure min(x) and max(x) work correctly when the datatype is
1.264 +# TEXT instead of NUMERIC. Ticket #623.
1.265 +#
1.266 +do_test minmax2-8.1 {
1.267 + execsql {
1.268 + CREATE TABLE t4(a TEXT);
1.269 + INSERT INTO t4 VALUES('1234');
1.270 + INSERT INTO t4 VALUES('234');
1.271 + INSERT INTO t4 VALUES('34');
1.272 + SELECT min(a), max(a) FROM t4;
1.273 + }
1.274 +} {1234 34}
1.275 +do_test minmax2-8.2 {
1.276 + execsql {
1.277 + CREATE TABLE t5(a INTEGER);
1.278 + INSERT INTO t5 VALUES('1234');
1.279 + INSERT INTO t5 VALUES('234');
1.280 + INSERT INTO t5 VALUES('34');
1.281 + SELECT min(a), max(a) FROM t5;
1.282 + }
1.283 +} {34 1234}
1.284 +
1.285 +# Ticket #658: Test the min()/max() optimization when the FROM clause
1.286 +# is a subquery.
1.287 +#
1.288 +ifcapable {compound && subquery} {
1.289 + do_test minmax2-9.1 {
1.290 + execsql {
1.291 + SELECT max(rowid) FROM (
1.292 + SELECT max(rowid) FROM t4 UNION SELECT max(rowid) FROM t5
1.293 + )
1.294 + }
1.295 + } {1}
1.296 + do_test minmax2-9.2 {
1.297 + execsql {
1.298 + SELECT max(rowid) FROM (
1.299 + SELECT max(rowid) FROM t4 EXCEPT SELECT max(rowid) FROM t5
1.300 + )
1.301 + }
1.302 + } {{}}
1.303 +} ;# ifcapable compound&&subquery
1.304 +
1.305 +# If there is a NULL in an aggregate max() or min(), ignore it. An
1.306 +# aggregate min() or max() will only return NULL if all values are NULL.
1.307 +#
1.308 +do_test minmax2-10.1 {
1.309 + execsql {
1.310 + CREATE TABLE t6(x);
1.311 + INSERT INTO t6 VALUES(1);
1.312 + INSERT INTO t6 VALUES(2);
1.313 + INSERT INTO t6 VALUES(NULL);
1.314 + SELECT coalesce(min(x),-1) FROM t6;
1.315 + }
1.316 +} {1}
1.317 +do_test minmax2-10.2 {
1.318 + execsql {
1.319 + SELECT max(x) FROM t6;
1.320 + }
1.321 +} {2}
1.322 +do_test minmax2-10.3 {
1.323 + execsql {
1.324 + CREATE INDEX i6 ON t6(x DESC);
1.325 + SELECT coalesce(min(x),-1) FROM t6;
1.326 + }
1.327 +} {1}
1.328 +do_test minmax2-10.4 {
1.329 + execsql {
1.330 + SELECT max(x) FROM t6;
1.331 + }
1.332 +} {2}
1.333 +do_test minmax2-10.5 {
1.334 + execsql {
1.335 + DELETE FROM t6 WHERE x NOT NULL;
1.336 + SELECT count(*) FROM t6;
1.337 + }
1.338 +} 1
1.339 +do_test minmax2-10.6 {
1.340 + execsql {
1.341 + SELECT count(x) FROM t6;
1.342 + }
1.343 +} 0
1.344 +ifcapable subquery {
1.345 + do_test minmax2-10.7 {
1.346 + execsql {
1.347 + SELECT (SELECT min(x) FROM t6), (SELECT max(x) FROM t6);
1.348 + }
1.349 + } {{} {}}
1.350 +}
1.351 +do_test minmax2-10.8 {
1.352 + execsql {
1.353 + SELECT min(x), max(x) FROM t6;
1.354 + }
1.355 +} {{} {}}
1.356 +do_test minmax2-10.9 {
1.357 + execsql {
1.358 + INSERT INTO t6 SELECT * FROM t6;
1.359 + INSERT INTO t6 SELECT * FROM t6;
1.360 + INSERT INTO t6 SELECT * FROM t6;
1.361 + INSERT INTO t6 SELECT * FROM t6;
1.362 + INSERT INTO t6 SELECT * FROM t6;
1.363 + INSERT INTO t6 SELECT * FROM t6;
1.364 + INSERT INTO t6 SELECT * FROM t6;
1.365 + INSERT INTO t6 SELECT * FROM t6;
1.366 + INSERT INTO t6 SELECT * FROM t6;
1.367 + INSERT INTO t6 SELECT * FROM t6;
1.368 + SELECT count(*) FROM t6;
1.369 + }
1.370 +} 1024
1.371 +do_test minmax2-10.10 {
1.372 + execsql {
1.373 + SELECT count(x) FROM t6;
1.374 + }
1.375 +} 0
1.376 +ifcapable subquery {
1.377 + do_test minmax2-10.11 {
1.378 + execsql {
1.379 + SELECT (SELECT min(x) FROM t6), (SELECT max(x) FROM t6);
1.380 + }
1.381 + } {{} {}}
1.382 +}
1.383 +do_test minmax2-10.12 {
1.384 + execsql {
1.385 + SELECT min(x), max(x) FROM t6;
1.386 + }
1.387 +} {{} {}}
1.388 +
1.389 +
1.390 +finish_test