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 # EVIDENCE-OF: R-52129-05406 you can say things like "OUTER LEFT NATURAL
 254 # JOIN" which means the same as "NATURAL LEFT OUTER JOIN".
 255 do_test join-2.1b {
 256   execsql {
 257     SELECT * FROM t1 OUTER LEFT NATURAL JOIN t2;
 258   }
 259 } {1 2 3 4 2 3 4 5 3 4 5 {}}
 260 do_test join-2.1c {
 261   execsql {
 262     SELECT * FROM t1 NATURAL LEFT OUTER JOIN t2;
 263   }
 264 } {1 2 3 4 2 3 4 5 3 4 5 {}}
 265
 266 # ticket #3522
 267 do_test join-2.1.1 {
 268   execsql2 {
 269     SELECT * FROM t1 NATURAL LEFT JOIN t2;
 270   }
 271 } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5 a 3 b 4 c 5 d {}}
 272 do_test join-2.1.2 {
 273   execsql2 {
 274     SELECT t1.* FROM t1 NATURAL LEFT JOIN t2;
 275   }
 276 } {a 1 b 2 c 3 a 2 b 3 c 4 a 3 b 4 c 5}
 277 do_test join-2.1.3 {
 278   execsql2 {
 279     SELECT t2.* FROM t1 NATURAL LEFT JOIN t2;
 280   }
 281 } {b 2 c 3 d 4 b 3 c 4 d 5 b {} c {} d {}}
 282
 283 do_test join-2.2 {
 284   execsql {
 285     SELECT * FROM t2 NATURAL LEFT OUTER JOIN t1;
 286   }
 287 } {1 2 3 {} 2 3 4 1 3 4 5 2}
 288
 289 #do_test join-2.3 {
 290 #  catchsql {
 291 #    SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2;
 292 #  }
 293 #} {1 {RIGHT and FULL OUTER JOINs are not currently supported}}
 294
 295 do_test join-2.4 {
 296   execsql {
 297     SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d
 298   }
 299 } {1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3}
 300 do_test join-2.5 {
 301   execsql {
 302     SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t1.a>1
 303   }
 304 } {2 3 4 {} {} {} 3 4 5 1 2 3}
 305 do_test join-2.6 {
 306   execsql {
 307     SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t2.b IS NULL OR t2.b>1
 308   }
 309 } {1 2 3 {} {} {} 2 3 4 {} {} {}}
 310
 311 do_test join-3.1 {
 312   catchsql {
 313     SELECT * FROM t1 NATURAL JOIN t2 ON t1.a=t2.b;
 314   }
 315 } {1 {a NATURAL join may not have an ON or USING clause}}
 316 do_test join-3.2 {
 317   catchsql {
 318     SELECT * FROM t1 NATURAL JOIN t2 USING(b);
 319   }
 320 } {1 {a NATURAL join may not have an ON or USING clause}}
 321 do_test join-3.3 {
 322   catchsql {
 323     SELECT * FROM t1 JOIN t2 ON t1.a=t2.b USING(b);
 324   }
 325 } {1 {near "USING": syntax error}}
 326 do_test join-3.4.1 {
 327   catchsql {
 328     SELECT * FROM t1 JOIN t2 USING(a);
 329   }
 330 } {1 {cannot join using column a - column not present in both tables}}
 331 do_test join-3.4.2 {
 332   catchsql {
 333     SELECT * FROM t1 JOIN t2 USING(d);
 334   }
 335 } {1 {cannot join using column d - column not present in both tables}}
 336 do_test join-3.5 {
 337   catchsql { SELECT * FROM t1 USING(a) }
 338 } {1 {a JOIN clause is required before USING}}
 339 do_test join-3.6 {
 340   catchsql {
 341     SELECT * FROM t1 JOIN t2 ON t3.a=t2.b;
 342   }
 343 } {1 {no such column: t3.a}}
 344
 345 # EVIDENCE-OF: R-47973-48020 you cannot say "INNER OUTER JOIN", because
 346 # that would be contradictory.
 347 do_test join-3.7 {
 348   catchsql {
 349     SELECT * FROM t1 INNER OUTER JOIN t2;
 350   }
 351 } {1 {unknown join type: INNER OUTER}}
 352 do_test join-3.8 {
 353   catchsql {
 354     SELECT * FROM t1 INNER OUTER CROSS JOIN t2;
 355   }
 356 } {1 {unknown join type: INNER OUTER CROSS}}
 357 do_test join-3.9 {
 358   catchsql {
 359     SELECT * FROM t1 OUTER NATURAL INNER JOIN t2;
 360   }
 361 } {1 {unknown join type: OUTER NATURAL INNER}}
 362 do_test join-3.10 {
 363   catchsql {
 364     SELECT * FROM t1 LEFT BOGUS JOIN t2;
 365   }
 366 } {1 {unknown join type: LEFT BOGUS}}
 367 do_test join-3.11 {
 368   catchsql {
 369     SELECT * FROM t1 INNER BOGUS CROSS JOIN t2;
 370   }
 371 } {1 {unknown join type: INNER BOGUS CROSS}}
 372 do_test join-3.12 {
 373   catchsql {
 374     SELECT * FROM t1 NATURAL AWK SED JOIN t2;
 375   }
 376 } {1 {unknown join type: NATURAL AWK SED}}
 377
 378 do_test join-4.1 {
 379   execsql {
 380     BEGIN;
 381     CREATE TABLE t5(a INTEGER PRIMARY KEY);
 382     CREATE TABLE t6(a INTEGER);
 383     INSERT INTO t6 VALUES(NULL);
 384     INSERT INTO t6 VALUES(NULL);
 385     INSERT INTO t6 SELECT * FROM t6;
 386     INSERT INTO t6 SELECT * FROM t6;
 387     INSERT INTO t6 SELECT * FROM t6;
 388     INSERT INTO t6 SELECT * FROM t6;
 389     INSERT INTO t6 SELECT * FROM t6;
 390     INSERT INTO t6 SELECT * FROM t6;
 391     COMMIT;
 392   }
 393   execsql {
 394     SELECT * FROM t6 NATURAL JOIN t5;
 395   }
 396 } {}
 397 do_test join-4.2 {
 398   execsql {
 399     SELECT * FROM t6, t5 WHERE t6.a<t5.a;
 400   }
 401 } {}
 402 do_test join-4.3 {
 403   execsql {
 404     SELECT * FROM t6, t5 WHERE t6.a>t5.a;
 405   }
 406 } {}
 407 do_test join-4.4 {
 408   execsql {
 409     UPDATE t6 SET a='xyz';
 410     SELECT * FROM t6 NATURAL JOIN t5;
 411   }
 412 } {}
 413 do_test join-4.6 {
 414   execsql {
 415     SELECT * FROM t6, t5 WHERE t6.a<t5.a;
 416   }
 417 } {}
 418 do_test join-4.7 {
 419   execsql {
 420     SELECT * FROM t6, t5 WHERE t6.a>t5.a;
 421   }
 422 } {}
 423 do_test join-4.8 {
 424   execsql {
 425     UPDATE t6 SET a=1;
 426     SELECT * FROM t6 NATURAL JOIN t5;
 427   }
 428 } {}
 429 do_test join-4.9 {
 430   execsql {
 431     SELECT * FROM t6, t5 WHERE t6.a<t5.a;
 432   }
 433 } {}
 434 do_test join-4.10 {
 435   execsql {
 436     SELECT * FROM t6, t5 WHERE t6.a>t5.a;
 437   }
 438 } {}
 439
 440 do_test join-5.1 {
 441   execsql {
 442     BEGIN;
 443     create table centros (id integer primary key, centro);
 444     INSERT INTO centros VALUES(1,'xxx');
 445     create table usuarios (id integer primary key, nombre, apellidos,
 446     idcentro integer);
 447     INSERT INTO usuarios VALUES(1,'a','aa',1);
 448     INSERT INTO usuarios VALUES(2,'b','bb',1);
 449     INSERT INTO usuarios VALUES(3,'c','cc',NULL);
 450     create index idcentro on usuarios (idcentro);
 451     END;
 452     select usuarios.id, usuarios.nombre, centros.centro from
 453     usuarios left outer join centros on usuarios.idcentro = centros.id;
 454   }
 455 } {1 a xxx 2 b xxx 3 c {}}
 456
 457 # A test for ticket #247.
 458 #
 459 do_test join-7.1 {
 460   sqlite3_db_config db SQLITE_DBCONFIG_DQS_DML 1
 461   execsql {
 462     CREATE TABLE t7 (x, y);
 463     INSERT INTO t7 VALUES ("pa1", 1);
 464     INSERT INTO t7 VALUES ("pa2", NULL);
 465     INSERT INTO t7 VALUES ("pa3", NULL);
 466     INSERT INTO t7 VALUES ("pa4", 2);
 467     INSERT INTO t7 VALUES ("pa30", 131);
 468     INSERT INTO t7 VALUES ("pa31", 130);
 469     INSERT INTO t7 VALUES ("pa28", NULL);
 470
 471     CREATE TABLE t8 (a integer primary key, b);
 472     INSERT INTO t8 VALUES (1, "pa1");
 473     INSERT INTO t8 VALUES (2, "pa4");
 474     INSERT INTO t8 VALUES (3, NULL);
 475     INSERT INTO t8 VALUES (4, NULL);
 476     INSERT INTO t8 VALUES (130, "pa31");
 477     INSERT INTO t8 VALUES (131, "pa30");
 478
 479     SELECT coalesce(t8.a,999) from t7 LEFT JOIN t8 on y=a;
 480   }
 481 } {1 999 999 2 131 130 999}
 482
 483 # Make sure a left join where the right table is really a view that
 484 # is itself a join works right.  Ticket #306.
 485 #
 486 ifcapable view {
 487 do_test join-8.1 {
 488   execsql {
 489     BEGIN;
 490     CREATE TABLE t9(a INTEGER PRIMARY KEY, b);
 491     INSERT INTO t9 VALUES(1,11);
 492     INSERT INTO t9 VALUES(2,22);
 493     CREATE TABLE t10(x INTEGER PRIMARY KEY, y);
 494     INSERT INTO t10 VALUES(1,2);
 495     INSERT INTO t10 VALUES(3,3);
 496     CREATE TABLE t11(p INTEGER PRIMARY KEY, q);
 497     INSERT INTO t11 VALUES(2,111);
 498     INSERT INTO t11 VALUES(3,333);
 499     CREATE VIEW v10_11 AS SELECT x, q FROM t10, t11 WHERE t10.y=t11.p;
 500     COMMIT;
 501     SELECT * FROM t9 LEFT JOIN v10_11 ON( a=x );
 502   }
 503 } {1 11 1 111 2 22 {} {}}
 504 ifcapable subquery {
 505   do_test join-8.2 {
 506     execsql {
 507       SELECT * FROM t9 LEFT JOIN (SELECT x, q FROM t10, t11 WHERE t10.y=t11.p)
 508            ON( a=x);
 509     }
 510   } {1 11 1 111 2 22 {} {}}
 511 }
 512 do_test join-8.3 {
 513   execsql {
 514     SELECT * FROM v10_11 LEFT JOIN t9 ON( a=x );
 515   }
 516 } {1 111 1 11 3 333 {} {}}
 517 ifcapable subquery {
 518   # Constant expressions in a subquery that is the right element of a
 519   # LEFT JOIN evaluate to NULL for rows where the LEFT JOIN does not
 520   # match.  Ticket #3300
 521   do_test join-8.4 {
 522     execsql {
 523       SELECT * FROM t9 LEFT JOIN (SELECT 44, p, q FROM t11) AS sub1 ON p=a
 524     }
 525   } {1 11 {} {} {} 2 22 44 2 111}
 526 }
 527 } ;# ifcapable view
 528
 529 # Ticket #350 describes a scenario where LEFT OUTER JOIN does not
 530 # function correctly if the right table in the join is really
 531 # subquery.
 532 #
 533 # To test the problem, we generate the same LEFT OUTER JOIN in two
 534 # separate selects but with on using a subquery and the other calling
 535 # the table directly.  Then connect the two SELECTs using an EXCEPT.
 536 # Both queries should generate the same results so the answer should
 537 # be an empty set.
 538 #
 539 ifcapable compound {
 540 do_test join-9.1 {
 541   execsql {
 542     BEGIN;
 543     CREATE TABLE t12(a,b);
 544     INSERT INTO t12 VALUES(1,11);
 545     INSERT INTO t12 VALUES(2,22);
 546     CREATE TABLE t13(b,c);
 547     INSERT INTO t13 VALUES(22,222);
 548     COMMIT;
 549   }
 550 } {}
 551
 552 ifcapable subquery {
 553   do_test join-9.1.1 {
 554     execsql {
 555       SELECT * FROM t12 NATURAL LEFT JOIN t13
 556       EXCEPT
 557       SELECT * FROM t12 NATURAL LEFT JOIN (SELECT * FROM t13 WHERE b>0);
 558     }
 559   } {}
 560 }
 561 ifcapable view {
 562   do_test join-9.2 {
 563     execsql {
 564       CREATE VIEW v13 AS SELECT * FROM t13 WHERE b>0;
 565       SELECT * FROM t12 NATURAL LEFT JOIN t13
 566         EXCEPT
 567         SELECT * FROM t12 NATURAL LEFT JOIN v13;
 568     }
 569   } {}
 570 } ;# ifcapable view
 571 } ;# ifcapable compound
 572
 573 ifcapable subquery {
 574   # Ticket #1697:  Left Join WHERE clause terms that contain an
 575   # aggregate subquery.
 576   #
 577   do_test join-10.1 {
 578     execsql {
 579       CREATE TABLE t21(a,b,c);
 580       CREATE TABLE t22(p,q);
 581       CREATE INDEX i22 ON t22(q);
 582       SELECT a FROM t21 LEFT JOIN t22 ON b=p WHERE q=
 583          (SELECT max(m.q) FROM t22 m JOIN t21 n ON n.b=m.p WHERE n.c=1);
 584     }
 585   } {}
 586
 587   # Test a LEFT JOIN when the right-hand side of hte join is an empty
 588   # sub-query. Seems fine.
 589   #
 590   do_test join-10.2 {
 591     execsql {
 592       CREATE TABLE t23(a, b, c);
 593       CREATE TABLE t24(a, b, c);
 594       INSERT INTO t23 VALUES(1, 2, 3);
 595     }
 596     execsql {
 597       SELECT * FROM t23 LEFT JOIN t24;
 598     }
 599   } {1 2 3 {} {} {}}
 600   do_test join-10.3 {
 601     execsql {
 602       SELECT * FROM t23 LEFT JOIN (SELECT * FROM t24);
 603     }
 604   } {1 2 3 {} {} {}}
 605
 606 } ;# ifcapable subquery
 607
 608 #-------------------------------------------------------------------------
 609 # The following tests are to ensure that bug b73fb0bd64 is fixed.
 610 #
 611 do_test join-11.1 {
 612   drop_all_tables
 613   execsql {
 614     CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT);
 615     CREATE TABLE t2(a INTEGER PRIMARY KEY, b TEXT);
 616     INSERT INTO t1 VALUES(1,'abc');
 617     INSERT INTO t1 VALUES(2,'def');
 618     INSERT INTO t2 VALUES(1,'abc');
 619     INSERT INTO t2 VALUES(2,'def');
 620     SELECT * FROM t1 NATURAL JOIN t2;
 621   }
 622 } {1 abc 2 def}
 623
 624 do_test join-11.2 {
 625   execsql { SELECT a FROM t1 JOIN t1 USING (a)}
 626 } {1 2}
 627 do_test join-11.3 {
 628   execsql { SELECT a FROM t1 JOIN t1 AS t2 USING (a)}
 629 } {1 2}
 630 do_test join-11.3 {
 631   execsql { SELECT * FROM t1 NATURAL JOIN t1 AS t2}
 632 } {1 abc 2 def}
 633 do_test join-11.4 {
 634   execsql { SELECT * FROM t1 NATURAL JOIN t1 }
 635 } {1 abc 2 def}
 636
 637 do_test join-11.5 {
 638   drop_all_tables
 639   execsql {
 640     CREATE TABLE t1(a COLLATE nocase, b);
 641     CREATE TABLE t2(a, b);
 642     INSERT INTO t1 VALUES('ONE', 1);
 643     INSERT INTO t1 VALUES('two', 2);
 644     INSERT INTO t2 VALUES('one', 1);
 645     INSERT INTO t2 VALUES('two', 2);
 646   }
 647 } {}
 648 do_test join-11.6 {
 649   execsql { SELECT * FROM t1 NATURAL JOIN t2 }
 650 } {ONE 1 two 2}
 651 do_test join-11.7 {
 652   execsql { SELECT * FROM t2 NATURAL JOIN t1 }
 653 } {two 2}
 654
 655 do_test join-11.8 {
 656   drop_all_tables
 657   execsql {
 658     CREATE TABLE t1(a, b TEXT);
 659     CREATE TABLE t2(b INTEGER, a);
 660     INSERT INTO t1 VALUES('one', '1.0');
 661     INSERT INTO t1 VALUES('two', '2');
 662     INSERT INTO t2 VALUES(1, 'one');
 663     INSERT INTO t2 VALUES(2, 'two');
 664   }
 665 } {}
 666 do_test join-11.9 {
 667   execsql { SELECT * FROM t1 NATURAL JOIN t2 }
 668 } {one 1.0 two 2}
 669 do_test join-11.10 {
 670   execsql { SELECT * FROM t2 NATURAL JOIN t1 }
 671 } {1 one 2 two}
 672
 673 #-------------------------------------------------------------------------
 674 # Test that at most 64 tables are allowed in a join.
 675 #
 676 do_execsql_test join-12.1 {
 677   CREATE TABLE t14(x);
 678   INSERT INTO t14 VALUES('abcdefghij');
 679 }
 680
 681 proc jointest {tn nTbl res} {
 682   set sql "SELECT 1 FROM [string repeat t14, [expr $nTbl-1]] t14;"
 683   uplevel [list do_catchsql_test $tn $sql $res]
 684 }
 685
 686 jointest join-12.2 30 {0 1}
 687 jointest join-12.3 63 {0 1}
 688 jointest join-12.4 64 {0 1}
 689 jointest join-12.5 65 {1 {at most 64 tables in a join}}
 690 jointest join-12.6 66 {1 {at most 64 tables in a join}}
 691 jointest join-12.7 127 {1 {at most 64 tables in a join}}
 692 jointest join-12.8 128 {1 {at most 64 tables in a join}}
 693
 694 # As of 2019-01-17, the number of elements in a SrcList is limited
 695 # to 200.  The following tests still run, but the answer is now
 696 # an SQLITE_NOMEM error.
 697 #
 698 # jointest join-12.9 1000 {1 {at most 64 tables in a join}}
 699 #
 700 #  If SQLite is built with SQLITE_MEMDEBUG, then the huge number of realloc()
 701 #  calls made by the following test cases are too time consuming to run.
 702 #  Without SQLITE_MEMDEBUG, realloc() is fast enough that these are not
 703 #  a problem.
 704 #
 705 # ifcapable pragma&&compileoption_diags {
 706 #    if {[lsearch [db eval {PRAGMA compile_options}] MEMDEBUG]<0} {
 707 #     jointest join-12.10 65534 {1 {at most 64 tables in a join}}
 708 #     jointest join-12.11 65535 {1 {too many references to "t14": max 65535}}
 709 #     jointest join-12.12 65536 {1 {too many references to "t14": max 65535}}
 710 #     jointest join-12.13 65537 {1 {too many references to "t14": max 65535}}
 711 #   }
 712 # }
 713
 714
 715 #-------------------------------------------------------------------------
 716 # Test a problem with reordering tables following a LEFT JOIN.
 717 #
 718 do_execsql_test join-13.0 {
 719   CREATE TABLE aa(a);
 720   CREATE TABLE bb(b);
 721   CREATE TABLE cc(c);
 722
 723   INSERT INTO aa VALUES(45);
 724   INSERT INTO cc VALUES(45);
 725   INSERT INTO cc VALUES(45);
 726 }
 727
 728 do_execsql_test join-13.1 {
 729   SELECT * FROM aa LEFT JOIN bb, cc WHERE cc.c=aa.a;
 730 } {45 {} 45 45 {} 45}
 731
 732 # In the following, the order of [cc] and [bb] must not be exchanged, even
 733 # though this would be helpful if the query used an inner join.
 734 do_execsql_test join-13.2 {
 735   CREATE INDEX ccc ON cc(c);
 736   SELECT * FROM aa LEFT JOIN bb, cc WHERE cc.c=aa.a;
 737 } {45 {} 45 45 {} 45}
 738
 739 # Verify that that iTable attributes the TK_IF_NULL_ROW operators in the
 740 # expression tree are correctly updated by the query flattener.  This was
 741 # a bug discovered on 2017-05-22 by Mark Brand.
 742 #
 743 do_execsql_test join-14.1 {
 744   SELECT *
 745     FROM (SELECT 1 a) AS x
 746          LEFT JOIN (SELECT 1, * FROM (SELECT * FROM (SELECT 1)));
 747 } {1 1 1}
 748 do_execsql_test join-14.2 {
 749   SELECT *
 750   FROM (SELECT 1 a) AS x
 751     LEFT JOIN (SELECT 1, * FROM (SELECT * FROM (SELECT * FROM (SELECT 1)))) AS y
 752     JOIN (SELECT * FROM (SELECT 9)) AS z;
 753 } {1 1 1 9}
 754 do_execsql_test join-14.3 {
 755   SELECT *
 756   FROM (SELECT 111)
 757   LEFT JOIN (SELECT cc+222, * FROM (SELECT * FROM (SELECT 333 cc)));
 758 } {111 555 333}
 759
 760 do_execsql_test join-14.4 {
 761   DROP TABLE IF EXISTS t1;
 762   CREATE TABLE t1(c PRIMARY KEY, a TEXT(10000), b TEXT(10000));
 763   SELECT * FROM (SELECT 111) LEFT JOIN (SELECT c+222 FROM t1) GROUP BY 1;
 764 } {111 {}}
 765 do_execsql_test join-14.4b {
 766   SELECT * FROM (SELECT 111) LEFT JOIN (SELECT c+222 FROM t1);
 767 } {111 {}}
 768 do_execsql_test join-14.5 {
 769   SELECT * FROM (SELECT 111 AS x UNION ALL SELECT 222)
 770                 LEFT JOIN (SELECT c+333 AS y FROM t1) ON x=y GROUP BY 1;
 771 } {111 {} 222 {}}
 772 do_execsql_test join-14.5b {
 773   SELECT count(*)
 774     FROM (SELECT 111 AS x UNION ALL SELECT 222)
 775          LEFT JOIN (SELECT c+333 AS y FROM t1) ON x=y;
 776 } {2}
 777 do_execsql_test join-14.5c {
 778   SELECT count(*)
 779     FROM (SELECT c+333 AS y FROM t1)
 780          RIGHT JOIN (SELECT 111 AS x UNION ALL SELECT 222) ON x=y;
 781 } {2}
 782 do_execsql_test join-14.6 {
 783   SELECT * FROM (SELECT 111 AS x UNION ALL SELECT 111)
 784                 LEFT JOIN (SELECT c+333 AS y FROM t1) ON x=y GROUP BY 1;
 785 } {111 {}}
 786 do_execsql_test join-14.7 {
 787   SELECT * FROM (SELECT 111 AS x UNION ALL SELECT 111 UNION ALL SELECT 222)
 788                 LEFT JOIN (SELECT c+333 AS y FROM t1) ON x=y GROUP BY 1;
 789 } {111 {} 222 {}}
 790 do_execsql_test join-14.8 {
 791   INSERT INTO t1(c) VALUES(-111);
 792   SELECT * FROM (SELECT 111 AS x UNION ALL SELECT 111 UNION ALL SELECT 222)
 793                 LEFT JOIN (SELECT c+333 AS y FROM t1) ON x=y GROUP BY 1;
 794 } {111 {} 222 222}
 795 do_execsql_test join-14.9 {
 796   DROP TABLE IF EXISTS t1;
 797   CREATE TABLE t1(c PRIMARY KEY) WITHOUT ROWID;
 798   SELECT * FROM (SELECT 111) LEFT JOIN (SELECT c+222 FROM t1) GROUP BY 1;
 799 } {111 {}}
 800
 801 # Verify the fix to ticket
 802 # https://www.sqlite.org/src/tktview/7fde638e94287d2c948cd9389
 803 #
 804 db close
 805 sqlite3 db :memory:
 806 do_execsql_test join-14.10 {
 807   CREATE TABLE t1(a);
 808   INSERT INTO t1 VALUES(1),(2),(3);
 809   CREATE VIEW v2 AS SELECT a, 1 AS b FROM t1;
 810   CREATE TABLE t3(x);
 811   INSERT INTO t3 VALUES(2),(4);
 812   SELECT *, '|' FROM t3 LEFT JOIN v2 ON a=x WHERE b=1;
 813 } {2 2 1 |}
 814 do_execsql_test join-14.11 {
 815   SELECT *, '|' FROM t3 LEFT JOIN v2 ON a=x WHERE b+1=x;
 816 } {2 2 1 |}
 817 do_execsql_test join-14.12 {
 818   SELECT *, '|' FROM t3 LEFT JOIN v2 ON a=x ORDER BY b;
 819 } {4 {} {} | 2 2 1 |}
 820
 821 # Verify the fix for ticket
 822 # https://www.sqlite.org/src/info/892fc34f173e99d8
 823 #
 824 db close
 825 sqlite3 db :memory:
 826 do_execsql_test join-14.20 {
 827   CREATE TABLE t1(id INTEGER PRIMARY KEY);
 828   CREATE TABLE t2(id INTEGER PRIMARY KEY, c2 INTEGER);
 829   CREATE TABLE t3(id INTEGER PRIMARY KEY, c3 INTEGER);
 830   INSERT INTO t1(id) VALUES(456);
 831   INSERT INTO t3(id) VALUES(1),(2);
 832   SELECT t1.id, x2.id, x3.id
 833   FROM t1
 834   LEFT JOIN (SELECT * FROM t2) AS x2 ON t1.id=x2.c2
 835   LEFT JOIN t3 AS x3 ON x2.id=x3.c3;
 836 } {456 {} {}}
 837
 838 # 2018-03-24.
 839 # E.Pasma discovered that the LEFT JOIN strength reduction optimization
 840 # was misbehaving.  The problem turned out to be that the
 841 # sqlite3ExprImpliesNotNull() routine was saying that CASE expressions
 842 # like
 843 #
 844 #     CASE WHEN true THEN true ELSE x=0 END
 845 #
 846 # could never be true if x is NULL.  The following test cases verify
 847 # that this error has been resolved.
 848 #
 849 db close
 850 sqlite3 db :memory:
 851 do_execsql_test join-15.100 {
 852   CREATE TABLE t1(a INT, b INT);
 853   INSERT INTO t1 VALUES(1,2),(3,4);
 854   CREATE TABLE t2(x INT, y INT);
 855   SELECT *, 'x'
 856     FROM t1 LEFT JOIN t2
 857    WHERE CASE WHEN FALSE THEN a=x ELSE 1 END;
 858 } {1 2 {} {} x 3 4 {} {} x}
 859 do_execsql_test join-15.105 {
 860   SELECT *, 'x'
 861     FROM t1 LEFT JOIN t2
 862    WHERE a IN (1,3,x,y);
 863 } {1 2 {} {} x 3 4 {} {} x}
 864 do_execsql_test join-15.106a {
 865   SELECT *, 'x'
 866     FROM t1 LEFT JOIN t2
 867    WHERE NOT ( 'x'='y' AND t2.y=1 );
 868 } {1 2 {} {} x 3 4 {} {} x}
 869 do_execsql_test join-15.106b {
 870   SELECT *, 'x'
 871     FROM t1 LEFT JOIN t2
 872    WHERE ~ ( 'x'='y' AND t2.y=1 );
 873 } {1 2 {} {} x 3 4 {} {} x}
 874 do_execsql_test join-15.107 {
 875   SELECT *, 'x'
 876     FROM t1 LEFT JOIN t2
 877    WHERE t2.y IS NOT 'abc'
 878 } {1 2 {} {} x 3 4 {} {} x}
 879 do_execsql_test join-15.110 {
 880   DROP TABLE t1;
 881   DROP TABLE t2;
 882   CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);
 883   INSERT INTO t1(a,b) VALUES(1,0),(11,1),(12,1),(13,1),(121,12);
 884   CREATE INDEX t1b ON t1(b);
 885   CREATE TABLE t2(x INTEGER PRIMARY KEY);
 886   INSERT INTO t2(x) VALUES(0),(1);
 887   SELECT  a1, a2, a3, a4, a5
 888    FROM (SELECT a AS a1 FROM t1 WHERE b=0)
 889         JOIN (SELECT x AS x1 FROM t2)
 890         LEFT JOIN (SELECT a AS a2, b AS b2 FROM t1)
 891           ON x1 IS TRUE AND b2=a1
 892         JOIN (SELECT x AS x2 FROM t2)
 893           ON x2<=CASE WHEN x1 THEN CASE WHEN a2 THEN 1 ELSE -1 END ELSE 0 END
 894         LEFT JOIN (SELECT a AS a3, b AS b3 FROM t1)
 895           ON x2 IS TRUE AND b3=a2
 896         JOIN (SELECT x AS x3 FROM t2)
 897           ON x3<=CASE WHEN x2 THEN CASE WHEN a3 THEN 1 ELSE -1 END ELSE 0 END
 898         LEFT JOIN (SELECT a AS a4, b AS b4 FROM t1)
 899           ON x3 IS TRUE AND b4=a3
 900         JOIN (SELECT x AS x4 FROM t2)
 901           ON x4<=CASE WHEN x3 THEN CASE WHEN a4 THEN 1 ELSE -1 END ELSE 0 END
 902         LEFT JOIN (SELECT a AS a5, b AS b5 FROM t1)
 903           ON x4 IS TRUE AND b5=a4
 904    ORDER BY a1, a2, a3, a4, a5;
 905 } {1 {} {} {} {} 1 11 {} {} {} 1 12 {} {} {} 1 12 121 {} {} 1 13 {} {} {}}
 906
 907 # 2019-02-05 Ticket https://www.sqlite.org/src/tktview/5948e09b8c415bc45da5c
 908 # Error in join due to the LEFT JOIN strength reduction optimization.
 909 #
 910 do_execsql_test join-16.100 {
 911   DROP TABLE IF EXISTS t1;
 912   DROP TABLE IF EXISTS t2;
 913   CREATE TABLE t1(a INT);
 914   INSERT INTO t1(a) VALUES(1);
 915   CREATE TABLE t2(b INT);
 916   SELECT a, b
 917     FROM t1 LEFT JOIN t2 ON 0
 918    WHERE (b IS NOT NULL)=0;
 919 } {1 {}}
 920
 921 # 2019-08-17 ticket https://sqlite.org/src/tktview/6710d2f7a13a299728ab
 922 # Ensure that constants that derive from the right-hand table of a LEFT JOIN
 923 # are never factored out, since they are not really constant.
 924 #
 925 do_execsql_test join-17.100 {
 926   DROP TABLE IF EXISTS t1;
 927   CREATE TABLE t1(x);
 928   INSERT INTO t1(x) VALUES(0),(1);
 929   SELECT * FROM t1 LEFT JOIN (SELECT abs(1) AS y FROM t1) ON x WHERE NOT(y='a');
 930 } {1 1 1 1}
 931 do_execsql_test join-17.110 {
 932   SELECT * FROM t1 LEFT JOIN (SELECT abs(1)+2 AS y FROM t1) ON x
 933    WHERE NOT(y='a');
 934 } {1 3 1 3}
 935
 936 #-------------------------------------------------------------------------
 937 reset_db
 938 do_execsql_test join-18.1 {
 939   CREATE TABLE t0(a);
 940   CREATE TABLE t1(b);
 941   CREATE VIEW v0 AS SELECT a FROM t1 LEFT JOIN t0;
 942   INSERT INTO t1 VALUES (1);
 943 } {}
 944
 945 do_execsql_test join-18.2 {
 946   SELECT * FROM v0 WHERE NOT(v0.a IS FALSE);
 947 } {{}}
 948
 949 do_execsql_test join-18.3 {
 950   SELECT * FROM t1 LEFT JOIN t0 WHERE NOT(a IS FALSE);
 951 } {1 {}}
 952
 953 do_execsql_test join-18.4 {
 954   SELECT NOT(v0.a IS FALSE) FROM v0
 955 } {1}
 956
 957 #-------------------------------------------------------------------------
 958 reset_db
 959 do_execsql_test join-19.0 {
 960   CREATE TABLE t1(a);
 961   CREATE TABLE t2(b);
 962   INSERT INTO t1(a) VALUES(0);
 963   CREATE VIEW v0(c) AS SELECT t2.b FROM t1 LEFT JOIN t2;
 964 }
 965
 966 do_execsql_test join-19.1 {
 967   SELECT * FROM v0 WHERE v0.c NOTNULL NOTNULL;
 968 } {{}}
 969
 970 do_execsql_test join-19.2 {
 971   SELECT * FROM t1 LEFT JOIN t2
 972 } {0 {}}
 973
 974 do_execsql_test join-19.3 {
 975   SELECT * FROM t1 LEFT JOIN t2 WHERE (b IS NOT NULL) IS NOT NULL;
 976 } {0 {}}
 977
 978 do_execsql_test join-19.4 {
 979   SELECT (b IS NOT NULL) IS NOT NULL FROM t1 LEFT JOIN t2
 980 } {1}
 981
 982 do_execsql_test join-19.5 {
 983   SELECT * FROM t1 LEFT JOIN t2 WHERE
 984     (b IS NOT NULL AND b IS NOT NULL) IS NOT NULL;
 985 } {0 {}}
 986
 987 # 2019-11-02 ticket 623eff57e76d45f6
 988 # The optimization of exclusing the WHERE expression of a partial index
 989 # from the WHERE clause of the query if the index is used does not work
 990 # of the table of the index is the right-hand table of a LEFT JOIN.
 991 #
 992 db close
 993 sqlite3 db :memory:
 994 do_execsql_test join-20.1 {
 995   CREATE TABLE t1(c1);
 996   CREATE TABLE t0(c0);
 997   INSERT INTO t0(c0) VALUES (0);
 998   SELECT * FROM t0 LEFT JOIN t1 WHERE NULL IN (c1);
 999 } {}
