000001 # 2022-04-12
000002 #
000003 # The author disclaims copyright to this source code. In place of
000004 # a legal notice, here is a blessing:
000005 #
000006 # May you do good and not evil.
000007 # May you find forgiveness for yourself and forgive others.
000008 # May you share freely, never taking more than you give.
000009 #
000010 #***********************************************************************
000011 #
000012 # This file implements tests for RIGHT and FULL OUTER JOINs.
000013
000014 set testdir [file dirname $argv0]
000015 source $testdir/tester.tcl
000016
000017 ifcapable !vtab {
000018 finish_test
000019 return
000020 }
000021
000022 db null NULL
000023 # EVIDENCE-OF: R-33754-02880 you can say "LEFT RIGHT JOIN" which is the
000024 # same as "FULL JOIN".
000025 do_execsql_test join8-10 {
000026 CREATE TABLE t1(a,b,c);
000027 CREATE TABLE t2(x,y);
000028 CREATE INDEX t2x ON t2(x);
000029 SELECT avg(DISTINCT b) FROM (SELECT * FROM t2 LEFT RIGHT JOIN t1 ON c);
000030 } {NULL}
000031
000032 # Pending optimization opportunity:
000033 # Row-value initialization subroutines must be called from with the
000034 # RIGHT JOIN body subroutine before the first use of any register containing
000035 # the results of that subroutine. This seems dodgy. Test case:
000036 #
000037 reset_db
000038 do_execsql_test join8-1000 {
000039 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);
000040 CREATE INDEX t1x1 ON t1(g+h,j,k);
000041 CREATE INDEX t1x2 ON t1(b);
000042 INSERT INTO t1 DEFAULT VALUES;
000043 } {}
000044 do_catchsql_test join8-1010 {
000045 SELECT a
000046 FROM (
000047 SELECT a
000048 FROM (
000049 SELECT a
000050 FROM (
000051 SELECT a FROM t1 NATURAL LEFT JOIN t1
000052 WHERE (b, 2 ) IS ( SELECT 2 IN(2,2),2)
000053 )
000054 NATURAL LEFT FULL JOIN t1
000055 WHERE ( rowid , 1 )<=(CASE 5 WHEN 619 THEN 841 ELSE 3374391096 END,0)
000056 ORDER BY a ASC
000057 )
000058 NATURAL LEFT JOIN t1
000059 WHERE (b, 2 ) IS ( SELECT 3 IN(3,3),3)
000060 )
000061 NATURAL LEFT FULL JOIN t1
000062 WHERE ( rowid , 1 )<=(CASE 5 WHEN 619 THEN 841 ELSE 3374391096 END,0)
000063 ORDER BY a ASC;
000064 } {0 1}
000065
000066 # Pending issue #2: (now resolved)
000067 # Jump to addrHalt inside the RIGHT JOIN body subroutine bypasses the
000068 # OP_Return, resulting in a subroutine loop. Test case:
000069 #
000070 reset_db
000071 do_execsql_test join8-2000 {
000072 CREATE TABLE t1(a int, b int, c int);
000073 INSERT INTO t1 VALUES(1,2,3),(4,5,6);
000074 CREATE TABLE t2(d int, e int);
000075 INSERT INTO t2 VALUES(3,333),(4,444);
000076 CREATE TABLE t3(f int, g int);
000077 PRAGMA automatic_index=off;
000078 } {}
000079 do_catchsql_test join8-2010 {
000080 SELECT * FROM t1 RIGHT JOIN t2 ON c=d JOIN t3 ON f=e;
000081 } {0 {}}
000082
000083 # Demonstrate that nested FULL JOINs and USING clauses work
000084 #
000085 reset_db
000086 load_static_extension db series
000087 do_execsql_test join8-3000 {
000088 CREATE TABLE t1(id INTEGER PRIMARY KEY, a INT);
000089 CREATE TABLE t2(id INTEGER PRIMARY KEY, b INT);
000090 CREATE TABLE t3(id INTEGER PRIMARY KEY, c INT);
000091 CREATE TABLE t4(id INTEGER PRIMARY KEY, d INT);
000092 CREATE TABLE t5(id INTEGER PRIMARY KEY, e INT);
000093 CREATE TABLE t6(id INTEGER PRIMARY KEY, f INT);
000094 CREATE TABLE t7(id INTEGER PRIMARY KEY, g INT);
000095 CREATE TABLE t8(id INTEGER PRIMARY KEY, h INT);
000096 INSERT INTO t1 SELECT value, 1 FROM generate_series(1,256) WHERE value & 1;
000097 INSERT INTO t2 SELECT value, 1 FROM generate_series(1,256) WHERE value & 2;
000098 INSERT INTO t3 SELECT value, 1 FROM generate_series(1,256) WHERE value & 4;
000099 INSERT INTO t4 SELECT value, 1 FROM generate_series(1,256) WHERE value & 8;
000100 INSERT INTO t5 SELECT value, 1 FROM generate_series(1,256) WHERE value & 16;
000101 INSERT INTO t6 SELECT value, 1 FROM generate_series(1,256) WHERE value & 32;
000102 INSERT INTO t7 SELECT value, 1 FROM generate_series(1,256) WHERE value & 64;
000103 INSERT INTO t8 SELECT value, 1 FROM generate_series(1,256) WHERE value & 128;
000104 CREATE TABLE t9 AS
000105 SELECT id, h, g, f, e, d, c, b, a
000106 FROM t1
000107 NATURAL FULL JOIN t2
000108 NATURAL FULL JOIN t3
000109 NATURAL FULL JOIN t4
000110 NATURAL FULL JOIN t5
000111 NATURAL FULL JOIN t6
000112 NATURAL FULL JOIN t7
000113 NATURAL FULL JOIN t8;
000114 } {}
000115 do_execsql_test join8-3010 {
000116 SELECT count(*) FROM t9;
000117 } {255}
000118 do_execsql_test join8-3020 {
000119 SELECT id, count(*) FROM t9 GROUP BY id HAVING count(*)!=1;
000120 } {}
000121 do_execsql_test join8-3030 {
000122 UPDATE t9 SET a=0 WHERE a IS NULL;
000123 UPDATE t9 SET b=0 WHERE b IS NULL;
000124 UPDATE t9 SET c=0 WHERE c IS NULL;
000125 UPDATE t9 SET d=0 WHERE d IS NULL;
000126 UPDATE t9 SET e=0 WHERE e IS NULL;
000127 UPDATE t9 SET f=0 WHERE f IS NULL;
000128 UPDATE t9 SET g=0 WHERE g IS NULL;
000129 UPDATE t9 SET h=0 WHERE h IS NULL;
000130 SELECT count(*) FROM t9 WHERE id=128*h+64*g+32*f+16*e+8*d+4*c+2*b+a;
000131 } {255}
000132 do_execsql_test join8-3040 {
000133 SELECT * FROM t9 WHERE id<>128*h+64*g+32*f+16*e+8*d+4*c+2*b+a;
000134 } {}
000135
000136 # 2022-04-21 dbsqlfuzz find
000137 #
000138 reset_db
000139 do_execsql_test join8-4000 {
000140 CREATE TABLE t1(x INTEGER PRIMARY KEY, a, b);
000141 INSERT INTO t1 VALUES(1,5555,4);
000142 CREATE INDEX i1a ON t1(a);
000143 CREATE INDEX i1b ON t1(b);
000144 SELECT a FROM t1 NATURAL RIGHT JOIN t1 WHERE a=5555 OR (1,b)==(SELECT 2 IN (2,2),4);
000145 } {5555}
000146
000147 # 2022-04-23 dbsqlfuzz c7ee5500e3abddec3557016de777713b80c790d3
000148 # Escape from the right-join body subroutine via the ORDER BY LIMIT optimization.
000149 #
000150 reset_db
000151 db null -
000152 do_catchsql_test join8-5000 {
000153 CREATE TABLE t1(x);
000154 INSERT INTO t1(x) VALUES(NULL),(NULL);
000155 CREATE TABLE t2(c, d);
000156 INSERT INTO t2(c,d) SELECT x, x FROM t1;
000157 CREATE INDEX t2dc ON t2(d, c);
000158 SELECT (SELECT c FROM sqlite_temp_schema FULL JOIN t2 ON d IN (1,2,3) ORDER BY d) AS x FROM t1;
000159 } {0 {- -}}
000160
000161 # 2022-04-29 dbsqlfuzz 19f1102a70cf966ab249de56d944fc20dbebcfcf
000162 # Verification of testtag-20230227b and testtag-20230227c
000163 #
000164 reset_db
000165 do_execsql_test join8-6000 {
000166 CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d REAL);
000167 INSERT INTO t1 VALUES(1,'A','aa',2.5);
000168 SELECT * FROM t1 AS t2 NATURAL RIGHT JOIN t1 AS t3
000169 WHERE (a,b) IN (SELECT rowid, b FROM t1);
000170 } {1 A aa 2.5}
000171 do_execsql_test join8-6010 {
000172 DROP TABLE IF EXISTS t1;
000173 CREATE TABLE t1(a INT PRIMARY KEY, b TEXT, c TEXT, d INT) WITHOUT ROWID;
000174 INSERT INTO t1 VALUES(15,'xray','baker',42);
000175 SELECT value, t1.* FROM json_each('7') NATURAL RIGHT JOIN t1
000176 WHERE (a,b) IN (SELECT a, b FROM t1);
000177 } {7 15 xray baker 42}
000178 do_execsql_test join8-6020 {
000179 DROP TABLE IF EXISTS t1;
000180 CREATE TABLE t1(a INTEGER PRIMARY KEY,b);
000181 INSERT INTO t1 VALUES(0,NULL),(1,2);
000182 SELECT value, t1.* FROM json_each('17') NATURAL RIGHT JOIN t1
000183 WHERE (a,b) IN (SELECT rowid, b FROM t1);
000184 } {17 1 2}
000185 do_execsql_test join8-6021 {
000186 SELECT value, t1.* FROM json_each('null') NATURAL RIGHT JOIN t1
000187 WHERE (a,b) IN (SELECT rowid, b FROM t1);
000188 } {{} 1 2}
000189 do_execsql_test join8-6022 {
000190 CREATE TABLE a(key TEXT);
000191 INSERT INTO a(key) VALUES('a'),('b');
000192 SELECT quote(a.key), b.value
000193 FROM a RIGHT JOIN json_each('["a","c"]') AS b ON a.key=b.value;
000194 } {'a' a NULL c}
000195
000196 # Bloom filter usage by RIGHT and FULL JOIN
000197 #
000198 reset_db
000199 do_execsql_test join8-7000 {
000200 CREATE TABLE t1(a INT, b INT, c INT, d INT);
000201 WITH RECURSIVE c(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM c WHERE x<10)
000202 INSERT INTO t1(a,b,c,d) SELECT x, x+100, x+200, x+300 FROM c;
000203 CREATE TABLE t2(b INT, x INT);
000204 INSERT INTO t2(b,x) SELECT b, a FROM t1 WHERE a%2=0;
000205 CREATE INDEX t2b ON t2(b);
000206 CREATE TABLE t3(c INT, y INT);
000207 INSERT INTO t3(c,y) SELECT c, a FROM t1 WHERE a%3=0;
000208 CREATE INDEX t3c ON t3(c);
000209 CREATE TABLE t4(d INT, z INT);
000210 INSERT INTO t4(d,z) SELECT d, a FROM t1 WHERE a%5=0;
000211 CREATE INDEX t4d ON t4(d);
000212 INSERT INTO t1(a,b,c,d) VALUES
000213 (96,NULL,296,396),
000214 (97,197,NULL,397),
000215 (98,198,298,NULL),
000216 (99,NULL,NULL,NULL);
000217 ANALYZE sqlite_schema;
000218 INSERT INTO sqlite_stat1 VALUES('t4','t4d','20 1');
000219 INSERT INTO sqlite_stat1 VALUES('t3','t3c','32 1');
000220 INSERT INTO sqlite_stat1 VALUES('t2','t2b','48 1');
000221 INSERT INTO sqlite_stat1 VALUES('t1',NULL,'100');
000222 ANALYZE sqlite_schema;
000223 } {}
000224 db null -
000225 do_execsql_test join8-7010 {
000226 WITH t0 AS MATERIALIZED (
000227 SELECT t1.*, t2.*, t3.*
000228 FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>0
000229 RIGHT JOIN t3 ON t1.c=t3.c AND t3.y>0
000230 )
000231 SELECT * FROM t0 FULL JOIN t4 ON t0.a=t4.d AND t4.z>0
000232 ORDER BY coalesce(t0.a, t0.y+200, t4.d);
000233 } {
000234 6 106 206 306 106 6 206 6 - -
000235 - - - - - - 200 0 - -
000236 - - - - - - 203 3 - -
000237 - - - - - - 209 9 - -
000238 - - - - - - - - 300 0
000239 - - - - - - - - 305 5
000240 - - - - - - - - 310 10
000241 }
000242
000243 # EVIDENCE-OF: R-33754-02880 you can say "LEFT RIGHT JOIN" which is the
000244 # same as "FULL JOIN".
000245 do_execsql_test join8-7011 {
000246 WITH t0 AS MATERIALIZED (
000247 SELECT t1.*, t2.*, t3.*
000248 FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>0
000249 RIGHT JOIN t3 ON t1.c=t3.c AND t3.y>0
000250 )
000251 SELECT * FROM t0 LEFT RIGHT JOIN t4 ON t0.a=t4.d AND t4.z>0
000252 ORDER BY coalesce(t0.a, t0.y+200, t4.d);
000253 } {
000254 6 106 206 306 106 6 206 6 - -
000255 - - - - - - 200 0 - -
000256 - - - - - - 203 3 - -
000257 - - - - - - 209 9 - -
000258 - - - - - - - - 300 0
000259 - - - - - - - - 305 5
000260 - - - - - - - - 310 10
000261 }
000262
000263 do_execsql_test join8-7020 {
000264 EXPLAIN QUERY PLAN
000265 WITH t0 AS MATERIALIZED (
000266 SELECT t1.*, t2.*, t3.*
000267 FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>0
000268 RIGHT JOIN t3 ON t1.c=t3.c AND t3.y>0
000269 )
000270 SELECT * FROM t0 FULL JOIN t4 ON t0.a=t4.d AND t4.z>0
000271 ORDER BY coalesce(t0.a, t0.y+200, t4.d);
000272 } {/.*BLOOM FILTER ON t2.*BLOOM FILTER ON t3.*/}
000273
000274 # 2022-05-12 Difference with PG found (by Dan) while exploring
000275 # https://sqlite.org/forum/forumpost/677a0ab93fcd9ccd
000276 #
000277 reset_db
000278 do_execsql_test join8-8000 {
000279 CREATE TABLE t1(a INT, b INT);
000280 CREATE TABLE t2(c INT, d INT);
000281 CREATE TABLE t3(e INT, f INT);
000282 INSERT INTO t1 VALUES(1, 2);
000283 INSERT INTO t2 VALUES(3, 4);
000284 INSERT INTO t3 VALUES(5, 6);
000285 } {}
000286 do_execsql_test join8-8010 {
000287 SELECT *
000288 FROM t3 LEFT JOIN t2 ON true
000289 JOIN t1 ON (t3.e IS t2.c);
000290 } {}
000291 do_execsql_test join8-8020 {
000292 SELECT *
000293 FROM t3 LEFT JOIN t2 ON true
000294 JOIN t1 ON (t3.e IS NOT DISTINCT FROM t2.c);
000295 } {}
000296
000297 # 2022-05-13 The idea of reusing subquery cursors does not
000298 # work, if the cursors are used both for scanning and lookups.
000299 #
000300 reset_db
000301 db null -
000302 do_execsql_test join8-9000 {
000303 CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d REAL);
000304 INSERT INTO t1 VALUES(1,'E','bb',NULL),(2,NULL,NULL,NULL);
000305 SELECT * FROM t1 NATURAL RIGHT JOIN t1 AS t2 WHERE (a,b) IN (SELECT a+0, b FROM t1);
000306 } {1 E bb -}
000307
000308 # 2022-05-14 https://sqlite.org/forum/forumpost/c06b10ad7e
000309 #
000310 reset_db
000311 db null -
000312 do_execsql_test join8-10000 {
000313 CREATE TABLE t1(c0 INT UNIQUE);
000314 CREATE TABLE t2(c0);
000315 CREATE TABLE t2i(c0 INT);
000316 CREATE TABLE t3(c0 INT);
000317 INSERT INTO t1 VALUES(1);
000318 INSERT INTO t2 VALUES(2);
000319 INSERT INTO t2i VALUES(2);
000320 INSERT INTO t3 VALUES(3);
000321 } {}
000322 do_execsql_test join8-10010 {
000323 SELECT DISTINCT t1.c0, t3.c0
000324 FROM t2 NATURAL JOIN t1 RIGHT JOIN t3 ON t1.c0;
000325 } {- 3}
000326 do_execsql_test join8-10020 {
000327 SELECT t1.c0, t3.c0
000328 FROM t2 NATURAL JOIN t1 RIGHT JOIN t3 ON t1.c0;
000329 } {- 3}
000330 do_execsql_test join8-10030 {
000331 SELECT DISTINCT t1.c0, t3.c0
000332 FROM t2 NATURAL CROSS JOIN t1 RIGHT JOIN t3 ON t1.c0;
000333 } {- 3}
000334 do_execsql_test join8-10040 {
000335 SELECT t1.c0, t3.c0
000336 FROM t1 NATURAL CROSS JOIN t2 RIGHT JOIN t3 ON t1.c0;
000337 } {- 3}
000338 do_execsql_test join8-10050 {
000339 SELECT DISTINCT t1.c0, t3.c0
000340 FROM t2i NATURAL JOIN t1 RIGHT JOIN t3 ON t1.c0;
000341 } {- 3}
000342 do_execsql_test join8-10060 {
000343 SELECT DISTINCT +t1.c0, t3.c0
000344 FROM t2 NATURAL JOIN t1 RIGHT JOIN t3 ON t1.c0;
000345 } {- 3}
000346 do_execsql_test join8-10070 {
000347 SELECT DISTINCT +t1.c0, t3.c0
000348 FROM t1 NATURAL CROSS JOIN t2 RIGHT JOIN t3 ON t1.c0;
000349 } {- 3}
000350 do_execsql_test join8-10080 {
000351 SELECT DISTINCT t1.c0, t3.c0
000352 FROM t2 NATURAL JOIN t1 RIGHT JOIN t3 ON t1.c0<>0;
000353 } {- 3}
000354
000355 # 2022-05-14
000356 # index-on-expr scan on a RIGHT JOIN
000357 # dbsqlfuzz 39ee60004ff027a9e2846cf76e02cd5ac0953739
000358 #
000359 reset_db
000360 db null -
000361 do_execsql_test join8-11000 {
000362 CREATE TABLE t1(a);
000363 CREATE TABLE t2(b);
000364 INSERT INTO t2 VALUES(0),(1),(2);
000365 SELECT * FROM t1 RIGHT JOIN t2 ON (a=b) WHERE 99+(b+1)!=99;
000366 } {- 0 - 1 - 2}
000367 do_execsql_test join8-11010 {
000368 CREATE INDEX t2b ON t2(b+1) WHERE b IS NOT NULL;
000369 SELECT * FROM t1 RIGHT JOIN t2 ON (a=b) WHERE 99+(b+1)!=99;
000370 } {- 0 - 1 - 2}
000371 do_execsql_test join8-11020 {
000372 DROP TABLE t1;
000373 DROP TABLE t2;
000374 CREATE TABLE t1(a);
000375 CREATE TABLE t2(b, c, d);
000376 INSERT INTO t2 VALUES(1, 3, 'not-4');
000377 SELECT b, d FROM t1 RIGHT JOIN t2 WHERE (b+0)=1 AND d!=4;
000378 } {1 not-4}
000379 do_execsql_test join8-11030 {
000380 CREATE INDEX i2 ON t2((b+0), d);
000381 SELECT b, d FROM t1 RIGHT JOIN t2 WHERE (b+0)=1 AND d!=4;
000382 } {1 not-4}
000383 do_execsql_test join8-11040 {
000384 DROP INDEX i2;
000385 CREATE INDEX i2 ON t2((b+0), d) WHERE d IS NOT NULL;
000386 SELECT b, d FROM t1 RIGHT JOIN t2 WHERE (b+0)=1 AND d!=4;
000387 } {1 not-4}
000388
000389 # 2022-05-23
000390 # NATURAL JOIN name resolution is more forgiving with LEFT JOIN
000391 # https://sqlite.org/forum/forumpost/e90a8e6e6f
000392 #
000393 reset_db
000394 db null -
000395 do_execsql_test join8-12000 {
000396 CREATE TABLE t1(a INT); INSERT INTO t1 VALUES(0),(1);
000397 CREATE TABLE t2(a INT); INSERT INTO t2 VALUES(0),(2);
000398 CREATE TABLE t3(a INT); INSERT INTO t3 VALUES(0),(3);
000399 } {}
000400 do_catchsql_test join8-12010 {
000401 SELECT * FROM t1 RIGHT JOIN t2 ON t2.a<>0 NATURAL RIGHT JOIN t3;
000402 } {1 {ambiguous reference to a in USING()}}
000403 do_catchsql_test join8-12020 {
000404 SELECT * FROM t1 RIGHT JOIN t2 ON t2.a<>0 NATURAL LEFT JOIN t3;
000405 } {1 {ambiguous reference to a in USING()}}
000406 do_catchsql_test join8-12030 {
000407 SELECT * FROM t1 LEFT JOIN t2 ON t2.a<>0 NATURAL RIGHT JOIN t3;
000408 } {1 {ambiguous reference to a in USING()}}
000409
000410 # The following query should probably also return the same error as the
000411 # previous three cases. However, historical versions of SQLite have always
000412 # let it pass. We will not "fix" this, since to do so might break legacy
000413 # applications.
000414 #
000415 do_catchsql_test join8-12040 {
000416 SELECT * FROM t1 LEFT JOIN t2 ON t2.a<>0 NATURAL LEFT JOIN t3;
000417 } {0 {0 2 1 2}}
000418
000419 # 2022-05-24
000420 # https://sqlite.org/forum/forumpost/687b0bf563a1d4f1
000421 #
000422 reset_db
000423 do_execsql_test join8-13000 {
000424 CREATE TABLE t0(t TEXT, u TEXT); INSERT INTO t0 VALUES('t', 'u');
000425 CREATE TABLE t1(v TEXT, w TEXT); INSERT INTO t1 VALUES('v', 'w');
000426 CREATE TABLE t2(x TEXT, y TEXT); INSERT INTO t2 VALUES('x', 'y');
000427 SELECT * FROM t0 JOIN t1 ON (t2.x NOTNULL) LEFT JOIN t2 ON false;
000428 SELECT * FROM t0 JOIN t1 ON (t2.x NOTNULL) LEFT JOIN t2 ON false
000429 WHERE t2.y ISNULL;
000430 } {}
000431
000432 # 2022-05-25
000433 # https://sqlite.org/forum/forumpost/5cfe08eed6
000434 #
000435 reset_db
000436 do_execsql_test join8-14000 {
000437 CREATE TABLE t0(a TEXT, b TEXT, c TEXT);
000438 CREATE TABLE t1(a TEXT);
000439 INSERT INTO t1 VALUES('1');
000440 CREATE VIEW v0 AS SELECT 'xyz' AS d;
000441 SELECT * FROM v0 RIGHT JOIN t1 ON t1.a<>'' INNER JOIN t0 ON t0.c<>'';
000442 SELECT * FROM v0 RIGHT JOIN t1 ON t1.a<>'' INNER JOIN t0 ON t0.c<>'' WHERE b ISNULL;
000443 } {}
000444 do_execsql_test join8-14010 {
000445 CREATE TABLE y0(a INT);
000446 CREATE TABLE y1(b INT); INSERT INTO y1 VALUES(1), (2);
000447 CREATE TABLE y2(c INT); INSERT INTO y2 VALUES(3), (4);
000448 } {}
000449 db null -
000450 do_execsql_test join8-14020 {
000451 SELECT * FROM y0 RIGHT JOIN y1 ON true INNER JOIN y2 ON true WHERE y2.c!=99 AND y2.c!=98;
000452 } {
000453 - 1 3
000454 - 1 4
000455 - 2 3
000456 - 2 4
000457 }
000458
000459 # 2022-05-30
000460 # https://sqlite.org/forum/forumpost/3902c7b833
000461 #
000462 reset_db
000463 do_execsql_test join8-15000 {
000464 CREATE TABLE t1(x INT);
000465 CREATE TABLE t2(y INT);
000466 CREATE TABLE t3(z INT);
000467 INSERT INTO t1 VALUES(10);
000468 INSERT INTO t3 VALUES(20),(30);
000469 }
000470 do_execsql_test join8-15010 {
000471 SELECT * FROM t1 LEFT JOIN t2 ON true JOIN t3 ON t2.y IS NOT NULL;
000472 } {}
000473 do_execsql_test join8-15020 {
000474 SELECT * FROM t1 LEFT JOIN t2 ON true JOIN t3 ON t2.y IS NOT NULL
000475 WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600);
000476 } {}
000477 do_execsql_test join8-15100 {
000478 PRAGMA automatic_index = 0;
000479 CREATE TABLE t4(x TEXT);
000480 CREATE TABLE t5(y TEXT);
000481 CREATE TABLE t6(z TEXT);
000482 INSERT INTO t4 VALUES('a'), ('b');
000483 INSERT INTO t5 VALUES('b'), ('c');
000484 INSERT INTO t6 VALUES('a'), ('d');
000485 } {}
000486 db null -
000487 do_execsql_test join8-15110 {
000488 SELECT * FROM t4 LEFT JOIN t5 ON x=y LEFT JOIN t6 ON (x=z) ORDER BY +x;
000489 } {a - a b b -}
000490 do_execsql_test join8-15120 {
000491 SELECT * FROM t4 LEFT JOIN t5 ON x=y LEFT JOIN t6 ON (x=z)
000492 WHERE t5.y!='x' AND t4.x!='x';
000493 } {b b -}
000494
000495 # 2022-05-31
000496 # https://sqlite.org/forum/forumpost/c2554d560b
000497 reset_db
000498 do_execsql_test join8-16000 {
000499 CREATE TABLE t1(a TEXT);
000500 CREATE TABLE t2(b TEXT);
000501 CREATE TABLE t3(c TEXT);
000502 INSERT INTO t2(b) VALUES ('x');
000503 INSERT INTO t3(c) VALUES ('y'), ('z');
000504 } {}
000505 db null -
000506 do_execsql_test join8-16010 {
000507 SELECT * FROM t1 RIGHT JOIN t2 ON true LEFT JOIN t3 ON a<>'';
000508 } {- x -}
000509 do_execsql_test join8-16020 {
000510 SELECT * FROM t1 RIGHT JOIN t2 ON true LEFT JOIN t3 ON a<>'' WHERE c IS NULL;
000511 } {- x -}
000512 do_execsql_test join8-16020 {
000513 SELECT * FROM t1 RIGHT JOIN t2 ON true JOIN t3 ON a<>'' WHERE c IS NULL;
000514 } {}
000515 do_execsql_test join8-16030 {
000516 SELECT * FROM t1 RIGHT JOIN t2 ON true JOIN t3 ON a<>'';
000517 } {}
000518 do_execsql_test join8-16040 {
000519 SELECT * FROM t1 RIGHT JOIN t2 ON true LEFT JOIN t3 ON a<>'' WHERE c<>'';
000520 } {}
000521 do_execsql_test join8-16050 {
000522 SELECT * FROM t1 RIGHT JOIN t2 ON true LEFT JOIN t3 ON a<>'' WHERE c IS NOT NULL;
000523 } {}
000524 do_execsql_test join8-16060 {
000525 SELECT * FROM t1 RIGHT JOIN t2 ON true JOIN t3 ON a<>'' WHERE c<>'';
000526 } {}
000527 do_execsql_test join8-16070 {
000528 SELECT * FROM t1 RIGHT JOIN t2 ON true JOIN t3 ON a<>'' WHERE c IS NOT NULL;
000529 } {}
000530
000531 # 2022-06-01
000532 # https://sqlite.org/forum/forumpost/087de2d9ec
000533 #
000534 reset_db
000535 do_execsql_test join8-17000 {
000536 CREATE TABLE t1(id INTEGER PRIMARY KEY, x INT, y INT);
000537 CREATE TABLE t2(z INT);
000538 INSERT INTO t1(id,x,y) VALUES(1, 0, 0);
000539 } {}
000540 db null NULL
000541 do_execsql_test join8-17010 {
000542 SELECT * FROM t2 RIGHT JOIN t1 ON true;
000543 } {NULL 1 0 0}
000544 do_execsql_test join8-17020 {
000545 SELECT 99=id AND 0=y AS "truth" FROM t2 RIGHT JOIN t1 ON true;
000546 } {0}
000547 do_execsql_test join8-17030 {
000548 SELECT (99, 0)==(id, y) AS "truth" FROM t2 RIGHT JOIN t1;
000549 } {0}
000550 do_execsql_test join8-17040 {
000551 SELECT * FROM t2 RIGHT JOIN t1 WHERE 99=id AND 0=y;
000552 } {}
000553 do_execsql_test join8-17041 {
000554 SELECT * FROM t2 RIGHT JOIN t1 WHERE 99=+id AND 0=y;
000555 } {}
000556 do_execsql_test join8-17050 {
000557 SELECT * FROM t2 RIGHT JOIN t1 WHERE (99, 0)==(id,y);
000558 } {}
000559 do_execsql_test join8-17051 {
000560 SELECT * FROM t2 RIGHT JOIN t1 WHERE (99, 0)==(+id,y);
000561 } {}
000562 do_execsql_test join8-17060 {
000563 SELECT * FROM t2 RIGHT JOIN t1 WHERE 1=id AND 0=y;
000564 } {NULL 1 0 0}
000565 do_execsql_test join8-17061 {
000566 SELECT * FROM t2 RIGHT JOIN t1 WHERE 1=+id AND 0=y;
000567 } {NULL 1 0 0}
000568 do_execsql_test join8-17070 {
000569 SELECT * FROM t2 RIGHT JOIN t1 WHERE (1, 0)==(id,y);
000570 } {NULL 1 0 0}
000571 do_execsql_test join8-17071 {
000572 SELECT * FROM t2 RIGHT JOIN t1 WHERE (1, 0)==(+id,y);
000573 } {NULL 1 0 0}
000574 do_execsql_test join8-17080 {
000575 CREATE TABLE t3(a INTEGER PRIMARY KEY, b INT);
000576 CREATE TABLE t4(x INT, y INT);
000577 INSERT INTO t3(a,b) VALUES(1, 3);
000578 } {}
000579 do_execsql_test join8-17090 {
000580 SELECT t3.a FROM t4 RIGHT JOIN t3 ON (x=a) WHERE (b, 4)=(SELECT 3, 4);
000581 } {1}
000582 do_execsql_test join8-17091 {
000583 SELECT t3.a FROM t4 RIGHT JOIN t3 ON (x=a) WHERE (b, 4) IS (SELECT 3, 4);
000584 } {1}
000585
000586 # 2022-06-06
000587 # https://sqlite.org/forum/forumpost/206d99a16dd9212f
000588 # tag-20191211-001
000589 #
000590 reset_db
000591 do_execsql_test join8-18000 {
000592 CREATE TABLE t1(a BOOLEAN); INSERT INTO t1 VALUES (false);
000593 CREATE TABLE t2(x INT); INSERT INTO t2 VALUES (0);
000594 SELECT *, x NOTNULL, (x NOTNULL)=a FROM t2 RIGHT JOIN t1 ON true WHERE (x NOTNULL)=a;
000595 } {}
000596 do_execsql_test join8-18010 {
000597 CREATE INDEX t1a ON t1(a);
000598 SELECT *, x NOTNULL, (x NOTNULL)=a FROM t2 RIGHT JOIN t1 ON true WHERE (x NOTNULL)=a;
000599 } {}
000600
000601 do_execsql_test join8-18020 {
000602 CREATE TABLE t3(z);
000603 INSERT INTO t3 VALUES('t3value');
000604 SELECT *, x NOTNULL, (x NOTNULL)=a FROM t2 RIGHT JOIN t1 ON true INNER JOIN t3 ON (x NOTNULL)=a;
000605 } {}
000606
000607 ifcapable rtree {
000608 do_execsql_test join8-18030 {
000609 CREATE VIRTUAL TABLE rtree1 USING rtree(a, x1, x2);
000610 INSERT INTO rtree1 VALUES(0, 0, 0);
000611 }
000612 do_execsql_test join8-18040 {
000613 SELECT *, x NOTNULL, (x NOTNULL)=a FROM t2
000614 RIGHT JOIN rtree1 ON true INNER JOIN t3 ON (x NOTNULL)=+a;
000615 } {}
000616 do_execsql_test join8-18050 {
000617 SELECT *, x NOTNULL, (x NOTNULL)=a FROM t2
000618 RIGHT JOIN rtree1 ON true INNER JOIN t3 ON (x NOTNULL)=a;
000619 } {}
000620 }
000621
000622
000623 reset_db
000624 do_execsql_test join8-19000 {
000625 CREATE TABLE t1(a INT);
000626 CREATE TABLE t2(b INT, c INT);
000627 CREATE TABLE t3(d INT);
000628
000629 INSERT INTO t1 VALUES(10);
000630 INSERT INTO t2 VALUES(50,51);
000631 INSERT INTO t3 VALUES(299);
000632
000633 CREATE INDEX t2b ON t2( (b IS NOT NULL) );
000634 }
000635
000636 do_execsql_test join8-19010 {
000637 SELECT * FROM t1 LEFT JOIN t2 ON true INNER JOIN t3 ON (b IS NOT NULL)=0;
000638 }
000639
000640 # 2022-06-07
000641 # https://sqlite.org/forum/forumpost/323f86cc30
000642 reset_db
000643 do_execsql_test join8-20000 {
000644 CREATE TABLE t1(x TEXT);
000645 INSERT INTO t1(x) VALUES('aaa');
000646 CREATE VIEW v0(y) AS SELECT x FROM t1;
000647 CREATE TABLE t2(z TEXT);
000648 } {}
000649 db null -
000650 do_execsql_test join8-20010 {
000651 SELECT * FROM t2 JOIN v0 ON z<>'bbb' RIGHT JOIN t1 ON z<>'ccc';
000652 } {- - aaa}
000653 do_execsql_test join8-20020 {
000654 SELECT * FROM t2 JOIN v0 ON z<>'bbb' RIGHT JOIN t1 ON z<>'ccc' ORDER BY z;
000655 } {- - aaa}
000656 do_execsql_test join8-20030 {
000657 SELECT 99 as "m" FROM t2 JOIN v0 ON z<>'bbb' RIGHT JOIN t1 ON z<>'ccc';
000658 } {99}
000659 do_execsql_test join8-20040 {
000660 SELECT 99 as "m" FROM t2 JOIN v0 ON z<>'bbb' RIGHT JOIN t1 ON z<>'ccc' ORDER BY z;
000661 } {99}
000662 do_execsql_test join8-20050 {
000663 SELECT count(*)
000664 FROM (SELECT 99 as "m" FROM t2 JOIN v0 ON z<>'' RIGHT JOIN t1 ON z<>'') AS "t3";
000665 } {1}
000666 do_execsql_test join8-20060 {
000667 SELECT count(*)
000668 FROM (SELECT 99 as "m" FROM t2 JOIN v0 ON z<>'' RIGHT JOIN t1 ON z<>'' ORDER BY z) AS "t3";
000669 } {1}
000670
000671 # 2022-06-10
000672 # https://sqlite.org/forum/forumpost/8e4c352937e82929
000673 #
000674 # Do not allow constant propagation between ON and WHERE clause terms.
000675 # (Updated 2022-06-20) See also https://sqlite.org/forum/forumpost/57bdf2217d
000676 #
000677 reset_db
000678 do_execsql_test join8-21000 {
000679 CREATE TABLE t1(a INT,b BOOLEAN);
000680 CREATE TABLE t2(c INT); INSERT INTO t2 VALUES(NULL);
000681 CREATE TABLE t3(d INT);
000682 }
000683 do_execsql_test join8-21010 {
000684 SELECT (b IS TRUE) FROM t1 JOIN t3 ON (b=TRUE) RIGHT JOIN t2 ON TRUE;
000685 } {0}
000686 do_execsql_test join8-22020 {
000687 SELECT * FROM t1 JOIN t3 ON (b=TRUE) RIGHT JOIN t2 ON TRUE WHERE (b IS TRUE);
000688 } {}
000689 do_execsql_test join8-22030 {
000690 DROP TABLE t1;
000691 DROP TABLE t2;
000692 DROP TABLE t3;
000693 CREATE TABLE t1(a INT);
000694 CREATE TABLE t2(b INT);
000695 CREATE TABLE t3(c INTEGER PRIMARY KEY, d INT);
000696 CREATE INDEX t3d ON t3(d);
000697 INSERT INTO t3 VALUES(0, 0);
000698 }
000699 do_catchsql_test join8-22031 {
000700 SELECT * FROM t1 JOIN t2 ON d>b RIGHT JOIN t3 ON true WHERE +d = 0;
000701 } {1 {ON clause references tables to its right}}
000702 do_catchsql_test join8-22040 {
000703 SELECT * FROM t1 JOIN t2 ON d>b RIGHT JOIN t3 ON true WHERE d = 0;
000704 } {1 {ON clause references tables to its right}}
000705
000706
000707 # 2022-06-10
000708 # https://sqlite.org/forum/forumpost/51e6959f61
000709 #
000710 # Restrictions on the usage of WHERE clause constraints by joins that are
000711 # involved with a RIGHT JOIN must also be applied to automatic indexes.
000712 #
000713 reset_db
000714 do_execsql_test join8-22000 {
000715 CREATE TABLE t1(a INT);
000716 CREATE TABLE t2(b INT);
000717 CREATE TABLE t3(c TEXT); INSERT INTO t3 VALUES('x');
000718 CREATE TABLE t4(d TEXT); INSERT INTO t4 VALUES('y');
000719 SELECT 99
000720 FROM t1
000721 LEFT JOIN t2 ON true
000722 RIGHT JOIN t3 ON true
000723 RIGHT JOIN t4 ON true
000724 WHERE a=b;
000725 } {}
000726
000727 # 2022-06-13
000728 # https://sqlite.org/forum/forumpost/b40696f501
000729 #
000730 # This optimization that converts "x ISNULL" into "FALSE" when column "x" has a
000731 # NOT NULL constraint is too aggresive if the query contains RIGHT JOIN.
000732 #
000733 reset_db
000734 db null -
000735 do_execsql_test join8-23000 {
000736 CREATE TABLE t1(a TEXT);
000737 INSERT INTO t1 VALUES('c');
000738 CREATE TABLE t2(b TEXT, c TEXT NOT NULL);
000739 INSERT INTO t2 VALUES('a', 'b');
000740 CREATE TABLE t3(d TEXT);
000741 INSERT INTO t3 VALUES('x');
000742 CREATE TABLE t4(e TEXT);
000743 INSERT INTO t4 VALUES('y');
000744 }
000745 do_execsql_test join8-23010 {
000746 SELECT *
000747 FROM t1
000748 LEFT JOIN t2 ON TRUE
000749 JOIN t3 ON c=''
000750 RIGHT JOIN t4 ON b='';
000751 } {- - - - y}
000752 do_execsql_test join8-23020 {
000753 SELECT *
000754 FROM t1
000755 LEFT JOIN t2 ON TRUE
000756 JOIN t3 ON c=''
000757 RIGHT JOIN t4 ON b=''
000758 WHERE d ISNULL
000759 } {- - - - y}
000760
000761 # 2022-06-14
000762 # dbsqlfuzz 2f3101834d14325a976f601b9267a0fd323d6bbd
000763 #
000764 # When the OP_NullRow opcode creates a new cursor, it must
000765 # set the cursor to no-reuse so that an OP_OpenEphemeral in
000766 # a subroutine does not try to reuse it.
000767 #
000768 reset_db
000769 db null -
000770 do_execsql_test join8-24000 {
000771 CREATE TABLE t4(b INT, c INT);
000772 CREATE TABLE t5(a INT, f INT);
000773 INSERT INTO t5 VALUES(1,2);
000774 WITH t7(x, y) AS (SELECT 100, 200 FROM t5)
000775 SELECT * FROM t4 JOIN t7 ON true RIGHT JOIN (SELECT y AS z FROM t7) AS t6 ON (x=z);
000776 } {- - - - 200}
000777
000778 # 2022-06-20
000779 # forum/forumpost/6650cd40b5634f35
000780 #
000781 reset_db
000782 do_execsql_test join8-25000 {
000783 CREATE TABLE t1(a1 INT);
000784 CREATE TABLE t2(b2 INT);
000785 CREATE TABLE t3(c3 INT, d3 INT UNIQUE);
000786 CREATE TABLE t4(e4 INT, f4 TEXT);
000787 INSERT INTO t3(c3, d3) VALUES (2, 1);
000788 INSERT INTO t4(f4) VALUES ('x');
000789 CREATE INDEX i0 ON t3(c3) WHERE d3 ISNULL;
000790 ANALYZE main;
000791 }
000792 db null -
000793 do_execsql_test join8-25010 {
000794 SELECT * FROM t1 LEFT JOIN t2 ON true JOIN t3 ON (b2 IN (a1)) FULL JOIN t4 ON true;
000795 } {- - - - - x}
000796 do_execsql_test join8-25020 {
000797 SELECT 1 FROM t1 LEFT JOIN t2 ON true JOIN t3 ON (b2 IN (a1)) FULL JOIN t4 ON true;
000798 } {1}
000799
000800 # 2022-07-13
000801 # forum/forumpost/174afeae57
000802 #
000803 reset_db
000804 db null -
000805 do_execsql_test join8-26000 {
000806 CREATE TABLE t1(a INT);
000807 CREATE TABLE t2(b INT, c INT);
000808 CREATE VIEW t3(d) AS SELECT NULL FROM t2 FULL OUTER JOIN t1 ON c=a UNION ALL SELECT b FROM t2;
000809 INSERT INTO t1(a) VALUES (NULL);
000810 INSERT INTO t2(b, c) VALUES (99, NULL);
000811 SELECT DISTINCT b, c, d FROM t2, t3 WHERE b<>0
000812 UNION SELECT DISTINCT b, c, d FROM t2, t3 WHERE b ISNULL;
000813 } {99 - - 99 - 99}
000814
000815 finish_test