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.26 2008/12/05 00:00:07 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} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5} 44do_test join-1.3.1 { 45 execsql2 { 46 SELECT * FROM t2 NATURAL JOIN t1; 47 } 48} {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2} 49do_test join-1.3.2 { 50 execsql2 { 51 SELECT * FROM t2 AS x NATURAL JOIN t1; 52 } 53} {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2} 54do_test join-1.3.3 { 55 execsql2 { 56 SELECT * FROM t2 NATURAL JOIN t1 AS y; 57 } 58} {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2} 59do_test join-1.3.4 { 60 execsql { 61 SELECT b FROM t1 NATURAL JOIN t2; 62 } 63} {2 3} 64 65# ticket #3522 66do_test join-1.3.5 { 67 execsql2 { 68 SELECT t2.* FROM t2 NATURAL JOIN t1 69 } 70} {b 2 c 3 d 4 b 3 c 4 d 5} 71do_test join-1.3.6 { 72 execsql2 { 73 SELECT xyzzy.* FROM t2 AS xyzzy NATURAL JOIN t1 74 } 75} {b 2 c 3 d 4 b 3 c 4 d 5} 76do_test join-1.3.7 { 77 execsql2 { 78 SELECT t1.* FROM t2 NATURAL JOIN t1 79 } 80} {a 1 b 2 c 3 a 2 b 3 c 4} 81do_test join-1.3.8 { 82 execsql2 { 83 SELECT xyzzy.* FROM t2 NATURAL JOIN t1 AS xyzzy 84 } 85} {a 1 b 2 c 3 a 2 b 3 c 4} 86do_test join-1.3.9 { 87 execsql2 { 88 SELECT aaa.*, bbb.* FROM t2 AS aaa NATURAL JOIN t1 AS bbb 89 } 90} {b 2 c 3 d 4 a 1 b 2 c 3 b 3 c 4 d 5 a 2 b 3 c 4} 91do_test join-1.3.10 { 92 execsql2 { 93 SELECT t1.*, t2.* FROM t2 NATURAL JOIN t1 94 } 95} {a 1 b 2 c 3 b 2 c 3 d 4 a 2 b 3 c 4 b 3 c 4 d 5} 96 97 98do_test join-1.4.1 { 99 execsql2 { 100 SELECT * FROM t1 INNER JOIN t2 USING(b,c); 101 } 102} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5} 103do_test join-1.4.2 { 104 execsql2 { 105 SELECT * FROM t1 AS x INNER JOIN t2 USING(b,c); 106 } 107} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5} 108do_test join-1.4.3 { 109 execsql2 { 110 SELECT * FROM t1 INNER JOIN t2 AS y USING(b,c); 111 } 112} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5} 113do_test join-1.4.4 { 114 execsql2 { 115 SELECT * FROM t1 AS x INNER JOIN t2 AS y USING(b,c); 116 } 117} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5} 118do_test join-1.4.5 { 119 execsql { 120 SELECT b FROM t1 JOIN t2 USING(b); 121 } 122} {2 3} 123 124# Ticket #3522 125do_test join-1.4.6 { 126 execsql2 { 127 SELECT t1.* FROM t1 JOIN t2 USING(b); 128 } 129} {a 1 b 2 c 3 a 2 b 3 c 4} 130do_test join-1.4.7 { 131 execsql2 { 132 SELECT t2.* FROM t1 JOIN t2 USING(b); 133 } 134} {b 2 c 3 d 4 b 3 c 4 d 5} 135 136do_test join-1.5 { 137 execsql2 { 138 SELECT * FROM t1 INNER JOIN t2 USING(b); 139 } 140} {a 1 b 2 c 3 c 3 d 4 a 2 b 3 c 4 c 4 d 5} 141do_test join-1.6 { 142 execsql2 { 143 SELECT * FROM t1 INNER JOIN t2 USING(c); 144 } 145} {a 1 b 2 c 3 b 2 d 4 a 2 b 3 c 4 b 3 d 5} 146do_test join-1.7 { 147 execsql2 { 148 SELECT * FROM t1 INNER JOIN t2 USING(c,b); 149 } 150} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5} 151 152do_test join-1.8 { 153 execsql { 154 SELECT * FROM t1 NATURAL CROSS JOIN t2; 155 } 156} {1 2 3 4 2 3 4 5} 157do_test join-1.9 { 158 execsql { 159 SELECT * FROM t1 CROSS JOIN t2 USING(b,c); 160 } 161} {1 2 3 4 2 3 4 5} 162do_test join-1.10 { 163 execsql { 164 SELECT * FROM t1 NATURAL INNER JOIN t2; 165 } 166} {1 2 3 4 2 3 4 5} 167do_test join-1.11 { 168 execsql { 169 SELECT * FROM t1 INNER JOIN t2 USING(b,c); 170 } 171} {1 2 3 4 2 3 4 5} 172do_test join-1.12 { 173 execsql { 174 SELECT * FROM t1 natural inner join t2; 175 } 176} {1 2 3 4 2 3 4 5} 177 178ifcapable subquery { 179 do_test join-1.13 { 180 execsql2 { 181 SELECT * FROM t1 NATURAL JOIN 182 (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as t3 183 } 184 } {a 1 b 2 c 3 d 4 e 5} 185 do_test join-1.14 { 186 execsql2 { 187 SELECT * FROM (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as 'tx' 188 NATURAL JOIN t1 189 } 190 } {c 3 d 4 e 5 a 1 b 2} 191} 192 193do_test join-1.15 { 194 execsql { 195 CREATE TABLE t3(c,d,e); 196 INSERT INTO t3 VALUES(2,3,4); 197 INSERT INTO t3 VALUES(3,4,5); 198 INSERT INTO t3 VALUES(4,5,6); 199 SELECT * FROM t3; 200 } 201} {2 3 4 3 4 5 4 5 6} 202do_test join-1.16 { 203 execsql { 204 SELECT * FROM t1 natural join t2 natural join t3; 205 } 206} {1 2 3 4 5 2 3 4 5 6} 207do_test join-1.17 { 208 execsql2 { 209 SELECT * FROM t1 natural join t2 natural join t3; 210 } 211} {a 1 b 2 c 3 d 4 e 5 a 2 b 3 c 4 d 5 e 6} 212do_test join-1.18 { 213 execsql { 214 CREATE TABLE t4(d,e,f); 215 INSERT INTO t4 VALUES(2,3,4); 216 INSERT INTO t4 VALUES(3,4,5); 217 INSERT INTO t4 VALUES(4,5,6); 218 SELECT * FROM t4; 219 } 220} {2 3 4 3 4 5 4 5 6} 221do_test join-1.19.1 { 222 execsql { 223 SELECT * FROM t1 natural join t2 natural join t4; 224 } 225} {1 2 3 4 5 6} 226do_test join-1.19.2 { 227 execsql2 { 228 SELECT * FROM t1 natural join t2 natural join t4; 229 } 230} {a 1 b 2 c 3 d 4 e 5 f 6} 231do_test join-1.20 { 232 execsql { 233 SELECT * FROM t1 natural join t2 natural join t3 WHERE t1.a=1 234 } 235} {1 2 3 4 5} 236 237do_test join-2.1 { 238 execsql { 239 SELECT * FROM t1 NATURAL LEFT JOIN t2; 240 } 241} {1 2 3 4 2 3 4 5 3 4 5 {}} 242 243# ticket #3522 244do_test join-2.1.1 { 245 execsql2 { 246 SELECT * FROM t1 NATURAL LEFT JOIN t2; 247 } 248} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5 a 3 b 4 c 5 d {}} 249do_test join-2.1.2 { 250 execsql2 { 251 SELECT t1.* FROM t1 NATURAL LEFT JOIN t2; 252 } 253} {a 1 b 2 c 3 a 2 b 3 c 4 a 3 b 4 c 5} 254do_test join-2.1.3 { 255 execsql2 { 256 SELECT t2.* FROM t1 NATURAL LEFT JOIN t2; 257 } 258} {b 2 c 3 d 4 b 3 c 4 d 5 b {} c {} d {}} 259 260do_test join-2.2 { 261 execsql { 262 SELECT * FROM t2 NATURAL LEFT OUTER JOIN t1; 263 } 264} {1 2 3 {} 2 3 4 1 3 4 5 2} 265do_test join-2.3 { 266 catchsql { 267 SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2; 268 } 269} {1 {RIGHT and FULL OUTER JOINs are not currently supported}} 270do_test join-2.4 { 271 execsql { 272 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d 273 } 274} {1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3} 275do_test join-2.5 { 276 execsql { 277 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t1.a>1 278 } 279} {2 3 4 {} {} {} 3 4 5 1 2 3} 280do_test join-2.6 { 281 execsql { 282 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t2.b IS NULL OR t2.b>1 283 } 284} {1 2 3 {} {} {} 2 3 4 {} {} {}} 285 286do_test join-3.1 { 287 catchsql { 288 SELECT * FROM t1 NATURAL JOIN t2 ON t1.a=t2.b; 289 } 290} {1 {a NATURAL join may not have an ON or USING clause}} 291do_test join-3.2 { 292 catchsql { 293 SELECT * FROM t1 NATURAL JOIN t2 USING(b); 294 } 295} {1 {a NATURAL join may not have an ON or USING clause}} 296do_test join-3.3 { 297 catchsql { 298 SELECT * FROM t1 JOIN t2 ON t1.a=t2.b USING(b); 299 } 300} {1 {cannot have both ON and USING clauses in the same join}} 301do_test join-3.4.1 { 302 catchsql { 303 SELECT * FROM t1 JOIN t2 USING(a); 304 } 305} {1 {cannot join using column a - column not present in both tables}} 306do_test join-3.4.2 { 307 catchsql { 308 SELECT * FROM t1 JOIN t2 USING(d); 309 } 310} {1 {cannot join using column d - column not present in both tables}} 311do_test join-3.5 { 312 catchsql { 313 SELECT * FROM t1 USING(a); 314 } 315} {0 {1 2 3 2 3 4 3 4 5}} 316do_test join-3.6 { 317 catchsql { 318 SELECT * FROM t1 JOIN t2 ON t3.a=t2.b; 319 } 320} {1 {no such column: t3.a}} 321do_test join-3.7 { 322 catchsql { 323 SELECT * FROM t1 INNER OUTER JOIN t2; 324 } 325} {1 {unknown or unsupported join type: INNER OUTER}} 326do_test join-3.8 { 327 catchsql { 328 SELECT * FROM t1 INNER OUTER CROSS JOIN t2; 329 } 330} {1 {unknown or unsupported join type: INNER OUTER CROSS}} 331do_test join-3.9 { 332 catchsql { 333 SELECT * FROM t1 OUTER NATURAL INNER JOIN t2; 334 } 335} {1 {unknown or unsupported join type: OUTER NATURAL INNER}} 336do_test join-3.10 { 337 catchsql { 338 SELECT * FROM t1 LEFT BOGUS JOIN t2; 339 } 340} {1 {unknown or unsupported join type: LEFT BOGUS}} 341do_test join-3.11 { 342 catchsql { 343 SELECT * FROM t1 INNER BOGUS CROSS JOIN t2; 344 } 345} {1 {unknown or unsupported join type: INNER BOGUS CROSS}} 346do_test join-3.12 { 347 catchsql { 348 SELECT * FROM t1 NATURAL AWK SED JOIN t2; 349 } 350} {1 {unknown or unsupported join type: NATURAL AWK SED}} 351 352do_test join-4.1 { 353 execsql { 354 BEGIN; 355 CREATE TABLE t5(a INTEGER PRIMARY KEY); 356 CREATE TABLE t6(a INTEGER); 357 INSERT INTO t6 VALUES(NULL); 358 INSERT INTO t6 VALUES(NULL); 359 INSERT INTO t6 SELECT * FROM t6; 360 INSERT INTO t6 SELECT * FROM t6; 361 INSERT INTO t6 SELECT * FROM t6; 362 INSERT INTO t6 SELECT * FROM t6; 363 INSERT INTO t6 SELECT * FROM t6; 364 INSERT INTO t6 SELECT * FROM t6; 365 COMMIT; 366 } 367 execsql { 368 SELECT * FROM t6 NATURAL JOIN t5; 369 } 370} {} 371do_test join-4.2 { 372 execsql { 373 SELECT * FROM t6, t5 WHERE t6.a<t5.a; 374 } 375} {} 376do_test join-4.3 { 377 execsql { 378 SELECT * FROM t6, t5 WHERE t6.a>t5.a; 379 } 380} {} 381do_test join-4.4 { 382 execsql { 383 UPDATE t6 SET a='xyz'; 384 SELECT * FROM t6 NATURAL JOIN t5; 385 } 386} {} 387do_test join-4.6 { 388 execsql { 389 SELECT * FROM t6, t5 WHERE t6.a<t5.a; 390 } 391} {} 392do_test join-4.7 { 393 execsql { 394 SELECT * FROM t6, t5 WHERE t6.a>t5.a; 395 } 396} {} 397do_test join-4.8 { 398 execsql { 399 UPDATE t6 SET a=1; 400 SELECT * FROM t6 NATURAL JOIN t5; 401 } 402} {} 403do_test join-4.9 { 404 execsql { 405 SELECT * FROM t6, t5 WHERE t6.a<t5.a; 406 } 407} {} 408do_test join-4.10 { 409 execsql { 410 SELECT * FROM t6, t5 WHERE t6.a>t5.a; 411 } 412} {} 413 414do_test join-5.1 { 415 execsql { 416 BEGIN; 417 create table centros (id integer primary key, centro); 418 INSERT INTO centros VALUES(1,'xxx'); 419 create table usuarios (id integer primary key, nombre, apellidos, 420 idcentro integer); 421 INSERT INTO usuarios VALUES(1,'a','aa',1); 422 INSERT INTO usuarios VALUES(2,'b','bb',1); 423 INSERT INTO usuarios VALUES(3,'c','cc',NULL); 424 create index idcentro on usuarios (idcentro); 425 END; 426 select usuarios.id, usuarios.nombre, centros.centro from 427 usuarios left outer join centros on usuarios.idcentro = centros.id; 428 } 429} {1 a xxx 2 b xxx 3 c {}} 430 431# A test for ticket #247. 432# 433do_test join-7.1 { 434 execsql { 435 CREATE TABLE t7 (x, y); 436 INSERT INTO t7 VALUES ("pa1", 1); 437 INSERT INTO t7 VALUES ("pa2", NULL); 438 INSERT INTO t7 VALUES ("pa3", NULL); 439 INSERT INTO t7 VALUES ("pa4", 2); 440 INSERT INTO t7 VALUES ("pa30", 131); 441 INSERT INTO t7 VALUES ("pa31", 130); 442 INSERT INTO t7 VALUES ("pa28", NULL); 443 444 CREATE TABLE t8 (a integer primary key, b); 445 INSERT INTO t8 VALUES (1, "pa1"); 446 INSERT INTO t8 VALUES (2, "pa4"); 447 INSERT INTO t8 VALUES (3, NULL); 448 INSERT INTO t8 VALUES (4, NULL); 449 INSERT INTO t8 VALUES (130, "pa31"); 450 INSERT INTO t8 VALUES (131, "pa30"); 451 452 SELECT coalesce(t8.a,999) from t7 LEFT JOIN t8 on y=a; 453 } 454} {1 999 999 2 131 130 999} 455 456# Make sure a left join where the right table is really a view that 457# is itself a join works right. Ticket #306. 458# 459ifcapable view { 460do_test join-8.1 { 461 execsql { 462 BEGIN; 463 CREATE TABLE t9(a INTEGER PRIMARY KEY, b); 464 INSERT INTO t9 VALUES(1,11); 465 INSERT INTO t9 VALUES(2,22); 466 CREATE TABLE t10(x INTEGER PRIMARY KEY, y); 467 INSERT INTO t10 VALUES(1,2); 468 INSERT INTO t10 VALUES(3,3); 469 CREATE TABLE t11(p INTEGER PRIMARY KEY, q); 470 INSERT INTO t11 VALUES(2,111); 471 INSERT INTO t11 VALUES(3,333); 472 CREATE VIEW v10_11 AS SELECT x, q FROM t10, t11 WHERE t10.y=t11.p; 473 COMMIT; 474 SELECT * FROM t9 LEFT JOIN v10_11 ON( a=x ); 475 } 476} {1 11 1 111 2 22 {} {}} 477ifcapable subquery { 478 do_test join-8.2 { 479 execsql { 480 SELECT * FROM t9 LEFT JOIN (SELECT x, q FROM t10, t11 WHERE t10.y=t11.p) 481 ON( a=x); 482 } 483 } {1 11 1 111 2 22 {} {}} 484} 485do_test join-8.3 { 486 execsql { 487 SELECT * FROM v10_11 LEFT JOIN t9 ON( a=x ); 488 } 489} {1 111 1 11 3 333 {} {}} 490ifcapable subquery { 491 # Constant expressions in a subquery that is the right element of a 492 # LEFT JOIN evaluate to NULL for rows where the LEFT JOIN does not 493 # match. Ticket #3300 494 do_test join-8.4 { 495 execsql { 496 SELECT * FROM t9 LEFT JOIN (SELECT 44, p, q FROM t11) AS sub1 ON p=a 497 } 498 } {1 11 {} {} {} 2 22 44 2 111} 499} 500} ;# ifcapable view 501 502# Ticket #350 describes a scenario where LEFT OUTER JOIN does not 503# function correctly if the right table in the join is really 504# subquery. 505# 506# To test the problem, we generate the same LEFT OUTER JOIN in two 507# separate selects but with on using a subquery and the other calling 508# the table directly. Then connect the two SELECTs using an EXCEPT. 509# Both queries should generate the same results so the answer should 510# be an empty set. 511# 512ifcapable compound { 513do_test join-9.1 { 514 execsql { 515 BEGIN; 516 CREATE TABLE t12(a,b); 517 INSERT INTO t12 VALUES(1,11); 518 INSERT INTO t12 VALUES(2,22); 519 CREATE TABLE t13(b,c); 520 INSERT INTO t13 VALUES(22,222); 521 COMMIT; 522 } 523} {} 524 525ifcapable subquery { 526 do_test join-9.1.1 { 527 execsql { 528 SELECT * FROM t12 NATURAL LEFT JOIN t13 529 EXCEPT 530 SELECT * FROM t12 NATURAL LEFT JOIN (SELECT * FROM t13 WHERE b>0); 531 } 532 } {} 533} 534ifcapable view { 535 do_test join-9.2 { 536 execsql { 537 CREATE VIEW v13 AS SELECT * FROM t13 WHERE b>0; 538 SELECT * FROM t12 NATURAL LEFT JOIN t13 539 EXCEPT 540 SELECT * FROM t12 NATURAL LEFT JOIN v13; 541 } 542 } {} 543} ;# ifcapable view 544} ;# ifcapable compound 545 546ifcapable subquery { 547 # Ticket #1697: Left Join WHERE clause terms that contain an 548 # aggregate subquery. 549 # 550 do_test join-10.1 { 551 execsql { 552 CREATE TABLE t21(a,b,c); 553 CREATE TABLE t22(p,q); 554 CREATE INDEX i22 ON t22(q); 555 SELECT a FROM t21 LEFT JOIN t22 ON b=p WHERE q= 556 (SELECT max(m.q) FROM t22 m JOIN t21 n ON n.b=m.p WHERE n.c=1); 557 } 558 } {} 559 560 # Test a LEFT JOIN when the right-hand side of hte join is an empty 561 # sub-query. Seems fine. 562 # 563 do_test join-10.2 { 564 execsql { 565 CREATE TABLE t23(a, b, c); 566 CREATE TABLE t24(a, b, c); 567 INSERT INTO t23 VALUES(1, 2, 3); 568 } 569 execsql { 570 SELECT * FROM t23 LEFT JOIN t24; 571 } 572 } {1 2 3 {} {} {}} 573 do_test join-10.3 { 574 execsql { 575 SELECT * FROM t23 LEFT JOIN (SELECT * FROM t24); 576 } 577 } {1 2 3 {} {} {}} 578 579} ;# ifcapable subquery 580 581finish_test 582