1# 2022-04-16 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 t3(id INTEGER PRIMARY KEY, w TEXT); 21 CREATE TABLE t4(id INTEGER PRIMARY KEY, x TEXT); 22 CREATE TABLE t5(id INTEGER PRIMARY KEY, y TEXT); 23 CREATE TABLE t6(id INTEGER PRIMARY KEY, z INT); 24 CREATE VIEW dual(dummy) AS VALUES('x'); 25 INSERT INTO t3(id,w) VALUES(2,'two'),(3,'three'),(6,'six'),(7,'seven'); 26 INSERT INTO t4(id,x) VALUES(2,'alice'),(4,'bob'),(6,'cindy'),(8,'dave'); 27 INSERT INTO t5(id,y) VALUES(1,'red'),(2,'orange'),(3,'yellow'),(4,'green'), 28 (5,'blue'); 29 INSERT INTO t6(id,z) VALUES(3,333),(4,444),(5,555),(0,1000),(9,999); 30 } 31 2 { 32 CREATE TABLE t3(id INT PRIMARY KEY, w TEXT) WITHOUT ROWID; 33 CREATE TABLE t4(id INT PRIMARY KEY, x TEXT) WITHOUT ROWID; 34 CREATE TABLE t5(id INT PRIMARY KEY, y TEXT) WITHOUT ROWID; 35 CREATE TABLE t6(id INT PRIMARY KEY, z INT) WITHOUT ROWID; 36 CREATE TABLE dual(dummy TEXT); 37 INSERT INTO dual(dummy) VALUES('x'); 38 INSERT INTO t3(id,w) VALUES(2,'two'),(3,'three'),(6,'six'),(7,'seven'); 39 INSERT INTO t4(id,x) VALUES(2,'alice'),(4,'bob'),(6,'cindy'),(8,'dave'); 40 INSERT INTO t5(id,y) VALUES(1,'red'),(2,'orange'),(3,'yellow'),(4,'green'), 41 (5,'blue'); 42 INSERT INTO t6(id,z) VALUES(3,333),(4,444),(5,555),(0,1000),(9,999); 43 } 44 3 { 45 CREATE TABLE t3x(id INTEGER PRIMARY KEY, w TEXT); 46 CREATE TABLE t4x(id INTEGER PRIMARY KEY, x TEXT); 47 CREATE TABLE t5x(id INTEGER PRIMARY KEY, y TEXT); 48 CREATE TABLE t6x(id INTEGER PRIMARY KEY, z INT); 49 CREATE VIEW dual(dummy) AS VALUES('x'); 50 INSERT INTO t3x(id,w) VALUES(2,'two'),(3,'three'),(6,'six'),(7,'seven'); 51 INSERT INTO t4x(id,x) VALUES(2,'alice'),(4,'bob'),(6,'cindy'),(8,'dave'); 52 INSERT INTO t5x(id,y) VALUES(1,'red'),(2,'orange'),(3,'yellow'),(4,'green'), 53 (5,'blue'); 54 INSERT INTO t6x(id,z) VALUES(3,333),(4,444),(5,555),(0,1000),(9,999); 55 CREATE VIEW t3 AS SELECT * FROM t3x LIMIT 1000; 56 CREATE VIEW t4 AS SELECT * FROM t4x LIMIT 1000; 57 CREATE VIEW t5 AS SELECT * FROM t5x LIMIT 1000; 58 CREATE VIEW t6 AS SELECT * FROM t6x LIMIT 1000; 59 } 60 4 { 61 CREATE TABLE t3a(id INTEGER PRIMARY KEY, w TEXT); 62 CREATE TABLE t3b(id INTEGER PRIMARY KEY, w TEXT); 63 CREATE TABLE t4a(id INTEGER PRIMARY KEY, x TEXT); 64 CREATE TABLE t4b(id INTEGER PRIMARY KEY, x TEXT); 65 CREATE TABLE t5a(id INTEGER PRIMARY KEY, y TEXT); 66 CREATE TABLE t5b(id INTEGER PRIMARY KEY, y TEXT); 67 CREATE TABLE t6a(id INTEGER PRIMARY KEY, z INT); 68 CREATE TABLE t6b(id INTEGER PRIMARY KEY, z INT); 69 CREATE VIEW dual(dummy) AS VALUES('x'); 70 INSERT INTO t3a(id,w) VALUES(2,'two'),(3,'three'); 71 INSERT INTO t3b(id,w) VALUES(6,'six'),(7,'seven'); 72 INSERT INTO t4a(id,x) VALUES(2,'alice'),(4,'bob'); 73 INSERT INTO t4b(id,x) VALUES(6,'cindy'),(8,'dave'); 74 INSERT INTO t5a(id,y) VALUES(1,'red'),(2,'orange'),(3,'yellow'); 75 INSERT INTO t5b(id,y) VALUES(4,'green'),(5,'blue'); 76 INSERT INTO t6a(id,z) VALUES(3,333),(4,444); 77 INSERT INTO t6b(id,z) VALUES(5,555),(0,1000),(9,999); 78 CREATE VIEW t3 AS SELECT * FROM t3a UNION ALL SELECT * FROM t3b; 79 CREATE VIEW t4 AS SELECT * FROM t4a UNION ALL SELECT * FROM t4b; 80 CREATE VIEW t5 AS SELECT * FROM t5a UNION ALL SELECT * FROM t5b; 81 CREATE VIEW t6 AS SELECT * FROM t6a UNION ALL SELECT * FROM t6b; 82 } 83 5 { 84 CREATE TABLE t3a(id INTEGER PRIMARY KEY, w TEXT) WITHOUT ROWID; 85 CREATE TABLE t3b(id INTEGER PRIMARY KEY, w TEXT); 86 CREATE TABLE t4a(id INTEGER PRIMARY KEY, x TEXT) WITHOUT ROWID; 87 CREATE TABLE t4b(id INTEGER PRIMARY KEY, x TEXT) WITHOUT ROWID; 88 CREATE TABLE t5a(id INTEGER PRIMARY KEY, y TEXT); 89 CREATE TABLE t5b(id INTEGER PRIMARY KEY, y TEXT) WITHOUT ROWID; 90 CREATE TABLE t6a(id INTEGER PRIMARY KEY, z INT); 91 CREATE TABLE t6b(id INTEGER PRIMARY KEY, z INT); 92 CREATE VIEW dual(dummy) AS VALUES('x'); 93 INSERT INTO t3a(id,w) VALUES(2,'two'),(3,'three'); 94 INSERT INTO t3b(id,w) VALUES(6,'six'),(7,'seven'); 95 INSERT INTO t4a(id,x) VALUES(2,'alice'),(4,'bob'); 96 INSERT INTO t4b(id,x) VALUES(6,'cindy'),(8,'dave'); 97 INSERT INTO t5a(id,y) VALUES(1,'red'),(2,'orange'),(3,'yellow'); 98 INSERT INTO t5b(id,y) VALUES(4,'green'),(5,'blue'); 99 INSERT INTO t6a(id,z) VALUES(3,333),(4,444); 100 INSERT INTO t6b(id,z) VALUES(5,555),(0,1000),(9,999); 101 CREATE VIEW t3 AS SELECT * FROM t3a UNION ALL SELECT * FROM t3b; 102 CREATE VIEW t4 AS SELECT * FROM t4a UNION ALL SELECT * FROM t4b LIMIT 50; 103 CREATE VIEW t5 AS SELECT * FROM t5a UNION ALL SELECT * FROM t5b LIMIT 100; 104 CREATE VIEW t6 AS SELECT * FROM t6a UNION ALL SELECT * FROM t6b; 105 } 106} { 107 reset_db 108 db nullvalue - 109 do_execsql_test join9-$id.setup $schema {} 110 111 # Verifid by PG-14 for case 1 112 do_execsql_test join9-$id.100 { 113 SELECT *, t4.id, t5.id, t6.id 114 FROM t4 NATURAL LEFT JOIN t5 NATURAL LEFT JOIN t6 115 ORDER BY 1; 116 } { 117 2 alice orange - 2 2 - 118 4 bob green 444 4 4 4 119 6 cindy - - 6 - - 120 8 dave - - 8 - - 121 } 122 123 do_execsql_test join9-$id.101 { 124 SELECT *, t4.id, t5.id, t6.id 125 FROM t4 NATURAL LEFT JOIN t5 NATURAL LEFT JOIN t6 126 ORDER BY id; 127 } { 128 2 alice orange - 2 2 - 129 4 bob green 444 4 4 4 130 6 cindy - - 6 - - 131 8 dave - - 8 - - 132 } 133 do_execsql_test join9-$id.102 { 134 SELECT *, t4.id, t5.id, t6.id 135 FROM t4 LEFT JOIN t5 USING(id) LEFT JOIN t6 USING(id) 136 ORDER BY id; 137 } { 138 2 alice orange - 2 2 - 139 4 bob green 444 4 4 4 140 6 cindy - - 6 - - 141 8 dave - - 8 - - 142 } 143 144 # Verifid by PG-14 using case 1 145 do_execsql_test join9-$id.200 { 146 SELECT id, x, y, z, t4.id, t5.id, t6.id 147 FROM t5 NATURAL RIGHT JOIN t4 NATURAL LEFT JOIN t6 148 ORDER BY 1; 149 } { 150 2 alice orange - 2 2 - 151 4 bob green 444 4 4 4 152 6 cindy - - 6 - - 153 8 dave - - 8 - - 154 } 155 156 do_execsql_test join9-$id.201 { 157 SELECT id, x, y, z, t4.id, t5.id, t6.id 158 FROM t5 NATURAL RIGHT JOIN t4 NATURAL LEFT JOIN t6 159 ORDER BY id; 160 } { 161 2 alice orange - 2 2 - 162 4 bob green 444 4 4 4 163 6 cindy - - 6 - - 164 8 dave - - 8 - - 165 } 166 167 # Verified by PG-14 using case 1 168 do_execsql_test join9-$id.300 { 169 SELECT *, t4.id, t5.id, t6.id 170 FROM t4 NATURAL RIGHT JOIN t5 NATURAL RIGHT JOIN t6 171 ORDER BY 1; 172 } { 173 0 - - 1000 - - 0 174 3 - yellow 333 - 3 3 175 4 bob green 444 4 4 4 176 5 - blue 555 - 5 5 177 9 - - 999 - - 9 178 } 179 180 do_execsql_test join9-$id.301 { 181 SELECT *, t4.id, t5.id, t6.id 182 FROM t4 NATURAL RIGHT JOIN t5 NATURAL RIGHT JOIN t6 183 ORDER BY id; 184 } { 185 0 - - 1000 - - 0 186 3 - yellow 333 - 3 3 187 4 bob green 444 4 4 4 188 5 - blue 555 - 5 5 189 9 - - 999 - - 9 190 } 191 192 # Verified by PG-14 for case 1 193 do_execsql_test join9-$id.400 { 194 SELECT *, t4.id, t5.id, t6.id 195 FROM t4 NATURAL FULL JOIN t5 NATURAL FULL JOIN t6 196 ORDER BY 1; 197 } { 198 0 - - 1000 - - 0 199 1 - red - - 1 - 200 2 alice orange - 2 2 - 201 3 - yellow 333 - 3 3 202 4 bob green 444 4 4 4 203 5 - blue 555 - 5 5 204 6 cindy - - 6 - - 205 8 dave - - 8 - - 206 9 - - 999 - - 9 207 } 208 209 do_execsql_test join9-$id.401 { 210 SELECT *, t4.id, t5.id, t6.id 211 FROM t4 NATURAL FULL JOIN t5 NATURAL FULL JOIN t6 212 ORDER BY id; 213 } { 214 0 - - 1000 - - 0 215 1 - red - - 1 - 216 2 alice orange - 2 2 - 217 3 - yellow 333 - 3 3 218 4 bob green 444 4 4 4 219 5 - blue 555 - 5 5 220 6 cindy - - 6 - - 221 8 dave - - 8 - - 222 9 - - 999 - - 9 223 } 224 do_execsql_test join9-$id.402 { 225 SELECT id, x, y, z, t4.id, t5.id, t6.id 226 FROM t4 NATURAL FULL JOIN t6 NATURAL FULL JOIN t5 227 ORDER BY id; 228 } { 229 0 - - 1000 - - 0 230 1 - red - - 1 - 231 2 alice orange - 2 2 - 232 3 - yellow 333 - 3 3 233 4 bob green 444 4 4 4 234 5 - blue 555 - 5 5 235 6 cindy - - 6 - - 236 8 dave - - 8 - - 237 9 - - 999 - - 9 238 } 239 do_execsql_test join9-$id.403 { 240 SELECT id, x, y, z, t4.id, t5.id, t6.id 241 FROM t5 NATURAL FULL JOIN t4 NATURAL FULL JOIN t6 242 ORDER BY id; 243 } { 244 0 - - 1000 - - 0 245 1 - red - - 1 - 246 2 alice orange - 2 2 - 247 3 - yellow 333 - 3 3 248 4 bob green 444 4 4 4 249 5 - blue 555 - 5 5 250 6 cindy - - 6 - - 251 8 dave - - 8 - - 252 9 - - 999 - - 9 253 } 254 do_execsql_test join9-$id.404 { 255 SELECT id, x, y, z, t4.id, t5.id, t6.id 256 FROM t5 NATURAL FULL JOIN t6 NATURAL FULL JOIN t4 257 ORDER BY id; 258 } { 259 0 - - 1000 - - 0 260 1 - red - - 1 - 261 2 alice orange - 2 2 - 262 3 - yellow 333 - 3 3 263 4 bob green 444 4 4 4 264 5 - blue 555 - 5 5 265 6 cindy - - 6 - - 266 8 dave - - 8 - - 267 9 - - 999 - - 9 268 } 269 do_execsql_test join9-$id.405 { 270 SELECT id, x, y, z, t4.id, t5.id, t6.id 271 FROM t6 NATURAL FULL JOIN t4 NATURAL FULL JOIN t5 272 ORDER BY id; 273 } { 274 0 - - 1000 - - 0 275 1 - red - - 1 - 276 2 alice orange - 2 2 - 277 3 - yellow 333 - 3 3 278 4 bob green 444 4 4 4 279 5 - blue 555 - 5 5 280 6 cindy - - 6 - - 281 8 dave - - 8 - - 282 9 - - 999 - - 9 283 } 284 do_execsql_test join9-$id.406 { 285 SELECT id, x, y, z, t4.id, t5.id, t6.id 286 FROM t6 NATURAL FULL JOIN t5 NATURAL FULL JOIN t4 287 ORDER BY id; 288 } { 289 0 - - 1000 - - 0 290 1 - red - - 1 - 291 2 alice orange - 2 2 - 292 3 - yellow 333 - 3 3 293 4 bob green 444 4 4 4 294 5 - blue 555 - 5 5 295 6 cindy - - 6 - - 296 8 dave - - 8 - - 297 9 - - 999 - - 9 298 } 299 300 # Verified by PG-14 using case 1 301 do_execsql_test join9-$id.500 { 302 SELECT id, w, x, y, z 303 FROM t3 FULL JOIN t4 USING(id) 304 NATURAL FULL JOIN t5 305 FULL JOIN t6 USING(id) 306 ORDER BY 1; 307 } { 308 0 - - - 1000 309 1 - - red - 310 2 two alice orange - 311 3 three - yellow 333 312 4 - bob green 444 313 5 - - blue 555 314 6 six cindy - - 315 7 seven - - - 316 8 - dave - - 317 9 - - - 999 318 } 319 320 # Verified by PG-14 using case 1 321 do_execsql_test join9-$id.600 { 322 SELECT id, w, x, y, z 323 FROM t3 JOIN dual AS d1 ON true 324 FULL JOIN t4 USING(id) 325 JOIN dual AS d2 ON true 326 NATURAL FULL JOIN t5 327 JOIN dual AS d3 ON true 328 FULL JOIN t6 USING(id) 329 CROSS JOIN dual AS d4 330 ORDER BY 1; 331 } { 332 0 - - - 1000 333 1 - - red - 334 2 two alice orange - 335 3 three - yellow 333 336 4 - bob green 444 337 5 - - blue 555 338 6 six cindy - - 339 7 seven - - - 340 8 - dave - - 341 9 - - - 999 342 } 343 344 # Verified by PG-14 using case 1 345 do_execsql_test join9-$id.700 { 346 SELECT id, w, x, y, z 347 FROM t3 JOIN dual AS d1 ON true 348 FULL JOIN t4 USING(id) 349 JOIN dual AS d2 ON true 350 NATURAL FULL JOIN t5 351 JOIN dual AS d3 ON true 352 FULL JOIN t6 USING(id) 353 CROSS JOIN dual AS d4 354 WHERE x<>'bob' OR x IS NULL 355 ORDER BY 1; 356 } { 357 0 - - - 1000 358 1 - - red - 359 2 two alice orange - 360 3 three - yellow 333 361 5 - - blue 555 362 6 six cindy - - 363 7 seven - - - 364 8 - dave - - 365 9 - - - 999 366 } 367 368 # Verified by PG-14 using case 1 369 do_execsql_test join9-$id.800 { 370 WITH t7(id,a) AS MATERIALIZED (SELECT * FROM t4 WHERE false) 371 SELECT * 372 FROM t7 373 JOIN t7 AS t7b USING(id) 374 FULL JOIN t3 USING(id); 375 } { 376 2 - - two 377 3 - - three 378 6 - - six 379 7 - - seven 380 } 381} 382finish_test 383