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