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(x,y) VALUES(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 null=id AND false=y AS "truth" FROM t2 RIGHT JOIN t1 ON true; 535} {NULL} 536do_execsql_test join8-17030 { 537 SELECT (null, false)==(id, y) AS "truth" FROM t2 RIGHT JOIN t1; 538} {NULL} 539do_execsql_test join8-17040 { 540 SELECT * FROM t2 RIGHT JOIN t1 WHERE null=id AND false=y; 541} {} 542do_execsql_test join8-17050 { 543 SELECT * FROM t2 RIGHT JOIN t1 WHERE (null, false)==(id,y); 544} {} 545 546finish_test 547