1# 2002 May 24 2# 3# The author disclaims copyright to this source code. In place of 4# a legal notice, here is a blessing: 5# 6# May you do good and not evil. 7# May you find forgiveness for yourself and forgive others. 8# May you share freely, never taking more than you give. 9# 10#*********************************************************************** 11# This file implements regression tests for SQLite library. 12# 13# This file implements tests for joins, including outer joins. 14# 15# $Id: join.test,v 1.10 2003/06/16 00:40:35 drh Exp $ 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19 20do_test join-1.1 { 21 execsql { 22 CREATE TABLE t1(a,b,c); 23 INSERT INTO t1 VALUES(1,2,3); 24 INSERT INTO t1 VALUES(2,3,4); 25 INSERT INTO t1 VALUES(3,4,5); 26 SELECT * FROM t1; 27 } 28} {1 2 3 2 3 4 3 4 5} 29do_test join-1.2 { 30 execsql { 31 CREATE TABLE t2(b,c,d); 32 INSERT INTO t2 VALUES(1,2,3); 33 INSERT INTO t2 VALUES(2,3,4); 34 INSERT INTO t2 VALUES(3,4,5); 35 SELECT * FROM t2; 36 } 37} {1 2 3 2 3 4 3 4 5} 38 39do_test join-1.3 { 40 execsql2 { 41 SELECT * FROM t1 NATURAL JOIN t2; 42 } 43} {t1.a 1 t1.b 2 t1.c 3 t2.d 4 t1.a 2 t1.b 3 t1.c 4 t2.d 5} 44do_test join-1.3.1 { 45 execsql2 { 46 SELECT * FROM t2 NATURAL JOIN t1; 47 } 48} {t2.b 2 t2.c 3 t2.d 4 t1.a 1 t2.b 3 t2.c 4 t2.d 5 t1.a 2} 49do_test join-1.4 { 50 execsql2 { 51 SELECT * FROM t1 INNER JOIN t2 USING(b,c); 52 } 53} {t1.a 1 t1.b 2 t1.c 3 t2.d 4 t1.a 2 t1.b 3 t1.c 4 t2.d 5} 54do_test join-1.5 { 55 execsql2 { 56 SELECT * FROM t1 INNER JOIN t2 USING(b); 57 } 58} {t1.a 1 t1.b 2 t1.c 3 t2.c 3 t2.d 4 t1.a 2 t1.b 3 t1.c 4 t2.c 4 t2.d 5} 59do_test join-1.6 { 60 execsql2 { 61 SELECT * FROM t1 INNER JOIN t2 USING(c); 62 } 63} {t1.a 1 t1.b 2 t1.c 3 t2.b 2 t2.d 4 t1.a 2 t1.b 3 t1.c 4 t2.b 3 t2.d 5} 64do_test join-1.7 { 65 execsql2 { 66 SELECT * FROM t1 INNER JOIN t2 USING(c,b); 67 } 68} {t1.a 1 t1.b 2 t1.c 3 t2.d 4 t1.a 2 t1.b 3 t1.c 4 t2.d 5} 69 70do_test join-1.8 { 71 execsql { 72 SELECT * FROM t1 NATURAL CROSS JOIN t2; 73 } 74} {1 2 3 4 2 3 4 5} 75do_test join-1.9 { 76 execsql { 77 SELECT * FROM t1 CROSS JOIN t2 USING(b,c); 78 } 79} {1 2 3 4 2 3 4 5} 80do_test join-1.10 { 81 execsql { 82 SELECT * FROM t1 NATURAL INNER JOIN t2; 83 } 84} {1 2 3 4 2 3 4 5} 85do_test join-1.11 { 86 execsql { 87 SELECT * FROM t1 INNER JOIN t2 USING(b,c); 88 } 89} {1 2 3 4 2 3 4 5} 90do_test join-1.12 { 91 execsql { 92 SELECT * FROM t1 natural inner join t2; 93 } 94} {1 2 3 4 2 3 4 5} 95do_test join-1.13 { 96 execsql2 { 97 SELECT * FROM t1 NATURAL JOIN 98 (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as t3 99 } 100} {t1.a 1 t1.b 2 t1.c 3 t3.d 4 t3.e 5} 101do_test join-1.14 { 102 execsql2 { 103 SELECT * FROM (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as 'tx' 104 NATURAL JOIN t1 105 } 106} {tx.c 3 tx.d 4 tx.e 5 t1.a 1 t1.b 2} 107 108do_test join-1.15 { 109 execsql { 110 CREATE TABLE t3(c,d,e); 111 INSERT INTO t3 VALUES(2,3,4); 112 INSERT INTO t3 VALUES(3,4,5); 113 INSERT INTO t3 VALUES(4,5,6); 114 SELECT * FROM t3; 115 } 116} {2 3 4 3 4 5 4 5 6} 117do_test join-1.16 { 118 execsql { 119 SELECT * FROM t1 natural join t2 natural join t3; 120 } 121} {1 2 3 4 5 2 3 4 5 6} 122do_test join-1.17 { 123 execsql2 { 124 SELECT * FROM t1 natural join t2 natural join t3; 125 } 126} {t1.a 1 t1.b 2 t1.c 3 t2.d 4 t3.e 5 t1.a 2 t1.b 3 t1.c 4 t2.d 5 t3.e 6} 127do_test join-1.18 { 128 execsql { 129 CREATE TABLE t4(d,e,f); 130 INSERT INTO t4 VALUES(2,3,4); 131 INSERT INTO t4 VALUES(3,4,5); 132 INSERT INTO t4 VALUES(4,5,6); 133 SELECT * FROM t4; 134 } 135} {2 3 4 3 4 5 4 5 6} 136do_test join-1.19 { 137 execsql { 138 SELECT * FROM t1 natural join t2 natural join t4; 139 } 140} {1 2 3 4 5 6} 141do_test join-1.19 { 142 execsql2 { 143 SELECT * FROM t1 natural join t2 natural join t4; 144 } 145} {t1.a 1 t1.b 2 t1.c 3 t2.d 4 t4.e 5 t4.f 6} 146do_test join-1.20 { 147 execsql { 148 SELECT * FROM t1 natural join t2 natural join t3 WHERE t1.a=1 149 } 150} {1 2 3 4 5} 151 152do_test join-2.1 { 153 execsql { 154 SELECT * FROM t1 NATURAL LEFT JOIN t2; 155 } 156} {1 2 3 4 2 3 4 5 3 4 5 {}} 157do_test join-2.2 { 158 execsql { 159 SELECT * FROM t2 NATURAL LEFT OUTER JOIN t1; 160 } 161} {1 2 3 {} 2 3 4 1 3 4 5 2} 162do_test join-2.3 { 163 catchsql { 164 SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2; 165 } 166} {1 {RIGHT and FULL OUTER JOINs are not currently supported}} 167do_test join-2.4 { 168 execsql { 169 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d 170 } 171} {1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3} 172do_test join-2.5 { 173 execsql { 174 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t1.a>1 175 } 176} {2 3 4 {} {} {} 3 4 5 1 2 3} 177do_test join-2.6 { 178 execsql { 179 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t2.b IS NULL OR t2.b>1 180 } 181} {1 2 3 {} {} {} 2 3 4 {} {} {}} 182 183do_test join-3.1 { 184 catchsql { 185 SELECT * FROM t1 NATURAL JOIN t2 ON t1.a=t2.b; 186 } 187} {1 {a NATURAL join may not have an ON or USING clause}} 188do_test join-3.2 { 189 catchsql { 190 SELECT * FROM t1 NATURAL JOIN t2 USING(b); 191 } 192} {1 {a NATURAL join may not have an ON or USING clause}} 193do_test join-3.3 { 194 catchsql { 195 SELECT * FROM t1 JOIN t2 ON t1.a=t2.b USING(b); 196 } 197} {1 {cannot have both ON and USING clauses in the same join}} 198do_test join-3.4 { 199 catchsql { 200 SELECT * FROM t1 JOIN t2 USING(a); 201 } 202} {1 {cannot join using column a - column not present in both tables}} 203do_test join-3.5 { 204 catchsql { 205 SELECT * FROM t1 USING(a); 206 } 207} {0 {1 2 3 2 3 4 3 4 5}} 208do_test join-3.6 { 209 catchsql { 210 SELECT * FROM t1 JOIN t2 ON t3.a=t2.b; 211 } 212} {1 {no such column: t3.a}} 213do_test join-3.7 { 214 catchsql { 215 SELECT * FROM t1 INNER OUTER JOIN t2; 216 } 217} {1 {unknown or unsupported join type: INNER OUTER}} 218do_test join-3.7 { 219 catchsql { 220 SELECT * FROM t1 LEFT BOGUS JOIN t2; 221 } 222} {1 {unknown or unsupported join type: LEFT BOGUS}} 223 224do_test join-4.1 { 225 execsql { 226 BEGIN; 227 CREATE TABLE t5(a INTEGER PRIMARY KEY); 228 CREATE TABLE t6(a INTEGER); 229 INSERT INTO t6 VALUES(NULL); 230 INSERT INTO t6 VALUES(NULL); 231 INSERT INTO t6 SELECT * FROM t6; 232 INSERT INTO t6 SELECT * FROM t6; 233 INSERT INTO t6 SELECT * FROM t6; 234 INSERT INTO t6 SELECT * FROM t6; 235 INSERT INTO t6 SELECT * FROM t6; 236 INSERT INTO t6 SELECT * FROM t6; 237 COMMIT; 238 } 239 execsql { 240 SELECT * FROM t6 NATURAL JOIN t5; 241 } 242} {} 243do_test join-4.2 { 244 execsql { 245 SELECT * FROM t6, t5 WHERE t6.a<t5.a; 246 } 247} {} 248do_test join-4.3 { 249 execsql { 250 SELECT * FROM t6, t5 WHERE t6.a>t5.a; 251 } 252} {} 253do_test join-4.4 { 254 execsql { 255 UPDATE t6 SET a='xyz'; 256 SELECT * FROM t6 NATURAL JOIN t5; 257 } 258} {} 259do_test join-4.6 { 260 execsql { 261 SELECT * FROM t6, t5 WHERE t6.a<t5.a; 262 } 263} {} 264do_test join-4.7 { 265 execsql { 266 SELECT * FROM t6, t5 WHERE t6.a>t5.a; 267 } 268} {} 269do_test join-4.8 { 270 execsql { 271 UPDATE t6 SET a=1; 272 SELECT * FROM t6 NATURAL JOIN t5; 273 } 274} {} 275do_test join-4.9 { 276 execsql { 277 SELECT * FROM t6, t5 WHERE t6.a<t5.a; 278 } 279} {} 280do_test join-4.10 { 281 execsql { 282 SELECT * FROM t6, t5 WHERE t6.a>t5.a; 283 } 284} {} 285 286do_test join-5.1 { 287 execsql { 288 BEGIN; 289 create table centros (id integer primary key, centro); 290 INSERT INTO centros VALUES(1,'xxx'); 291 create table usuarios (id integer primary key, nombre, apellidos, 292 idcentro integer); 293 INSERT INTO usuarios VALUES(1,'a','aa',1); 294 INSERT INTO usuarios VALUES(2,'b','bb',1); 295 INSERT INTO usuarios VALUES(3,'c','cc',NULL); 296 create index idcentro on usuarios (idcentro); 297 END; 298 select usuarios.id, usuarios.nombre, centros.centro from 299 usuarios left outer join centros on usuarios.idcentro = centros.id; 300 } 301} {1 a xxx 2 b xxx 3 c {}} 302 303# Test the goofy Oracle8 outer join syntax. 304# 305do_test join-6.1 { 306 execsql { 307 DELETE FROM t1; 308 INSERT INTO t1 VALUES(1,2,3); 309 INSERT INTO t1 VALUES(2,3,4); 310 INSERT INTO t1 VALUES(3,4,5); 311 SELECT * FROM t1; 312 } 313} {1 2 3 2 3 4 3 4 5} 314do_test join-6.2 { 315 execsql { 316 DELETE FROM t2; 317 INSERT INTO t2 VALUES(1,2,3); 318 INSERT INTO t2 VALUES(2,3,4); 319 INSERT INTO t2 VALUES(3,4,5); 320 SELECT * FROM t2; 321 } 322} {1 2 3 2 3 4 3 4 5} 323do_test join-6.3 { 324 execsql { 325 SELECT * FROM t1 LEFT OUTER JOIN t2 ON (t1.a=t2.c); 326 } 327} {1 2 3 {} {} {} 2 3 4 1 2 3 3 4 5 2 3 4} 328do_test join-6.4 { 329 execsql { 330 SELECT * FROM t1, t2 WHERE t1.a=t2.c(+); 331 } 332} {1 2 3 {} {} {} 2 3 4 1 2 3 3 4 5 2 3 4} 333do_test join-6.5 { 334 execsql { 335 SELECT * FROM t1 LEFT OUTER JOIN t2 ON (t1.a=t2.c) WHERE t1.b=2 336 } 337} {1 2 3 {} {} {}} 338do_test join-6.6 { 339 execsql { 340 SELECT * FROM t1, t2 WHERE t1.a=t2.c(+) AND t1.b=2; 341 } 342} {1 2 3 {} {} {}} 343do_test join-6.7 { 344 execsql { 345 SELECT * FROM t1 LEFT OUTER JOIN t2 ON (t1.b=t2.b AND t1.c=t2.c) 346 } 347} {1 2 3 2 3 4 2 3 4 3 4 5 3 4 5 {} {} {}} 348do_test join-6.8 { 349 execsql { 350 SELECT * FROM t1, t2 WHERE t1.b=t2.b(+) AND t1.c=t2.c(+); 351 } 352} {1 2 3 2 3 4 2 3 4 3 4 5 3 4 5 {} {} {}} 353 354 355# A test for ticket #247. 356# 357do_test join-7.1 { 358 execsql { 359 CREATE TABLE t7 (x, y); 360 INSERT INTO t7 VALUES ("pa1", 1); 361 INSERT INTO t7 VALUES ("pa2", NULL); 362 INSERT INTO t7 VALUES ("pa3", NULL); 363 INSERT INTO t7 VALUES ("pa4", 2); 364 INSERT INTO t7 VALUES ("pa30", 131); 365 INSERT INTO t7 VALUES ("pa31", 130); 366 INSERT INTO t7 VALUES ("pa28", NULL); 367 368 CREATE TABLE t8 (a integer primary key, b); 369 INSERT INTO t8 VALUES (1, "pa1"); 370 INSERT INTO t8 VALUES (2, "pa4"); 371 INSERT INTO t8 VALUES (3, NULL); 372 INSERT INTO t8 VALUES (4, NULL); 373 INSERT INTO t8 VALUES (130, "pa31"); 374 INSERT INTO t8 VALUES (131, "pa30"); 375 376 SELECT coalesce(t8.a,999) from t7 LEFT JOIN t8 on y=a; 377 } 378} {1 999 999 2 131 130 999} 379 380# Make sure a left join where the right table is really a view that 381# is itself a join works right. Ticket #306. 382# 383do_test join-8.1 { 384 execsql { 385 BEGIN; 386 CREATE TABLE t9(a INTEGER PRIMARY KEY, b); 387 INSERT INTO t9 VALUES(1,11); 388 INSERT INTO t9 VALUES(2,22); 389 CREATE TABLE t10(x INTEGER PRIMARY KEY, y); 390 INSERT INTO t10 VALUES(1,2); 391 INSERT INTO t10 VALUES(3,3); 392 CREATE TABLE t11(p INTEGER PRIMARY KEY, q); 393 INSERT INTO t11 VALUES(2,111); 394 INSERT INTO t11 VALUES(3,333); 395 CREATE VIEW v10_11 AS SELECT x, q FROM t10, t11 WHERE t10.y=t11.p; 396 COMMIT; 397 SELECT * FROM t9 LEFT JOIN v10_11 ON( a=x ); 398 } 399} {1 11 1 111 2 22 {} {}} 400do_test join-8.2 { 401 execsql { 402 SELECT * FROM t9 LEFT JOIN (SELECT x, q FROM t10, t11 WHERE t10.y=t11.p) 403 ON( a=x); 404 } 405} {1 11 1 111 2 22 {} {}} 406do_test join-8.3 { 407 execsql { 408 SELECT * FROM v10_11 LEFT JOIN t9 ON( a=x ); 409 } 410} {1 111 1 11 3 333 {} {}} 411 412# Ticket #350 describes a scenario where LEFT OUTER JOIN does not 413# function correctly if the right table in the join is really 414# subquery. 415# 416# To test the problem, we generate the same LEFT OUTER JOIN in two 417# separate selects but with on using a subquery and the other calling 418# the table directly. Then connect the two SELECTs using an EXCEPT. 419# Both queries should generate the same results so the answer should 420# be an empty set. 421# 422do_test join-9.1 { 423 execsql { 424 BEGIN; 425 CREATE TABLE t12(a,b); 426 INSERT INTO t12 VALUES(1,11); 427 INSERT INTO t12 VALUES(2,22); 428 CREATE TABLE t13(b,c); 429 INSERT INTO t13 VALUES(22,222); 430 COMMIT; 431 SELECT * FROM t12 NATURAL LEFT JOIN t13 432 EXCEPT 433 SELECT * FROM t12 NATURAL LEFT JOIN (SELECT * FROM t13 WHERE b>0); 434 } 435} {} 436do_test join-9.2 { 437 execsql { 438 CREATE VIEW v13 AS SELECT * FROM t13 WHERE b>0; 439 SELECT * FROM t12 NATURAL LEFT JOIN t13 440 EXCEPT 441 SELECT * FROM t12 NATURAL LEFT JOIN v13; 442 } 443} {} 444 445finish_test 446