1000 do_execsql_test join-20.2 {
1001   CREATE INDEX t1x ON t1(0) WHERE NULL IN (c1);
1002   SELECT * FROM t0 LEFT JOIN t1 WHERE NULL IN (c1);
1003 } {}
1004
1005 # 2019-11-30 ticket 7f39060a24b47353
1006 # Do not allow a WHERE clause term to qualify a partial index on the
1007 # right table of a LEFT JOIN.
1008 #
1009 do_execsql_test join-21.10 {
1010   DROP TABLE t0;
1011   DROP TABLE t1;
1012   CREATE TABLE t0(aa);
1013   CREATE TABLE t1(bb);
1014   INSERT INTO t0(aa) VALUES (1);
1015   INSERT INTO t1(bb) VALUES (1);
1016   SELECT 11, * FROM t1 LEFT JOIN t0 WHERE aa ISNULL;
1017   SELECT 12, * FROM t1 LEFT JOIN t0 WHERE +aa ISNULL;
1018   SELECT 13, * FROM t1 LEFT JOIN t0 ON aa ISNULL;
1019   SELECT 14, * FROM t1 LEFT JOIN t0 ON +aa ISNULL;
1020   CREATE INDEX i0 ON t0(aa) WHERE aa ISNULL;
1021   SELECT 21, * FROM t1 LEFT JOIN t0 WHERE aa ISNULL;
1022   SELECT 22, * FROM t1 LEFT JOIN t0 WHERE +aa ISNULL;
1023   SELECT 23, * FROM t1 LEFT JOIN t0 ON aa ISNULL;
1024   SELECT 24, * FROM t1 LEFT JOIN t0 ON +aa ISNULL;
1025 } {13 1 {} 14 1 {} 23 1 {} 24 1 {}}
1026
1027 # 2019-12-18 problem with a LEFT JOIN where the RHS is a view.
1028 # Detected by Yongheng and Rui.
1029 # Follows from the optimization attempt of check-in 41c27bc0ff1d3135
1030 # on 2017-04-18
1031 #
1032 reset_db
1033 do_execsql_test join-22.10 {
1034   CREATE TABLE t0(a, b);
1035   CREATE INDEX t0a ON t0(a);
1036   INSERT INTO t0 VALUES(10,10),(10,11),(10,12);
1037   SELECT DISTINCT c FROM t0 LEFT JOIN (SELECT a+1 AS c FROM t0) ORDER BY c ;
1038 } {11}
1039
1040 # 2019-12-22 ticket 7929c1efb2d67e98
1041 # Verification of testtag-20230227a
1042 #
1043 # 2023-02-27 https://sqlite.org/forum/forumpost/422e635f3beafbf6
1044 # Verification of testtag-20230227a, testtag-20230227b, and testtag-20230227c
1045 #
1046 reset_db
1047 ifcapable vtab {
1048   do_execsql_test join-23.10 {
1049     CREATE TABLE t0(c0);
1050     INSERT INTO t0(c0) VALUES(123);
1051     CREATE VIEW v0(c0) AS SELECT 0 GROUP BY 1;
1052     SELECT t0.c0, v0.c0, vt0.name
1053      FROM v0, t0 LEFT JOIN pragma_table_info('t0') AS vt0
1054        ON vt0.name LIKE 'c0'
1055      WHERE v0.c0 == 0;
1056   } {123 0 c0}
1057   do_execsql_test join-23.20 {
1058     CREATE TABLE a(value TEXT);
1059     INSERT INTO a(value) SELECT value FROM json_each('["a", "b", null]');
1060     CREATE TABLE b(value TEXT);
1061     INSERT INTO b(value) SELECT value FROM json_each('["a", "c", null]');
1062     SELECT a.value, b.value FROM a RIGHT JOIN b ON a.value = b.value;
1063   } {a a {} c {} {}}
1064   do_execsql_test join-23.21 {
1065     SELECT a.value, b.value FROM b LEFT JOIN a ON a.value = b.value;
1066   } {a a {} c {} {}}
1067   do_execsql_test join-23.22 {
1068     SELECT a.value, b.value
1069       FROM json_each('["a", "c", null]') AS b
1070            LEFT JOIN
1071            json_each('["a", "b", null]') AS a ON a.value = b.value;
1072   } {a a {} c {} {}}
1073   do_execsql_test join-23.23 {
1074     SELECT a.value, b.value
1075       FROM json_each('["a", "b", null]') AS a
1076            RIGHT JOIN
1077            json_each('["a", "c", null]') AS b ON a.value = b.value;
1078   } {a a {} c {} {}}
1079   do_execsql_test join-23.24 {
1080     SELECT a.value, b.value
1081       FROM json_each('["a", "b", null]') AS a
1082            RIGHT JOIN
1083            b ON a.value = b.value;
1084   } {a a {} c {} {}}
1085   do_execsql_test join-23.25 {
1086     SELECT a.value, b.value
1087       FROM a
1088            RIGHT JOIN
1089            json_each('["a", "c", null]') AS b ON a.value = b.value;
1090   } {a a {} c {} {}}
1091 }
1092
1093 #-------------------------------------------------------------------------
1094 reset_db
1095 do_execsql_test join-24.1 {
1096   CREATE TABLE t1(a PRIMARY KEY, x);
1097   CREATE TABLE t2(b INT);
1098   CREATE INDEX t1aa ON t1(a, a);
1099
1100   INSERT INTO t1 VALUES('abc', 'def');
1101   INSERT INTO t2 VALUES(1);
1102 }
1103
1104 do_execsql_test join-24.2 {
1105   SELECT * FROM t2 JOIN t1 WHERE a='abc' AND x='def';
1106 } {1 abc def}
1107 do_execsql_test join-24.3 {
1108   SELECT * FROM t2 JOIN t1 WHERE a='abc' AND x='abc';
1109 } {}
1110
1111 do_execsql_test join-24.2 {
1112   SELECT * FROM t2 LEFT JOIN t1 ON a=0 WHERE (x='x' OR x IS NULL);
1113 } {1 {} {}}
1114
1115 # 2020-09-30 ticket 66e4b0e271c47145
1116 # The query flattener inserts an "expr AND expr" expression as a substitution
1117 # for the column of a view where that view column is part of an ON expression
1118 # of a LEFT JOIN.
1119 #
1120 reset_db
1121 do_execsql_test join-25.1 {
1122   CREATE TABLE t0(c0 INT);
1123   CREATE VIEW v0 AS SELECT (NULL AND 5) as c0 FROM t0;
1124   INSERT INTO t0(c0) VALUES (NULL);
1125   SELECT count(*)  FROM v0 LEFT JOIN t0 ON v0.c0;
1126 } {1}
1127
1128 # 2022-04-21 Parser issue detected by dbsqlfuzz
1129 #
1130 reset_db
1131 do_catchsql_test join-26.1 {
1132   CREATE TABLE t4(a,b);
1133   CREATE TABLE t5(a,c);
1134   CREATE TABLE t6(a,d);
1135   SELECT * FROM t5 JOIN ((t4 JOIN (t5 JOIN t6)) t7);
1136 } {/1 {.*}/}
1137
1138 # 2022-06-09 Invalid subquery flattening caused by
1139 # check-in 3f45007d544e5f78 and detected by dbsqlfuzz
1140 #
1141 reset_db
1142 do_execsql_test join-27.1 {
1143   CREATE TABLE t1(a INT,b INT,c INT);  INSERT INTO t1 VALUES(NULL,NULL,NULL);
1144   CREATE TABLE t2(d INT,e INT);        INSERT INTO t2 VALUES(NULL,NULL);
1145   CREATE INDEX x2 ON t1(c,b);
1146   CREATE TABLE t3(x INT);              INSERT INTO t3 VALUES(NULL);
1147 }
1148 do_execsql_test join-27.2 {
1149   WITH t99(b) AS MATERIALIZED (
1150     SELECT b FROM t2 LEFT JOIN t1 ON c IN (SELECT x FROM t3)
1151   )
1152   SELECT 5 FROM t2 JOIN t99 ON b IN (1,2,3);
1153 } {}
1154 do_execsql_test join-27.3 {
1155   WITH t99(b) AS NOT MATERIALIZED (
1156     SELECT b FROM t2 LEFT JOIN t1 ON c IN (SELECT x FROM t3)
1157   )
1158   SELECT 5 FROM t2 JOIN t99 ON b IN (1,2,3);
1159 } {}
1160 do_execsql_test join-27.4 {
1161   WITH t99(b) AS (SELECT b FROM t2 LEFT JOIN t1 ON c IN (SELECT x FROM t3))
1162   SELECT 5 FROM t2 JOIN t99 ON b IN (1,2,3);
1163 } {}
1164 do_execsql_test join-27.5 {
1165   SELECT 5
1166     FROM t2 JOIN (
1167        SELECT b FROM t2 LEFT JOIN t1 ON c IN (SELECT x FROM t3)
1168     ) AS t99 ON b IN (1,2,3);
1169 } {}
1170
1171 db null NULL
1172 do_execsql_test join-27.6 {
1173   INSERT INTO t1 VALUES(3,4,NULL);
1174   INSERT INTO t2 VALUES(1,2);
1175   WITH t99(b) AS (
1176     SELECT coalesce(b,3) FROM t2 AS x LEFT JOIN t1 ON c IN (SELECT x FROM t3)
1177   )
1178   SELECT d, e, b FROM t2 JOIN t99 ON b IN (1,2,3) ORDER BY +d;
1179 } {NULL NULL 3 NULL NULL 3 1 2 3 1 2 3}
1180 do_execsql_test join-27.7 {
1181   SELECT d, e, b2
1182     FROM t2
1183          JOIN (SELECT coalesce(b,3) AS b2 FROM t2 AS x LEFT JOIN t1
1184                 ON c IN (SELECT x FROM t3)) AS t99
1185             ON b2 IN (1,2,3) ORDER BY +d;
1186 } {NULL NULL 3 NULL NULL 3 1 2 3 1 2 3}
1187
1188 do_execsql_test join-27.8 {
1189   DELETE FROM t1;
1190   DELETE FROM t2 WHERE d IS NOT NULL;
1191   DELETE FROM t3;
1192   SELECT * FROM t2 JOIN (SELECT b FROM t2 LEFT JOIN t1
1193                        ON c IN (SELECT x FROM t3)) AS t99 ON b IN (1,2,3);
1194 } {}
1195
1196 do_execsql_test join-27.9 {
1197   DELETE FROM t1;
1198   DELETE FROM t2;
1199   DELETE FROM t3;
1200   INSERT INTO t1 VALUES(4,3,5);
1201   INSERT INTO t2 VALUES(1,2);
1202   INSERT INTO t3 VALUES(5);
1203   SELECT * FROM t2 JOIN (SELECT b FROM t2 LEFT JOIN t1
1204                        ON c IN (SELECT x FROM t3)) AS t99 ON b IS NULL;
1205 } {}
1206 do_execsql_test join-27.10 {
1207   WITH t99(b) AS (
1208     SELECT b FROM t2 AS x LEFT JOIN t1 ON c IN (SELECT x FROM t3)
1209   )
1210   SELECT d, e, b FROM t2 JOIN t99 ON b IS NULL;
1211 } {}
1212
1213
1214 # 2022-09-19 https://sqlite.org/forum/forumpost/96b9e5709cf47cda
1215 # Performance regression relative to version 3.38.0 that resulted from
1216 # a new query flattener restriction that was added to fixes the join-27.*
1217 # tests above.  The restriction needed to be removed and the join-27.*
1218 # problem fixed another way.
1219 #
1220 reset_db
1221 do_execsql_test join-28.1 {
1222   CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c INT);
1223   CREATE TABLE t2(d INTEGER PRIMARY KEY, e INT);
1224   CREATE VIEW t3(a,b,c,d,e) AS SELECT * FROM t1 LEFT JOIN t2 ON d=c;
1225   CREATE TABLE t4(x INT, y INT);
1226   INSERT INTO t1 VALUES(1,2,3);
1227   INSERT INTO t2 VALUES(1,5);
1228   INSERT INTO t4 VALUES(1,4);
1229   SELECT a, b, y FROM t4 JOIN t3 ON a=x;
1230 } {1 2 4}
1231 do_eqp_test join-28.2 {
1232   SELECT a, b, y FROM t4 JOIN t3 ON a=x;
1233 } {
1234   QUERY PLAN
1235   |--SCAN t4
1236   `--SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?)
1237 }
1238 # ^^^^^^^ Without the fix (if the query flattening optimization does not
1239 # run) the query plan above would look like this:
1240 #
1241 #   QUERY PLAN
1242 #   |--MATERIALIZE t3
1243 #   |  |--SCAN t1
1244 #   |  `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
1245 #   |--SCAN t4
1246 #   `--SEARCH t3 USING AUTOMATIC COVERING INDEX (a=?)
1247
1248
1249 # 2023-05-01 https://sqlite.org/forum/forumpost/96cd4a7e9e
1250 #
1251 reset_db
1252 db null NULL
1253 do_execsql_test join-29.1 {
1254   CREATE TABLE t0(a INT); INSERT INTO t0(a) VALUES (1);
1255   CREATE TABLE t1(b INT); INSERT INTO t1(b) VALUES (2);
1256   CREATE VIEW v2(c) AS SELECT 3 FROM t1;
1257   SELECT * FROM t1 JOIN v2 ON 0     FULL OUTER JOIN t0 ON true;
1258 } {NULL NULL 1}
1259 do_execsql_test join-29.2 {
1260   SELECT * FROM t1 JOIN v2 ON 1=0   FULL OUTER JOIN t0 ON true;
1261 } {NULL NULL 1}
1262 do_execsql_test join-29.3 {
1263   SELECT * FROM t1 JOIN v2 ON false FULL OUTER JOIN t0 ON true;
1264 } {NULL NULL 1}
1265
1266 # 2023-05-11 https://sqlite.org/forum/forumpost/49f2c7f690
1267 # Verify that omit-noop-join optimization does not apply if the table
1268 # to be omitted has an inner-join constraint and there is a RIGHT JOIN
1269 # anywhere in the query.
1270 #
1271 reset_db
1272 db null NULL
1273 do_execsql_test join-30.1 {
1274   CREATE TABLE t0(z INT);         INSERT INTO t0 VALUES(1),(2);
1275   CREATE TABLE t1(a INT);         INSERT INTO t1 VALUES(1);
1276   CREATE TABLE t2(b INT);         INSERT INTO t2 VALUES(2);
1277   CREATE TABLE t3(c INT, d INT);  INSERT INTO t3 VALUES(3,4);
1278   CREATE TABLE t4(e INT);         INSERT INTO t4 VALUES(5);
1279   CREATE VIEW v5(x,y) AS SELECT c, d FROM t3 LEFT JOIN t4 ON false;
1280 }
1281 do_execsql_test join-30.2 {
1282   SELECT DISTINCT a, b
1283     FROM t1 RIGHT JOIN t2 ON a=b LEFT JOIN v5 ON false
1284    WHERE x <= y;
1285 } {}
1286 do_execsql_test join-30.3 {
1287   SELECT DISTINCT a, b
1288     FROM t0 JOIN t1 ON z=a RIGHT JOIN t2 ON a=b LEFT JOIN v5 ON false
1289    WHERE x <= y;
1290 } {}
1291
1292 finish_test