AryaWu/sqlite
0
1# 2022-04-162#3# The author disclaims copyright to this source code. In place of4# 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.tcl17 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_db108 db nullvalue -109 do_execsql_test join9-$id.setup $schema {}110 111 # Verifid by PG-14 for case 1112 do_execsql_test join9-$id.100 {113 SELECT *, t4.id, t5.id, t6.id114 FROM t4 NATURAL LEFT JOIN t5 NATURAL LEFT JOIN t6115 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.id125 FROM t4 NATURAL LEFT JOIN t5 NATURAL LEFT JOIN t6126 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.id135 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 1145 do_execsql_test join9-$id.200 {146 SELECT id, x, y, z, t4.id, t5.id, t6.id147 FROM t5 NATURAL RIGHT JOIN t4 NATURAL LEFT JOIN t6148 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.id158 FROM t5 NATURAL RIGHT JOIN t4 NATURAL LEFT JOIN t6159 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 1168 do_execsql_test join9-$id.300 {169 SELECT *, t4.id, t5.id, t6.id170 FROM t4 NATURAL RIGHT JOIN t5 NATURAL RIGHT JOIN t6171 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.id182 FROM t4 NATURAL RIGHT JOIN t5 NATURAL RIGHT JOIN t6183 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 1193 do_execsql_test join9-$id.400 {194 SELECT *, t4.id, t5.id, t6.id195 FROM t4 NATURAL FULL JOIN t5 NATURAL FULL JOIN t6196 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.id211 FROM t4 NATURAL FULL JOIN t5 NATURAL FULL JOIN t6212 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.id226 FROM t4 NATURAL FULL JOIN t6 NATURAL FULL JOIN t5227 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.id241 FROM t5 NATURAL FULL JOIN t4 NATURAL FULL JOIN t6242 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.id256 FROM t5 NATURAL FULL JOIN t6 NATURAL FULL JOIN t4257 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.id271 FROM t6 NATURAL FULL JOIN t4 NATURAL FULL JOIN t5272 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.id286 FROM t6 NATURAL FULL JOIN t5 NATURAL FULL JOIN t4287 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 1301 do_execsql_test join9-$id.500 {302 SELECT id, w, x, y, z303 FROM t3 FULL JOIN t4 USING(id)304 NATURAL FULL JOIN t5305 FULL JOIN t6 USING(id)306 ORDER BY 1;307 } {308 0 - - - 1000309 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 1321 do_execsql_test join9-$id.600 {322 SELECT id, w, x, y, z323 FROM t3 JOIN dual AS d1 ON true324 FULL JOIN t4 USING(id)325 JOIN dual AS d2 ON true326 NATURAL FULL JOIN t5327 JOIN dual AS d3 ON true328 FULL JOIN t6 USING(id)329 CROSS JOIN dual AS d4330 ORDER BY 1;331 } {332 0 - - - 1000333 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 1345 do_execsql_test join9-$id.700 {346 SELECT id, w, x, y, z347 FROM t3 JOIN dual AS d1 ON true348 FULL JOIN t4 USING(id)349 JOIN dual AS d2 ON true350 NATURAL FULL JOIN t5351 JOIN dual AS d3 ON true352 FULL JOIN t6 USING(id)353 CROSS JOIN dual AS d4354 WHERE x<>'bob' OR x IS NULL355 ORDER BY 1;356 } {357 0 - - - 1000358 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 1369 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 - - three378 6 - - six 379 7 - - seven380 }381 382 # Verified by PG-14383 do_execsql_test join9-$id.900 {384 SELECT *385 FROM (t3 NATURAL FULL JOIN t4)386 NATURAL FULL JOIN387 (t5 NATURAL FULL JOIN t6)388 ORDER BY 1;389 } {390 0 - - - 1000391 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 JOIN405 (t5 NATURAL FULL JOIN t6))406 ORDER BY 1;407 } {408 0 - - - 1000409 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 - - - 1000429 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 - - - 1000449 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-14461 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.id468 ORDER BY coalesce(t3.id,j1.id);469 } {470 - - 0 - - 1000471 - - 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-14483 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.id490 ORDER BY coalesce(t3.id,j1.id);491 } {492 - - 0 - - 1000493 - - 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-14504 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.id511 ORDER BY coalesce(t3.id,j1.id);512 } {513 2 two 2 alice orange - 514 3 three - - - - 515 - - 4 bob green 444516 6 six 6 cindy - - 517 7 seven - - - - 518 - - 8 dave - - 519 }520 521 # Restriction (27) in the query flattener522 # Verified by PG-14523 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 444530 }531 532 # Verified by PG-14533 do_execsql_test join9-$id.1010 {534 SELECT id,x,y,z535 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 444540 }541 542 # Verified by PG-14543 do_execsql_test join9-$id.1020 {544 SELECT id,x,y,z545 FROM t4 FULL JOIN t5 USING(id) INNER JOIN t6 USING(id)546 ORDER BY 1;547 } {548 3 - yellow 333549 4 bob green 444550 5 - blue 555551 }552 553 # Verified by PG-14554 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 333560 4 bob green 444561 5 - blue 555562 }563 564}565finish_test566