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 23do_execsql_test join8-10 { 24 CREATE TABLE t1(a,b,c); 25 CREATE TABLE t2(x,y); 26 CREATE INDEX t2x ON t2(x); 27 SELECT avg(DISTINCT b) FROM (SELECT * FROM t2 LEFT RIGHT JOIN t1 ON c); 28} {NULL} 29 30# Pending optimization opportunity: 31# Row-value initialization subroutines must be called from with the 32# RIGHT JOIN body subroutine before the first use of any register containing 33# the results of that subroutine. This seems dodgy. Test case: 34# 35reset_db 36do_execsql_test join8-1000 { 37 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); 38 CREATE INDEX t1x1 ON t1(g+h,j,k); 39 CREATE INDEX t1x2 ON t1(b); 40 INSERT INTO t1 DEFAULT VALUES; 41} {} 42do_catchsql_test join8-1010 { 43 SELECT a 44 FROM ( 45 SELECT a 46 FROM ( 47 SELECT a 48 FROM ( 49 SELECT a FROM t1 NATURAL LEFT JOIN t1 50 WHERE (b, 2 ) IS ( SELECT 2 IN(2,2),2) 51 ) 52 NATURAL LEFT FULL JOIN t1 53 WHERE ( rowid , 1 )<=(CASE 5 WHEN 619 THEN 841 ELSE 3374391096 END,0) 54 ORDER BY a ASC 55 ) 56 NATURAL LEFT JOIN t1 57 WHERE (b, 2 ) IS ( SELECT 3 IN(3,3),3) 58 ) 59 NATURAL LEFT FULL JOIN t1 60 WHERE ( rowid , 1 )<=(CASE 5 WHEN 619 THEN 841 ELSE 3374391096 END,0) 61 ORDER BY a ASC; 62} {0 1} 63 64# Pending issue #2: (now resolved) 65# Jump to addrHalt inside the RIGHT JOIN body subroutine bypasses the 66# OP_Return, resulting in a subroutine loop. Test case: 67# 68reset_db 69do_execsql_test join8-2000 { 70 CREATE TABLE t1(a int, b int, c int); 71 INSERT INTO t1 VALUES(1,2,3),(4,5,6); 72 CREATE TABLE t2(d int, e int); 73 INSERT INTO t2 VALUES(3,333),(4,444); 74 CREATE TABLE t3(f int, g int); 75 PRAGMA automatic_index=off; 76} {} 77do_catchsql_test join8-2010 { 78 SELECT * FROM t1 RIGHT JOIN t2 ON c=d JOIN t3 ON f=e; 79} {0 {}} 80 81# Demonstrate that nested FULL JOINs and USING clauses work 82# 83reset_db 84load_static_extension db series 85do_execsql_test join8-3000 { 86 CREATE TABLE t1(id INTEGER PRIMARY KEY, a INT); 87 CREATE TABLE t2(id INTEGER PRIMARY KEY, b INT); 88 CREATE TABLE t3(id INTEGER PRIMARY KEY, c INT); 89 CREATE TABLE t4(id INTEGER PRIMARY KEY, d INT); 90 CREATE TABLE t5(id INTEGER PRIMARY KEY, e INT); 91 CREATE TABLE t6(id INTEGER PRIMARY KEY, f INT); 92 CREATE TABLE t7(id INTEGER PRIMARY KEY, g INT); 93 CREATE TABLE t8(id INTEGER PRIMARY KEY, h INT); 94 INSERT INTO t1 SELECT value, 1 FROM generate_series(1,256) WHERE value & 1; 95 INSERT INTO t2 SELECT value, 1 FROM generate_series(1,256) WHERE value & 2; 96 INSERT INTO t3 SELECT value, 1 FROM generate_series(1,256) WHERE value & 4; 97 INSERT INTO t4 SELECT value, 1 FROM generate_series(1,256) WHERE value & 8; 98 INSERT INTO t5 SELECT value, 1 FROM generate_series(1,256) WHERE value & 16; 99 INSERT INTO t6 SELECT value, 1 FROM generate_series(1,256) WHERE value & 32; 100 INSERT INTO t7 SELECT value, 1 FROM generate_series(1,256) WHERE value & 64; 101 INSERT INTO t8 SELECT value, 1 FROM generate_series(1,256) WHERE value & 128; 102 CREATE TABLE t9 AS 103 SELECT id, h, g, f, e, d, c, b, a 104 FROM t1 105 NATURAL FULL JOIN t2 106 NATURAL FULL JOIN t3 107 NATURAL FULL JOIN t4 108 NATURAL FULL JOIN t5 109 NATURAL FULL JOIN t6 110 NATURAL FULL JOIN t7 111 NATURAL FULL JOIN t8; 112} {} 113do_execsql_test join8-3010 { 114 SELECT count(*) FROM t9; 115} {255} 116do_execsql_test join8-3020 { 117 SELECT id, count(*) FROM t9 GROUP BY id HAVING count(*)!=1; 118} {} 119do_execsql_test join8-3030 { 120 UPDATE t9 SET a=0 WHERE a IS NULL; 121 UPDATE t9 SET b=0 WHERE b IS NULL; 122 UPDATE t9 SET c=0 WHERE c IS NULL; 123 UPDATE t9 SET d=0 WHERE d IS NULL; 124 UPDATE t9 SET e=0 WHERE e IS NULL; 125 UPDATE t9 SET f=0 WHERE f IS NULL; 126 UPDATE t9 SET g=0 WHERE g IS NULL; 127 UPDATE t9 SET h=0 WHERE h IS NULL; 128 SELECT count(*) FROM t9 WHERE id=128*h+64*g+32*f+16*e+8*d+4*c+2*b+a; 129} {255} 130do_execsql_test join8-3040 { 131 SELECT * FROM t9 WHERE id<>128*h+64*g+32*f+16*e+8*d+4*c+2*b+a; 132} {} 133 134# 2022-04-21 dbsqlfuzz find 135# 136reset_db 137do_execsql_test join8-4000 { 138 CREATE TABLE t1(x INTEGER PRIMARY KEY, a, b); 139 INSERT INTO t1 VALUES(1,5555,4); 140 CREATE INDEX i1a ON t1(a); 141 CREATE INDEX i1b ON t1(b); 142 SELECT a FROM t1 NATURAL RIGHT JOIN t1 WHERE a=5555 OR (1,b)==(SELECT 2 IN (2,2),4); 143} {5555} 144 145# 2022-04-23 dbsqlfuzz c7ee5500e3abddec3557016de777713b80c790d3 146# Escape from the right-join body subroutine via the ORDER BY LIMIT optimization. 147# 148reset_db 149db null - 150do_catchsql_test join8-5000 { 151 CREATE TABLE t1(x); 152 INSERT INTO t1(x) VALUES(NULL),(NULL); 153 CREATE TABLE t2(c, d); 154 INSERT INTO t2(c,d) SELECT x, x FROM t1; 155 CREATE INDEX t2dc ON t2(d, c); 156 SELECT (SELECT c FROM sqlite_temp_schema FULL JOIN t2 ON d IN (1,2,3) ORDER BY d) AS x FROM t1; 157} {0 {- -}} 158 159finish_test 160