AryaWu/sqlite
0
1# 2022-04-122#3# The author disclaims copyright to this source code. In place of4# 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 14set testdir [file dirname $argv0]15source $testdir/tester.tcl16 17ifcapable !vtab {18 finish_test19 return20}21 22db null NULL23# EVIDENCE-OF: R-33754-02880 you can say "LEFT RIGHT JOIN" which is the24# same as "FULL JOIN".25do_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 the34# RIGHT JOIN body subroutine before the first use of any register containing35# the results of that subroutine. This seems dodgy. Test case:36#37reset_db38do_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} {}44do_catchsql_test join8-1010 {45 SELECT a46 FROM (47 SELECT a48 FROM (49 SELECT a50 FROM (51 SELECT a FROM t1 NATURAL LEFT JOIN t152 WHERE (b, 2 ) IS ( SELECT 2 IN(2,2),2)53 )54 NATURAL LEFT FULL JOIN t155 WHERE ( rowid , 1 )<=(CASE 5 WHEN 619 THEN 841 ELSE 3374391096 END,0)56 ORDER BY a ASC57 )58 NATURAL LEFT JOIN t159 WHERE (b, 2 ) IS ( SELECT 3 IN(3,3),3)60 )61 NATURAL LEFT FULL JOIN t162 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 the68# OP_Return, resulting in a subroutine loop. Test case:69#70reset_db71do_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} {}79do_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 work84#85reset_db86load_static_extension db series87do_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 AS105 SELECT id, h, g, f, e, d, c, b, a106 FROM t1107 NATURAL FULL JOIN t2108 NATURAL FULL JOIN t3109 NATURAL FULL JOIN t4110 NATURAL FULL JOIN t5111 NATURAL FULL JOIN t6112 NATURAL FULL JOIN t7113 NATURAL FULL JOIN t8;114} {}115do_execsql_test join8-3010 {116 SELECT count(*) FROM t9;117} {255}118do_execsql_test join8-3020 {119 SELECT id, count(*) FROM t9 GROUP BY id HAVING count(*)!=1;120} {}121do_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}132do_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 find137#138reset_db139do_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 c7ee5500e3abddec3557016de777713b80c790d3148# Escape from the right-join body subroutine via the ORDER BY LIMIT optimization.149#150reset_db151db null -152do_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 19f1102a70cf966ab249de56d944fc20dbebcfcf162# Verification of testtag-20230227b and testtag-20230227c163#164reset_db165do_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 t3169 WHERE (a,b) IN (SELECT rowid, b FROM t1);170} {1 A aa 2.5}171do_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 t1176 WHERE (a,b) IN (SELECT a, b FROM t1);177} {7 15 xray baker 42}178do_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 t1183 WHERE (a,b) IN (SELECT rowid, b FROM t1);184} {17 1 2}185do_execsql_test join8-6021 {186 SELECT value, t1.* FROM json_each('null') NATURAL RIGHT JOIN t1187 WHERE (a,b) IN (SELECT rowid, b FROM t1);188} {{} 1 2}189do_execsql_test join8-6022 {190 CREATE TABLE a(key TEXT);191 INSERT INTO a(key) VALUES('a'),('b');192 SELECT quote(a.key), b.value193 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 JOIN197#198reset_db199do_execsql_test join8-7000 {200CREATE 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) VALUES213 (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} {}224db null -225do_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>0229 RIGHT JOIN t3 ON t1.c=t3.c AND t3.y>0230 )231 SELECT * FROM t0 FULL JOIN t4 ON t0.a=t4.d AND t4.z>0232 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 0239 - - - - - - - - 305 5240 - - - - - - - - 310 10241}242 243# EVIDENCE-OF: R-33754-02880 you can say "LEFT RIGHT JOIN" which is the244# same as "FULL JOIN".245do_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>0249 RIGHT JOIN t3 ON t1.c=t3.c AND t3.y>0250 )251 SELECT * FROM t0 LEFT RIGHT JOIN t4 ON t0.a=t4.d AND t4.z>0252 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 0259 - - - - - - - - 305 5260 - - - - - - - - 310 10261}262 263do_execsql_test join8-7020 {264 EXPLAIN QUERY PLAN265 WITH t0 AS MATERIALIZED (266 SELECT t1.*, t2.*, t3.*267 FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>0268 RIGHT JOIN t3 ON t1.c=t3.c AND t3.y>0269 )270 SELECT * FROM t0 FULL JOIN t4 ON t0.a=t4.d AND t4.z>0271 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 exploring275# https://sqlite.org/forum/forumpost/677a0ab93fcd9ccd276#277reset_db278do_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} {}286do_execsql_test join8-8010 {287 SELECT *288 FROM t3 LEFT JOIN t2 ON true289 JOIN t1 ON (t3.e IS t2.c);290} {}291do_execsql_test join8-8020 {292 SELECT *293 FROM t3 LEFT JOIN t2 ON true294 JOIN t1 ON (t3.e IS NOT DISTINCT FROM t2.c);295} {}296 297# 2022-05-13 The idea of reusing subquery cursors does not298# work, if the cursors are used both for scanning and lookups.299#300reset_db301db null -302do_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/c06b10ad7e309#310reset_db311db null -312do_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} {}322do_execsql_test join8-10010 {323 SELECT DISTINCT t1.c0, t3.c0324 FROM t2 NATURAL JOIN t1 RIGHT JOIN t3 ON t1.c0;325} {- 3}326do_execsql_test join8-10020 {327 SELECT t1.c0, t3.c0328 FROM t2 NATURAL JOIN t1 RIGHT JOIN t3 ON t1.c0;329} {- 3}330do_execsql_test join8-10030 {331 SELECT DISTINCT t1.c0, t3.c0332 FROM t2 NATURAL CROSS JOIN t1 RIGHT JOIN t3 ON t1.c0;333} {- 3}334do_execsql_test join8-10040 {335 SELECT t1.c0, t3.c0336 FROM t1 NATURAL CROSS JOIN t2 RIGHT JOIN t3 ON t1.c0;337} {- 3}338do_execsql_test join8-10050 {339 SELECT DISTINCT t1.c0, t3.c0340 FROM t2i NATURAL JOIN t1 RIGHT JOIN t3 ON t1.c0;341} {- 3}342do_execsql_test join8-10060 {343 SELECT DISTINCT +t1.c0, t3.c0344 FROM t2 NATURAL JOIN t1 RIGHT JOIN t3 ON t1.c0;345} {- 3}346do_execsql_test join8-10070 {347 SELECT DISTINCT +t1.c0, t3.c0348 FROM t1 NATURAL CROSS JOIN t2 RIGHT JOIN t3 ON t1.c0;349} {- 3}350do_execsql_test join8-10080 {351 SELECT DISTINCT t1.c0, t3.c0352 FROM t2 NATURAL JOIN t1 RIGHT JOIN t3 ON t1.c0<>0;353} {- 3}354 355# 2022-05-14356# index-on-expr scan on a RIGHT JOIN357# dbsqlfuzz 39ee60004ff027a9e2846cf76e02cd5ac0953739358#359reset_db360db null -361do_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}367do_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}371do_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}379do_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}383do_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-23390# NATURAL JOIN name resolution is more forgiving with LEFT JOIN391# https://sqlite.org/forum/forumpost/e90a8e6e6f392#393reset_db394db null -395do_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} {}400do_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()}}403do_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()}}406do_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 the411# previous three cases. However, historical versions of SQLite have always412# let it pass. We will not "fix" this, since to do so might break legacy413# applications.414#415do_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-24420# https://sqlite.org/forum/forumpost/687b0bf563a1d4f1421#422reset_db423do_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 false429 WHERE t2.y ISNULL;430} {}431 432# 2022-05-25433# https://sqlite.org/forum/forumpost/5cfe08eed6434#435reset_db436do_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} {}444do_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} {}449db null -450do_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 3454 - 1 4455 - 2 3456 - 2 4457}458 459# 2022-05-30460# https://sqlite.org/forum/forumpost/3902c7b833461#462reset_db463do_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}470do_execsql_test join8-15010 {471 SELECT * FROM t1 LEFT JOIN t2 ON true JOIN t3 ON t2.y IS NOT NULL;472} {}473do_execsql_test join8-15020 {474 SELECT * FROM t1 LEFT JOIN t2 ON true JOIN t3 ON t2.y IS NOT NULL475 WHERE (t3.z!=400 AND t3.z!=500 AND t3.z!=600);476} {}477do_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} {}486db null -487do_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 -}490do_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-31496# https://sqlite.org/forum/forumpost/c2554d560b497reset_db498do_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} {}505db null -506do_execsql_test join8-16010 {507 SELECT * FROM t1 RIGHT JOIN t2 ON true LEFT JOIN t3 ON a<>'';508} {- x -}509do_execsql_test join8-16020 {510 SELECT * FROM t1 RIGHT JOIN t2 ON true LEFT JOIN t3 ON a<>'' WHERE c IS NULL;511} {- x -}512do_execsql_test join8-16020 {513 SELECT * FROM t1 RIGHT JOIN t2 ON true JOIN t3 ON a<>'' WHERE c IS NULL;514} {}515do_execsql_test join8-16030 {516 SELECT * FROM t1 RIGHT JOIN t2 ON true JOIN t3 ON a<>'';517} {}518do_execsql_test join8-16040 {519 SELECT * FROM t1 RIGHT JOIN t2 ON true LEFT JOIN t3 ON a<>'' WHERE c<>'';520} {}521do_execsql_test join8-16050 {522 SELECT * FROM t1 RIGHT JOIN t2 ON true LEFT JOIN t3 ON a<>'' WHERE c IS NOT NULL;523} {}524do_execsql_test join8-16060 {525 SELECT * FROM t1 RIGHT JOIN t2 ON true JOIN t3 ON a<>'' WHERE c<>'';526} {}527do_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-01532# https://sqlite.org/forum/forumpost/087de2d9ec533#534reset_db535do_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} {}540db null NULL541do_execsql_test join8-17010 {542 SELECT * FROM t2 RIGHT JOIN t1 ON true;543} {NULL 1 0 0}544do_execsql_test join8-17020 {545 SELECT 99=id AND 0=y AS "truth" FROM t2 RIGHT JOIN t1 ON true;546} {0}547do_execsql_test join8-17030 {548 SELECT (99, 0)==(id, y) AS "truth" FROM t2 RIGHT JOIN t1;549} {0}550do_execsql_test join8-17040 {551 SELECT * FROM t2 RIGHT JOIN t1 WHERE 99=id AND 0=y;552} {}553do_execsql_test join8-17041 {554 SELECT * FROM t2 RIGHT JOIN t1 WHERE 99=+id AND 0=y;555} {}556do_execsql_test join8-17050 {557 SELECT * FROM t2 RIGHT JOIN t1 WHERE (99, 0)==(id,y);558} {}559do_execsql_test join8-17051 {560 SELECT * FROM t2 RIGHT JOIN t1 WHERE (99, 0)==(+id,y);561} {}562do_execsql_test join8-17060 {563 SELECT * FROM t2 RIGHT JOIN t1 WHERE 1=id AND 0=y;564} {NULL 1 0 0}565do_execsql_test join8-17061 {566 SELECT * FROM t2 RIGHT JOIN t1 WHERE 1=+id AND 0=y;567} {NULL 1 0 0}568do_execsql_test join8-17070 {569 SELECT * FROM t2 RIGHT JOIN t1 WHERE (1, 0)==(id,y);570} {NULL 1 0 0}571do_execsql_test join8-17071 {572 SELECT * FROM t2 RIGHT JOIN t1 WHERE (1, 0)==(+id,y);573} {NULL 1 0 0}574do_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} {}579do_execsql_test join8-17090 {580 SELECT t3.a FROM t4 RIGHT JOIN t3 ON (x=a) WHERE (b, 4)=(SELECT 3, 4);581} {1}582do_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-06587# https://sqlite.org/forum/forumpost/206d99a16dd9212f588# tag-20191211-001589#590reset_db591do_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} {}596do_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 601do_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 607ifcapable 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 623reset_db624do_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 636do_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-07641# https://sqlite.org/forum/forumpost/323f86cc30642reset_db643do_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} {}649db null -650do_execsql_test join8-20010 {651 SELECT * FROM t2 JOIN v0 ON z<>'bbb' RIGHT JOIN t1 ON z<>'ccc';652} {- - aaa}653do_execsql_test join8-20020 {654 SELECT * FROM t2 JOIN v0 ON z<>'bbb' RIGHT JOIN t1 ON z<>'ccc' ORDER BY z;655} {- - aaa}656do_execsql_test join8-20030 {657 SELECT 99 as "m" FROM t2 JOIN v0 ON z<>'bbb' RIGHT JOIN t1 ON z<>'ccc';658} {99}659do_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}662do_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}666do_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-10672# https://sqlite.org/forum/forumpost/8e4c352937e82929673#674# Do not allow constant propagation between ON and WHERE clause terms.675# (Updated 2022-06-20) See also https://sqlite.org/forum/forumpost/57bdf2217d676#677reset_db678do_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}683do_execsql_test join8-21010 {684 SELECT (b IS TRUE) FROM t1 JOIN t3 ON (b=TRUE) RIGHT JOIN t2 ON TRUE;685} {0}686do_execsql_test join8-22020 {687 SELECT * FROM t1 JOIN t3 ON (b=TRUE) RIGHT JOIN t2 ON TRUE WHERE (b IS TRUE);688} {}689do_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}699do_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}}702do_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-10708# https://sqlite.org/forum/forumpost/51e6959f61709#710# Restrictions on the usage of WHERE clause constraints by joins that are711# involved with a RIGHT JOIN must also be applied to automatic indexes.712#713reset_db714do_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 99720 FROM t1721 LEFT JOIN t2 ON true722 RIGHT JOIN t3 ON true723 RIGHT JOIN t4 ON true724 WHERE a=b;725} {}726 727# 2022-06-13728# https://sqlite.org/forum/forumpost/b40696f501729#730# This optimization that converts "x ISNULL" into "FALSE" when column "x" has a731# NOT NULL constraint is too aggresive if the query contains RIGHT JOIN.732#733reset_db734db null -735do_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}745do_execsql_test join8-23010 {746 SELECT *747 FROM t1748 LEFT JOIN t2 ON TRUE749 JOIN t3 ON c=''750 RIGHT JOIN t4 ON b='';751} {- - - - y}752do_execsql_test join8-23020 {753 SELECT *754 FROM t1755 LEFT JOIN t2 ON TRUE756 JOIN t3 ON c=''757 RIGHT JOIN t4 ON b=''758 WHERE d ISNULL759} {- - - - y}760 761# 2022-06-14762# dbsqlfuzz 2f3101834d14325a976f601b9267a0fd323d6bbd763#764# When the OP_NullRow opcode creates a new cursor, it must765# set the cursor to no-reuse so that an OP_OpenEphemeral in766# a subroutine does not try to reuse it.767#768reset_db769db null -770do_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-20779# forum/forumpost/6650cd40b5634f35780#781reset_db782do_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}792db null -793do_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}796do_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-13801# forum/forumpost/174afeae57802#803reset_db804db null -805do_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<>0812 UNION SELECT DISTINCT b, c, d FROM t2, t3 WHERE b ISNULL;813} {99 - - 99 - 99}814 815finish_test816