test/join.test

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