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
 675 # As of 2019-01-17, the number of elements in a SrcList is limited
 676 # to 200.  The following tests still run, but the answer is now
 677 # an SQLITE_NOMEM error.
 678 #
 679 # jointest join-12.9 1000 {1 {at most 64 tables in a join}}
 680 #
 681 #  If SQLite is built with SQLITE_MEMDEBUG, then the huge number of realloc()
 682 #  calls made by the following test cases are too time consuming to run.
 683 #  Without SQLITE_MEMDEBUG, realloc() is fast enough that these are not
 684 #  a problem.
 685 #
 686 # ifcapable pragma&&compileoption_diags {
 687 #    if {[lsearch [db eval {PRAGMA compile_options}] MEMDEBUG]<0} {
 688 #     jointest join-12.10 65534 {1 {at most 64 tables in a join}}
 689 #     jointest join-12.11 65535 {1 {too many references to "t14": max 65535}}
 690 #     jointest join-12.12 65536 {1 {too many references to "t14": max 65535}}
 691 #     jointest join-12.13 65537 {1 {too many references to "t14": max 65535}}
 692 #   }
 693 # }
 694
 695
 696 #-------------------------------------------------------------------------
 697 # Test a problem with reordering tables following a LEFT JOIN.
 698 #
 699 do_execsql_test join-13.0 {
 700   CREATE TABLE aa(a);
 701   CREATE TABLE bb(b);
 702   CREATE TABLE cc(c);
 703
 704   INSERT INTO aa VALUES(45);
 705   INSERT INTO cc VALUES(45);
 706   INSERT INTO cc VALUES(45);
 707 }
 708
 709 do_execsql_test join-13.1 {
 710   SELECT * FROM aa LEFT JOIN bb, cc WHERE cc.c=aa.a;
 711 } {45 {} 45 45 {} 45}
 712
 713 # In the following, the order of [cc] and [bb] must not be exchanged, even
 714 # though this would be helpful if the query used an inner join.
 715 do_execsql_test join-13.2 {
 716   CREATE INDEX ccc ON cc(c);
 717   SELECT * FROM aa LEFT JOIN bb, cc WHERE cc.c=aa.a;
 718 } {45 {} 45 45 {} 45}
 719
 720 # Verify that that iTable attributes the TK_IF_NULL_ROW operators in the
 721 # expression tree are correctly updated by the query flattener.  This was
 722 # a bug discovered on 2017-05-22 by Mark Brand.
 723 #
 724 do_execsql_test join-14.1 {
 725   SELECT *
 726     FROM (SELECT 1 a) AS x
 727          LEFT JOIN (SELECT 1, * FROM (SELECT * FROM (SELECT 1)));
 728 } {1 1 1}
 729 do_execsql_test join-14.2 {
 730   SELECT *
 731   FROM (SELECT 1 a) AS x
 732     LEFT JOIN (SELECT 1, * FROM (SELECT * FROM (SELECT * FROM (SELECT 1)))) AS y
 733     JOIN (SELECT * FROM (SELECT 9)) AS z;
 734 } {1 1 1 9}
 735 do_execsql_test join-14.3 {
 736   SELECT *
 737   FROM (SELECT 111)
 738   LEFT JOIN (SELECT cc+222, * FROM (SELECT * FROM (SELECT 333 cc)));
 739 } {111 555 333}
 740
 741 do_execsql_test join-14.4 {
 742   DROP TABLE IF EXISTS t1;
 743   CREATE TABLE t1(c PRIMARY KEY, a TEXT(10000), b TEXT(10000));
 744   SELECT * FROM (SELECT 111) LEFT JOIN (SELECT c+222 FROM t1) GROUP BY 1;
 745 } {111 {}}
 746 do_execsql_test join-14.5 {
 747   DROP TABLE IF EXISTS t1;
 748   CREATE TABLE t1(c PRIMARY KEY) WITHOUT ROWID;
 749   SELECT * FROM (SELECT 111) LEFT JOIN (SELECT c+222 FROM t1) GROUP BY 1;
 750 } {111 {}}
 751
 752 # Verify the fix to ticket
 753 # https://www.sqlite.org/src/tktview/7fde638e94287d2c948cd9389
 754 #
 755 db close
 756 sqlite3 db :memory:
 757 do_execsql_test join-14.10 {
 758   CREATE TABLE t1(a);
 759   INSERT INTO t1 VALUES(1),(2),(3);
 760   CREATE VIEW v2 AS SELECT a, 1 AS b FROM t1;
 761   CREATE TABLE t3(x);
 762   INSERT INTO t3 VALUES(2),(4);
 763   SELECT *, '|' FROM t3 LEFT JOIN v2 ON a=x WHERE b=1;
 764 } {2 2 1 |}
 765 do_execsql_test join-14.11 {
 766   SELECT *, '|' FROM t3 LEFT JOIN v2 ON a=x WHERE b+1=x;
 767 } {2 2 1 |}
 768 do_execsql_test join-14.12 {
 769   SELECT *, '|' FROM t3 LEFT JOIN v2 ON a=x ORDER BY b;
 770 } {4 {} {} | 2 2 1 |}
 771
 772 # Verify the fix for ticket
 773 # https://www.sqlite.org/src/info/892fc34f173e99d8
 774 #
 775 db close
 776 sqlite3 db :memory:
 777 do_execsql_test join-14.20 {
 778   CREATE TABLE t1(id INTEGER PRIMARY KEY);
 779   CREATE TABLE t2(id INTEGER PRIMARY KEY, c2 INTEGER);
 780   CREATE TABLE t3(id INTEGER PRIMARY KEY, c3 INTEGER);
 781   INSERT INTO t1(id) VALUES(456);
 782   INSERT INTO t3(id) VALUES(1),(2);
 783   SELECT t1.id, x2.id, x3.id
 784   FROM t1
 785   LEFT JOIN (SELECT * FROM t2) AS x2 ON t1.id=x2.c2
 786   LEFT JOIN t3 AS x3 ON x2.id=x3.c3;
 787 } {456 {} {}}
 788
 789 # 2018-03-24.
 790 # E.Pasma discovered that the LEFT JOIN strength reduction optimization
 791 # was misbehaving.  The problem turned out to be that the
 792 # sqlite3ExprImpliesNotNull() routine was saying that CASE expressions
 793 # like
 794 #
 795 #     CASE WHEN true THEN true ELSE x=0 END
 796 #
 797 # could never be true if x is NULL.  The following test cases verify
 798 # that this error has been resolved.
 799 #
 800 db close
 801 sqlite3 db :memory:
 802 do_execsql_test join-15.100 {
 803   CREATE TABLE t1(a INT, b INT);
 804   INSERT INTO t1 VALUES(1,2),(3,4);
 805   CREATE TABLE t2(x INT, y INT);
 806   SELECT *, 'x'
 807     FROM t1 LEFT JOIN t2
 808    WHERE CASE WHEN FALSE THEN a=x ELSE 1 END;
 809 } {1 2 {} {} x 3 4 {} {} x}
 810 do_execsql_test join-15.105 {
 811   SELECT *, 'x'
 812     FROM t1 LEFT JOIN t2
 813    WHERE a IN (1,3,x,y);
 814 } {1 2 {} {} x 3 4 {} {} x}
 815 do_execsql_test join-15.106a {
 816   SELECT *, 'x'
 817     FROM t1 LEFT JOIN t2
 818    WHERE NOT ( 'x'='y' AND t2.y=1 );
 819 } {1 2 {} {} x 3 4 {} {} x}
 820 do_execsql_test join-15.106b {
 821   SELECT *, 'x'
 822     FROM t1 LEFT JOIN t2
 823    WHERE ~ ( 'x'='y' AND t2.y=1 );
 824 } {1 2 {} {} x 3 4 {} {} x}
 825 do_execsql_test join-15.107 {
 826   SELECT *, 'x'
 827     FROM t1 LEFT JOIN t2
 828    WHERE t2.y IS NOT 'abc'
 829 } {1 2 {} {} x 3 4 {} {} x}
 830 do_execsql_test join-15.110 {
 831   DROP TABLE t1;
 832   DROP TABLE t2;
 833   CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);
 834   INSERT INTO t1(a,b) VALUES(1,0),(11,1),(12,1),(13,1),(121,12);
 835   CREATE INDEX t1b ON t1(b);
 836   CREATE TABLE t2(x INTEGER PRIMARY KEY);
 837   INSERT INTO t2(x) VALUES(0),(1);
 838   SELECT  a1, a2, a3, a4, a5
 839    FROM (SELECT a AS a1 FROM t1 WHERE b=0)
 840         JOIN (SELECT x AS x1 FROM t2)
 841         LEFT JOIN (SELECT a AS a2, b AS b2 FROM t1)
 842           ON x1 IS TRUE AND b2=a1
 843         JOIN (SELECT x AS x2 FROM t2)
 844           ON x2<=CASE WHEN x1 THEN CASE WHEN a2 THEN 1 ELSE -1 END ELSE 0 END
 845         LEFT JOIN (SELECT a AS a3, b AS b3 FROM t1)
 846           ON x2 IS TRUE AND b3=a2
 847         JOIN (SELECT x AS x3 FROM t2)
 848           ON x3<=CASE WHEN x2 THEN CASE WHEN a3 THEN 1 ELSE -1 END ELSE 0 END
 849         LEFT JOIN (SELECT a AS a4, b AS b4 FROM t1)
 850           ON x3 IS TRUE AND b4=a3
 851         JOIN (SELECT x AS x4 FROM t2)
 852           ON x4<=CASE WHEN x3 THEN CASE WHEN a4 THEN 1 ELSE -1 END ELSE 0 END
 853         LEFT JOIN (SELECT a AS a5, b AS b5 FROM t1)
 854           ON x4 IS TRUE AND b5=a4
 855    ORDER BY a1, a2, a3, a4, a5;
 856 } {1 {} {} {} {} 1 11 {} {} {} 1 12 {} {} {} 1 12 121 {} {} 1 13 {} {} {}}
 857
 858 # 2019-02-05 Ticket https://www.sqlite.org/src/tktview/5948e09b8c415bc45da5c
 859 # Error in join due to the LEFT JOIN strength reduction optimization.
 860 #
 861 do_execsql_test join-16.100 {
 862   DROP TABLE IF EXISTS t1;
 863   DROP TABLE IF EXISTS t2;
 864   CREATE TABLE t1(a INT);
 865   INSERT INTO t1(a) VALUES(1);
 866   CREATE TABLE t2(b INT);
 867   SELECT a, b
 868     FROM t1 LEFT JOIN t2 ON 0
 869    WHERE (b IS NOT NULL)=0;
 870 } {1 {}}
 871
 872 # 2019-08-17 ticket https://sqlite.org/src/tktview/6710d2f7a13a299728ab
 873 # Ensure that constants that derive from the right-hand table of a LEFT JOIN
 874 # are never factored out, since they are not really constant.
 875 #
 876 do_execsql_test join-17.100 {
 877   DROP TABLE IF EXISTS t1;
 878   CREATE TABLE t1(x);
 879   INSERT INTO t1(x) VALUES(0),(1);
 880   SELECT * FROM t1 LEFT JOIN (SELECT abs(1) AS y FROM t1) ON x WHERE NOT(y='a');
 881 } {1 1 1 1}
 882 do_execsql_test join-17.110 {
 883   SELECT * FROM t1 LEFT JOIN (SELECT abs(1)+2 AS y FROM t1) ON x
 884    WHERE NOT(y='a');
 885 } {1 3 1 3}
 886
 887 #-------------------------------------------------------------------------
 888 reset_db
 889 do_execsql_test join-18.1 {
 890   CREATE TABLE t0(a);
 891   CREATE TABLE t1(b);
 892   CREATE VIEW v0 AS SELECT a FROM t1 LEFT JOIN t0;
 893   INSERT INTO t1 VALUES (1);
 894 } {}
 895
 896 do_execsql_test join-18.2 {
 897   SELECT * FROM v0 WHERE NOT(v0.a IS FALSE);
 898 } {{}}
 899
 900 do_execsql_test join-18.3 {
 901   SELECT * FROM t1 LEFT JOIN t0 WHERE NOT(a IS FALSE);
 902 } {1 {}}
 903
 904 do_execsql_test join-18.4 {
 905   SELECT NOT(v0.a IS FALSE) FROM v0
 906 } {1}
 907
 908 #-------------------------------------------------------------------------
 909 reset_db
 910 do_execsql_test join-19.0 {
 911   CREATE TABLE t1(a);
 912   CREATE TABLE t2(b);
 913   INSERT INTO t1(a) VALUES(0);
 914   CREATE VIEW v0(c) AS SELECT t2.b FROM t1 LEFT JOIN t2;
 915 }
 916
 917 do_execsql_test join-19.1 {
 918   SELECT * FROM v0 WHERE v0.c NOTNULL NOTNULL;
 919 } {{}}
 920
 921 do_execsql_test join-19.2 {
 922   SELECT * FROM t1 LEFT JOIN t2
 923 } {0 {}}
 924
 925 do_execsql_test join-19.3 {
 926   SELECT * FROM t1 LEFT JOIN t2 WHERE (b IS NOT NULL) IS NOT NULL;
 927 } {0 {}}
 928
 929 do_execsql_test join-19.4 {
 930   SELECT (b IS NOT NULL) IS NOT NULL FROM t1 LEFT JOIN t2
 931 } {1}
 932
 933 do_execsql_test join-19.5 {
 934   SELECT * FROM t1 LEFT JOIN t2 WHERE
 935     (b IS NOT NULL AND b IS NOT NULL) IS NOT NULL;
 936 } {0 {}}
 937
 938 # 2019-11-02 ticket 623eff57e76d45f6
 939 # The optimization of exclusing the WHERE expression of a partial index
 940 # from the WHERE clause of the query if the index is used does not work
 941 # of the table of the index is the right-hand table of a LEFT JOIN.
 942 #
 943 db close
 944 sqlite3 db :memory:
 945 do_execsql_test join-20.1 {
 946   CREATE TABLE t1(c1);
 947   CREATE TABLE t0(c0);
 948   INSERT INTO t0(c0) VALUES (0);
 949   SELECT * FROM t0 LEFT JOIN t1 WHERE NULL IN (c1);
 950 } {}
 951 do_execsql_test join-20.2 {
 952   CREATE INDEX t1x ON t1(0) WHERE NULL IN (c1);
 953   SELECT * FROM t0 LEFT JOIN t1 WHERE NULL IN (c1);
 954 } {}
 955
 956 # 2019-11-30 ticket 7f39060a24b47353
 957 # Do not allow a WHERE clause term to qualify a partial index on the
 958 # right table of a LEFT JOIN.
 959 #
 960 do_execsql_test join-21.10 {
 961   DROP TABLE t0;
 962   DROP TABLE t1;
 963   CREATE TABLE t0(aa);
 964   CREATE TABLE t1(bb);
 965   INSERT INTO t0(aa) VALUES (1);
 966   INSERT INTO t1(bb) VALUES (1);
 967   SELECT 11, * FROM t1 LEFT JOIN t0 WHERE aa ISNULL;
 968   SELECT 12, * FROM t1 LEFT JOIN t0 WHERE +aa ISNULL;
 969   SELECT 13, * FROM t1 LEFT JOIN t0 ON aa ISNULL;
 970   SELECT 14, * FROM t1 LEFT JOIN t0 ON +aa ISNULL;
 971   CREATE INDEX i0 ON t0(aa) WHERE aa ISNULL;
 972   SELECT 21, * FROM t1 LEFT JOIN t0 WHERE aa ISNULL;
 973   SELECT 22, * FROM t1 LEFT JOIN t0 WHERE +aa ISNULL;
 974   SELECT 23, * FROM t1 LEFT JOIN t0 ON aa ISNULL;
 975   SELECT 24, * FROM t1 LEFT JOIN t0 ON +aa ISNULL;
 976 } {13 1 {} 14 1 {} 23 1 {} 24 1 {}}
 977
 978 # 2019-12-18 problem with a LEFT JOIN where the RHS is a view.
 979 # Detected by Yongheng and Rui.
 980 # Follows from the optimization attempt of check-in 41c27bc0ff1d3135
 981 # on 2017-04-18
 982 #
 983 reset_db
 984 do_execsql_test join-22.10 {
 985   CREATE TABLE t0(a, b);
 986   CREATE INDEX t0a ON t0(a);
 987   INSERT INTO t0 VALUES(10,10),(10,11),(10,12);
 988   SELECT DISTINCT c FROM t0 LEFT JOIN (SELECT a+1 AS c FROM t0) ORDER BY c ;
 989 } {11}
 990
 991 # 2019-12-22 ticket 7929c1efb2d67e98
 992 #
 993 reset_db
 994 ifcapable vtab {
 995 do_execsql_test join-23.10 {
 996   CREATE TABLE t0(c0);
 997   INSERT INTO t0(c0) VALUES(123);
 998   CREATE VIEW v0(c0) AS SELECT 0 GROUP BY 1;
 999   SELECT t0.c0, v0.c0, vt0.name
1000    FROM v0, t0 LEFT JOIN pragma_table_info('t0') AS vt0
1001      ON vt0.name LIKE 'c0'
1002    WHERE v0.c0 == 0;
1003 } {123 0 c0}
1004 }
1005
1006 #-------------------------------------------------------------------------
1007 reset_db
1008 do_execsql_test join-24.1 {
1009   CREATE TABLE t1(a PRIMARY KEY, x);
1010   CREATE TABLE t2(b INT);
1011   CREATE INDEX t1aa ON t1(a, a);
1012
1013   INSERT INTO t1 VALUES('abc', 'def');
1014   INSERT INTO t2 VALUES(1);
1015 }
1016
1017 do_execsql_test join-24.2 {
1018   SELECT * FROM t2 JOIN t1 WHERE a='abc' AND x='def';
1019 } {1 abc def}
1020 do_execsql_test join-24.3 {
1021   SELECT * FROM t2 JOIN t1 WHERE a='abc' AND x='abc';
1022 } {}
1023
1024 do_execsql_test join-24.2 {
1025   SELECT * FROM t2 LEFT JOIN t1 ON a=0 WHERE (x='x' OR x IS NULL);
1026 } {1 {} {}}
1027
1028 # 2020-09-30 ticket 66e4b0e271c47145
1029 # The query flattener inserts an "expr AND expr" expression as a substitution
1030 # for the column of a view where that view column is part of an ON expression
1031 # of a LEFT JOIN.
1032 #
1033 reset_db
1034 do_execsql_test join-25.1 {
1035   CREATE TABLE t0(c0 INT);
1036   CREATE VIEW v0 AS SELECT (NULL AND 5) as c0 FROM t0;
1037   INSERT INTO t0(c0) VALUES (NULL);
1038   SELECT count(*)  FROM v0 LEFT JOIN t0 ON v0.c0;
1039 } {1}
1040
1041
1042 finish_test