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 106 # Verified against PG-14 for case 1 107 do_execsql_test join7-$id.10 { 108 SELECT b, d FROM t1 FULL OUTER JOIN t2 ON b=c ORDER BY +b; 109 } { 110 NULL 55 111 2 NULL 112 3 33 113 4 44 114 } 115 116 # Verified against PG-14 for case 1 117 do_execsql_test join7-$id.20 { 118 SELECT a, c FROM t1 FULL OUTER JOIN t2 ON b=c ORDER BY +b; 119 } { 120 NULL 5 121 1 NULL 122 1 3 123 1 4 124 } 125 126 do_execsql_test join7-$id.30 { 127 SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c ORDER BY +b; 128 } { 129 NULL NULL 5 55 130 1 2 NULL NULL 131 1 3 3 33 132 1 4 4 44 133 } 134 do_execsql_test join7-$id.31 { 135 SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c ORDER BY +b; 136 } { 137 NULL NULL 5 55 138 1 2 NULL NULL 139 1 3 3 33 140 1 4 4 44 141 } 142 do_execsql_test join7-$id.40 { 143 SELECT * FROM t1 RIGHT OUTER JOIN t2 ON b=c ORDER BY +b; 144 } { 145 NULL NULL 5 55 146 1 3 3 33 147 1 4 4 44 148 } 149 do_execsql_test join7-$id.50 { 150 SELECT t1.*, t2.* FROM t2 LEFT OUTER JOIN t1 ON b=c ORDER BY +b; 151 } { 152 NULL NULL 5 55 153 1 3 3 33 154 1 4 4 44 155 } 156 do_execsql_test join7-$id.60 { 157 SELECT * FROM dual JOIN t1 ON true RIGHT OUTER JOIN t2 ON b=c ORDER BY +b; 158 } { 159 NULL NULL NULL 5 55 160 x 1 3 3 33 161 x 1 4 4 44 162 } 163 do_execsql_test join7-$id.70 { 164 SELECT t1.*, t2.* 165 FROM t2 LEFT JOIN (dual JOIN t1 ON true) ON b=c ORDER BY +b; 166 } { 167 NULL NULL 5 55 168 1 3 3 33 169 1 4 4 44 170 } 171 do_execsql_test join7-$id.80 { 172 SELECT * FROM dual CROSS JOIN t1 RIGHT OUTER JOIN t2 ON b=c ORDER BY +b; 173 } { 174 NULL NULL NULL 5 55 175 x 1 3 3 33 176 x 1 4 4 44 177 } 178 do_execsql_test join7-$id.81 { 179 SELECT dual.*, t1.*, t2.* 180 FROM t1 CROSS JOIN dual RIGHT OUTER JOIN t2 ON b=c ORDER BY +b; 181 } { 182 NULL NULL NULL 5 55 183 x 1 3 3 33 184 x 1 4 4 44 185 } 186 do_execsql_test join7-$id.90 { 187 SELECT * FROM t1 LEFT OUTER JOIN t2 ON b=c ORDER BY +b; 188 } { 189 1 2 NULL NULL 190 1 3 3 33 191 1 4 4 44 192 } 193 do_execsql_test join7-$id.100 { 194 SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c AND a=1 ORDER BY +b; 195 } { 196 NULL NULL 5 55 197 1 2 NULL NULL 198 1 3 3 33 199 1 4 4 44 200 } 201 do_execsql_test join7-$id.101 { 202 SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c AND a=1 ORDER BY +b; 203 } { 204 NULL NULL 5 55 205 1 2 NULL NULL 206 1 3 3 33 207 1 4 4 44 208 } 209 210 # Verified against PG-14 for case 1 211 do_execsql_test join7-$id.110 { 212 SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c WHERE a=1 ORDER BY +b; 213 } { 214 1 2 NULL NULL 215 1 3 3 33 216 1 4 4 44 217 } 218 219 do_execsql_test join7-$id.111 { 220 SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c WHERE a=1 ORDER BY +b; 221 } { 222 1 2 NULL NULL 223 1 3 3 33 224 1 4 4 44 225 } 226 227 # Verified against PG-14 for case 1 228 do_execsql_test join7-$id.115 { 229 SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c 230 WHERE a=1 OR a IS NULL ORDER BY +b; 231 } { 232 NULL NULL 5 55 233 1 2 NULL NULL 234 1 3 3 33 235 1 4 4 44 236 } 237 238 do_execsql_test join7-$id.116 { 239 SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c 240 WHERE a=1 OR a IS NULL ORDER BY +b; 241 } { 242 NULL NULL 5 55 243 1 2 NULL NULL 244 1 3 3 33 245 1 4 4 44 246 } 247 248 # Verified against PG-14 for case 1: 249 do_execsql_test join7-$id.120 { 250 SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c WHERE a IS NULL ORDER BY +d; 251 } { 252 NULL NULL 5 55 253 } 254 255 # Verified against PG-14 for case 1: 256 do_execsql_test join7-$id.130 { 257 SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c AND d<=0 ORDER BY +b, +d; 258 } { 259 NULL NULL 3 33 260 NULL NULL 4 44 261 NULL NULL 5 55 262 1 2 NULL NULL 263 1 3 NULL NULL 264 1 4 NULL NULL 265 } 266 267 # Verified against PG-14 for case 1: 268 do_execsql_test join7-$id.140 { 269 SELECT a, b, c, d 270 FROM t2 FULL OUTER JOIN t1 ON b=c AND d<=0 ORDER BY +b, +d; 271 } { 272 NULL NULL 3 33 273 NULL NULL 4 44 274 NULL NULL 5 55 275 1 2 NULL NULL 276 1 3 NULL NULL 277 1 4 NULL NULL 278 } 279 280 do_execsql_test join7-$id.141 { 281 SELECT a, b, c, d 282 FROM t2 FULL OUTER JOIN t1 ON b=c AND d<=0 283 ORDER BY +b, +d LIMIT 2 OFFSET 2 284 } { 285 NULL NULL 5 55 286 1 2 NULL NULL 287 } 288} 289finish_test 290