1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/minmax.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,542 @@
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 SELECT statements that contain
1.16 +# aggregate min() and max() functions and which are handled as
1.17 +# as a special case.
1.18 +#
1.19 +# $Id: minmax.test,v 1.21 2008/07/08 18:05:26 drh Exp $
1.20 +
1.21 +set testdir [file dirname $argv0]
1.22 +source $testdir/tester.tcl
1.23 +
1.24 +do_test minmax-1.0 {
1.25 + execsql {
1.26 + BEGIN;
1.27 + CREATE TABLE t1(x, y);
1.28 + INSERT INTO t1 VALUES(1,1);
1.29 + INSERT INTO t1 VALUES(2,2);
1.30 + INSERT INTO t1 VALUES(3,2);
1.31 + INSERT INTO t1 VALUES(4,3);
1.32 + INSERT INTO t1 VALUES(5,3);
1.33 + INSERT INTO t1 VALUES(6,3);
1.34 + INSERT INTO t1 VALUES(7,3);
1.35 + INSERT INTO t1 VALUES(8,4);
1.36 + INSERT INTO t1 VALUES(9,4);
1.37 + INSERT INTO t1 VALUES(10,4);
1.38 + INSERT INTO t1 VALUES(11,4);
1.39 + INSERT INTO t1 VALUES(12,4);
1.40 + INSERT INTO t1 VALUES(13,4);
1.41 + INSERT INTO t1 VALUES(14,4);
1.42 + INSERT INTO t1 VALUES(15,4);
1.43 + INSERT INTO t1 VALUES(16,5);
1.44 + INSERT INTO t1 VALUES(17,5);
1.45 + INSERT INTO t1 VALUES(18,5);
1.46 + INSERT INTO t1 VALUES(19,5);
1.47 + INSERT INTO t1 VALUES(20,5);
1.48 + COMMIT;
1.49 + SELECT DISTINCT y FROM t1 ORDER BY y;
1.50 + }
1.51 +} {1 2 3 4 5}
1.52 +
1.53 +do_test minmax-1.1 {
1.54 + set sqlite_search_count 0
1.55 + execsql {SELECT min(x) FROM t1}
1.56 +} {1}
1.57 +do_test minmax-1.2 {
1.58 + set sqlite_search_count
1.59 +} {19}
1.60 +do_test minmax-1.3 {
1.61 + set sqlite_search_count 0
1.62 + execsql {SELECT max(x) FROM t1}
1.63 +} {20}
1.64 +do_test minmax-1.4 {
1.65 + set sqlite_search_count
1.66 +} {19}
1.67 +do_test minmax-1.5 {
1.68 + execsql {CREATE INDEX t1i1 ON t1(x)}
1.69 + set sqlite_search_count 0
1.70 + execsql {SELECT min(x) FROM t1}
1.71 +} {1}
1.72 +do_test minmax-1.6 {
1.73 + set sqlite_search_count
1.74 +} {1}
1.75 +do_test minmax-1.7 {
1.76 + set sqlite_search_count 0
1.77 + execsql {SELECT max(x) FROM t1}
1.78 +} {20}
1.79 +do_test minmax-1.8 {
1.80 + set sqlite_search_count
1.81 +} {0}
1.82 +do_test minmax-1.9 {
1.83 + set sqlite_search_count 0
1.84 + execsql {SELECT max(y) FROM t1}
1.85 +} {5}
1.86 +do_test minmax-1.10 {
1.87 + set sqlite_search_count
1.88 +} {19}
1.89 +
1.90 +do_test minmax-1.21 {
1.91 + execsql {SELECT min(x) FROM t1 WHERE x=5}
1.92 +} {5}
1.93 +do_test minmax-1.22 {
1.94 + execsql {SELECT min(x) FROM t1 WHERE x>=5}
1.95 +} {5}
1.96 +do_test minmax-1.23 {
1.97 + execsql {SELECT min(x) FROM t1 WHERE x>=4.5}
1.98 +} {5}
1.99 +do_test minmax-1.24 {
1.100 + execsql {SELECT min(x) FROM t1 WHERE x<4.5}
1.101 +} {1}
1.102 +
1.103 +do_test minmax-2.0 {
1.104 + execsql {
1.105 + CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
1.106 + INSERT INTO t2 SELECT * FROM t1;
1.107 + }
1.108 + set sqlite_search_count 0
1.109 + execsql {SELECT min(a) FROM t2}
1.110 +} {1}
1.111 +do_test minmax-2.1 {
1.112 + set sqlite_search_count
1.113 +} {0}
1.114 +do_test minmax-2.2 {
1.115 + set sqlite_search_count 0
1.116 + execsql {SELECT max(a) FROM t2}
1.117 +} {20}
1.118 +do_test minmax-2.3 {
1.119 + set sqlite_search_count
1.120 +} {0}
1.121 +
1.122 +do_test minmax-3.0 {
1.123 + ifcapable subquery {
1.124 + execsql {INSERT INTO t2 VALUES((SELECT max(a) FROM t2)+1,999)}
1.125 + } else {
1.126 + db function max_a_t2 {execsql {SELECT max(a) FROM t2}}
1.127 + execsql {INSERT INTO t2 VALUES(max_a_t2()+1,999)}
1.128 + }
1.129 + set sqlite_search_count 0
1.130 + execsql {SELECT max(a) FROM t2}
1.131 +} {21}
1.132 +do_test minmax-3.1 {
1.133 + set sqlite_search_count
1.134 +} {0}
1.135 +do_test minmax-3.2 {
1.136 + ifcapable subquery {
1.137 + execsql {INSERT INTO t2 VALUES((SELECT max(a) FROM t2)+1,999)}
1.138 + } else {
1.139 + db function max_a_t2 {execsql {SELECT max(a) FROM t2}}
1.140 + execsql {INSERT INTO t2 VALUES(max_a_t2()+1,999)}
1.141 + }
1.142 + set sqlite_search_count 0
1.143 + ifcapable subquery {
1.144 + execsql { SELECT b FROM t2 WHERE a=(SELECT max(a) FROM t2) }
1.145 + } else {
1.146 + execsql { SELECT b FROM t2 WHERE a=max_a_t2() }
1.147 + }
1.148 +} {999}
1.149 +do_test minmax-3.3 {
1.150 + set sqlite_search_count
1.151 +} {0}
1.152 +
1.153 +ifcapable {compound && subquery} {
1.154 + do_test minmax-4.1 {
1.155 + execsql {
1.156 + SELECT coalesce(min(x+0),-1), coalesce(max(x+0),-1) FROM
1.157 + (SELECT * FROM t1 UNION SELECT NULL as 'x', NULL as 'y')
1.158 + }
1.159 + } {1 20}
1.160 + do_test minmax-4.2 {
1.161 + execsql {
1.162 + SELECT y, coalesce(sum(x),0) FROM
1.163 + (SELECT null AS x, y+1 AS y FROM t1 UNION SELECT * FROM t1)
1.164 + GROUP BY y ORDER BY y;
1.165 + }
1.166 + } {1 1 2 5 3 22 4 92 5 90 6 0}
1.167 + do_test minmax-4.3 {
1.168 + execsql {
1.169 + SELECT y, count(x), count(*) FROM
1.170 + (SELECT null AS x, y+1 AS y FROM t1 UNION SELECT * FROM t1)
1.171 + GROUP BY y ORDER BY y;
1.172 + }
1.173 + } {1 1 1 2 2 3 3 4 5 4 8 9 5 5 6 6 0 1}
1.174 +} ;# ifcapable compound
1.175 +
1.176 +# Make sure the min(x) and max(x) optimizations work on empty tables
1.177 +# including empty tables with indices. Ticket #296.
1.178 +#
1.179 +do_test minmax-5.1 {
1.180 + execsql {
1.181 + CREATE TABLE t3(x INTEGER UNIQUE NOT NULL);
1.182 + SELECT coalesce(min(x),999) FROM t3;
1.183 + }
1.184 +} {999}
1.185 +do_test minmax-5.2 {
1.186 + execsql {
1.187 + SELECT coalesce(min(rowid),999) FROM t3;
1.188 + }
1.189 +} {999}
1.190 +do_test minmax-5.3 {
1.191 + execsql {
1.192 + SELECT coalesce(max(x),999) FROM t3;
1.193 + }
1.194 +} {999}
1.195 +do_test minmax-5.4 {
1.196 + execsql {
1.197 + SELECT coalesce(max(rowid),999) FROM t3;
1.198 + }
1.199 +} {999}
1.200 +do_test minmax-5.5 {
1.201 + execsql {
1.202 + SELECT coalesce(max(rowid),999) FROM t3 WHERE rowid<25;
1.203 + }
1.204 +} {999}
1.205 +
1.206 +# Make sure the min(x) and max(x) optimizations work when there
1.207 +# is a LIMIT clause. Ticket #396.
1.208 +#
1.209 +do_test minmax-6.1 {
1.210 + execsql {
1.211 + SELECT min(a) FROM t2 LIMIT 1
1.212 + }
1.213 +} {1}
1.214 +do_test minmax-6.2 {
1.215 + execsql {
1.216 + SELECT max(a) FROM t2 LIMIT 3
1.217 + }
1.218 +} {22}
1.219 +do_test minmax-6.3 {
1.220 + execsql {
1.221 + SELECT min(a) FROM t2 LIMIT 0,100
1.222 + }
1.223 +} {1}
1.224 +do_test minmax-6.4 {
1.225 + execsql {
1.226 + SELECT max(a) FROM t2 LIMIT 1,100
1.227 + }
1.228 +} {}
1.229 +do_test minmax-6.5 {
1.230 + execsql {
1.231 + SELECT min(x) FROM t3 LIMIT 1
1.232 + }
1.233 +} {{}}
1.234 +do_test minmax-6.6 {
1.235 + execsql {
1.236 + SELECT max(x) FROM t3 LIMIT 0
1.237 + }
1.238 +} {}
1.239 +do_test minmax-6.7 {
1.240 + execsql {
1.241 + SELECT max(a) FROM t2 LIMIT 0
1.242 + }
1.243 +} {}
1.244 +
1.245 +# Make sure the max(x) and min(x) optimizations work for nested
1.246 +# queries. Ticket #587.
1.247 +#
1.248 +do_test minmax-7.1 {
1.249 + execsql {
1.250 + SELECT max(x) FROM t1;
1.251 + }
1.252 +} 20
1.253 +ifcapable subquery {
1.254 + do_test minmax-7.2 {
1.255 + execsql {
1.256 + SELECT * FROM (SELECT max(x) FROM t1);
1.257 + }
1.258 + } 20
1.259 +}
1.260 +do_test minmax-7.3 {
1.261 + execsql {
1.262 + SELECT min(x) FROM t1;
1.263 + }
1.264 +} 1
1.265 +ifcapable subquery {
1.266 + do_test minmax-7.4 {
1.267 + execsql {
1.268 + SELECT * FROM (SELECT min(x) FROM t1);
1.269 + }
1.270 + } 1
1.271 +}
1.272 +
1.273 +# Make sure min(x) and max(x) work correctly when the datatype is
1.274 +# TEXT instead of NUMERIC. Ticket #623.
1.275 +#
1.276 +do_test minmax-8.1 {
1.277 + execsql {
1.278 + CREATE TABLE t4(a TEXT);
1.279 + INSERT INTO t4 VALUES('1234');
1.280 + INSERT INTO t4 VALUES('234');
1.281 + INSERT INTO t4 VALUES('34');
1.282 + SELECT min(a), max(a) FROM t4;
1.283 + }
1.284 +} {1234 34}
1.285 +do_test minmax-8.2 {
1.286 + execsql {
1.287 + CREATE TABLE t5(a INTEGER);
1.288 + INSERT INTO t5 VALUES('1234');
1.289 + INSERT INTO t5 VALUES('234');
1.290 + INSERT INTO t5 VALUES('34');
1.291 + SELECT min(a), max(a) FROM t5;
1.292 + }
1.293 +} {34 1234}
1.294 +
1.295 +# Ticket #658: Test the min()/max() optimization when the FROM clause
1.296 +# is a subquery.
1.297 +#
1.298 +ifcapable {compound && subquery} {
1.299 + do_test minmax-9.1 {
1.300 + execsql {
1.301 + SELECT max(rowid) FROM (
1.302 + SELECT max(rowid) FROM t4 UNION SELECT max(rowid) FROM t5
1.303 + )
1.304 + }
1.305 + } {1}
1.306 + do_test minmax-9.2 {
1.307 + execsql {
1.308 + SELECT max(rowid) FROM (
1.309 + SELECT max(rowid) FROM t4 EXCEPT SELECT max(rowid) FROM t5
1.310 + )
1.311 + }
1.312 + } {{}}
1.313 +} ;# ifcapable compound&&subquery
1.314 +
1.315 +# If there is a NULL in an aggregate max() or min(), ignore it. An
1.316 +# aggregate min() or max() will only return NULL if all values are NULL.
1.317 +#
1.318 +do_test minmax-10.1 {
1.319 + execsql {
1.320 + CREATE TABLE t6(x);
1.321 + INSERT INTO t6 VALUES(1);
1.322 + INSERT INTO t6 VALUES(2);
1.323 + INSERT INTO t6 VALUES(NULL);
1.324 + SELECT coalesce(min(x),-1) FROM t6;
1.325 + }
1.326 +} {1}
1.327 +do_test minmax-10.2 {
1.328 + execsql {
1.329 + SELECT max(x) FROM t6;
1.330 + }
1.331 +} {2}
1.332 +do_test minmax-10.3 {
1.333 + execsql {
1.334 + CREATE INDEX i6 ON t6(x);
1.335 + SELECT coalesce(min(x),-1) FROM t6;
1.336 + }
1.337 +} {1}
1.338 +do_test minmax-10.4 {
1.339 + execsql {
1.340 + SELECT max(x) FROM t6;
1.341 + }
1.342 +} {2}
1.343 +do_test minmax-10.5 {
1.344 + execsql {
1.345 + DELETE FROM t6 WHERE x NOT NULL;
1.346 + SELECT count(*) FROM t6;
1.347 + }
1.348 +} 1
1.349 +do_test minmax-10.6 {
1.350 + execsql {
1.351 + SELECT count(x) FROM t6;
1.352 + }
1.353 +} 0
1.354 +ifcapable subquery {
1.355 + do_test minmax-10.7 {
1.356 + execsql {
1.357 + SELECT (SELECT min(x) FROM t6), (SELECT max(x) FROM t6);
1.358 + }
1.359 + } {{} {}}
1.360 +}
1.361 +do_test minmax-10.8 {
1.362 + execsql {
1.363 + SELECT min(x), max(x) FROM t6;
1.364 + }
1.365 +} {{} {}}
1.366 +do_test minmax-10.9 {
1.367 + execsql {
1.368 + INSERT INTO t6 SELECT * FROM t6;
1.369 + INSERT INTO t6 SELECT * FROM t6;
1.370 + INSERT INTO t6 SELECT * FROM t6;
1.371 + INSERT INTO t6 SELECT * FROM t6;
1.372 + INSERT INTO t6 SELECT * FROM t6;
1.373 + INSERT INTO t6 SELECT * FROM t6;
1.374 + INSERT INTO t6 SELECT * FROM t6;
1.375 + INSERT INTO t6 SELECT * FROM t6;
1.376 + INSERT INTO t6 SELECT * FROM t6;
1.377 + INSERT INTO t6 SELECT * FROM t6;
1.378 + SELECT count(*) FROM t6;
1.379 + }
1.380 +} 1024
1.381 +do_test minmax-10.10 {
1.382 + execsql {
1.383 + SELECT count(x) FROM t6;
1.384 + }
1.385 +} 0
1.386 +ifcapable subquery {
1.387 + do_test minmax-10.11 {
1.388 + execsql {
1.389 + SELECT (SELECT min(x) FROM t6), (SELECT max(x) FROM t6);
1.390 + }
1.391 + } {{} {}}
1.392 +}
1.393 +do_test minmax-10.12 {
1.394 + execsql {
1.395 + SELECT min(x), max(x) FROM t6;
1.396 + }
1.397 +} {{} {}}
1.398 +
1.399 +
1.400 +do_test minmax-11.1 {
1.401 + execsql {
1.402 + CREATE INDEX t1i2 ON t1(y,x);
1.403 + SELECT min(x) FROM t1 WHERE y=5;
1.404 + }
1.405 +} {16}
1.406 +do_test minmax-11.2 {
1.407 + execsql {
1.408 + SELECT max(x) FROM t1 WHERE y=5;
1.409 + }
1.410 +} {20}
1.411 +do_test minmax-11.3 {
1.412 + execsql {
1.413 + SELECT min(x) FROM t1 WHERE y=6;
1.414 + }
1.415 +} {{}}
1.416 +do_test minmax-11.4 {
1.417 + execsql {
1.418 + SELECT max(x) FROM t1 WHERE y=6;
1.419 + }
1.420 +} {{}}
1.421 +do_test minmax-11.5 {
1.422 + execsql {
1.423 + SELECT min(x) FROM t1 WHERE y=1;
1.424 + }
1.425 +} {1}
1.426 +do_test minmax-11.6 {
1.427 + execsql {
1.428 + SELECT max(x) FROM t1 WHERE y=1;
1.429 + }
1.430 +} {1}
1.431 +do_test minmax-11.7 {
1.432 + execsql {
1.433 + SELECT min(x) FROM t1 WHERE y=0;
1.434 + }
1.435 +} {{}}
1.436 +do_test minmax-11.8 {
1.437 + execsql {
1.438 + SELECT max(x) FROM t1 WHERE y=0;
1.439 + }
1.440 +} {{}}
1.441 +do_test minmax-11.9 {
1.442 + execsql {
1.443 + SELECT min(x) FROM t1 WHERE y=5 AND x>=17.5;
1.444 + }
1.445 +} {18}
1.446 +do_test minmax-11.10 {
1.447 + execsql {
1.448 + SELECT max(x) FROM t1 WHERE y=5 AND x>=17.5;
1.449 + }
1.450 +} {20}
1.451 +
1.452 +do_test minmax-12.1 {
1.453 + execsql {
1.454 + CREATE TABLE t7(a,b,c);
1.455 + INSERT INTO t7 SELECT y, x, x*y FROM t1;
1.456 + INSERT INTO t7 SELECT y, x, x*y+1000 FROM t1;
1.457 + CREATE INDEX t7i1 ON t7(a,b,c);
1.458 + SELECT min(a) FROM t7;
1.459 + }
1.460 +} {1}
1.461 +do_test minmax-12.2 {
1.462 + execsql {
1.463 + SELECT max(a) FROM t7;
1.464 + }
1.465 +} {5}
1.466 +do_test minmax-12.3 {
1.467 + execsql {
1.468 + SELECT max(a) FROM t7 WHERE a=5;
1.469 + }
1.470 +} {5}
1.471 +do_test minmax-12.4 {
1.472 + execsql {
1.473 + SELECT min(b) FROM t7 WHERE a=5;
1.474 + }
1.475 +} {16}
1.476 +do_test minmax-12.5 {
1.477 + execsql {
1.478 + SELECT max(b) FROM t7 WHERE a=5;
1.479 + }
1.480 +} {20}
1.481 +do_test minmax-12.6 {
1.482 + execsql {
1.483 + SELECT min(b) FROM t7 WHERE a=4;
1.484 + }
1.485 +} {8}
1.486 +do_test minmax-12.7 {
1.487 + execsql {
1.488 + SELECT max(b) FROM t7 WHERE a=4;
1.489 + }
1.490 +} {15}
1.491 +do_test minmax-12.8 {
1.492 + execsql {
1.493 + SELECT min(c) FROM t7 WHERE a=4 AND b=10;
1.494 + }
1.495 +} {40}
1.496 +do_test minmax-12.9 {
1.497 + execsql {
1.498 + SELECT max(c) FROM t7 WHERE a=4 AND b=10;
1.499 + }
1.500 +} {1040}
1.501 +do_test minmax-12.10 {
1.502 + execsql {
1.503 + SELECT min(rowid) FROM t7;
1.504 + }
1.505 +} {1}
1.506 +do_test minmax-12.11 {
1.507 + execsql {
1.508 + SELECT max(rowid) FROM t7;
1.509 + }
1.510 +} {40}
1.511 +do_test minmax-12.12 {
1.512 + execsql {
1.513 + SELECT min(rowid) FROM t7 WHERE a=3;
1.514 + }
1.515 +} {4}
1.516 +do_test minmax-12.13 {
1.517 + execsql {
1.518 + SELECT max(rowid) FROM t7 WHERE a=3;
1.519 + }
1.520 +} {27}
1.521 +do_test minmax-12.14 {
1.522 + execsql {
1.523 + SELECT min(rowid) FROM t7 WHERE a=3 AND b=5;
1.524 + }
1.525 +} {5}
1.526 +do_test minmax-12.15 {
1.527 + execsql {
1.528 + SELECT max(rowid) FROM t7 WHERE a=3 AND b=5;
1.529 + }
1.530 +} {25}
1.531 +do_test minmax-12.16 {
1.532 + execsql {
1.533 + SELECT min(rowid) FROM t7 WHERE a=3 AND b=5 AND c=1015;
1.534 + }
1.535 +} {25}
1.536 +do_test minmax-12.17 {
1.537 + execsql {
1.538 + SELECT max(rowid) FROM t7 WHERE a=3 AND b=5 AND c=15;
1.539 + }
1.540 +} {5}
1.541 +
1.542 +
1.543 +
1.544 +
1.545 +finish_test