1# 2022-04-09 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# This file implements regression tests for SQLite library. 12# 13# This file implements tests for RIGHT and FULL OUTER JOINs. 14 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17 18foreach {id schema} { 19 1 { 20 CREATE TABLE t1(a INT, b INT); 21 INSERT INTO t1 VALUES(1,2),(1,3),(1,4); 22 CREATE INDEX t1a ON t1(a); 23 CREATE TABLE t2(c INT, d INT); 24 INSERT INTO t2 VALUES(3,33),(4,44),(5,55); 25 CREATE INDEX t2c ON t2(c); 26 CREATE VIEW dual(dummy) AS VALUES('x'); 27 } 28 2 { 29 CREATE TABLE t1(a INT, b INT); 30 INSERT INTO t1 VALUES(1,2),(1,3),(1,4); 31 CREATE INDEX t1ab ON t1(a,b); 32 CREATE TABLE t2(c INT, d INT); 33 INSERT INTO t2 VALUES(3,33),(4,44),(5,55); 34 CREATE INDEX t2cd ON t2(c,d); 35 CREATE VIEW dual(dummy) AS VALUES('x'); 36 } 37 3 { 38 CREATE TABLE t1(a INT, b INT); 39 INSERT INTO t1 VALUES(1,2),(1,3),(1,4); 40 CREATE INDEX t1a ON t1(a); 41 CREATE TABLE t2(c INT, d INT PRIMARY KEY) WITHOUT ROWID; 42 INSERT INTO t2 VALUES(3,33),(4,44),(5,55); 43 CREATE INDEX t2c ON t2(c); 44 CREATE VIEW dual(dummy) AS VALUES('x'); 45 } 46 4 { 47 CREATE TABLE t1(a INT, b INT); 48 INSERT INTO t1 VALUES(1,2),(1,3),(1,4); 49 CREATE TABLE t2(c INTEGER PRIMARY KEY, d INT); 50 INSERT INTO t2 VALUES(3,33),(4,44),(5,55); 51 CREATE VIEW dual(dummy) AS VALUES('x'); 52 } 53 5 { 54 CREATE TABLE t1(a INT, b INT); 55 INSERT INTO t1 VALUES(1,2),(1,3),(1,4); 56 CREATE TABLE t2(c INT PRIMARY KEY, d INT) WITHOUT ROWID; 57 INSERT INTO t2 VALUES(3,33),(4,44),(5,55); 58 CREATE VIEW dual(dummy) AS VALUES('x'); 59 } 60 6 { 61 CREATE TABLE t1(a INT, b INT); 62 INSERT INTO t1 VALUES(1,2),(1,3),(1,4); 63 CREATE VIEW t2(c,d) AS VALUES(3,33),(4,44),(5,55); 64 CREATE VIEW dual(dummy) AS VALUES('x'); 65 } 66 7 { 67 CREATE VIEW t1(a,b) AS VALUES(1,2),(1,3),(1,4); 68 CREATE TABLE t2(c INTEGER PRIMARY KEY, d INT); 69 INSERT INTO t2 VALUES(3,33),(4,44),(5,55); 70 CREATE VIEW dual(dummy) AS VALUES('x'); 71 } 72 8 { 73 CREATE TABLE t1(a INT, b INT); 74 INSERT INTO t1 VALUES(1,2),(1,3),(1,4); 75 CREATE TABLE t2(c INT, d INT); 76 INSERT INTO t2 VALUES(3,33),(4,44),(5,55); 77 CREATE VIEW dual(dummy) AS VALUES('x'); 78 } 79 9 { 80 CREATE TABLE t1(a INT, b INT); 81 INSERT INTO t1 VALUES(1,2),(1,3),(1,4); 82 CREATE TABLE t2a(c INTEGER PRIMARY KEY, i1 INT); 83 CREATE TABLE t2b(i1 INTEGER PRIMARY KEY, d INT); 84 CREATE VIEW t2(c,d) AS SELECT c, d FROM t2a NATURAL JOIN t2b; 85 INSERT INTO t2a VALUES(3,93),(4,94),(5,95),(6,96),(7,97); 86 INSERT INTO t2b VALUES(91,11),(92,22),(93,33),(94,44),(95,55); 87 CREATE TABLE dual(dummy TEXT); 88 INSERT INTO dual(dummy) VALUES('x'); 89 } 90 10 { 91 CREATE TABLE t1(a INT, b INT, PRIMARY KEY(a,b)) WITHOUT ROWID; 92 INSERT INTO t1 VALUES(1,2),(1,3),(1,4); 93 CREATE TABLE t2a(c INTEGER PRIMARY KEY, i1 INT); 94 CREATE TABLE t2b(i1 INTEGER PRIMARY KEY, d INT); 95 CREATE VIEW t2(c,d) AS SELECT c, d FROM t2a NATURAL JOIN t2b; 96 INSERT INTO t2a VALUES(3,93),(4,94),(5,95),(6,96),(7,97); 97 INSERT INTO t2b VALUES(91,11),(92,22),(93,33),(94,44),(95,55); 98 CREATE TABLE dual(dummy TEXT); 99 INSERT INTO dual(dummy) VALUES('x'); 100 } 101} { 102 reset_db 103 db nullvalue NULL 104 do_execsql_test join7-$id.setup $schema {} 105 do_execsql_test join7-$id.10 { 106 SELECT b, d FROM t1 FULL OUTER JOIN t2 ON b=c ORDER BY +b; 107 } { 108 NULL 55 109 2 NULL 110 3 33 111 4 44 112 } 113 do_execsql_test join7-$id.20 { 114 SELECT a, c FROM t1 FULL OUTER JOIN t2 ON b=c ORDER BY +b; 115 } { 116 NULL 5 117 1 NULL 118 1 3 119 1 4 120 } 121 do_execsql_test join7-$id.30 { 122 SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c ORDER BY +b; 123 } { 124 NULL NULL 5 55 125 1 2 NULL NULL 126 1 3 3 33 127 1 4 4 44 128 } 129 do_execsql_test join7-$id.31 { 130 SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c ORDER BY +b; 131 } { 132 NULL NULL 5 55 133 1 2 NULL NULL 134 1 3 3 33 135 1 4 4 44 136 } 137 do_execsql_test join7-$id.40 { 138 SELECT * FROM t1 RIGHT OUTER JOIN t2 ON b=c ORDER BY +b; 139 } { 140 NULL NULL 5 55 141 1 3 3 33 142 1 4 4 44 143 } 144 do_execsql_test join7-$id.50 { 145 SELECT t1.*, t2.* FROM t2 LEFT OUTER JOIN t1 ON b=c ORDER BY +b; 146 } { 147 NULL NULL 5 55 148 1 3 3 33 149 1 4 4 44 150 } 151 do_execsql_test join7-$id.60 { 152 SELECT * FROM dual JOIN t1 ON true RIGHT OUTER JOIN t2 ON b=c ORDER BY +b; 153 } { 154 NULL NULL NULL 5 55 155 x 1 3 3 33 156 x 1 4 4 44 157 } 158 do_execsql_test join7-$id.70 { 159 SELECT t1.*, t2.* 160 FROM t2 LEFT JOIN (dual JOIN t1 ON true) ON b=c ORDER BY +b; 161 } { 162 NULL NULL 5 55 163 1 3 3 33 164 1 4 4 44 165 } 166 do_execsql_test join7-$id.80 { 167 SELECT * FROM dual CROSS JOIN t1 RIGHT OUTER JOIN t2 ON b=c ORDER BY +b; 168 } { 169 NULL NULL NULL 5 55 170 x 1 3 3 33 171 x 1 4 4 44 172 } 173 do_execsql_test join7-$id.81 { 174 SELECT dual.*, t1.*, t2.* 175 FROM t1 CROSS JOIN dual RIGHT OUTER JOIN t2 ON b=c ORDER BY +b; 176 } { 177 NULL NULL NULL 5 55 178 x 1 3 3 33 179 x 1 4 4 44 180 } 181 do_execsql_test join7-$id.90 { 182 SELECT * FROM t1 LEFT OUTER JOIN t2 ON b=c ORDER BY +b; 183 } { 184 1 2 NULL NULL 185 1 3 3 33 186 1 4 4 44 187 } 188 do_execsql_test join7-$id.100 { 189 SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c AND a=1 ORDER BY +b; 190 } { 191 NULL NULL 5 55 192 1 2 NULL NULL 193 1 3 3 33 194 1 4 4 44 195 } 196 do_execsql_test join7-$id.101 { 197 SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c AND a=1 ORDER BY +b; 198 } { 199 NULL NULL 5 55 200 1 2 NULL NULL 201 1 3 3 33 202 1 4 4 44 203 } 204 do_execsql_test join7-$id.110 { 205 SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c WHERE a=1 ORDER BY +b; 206 } { 207 1 2 NULL NULL 208 1 3 3 33 209 1 4 4 44 210 } 211 do_execsql_test join7-$id.120 { 212 SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c WHERE a IS NULL ORDER BY +d; 213 } { 214 NULL NULL 3 33 215 NULL NULL 4 44 216 NULL NULL 5 55 217 } 218 do_execsql_test join7-$id.130 { 219 SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c AND d<=0 ORDER BY +b, +d; 220 } { 221 NULL NULL 3 33 222 NULL NULL 4 44 223 NULL NULL 5 55 224 1 2 NULL NULL 225 1 3 NULL NULL 226 1 4 NULL NULL 227 } 228 do_execsql_test join7-$id.140 { 229 SELECT a, b, c, d 230 FROM t2 FULL OUTER JOIN t1 ON b=c AND d<=0 ORDER BY +b, +d; 231 } { 232 NULL NULL 3 33 233 NULL NULL 4 44 234 NULL NULL 5 55 235 1 2 NULL NULL 236 1 3 NULL NULL 237 1 4 NULL NULL 238 } 239} 240finish_test 241