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 382 # Verified by PG-14 383 do_execsql_test join9-$id.900 { 384 SELECT * 385 FROM (t3 NATURAL FULL JOIN t4) 386 NATURAL FULL JOIN 387 (t5 NATURAL FULL JOIN t6) 388 ORDER BY 1; 389 } { 390 0 - - - 1000 391 1 - - red - 392 2 two alice orange - 393 3 three - yellow 333 394 4 - bob green 444 395 5 - - blue 555 396 6 six cindy - - 397 7 seven - - - 398 8 - dave - - 399 9 - - - 999 400 } 401 do_execsql_test join9-$id.910 { 402 SELECT * 403 FROM t3 NATURAL FULL JOIN 404 (t4 NATURAL FULL JOIN 405 (t5 NATURAL FULL JOIN t6)) 406 ORDER BY 1; 407 } { 408 0 - - - 1000 409 1 - - red - 410 2 two alice orange - 411 3 three - yellow 333 412 4 - bob green 444 413 5 - - blue 555 414 6 six cindy - - 415 7 seven - - - 416 8 - dave - - 417 9 - - - 999 418 } 419 do_execsql_test join9-$id.920 { 420 SELECT * 421 FROM t3 FULL JOIN ( 422 t4 FULL JOIN ( 423 t5 FULL JOIN t6 USING (id) 424 ) USING(id) 425 ) USING(id) 426 ORDER BY 1; 427 } { 428 0 - - - 1000 429 1 - - red - 430 2 two alice orange - 431 3 three - yellow 333 432 4 - bob green 444 433 5 - - blue 555 434 6 six cindy - - 435 7 seven - - - 436 8 - dave - - 437 9 - - - 999 438 } 439 do_execsql_test join9-$id.920 { 440 SELECT * 441 FROM t3 FULL JOIN ( 442 t4 FULL JOIN ( 443 t5 FULL JOIN t6 USING (id) 444 ) USING(id) 445 ) USING(id) 446 ORDER BY 1; 447 } { 448 0 - - - 1000 449 1 - - red - 450 2 two alice orange - 451 3 three - yellow 333 452 4 - bob green 444 453 5 - - blue 555 454 6 six cindy - - 455 7 seven - - - 456 8 - dave - - 457 9 - - - 999 458 } 459 460 # Verified by PG-14 461 do_execsql_test join9-$id.930 { 462 SELECT * 463 FROM t3 FULL JOIN ( 464 t4 FULL JOIN ( 465 t5 FULL JOIN t6 USING(id) 466 ) USING(id) 467 ) AS j1 ON j1.id=t3.id 468 ORDER BY coalesce(t3.id,j1.id); 469 } { 470 - - 0 - - 1000 471 - - 1 - red - 472 2 two 2 alice orange - 473 3 three 3 - yellow 333 474 - - 4 bob green 444 475 - - 5 - blue 555 476 6 six 6 cindy - - 477 7 seven - - - - 478 - - 8 dave - - 479 - - 9 - - 999 480 } 481 482 # Verified by PG-14 483 do_execsql_test join9-$id.940 { 484 SELECT * 485 FROM t3 FULL JOIN ( 486 t4 RIGHT JOIN ( 487 t5 FULL JOIN t6 USING(id) 488 ) USING(id) 489 ) AS j1 ON j1.id=t3.id 490 ORDER BY coalesce(t3.id,j1.id); 491 } { 492 - - 0 - - 1000 493 - - 1 - red - 494 2 two 2 alice orange - 495 3 three 3 - yellow 333 496 - - 4 bob green 444 497 - - 5 - blue 555 498 6 six - - - - 499 7 seven - - - - 500 - - 9 - - 999 501 } 502 503 # Verified by PG-14 504 do_execsql_test join9-$id.950 { 505 SELECT * 506 FROM t3 FULL JOIN ( 507 t4 LEFT JOIN ( 508 t5 FULL JOIN t6 USING(id) 509 ) USING(id) 510 ) AS j1 ON j1.id=t3.id 511 ORDER BY coalesce(t3.id,j1.id); 512 } { 513 2 two 2 alice orange - 514 3 three - - - - 515 - - 4 bob green 444 516 6 six 6 cindy - - 517 7 seven - - - - 518 - - 8 dave - - 519 } 520 521 # Restriction (27) in the query flattener 522 # Verified by PG-14 523 do_execsql_test join9-$id.1000 { 524 WITH t56(id,y,z) AS (SELECT * FROM t5 FULL JOIN t6 USING(id) LIMIT 50) 525 SELECT id,x,y,z FROM t4 JOIN t56 USING(id) 526 ORDER BY 1; 527 } { 528 2 alice orange - 529 4 bob green 444 530 } 531 532 # Verified by PG-14 533 do_execsql_test join9-$id.1010 { 534 SELECT id,x,y,z 535 FROM t4 INNER JOIN (t5 FULL JOIN t6 USING(id)) USING(id) 536 ORDER BY 1; 537 } { 538 2 alice orange - 539 4 bob green 444 540 } 541 542 # Verified by PG-14 543 do_execsql_test join9-$id.1020 { 544 SELECT id,x,y,z 545 FROM t4 FULL JOIN t5 USING(id) INNER JOIN t6 USING(id) 546 ORDER BY 1; 547 } { 548 3 - yellow 333 549 4 bob green 444 550 5 - blue 555 551 } 552 553 # Verified by PG-14 554 do_execsql_test join9-$id.1030 { 555 WITH t45(id,x,y) AS (SELECT * FROM t4 FULL JOIN t5 USING(id) LIMIT 50) 556 SELECT id,x,y,z FROM t45 JOIN t6 USING(id) 557 ORDER BY 1; 558 } { 559 3 - yellow 333 560 4 bob green 444 561 5 - blue 555 562 } 563 564} 565finish_test 566