AryaWu/sqlite
0
1set testdir [file dirname $argv0]2# 2022-04-193#4# The author disclaims copyright to this source code. In place of5# a legal notice, here is a blessing:6#7# May you do good and not evil.8# May you find forgiveness for yourself and forgive others.9# May you share freely, never taking more than you give.10#11#***********************************************************************12#13# This file implements tests for JOINs.14#15# The test case output is all generated by PostgreSQL 14. This test module16# was created as follows:17#18# 1. Run a TCL script (included at the bottom of this file) that19# generates an input script for "psql" that will run man20# diverse tests on joins.21#22# 2. Run the script from step (1) through psql and collect the23# output.24#25# 3. Make a few minor global search-and-replace operations to convert26# the psql output into a form suitable for this test module.27#28# 4. Add this header, and the script content at the footer.29#30source $testdir/tester.tcl31db nullvalue -32db eval {33 DROP TABLE IF EXISTS t1;34 DROP TABLE IF EXISTS t2;35 DROP TABLE IF EXISTS t3;36 DROP TABLE IF EXISTS t4;37 DROP TABLE IF EXISTS t5;38 CREATE TABLE t1(a INT, b INT, c INT);39 CREATE TABLE t2(a INT, b INT, d INT);40 CREATE TABLE t3(a INT, b INT, e INT);41 CREATE TABLE t4(a INT, b INT, f INT);42 CREATE TABLE t5(a INT, b INT, g INT);43 INSERT INTO t1 VALUES(11,21,31),(12,22,32),(15,25,35),(17,27,37);44 INSERT INTO t2 VALUES(12,22,32),(13,23,33),(15,25,35),(18,28,38),45 (NULL,NULL,36);46 INSERT INTO t4 VALUES(11,21,31),(13,23,33),(15,25,35),(19,29,39);47 INSERT INTO t3 SELECT * FROM t1 UNION SELECT * FROM t2 UNION SELECT * FROM t4;48 INSERT INTO t5 SELECT * FROM t3 WHERE a>=15;49}50do_execsql_test joinB-1 {51 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a52 FROM t153 INNER JOIN t2 USING(a)54 INNER JOIN t3 USING(a)55 INNER JOIN t4 USING(a)56 INNER JOIN t5 USING(a)57 ORDER BY 1 NULLS FIRST;58} {59 15 15 15 15 15 1560}61do_execsql_test joinB-2 {62 SELECT a, c, d, e, f, g63 FROM t164 INNER JOIN t2 USING(a,b)65 INNER JOIN t3 USING(a,b)66 INNER JOIN t4 USING(a,b)67 INNER JOIN t5 USING(a,b)68 WHERE a<>1369 ORDER BY 1 NULLS FIRST;70} {71 15 35 35 35 35 3572}73do_execsql_test joinB-3 {74 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a75 FROM t176 INNER JOIN t2 USING(a)77 INNER JOIN t3 USING(a)78 INNER JOIN t4 USING(a)79 LEFT JOIN t5 USING(a)80 ORDER BY 1 NULLS FIRST;81} {82 15 15 15 15 15 1583}84do_execsql_test joinB-4 {85 SELECT a, c, d, e, f, g86 FROM t187 INNER JOIN (t2 INNER JOIN t3 USING(a)) USING(a)88 INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a)89 WHERE a<=1890 ORDER BY 1 NULLS FIRST;91} {92 15 35 35 35 35 3593}94do_execsql_test joinB-5 {95 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a96 FROM t197 INNER JOIN t2 USING(a)98 INNER JOIN t3 USING(a)99 INNER JOIN t4 USING(a)100 RIGHT JOIN t5 USING(a)101 ORDER BY 1 NULLS FIRST;102} {103 15 15 15 15 15 15104 17 - - - - 17105 18 - - - - 18106 19 - - - - 19107}108do_execsql_test joinB-6 {109 SELECT a, b, c, d, e, f, g110 FROM t1111 INNER JOIN t2 USING(a,b)112 INNER JOIN t3 USING(a,b)113 INNER JOIN t4 USING(a,b)114 RIGHT JOIN t5 USING(a,b)115 WHERE d<>33 OR d IS NULL116 ORDER BY 1 NULLS FIRST;117} {118 15 25 35 35 35 35 35119 17 27 - - - - 37120 18 28 - - - - 38121 19 29 - - - - 39122}123do_execsql_test joinB-7 {124 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a125 FROM t1126 INNER JOIN t2 USING(a)127 INNER JOIN t3 USING(a)128 INNER JOIN t4 USING(a)129 FULL JOIN t5 USING(a)130 ORDER BY 1 NULLS FIRST;131} {132 15 15 15 15 15 15133 17 - - - - 17134 18 - - - - 18135 19 - - - - 19136}137do_execsql_test joinB-8 {138 SELECT b, c, d, e, f, g139 FROM t1140 NATURAL INNER JOIN t2141 NATURAL INNER JOIN t3142 NATURAL INNER JOIN t4143 NATURAL FULL JOIN t5144 WHERE b BETWEEN 12 AND 17145 ORDER BY 1 NULLS FIRST;146} {147}148do_execsql_test joinB-9 {149 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a150 FROM t1151 INNER JOIN t2 USING(a)152 INNER JOIN t3 USING(a)153 LEFT JOIN t4 USING(a)154 INNER JOIN t5 USING(a)155 ORDER BY 1 NULLS FIRST;156} {157 15 15 15 15 15 15158}159do_execsql_test joinB-10 {160 SELECT a, c, d, e, f, g161 FROM t1162 INNER JOIN t2 USING(a,b)163 INNER JOIN t3 USING(a,b)164 LEFT JOIN t4 USING(a,b)165 INNER JOIN t5 USING(a,b)166 WHERE a<>13167 ORDER BY 1 NULLS FIRST;168} {169 15 35 35 35 35 35170}171do_execsql_test joinB-11 {172 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a173 FROM t1174 INNER JOIN t2 USING(a)175 INNER JOIN t3 USING(a)176 LEFT JOIN t4 USING(a)177 LEFT JOIN t5 USING(a)178 ORDER BY 1 NULLS FIRST;179} {180 12 12 12 12 - -181 15 15 15 15 15 15182}183do_execsql_test joinB-12 {184 SELECT a, c, d, e, f, g185 FROM t1186 INNER JOIN (t2 INNER JOIN t3 USING(a)) USING(a)187 LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a)188 WHERE a<=18189 ORDER BY 1 NULLS FIRST;190} {191 12 32 32 32 - -192 15 35 35 35 35 35193}194do_execsql_test joinB-13 {195 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a196 FROM t1197 INNER JOIN t2 USING(a)198 INNER JOIN t3 USING(a)199 LEFT JOIN t4 USING(a)200 RIGHT JOIN t5 USING(a)201 ORDER BY 1 NULLS FIRST;202} {203 15 15 15 15 15 15204 17 - - - - 17205 18 - - - - 18206 19 - - - - 19207}208do_execsql_test joinB-14 {209 SELECT a, b, c, d, e, f, g210 FROM t1211 INNER JOIN t2 USING(a,b)212 INNER JOIN t3 USING(a,b)213 LEFT JOIN t4 USING(a,b)214 RIGHT JOIN t5 USING(a,b)215 WHERE d<>33 OR d IS NULL216 ORDER BY 1 NULLS FIRST;217} {218 15 25 35 35 35 35 35219 17 27 - - - - 37220 18 28 - - - - 38221 19 29 - - - - 39222}223do_execsql_test joinB-15 {224 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a225 FROM t1226 INNER JOIN t2 USING(a)227 INNER JOIN t3 USING(a)228 LEFT JOIN t4 USING(a)229 FULL JOIN t5 USING(a)230 ORDER BY 1 NULLS FIRST;231} {232 12 12 12 12 - -233 15 15 15 15 15 15234 17 - - - - 17235 18 - - - - 18236 19 - - - - 19237}238do_execsql_test joinB-16 {239 SELECT b, c, d, e, f, g240 FROM t1241 NATURAL INNER JOIN t2242 NATURAL INNER JOIN t3243 NATURAL LEFT JOIN t4244 NATURAL FULL JOIN t5245 WHERE b BETWEEN 12 AND 17246 ORDER BY 1 NULLS FIRST;247} {248}249do_execsql_test joinB-17 {250 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a251 FROM t1252 INNER JOIN t2 USING(a)253 INNER JOIN t3 USING(a)254 RIGHT JOIN t4 USING(a)255 INNER JOIN t5 USING(a)256 ORDER BY 1 NULLS FIRST;257} {258 15 15 15 15 15 15259 19 - - - 19 19260}261do_execsql_test joinB-18 {262 SELECT a, c, d, e, f, g263 FROM t1264 INNER JOIN t2 USING(a,b)265 INNER JOIN t3 USING(a,b)266 RIGHT JOIN t4 USING(a,b)267 INNER JOIN t5 USING(a,b)268 WHERE a<>13269 ORDER BY 1 NULLS FIRST;270} {271 15 35 35 35 35 35272 19 - - - 39 39273}274do_execsql_test joinB-19 {275 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a276 FROM t1277 INNER JOIN t2 USING(a)278 INNER JOIN t3 USING(a)279 RIGHT JOIN t4 USING(a)280 LEFT JOIN t5 USING(a)281 ORDER BY 1 NULLS FIRST;282} {283 11 - - - 11 -284 13 - - - 13 -285 15 15 15 15 15 15286 19 - - - 19 19287}288do_execsql_test joinB-20 {289 SELECT a, c, d, e, f, g290 FROM t1291 INNER JOIN (t2 INNER JOIN t3 USING(a)) USING(a)292 RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a)293 WHERE a<=18294 ORDER BY 1 NULLS FIRST;295} {296 11 - - - 31 -297 13 - - - 33 -298 15 35 35 35 35 35299}300do_execsql_test joinB-21 {301 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a302 FROM t1303 INNER JOIN t2 USING(a)304 INNER JOIN t3 USING(a)305 RIGHT JOIN t4 USING(a)306 RIGHT JOIN t5 USING(a)307 ORDER BY 1 NULLS FIRST;308} {309 15 15 15 15 15 15310 17 - - - - 17311 18 - - - - 18312 19 - - - 19 19313}314do_execsql_test joinB-22 {315 SELECT a, b, c, d, e, f, g316 FROM t1317 INNER JOIN t2 USING(a,b)318 INNER JOIN t3 USING(a,b)319 RIGHT JOIN t4 USING(a,b)320 RIGHT JOIN t5 USING(a,b)321 WHERE d<>33 OR d IS NULL322 ORDER BY 1 NULLS FIRST;323} {324 15 25 35 35 35 35 35325 17 27 - - - - 37326 18 28 - - - - 38327 19 29 - - - 39 39328}329do_execsql_test joinB-23 {330 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a331 FROM t1332 INNER JOIN t2 USING(a)333 INNER JOIN t3 USING(a)334 RIGHT JOIN t4 USING(a)335 FULL JOIN t5 USING(a)336 ORDER BY 1 NULLS FIRST;337} {338 11 - - - 11 -339 13 - - - 13 -340 15 15 15 15 15 15341 17 - - - - 17342 18 - - - - 18343 19 - - - 19 19344}345do_execsql_test joinB-24 {346 SELECT b, c, d, e, f, g347 FROM t1348 NATURAL INNER JOIN t2349 NATURAL INNER JOIN t3350 NATURAL RIGHT JOIN t4351 NATURAL FULL JOIN t5352 WHERE b BETWEEN 12 AND 17353 ORDER BY 1 NULLS FIRST;354} {355}356do_execsql_test joinB-25 {357 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a358 FROM t1359 INNER JOIN t2 USING(a)360 INNER JOIN t3 USING(a)361 FULL JOIN t4 USING(a)362 INNER JOIN t5 USING(a)363 ORDER BY 1 NULLS FIRST;364} {365 15 15 15 15 15 15366 19 - - - 19 19367}368do_execsql_test joinB-26 {369 SELECT a, c, d, e, f, g370 FROM t1371 INNER JOIN t2 USING(a,b)372 INNER JOIN t3 USING(a,b)373 FULL JOIN t4 USING(a,b)374 INNER JOIN t5 USING(a,b)375 WHERE a<>13376 ORDER BY 1 NULLS FIRST;377} {378 15 35 35 35 35 35379 19 - - - 39 39380}381do_execsql_test joinB-27 {382 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a383 FROM t1384 INNER JOIN t2 USING(a)385 INNER JOIN t3 USING(a)386 FULL JOIN t4 USING(a)387 LEFT JOIN t5 USING(a)388 ORDER BY 1 NULLS FIRST;389} {390 11 - - - 11 -391 12 12 12 12 - -392 13 - - - 13 -393 15 15 15 15 15 15394 19 - - - 19 19395}396do_execsql_test joinB-28 {397 SELECT a, c, d, e, f, g398 FROM t1399 INNER JOIN (t2 INNER JOIN t3 USING(a)) USING(a)400 FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a)401 WHERE a<=18402 ORDER BY 1 NULLS FIRST;403} {404 11 - - - 31 -405 12 32 32 32 - -406 13 - - - 33 -407 15 35 35 35 35 35408}409do_execsql_test joinB-29 {410 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a411 FROM t1412 INNER JOIN t2 USING(a)413 INNER JOIN t3 USING(a)414 FULL JOIN t4 USING(a)415 RIGHT JOIN t5 USING(a)416 ORDER BY 1 NULLS FIRST;417} {418 15 15 15 15 15 15419 17 - - - - 17420 18 - - - - 18421 19 - - - 19 19422}423do_execsql_test joinB-30 {424 SELECT a, b, c, d, e, f, g425 FROM t1426 INNER JOIN t2 USING(a,b)427 INNER JOIN t3 USING(a,b)428 FULL JOIN t4 USING(a,b)429 RIGHT JOIN t5 USING(a,b)430 WHERE d<>33 OR d IS NULL431 ORDER BY 1 NULLS FIRST;432} {433 15 25 35 35 35 35 35434 17 27 - - - - 37435 18 28 - - - - 38436 19 29 - - - 39 39437}438do_execsql_test joinB-31 {439 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a440 FROM t1441 INNER JOIN t2 USING(a)442 INNER JOIN t3 USING(a)443 FULL JOIN t4 USING(a)444 FULL JOIN t5 USING(a)445 ORDER BY 1 NULLS FIRST;446} {447 11 - - - 11 -448 12 12 12 12 - -449 13 - - - 13 -450 15 15 15 15 15 15451 17 - - - - 17452 18 - - - - 18453 19 - - - 19 19454}455do_execsql_test joinB-32 {456 SELECT b, c, d, e, f, g457 FROM t1458 NATURAL INNER JOIN t2459 NATURAL INNER JOIN t3460 NATURAL FULL JOIN t4461 NATURAL FULL JOIN t5462 WHERE b BETWEEN 12 AND 17463 ORDER BY 1 NULLS FIRST;464} {465}466do_execsql_test joinB-33 {467 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a468 FROM t1469 INNER JOIN t2 USING(a)470 LEFT JOIN t3 USING(a)471 INNER JOIN t4 USING(a)472 INNER JOIN t5 USING(a)473 ORDER BY 1 NULLS FIRST;474} {475 15 15 15 15 15 15476}477do_execsql_test joinB-34 {478 SELECT a, c, d, e, f, g479 FROM t1480 INNER JOIN t2 USING(a,b)481 LEFT JOIN t3 USING(a,b)482 INNER JOIN t4 USING(a,b)483 INNER JOIN t5 USING(a,b)484 WHERE a<>13485 ORDER BY 1 NULLS FIRST;486} {487 15 35 35 35 35 35488}489do_execsql_test joinB-35 {490 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a491 FROM t1492 INNER JOIN t2 USING(a)493 LEFT JOIN t3 USING(a)494 INNER JOIN t4 USING(a)495 LEFT JOIN t5 USING(a)496 ORDER BY 1 NULLS FIRST;497} {498 15 15 15 15 15 15499}500do_execsql_test joinB-36 {501 SELECT a, c, d, e, f, g502 FROM t1503 INNER JOIN (t2 LEFT JOIN t3 USING(a)) USING(a)504 INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a)505 WHERE a<=18506 ORDER BY 1 NULLS FIRST;507} {508 15 35 35 35 35 35509}510do_execsql_test joinB-37 {511 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a512 FROM t1513 INNER JOIN t2 USING(a)514 LEFT JOIN t3 USING(a)515 INNER JOIN t4 USING(a)516 RIGHT JOIN t5 USING(a)517 ORDER BY 1 NULLS FIRST;518} {519 15 15 15 15 15 15520 17 - - - - 17521 18 - - - - 18522 19 - - - - 19523}524do_execsql_test joinB-38 {525 SELECT a, b, c, d, e, f, g526 FROM t1527 INNER JOIN t2 USING(a,b)528 LEFT JOIN t3 USING(a,b)529 INNER JOIN t4 USING(a,b)530 RIGHT JOIN t5 USING(a,b)531 WHERE d<>33 OR d IS NULL532 ORDER BY 1 NULLS FIRST;533} {534 15 25 35 35 35 35 35535 17 27 - - - - 37536 18 28 - - - - 38537 19 29 - - - - 39538}539do_execsql_test joinB-39 {540 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a541 FROM t1542 INNER JOIN t2 USING(a)543 LEFT JOIN t3 USING(a)544 INNER JOIN t4 USING(a)545 FULL JOIN t5 USING(a)546 ORDER BY 1 NULLS FIRST;547} {548 15 15 15 15 15 15549 17 - - - - 17550 18 - - - - 18551 19 - - - - 19552}553do_execsql_test joinB-40 {554 SELECT b, c, d, e, f, g555 FROM t1556 NATURAL INNER JOIN t2557 NATURAL LEFT JOIN t3558 NATURAL INNER JOIN t4559 NATURAL FULL JOIN t5560 WHERE b BETWEEN 12 AND 17561 ORDER BY 1 NULLS FIRST;562} {563}564do_execsql_test joinB-41 {565 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a566 FROM t1567 INNER JOIN t2 USING(a)568 LEFT JOIN t3 USING(a)569 LEFT JOIN t4 USING(a)570 INNER JOIN t5 USING(a)571 ORDER BY 1 NULLS FIRST;572} {573 15 15 15 15 15 15574}575do_execsql_test joinB-42 {576 SELECT a, c, d, e, f, g577 FROM t1578 INNER JOIN t2 USING(a,b)579 LEFT JOIN t3 USING(a,b)580 LEFT JOIN t4 USING(a,b)581 INNER JOIN t5 USING(a,b)582 WHERE a<>13583 ORDER BY 1 NULLS FIRST;584} {585 15 35 35 35 35 35586}587do_execsql_test joinB-43 {588 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a589 FROM t1590 INNER JOIN t2 USING(a)591 LEFT JOIN t3 USING(a)592 LEFT JOIN t4 USING(a)593 LEFT JOIN t5 USING(a)594 ORDER BY 1 NULLS FIRST;595} {596 12 12 12 12 - -597 15 15 15 15 15 15598}599do_execsql_test joinB-44 {600 SELECT a, c, d, e, f, g601 FROM t1602 INNER JOIN (t2 LEFT JOIN t3 USING(a)) USING(a)603 LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a)604 WHERE a<=18605 ORDER BY 1 NULLS FIRST;606} {607 12 32 32 32 - -608 15 35 35 35 35 35609}610do_execsql_test joinB-45 {611 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a612 FROM t1613 INNER JOIN t2 USING(a)614 LEFT JOIN t3 USING(a)615 LEFT JOIN t4 USING(a)616 RIGHT JOIN t5 USING(a)617 ORDER BY 1 NULLS FIRST;618} {619 15 15 15 15 15 15620 17 - - - - 17621 18 - - - - 18622 19 - - - - 19623}624do_execsql_test joinB-46 {625 SELECT a, b, c, d, e, f, g626 FROM t1627 INNER JOIN t2 USING(a,b)628 LEFT JOIN t3 USING(a,b)629 LEFT JOIN t4 USING(a,b)630 RIGHT JOIN t5 USING(a,b)631 WHERE d<>33 OR d IS NULL632 ORDER BY 1 NULLS FIRST;633} {634 15 25 35 35 35 35 35635 17 27 - - - - 37636 18 28 - - - - 38637 19 29 - - - - 39638}639do_execsql_test joinB-47 {640 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a641 FROM t1642 INNER JOIN t2 USING(a)643 LEFT JOIN t3 USING(a)644 LEFT JOIN t4 USING(a)645 FULL JOIN t5 USING(a)646 ORDER BY 1 NULLS FIRST;647} {648 12 12 12 12 - -649 15 15 15 15 15 15650 17 - - - - 17651 18 - - - - 18652 19 - - - - 19653}654do_execsql_test joinB-48 {655 SELECT b, c, d, e, f, g656 FROM t1657 NATURAL INNER JOIN t2658 NATURAL LEFT JOIN t3659 NATURAL LEFT JOIN t4660 NATURAL FULL JOIN t5661 WHERE b BETWEEN 12 AND 17662 ORDER BY 1 NULLS FIRST;663} {664}665do_execsql_test joinB-49 {666 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a667 FROM t1668 INNER JOIN t2 USING(a)669 LEFT JOIN t3 USING(a)670 RIGHT JOIN t4 USING(a)671 INNER JOIN t5 USING(a)672 ORDER BY 1 NULLS FIRST;673} {674 15 15 15 15 15 15675 19 - - - 19 19676}677do_execsql_test joinB-50 {678 SELECT a, c, d, e, f, g679 FROM t1680 INNER JOIN t2 USING(a,b)681 LEFT JOIN t3 USING(a,b)682 RIGHT JOIN t4 USING(a,b)683 INNER JOIN t5 USING(a,b)684 WHERE a<>13685 ORDER BY 1 NULLS FIRST;686} {687 15 35 35 35 35 35688 19 - - - 39 39689}690do_execsql_test joinB-51 {691 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a692 FROM t1693 INNER JOIN t2 USING(a)694 LEFT JOIN t3 USING(a)695 RIGHT JOIN t4 USING(a)696 LEFT JOIN t5 USING(a)697 ORDER BY 1 NULLS FIRST;698} {699 11 - - - 11 -700 13 - - - 13 -701 15 15 15 15 15 15702 19 - - - 19 19703}704do_execsql_test joinB-52 {705 SELECT a, c, d, e, f, g706 FROM t1707 INNER JOIN (t2 LEFT JOIN t3 USING(a)) USING(a)708 RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a)709 WHERE a<=18710 ORDER BY 1 NULLS FIRST;711} {712 11 - - - 31 -713 13 - - - 33 -714 15 35 35 35 35 35715}716do_execsql_test joinB-53 {717 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a718 FROM t1719 INNER JOIN t2 USING(a)720 LEFT JOIN t3 USING(a)721 RIGHT JOIN t4 USING(a)722 RIGHT JOIN t5 USING(a)723 ORDER BY 1 NULLS FIRST;724} {725 15 15 15 15 15 15726 17 - - - - 17727 18 - - - - 18728 19 - - - 19 19729}730do_execsql_test joinB-54 {731 SELECT a, b, c, d, e, f, g732 FROM t1733 INNER JOIN t2 USING(a,b)734 LEFT JOIN t3 USING(a,b)735 RIGHT JOIN t4 USING(a,b)736 RIGHT JOIN t5 USING(a,b)737 WHERE d<>33 OR d IS NULL738 ORDER BY 1 NULLS FIRST;739} {740 15 25 35 35 35 35 35741 17 27 - - - - 37742 18 28 - - - - 38743 19 29 - - - 39 39744}745do_execsql_test joinB-55 {746 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a747 FROM t1748 INNER JOIN t2 USING(a)749 LEFT JOIN t3 USING(a)750 RIGHT JOIN t4 USING(a)751 FULL JOIN t5 USING(a)752 ORDER BY 1 NULLS FIRST;753} {754 11 - - - 11 -755 13 - - - 13 -756 15 15 15 15 15 15757 17 - - - - 17758 18 - - - - 18759 19 - - - 19 19760}761do_execsql_test joinB-56 {762 SELECT b, c, d, e, f, g763 FROM t1764 NATURAL INNER JOIN t2765 NATURAL LEFT JOIN t3766 NATURAL RIGHT JOIN t4767 NATURAL FULL JOIN t5768 WHERE b BETWEEN 12 AND 17769 ORDER BY 1 NULLS FIRST;770} {771}772do_execsql_test joinB-57 {773 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a774 FROM t1775 INNER JOIN t2 USING(a)776 LEFT JOIN t3 USING(a)777 FULL JOIN t4 USING(a)778 INNER JOIN t5 USING(a)779 ORDER BY 1 NULLS FIRST;780} {781 15 15 15 15 15 15782 19 - - - 19 19783}784do_execsql_test joinB-58 {785 SELECT a, c, d, e, f, g786 FROM t1787 INNER JOIN t2 USING(a,b)788 LEFT JOIN t3 USING(a,b)789 FULL JOIN t4 USING(a,b)790 INNER JOIN t5 USING(a,b)791 WHERE a<>13792 ORDER BY 1 NULLS FIRST;793} {794 15 35 35 35 35 35795 19 - - - 39 39796}797do_execsql_test joinB-59 {798 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a799 FROM t1800 INNER JOIN t2 USING(a)801 LEFT JOIN t3 USING(a)802 FULL JOIN t4 USING(a)803 LEFT JOIN t5 USING(a)804 ORDER BY 1 NULLS FIRST;805} {806 11 - - - 11 -807 12 12 12 12 - -808 13 - - - 13 -809 15 15 15 15 15 15810 19 - - - 19 19811}812do_execsql_test joinB-60 {813 SELECT a, c, d, e, f, g814 FROM t1815 INNER JOIN (t2 LEFT JOIN t3 USING(a)) USING(a)816 FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a)817 WHERE a<=18818 ORDER BY 1 NULLS FIRST;819} {820 11 - - - 31 -821 12 32 32 32 - -822 13 - - - 33 -823 15 35 35 35 35 35824}825do_execsql_test joinB-61 {826 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a827 FROM t1828 INNER JOIN t2 USING(a)829 LEFT JOIN t3 USING(a)830 FULL JOIN t4 USING(a)831 RIGHT JOIN t5 USING(a)832 ORDER BY 1 NULLS FIRST;833} {834 15 15 15 15 15 15835 17 - - - - 17836 18 - - - - 18837 19 - - - 19 19838}839do_execsql_test joinB-62 {840 SELECT a, b, c, d, e, f, g841 FROM t1842 INNER JOIN t2 USING(a,b)843 LEFT JOIN t3 USING(a,b)844 FULL JOIN t4 USING(a,b)845 RIGHT JOIN t5 USING(a,b)846 WHERE d<>33 OR d IS NULL847 ORDER BY 1 NULLS FIRST;848} {849 15 25 35 35 35 35 35850 17 27 - - - - 37851 18 28 - - - - 38852 19 29 - - - 39 39853}854do_execsql_test joinB-63 {855 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a856 FROM t1857 INNER JOIN t2 USING(a)858 LEFT JOIN t3 USING(a)859 FULL JOIN t4 USING(a)860 FULL JOIN t5 USING(a)861 ORDER BY 1 NULLS FIRST;862} {863 11 - - - 11 -864 12 12 12 12 - -865 13 - - - 13 -866 15 15 15 15 15 15867 17 - - - - 17868 18 - - - - 18869 19 - - - 19 19870}871do_execsql_test joinB-64 {872 SELECT b, c, d, e, f, g873 FROM t1874 NATURAL INNER JOIN t2875 NATURAL LEFT JOIN t3876 NATURAL FULL JOIN t4877 NATURAL FULL JOIN t5878 WHERE b BETWEEN 12 AND 17879 ORDER BY 1 NULLS FIRST;880} {881}882do_execsql_test joinB-65 {883 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a884 FROM t1885 INNER JOIN t2 USING(a)886 RIGHT JOIN t3 USING(a)887 INNER JOIN t4 USING(a)888 INNER JOIN t5 USING(a)889 ORDER BY 1 NULLS FIRST;890} {891 15 15 15 15 15 15892 19 - - 19 19 19893}894do_execsql_test joinB-66 {895 SELECT a, c, d, e, f, g896 FROM t1897 INNER JOIN t2 USING(a,b)898 RIGHT JOIN t3 USING(a,b)899 INNER JOIN t4 USING(a,b)900 INNER JOIN t5 USING(a,b)901 WHERE a<>13902 ORDER BY 1 NULLS FIRST;903} {904 15 35 35 35 35 35905 19 - - 39 39 39906}907do_execsql_test joinB-67 {908 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a909 FROM t1910 INNER JOIN t2 USING(a)911 RIGHT JOIN t3 USING(a)912 INNER JOIN t4 USING(a)913 LEFT JOIN t5 USING(a)914 ORDER BY 1 NULLS FIRST;915} {916 11 - - 11 11 -917 13 - - 13 13 -918 15 15 15 15 15 15919 19 - - 19 19 19920}921do_execsql_test joinB-68 {922 SELECT a, c, d, e, f, g923 FROM t1924 INNER JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a)925 INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a)926 WHERE a<=18927 ORDER BY 1 NULLS FIRST;928} {929 11 31 - 31 31 -930 15 35 35 35 35 35931}932do_execsql_test joinB-69 {933 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a934 FROM t1935 INNER JOIN t2 USING(a)936 RIGHT JOIN t3 USING(a)937 INNER JOIN t4 USING(a)938 RIGHT JOIN t5 USING(a)939 ORDER BY 1 NULLS FIRST;940} {941 15 15 15 15 15 15942 17 - - - - 17943 18 - - - - 18944 19 - - 19 19 19945}946do_execsql_test joinB-70 {947 SELECT a, b, c, d, e, f, g948 FROM t1949 INNER JOIN t2 USING(a,b)950 RIGHT JOIN t3 USING(a,b)951 INNER JOIN t4 USING(a,b)952 RIGHT JOIN t5 USING(a,b)953 WHERE d<>33 OR d IS NULL954 ORDER BY 1 NULLS FIRST;955} {956 15 25 35 35 35 35 35957 17 27 - - - - 37958 18 28 - - - - 38959 19 29 - - 39 39 39960}961do_execsql_test joinB-71 {962 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a963 FROM t1964 INNER JOIN t2 USING(a)965 RIGHT JOIN t3 USING(a)966 INNER JOIN t4 USING(a)967 FULL JOIN t5 USING(a)968 ORDER BY 1 NULLS FIRST;969} {970 11 - - 11 11 -971 13 - - 13 13 -972 15 15 15 15 15 15973 17 - - - - 17974 18 - - - - 18975 19 - - 19 19 19976}977do_execsql_test joinB-72 {978 SELECT b, c, d, e, f, g979 FROM t1980 NATURAL INNER JOIN t2981 NATURAL RIGHT JOIN t3982 NATURAL INNER JOIN t4983 NATURAL FULL JOIN t5984 WHERE b BETWEEN 12 AND 17985 ORDER BY 1 NULLS FIRST;986} {987}988do_execsql_test joinB-73 {989 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a990 FROM t1991 INNER JOIN t2 USING(a)992 RIGHT JOIN t3 USING(a)993 LEFT JOIN t4 USING(a)994 INNER JOIN t5 USING(a)995 ORDER BY 1 NULLS FIRST;996} {997 15 15 15 15 15 15998 17 - - 17 - 17999 18 - - 18 - 181000 19 - - 19 19 191001}1002do_execsql_test joinB-74 {1003 SELECT a, c, d, e, f, g1004 FROM t11005 INNER JOIN t2 USING(a,b)1006 RIGHT JOIN t3 USING(a,b)1007 LEFT JOIN t4 USING(a,b)1008 INNER JOIN t5 USING(a,b)1009 WHERE a<>131010 ORDER BY 1 NULLS FIRST;1011} {1012 15 35 35 35 35 351013 17 - - 37 - 371014 18 - - 38 - 381015 19 - - 39 39 391016}1017do_execsql_test joinB-75 {1018 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a1019 FROM t11020 INNER JOIN t2 USING(a)1021 RIGHT JOIN t3 USING(a)1022 LEFT JOIN t4 USING(a)1023 LEFT JOIN t5 USING(a)1024 ORDER BY 1 NULLS FIRST;1025} {1026 - - - - - -1027 11 - - 11 11 -1028 12 12 12 12 - -1029 13 - - 13 13 -1030 15 15 15 15 15 151031 17 - - 17 - 171032 18 - - 18 - 181033 19 - - 19 19 191034}1035do_execsql_test joinB-76 {1036 SELECT a, c, d, e, f, g1037 FROM t11038 INNER JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a)1039 LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a)1040 WHERE a<=181041 ORDER BY 1 NULLS FIRST;1042} {1043 11 31 - 31 31 -1044 12 32 32 32 - -1045 15 35 35 35 35 351046 17 37 - 37 - -1047}1048do_execsql_test joinB-77 {1049 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a1050 FROM t11051 INNER JOIN t2 USING(a)1052 RIGHT JOIN t3 USING(a)1053 LEFT JOIN t4 USING(a)1054 RIGHT JOIN t5 USING(a)1055 ORDER BY 1 NULLS FIRST;1056} {1057 15 15 15 15 15 151058 17 - - 17 - 171059 18 - - 18 - 181060 19 - - 19 19 191061}1062do_execsql_test joinB-78 {1063 SELECT a, b, c, d, e, f, g1064 FROM t11065 INNER JOIN t2 USING(a,b)1066 RIGHT JOIN t3 USING(a,b)1067 LEFT JOIN t4 USING(a,b)1068 RIGHT JOIN t5 USING(a,b)1069 WHERE d<>33 OR d IS NULL1070 ORDER BY 1 NULLS FIRST;1071} {1072 15 25 35 35 35 35 351073 17 27 - - 37 - 371074 18 28 - - 38 - 381075 19 29 - - 39 39 391076}1077do_execsql_test joinB-79 {1078 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a1079 FROM t11080 INNER JOIN t2 USING(a)1081 RIGHT JOIN t3 USING(a)1082 LEFT JOIN t4 USING(a)1083 FULL JOIN t5 USING(a)1084 ORDER BY 1 NULLS FIRST;1085} {1086 - - - - - -1087 11 - - 11 11 -1088 12 12 12 12 - -1089 13 - - 13 13 -1090 15 15 15 15 15 151091 17 - - 17 - 171092 18 - - 18 - 181093 19 - - 19 19 191094}1095do_execsql_test joinB-80 {1096 SELECT b, c, d, e, f, g1097 FROM t11098 NATURAL INNER JOIN t21099 NATURAL RIGHT JOIN t31100 NATURAL LEFT JOIN t41101 NATURAL FULL JOIN t51102 WHERE b BETWEEN 12 AND 171103 ORDER BY 1 NULLS FIRST;1104} {1105}1106do_execsql_test joinB-81 {1107 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a1108 FROM t11109 INNER JOIN t2 USING(a)1110 RIGHT JOIN t3 USING(a)1111 RIGHT JOIN t4 USING(a)1112 INNER JOIN t5 USING(a)1113 ORDER BY 1 NULLS FIRST;1114} {1115 15 15 15 15 15 151116 19 - - 19 19 191117}1118do_execsql_test joinB-82 {1119 SELECT a, c, d, e, f, g1120 FROM t11121 INNER JOIN t2 USING(a,b)1122 RIGHT JOIN t3 USING(a,b)1123 RIGHT JOIN t4 USING(a,b)1124 INNER JOIN t5 USING(a,b)1125 WHERE a<>131126 ORDER BY 1 NULLS FIRST;1127} {1128 15 35 35 35 35 351129 19 - - 39 39 391130}1131do_execsql_test joinB-83 {1132 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a1133 FROM t11134 INNER JOIN t2 USING(a)1135 RIGHT JOIN t3 USING(a)1136 RIGHT JOIN t4 USING(a)1137 LEFT JOIN t5 USING(a)1138 ORDER BY 1 NULLS FIRST;1139} {1140 11 - - 11 11 -1141 13 - - 13 13 -1142 15 15 15 15 15 151143 19 - - 19 19 191144}1145do_execsql_test joinB-84 {1146 SELECT a, c, d, e, f, g1147 FROM t11148 INNER JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a)1149 RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a)1150 WHERE a<=181151 ORDER BY 1 NULLS FIRST;1152} {1153 11 31 - 31 31 -1154 13 - - - 33 -1155 15 35 35 35 35 351156}1157do_execsql_test joinB-85 {1158 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a1159 FROM t11160 INNER JOIN t2 USING(a)1161 RIGHT JOIN t3 USING(a)1162 RIGHT JOIN t4 USING(a)1163 RIGHT JOIN t5 USING(a)1164 ORDER BY 1 NULLS FIRST;1165} {1166 15 15 15 15 15 151167 17 - - - - 171168 18 - - - - 181169 19 - - 19 19 191170}1171do_execsql_test joinB-86 {1172 SELECT a, b, c, d, e, f, g1173 FROM t11174 INNER JOIN t2 USING(a,b)1175 RIGHT JOIN t3 USING(a,b)1176 RIGHT JOIN t4 USING(a,b)1177 RIGHT JOIN t5 USING(a,b)1178 WHERE d<>33 OR d IS NULL1179 ORDER BY 1 NULLS FIRST;1180} {1181 15 25 35 35 35 35 351182 17 27 - - - - 371183 18 28 - - - - 381184 19 29 - - 39 39 391185}1186do_execsql_test joinB-87 {1187 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a1188 FROM t11189 INNER JOIN t2 USING(a)1190 RIGHT JOIN t3 USING(a)1191 RIGHT JOIN t4 USING(a)1192 FULL JOIN t5 USING(a)1193 ORDER BY 1 NULLS FIRST;1194} {1195 11 - - 11 11 -1196 13 - - 13 13 -1197 15 15 15 15 15 151198 17 - - - - 171199 18 - - - - 181200 19 - - 19 19 19