test/join8.test

   1 # 2022-04-12
   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 #
  12 # This file implements tests for RIGHT and FULL OUTER JOINs.
  13
  14 set testdir [file dirname $argv0]
  15 source $testdir/tester.tcl
  16
  17 ifcapable !vtab {
  18   finish_test
  19   return
  20 }
  21
  22 db null NULL
  23 # EVIDENCE-OF: R-33754-02880 you can say "LEFT RIGHT JOIN" which is the
  24 # same as "FULL JOIN".
  25 do_execsql_test join8-10 {
  26   CREATE TABLE t1(a,b,c);
  27   CREATE TABLE t2(x,y);
  28   CREATE INDEX t2x ON t2(x);
  29   SELECT avg(DISTINCT b) FROM (SELECT * FROM t2 LEFT RIGHT JOIN t1 ON c);
  30 } {NULL}
  31
  32 # Pending optimization opportunity:
  33 # Row-value initialization subroutines must be called from with the
  34 # RIGHT JOIN body subroutine before the first use of any register containing
  35 # the results of that subroutine.  This seems dodgy.  Test case:
  36 #
  37 reset_db
  38 do_execsql_test join8-1000 {
  39   CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT,b,c,d,e,f,g,h,j,k,l,m,n,o,p,q,r,s);
  40   CREATE INDEX t1x1 ON t1(g+h,j,k);
  41   CREATE INDEX t1x2 ON t1(b);
  42   INSERT INTO t1 DEFAULT VALUES;
  43 } {}
  44 do_catchsql_test join8-1010 {
  45   SELECT a
  46     FROM (
  47           SELECT a
  48             FROM (
  49                   SELECT a
  50                     FROM (
  51                           SELECT a FROM t1 NATURAL LEFT JOIN t1
  52                            WHERE (b, 2 ) IS ( SELECT 2 IN(2,2),2)
  53                          )
  54                     NATURAL LEFT FULL JOIN t1
  55                    WHERE ( rowid , 1 )<=(CASE 5 WHEN 619 THEN 841 ELSE 3374391096 END,0)
  56                    ORDER BY a ASC
  57                  )
  58             NATURAL LEFT JOIN t1
  59            WHERE (b, 2 ) IS ( SELECT 3 IN(3,3),3)
  60          )
  61     NATURAL LEFT FULL JOIN t1
  62    WHERE ( rowid , 1 )<=(CASE 5 WHEN 619 THEN 841 ELSE 3374391096 END,0)
  63    ORDER BY a ASC;
  64 } {0 1}
  65
  66 # Pending issue #2: (now resolved)
  67 # Jump to addrHalt inside the RIGHT JOIN body subroutine bypasses the
  68 # OP_Return, resulting in a subroutine loop.  Test case:
  69 #
  70 reset_db
  71 do_execsql_test join8-2000 {
  72   CREATE TABLE t1(a int, b int, c int);
  73   INSERT INTO t1 VALUES(1,2,3),(4,5,6);
  74   CREATE TABLE t2(d int, e int);
  75   INSERT INTO t2 VALUES(3,333),(4,444);
  76   CREATE TABLE t3(f int, g int);
  77   PRAGMA automatic_index=off;
  78 } {}
  79 do_catchsql_test join8-2010 {
  80   SELECT * FROM t1 RIGHT JOIN t2 ON c=d JOIN t3 ON f=e;
  81 } {0 {}}
  82
  83 # Demonstrate that nested FULL JOINs and USING clauses work
  84 #
  85 reset_db
  86 load_static_extension db series
  87 do_execsql_test join8-3000 {
  88   CREATE TABLE t1(id INTEGER PRIMARY KEY, a INT);
  89   CREATE TABLE t2(id INTEGER PRIMARY KEY, b INT);
  90   CREATE TABLE t3(id INTEGER PRIMARY KEY, c INT);
  91   CREATE TABLE t4(id INTEGER PRIMARY KEY, d INT);
  92   CREATE TABLE t5(id INTEGER PRIMARY KEY, e INT);
  93   CREATE TABLE t6(id INTEGER PRIMARY KEY, f INT);
  94   CREATE TABLE t7(id INTEGER PRIMARY KEY, g INT);
  95   CREATE TABLE t8(id INTEGER PRIMARY KEY, h INT);
  96   INSERT INTO t1 SELECT value, 1 FROM generate_series(1,256) WHERE value & 1;
  97   INSERT INTO t2 SELECT value, 1 FROM generate_series(1,256) WHERE value & 2;
  98   INSERT INTO t3 SELECT value, 1 FROM generate_series(1,256) WHERE value & 4;
  99   INSERT INTO t4 SELECT value, 1 FROM generate_series(1,256) WHERE value & 8;
 100   INSERT INTO t5 SELECT value, 1 FROM generate_series(1,256) WHERE value & 16;
 101   INSERT INTO t6 SELECT value, 1 FROM generate_series(1,256) WHERE value & 32;
 102   INSERT INTO t7 SELECT value, 1 FROM generate_series(1,256) WHERE value & 64;
 103   INSERT INTO t8 SELECT value, 1 FROM generate_series(1,256) WHERE value & 128;
 104   CREATE TABLE t9 AS
 105     SELECT id, h, g, f, e, d, c, b, a
 106       FROM t1
 107       NATURAL FULL JOIN t2
 108       NATURAL FULL JOIN t3
 109       NATURAL FULL JOIN t4
 110       NATURAL FULL JOIN t5
 111       NATURAL FULL JOIN t6
 112       NATURAL FULL JOIN t7
 113       NATURAL FULL JOIN t8;
 114 } {}
 115 do_execsql_test join8-3010 {
 116   SELECT count(*) FROM t9;
 117 } {255}
 118 do_execsql_test join8-3020 {
 119   SELECT id, count(*) FROM t9 GROUP BY id HAVING count(*)!=1;
 120 } {}
 121 do_execsql_test join8-3030 {
 122   UPDATE t9 SET a=0 WHERE a IS NULL;
 123   UPDATE t9 SET b=0 WHERE b IS NULL;
 124   UPDATE t9 SET c=0 WHERE c IS NULL;
 125   UPDATE t9 SET d=0 WHERE d IS NULL;
 126   UPDATE t9 SET e=0 WHERE e IS NULL;
 127   UPDATE t9 SET f=0 WHERE f IS NULL;
 128   UPDATE t9 SET g=0 WHERE g IS NULL;
 129   UPDATE t9 SET h=0 WHERE h IS NULL;
 130   SELECT count(*) FROM t9 WHERE id=128*h+64*g+32*f+16*e+8*d+4*c+2*b+a;
 131 } {255}
 132 do_execsql_test join8-3040 {
 133   SELECT * FROM t9 WHERE id<>128*h+64*g+32*f+16*e+8*d+4*c+2*b+a;
 134 } {}
 135
 136 # 2022-04-21 dbsqlfuzz find
 137 #
 138 reset_db
 139 do_execsql_test join8-4000 {
 140   CREATE TABLE t1(x INTEGER PRIMARY KEY, a, b);
 141   INSERT INTO t1 VALUES(1,5555,4);
 142   CREATE INDEX i1a ON t1(a);
 143   CREATE INDEX i1b ON t1(b);
 144   SELECT a FROM t1 NATURAL RIGHT JOIN t1 WHERE a=5555 OR (1,b)==(SELECT 2 IN (2,2),4);
 145 } {5555}
 146
 147 # 2022-04-23 dbsqlfuzz c7ee5500e3abddec3557016de777713b80c790d3
 148 # Escape from the right-join body subroutine via the ORDER BY LIMIT optimization.
 149 #
 150 reset_db
 151 db null -
 152 do_catchsql_test join8-5000 {
 153   CREATE TABLE t1(x);
 154   INSERT INTO t1(x) VALUES(NULL),(NULL);
 155   CREATE TABLE t2(c, d);
 156   INSERT INTO t2(c,d) SELECT x, x FROM t1;
 157   CREATE INDEX t2dc ON t2(d, c);
 158   SELECT (SELECT c FROM sqlite_temp_schema FULL JOIN t2 ON d IN (1,2,3) ORDER BY d) AS x FROM t1;
 159 } {0 {- -}}
 160
 161 # 2022-04-29 dbsqlfuzz 19f1102a70cf966ab249de56d944fc20dbebcfcf
 162 #
 163 reset_db
 164 do_execsql_test join8-6000 {
 165   CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d REAL);
 166   INSERT INTO t1 VALUES(1,'A','aa',2.5);
 167   SELECT * FROM t1 AS t2 NATURAL RIGHT JOIN t1 AS t3
 168    WHERE (a,b) IN (SELECT rowid, b FROM t1);
 169 } {1 A aa 2.5}
 170 do_execsql_test join8-6010 {
 171   DROP TABLE IF EXISTS t1;
 172   CREATE TABLE t1(a INT PRIMARY KEY, b TEXT, c TEXT, d INT) WITHOUT ROWID;
 173   INSERT INTO t1 VALUES(15,'xray','baker',42);
 174   SELECT value, t1.* FROM json_each('7') NATURAL RIGHT JOIN t1
 175    WHERE (a,b) IN (SELECT a, b FROM t1);
 176 } {7 15 xray baker 42}
 177 do_execsql_test join8-6020 {
 178   DROP TABLE IF EXISTS t1;
 179   CREATE TABLE t1(a INTEGER PRIMARY KEY,b);
 180   INSERT INTO t1 VALUES(0,NULL),(1,2);
 181   SELECT value, t1.* FROM json_each('17') NATURAL RIGHT JOIN t1
 182    WHERE (a,b) IN (SELECT rowid, b FROM t1);
 183 } {17 1 2}
 184
 185 # Bloom filter usage by RIGHT and FULL JOIN
 186 #
 187 reset_db
 188 do_execsql_test join8-7000 {
 189 CREATE TABLE t1(a INT, b INT, c INT, d INT);
 190   WITH RECURSIVE c(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM c WHERE x<10)
 191     INSERT INTO t1(a,b,c,d) SELECT x, x+100, x+200, x+300 FROM c;
 192   CREATE TABLE t2(b INT, x INT);
 193   INSERT INTO t2(b,x) SELECT b, a FROM t1 WHERE a%2=0;
 194   CREATE INDEX t2b ON t2(b);
 195   CREATE TABLE t3(c INT, y INT);
 196   INSERT INTO t3(c,y) SELECT c, a FROM t1 WHERE a%3=0;
 197   CREATE INDEX t3c ON t3(c);
 198   CREATE TABLE t4(d INT, z INT);
 199   INSERT INTO t4(d,z) SELECT d, a FROM t1 WHERE a%5=0;
 200   CREATE INDEX t4d ON t4(d);
 201   INSERT INTO t1(a,b,c,d) VALUES
 202     (96,NULL,296,396),
 203     (97,197,NULL,397),
 204     (98,198,298,NULL),
 205     (99,NULL,NULL,NULL);
 206   ANALYZE sqlite_schema;
 207   INSERT INTO sqlite_stat1 VALUES('t4','t4d','20 1');
 208   INSERT INTO sqlite_stat1 VALUES('t3','t3c','32 1');
 209   INSERT INTO sqlite_stat1 VALUES('t2','t2b','48 1');
 210   INSERT INTO sqlite_stat1 VALUES('t1',NULL,'100');
 211   ANALYZE sqlite_schema;
 212 } {}
 213 db null -
 214 do_execsql_test join8-7010 {
 215   WITH t0 AS MATERIALIZED (
 216     SELECT t1.*, t2.*, t3.*
 217       FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>0
 218         RIGHT JOIN t3 ON t1.c=t3.c AND t3.y>0
 219   )
 220   SELECT * FROM t0 FULL JOIN t4 ON t0.a=t4.d AND t4.z>0
 221    ORDER BY coalesce(t0.a, t0.y+200, t4.d);
 222 } {
 223   6  106  206  306  106  6  206  6    -   -
 224   -    -    -    -    -  -  200  0    -   -
 225   -    -    -    -    -  -  203  3    -   -
 226   -    -    -    -    -  -  209  9    -   -
 227   -    -    -    -    -  -    -  -  300   0
 228   -    -    -    -    -  -    -  -  305   5
 229   -    -    -    -    -  -    -  -  310  10
 230 }
 231
 232 # EVIDENCE-OF: R-33754-02880 you can say "LEFT RIGHT JOIN" which is the
 233 # same as "FULL JOIN".
 234 do_execsql_test join8-7011 {
 235   WITH t0 AS MATERIALIZED (
 236     SELECT t1.*, t2.*, t3.*
 237       FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>0
 238         RIGHT JOIN t3 ON t1.c=t3.c AND t3.y>0
 239   )
 240   SELECT * FROM t0 LEFT RIGHT JOIN t4 ON t0.a=t4.d AND t4.z>0
 241    ORDER BY coalesce(t0.a, t0.y+200, t4.d);
 242 } {
 243   6  106  206  306  106  6  206  6    -   -
 244   -    -    -    -    -  -  200  0    -   -
 245   -    -    -    -    -  -  203  3    -   -
 246   -    -    -    -    -  -  209  9    -   -
 247   -    -    -    -    -  -    -  -  300   0
 248   -    -    -    -    -  -    -  -  305   5
 249   -    -    -    -    -  -    -  -  310  10
 250 }
 251
 252 do_execsql_test join8-7020 {
 253   EXPLAIN QUERY PLAN
 254   WITH t0 AS MATERIALIZED (
 255     SELECT t1.*, t2.*, t3.*
 256       FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>0
 257         RIGHT JOIN t3 ON t1.c=t3.c AND t3.y>0
 258   )
 259   SELECT * FROM t0 FULL JOIN t4 ON t0.a=t4.d AND t4.z>0
 260    ORDER BY coalesce(t0.a, t0.y+200, t4.d);
 261 } {/.*BLOOM FILTER ON t2.*BLOOM FILTER ON t3.*BLOOM FILTER ON t4.*/}
 262
 263 # 2022-05-12 Difference with PG found (by Dan) while exploring
 264 # https://sqlite.org/forum/forumpost/677a0ab93fcd9ccd
 265 #
 266 reset_db
 267 do_execsql_test join8-8000 {
 268   CREATE TABLE t1(a INT, b INT);
 269   CREATE TABLE t2(c INT, d INT);
 270   CREATE TABLE t3(e INT, f INT);
 271   INSERT INTO t1 VALUES(1, 2);
 272   INSERT INTO t2 VALUES(3, 4);
 273   INSERT INTO t3 VALUES(5, 6);
 274 } {}
 275 do_execsql_test join8-8010 {
 276   SELECT *
 277     FROM t3 LEFT JOIN t2 ON true
 278             JOIN t1 ON (t3.e IS t2.c);
 279 } {}
 280 do_execsql_test join8-8020 {
 281   SELECT *
 282     FROM t3 LEFT JOIN t2 ON true
 283             JOIN t1 ON (t3.e IS NOT DISTINCT FROM t2.c);
 284 } {}
 285
 286 # 2022-05-13 The idea of reusing subquery cursors does not
 287 # work, if the cursors are used both for scanning and lookups.
 288 #
 289 reset_db
 290 db null -
 291 do_execsql_test join8-9000 {
 292   CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d REAL);
 293   INSERT INTO t1 VALUES(1,'E','bb',NULL),(2,NULL,NULL,NULL);
 294   SELECT * FROM t1 NATURAL RIGHT JOIN t1 AS t2 WHERE (a,b) IN (SELECT a+0, b FROM t1);
 295 } {1 E bb -}
 296
 297 # 2022-05-14 https://sqlite.org/forum/forumpost/c06b10ad7e
 298 #
 299 reset_db
 300 db null -
 301 do_execsql_test join8-10000 {
 302   CREATE TABLE t1(c0 INT UNIQUE);
 303   CREATE TABLE t2(c0);
 304   CREATE TABLE t2i(c0 INT);
 305   CREATE TABLE t3(c0 INT);
 306   INSERT INTO t1 VALUES(1);
 307   INSERT INTO t2 VALUES(2);
 308   INSERT INTO t2i VALUES(2);
 309   INSERT INTO t3 VALUES(3);
 310 } {}
 311 do_execsql_test join8-10010 {
 312   SELECT DISTINCT t1.c0, t3.c0
 313     FROM t2 NATURAL JOIN t1 RIGHT JOIN t3 ON t1.c0;
 314 } {- 3}
 315 do_execsql_test join8-10020 {
 316   SELECT t1.c0, t3.c0
 317     FROM t2 NATURAL JOIN t1 RIGHT JOIN t3 ON t1.c0;
 318 } {- 3}
 319 do_execsql_test join8-10030 {
 320   SELECT DISTINCT t1.c0, t3.c0
 321     FROM t2 NATURAL CROSS JOIN t1 RIGHT JOIN t3 ON t1.c0;
 322 } {- 3}
 323 do_execsql_test join8-10040 {
 324   SELECT t1.c0, t3.c0
 325     FROM t1 NATURAL CROSS JOIN t2 RIGHT JOIN t3 ON t1.c0;
 326 } {- 3}
 327 do_execsql_test join8-10050 {
 328   SELECT DISTINCT t1.c0, t3.c0
 329     FROM t2i NATURAL JOIN t1 RIGHT JOIN t3 ON t1.c0;
 330 } {- 3}
 331 do_execsql_test join8-10060 {
 332   SELECT DISTINCT +t1.c0, t3.c0
 333     FROM t2 NATURAL JOIN t1 RIGHT JOIN t3 ON t1.c0;
 334 } {- 3}
 335 do_execsql_test join8-10070 {
 336   SELECT DISTINCT +t1.c0, t3.c0
 337     FROM t1 NATURAL CROSS JOIN t2 RIGHT JOIN t3 ON t1.c0;
 338 } {- 3}
 339 do_execsql_test join8-10080 {
 340   SELECT DISTINCT t1.c0, t3.c0
 341     FROM t2 NATURAL JOIN t1 RIGHT JOIN t3 ON t1.c0<>0;
 342 } {- 3}
 343
 344 # 2022-05-14
 345 # index-on-expr scan on a RIGHT JOIN
 346 # dbsqlfuzz 39ee60004ff027a9e2846cf76e02cd5ac0953739
 347 #
 348 reset_db
 349 db null -
 350 do_execsql_test join8-11000 {
 351   CREATE TABLE t1(a);
 352   CREATE TABLE t2(b);
 353   INSERT INTO t2 VALUES(0),(1),(2);
 354   SELECT * FROM t1 RIGHT JOIN t2 ON (a=b) WHERE 99+(b+1)!=99;
 355 } {- 0 - 1 - 2}
 356 do_execsql_test join8-11010 {
 357   CREATE INDEX t2b ON t2(b+1) WHERE b IS NOT NULL;
 358   SELECT * FROM t1 RIGHT JOIN t2 ON (a=b) WHERE 99+(b+1)!=99;
 359 } {- 0 - 1 - 2}
 360 do_execsql_test join8-11020 {
 361   DROP TABLE t1;
 362   DROP TABLE t2;
 363   CREATE TABLE t1(a);
 364   CREATE TABLE t2(b, c, d);
 365   INSERT INTO t2 VALUES(1, 3, 'not-4');
 366   SELECT b, d FROM t1 RIGHT JOIN t2 WHERE (b+0)=1 AND d!=4;
 367 } {1 not-4}
 368 do_execsql_test join8-11030 {
 369   CREATE INDEX i2 ON t2((b+0), d);
 370   SELECT b, d FROM t1 RIGHT JOIN t2 WHERE (b+0)=1 AND d!=4;
 371 } {1 not-4}
 372 do_execsql_test join8-11040 {
 373   DROP INDEX i2;
 374   CREATE INDEX i2 ON t2((b+0), d) WHERE d IS NOT NULL;
 375   SELECT b, d FROM t1 RIGHT JOIN t2 WHERE (b+0)=1 AND d!=4;
 376 } {1 not-4}
 377
 378 # 2022-05-23
 379 # NATURAL JOIN name resolution is more forgiving with LEFT JOIN
 380 # https://sqlite.org/forum/forumpost/e90a8e6e6f
 381 #
 382 reset_db
 383 db null -
 384 do_execsql_test join8-12000 {
 385   CREATE TABLE t1(a INT);  INSERT INTO t1 VALUES(0),(1);
 386   CREATE TABLE t2(a INT);  INSERT INTO t2 VALUES(0),(2);
 387   CREATE TABLE t3(a INT);  INSERT INTO t3 VALUES(0),(3);
 388 } {}
 389 do_catchsql_test join8-12010 {
 390   SELECT * FROM t1 RIGHT JOIN t2 ON t2.a<>0 NATURAL RIGHT JOIN t3;
 391 } {1 {ambiguous reference to a in USING()}}
 392 do_catchsql_test join8-12020 {
 393   SELECT * FROM t1 RIGHT JOIN t2 ON t2.a<>0 NATURAL LEFT JOIN t3;
 394 } {1 {ambiguous reference to a in USING()}}
 395 do_catchsql_test join8-12030 {
 396   SELECT * FROM t1 LEFT JOIN t2 ON t2.a<>0 NATURAL RIGHT JOIN t3;
 397 } {1 {ambiguous reference to a in USING()}}
 398
 399 # The following query should probably also return the same error as the
 400 # previous three cases.  However, historical versions of SQLite have always
 401 # let it pass.  We will not "fix" this, since to do so might break legacy
 402 # applications.
 403 #
 404 do_catchsql_test join8-12040 {
 405   SELECT * FROM t1 LEFT JOIN t2 ON t2.a<>0 NATURAL LEFT JOIN t3;
 406 } {0 {0 2 1 2}}
 407
 408 # 2022-05-24
 409 # https://sqlite.org/forum/forumpost/687b0bf563a1d4f1
 410 #
 411 reset_db
 412 do_execsql_test join8-13000 {
 413   CREATE TABLE t0(t TEXT, u TEXT);  INSERT INTO t0 VALUES('t', 'u');
 414   CREATE TABLE t1(v TEXT, w TEXT);  INSERT INTO t1 VALUES('v', 'w');
 415   CREATE TABLE t2(x TEXT, y TEXT);  INSERT INTO t2 VALUES('x', 'y');
 416   SELECT * FROM t0 JOIN t1 ON (t2.x NOTNULL) LEFT JOIN t2 ON false;
 417   SELECT * FROM t0 JOIN t1 ON (t2.x NOTNULL) LEFT JOIN t2 ON false
 418    WHERE t2.y ISNULL;
 419 } {}
 420
 421 # 2022-05-25
 422 # https://sqlite.org/forum/forumpost/5cfe08eed6
 423 #
 424 reset_db
 425 do_execsql_test join8-14000 {
 426   CREATE TABLE t0(a TEXT, b TEXT, c TEXT);
 427   CREATE TABLE t1(a TEXT);
 428   INSERT INTO t1 VALUES('1');
 429   CREATE VIEW v0 AS SELECT 'xyz' AS d;
 430   SELECT * FROM v0 RIGHT JOIN t1 ON t1.a<>'' INNER JOIN t0 ON t0.c<>'';
 431   SELECT * FROM v0 RIGHT JOIN t1 ON t1.a<>'' INNER JOIN t0 ON t0.c<>'' WHERE b ISNULL;
 432 } {}
 433 do_execsql_test join8-14010 {
 434   CREATE TABLE y0(a INT);
 435   CREATE TABLE y1(b INT); INSERT INTO y1 VALUES(1), (2);
 436   CREATE TABLE y2(c INT); INSERT INTO y2 VALUES(3), (4);
 437 } {}
 438 db null -
 439 do_execsql_test join8-14020 {
 440   SELECT * FROM y0 RIGHT JOIN y1 ON true INNER JOIN y2 ON true WHERE y2.c!=99 AND y2.c!=98;
 441 } {
 442   - 1 3
 443   - 1 4
 444   - 2 3
 445   - 2 4
 446 }
 447
 448 # 2022-05-30
 449 # https://sqlite.org/forum/forumpost/3902c7b833
 450 #
 451 reset_db
 452 do_execsql_test join8-15000 {
 453   CREATE TABLE t1(x INT);
 454   CREATE TABLE t2(y INT);
 455   CREATE TABLE t3(z INT);
 456   INSERT INTO t1 VALUES(10);
 457   INSERT INTO t3 VALUES(20),(30);
 458 }
 459 do_execsql_test join8-15010 {
 460   SELECT * FROM t1 LEFT JOIN t2 ON true JOIN t3 ON t2.y IS NOT NULL;
 461 } {}
 462 do_execsql_test join8-15020 {
 463   SELECT * FROM t1 LEFT JOIN t2 ON true JOIN t3 ON t2.y IS NOT NULL
 464    WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600);
 465 } {}
 466 do_execsql_test join8-15100 {
 467   PRAGMA automatic_index = 0;
 468   CREATE TABLE t4(x TEXT);
 469   CREATE TABLE t5(y TEXT);
 470   CREATE TABLE t6(z TEXT);
 471   INSERT INTO t4 VALUES('a'), ('b');
 472   INSERT INTO t5 VALUES('b'), ('c');
 473   INSERT INTO t6 VALUES('a'), ('d');
 474 } {}
 475 db null -
 476 do_execsql_test join8-15110 {
 477   SELECT * FROM t4 LEFT JOIN t5 ON x=y LEFT JOIN t6 ON (x=z) ORDER BY +x;
 478 } {a - a b b -}
 479 do_execsql_test join8-15120 {
 480   SELECT * FROM t4 LEFT JOIN t5 ON x=y LEFT JOIN t6 ON (x=z)
 481    WHERE t5.y!='x' AND t4.x!='x';
 482 } {b b -}
 483
 484 # 2022-05-31
 485 # https://sqlite.org/forum/forumpost/c2554d560b
 486 reset_db
 487 do_execsql_test join8-16000 {
 488   CREATE TABLE t1(a TEXT);
 489   CREATE TABLE t2(b TEXT);
 490   CREATE TABLE t3(c TEXT);
 491   INSERT INTO t2(b) VALUES ('x');
 492   INSERT INTO t3(c) VALUES ('y'), ('z');
 493 } {}
 494 db null -
 495 do_execsql_test join8-16010 {
 496   SELECT * FROM t1 RIGHT JOIN t2 ON true LEFT JOIN t3 ON a<>'';
 497 } {- x -}
 498 do_execsql_test join8-16020 {
 499   SELECT * FROM t1 RIGHT JOIN t2 ON true LEFT JOIN t3 ON a<>'' WHERE c IS NULL;
 500 } {- x -}
 501 do_execsql_test join8-16020 {
 502   SELECT * FROM t1 RIGHT JOIN t2 ON true JOIN t3 ON a<>'' WHERE c IS NULL;
 503 } {}
 504 do_execsql_test join8-16030 {
 505   SELECT * FROM t1 RIGHT JOIN t2 ON true JOIN t3 ON a<>'';
 506 } {}
 507 do_execsql_test join8-16040 {
 508   SELECT * FROM t1 RIGHT JOIN t2 ON true LEFT JOIN t3 ON a<>'' WHERE c<>'';
 509 } {}
 510 do_execsql_test join8-16050 {
 511   SELECT * FROM t1 RIGHT JOIN t2 ON true LEFT JOIN t3 ON a<>'' WHERE c IS NOT NULL;
 512 } {}
 513 do_execsql_test join8-16060 {
 514   SELECT * FROM t1 RIGHT JOIN t2 ON true JOIN t3 ON a<>'' WHERE c<>'';
 515 } {}
 516 do_execsql_test join8-16070 {
 517   SELECT * FROM t1 RIGHT JOIN t2 ON true JOIN t3 ON a<>'' WHERE c IS NOT NULL;
 518 } {}
 519
 520 # 2022-06-01
 521 # https://sqlite.org/forum/forumpost/087de2d9ec
 522 #
 523 reset_db
 524 do_execsql_test join8-17000 {
 525   CREATE TABLE t1(id INTEGER PRIMARY KEY, x INT, y INT);
 526   CREATE TABLE t2(z INT);
 527   INSERT INTO t1(id,x,y) VALUES(1, 0, 0);
 528 } {}
 529 db null NULL
 530 do_execsql_test join8-17010 {
 531   SELECT * FROM t2 RIGHT JOIN t1 ON true;
 532 } {NULL 1 0 0}
 533 do_execsql_test join8-17020 {
 534   SELECT 99=id AND 0=y AS "truth" FROM t2 RIGHT JOIN t1 ON true;
 535 } {0}
 536 do_execsql_test join8-17030 {
 537   SELECT (99, 0)==(id, y) AS "truth" FROM t2 RIGHT JOIN t1;
 538 } {0}
 539 do_execsql_test join8-17040 {
 540   SELECT * FROM t2 RIGHT JOIN t1 WHERE 99=id AND 0=y;
 541 } {}
 542 do_execsql_test join8-17041 {
 543   SELECT * FROM t2 RIGHT JOIN t1 WHERE 99=+id AND 0=y;
 544 } {}
 545 do_execsql_test join8-17050 {
 546   SELECT * FROM t2 RIGHT JOIN t1 WHERE (99, 0)==(id,y);
 547 } {}
 548 do_execsql_test join8-17051 {
 549   SELECT * FROM t2 RIGHT JOIN t1 WHERE (99, 0)==(+id,y);
 550 } {}
 551 do_execsql_test join8-17060 {
 552   SELECT * FROM t2 RIGHT JOIN t1 WHERE 1=id AND 0=y;
 553 } {NULL 1 0 0}
 554 do_execsql_test join8-17061 {
 555   SELECT * FROM t2 RIGHT JOIN t1 WHERE 1=+id AND 0=y;
 556 } {NULL 1 0 0}
 557 do_execsql_test join8-17070 {
 558   SELECT * FROM t2 RIGHT JOIN t1 WHERE (1, 0)==(id,y);
 559 } {NULL 1 0 0}
 560 do_execsql_test join8-17071 {
 561   SELECT * FROM t2 RIGHT JOIN t1 WHERE (1, 0)==(+id,y);
 562 } {NULL 1 0 0}
 563 do_execsql_test join8-17080 {
 564   CREATE TABLE t3(a INTEGER PRIMARY KEY, b INT);
 565   CREATE TABLE t4(x INT, y INT);
 566   INSERT INTO t3(a,b) VALUES(1, 3);
 567 } {}
 568 do_execsql_test join8-17090 {
 569   SELECT t3.a FROM t4 RIGHT JOIN t3 ON (x=a) WHERE (b, 4)=(SELECT 3, 4);
 570 } {1}
 571 do_execsql_test join8-17091 {
 572   SELECT t3.a FROM t4 RIGHT JOIN t3 ON (x=a) WHERE (b, 4) IS (SELECT 3, 4);
 573 } {1}
 574
 575 # 2022-06-06
 576 # https://sqlite.org/forum/forumpost/206d99a16dd9212f
 577 # tag-20191211-001
 578 #
 579 reset_db
 580 do_execsql_test join8-18000 {
 581   CREATE TABLE t1(a BOOLEAN); INSERT INTO t1 VALUES (false);
 582   CREATE TABLE t2(x INT);     INSERT INTO t2 VALUES (0);
 583   SELECT *, x NOTNULL, (x NOTNULL)=a FROM t2 RIGHT JOIN t1 ON true WHERE (x NOTNULL)=a;
 584 } {}
 585 do_execsql_test join8-18010 {
 586   CREATE INDEX t1a ON t1(a);
 587   SELECT *, x NOTNULL, (x NOTNULL)=a FROM t2 RIGHT JOIN t1 ON true WHERE (x NOTNULL)=a;
 588 } {}
 589
 590 do_execsql_test join8-18020 {
 591   CREATE TABLE t3(z);
 592   INSERT INTO t3 VALUES('t3value');
 593   SELECT *, x NOTNULL, (x NOTNULL)=a FROM t2 RIGHT JOIN t1 ON true INNER JOIN t3 ON (x NOTNULL)=a;
 594 } {}
 595
 596 ifcapable rtree {
 597   do_execsql_test join8-18030 {
 598     CREATE VIRTUAL TABLE rtree1 USING rtree(a, x1, x2);
 599     INSERT INTO rtree1 VALUES(0, 0, 0);
 600   }
 601   do_execsql_test join8-18040 {
 602     SELECT *, x NOTNULL, (x NOTNULL)=a FROM t2
 603       RIGHT JOIN rtree1 ON true INNER JOIN t3 ON (x NOTNULL)=+a;
 604   } {}
 605   do_execsql_test join8-18050 {
 606     SELECT *, x NOTNULL, (x NOTNULL)=a FROM t2
 607       RIGHT JOIN rtree1 ON true INNER JOIN t3 ON (x NOTNULL)=a;
 608   } {}
 609 }
 610
 611
 612 reset_db
 613 do_execsql_test join8-19000 {
 614   CREATE TABLE t1(a INT);
 615   CREATE TABLE t2(b INT, c INT);
 616   CREATE TABLE t3(d INT);
 617
 618   INSERT INTO t1 VALUES(10);
 619   INSERT INTO t2 VALUES(50,51);
 620   INSERT INTO t3 VALUES(299);
 621
 622   CREATE INDEX t2b ON t2( (b IS NOT NULL) );
 623 }
 624
 625 do_execsql_test join8-19010 {
 626   SELECT * FROM t1 LEFT JOIN t2 ON true INNER JOIN t3 ON (b IS NOT NULL)=0;
 627 }
 628
 629 # 2022-06-07
 630 # https://sqlite.org/forum/forumpost/323f86cc30
 631 reset_db
 632 do_execsql_test join8-20000 {
 633   CREATE TABLE t1(x TEXT);
 634   INSERT INTO t1(x) VALUES('aaa');
 635   CREATE VIEW v0(y) AS SELECT x FROM t1;
 636   CREATE TABLE t2(z TEXT);
 637 } {}
 638 db null -
 639 do_execsql_test join8-20010 {
 640   SELECT * FROM t2 JOIN v0 ON z<>'bbb' RIGHT JOIN t1 ON z<>'ccc';
 641 } {- - aaa}
 642 do_execsql_test join8-20020 {
 643   SELECT * FROM t2 JOIN v0 ON z<>'bbb' RIGHT JOIN t1 ON z<>'ccc' ORDER BY z;
 644 } {- - aaa}
 645 do_execsql_test join8-20030 {
 646   SELECT 99 as "m" FROM t2 JOIN v0 ON z<>'bbb' RIGHT JOIN t1 ON z<>'ccc';
 647 } {99}
 648 do_execsql_test join8-20040 {
 649   SELECT 99 as "m" FROM t2 JOIN v0 ON z<>'bbb' RIGHT JOIN t1 ON z<>'ccc' ORDER BY z;
 650 } {99}
 651 do_execsql_test join8-20050 {
 652   SELECT count(*)
 653     FROM (SELECT 99 as "m" FROM t2 JOIN v0 ON z<>'' RIGHT JOIN t1 ON z<>'') AS "t3";
 654 } {1}
 655 do_execsql_test join8-20060 {
 656   SELECT count(*)
 657     FROM (SELECT 99 as "m" FROM t2 JOIN v0 ON z<>'' RIGHT JOIN t1 ON z<>'' ORDER BY z) AS "t3";
 658 } {1}
 659
 660 # 2022-06-10
 661 # https://sqlite.org/forum/forumpost/8e4c352937e82929
 662 #
 663 # Do not allow constant propagation between ON and WHERE clause terms.
 664 # (Updated 2022-06-20) See also https://sqlite.org/forum/forumpost/57bdf2217d
 665 #
 666 reset_db
 667 do_execsql_test join8-21000 {
 668   CREATE TABLE t1(a INT,b BOOLEAN);
 669   CREATE TABLE t2(c INT);  INSERT INTO t2 VALUES(NULL);
 670   CREATE TABLE t3(d INT);
 671 }
 672 do_execsql_test join8-21010 {
 673   SELECT (b IS TRUE) FROM t1 JOIN t3 ON (b=TRUE) RIGHT JOIN t2 ON TRUE;
 674 } {0}
 675 do_execsql_test join8-22020 {
 676   SELECT * FROM t1 JOIN t3 ON (b=TRUE) RIGHT JOIN t2 ON TRUE WHERE (b IS TRUE);
 677 } {}
 678 do_execsql_test join8-22030 {
 679   DROP TABLE t1;
 680   DROP TABLE t2;
 681   DROP TABLE t3;
 682   CREATE TABLE t1(a INT);
 683   CREATE TABLE t2(b INT);
 684   CREATE TABLE t3(c INTEGER PRIMARY KEY, d INT);
 685   CREATE INDEX t3d ON t3(d);
 686   INSERT INTO t3 VALUES(0, 0);
 687 }
 688 do_catchsql_test join8-22031 {
 689   SELECT * FROM t1 JOIN t2 ON d>b RIGHT JOIN t3 ON true WHERE +d = 0;
 690 } {1 {ON clause references tables to its right}}
 691 do_catchsql_test join8-22040 {
 692   SELECT * FROM t1 JOIN t2 ON d>b RIGHT JOIN t3 ON true WHERE d = 0;
 693 } {1 {ON clause references tables to its right}}
 694
 695
 696 # 2022-06-10
 697 # https://sqlite.org/forum/forumpost/51e6959f61
 698 #
 699 # Restrictions on the usage of WHERE clause constraints by joins that are
 700 # involved with a RIGHT JOIN must also be applied to automatic indexes.
 701 #
 702 reset_db
 703 do_execsql_test join8-22000 {
 704   CREATE TABLE t1(a INT);
 705   CREATE TABLE t2(b INT);
 706   CREATE TABLE t3(c TEXT);  INSERT INTO t3 VALUES('x');
 707   CREATE TABLE t4(d TEXT);  INSERT INTO t4 VALUES('y');
 708   SELECT 99
 709     FROM t1
 710          LEFT JOIN t2 ON true
 711          RIGHT JOIN t3 ON true
 712          RIGHT JOIN t4 ON true
 713    WHERE a=b;
 714 } {}
 715
 716 # 2022-06-13
 717 # https://sqlite.org/forum/forumpost/b40696f501
 718 #
 719 # This optimization that converts "x ISNULL" into "FALSE" when column "x" has a
 720 # NOT NULL constraint is too aggresive if the query contains RIGHT JOIN.
 721 #
 722 reset_db
 723 db null -
 724 do_execsql_test join8-23000 {
 725   CREATE TABLE t1(a TEXT);
 726   INSERT INTO t1 VALUES('c');
 727   CREATE TABLE t2(b TEXT, c TEXT NOT NULL);
 728   INSERT INTO t2 VALUES('a', 'b');
 729   CREATE TABLE t3(d TEXT);
 730   INSERT INTO t3 VALUES('x');
 731   CREATE TABLE t4(e TEXT);
 732   INSERT INTO t4 VALUES('y');
 733 }
 734 do_execsql_test join8-23010 {
 735   SELECT *
 736     FROM t1
 737          LEFT JOIN t2 ON TRUE
 738          JOIN t3 ON c=''
 739          RIGHT JOIN t4 ON b='';
 740 } {- - - - y}
 741 do_execsql_test join8-23020 {
 742   SELECT *
 743     FROM t1
 744          LEFT JOIN t2 ON TRUE
 745          JOIN t3 ON c=''
 746          RIGHT JOIN t4 ON b=''
 747    WHERE d ISNULL
 748 } {- - - - y}
 749
 750 # 2022-06-14
 751 # dbsqlfuzz 2f3101834d14325a976f601b9267a0fd323d6bbd
 752 #
 753 # When the OP_NullRow opcode creates a new cursor, it must
 754 # set the cursor to no-reuse so that an OP_OpenEphemeral in
 755 # a subroutine does not try to reuse it.
 756 #
 757 reset_db
 758 db null -
 759 do_execsql_test join8-24000 {
 760   CREATE TABLE t4(b INT, c INT);
 761   CREATE TABLE t5(a INT, f INT);
 762   INSERT INTO t5 VALUES(1,2);
 763   WITH t7(x, y) AS (SELECT 100, 200 FROM t5)
 764     SELECT * FROM t4 JOIN t7 ON true RIGHT JOIN (SELECT y AS z FROM t7) AS t6 ON (x=z);
 765 } {- - - - 200}
 766
 767 # 2022-06-20
 768 # forum/forumpost/6650cd40b5634f35
 769 #
 770 reset_db
 771 do_execsql_test join8-25000 {
 772   CREATE TABLE t1(a1 INT);
 773   CREATE TABLE t2(b2 INT);
 774   CREATE TABLE t3(c3 INT, d3 INT UNIQUE);
 775   CREATE TABLE t4(e4 INT, f4 TEXT);
 776   INSERT INTO t3(c3, d3) VALUES (2, 1);
 777   INSERT INTO t4(f4) VALUES ('x');
 778   CREATE INDEX i0 ON t3(c3) WHERE d3 ISNULL;
 779   ANALYZE main;
 780 }
 781 db null -
 782 do_execsql_test join8-25010 {
 783   SELECT * FROM t1 LEFT JOIN t2 ON true JOIN t3 ON (b2 IN (a1)) FULL JOIN t4 ON true;
 784 } {- - - - - x}
 785 do_execsql_test join8-25020 {
 786   SELECT 1 FROM t1 LEFT JOIN t2 ON true JOIN t3 ON (b2 IN (a1)) FULL JOIN t4 ON true;
 787 } {1}
 788
 789 # 2022-07-13
 790 # forum/forumpost/174afeae57
 791 #
 792 reset_db
 793 db null -
 794 do_execsql_test join8-26000 {
 795   CREATE TABLE t1(a INT);
 796   CREATE TABLE t2(b INT, c INT);
 797   CREATE VIEW t3(d) AS SELECT NULL FROM t2 FULL OUTER JOIN t1 ON c=a UNION ALL SELECT b FROM t2;
 798   INSERT INTO t1(a) VALUES (NULL);
 799   INSERT INTO t2(b, c) VALUES (99, NULL);
 800   SELECT DISTINCT b, c, d FROM t2, t3 WHERE b<>0
 801    UNION SELECT DISTINCT b, c, d FROM t2, t3 WHERE b ISNULL;
 802 } {99 - - 99 - 99}
 803
 804 finish_test