1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/join.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,516 @@
1.4 +# 2002 May 24
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.
1.15 +#
1.16 +# This file implements tests for joins, including outer joins.
1.17 +#
1.18 +# $Id: join.test,v 1.25 2008/08/14 00:19:49 drh Exp $
1.19 +
1.20 +set testdir [file dirname $argv0]
1.21 +source $testdir/tester.tcl
1.22 +
1.23 +do_test join-1.1 {
1.24 + execsql {
1.25 + CREATE TABLE t1(a,b,c);
1.26 + INSERT INTO t1 VALUES(1,2,3);
1.27 + INSERT INTO t1 VALUES(2,3,4);
1.28 + INSERT INTO t1 VALUES(3,4,5);
1.29 + SELECT * FROM t1;
1.30 + }
1.31 +} {1 2 3 2 3 4 3 4 5}
1.32 +do_test join-1.2 {
1.33 + execsql {
1.34 + CREATE TABLE t2(b,c,d);
1.35 + INSERT INTO t2 VALUES(1,2,3);
1.36 + INSERT INTO t2 VALUES(2,3,4);
1.37 + INSERT INTO t2 VALUES(3,4,5);
1.38 + SELECT * FROM t2;
1.39 + }
1.40 +} {1 2 3 2 3 4 3 4 5}
1.41 +
1.42 +do_test join-1.3 {
1.43 + execsql2 {
1.44 + SELECT * FROM t1 NATURAL JOIN t2;
1.45 + }
1.46 +} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
1.47 +do_test join-1.3.1 {
1.48 + execsql2 {
1.49 + SELECT * FROM t2 NATURAL JOIN t1;
1.50 + }
1.51 +} {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2}
1.52 +do_test join-1.3.2 {
1.53 + execsql2 {
1.54 + SELECT * FROM t2 AS x NATURAL JOIN t1;
1.55 + }
1.56 +} {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2}
1.57 +do_test join-1.3.3 {
1.58 + execsql2 {
1.59 + SELECT * FROM t2 NATURAL JOIN t1 AS y;
1.60 + }
1.61 +} {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2}
1.62 +do_test join-1.3.4 {
1.63 + execsql {
1.64 + SELECT b FROM t1 NATURAL JOIN t2;
1.65 + }
1.66 +} {2 3}
1.67 +do_test join-1.4.1 {
1.68 + execsql2 {
1.69 + SELECT * FROM t1 INNER JOIN t2 USING(b,c);
1.70 + }
1.71 +} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
1.72 +do_test join-1.4.2 {
1.73 + execsql2 {
1.74 + SELECT * FROM t1 AS x INNER JOIN t2 USING(b,c);
1.75 + }
1.76 +} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
1.77 +do_test join-1.4.3 {
1.78 + execsql2 {
1.79 + SELECT * FROM t1 INNER JOIN t2 AS y USING(b,c);
1.80 + }
1.81 +} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
1.82 +do_test join-1.4.4 {
1.83 + execsql2 {
1.84 + SELECT * FROM t1 AS x INNER JOIN t2 AS y USING(b,c);
1.85 + }
1.86 +} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
1.87 +do_test join-1.4.5 {
1.88 + execsql {
1.89 + SELECT b FROM t1 JOIN t2 USING(b);
1.90 + }
1.91 +} {2 3}
1.92 +do_test join-1.5 {
1.93 + execsql2 {
1.94 + SELECT * FROM t1 INNER JOIN t2 USING(b);
1.95 + }
1.96 +} {a 1 b 2 c 3 c 3 d 4 a 2 b 3 c 4 c 4 d 5}
1.97 +do_test join-1.6 {
1.98 + execsql2 {
1.99 + SELECT * FROM t1 INNER JOIN t2 USING(c);
1.100 + }
1.101 +} {a 1 b 2 c 3 b 2 d 4 a 2 b 3 c 4 b 3 d 5}
1.102 +do_test join-1.7 {
1.103 + execsql2 {
1.104 + SELECT * FROM t1 INNER JOIN t2 USING(c,b);
1.105 + }
1.106 +} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
1.107 +
1.108 +do_test join-1.8 {
1.109 + execsql {
1.110 + SELECT * FROM t1 NATURAL CROSS JOIN t2;
1.111 + }
1.112 +} {1 2 3 4 2 3 4 5}
1.113 +do_test join-1.9 {
1.114 + execsql {
1.115 + SELECT * FROM t1 CROSS JOIN t2 USING(b,c);
1.116 + }
1.117 +} {1 2 3 4 2 3 4 5}
1.118 +do_test join-1.10 {
1.119 + execsql {
1.120 + SELECT * FROM t1 NATURAL INNER JOIN t2;
1.121 + }
1.122 +} {1 2 3 4 2 3 4 5}
1.123 +do_test join-1.11 {
1.124 + execsql {
1.125 + SELECT * FROM t1 INNER JOIN t2 USING(b,c);
1.126 + }
1.127 +} {1 2 3 4 2 3 4 5}
1.128 +do_test join-1.12 {
1.129 + execsql {
1.130 + SELECT * FROM t1 natural inner join t2;
1.131 + }
1.132 +} {1 2 3 4 2 3 4 5}
1.133 +
1.134 +ifcapable subquery {
1.135 + do_test join-1.13 {
1.136 + execsql2 {
1.137 + SELECT * FROM t1 NATURAL JOIN
1.138 + (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as t3
1.139 + }
1.140 + } {a 1 b 2 c 3 d 4 e 5}
1.141 + do_test join-1.14 {
1.142 + execsql2 {
1.143 + SELECT * FROM (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as 'tx'
1.144 + NATURAL JOIN t1
1.145 + }
1.146 + } {c 3 d 4 e 5 a 1 b 2}
1.147 +}
1.148 +
1.149 +do_test join-1.15 {
1.150 + execsql {
1.151 + CREATE TABLE t3(c,d,e);
1.152 + INSERT INTO t3 VALUES(2,3,4);
1.153 + INSERT INTO t3 VALUES(3,4,5);
1.154 + INSERT INTO t3 VALUES(4,5,6);
1.155 + SELECT * FROM t3;
1.156 + }
1.157 +} {2 3 4 3 4 5 4 5 6}
1.158 +do_test join-1.16 {
1.159 + execsql {
1.160 + SELECT * FROM t1 natural join t2 natural join t3;
1.161 + }
1.162 +} {1 2 3 4 5 2 3 4 5 6}
1.163 +do_test join-1.17 {
1.164 + execsql2 {
1.165 + SELECT * FROM t1 natural join t2 natural join t3;
1.166 + }
1.167 +} {a 1 b 2 c 3 d 4 e 5 a 2 b 3 c 4 d 5 e 6}
1.168 +do_test join-1.18 {
1.169 + execsql {
1.170 + CREATE TABLE t4(d,e,f);
1.171 + INSERT INTO t4 VALUES(2,3,4);
1.172 + INSERT INTO t4 VALUES(3,4,5);
1.173 + INSERT INTO t4 VALUES(4,5,6);
1.174 + SELECT * FROM t4;
1.175 + }
1.176 +} {2 3 4 3 4 5 4 5 6}
1.177 +do_test join-1.19.1 {
1.178 + execsql {
1.179 + SELECT * FROM t1 natural join t2 natural join t4;
1.180 + }
1.181 +} {1 2 3 4 5 6}
1.182 +do_test join-1.19.2 {
1.183 + execsql2 {
1.184 + SELECT * FROM t1 natural join t2 natural join t4;
1.185 + }
1.186 +} {a 1 b 2 c 3 d 4 e 5 f 6}
1.187 +do_test join-1.20 {
1.188 + execsql {
1.189 + SELECT * FROM t1 natural join t2 natural join t3 WHERE t1.a=1
1.190 + }
1.191 +} {1 2 3 4 5}
1.192 +
1.193 +do_test join-2.1 {
1.194 + execsql {
1.195 + SELECT * FROM t1 NATURAL LEFT JOIN t2;
1.196 + }
1.197 +} {1 2 3 4 2 3 4 5 3 4 5 {}}
1.198 +do_test join-2.2 {
1.199 + execsql {
1.200 + SELECT * FROM t2 NATURAL LEFT OUTER JOIN t1;
1.201 + }
1.202 +} {1 2 3 {} 2 3 4 1 3 4 5 2}
1.203 +do_test join-2.3 {
1.204 + catchsql {
1.205 + SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2;
1.206 + }
1.207 +} {1 {RIGHT and FULL OUTER JOINs are not currently supported}}
1.208 +do_test join-2.4 {
1.209 + execsql {
1.210 + SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d
1.211 + }
1.212 +} {1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3}
1.213 +do_test join-2.5 {
1.214 + execsql {
1.215 + SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t1.a>1
1.216 + }
1.217 +} {2 3 4 {} {} {} 3 4 5 1 2 3}
1.218 +do_test join-2.6 {
1.219 + execsql {
1.220 + SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t2.b IS NULL OR t2.b>1
1.221 + }
1.222 +} {1 2 3 {} {} {} 2 3 4 {} {} {}}
1.223 +
1.224 +do_test join-3.1 {
1.225 + catchsql {
1.226 + SELECT * FROM t1 NATURAL JOIN t2 ON t1.a=t2.b;
1.227 + }
1.228 +} {1 {a NATURAL join may not have an ON or USING clause}}
1.229 +do_test join-3.2 {
1.230 + catchsql {
1.231 + SELECT * FROM t1 NATURAL JOIN t2 USING(b);
1.232 + }
1.233 +} {1 {a NATURAL join may not have an ON or USING clause}}
1.234 +do_test join-3.3 {
1.235 + catchsql {
1.236 + SELECT * FROM t1 JOIN t2 ON t1.a=t2.b USING(b);
1.237 + }
1.238 +} {1 {cannot have both ON and USING clauses in the same join}}
1.239 +do_test join-3.4.1 {
1.240 + catchsql {
1.241 + SELECT * FROM t1 JOIN t2 USING(a);
1.242 + }
1.243 +} {1 {cannot join using column a - column not present in both tables}}
1.244 +do_test join-3.4.2 {
1.245 + catchsql {
1.246 + SELECT * FROM t1 JOIN t2 USING(d);
1.247 + }
1.248 +} {1 {cannot join using column d - column not present in both tables}}
1.249 +do_test join-3.5 {
1.250 + catchsql {
1.251 + SELECT * FROM t1 USING(a);
1.252 + }
1.253 +} {0 {1 2 3 2 3 4 3 4 5}}
1.254 +do_test join-3.6 {
1.255 + catchsql {
1.256 + SELECT * FROM t1 JOIN t2 ON t3.a=t2.b;
1.257 + }
1.258 +} {1 {no such column: t3.a}}
1.259 +do_test join-3.7 {
1.260 + catchsql {
1.261 + SELECT * FROM t1 INNER OUTER JOIN t2;
1.262 + }
1.263 +} {1 {unknown or unsupported join type: INNER OUTER}}
1.264 +do_test join-3.8 {
1.265 + catchsql {
1.266 + SELECT * FROM t1 INNER OUTER CROSS JOIN t2;
1.267 + }
1.268 +} {1 {unknown or unsupported join type: INNER OUTER CROSS}}
1.269 +do_test join-3.9 {
1.270 + catchsql {
1.271 + SELECT * FROM t1 OUTER NATURAL INNER JOIN t2;
1.272 + }
1.273 +} {1 {unknown or unsupported join type: OUTER NATURAL INNER}}
1.274 +do_test join-3.10 {
1.275 + catchsql {
1.276 + SELECT * FROM t1 LEFT BOGUS JOIN t2;
1.277 + }
1.278 +} {1 {unknown or unsupported join type: LEFT BOGUS}}
1.279 +do_test join-3.11 {
1.280 + catchsql {
1.281 + SELECT * FROM t1 INNER BOGUS CROSS JOIN t2;
1.282 + }
1.283 +} {1 {unknown or unsupported join type: INNER BOGUS CROSS}}
1.284 +do_test join-3.12 {
1.285 + catchsql {
1.286 + SELECT * FROM t1 NATURAL AWK SED JOIN t2;
1.287 + }
1.288 +} {1 {unknown or unsupported join type: NATURAL AWK SED}}
1.289 +
1.290 +do_test join-4.1 {
1.291 + execsql {
1.292 + BEGIN;
1.293 + CREATE TABLE t5(a INTEGER PRIMARY KEY);
1.294 + CREATE TABLE t6(a INTEGER);
1.295 + INSERT INTO t6 VALUES(NULL);
1.296 + INSERT INTO t6 VALUES(NULL);
1.297 + INSERT INTO t6 SELECT * FROM t6;
1.298 + INSERT INTO t6 SELECT * FROM t6;
1.299 + INSERT INTO t6 SELECT * FROM t6;
1.300 + INSERT INTO t6 SELECT * FROM t6;
1.301 + INSERT INTO t6 SELECT * FROM t6;
1.302 + INSERT INTO t6 SELECT * FROM t6;
1.303 + COMMIT;
1.304 + }
1.305 + execsql {
1.306 + SELECT * FROM t6 NATURAL JOIN t5;
1.307 + }
1.308 +} {}
1.309 +do_test join-4.2 {
1.310 + execsql {
1.311 + SELECT * FROM t6, t5 WHERE t6.a<t5.a;
1.312 + }
1.313 +} {}
1.314 +do_test join-4.3 {
1.315 + execsql {
1.316 + SELECT * FROM t6, t5 WHERE t6.a>t5.a;
1.317 + }
1.318 +} {}
1.319 +do_test join-4.4 {
1.320 + execsql {
1.321 + UPDATE t6 SET a='xyz';
1.322 + SELECT * FROM t6 NATURAL JOIN t5;
1.323 + }
1.324 +} {}
1.325 +do_test join-4.6 {
1.326 + execsql {
1.327 + SELECT * FROM t6, t5 WHERE t6.a<t5.a;
1.328 + }
1.329 +} {}
1.330 +do_test join-4.7 {
1.331 + execsql {
1.332 + SELECT * FROM t6, t5 WHERE t6.a>t5.a;
1.333 + }
1.334 +} {}
1.335 +do_test join-4.8 {
1.336 + execsql {
1.337 + UPDATE t6 SET a=1;
1.338 + SELECT * FROM t6 NATURAL JOIN t5;
1.339 + }
1.340 +} {}
1.341 +do_test join-4.9 {
1.342 + execsql {
1.343 + SELECT * FROM t6, t5 WHERE t6.a<t5.a;
1.344 + }
1.345 +} {}
1.346 +do_test join-4.10 {
1.347 + execsql {
1.348 + SELECT * FROM t6, t5 WHERE t6.a>t5.a;
1.349 + }
1.350 +} {}
1.351 +
1.352 +do_test join-5.1 {
1.353 + execsql {
1.354 + BEGIN;
1.355 + create table centros (id integer primary key, centro);
1.356 + INSERT INTO centros VALUES(1,'xxx');
1.357 + create table usuarios (id integer primary key, nombre, apellidos,
1.358 + idcentro integer);
1.359 + INSERT INTO usuarios VALUES(1,'a','aa',1);
1.360 + INSERT INTO usuarios VALUES(2,'b','bb',1);
1.361 + INSERT INTO usuarios VALUES(3,'c','cc',NULL);
1.362 + create index idcentro on usuarios (idcentro);
1.363 + END;
1.364 + select usuarios.id, usuarios.nombre, centros.centro from
1.365 + usuarios left outer join centros on usuarios.idcentro = centros.id;
1.366 + }
1.367 +} {1 a xxx 2 b xxx 3 c {}}
1.368 +
1.369 +# A test for ticket #247.
1.370 +#
1.371 +do_test join-7.1 {
1.372 + execsql {
1.373 + CREATE TABLE t7 (x, y);
1.374 + INSERT INTO t7 VALUES ("pa1", 1);
1.375 + INSERT INTO t7 VALUES ("pa2", NULL);
1.376 + INSERT INTO t7 VALUES ("pa3", NULL);
1.377 + INSERT INTO t7 VALUES ("pa4", 2);
1.378 + INSERT INTO t7 VALUES ("pa30", 131);
1.379 + INSERT INTO t7 VALUES ("pa31", 130);
1.380 + INSERT INTO t7 VALUES ("pa28", NULL);
1.381 +
1.382 + CREATE TABLE t8 (a integer primary key, b);
1.383 + INSERT INTO t8 VALUES (1, "pa1");
1.384 + INSERT INTO t8 VALUES (2, "pa4");
1.385 + INSERT INTO t8 VALUES (3, NULL);
1.386 + INSERT INTO t8 VALUES (4, NULL);
1.387 + INSERT INTO t8 VALUES (130, "pa31");
1.388 + INSERT INTO t8 VALUES (131, "pa30");
1.389 +
1.390 + SELECT coalesce(t8.a,999) from t7 LEFT JOIN t8 on y=a;
1.391 + }
1.392 +} {1 999 999 2 131 130 999}
1.393 +
1.394 +# Make sure a left join where the right table is really a view that
1.395 +# is itself a join works right. Ticket #306.
1.396 +#
1.397 +ifcapable view {
1.398 +do_test join-8.1 {
1.399 + execsql {
1.400 + BEGIN;
1.401 + CREATE TABLE t9(a INTEGER PRIMARY KEY, b);
1.402 + INSERT INTO t9 VALUES(1,11);
1.403 + INSERT INTO t9 VALUES(2,22);
1.404 + CREATE TABLE t10(x INTEGER PRIMARY KEY, y);
1.405 + INSERT INTO t10 VALUES(1,2);
1.406 + INSERT INTO t10 VALUES(3,3);
1.407 + CREATE TABLE t11(p INTEGER PRIMARY KEY, q);
1.408 + INSERT INTO t11 VALUES(2,111);
1.409 + INSERT INTO t11 VALUES(3,333);
1.410 + CREATE VIEW v10_11 AS SELECT x, q FROM t10, t11 WHERE t10.y=t11.p;
1.411 + COMMIT;
1.412 + SELECT * FROM t9 LEFT JOIN v10_11 ON( a=x );
1.413 + }
1.414 +} {1 11 1 111 2 22 {} {}}
1.415 +ifcapable subquery {
1.416 + do_test join-8.2 {
1.417 + execsql {
1.418 + SELECT * FROM t9 LEFT JOIN (SELECT x, q FROM t10, t11 WHERE t10.y=t11.p)
1.419 + ON( a=x);
1.420 + }
1.421 + } {1 11 1 111 2 22 {} {}}
1.422 +}
1.423 +do_test join-8.3 {
1.424 + execsql {
1.425 + SELECT * FROM v10_11 LEFT JOIN t9 ON( a=x );
1.426 + }
1.427 +} {1 111 1 11 3 333 {} {}}
1.428 +ifcapable subquery {
1.429 + # Constant expressions in a subquery that is the right element of a
1.430 + # LEFT JOIN evaluate to NULL for rows where the LEFT JOIN does not
1.431 + # match. Ticket #3300
1.432 + do_test join-8.4 {
1.433 + execsql {
1.434 + SELECT * FROM t9 LEFT JOIN (SELECT 44, p, q FROM t11) AS sub1 ON p=a
1.435 + }
1.436 + } {1 11 {} {} {} 2 22 44 2 111}
1.437 +}
1.438 +} ;# ifcapable view
1.439 +
1.440 +# Ticket #350 describes a scenario where LEFT OUTER JOIN does not
1.441 +# function correctly if the right table in the join is really
1.442 +# subquery.
1.443 +#
1.444 +# To test the problem, we generate the same LEFT OUTER JOIN in two
1.445 +# separate selects but with on using a subquery and the other calling
1.446 +# the table directly. Then connect the two SELECTs using an EXCEPT.
1.447 +# Both queries should generate the same results so the answer should
1.448 +# be an empty set.
1.449 +#
1.450 +ifcapable compound {
1.451 +do_test join-9.1 {
1.452 + execsql {
1.453 + BEGIN;
1.454 + CREATE TABLE t12(a,b);
1.455 + INSERT INTO t12 VALUES(1,11);
1.456 + INSERT INTO t12 VALUES(2,22);
1.457 + CREATE TABLE t13(b,c);
1.458 + INSERT INTO t13 VALUES(22,222);
1.459 + COMMIT;
1.460 + }
1.461 +} {}
1.462 +
1.463 +ifcapable subquery {
1.464 + do_test join-9.1.1 {
1.465 + execsql {
1.466 + SELECT * FROM t12 NATURAL LEFT JOIN t13
1.467 + EXCEPT
1.468 + SELECT * FROM t12 NATURAL LEFT JOIN (SELECT * FROM t13 WHERE b>0);
1.469 + }
1.470 + } {}
1.471 +}
1.472 +ifcapable view {
1.473 + do_test join-9.2 {
1.474 + execsql {
1.475 + CREATE VIEW v13 AS SELECT * FROM t13 WHERE b>0;
1.476 + SELECT * FROM t12 NATURAL LEFT JOIN t13
1.477 + EXCEPT
1.478 + SELECT * FROM t12 NATURAL LEFT JOIN v13;
1.479 + }
1.480 + } {}
1.481 +} ;# ifcapable view
1.482 +} ;# ifcapable compound
1.483 +
1.484 +ifcapable subquery {
1.485 + # Ticket #1697: Left Join WHERE clause terms that contain an
1.486 + # aggregate subquery.
1.487 + #
1.488 + do_test join-10.1 {
1.489 + execsql {
1.490 + CREATE TABLE t21(a,b,c);
1.491 + CREATE TABLE t22(p,q);
1.492 + CREATE INDEX i22 ON t22(q);
1.493 + SELECT a FROM t21 LEFT JOIN t22 ON b=p WHERE q=
1.494 + (SELECT max(m.q) FROM t22 m JOIN t21 n ON n.b=m.p WHERE n.c=1);
1.495 + }
1.496 + } {}
1.497 +
1.498 + # Test a LEFT JOIN when the right-hand side of hte join is an empty
1.499 + # sub-query. Seems fine.
1.500 + #
1.501 + do_test join-10.2 {
1.502 + execsql {
1.503 + CREATE TABLE t23(a, b, c);
1.504 + CREATE TABLE t24(a, b, c);
1.505 + INSERT INTO t23 VALUES(1, 2, 3);
1.506 + }
1.507 + execsql {
1.508 + SELECT * FROM t23 LEFT JOIN t24;
1.509 + }
1.510 + } {1 2 3 {} {} {}}
1.511 + do_test join-10.3 {
1.512 + execsql {
1.513 + SELECT * FROM t23 LEFT JOIN (SELECT * FROM t24);
1.514 + }
1.515 + } {1 2 3 {} {} {}}
1.516 +
1.517 +} ;# ifcapable subquery
1.518 +
1.519 +finish_test