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.27 2009/07/01 16:12:08 danielk1977 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 39# A FROM clause of the form: "<table>, <table> ON <expr>" is not 40# allowed by the SQLite syntax diagram, nor by any other SQL database 41# engine that we are aware of. Nevertheless, historic versions of 42# SQLite have allowed it. We need to continue to support it moving 43# forward to prevent breakage of legacy applications. Though, we will 44# not advertise it as being supported. 45# 46do_execsql_test join-1.2.1 { 47 SELECT t1.rowid, t2.rowid, '|' FROM t1, t2 ON t1.a=t2.b; 48} {1 1 | 2 2 | 3 3 |} 49 50do_test join-1.3 { 51 execsql2 { 52 SELECT * FROM t1 NATURAL JOIN t2; 53 } 54} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5} 55do_test join-1.3.1 { 56 execsql2 { 57 SELECT * FROM t2 NATURAL JOIN t1; 58 } 59} {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2} 60do_test join-1.3.2 { 61 execsql2 { 62 SELECT * FROM t2 AS x NATURAL JOIN t1; 63 } 64} {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2} 65do_test join-1.3.3 { 66 execsql2 { 67 SELECT * FROM t2 NATURAL JOIN t1 AS y; 68 } 69} {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2} 70do_test join-1.3.4 { 71 execsql { 72 SELECT b FROM t1 NATURAL JOIN t2; 73 } 74} {2 3} 75 76# ticket #3522 77do_test join-1.3.5 { 78 execsql2 { 79 SELECT t2.* FROM t2 NATURAL JOIN t1 80 } 81} {b 2 c 3 d 4 b 3 c 4 d 5} 82do_test join-1.3.6 { 83 execsql2 { 84 SELECT xyzzy.* FROM t2 AS xyzzy NATURAL JOIN t1 85 } 86} {b 2 c 3 d 4 b 3 c 4 d 5} 87do_test join-1.3.7 { 88 execsql2 { 89 SELECT t1.* FROM t2 NATURAL JOIN t1 90 } 91} {a 1 b 2 c 3 a 2 b 3 c 4} 92do_test join-1.3.8 { 93 execsql2 { 94 SELECT xyzzy.* FROM t2 NATURAL JOIN t1 AS xyzzy 95 } 96} {a 1 b 2 c 3 a 2 b 3 c 4} 97do_test join-1.3.9 { 98 execsql2 { 99 SELECT aaa.*, bbb.* FROM t2 AS aaa NATURAL JOIN t1 AS bbb 100 } 101} {b 2 c 3 d 4 a 1 b 2 c 3 b 3 c 4 d 5 a 2 b 3 c 4} 102do_test join-1.3.10 { 103 execsql2 { 104 SELECT t1.*, t2.* FROM t2 NATURAL JOIN t1 105 } 106} {a 1 b 2 c 3 b 2 c 3 d 4 a 2 b 3 c 4 b 3 c 4 d 5} 107 108 109do_test join-1.4.1 { 110 execsql2 { 111 SELECT * FROM t1 INNER JOIN t2 USING(b,c); 112 } 113} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5} 114do_test join-1.4.2 { 115 execsql2 { 116 SELECT * FROM t1 AS x INNER JOIN t2 USING(b,c); 117 } 118} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5} 119do_test join-1.4.3 { 120 execsql2 { 121 SELECT * FROM t1 INNER JOIN t2 AS y USING(b,c); 122 } 123} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5} 124do_test join-1.4.4 { 125 execsql2 { 126 SELECT * FROM t1 AS x INNER JOIN t2 AS y USING(b,c); 127 } 128} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5} 129do_test join-1.4.5 { 130 execsql { 131 SELECT b FROM t1 JOIN t2 USING(b); 132 } 133} {2 3} 134 135# Ticket #3522 136do_test join-1.4.6 { 137 execsql2 { 138 SELECT t1.* FROM t1 JOIN t2 USING(b); 139 } 140} {a 1 b 2 c 3 a 2 b 3 c 4} 141do_test join-1.4.7 { 142 execsql2 { 143 SELECT t2.* FROM t1 JOIN t2 USING(b); 144 } 145} {b 2 c 3 d 4 b 3 c 4 d 5} 146 147do_test join-1.5 { 148 execsql2 { 149 SELECT * FROM t1 INNER JOIN t2 USING(b); 150 } 151} {a 1 b 2 c 3 c 3 d 4 a 2 b 3 c 4 c 4 d 5} 152do_test join-1.6 { 153 execsql2 { 154 SELECT * FROM t1 INNER JOIN t2 USING(c); 155 } 156} {a 1 b 2 c 3 b 2 d 4 a 2 b 3 c 4 b 3 d 5} 157do_test join-1.7 { 158 execsql2 { 159 SELECT * FROM t1 INNER JOIN t2 USING(c,b); 160 } 161} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5} 162 163do_test join-1.8 { 164 execsql { 165 SELECT * FROM t1 NATURAL CROSS JOIN t2; 166 } 167} {1 2 3 4 2 3 4 5} 168do_test join-1.9 { 169 execsql { 170 SELECT * FROM t1 CROSS JOIN t2 USING(b,c); 171 } 172} {1 2 3 4 2 3 4 5} 173do_test join-1.10 { 174 execsql { 175 SELECT * FROM t1 NATURAL INNER JOIN t2; 176 } 177} {1 2 3 4 2 3 4 5} 178do_test join-1.11 { 179 execsql { 180 SELECT * FROM t1 INNER JOIN t2 USING(b,c); 181 } 182} {1 2 3 4 2 3 4 5} 183do_test join-1.12 { 184 execsql { 185 SELECT * FROM t1 natural inner join t2; 186 } 187} {1 2 3 4 2 3 4 5} 188 189ifcapable subquery { 190 do_test join-1.13 { 191 execsql2 { 192 SELECT * FROM t1 NATURAL JOIN 193 (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as t3 194 } 195 } {a 1 b 2 c 3 d 4 e 5} 196 do_test join-1.14 { 197 execsql2 { 198 SELECT * FROM (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as 'tx' 199 NATURAL JOIN t1 200 } 201 } {c 3 d 4 e 5 a 1 b 2} 202} 203 204do_test join-1.15 { 205 execsql { 206 CREATE TABLE t3(c,d,e); 207 INSERT INTO t3 VALUES(2,3,4); 208 INSERT INTO t3 VALUES(3,4,5); 209 INSERT INTO t3 VALUES(4,5,6); 210 SELECT * FROM t3; 211 } 212} {2 3 4 3 4 5 4 5 6} 213do_test join-1.16 { 214 execsql { 215 SELECT * FROM t1 natural join t2 natural join t3; 216 } 217} {1 2 3 4 5 2 3 4 5 6} 218do_test join-1.17 { 219 execsql2 { 220 SELECT * FROM t1 natural join t2 natural join t3; 221 } 222} {a 1 b 2 c 3 d 4 e 5 a 2 b 3 c 4 d 5 e 6} 223do_test join-1.18 { 224 execsql { 225 CREATE TABLE t4(d,e,f); 226 INSERT INTO t4 VALUES(2,3,4); 227 INSERT INTO t4 VALUES(3,4,5); 228 INSERT INTO t4 VALUES(4,5,6); 229 SELECT * FROM t4; 230 } 231} {2 3 4 3 4 5 4 5 6} 232do_test join-1.19.1 { 233 execsql { 234 SELECT * FROM t1 natural join t2 natural join t4; 235 } 236} {1 2 3 4 5 6} 237do_test join-1.19.2 { 238 execsql2 { 239 SELECT * FROM t1 natural join t2 natural join t4; 240 } 241} {a 1 b 2 c 3 d 4 e 5 f 6} 242do_test join-1.20 { 243 execsql { 244 SELECT * FROM t1 natural join t2 natural join t3 WHERE t1.a=1 245 } 246} {1 2 3 4 5} 247 248do_test join-2.1 { 249 execsql { 250 SELECT * FROM t1 NATURAL LEFT JOIN t2; 251 } 252} {1 2 3 4 2 3 4 5 3 4 5 {}} 253 254# ticket #3522 255do_test join-2.1.1 { 256 execsql2 { 257 SELECT * FROM t1 NATURAL LEFT JOIN t2; 258 } 259} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5 a 3 b 4 c 5 d {}} 260do_test join-2.1.2 { 261 execsql2 { 262 SELECT t1.* FROM t1 NATURAL LEFT JOIN t2; 263 } 264} {a 1 b 2 c 3 a 2 b 3 c 4 a 3 b 4 c 5} 265do_test join-2.1.3 { 266 execsql2 { 267 SELECT t2.* FROM t1 NATURAL LEFT JOIN t2; 268 } 269} {b 2 c 3 d 4 b 3 c 4 d 5 b {} c {} d {}} 270 271do_test join-2.2 { 272 execsql { 273 SELECT * FROM t2 NATURAL LEFT OUTER JOIN t1; 274 } 275} {1 2 3 {} 2 3 4 1 3 4 5 2} 276do_test join-2.3 { 277 catchsql { 278 SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2; 279 } 280} {1 {RIGHT and FULL OUTER JOINs are not currently supported}} 281do_test join-2.4 { 282 execsql { 283 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d 284 } 285} {1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3} 286do_test join-2.5 { 287 execsql { 288 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t1.a>1 289 } 290} {2 3 4 {} {} {} 3 4 5 1 2 3} 291do_test join-2.6 { 292 execsql { 293 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t2.b IS NULL OR t2.b>1 294 } 295} {1 2 3 {} {} {} 2 3 4 {} {} {}} 296 297do_test join-3.1 { 298 catchsql { 299 SELECT * FROM t1 NATURAL JOIN t2 ON t1.a=t2.b; 300 } 301} {1 {a NATURAL join may not have an ON or USING clause}} 302do_test join-3.2 { 303 catchsql { 304 SELECT * FROM t1 NATURAL JOIN t2 USING(b); 305 } 306} {1 {a NATURAL join may not have an ON or USING clause}} 307do_test join-3.3 { 308 catchsql { 309 SELECT * FROM t1 JOIN t2 ON t1.a=t2.b USING(b); 310 } 311} {1 {cannot have both ON and USING clauses in the same join}} 312do_test join-3.4.1 { 313 catchsql { 314 SELECT * FROM t1 JOIN t2 USING(a); 315 } 316} {1 {cannot join using column a - column not present in both tables}} 317do_test join-3.4.2 { 318 catchsql { 319 SELECT * FROM t1 JOIN t2 USING(d); 320 } 321} {1 {cannot join using column d - column not present in both tables}} 322do_test join-3.5 { 323 catchsql { SELECT * FROM t1 USING(a) } 324} {1 {a JOIN clause is required before USING}} 325do_test join-3.6 { 326 catchsql { 327 SELECT * FROM t1 JOIN t2 ON t3.a=t2.b; 328 } 329} {1 {no such column: t3.a}} 330do_test join-3.7 { 331 catchsql { 332 SELECT * FROM t1 INNER OUTER JOIN t2; 333 } 334} {1 {unknown or unsupported join type: INNER OUTER}} 335do_test join-3.8 { 336 catchsql { 337 SELECT * FROM t1 INNER OUTER CROSS JOIN t2; 338 } 339} {1 {unknown or unsupported join type: INNER OUTER CROSS}} 340do_test join-3.9 { 341 catchsql { 342 SELECT * FROM t1 OUTER NATURAL INNER JOIN t2; 343 } 344} {1 {unknown or unsupported join type: OUTER NATURAL INNER}} 345do_test join-3.10 { 346 catchsql { 347 SELECT * FROM t1 LEFT BOGUS JOIN t2; 348 } 349} {1 {unknown or unsupported join type: LEFT BOGUS}} 350do_test join-3.11 { 351 catchsql { 352 SELECT * FROM t1 INNER BOGUS CROSS JOIN t2; 353 } 354} {1 {unknown or unsupported join type: INNER BOGUS CROSS}} 355do_test join-3.12 { 356 catchsql { 357 SELECT * FROM t1 NATURAL AWK SED JOIN t2; 358 } 359} {1 {unknown or unsupported join type: NATURAL AWK SED}} 360 361do_test join-4.1 { 362 execsql { 363 BEGIN; 364 CREATE TABLE t5(a INTEGER PRIMARY KEY); 365 CREATE TABLE t6(a INTEGER); 366 INSERT INTO t6 VALUES(NULL); 367 INSERT INTO t6 VALUES(NULL); 368 INSERT INTO t6 SELECT * FROM t6; 369 INSERT INTO t6 SELECT * FROM t6; 370 INSERT INTO t6 SELECT * FROM t6; 371 INSERT INTO t6 SELECT * FROM t6; 372 INSERT INTO t6 SELECT * FROM t6; 373 INSERT INTO t6 SELECT * FROM t6; 374 COMMIT; 375 } 376 execsql { 377 SELECT * FROM t6 NATURAL JOIN t5; 378 } 379} {} 380do_test join-4.2 { 381 execsql { 382 SELECT * FROM t6, t5 WHERE t6.a<t5.a; 383 } 384} {} 385do_test join-4.3 { 386 execsql { 387 SELECT * FROM t6, t5 WHERE t6.a>t5.a; 388 } 389} {} 390do_test join-4.4 { 391 execsql { 392 UPDATE t6 SET a='xyz'; 393 SELECT * FROM t6 NATURAL JOIN t5; 394 } 395} {} 396do_test join-4.6 { 397 execsql { 398 SELECT * FROM t6, t5 WHERE t6.a<t5.a; 399 } 400} {} 401do_test join-4.7 { 402 execsql { 403 SELECT * FROM t6, t5 WHERE t6.a>t5.a; 404 } 405} {} 406do_test join-4.8 { 407 execsql { 408 UPDATE t6 SET a=1; 409 SELECT * FROM t6 NATURAL JOIN t5; 410 } 411} {} 412do_test join-4.9 { 413 execsql { 414 SELECT * FROM t6, t5 WHERE t6.a<t5.a; 415 } 416} {} 417do_test join-4.10 { 418 execsql { 419 SELECT * FROM t6, t5 WHERE t6.a>t5.a; 420 } 421} {} 422 423do_test join-5.1 { 424 execsql { 425 BEGIN; 426 create table centros (id integer primary key, centro); 427 INSERT INTO centros VALUES(1,'xxx'); 428 create table usuarios (id integer primary key, nombre, apellidos, 429 idcentro integer); 430 INSERT INTO usuarios VALUES(1,'a','aa',1); 431 INSERT INTO usuarios VALUES(2,'b','bb',1); 432 INSERT INTO usuarios VALUES(3,'c','cc',NULL); 433 create index idcentro on usuarios (idcentro); 434 END; 435 select usuarios.id, usuarios.nombre, centros.centro from 436 usuarios left outer join centros on usuarios.idcentro = centros.id; 437 } 438} {1 a xxx 2 b xxx 3 c {}} 439 440# A test for ticket #247. 441# 442do_test join-7.1 { 443 execsql { 444 CREATE TABLE t7 (x, y); 445 INSERT INTO t7 VALUES ("pa1", 1); 446 INSERT INTO t7 VALUES ("pa2", NULL); 447 INSERT INTO t7 VALUES ("pa3", NULL); 448 INSERT INTO t7 VALUES ("pa4", 2); 449 INSERT INTO t7 VALUES ("pa30", 131); 450 INSERT INTO t7 VALUES ("pa31", 130); 451 INSERT INTO t7 VALUES ("pa28", NULL); 452 453 CREATE TABLE t8 (a integer primary key, b); 454 INSERT INTO t8 VALUES (1, "pa1"); 455 INSERT INTO t8 VALUES (2, "pa4"); 456 INSERT INTO t8 VALUES (3, NULL); 457 INSERT INTO t8 VALUES (4, NULL); 458 INSERT INTO t8 VALUES (130, "pa31"); 459 INSERT INTO t8 VALUES (131, "pa30"); 460 461 SELECT coalesce(t8.a,999) from t7 LEFT JOIN t8 on y=a; 462 } 463} {1 999 999 2 131 130 999} 464 465# Make sure a left join where the right table is really a view that 466# is itself a join works right. Ticket #306. 467# 468ifcapable view { 469do_test join-8.1 { 470 execsql { 471 BEGIN; 472 CREATE TABLE t9(a INTEGER PRIMARY KEY, b); 473 INSERT INTO t9 VALUES(1,11); 474 INSERT INTO t9 VALUES(2,22); 475 CREATE TABLE t10(x INTEGER PRIMARY KEY, y); 476 INSERT INTO t10 VALUES(1,2); 477 INSERT INTO t10 VALUES(3,3); 478 CREATE TABLE t11(p INTEGER PRIMARY KEY, q); 479 INSERT INTO t11 VALUES(2,111); 480 INSERT INTO t11 VALUES(3,333); 481 CREATE VIEW v10_11 AS SELECT x, q FROM t10, t11 WHERE t10.y=t11.p; 482 COMMIT; 483 SELECT * FROM t9 LEFT JOIN v10_11 ON( a=x ); 484 } 485} {1 11 1 111 2 22 {} {}} 486ifcapable subquery { 487 do_test join-8.2 { 488 execsql { 489 SELECT * FROM t9 LEFT JOIN (SELECT x, q FROM t10, t11 WHERE t10.y=t11.p) 490 ON( a=x); 491 } 492 } {1 11 1 111 2 22 {} {}} 493} 494do_test join-8.3 { 495 execsql { 496 SELECT * FROM v10_11 LEFT JOIN t9 ON( a=x ); 497 } 498} {1 111 1 11 3 333 {} {}} 499ifcapable subquery { 500 # Constant expressions in a subquery that is the right element of a 501 # LEFT JOIN evaluate to NULL for rows where the LEFT JOIN does not 502 # match. Ticket #3300 503 do_test join-8.4 { 504 execsql { 505 SELECT * FROM t9 LEFT JOIN (SELECT 44, p, q FROM t11) AS sub1 ON p=a 506 } 507 } {1 11 {} {} {} 2 22 44 2 111} 508} 509} ;# ifcapable view 510 511# Ticket #350 describes a scenario where LEFT OUTER JOIN does not 512# function correctly if the right table in the join is really 513# subquery. 514# 515# To test the problem, we generate the same LEFT OUTER JOIN in two 516# separate selects but with on using a subquery and the other calling 517# the table directly. Then connect the two SELECTs using an EXCEPT. 518# Both queries should generate the same results so the answer should 519# be an empty set. 520# 521ifcapable compound { 522do_test join-9.1 { 523 execsql { 524 BEGIN; 525 CREATE TABLE t12(a,b); 526 INSERT INTO t12 VALUES(1,11); 527 INSERT INTO t12 VALUES(2,22); 528 CREATE TABLE t13(b,c); 529 INSERT INTO t13 VALUES(22,222); 530 COMMIT; 531 } 532} {} 533 534ifcapable subquery { 535 do_test join-9.1.1 { 536 execsql { 537 SELECT * FROM t12 NATURAL LEFT JOIN t13 538 EXCEPT 539 SELECT * FROM t12 NATURAL LEFT JOIN (SELECT * FROM t13 WHERE b>0); 540 } 541 } {} 542} 543ifcapable view { 544 do_test join-9.2 { 545 execsql { 546 CREATE VIEW v13 AS SELECT * FROM t13 WHERE b>0; 547 SELECT * FROM t12 NATURAL LEFT JOIN t13 548 EXCEPT 549 SELECT * FROM t12 NATURAL LEFT JOIN v13; 550 } 551 } {} 552} ;# ifcapable view 553} ;# ifcapable compound 554 555ifcapable subquery { 556 # Ticket #1697: Left Join WHERE clause terms that contain an 557 # aggregate subquery. 558 # 559 do_test join-10.1 { 560 execsql { 561 CREATE TABLE t21(a,b,c); 562 CREATE TABLE t22(p,q); 563 CREATE INDEX i22 ON t22(q); 564 SELECT a FROM t21 LEFT JOIN t22 ON b=p WHERE q= 565 (SELECT max(m.q) FROM t22 m JOIN t21 n ON n.b=m.p WHERE n.c=1); 566 } 567 } {} 568 569 # Test a LEFT JOIN when the right-hand side of hte join is an empty 570 # sub-query. Seems fine. 571 # 572 do_test join-10.2 { 573 execsql { 574 CREATE TABLE t23(a, b, c); 575 CREATE TABLE t24(a, b, c); 576 INSERT INTO t23 VALUES(1, 2, 3); 577 } 578 execsql { 579 SELECT * FROM t23 LEFT JOIN t24; 580 } 581 } {1 2 3 {} {} {}} 582 do_test join-10.3 { 583 execsql { 584 SELECT * FROM t23 LEFT JOIN (SELECT * FROM t24); 585 } 586 } {1 2 3 {} {} {}} 587 588} ;# ifcapable subquery 589 590#------------------------------------------------------------------------- 591# The following tests are to ensure that bug b73fb0bd64 is fixed. 592# 593do_test join-11.1 { 594 drop_all_tables 595 execsql { 596 CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT); 597 CREATE TABLE t2(a INTEGER PRIMARY KEY, b TEXT); 598 INSERT INTO t1 VALUES(1,'abc'); 599 INSERT INTO t1 VALUES(2,'def'); 600 INSERT INTO t2 VALUES(1,'abc'); 601 INSERT INTO t2 VALUES(2,'def'); 602 SELECT * FROM t1 NATURAL JOIN t2; 603 } 604} {1 abc 2 def} 605 606do_test join-11.2 { 607 execsql { SELECT a FROM t1 JOIN t1 USING (a)} 608} {1 2} 609do_test join-11.3 { 610 execsql { SELECT a FROM t1 JOIN t1 AS t2 USING (a)} 611} {1 2} 612do_test join-11.3 { 613 execsql { SELECT * FROM t1 NATURAL JOIN t1 AS t2} 614} {1 abc 2 def} 615do_test join-11.4 { 616 execsql { SELECT * FROM t1 NATURAL JOIN t1 } 617} {1 abc 2 def} 618 619do_test join-11.5 { 620 drop_all_tables 621 execsql { 622 CREATE TABLE t1(a COLLATE nocase, b); 623 CREATE TABLE t2(a, b); 624 INSERT INTO t1 VALUES('ONE', 1); 625 INSERT INTO t1 VALUES('two', 2); 626 INSERT INTO t2 VALUES('one', 1); 627 INSERT INTO t2 VALUES('two', 2); 628 } 629} {} 630do_test join-11.6 { 631 execsql { SELECT * FROM t1 NATURAL JOIN t2 } 632} {ONE 1 two 2} 633do_test join-11.7 { 634 execsql { SELECT * FROM t2 NATURAL JOIN t1 } 635} {two 2} 636 637do_test join-11.8 { 638 drop_all_tables 639 execsql { 640 CREATE TABLE t1(a, b TEXT); 641 CREATE TABLE t2(b INTEGER, a); 642 INSERT INTO t1 VALUES('one', '1.0'); 643 INSERT INTO t1 VALUES('two', '2'); 644 INSERT INTO t2 VALUES(1, 'one'); 645 INSERT INTO t2 VALUES(2, 'two'); 646 } 647} {} 648do_test join-11.9 { 649 execsql { SELECT * FROM t1 NATURAL JOIN t2 } 650} {one 1.0 two 2} 651do_test join-11.10 { 652 execsql { SELECT * FROM t2 NATURAL JOIN t1 } 653} {1 one 2 two} 654 655#------------------------------------------------------------------------- 656# Test that at most 64 tables are allowed in a join. 657# 658do_execsql_test join-12.1 { 659 CREATE TABLE t14(x); 660 INSERT INTO t14 VALUES('abcdefghij'); 661} 662 663proc jointest {tn nTbl res} { 664 set sql "SELECT 1 FROM [string repeat t14, [expr $nTbl-1]] t14;" 665 uplevel [list do_catchsql_test $tn $sql $res] 666} 667 668jointest join-12.2 30 {0 1} 669jointest join-12.3 63 {0 1} 670jointest join-12.4 64 {0 1} 671jointest join-12.5 65 {1 {at most 64 tables in a join}} 672jointest join-12.6 66 {1 {at most 64 tables in a join}} 673jointest join-12.7 127 {1 {at most 64 tables in a join}} 674jointest join-12.8 128 {1 {at most 64 tables in a join}} 675jointest join-12.9 1000 {1 {at most 64 tables in a join}} 676 677# If SQLite is built with SQLITE_MEMDEBUG, then the huge number of realloc() 678# calls made by the following test cases are too time consuming to run. 679# Without SQLITE_MEMDEBUG, realloc() is fast enough that these are not 680# a problem. 681ifcapable pragma&&compileoption_diags { 682 if {[lsearch [db eval {PRAGMA compile_options}] MEMDEBUG]<0} { 683 jointest join-12.10 65534 {1 {at most 64 tables in a join}} 684 jointest join-12.11 65535 {1 {too many references to "t14": max 65535}} 685 jointest join-12.12 65536 {1 {too many references to "t14": max 65535}} 686 jointest join-12.13 65537 {1 {too many references to "t14": max 65535}} 687 } 688} 689 690 691#------------------------------------------------------------------------- 692# Test a problem with reordering tables following a LEFT JOIN. 693# 694do_execsql_test join-13.0 { 695 CREATE TABLE aa(a); 696 CREATE TABLE bb(b); 697 CREATE TABLE cc(c); 698 699 INSERT INTO aa VALUES(45); 700 INSERT INTO cc VALUES(45); 701 INSERT INTO cc VALUES(45); 702} 703 704do_execsql_test join-13.1 { 705 SELECT * FROM aa LEFT JOIN bb, cc WHERE cc.c=aa.a; 706} {45 {} 45 45 {} 45} 707 708# In the following, the order of [cc] and [bb] must not be exchanged, even 709# though this would be helpful if the query used an inner join. 710do_execsql_test join-13.2 { 711 CREATE INDEX ccc ON cc(c); 712 SELECT * FROM aa LEFT JOIN bb, cc WHERE cc.c=aa.a; 713} {45 {} 45 45 {} 45} 714 715 716finish_test 717