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