1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/subquery.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,494 @@
1.4 +# 2005 January 19
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 script is testing correlated subqueries
1.16 +#
1.17 +# $Id: subquery.test,v 1.16 2008/07/10 00:32:42 drh Exp $
1.18 +#
1.19 +
1.20 +set testdir [file dirname $argv0]
1.21 +source $testdir/tester.tcl
1.22 +
1.23 +ifcapable !subquery {
1.24 + finish_test
1.25 + return
1.26 +}
1.27 +
1.28 +do_test subquery-1.1 {
1.29 + execsql {
1.30 + BEGIN;
1.31 + CREATE TABLE t1(a,b);
1.32 + INSERT INTO t1 VALUES(1,2);
1.33 + INSERT INTO t1 VALUES(3,4);
1.34 + INSERT INTO t1 VALUES(5,6);
1.35 + INSERT INTO t1 VALUES(7,8);
1.36 + CREATE TABLE t2(x,y);
1.37 + INSERT INTO t2 VALUES(1,1);
1.38 + INSERT INTO t2 VALUES(3,9);
1.39 + INSERT INTO t2 VALUES(5,25);
1.40 + INSERT INTO t2 VALUES(7,49);
1.41 + COMMIT;
1.42 + }
1.43 + execsql {
1.44 + SELECT a, (SELECT y FROM t2 WHERE x=a) FROM t1 WHERE b<8
1.45 + }
1.46 +} {1 1 3 9 5 25}
1.47 +do_test subquery-1.2 {
1.48 + execsql {
1.49 + UPDATE t1 SET b=b+(SELECT y FROM t2 WHERE x=a);
1.50 + SELECT * FROM t1;
1.51 + }
1.52 +} {1 3 3 13 5 31 7 57}
1.53 +
1.54 +do_test subquery-1.3 {
1.55 + execsql {
1.56 + SELECT b FROM t1 WHERE EXISTS(SELECT * FROM t2 WHERE y=a)
1.57 + }
1.58 +} {3}
1.59 +do_test subquery-1.4 {
1.60 + execsql {
1.61 + SELECT b FROM t1 WHERE NOT EXISTS(SELECT * FROM t2 WHERE y=a)
1.62 + }
1.63 +} {13 31 57}
1.64 +
1.65 +# Simple tests to make sure correlated subqueries in WHERE clauses
1.66 +# are used by the query optimizer correctly.
1.67 +do_test subquery-1.5 {
1.68 + execsql {
1.69 + SELECT a, x FROM t1, t2 WHERE t1.a = (SELECT x);
1.70 + }
1.71 +} {1 1 3 3 5 5 7 7}
1.72 +do_test subquery-1.6 {
1.73 + execsql {
1.74 + CREATE INDEX i1 ON t1(a);
1.75 + SELECT a, x FROM t1, t2 WHERE t1.a = (SELECT x);
1.76 + }
1.77 +} {1 1 3 3 5 5 7 7}
1.78 +do_test subquery-1.7 {
1.79 + execsql {
1.80 + SELECT a, x FROM t2, t1 WHERE t1.a = (SELECT x);
1.81 + }
1.82 +} {1 1 3 3 5 5 7 7}
1.83 +
1.84 +# Try an aggregate in both the subquery and the parent query.
1.85 +do_test subquery-1.8 {
1.86 + execsql {
1.87 + SELECT count(*) FROM t1 WHERE a > (SELECT count(*) FROM t2);
1.88 + }
1.89 +} {2}
1.90 +
1.91 +# Test a correlated subquery disables the "only open the index" optimization.
1.92 +do_test subquery-1.9.1 {
1.93 + execsql {
1.94 + SELECT (y*2)>b FROM t1, t2 WHERE a=x;
1.95 + }
1.96 +} {0 1 1 1}
1.97 +do_test subquery-1.9.2 {
1.98 + execsql {
1.99 + SELECT a FROM t1 WHERE (SELECT (y*2)>b FROM t2 WHERE a=x);
1.100 + }
1.101 +} {3 5 7}
1.102 +
1.103 +# Test that the flattening optimization works with subquery expressions.
1.104 +do_test subquery-1.10.1 {
1.105 + execsql {
1.106 + SELECT (SELECT a), b FROM t1;
1.107 + }
1.108 +} {1 3 3 13 5 31 7 57}
1.109 +do_test subquery-1.10.2 {
1.110 + execsql {
1.111 + SELECT * FROM (SELECT (SELECT a), b FROM t1);
1.112 + }
1.113 +} {1 3 3 13 5 31 7 57}
1.114 +do_test subquery-1.10.3 {
1.115 + execsql {
1.116 + SELECT * FROM (SELECT (SELECT sum(a) FROM t1));
1.117 + }
1.118 +} {16}
1.119 +do_test subquery-1.10.4 {
1.120 + execsql {
1.121 + CREATE TABLE t5 (val int, period text PRIMARY KEY);
1.122 + INSERT INTO t5 VALUES(5, '2001-3');
1.123 + INSERT INTO t5 VALUES(10, '2001-4');
1.124 + INSERT INTO t5 VALUES(15, '2002-1');
1.125 + INSERT INTO t5 VALUES(5, '2002-2');
1.126 + INSERT INTO t5 VALUES(10, '2002-3');
1.127 + INSERT INTO t5 VALUES(15, '2002-4');
1.128 + INSERT INTO t5 VALUES(10, '2003-1');
1.129 + INSERT INTO t5 VALUES(5, '2003-2');
1.130 + INSERT INTO t5 VALUES(25, '2003-3');
1.131 + INSERT INTO t5 VALUES(5, '2003-4');
1.132 +
1.133 + SELECT period, vsum
1.134 + FROM (SELECT
1.135 + a.period,
1.136 + (select sum(val) from t5 where period between a.period and '2002-4') vsum
1.137 + FROM t5 a where a.period between '2002-1' and '2002-4')
1.138 + WHERE vsum < 45 ;
1.139 + }
1.140 +} {2002-2 30 2002-3 25 2002-4 15}
1.141 +do_test subquery-1.10.5 {
1.142 + execsql {
1.143 + SELECT period, vsum from
1.144 + (select a.period,
1.145 + (select sum(val) from t5 where period between a.period and '2002-4') vsum
1.146 + FROM t5 a where a.period between '2002-1' and '2002-4')
1.147 + WHERE vsum < 45 ;
1.148 + }
1.149 +} {2002-2 30 2002-3 25 2002-4 15}
1.150 +do_test subquery-1.10.6 {
1.151 + execsql {
1.152 + DROP TABLE t5;
1.153 + }
1.154 +} {}
1.155 +
1.156 +
1.157 +
1.158 +#------------------------------------------------------------------
1.159 +# The following test cases - subquery-2.* - are not logically
1.160 +# organized. They're here largely because they were failing during
1.161 +# one stage of development of sub-queries.
1.162 +#
1.163 +do_test subquery-2.1 {
1.164 + execsql {
1.165 + SELECT (SELECT 10);
1.166 + }
1.167 +} {10}
1.168 +do_test subquery-2.2.1 {
1.169 + execsql {
1.170 + CREATE TABLE t3(a PRIMARY KEY, b);
1.171 + INSERT INTO t3 VALUES(1, 2);
1.172 + INSERT INTO t3 VALUES(3, 1);
1.173 + }
1.174 +} {}
1.175 +do_test subquery-2.2.2 {
1.176 + execsql {
1.177 + SELECT * FROM t3 WHERE a IN (SELECT b FROM t3);
1.178 + }
1.179 +} {1 2}
1.180 +do_test subquery-2.2.3 {
1.181 + execsql {
1.182 + DROP TABLE t3;
1.183 + }
1.184 +} {}
1.185 +do_test subquery-2.3.1 {
1.186 + execsql {
1.187 + CREATE TABLE t3(a TEXT);
1.188 + INSERT INTO t3 VALUES('10');
1.189 + }
1.190 +} {}
1.191 +do_test subquery-2.3.2 {
1.192 + execsql {
1.193 + SELECT a IN (10.0, 20) FROM t3;
1.194 + }
1.195 +} {0}
1.196 +do_test subquery-2.3.3 {
1.197 + execsql {
1.198 + DROP TABLE t3;
1.199 + }
1.200 +} {}
1.201 +do_test subquery-2.4.1 {
1.202 + execsql {
1.203 + CREATE TABLE t3(a TEXT);
1.204 + INSERT INTO t3 VALUES('XX');
1.205 + }
1.206 +} {}
1.207 +do_test subquery-2.4.2 {
1.208 + execsql {
1.209 + SELECT count(*) FROM t3 WHERE a IN (SELECT 'XX')
1.210 + }
1.211 +} {1}
1.212 +do_test subquery-2.4.3 {
1.213 + execsql {
1.214 + DROP TABLE t3;
1.215 + }
1.216 +} {}
1.217 +do_test subquery-2.5.1 {
1.218 + execsql {
1.219 + CREATE TABLE t3(a INTEGER);
1.220 + INSERT INTO t3 VALUES(10);
1.221 +
1.222 + CREATE TABLE t4(x TEXT);
1.223 + INSERT INTO t4 VALUES('10.0');
1.224 + }
1.225 +} {}
1.226 +do_test subquery-2.5.2 {
1.227 + # In the expr "x IN (SELECT a FROM t3)" the RHS of the IN operator
1.228 + # has text affinity and the LHS has integer affinity. The rule is
1.229 + # that we try to convert both sides to an integer before doing the
1.230 + # comparision. Hence, the integer value 10 in t3 will compare equal
1.231 + # to the string value '10.0' in t4 because the t4 value will be
1.232 + # converted into an integer.
1.233 + execsql {
1.234 + SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
1.235 + }
1.236 +} {10.0}
1.237 +do_test subquery-2.5.3.1 {
1.238 + # The t4i index cannot be used to resolve the "x IN (...)" constraint
1.239 + # because the constraint has integer affinity but t4i has text affinity.
1.240 + execsql {
1.241 + CREATE INDEX t4i ON t4(x);
1.242 + SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
1.243 + }
1.244 +} {10.0}
1.245 +do_test subquery-2.5.3.2 {
1.246 + # Verify that the t4i index was not used in the previous query
1.247 + set ::sqlite_query_plan
1.248 +} {t4 {}}
1.249 +do_test subquery-2.5.4 {
1.250 + execsql {
1.251 + DROP TABLE t3;
1.252 + DROP TABLE t4;
1.253 + }
1.254 +} {}
1.255 +
1.256 +#------------------------------------------------------------------
1.257 +# The following test cases - subquery-3.* - test tickets that
1.258 +# were raised during development of correlated subqueries.
1.259 +#
1.260 +
1.261 +# Ticket 1083
1.262 +ifcapable view {
1.263 + do_test subquery-3.1 {
1.264 + catchsql { DROP TABLE t1; }
1.265 + catchsql { DROP TABLE t2; }
1.266 + execsql {
1.267 + CREATE TABLE t1(a,b);
1.268 + INSERT INTO t1 VALUES(1,2);
1.269 + CREATE VIEW v1 AS SELECT b FROM t1 WHERE a>0;
1.270 + CREATE TABLE t2(p,q);
1.271 + INSERT INTO t2 VALUES(2,9);
1.272 + SELECT * FROM v1 WHERE EXISTS(SELECT * FROM t2 WHERE p=v1.b);
1.273 + }
1.274 + } {2}
1.275 +} else {
1.276 + catchsql { DROP TABLE t1; }
1.277 + catchsql { DROP TABLE t2; }
1.278 + execsql {
1.279 + CREATE TABLE t1(a,b);
1.280 + INSERT INTO t1 VALUES(1,2);
1.281 + CREATE TABLE t2(p,q);
1.282 + INSERT INTO t2 VALUES(2,9);
1.283 + }
1.284 +}
1.285 +
1.286 +# Ticket 1084
1.287 +do_test subquery-3.2 {
1.288 + catchsql {
1.289 + CREATE TABLE t1(a,b);
1.290 + INSERT INTO t1 VALUES(1,2);
1.291 + }
1.292 + execsql {
1.293 + SELECT (SELECT t1.a) FROM t1;
1.294 + }
1.295 +} {1}
1.296 +
1.297 +# Test Cases subquery-3.3.* test correlated subqueries where the
1.298 +# parent query is an aggregate query. Ticket #1105 is an example
1.299 +# of such a query.
1.300 +#
1.301 +do_test subquery-3.3.1 {
1.302 + execsql {
1.303 + SELECT a, (SELECT b) FROM t1 GROUP BY a;
1.304 + }
1.305 +} {1 2}
1.306 +do_test subquery-3.3.2 {
1.307 + catchsql {DROP TABLE t2}
1.308 + execsql {
1.309 + CREATE TABLE t2(c, d);
1.310 + INSERT INTO t2 VALUES(1, 'one');
1.311 + INSERT INTO t2 VALUES(2, 'two');
1.312 + SELECT a, (SELECT d FROM t2 WHERE a=c) FROM t1 GROUP BY a;
1.313 + }
1.314 +} {1 one}
1.315 +do_test subquery-3.3.3 {
1.316 + execsql {
1.317 + INSERT INTO t1 VALUES(2, 4);
1.318 + SELECT max(a), (SELECT d FROM t2 WHERE a=c) FROM t1;
1.319 + }
1.320 +} {2 two}
1.321 +do_test subquery-3.3.4 {
1.322 + execsql {
1.323 + SELECT a, (SELECT (SELECT d FROM t2 WHERE a=c)) FROM t1 GROUP BY a;
1.324 + }
1.325 +} {1 one 2 two}
1.326 +do_test subquery-3.3.5 {
1.327 + execsql {
1.328 + SELECT a, (SELECT count(*) FROM t2 WHERE a=c) FROM t1;
1.329 + }
1.330 +} {1 1 2 1}
1.331 +
1.332 +#------------------------------------------------------------------
1.333 +# These tests - subquery-4.* - use the TCL statement cache to try
1.334 +# and expose bugs to do with re-using statements that have been
1.335 +# passed to sqlite3_reset().
1.336 +#
1.337 +# One problem was that VDBE memory cells were not being initialised
1.338 +# to NULL on the second and subsequent executions.
1.339 +#
1.340 +do_test subquery-4.1.1 {
1.341 + execsql {
1.342 + SELECT (SELECT a FROM t1);
1.343 + }
1.344 +} {1}
1.345 +do_test subquery-4.2 {
1.346 + execsql {
1.347 + DELETE FROM t1;
1.348 + SELECT (SELECT a FROM t1);
1.349 + }
1.350 +} {{}}
1.351 +do_test subquery-4.2.1 {
1.352 + execsql {
1.353 + CREATE TABLE t3(a PRIMARY KEY);
1.354 + INSERT INTO t3 VALUES(10);
1.355 + }
1.356 + execsql {INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1)}
1.357 +} {}
1.358 +do_test subquery-4.2.2 {
1.359 + execsql {INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1)}
1.360 +} {}
1.361 +
1.362 +#------------------------------------------------------------------
1.363 +# The subquery-5.* tests make sure string literals in double-quotes
1.364 +# are handled efficiently. Double-quote literals are first checked
1.365 +# to see if they match any column names. If there is not column name
1.366 +# match then those literals are used a string constants. When a
1.367 +# double-quoted string appears, we want to make sure that the search
1.368 +# for a matching column name did not cause an otherwise static subquery
1.369 +# to become a dynamic (correlated) subquery.
1.370 +#
1.371 +do_test subquery-5.1 {
1.372 + proc callcntproc {n} {
1.373 + incr ::callcnt
1.374 + return $n
1.375 + }
1.376 + set callcnt 0
1.377 + db function callcnt callcntproc
1.378 + execsql {
1.379 + CREATE TABLE t4(x,y);
1.380 + INSERT INTO t4 VALUES('one',1);
1.381 + INSERT INTO t4 VALUES('two',2);
1.382 + INSERT INTO t4 VALUES('three',3);
1.383 + INSERT INTO t4 VALUES('four',4);
1.384 + CREATE TABLE t5(a,b);
1.385 + INSERT INTO t5 VALUES(1,11);
1.386 + INSERT INTO t5 VALUES(2,22);
1.387 + INSERT INTO t5 VALUES(3,33);
1.388 + INSERT INTO t5 VALUES(4,44);
1.389 + SELECT b FROM t5 WHERE a IN
1.390 + (SELECT callcnt(y)+0 FROM t4 WHERE x="two")
1.391 + }
1.392 +} {22}
1.393 +do_test subquery-5.2 {
1.394 + # This is the key test. The subquery should have only run once. If
1.395 + # The double-quoted identifier "two" were causing the subquery to be
1.396 + # processed as a correlated subquery, then it would have run 4 times.
1.397 + set callcnt
1.398 +} {1}
1.399 +
1.400 +
1.401 +# Ticket #1380. Make sure correlated subqueries on an IN clause work
1.402 +# correctly when the left-hand side of the IN operator is constant.
1.403 +#
1.404 +do_test subquery-6.1 {
1.405 + set callcnt 0
1.406 + execsql {
1.407 + SELECT x FROM t4 WHERE 1 IN (SELECT callcnt(count(*)) FROM t5 WHERE a=y)
1.408 + }
1.409 +} {one two three four}
1.410 +do_test subquery-6.2 {
1.411 + set callcnt
1.412 +} {4}
1.413 +do_test subquery-6.3 {
1.414 + set callcnt 0
1.415 + execsql {
1.416 + SELECT x FROM t4 WHERE 1 IN (SELECT callcnt(count(*)) FROM t5 WHERE a=1)
1.417 + }
1.418 +} {one two three four}
1.419 +do_test subquery-6.4 {
1.420 + set callcnt
1.421 +} {1}
1.422 +
1.423 +if 0 { ############# disable until we get #2652 fixed
1.424 +# Ticket #2652. Allow aggregate functions of outer queries inside
1.425 +# a non-aggregate subquery.
1.426 +#
1.427 +do_test subquery-7.1 {
1.428 + execsql {
1.429 + CREATE TABLE t7(c7);
1.430 + INSERT INTO t7 VALUES(1);
1.431 + INSERT INTO t7 VALUES(2);
1.432 + INSERT INTO t7 VALUES(3);
1.433 + CREATE TABLE t8(c8);
1.434 + INSERT INTO t8 VALUES(100);
1.435 + INSERT INTO t8 VALUES(200);
1.436 + INSERT INTO t8 VALUES(300);
1.437 + CREATE TABLE t9(c9);
1.438 + INSERT INTO t9 VALUES(10000);
1.439 + INSERT INTO t9 VALUES(20000);
1.440 + INSERT INTO t9 VALUES(30000);
1.441 +
1.442 + SELECT (SELECT c7+c8 FROM t7) FROM t8;
1.443 + }
1.444 +} {101 201 301}
1.445 +do_test subquery-7.2 {
1.446 + execsql {
1.447 + SELECT (SELECT max(c7)+c8 FROM t7) FROM t8;
1.448 + }
1.449 +} {103 203 303}
1.450 +do_test subquery-7.3 {
1.451 + execsql {
1.452 + SELECT (SELECT c7+max(c8) FROM t8) FROM t7
1.453 + }
1.454 +} {301}
1.455 +do_test subquery-7.4 {
1.456 + execsql {
1.457 + SELECT (SELECT max(c7)+max(c8) FROM t8) FROM t7
1.458 + }
1.459 +} {303}
1.460 +do_test subquery-7.5 {
1.461 + execsql {
1.462 + SELECT (SELECT c8 FROM t8 WHERE rowid=max(c7)) FROM t7
1.463 + }
1.464 +} {300}
1.465 +do_test subquery-7.6 {
1.466 + execsql {
1.467 + SELECT (SELECT (SELECT max(c7+c8+c9) FROM t9) FROM t8) FROM t7
1.468 + }
1.469 +} {30101 30102 30103}
1.470 +do_test subquery-7.7 {
1.471 + execsql {
1.472 + SELECT (SELECT (SELECT c7+max(c8+c9) FROM t9) FROM t8) FROM t7
1.473 + }
1.474 +} {30101 30102 30103}
1.475 +do_test subquery-7.8 {
1.476 + execsql {
1.477 + SELECT (SELECT (SELECT max(c7)+c8+c9 FROM t9) FROM t8) FROM t7
1.478 + }
1.479 +} {10103}
1.480 +do_test subquery-7.9 {
1.481 + execsql {
1.482 + SELECT (SELECT (SELECT c7+max(c8)+c9 FROM t9) FROM t8) FROM t7
1.483 + }
1.484 +} {10301 10302 10303}
1.485 +do_test subquery-7.10 {
1.486 + execsql {
1.487 + SELECT (SELECT (SELECT c7+c8+max(c9) FROM t9) FROM t8) FROM t7
1.488 + }
1.489 +} {30101 30102 30103}
1.490 +do_test subquery-7.11 {
1.491 + execsql {
1.492 + SELECT (SELECT (SELECT max(c7)+max(c8)+max(c9) FROM t9) FROM t8) FROM t7
1.493 + }
1.494 +} {30303}
1.495 +} ;############# Disabled
1.496 +
1.497 +finish_test