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 14set testdir [file dirname $argv0] 15source $testdir/tester.tcl 16 17ifcapable !vtab { 18 finish_test 19 return 20} 21 22db null NULL 23# EVIDENCE-OF: R-33754-02880 you can say "LEFT RIGHT JOIN" which is the 24# 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 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# 37reset_db 38do_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 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# 70reset_db 71do_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 work 84# 85reset_db 86load_static_extension db series 87do_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} {} 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 find 137# 138reset_db 139do_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# 150reset_db 151db 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 19f1102a70cf966ab249de56d944fc20dbebcfcf 162# 163reset_db 164do_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} 170do_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} 177do_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# 187reset_db 188do_execsql_test join8-7000 { 189CREATE 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} {} 213db null - 214do_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". 234do_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 252do_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# 266reset_db 267do_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} {} 275do_execsql_test join8-8010 { 276 SELECT * 277 FROM t3 LEFT JOIN t2 ON true 278 JOIN t1 ON (t3.e IS t2.c); 279} {} 280do_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# 289reset_db 290db null - 291do_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# 299reset_db 300db null - 301do_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} {} 311do_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} 315do_execsql_test join8-10020 { 316 SELECT t1.c0, t3.c0 317 FROM t2 NATURAL JOIN t1 RIGHT JOIN t3 ON t1.c0; 318} {- 3} 319do_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} 323do_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} 327do_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} 331do_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} 335do_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} 339do_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# 348reset_db 349db null - 350do_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} 356do_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} 360do_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} 368do_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} 372do_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# 382reset_db 383db null - 384do_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} {} 389do_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()}} 392do_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()}} 395do_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# 404do_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# 411reset_db 412do_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# 424reset_db 425do_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} {} 433do_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} {} 438db null - 439do_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# 451reset_db 452do_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} 459do_execsql_test join8-15010 { 460 SELECT * FROM t1 LEFT JOIN t2 ON true JOIN t3 ON t2.y IS NOT NULL; 461} {} 462do_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} {} 466do_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} {} 475db null - 476do_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 -} 479do_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 486reset_db 487do_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} {} 494db null - 495do_execsql_test join8-16010 { 496 SELECT * FROM t1 RIGHT JOIN t2 ON true LEFT JOIN t3 ON a<>''; 497} {- x -} 498do_execsql_test join8-16020 { 499 SELECT * FROM t1 RIGHT JOIN t2 ON true LEFT JOIN t3 ON a<>'' WHERE c IS NULL; 500} {- x -} 501do_execsql_test join8-16020 { 502 SELECT * FROM t1 RIGHT JOIN t2 ON true JOIN t3 ON a<>'' WHERE c IS NULL; 503} {} 504do_execsql_test join8-16030 { 505 SELECT * FROM t1 RIGHT JOIN t2 ON true JOIN t3 ON a<>''; 506} {} 507do_execsql_test join8-16040 { 508 SELECT * FROM t1 RIGHT JOIN t2 ON true LEFT JOIN t3 ON a<>'' WHERE c<>''; 509} {} 510do_execsql_test join8-16050 { 511 SELECT * FROM t1 RIGHT JOIN t2 ON true LEFT JOIN t3 ON a<>'' WHERE c IS NOT NULL; 512} {} 513do_execsql_test join8-16060 { 514 SELECT * FROM t1 RIGHT JOIN t2 ON true JOIN t3 ON a<>'' WHERE c<>''; 515} {} 516do_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# 523reset_db 524do_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} {} 529db null NULL 530do_execsql_test join8-17010 { 531 SELECT * FROM t2 RIGHT JOIN t1 ON true; 532} {NULL 1 0 0} 533do_execsql_test join8-17020 { 534 SELECT 99=id AND 0=y AS "truth" FROM t2 RIGHT JOIN t1 ON true; 535} {0} 536do_execsql_test join8-17030 { 537 SELECT (99, 0)==(id, y) AS "truth" FROM t2 RIGHT JOIN t1; 538} {0} 539do_execsql_test join8-17040 { 540 SELECT * FROM t2 RIGHT JOIN t1 WHERE 99=id AND 0=y; 541} {} 542do_execsql_test join8-17041 { 543 SELECT * FROM t2 RIGHT JOIN t1 WHERE 99=+id AND 0=y; 544} {} 545do_execsql_test join8-17050 { 546 SELECT * FROM t2 RIGHT JOIN t1 WHERE (99, 0)==(id,y); 547} {} 548do_execsql_test join8-17051 { 549 SELECT * FROM t2 RIGHT JOIN t1 WHERE (99, 0)==(+id,y); 550} {} 551do_execsql_test join8-17060 { 552 SELECT * FROM t2 RIGHT JOIN t1 WHERE 1=id AND 0=y; 553} {NULL 1 0 0} 554do_execsql_test join8-17061 { 555 SELECT * FROM t2 RIGHT JOIN t1 WHERE 1=+id AND 0=y; 556} {NULL 1 0 0} 557do_execsql_test join8-17070 { 558 SELECT * FROM t2 RIGHT JOIN t1 WHERE (1, 0)==(id,y); 559} {NULL 1 0 0} 560do_execsql_test join8-17071 { 561 SELECT * FROM t2 RIGHT JOIN t1 WHERE (1, 0)==(+id,y); 562} {NULL 1 0 0} 563do_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} {} 568do_execsql_test join8-17090 { 569 SELECT t3.a FROM t4 RIGHT JOIN t3 ON (x=a) WHERE (b, 4)=(SELECT 3, 4); 570} {1} 571do_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# 579reset_db 580do_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} {} 585do_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 590do_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 596ifcapable 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 612reset_db 613do_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 625do_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 631reset_db 632do_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} {} 638db null - 639do_execsql_test join8-20010 { 640 SELECT * FROM t2 JOIN v0 ON z<>'bbb' RIGHT JOIN t1 ON z<>'ccc'; 641} {- - aaa} 642do_execsql_test join8-20020 { 643 SELECT * FROM t2 JOIN v0 ON z<>'bbb' RIGHT JOIN t1 ON z<>'ccc' ORDER BY z; 644} {- - aaa} 645do_execsql_test join8-20030 { 646 SELECT 99 as "m" FROM t2 JOIN v0 ON z<>'bbb' RIGHT JOIN t1 ON z<>'ccc'; 647} {99} 648do_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} 651do_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} 655do_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# 666reset_db 667do_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} 672do_execsql_test join8-21010 { 673 SELECT (b IS TRUE) FROM t1 JOIN t3 ON (b=TRUE) RIGHT JOIN t2 ON TRUE; 674} {0} 675do_execsql_test join8-22020 { 676 SELECT * FROM t1 JOIN t3 ON (b=TRUE) RIGHT JOIN t2 ON TRUE WHERE (b IS TRUE); 677} {} 678do_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} 688do_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}} 691do_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# 702reset_db 703do_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# 722reset_db 723db null - 724do_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} 734do_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} 741do_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# 757reset_db 758db null - 759do_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# 770reset_db 771do_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} 781db null - 782do_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} 785do_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# 792reset_db 793db null - 794do_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 804finish_test 805