1set testdir [file dirname $argv0] 2# 2022-04-19 3# 4# The author disclaims copyright to this source code. In place of 5# 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 module 16# was created as follows: 17# 18# 1. Run a TCL script (included at the bottom of this file) that 19# generates an input script for "psql" that will run man 20# diverse tests on joins. 21# 22# 2. Run the script from step (1) through psql and collect the 23# output. 24# 25# 3. Make a few minor global search-and-replace operations to convert 26# 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.tcl 31db 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.a 52 FROM t1 53 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 15 60} 61do_execsql_test joinB-2 { 62 SELECT a, c, d, e, f, g 63 FROM t1 64 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<>13 69 ORDER BY 1 NULLS FIRST; 70} { 71 15 35 35 35 35 35 72} 73do_execsql_test joinB-3 { 74 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 75 FROM t1 76 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 15 83} 84do_execsql_test joinB-4 { 85 SELECT a, c, d, e, f, g 86 FROM t1 87 INNER JOIN (t2 INNER JOIN t3 USING(a)) USING(a) 88 INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 89 WHERE a<=18 90 ORDER BY 1 NULLS FIRST; 91} { 92 15 35 35 35 35 35 93} 94do_execsql_test joinB-5 { 95 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 96 FROM t1 97 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 15 104 17 - - - - 17 105 18 - - - - 18 106 19 - - - - 19 107} 108do_execsql_test joinB-6 { 109 SELECT a, b, c, d, e, f, g 110 FROM t1 111 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 NULL 116 ORDER BY 1 NULLS FIRST; 117} { 118 15 25 35 35 35 35 35 119 17 27 - - - - 37 120 18 28 - - - - 38 121 19 29 - - - - 39 122} 123do_execsql_test joinB-7 { 124 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 125 FROM t1 126 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 15 133 17 - - - - 17 134 18 - - - - 18 135 19 - - - - 19 136} 137do_execsql_test joinB-8 { 138 SELECT b, c, d, e, f, g 139 FROM t1 140 NATURAL INNER JOIN t2 141 NATURAL INNER JOIN t3 142 NATURAL INNER JOIN t4 143 NATURAL FULL JOIN t5 144 WHERE b BETWEEN 12 AND 17 145 ORDER BY 1 NULLS FIRST; 146} { 147} 148do_execsql_test joinB-9 { 149 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 150 FROM t1 151 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 15 158} 159do_execsql_test joinB-10 { 160 SELECT a, c, d, e, f, g 161 FROM t1 162 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<>13 167 ORDER BY 1 NULLS FIRST; 168} { 169 15 35 35 35 35 35 170} 171do_execsql_test joinB-11 { 172 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 173 FROM t1 174 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 15 182} 183do_execsql_test joinB-12 { 184 SELECT a, c, d, e, f, g 185 FROM t1 186 INNER JOIN (t2 INNER JOIN t3 USING(a)) USING(a) 187 LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 188 WHERE a<=18 189 ORDER BY 1 NULLS FIRST; 190} { 191 12 32 32 32 - - 192 15 35 35 35 35 35 193} 194do_execsql_test joinB-13 { 195 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 196 FROM t1 197 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 15 204 17 - - - - 17 205 18 - - - - 18 206 19 - - - - 19 207} 208do_execsql_test joinB-14 { 209 SELECT a, b, c, d, e, f, g 210 FROM t1 211 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 NULL 216 ORDER BY 1 NULLS FIRST; 217} { 218 15 25 35 35 35 35 35 219 17 27 - - - - 37 220 18 28 - - - - 38 221 19 29 - - - - 39 222} 223do_execsql_test joinB-15 { 224 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 225 FROM t1 226 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 15 234 17 - - - - 17 235 18 - - - - 18 236 19 - - - - 19 237} 238do_execsql_test joinB-16 { 239 SELECT b, c, d, e, f, g 240 FROM t1 241 NATURAL INNER JOIN t2 242 NATURAL INNER JOIN t3 243 NATURAL LEFT JOIN t4 244 NATURAL FULL JOIN t5 245 WHERE b BETWEEN 12 AND 17 246 ORDER BY 1 NULLS FIRST; 247} { 248} 249do_execsql_test joinB-17 { 250 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 251 FROM t1 252 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 15 259 19 - - - 19 19 260} 261do_execsql_test joinB-18 { 262 SELECT a, c, d, e, f, g 263 FROM t1 264 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<>13 269 ORDER BY 1 NULLS FIRST; 270} { 271 15 35 35 35 35 35 272 19 - - - 39 39 273} 274do_execsql_test joinB-19 { 275 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 276 FROM t1 277 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 15 286 19 - - - 19 19 287} 288do_execsql_test joinB-20 { 289 SELECT a, c, d, e, f, g 290 FROM t1 291 INNER JOIN (t2 INNER JOIN t3 USING(a)) USING(a) 292 RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 293 WHERE a<=18 294 ORDER BY 1 NULLS FIRST; 295} { 296 11 - - - 31 - 297 13 - - - 33 - 298 15 35 35 35 35 35 299} 300do_execsql_test joinB-21 { 301 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 302 FROM t1 303 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 15 310 17 - - - - 17 311 18 - - - - 18 312 19 - - - 19 19 313} 314do_execsql_test joinB-22 { 315 SELECT a, b, c, d, e, f, g 316 FROM t1 317 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 NULL 322 ORDER BY 1 NULLS FIRST; 323} { 324 15 25 35 35 35 35 35 325 17 27 - - - - 37 326 18 28 - - - - 38 327 19 29 - - - 39 39 328} 329do_execsql_test joinB-23 { 330 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 331 FROM t1 332 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 15 341 17 - - - - 17 342 18 - - - - 18 343 19 - - - 19 19 344} 345do_execsql_test joinB-24 { 346 SELECT b, c, d, e, f, g 347 FROM t1 348 NATURAL INNER JOIN t2 349 NATURAL INNER JOIN t3 350 NATURAL RIGHT JOIN t4 351 NATURAL FULL JOIN t5 352 WHERE b BETWEEN 12 AND 17 353 ORDER BY 1 NULLS FIRST; 354} { 355} 356do_execsql_test joinB-25 { 357 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 358 FROM t1 359 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 15 366 19 - - - 19 19 367} 368do_execsql_test joinB-26 { 369 SELECT a, c, d, e, f, g 370 FROM t1 371 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<>13 376 ORDER BY 1 NULLS FIRST; 377} { 378 15 35 35 35 35 35 379 19 - - - 39 39 380} 381do_execsql_test joinB-27 { 382 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 383 FROM t1 384 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 15 394 19 - - - 19 19 395} 396do_execsql_test joinB-28 { 397 SELECT a, c, d, e, f, g 398 FROM t1 399 INNER JOIN (t2 INNER JOIN t3 USING(a)) USING(a) 400 FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 401 WHERE a<=18 402 ORDER BY 1 NULLS FIRST; 403} { 404 11 - - - 31 - 405 12 32 32 32 - - 406 13 - - - 33 - 407 15 35 35 35 35 35 408} 409do_execsql_test joinB-29 { 410 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 411 FROM t1 412 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 15 419 17 - - - - 17 420 18 - - - - 18 421 19 - - - 19 19 422} 423do_execsql_test joinB-30 { 424 SELECT a, b, c, d, e, f, g 425 FROM t1 426 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 NULL 431 ORDER BY 1 NULLS FIRST; 432} { 433 15 25 35 35 35 35 35 434 17 27 - - - - 37 435 18 28 - - - - 38 436 19 29 - - - 39 39 437} 438do_execsql_test joinB-31 { 439 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 440 FROM t1 441 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 15 451 17 - - - - 17 452 18 - - - - 18 453 19 - - - 19 19 454} 455do_execsql_test joinB-32 { 456 SELECT b, c, d, e, f, g 457 FROM t1 458 NATURAL INNER JOIN t2 459 NATURAL INNER JOIN t3 460 NATURAL FULL JOIN t4 461 NATURAL FULL JOIN t5 462 WHERE b BETWEEN 12 AND 17 463 ORDER BY 1 NULLS FIRST; 464} { 465} 466do_execsql_test joinB-33 { 467 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 468 FROM t1 469 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 15 476} 477do_execsql_test joinB-34 { 478 SELECT a, c, d, e, f, g 479 FROM t1 480 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<>13 485 ORDER BY 1 NULLS FIRST; 486} { 487 15 35 35 35 35 35 488} 489do_execsql_test joinB-35 { 490 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 491 FROM t1 492 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 15 499} 500do_execsql_test joinB-36 { 501 SELECT a, c, d, e, f, g 502 FROM t1 503 INNER JOIN (t2 LEFT JOIN t3 USING(a)) USING(a) 504 INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 505 WHERE a<=18 506 ORDER BY 1 NULLS FIRST; 507} { 508 15 35 35 35 35 35 509} 510do_execsql_test joinB-37 { 511 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 512 FROM t1 513 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 15 520 17 - - - - 17 521 18 - - - - 18 522 19 - - - - 19 523} 524do_execsql_test joinB-38 { 525 SELECT a, b, c, d, e, f, g 526 FROM t1 527 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 NULL 532 ORDER BY 1 NULLS FIRST; 533} { 534 15 25 35 35 35 35 35 535 17 27 - - - - 37 536 18 28 - - - - 38 537 19 29 - - - - 39 538} 539do_execsql_test joinB-39 { 540 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 541 FROM t1 542 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 15 549 17 - - - - 17 550 18 - - - - 18 551 19 - - - - 19 552} 553do_execsql_test joinB-40 { 554 SELECT b, c, d, e, f, g 555 FROM t1 556 NATURAL INNER JOIN t2 557 NATURAL LEFT JOIN t3 558 NATURAL INNER JOIN t4 559 NATURAL FULL JOIN t5 560 WHERE b BETWEEN 12 AND 17 561 ORDER BY 1 NULLS FIRST; 562} { 563} 564do_execsql_test joinB-41 { 565 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 566 FROM t1 567 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 15 574} 575do_execsql_test joinB-42 { 576 SELECT a, c, d, e, f, g 577 FROM t1 578 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<>13 583 ORDER BY 1 NULLS FIRST; 584} { 585 15 35 35 35 35 35 586} 587do_execsql_test joinB-43 { 588 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 589 FROM t1 590 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 15 598} 599do_execsql_test joinB-44 { 600 SELECT a, c, d, e, f, g 601 FROM t1 602 INNER JOIN (t2 LEFT JOIN t3 USING(a)) USING(a) 603 LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 604 WHERE a<=18 605 ORDER BY 1 NULLS FIRST; 606} { 607 12 32 32 32 - - 608 15 35 35 35 35 35 609} 610do_execsql_test joinB-45 { 611 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 612 FROM t1 613 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 15 620 17 - - - - 17 621 18 - - - - 18 622 19 - - - - 19 623} 624do_execsql_test joinB-46 { 625 SELECT a, b, c, d, e, f, g 626 FROM t1 627 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 NULL 632 ORDER BY 1 NULLS FIRST; 633} { 634 15 25 35 35 35 35 35 635 17 27 - - - - 37 636 18 28 - - - - 38 637 19 29 - - - - 39 638} 639do_execsql_test joinB-47 { 640 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 641 FROM t1 642 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 15 650 17 - - - - 17 651 18 - - - - 18 652 19 - - - - 19 653} 654do_execsql_test joinB-48 { 655 SELECT b, c, d, e, f, g 656 FROM t1 657 NATURAL INNER JOIN t2 658 NATURAL LEFT JOIN t3 659 NATURAL LEFT JOIN t4 660 NATURAL FULL JOIN t5 661 WHERE b BETWEEN 12 AND 17 662 ORDER BY 1 NULLS FIRST; 663} { 664} 665do_execsql_test joinB-49 { 666 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 667 FROM t1 668 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 15 675 19 - - - 19 19 676} 677do_execsql_test joinB-50 { 678 SELECT a, c, d, e, f, g 679 FROM t1 680 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<>13 685 ORDER BY 1 NULLS FIRST; 686} { 687 15 35 35 35 35 35 688 19 - - - 39 39 689} 690do_execsql_test joinB-51 { 691 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 692 FROM t1 693 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 15 702 19 - - - 19 19 703} 704do_execsql_test joinB-52 { 705 SELECT a, c, d, e, f, g 706 FROM t1 707 INNER JOIN (t2 LEFT JOIN t3 USING(a)) USING(a) 708 RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 709 WHERE a<=18 710 ORDER BY 1 NULLS FIRST; 711} { 712 11 - - - 31 - 713 13 - - - 33 - 714 15 35 35 35 35 35 715} 716do_execsql_test joinB-53 { 717 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 718 FROM t1 719 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 15 726 17 - - - - 17 727 18 - - - - 18 728 19 - - - 19 19 729} 730do_execsql_test joinB-54 { 731 SELECT a, b, c, d, e, f, g 732 FROM t1 733 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 NULL 738 ORDER BY 1 NULLS FIRST; 739} { 740 15 25 35 35 35 35 35 741 17 27 - - - - 37 742 18 28 - - - - 38 743 19 29 - - - 39 39 744} 745do_execsql_test joinB-55 { 746 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 747 FROM t1 748 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 15 757 17 - - - - 17 758 18 - - - - 18 759 19 - - - 19 19 760} 761do_execsql_test joinB-56 { 762 SELECT b, c, d, e, f, g 763 FROM t1 764 NATURAL INNER JOIN t2 765 NATURAL LEFT JOIN t3 766 NATURAL RIGHT JOIN t4 767 NATURAL FULL JOIN t5 768 WHERE b BETWEEN 12 AND 17 769 ORDER BY 1 NULLS FIRST; 770} { 771} 772do_execsql_test joinB-57 { 773 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 774 FROM t1 775 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 15 782 19 - - - 19 19 783} 784do_execsql_test joinB-58 { 785 SELECT a, c, d, e, f, g 786 FROM t1 787 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<>13 792 ORDER BY 1 NULLS FIRST; 793} { 794 15 35 35 35 35 35 795 19 - - - 39 39 796} 797do_execsql_test joinB-59 { 798 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 799 FROM t1 800 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 15 810 19 - - - 19 19 811} 812do_execsql_test joinB-60 { 813 SELECT a, c, d, e, f, g 814 FROM t1 815 INNER JOIN (t2 LEFT JOIN t3 USING(a)) USING(a) 816 FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 817 WHERE a<=18 818 ORDER BY 1 NULLS FIRST; 819} { 820 11 - - - 31 - 821 12 32 32 32 - - 822 13 - - - 33 - 823 15 35 35 35 35 35 824} 825do_execsql_test joinB-61 { 826 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 827 FROM t1 828 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 15 835 17 - - - - 17 836 18 - - - - 18 837 19 - - - 19 19 838} 839do_execsql_test joinB-62 { 840 SELECT a, b, c, d, e, f, g 841 FROM t1 842 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 NULL 847 ORDER BY 1 NULLS FIRST; 848} { 849 15 25 35 35 35 35 35 850 17 27 - - - - 37 851 18 28 - - - - 38 852 19 29 - - - 39 39 853} 854do_execsql_test joinB-63 { 855 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 856 FROM t1 857 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 15 867 17 - - - - 17 868 18 - - - - 18 869 19 - - - 19 19 870} 871do_execsql_test joinB-64 { 872 SELECT b, c, d, e, f, g 873 FROM t1 874 NATURAL INNER JOIN t2 875 NATURAL LEFT JOIN t3 876 NATURAL FULL JOIN t4 877 NATURAL FULL JOIN t5 878 WHERE b BETWEEN 12 AND 17 879 ORDER BY 1 NULLS FIRST; 880} { 881} 882do_execsql_test joinB-65 { 883 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 884 FROM t1 885 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 15 892 19 - - 19 19 19 893} 894do_execsql_test joinB-66 { 895 SELECT a, c, d, e, f, g 896 FROM t1 897 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<>13 902 ORDER BY 1 NULLS FIRST; 903} { 904 15 35 35 35 35 35 905 19 - - 39 39 39 906} 907do_execsql_test joinB-67 { 908 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 909 FROM t1 910 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 15 919 19 - - 19 19 19 920} 921do_execsql_test joinB-68 { 922 SELECT a, c, d, e, f, g 923 FROM t1 924 INNER JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a) 925 INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 926 WHERE a<=18 927 ORDER BY 1 NULLS FIRST; 928} { 929 11 31 - 31 31 - 930 15 35 35 35 35 35 931} 932do_execsql_test joinB-69 { 933 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 934 FROM t1 935 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 15 942 17 - - - - 17 943 18 - - - - 18 944 19 - - 19 19 19 945} 946do_execsql_test joinB-70 { 947 SELECT a, b, c, d, e, f, g 948 FROM t1 949 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 NULL 954 ORDER BY 1 NULLS FIRST; 955} { 956 15 25 35 35 35 35 35 957 17 27 - - - - 37 958 18 28 - - - - 38 959 19 29 - - 39 39 39 960} 961do_execsql_test joinB-71 { 962 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 963 FROM t1 964 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 15 973 17 - - - - 17 974 18 - - - - 18 975 19 - - 19 19 19 976} 977do_execsql_test joinB-72 { 978 SELECT b, c, d, e, f, g 979 FROM t1 980 NATURAL INNER JOIN t2 981 NATURAL RIGHT JOIN t3 982 NATURAL INNER JOIN t4 983 NATURAL FULL JOIN t5 984 WHERE b BETWEEN 12 AND 17 985 ORDER BY 1 NULLS FIRST; 986} { 987} 988do_execsql_test joinB-73 { 989 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 990 FROM t1 991 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 15 998 17 - - 17 - 17 999 18 - - 18 - 18 1000 19 - - 19 19 19 1001} 1002do_execsql_test joinB-74 { 1003 SELECT a, c, d, e, f, g 1004 FROM t1 1005 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<>13 1010 ORDER BY 1 NULLS FIRST; 1011} { 1012 15 35 35 35 35 35 1013 17 - - 37 - 37 1014 18 - - 38 - 38 1015 19 - - 39 39 39 1016} 1017do_execsql_test joinB-75 { 1018 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1019 FROM t1 1020 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 15 1031 17 - - 17 - 17 1032 18 - - 18 - 18 1033 19 - - 19 19 19 1034} 1035do_execsql_test joinB-76 { 1036 SELECT a, c, d, e, f, g 1037 FROM t1 1038 INNER JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a) 1039 LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 1040 WHERE a<=18 1041 ORDER BY 1 NULLS FIRST; 1042} { 1043 11 31 - 31 31 - 1044 12 32 32 32 - - 1045 15 35 35 35 35 35 1046 17 37 - 37 - - 1047} 1048do_execsql_test joinB-77 { 1049 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1050 FROM t1 1051 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 15 1058 17 - - 17 - 17 1059 18 - - 18 - 18 1060 19 - - 19 19 19 1061} 1062do_execsql_test joinB-78 { 1063 SELECT a, b, c, d, e, f, g 1064 FROM t1 1065 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 NULL 1070 ORDER BY 1 NULLS FIRST; 1071} { 1072 15 25 35 35 35 35 35 1073 17 27 - - 37 - 37 1074 18 28 - - 38 - 38 1075 19 29 - - 39 39 39 1076} 1077do_execsql_test joinB-79 { 1078 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1079 FROM t1 1080 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 15 1091 17 - - 17 - 17 1092 18 - - 18 - 18 1093 19 - - 19 19 19 1094} 1095do_execsql_test joinB-80 { 1096 SELECT b, c, d, e, f, g 1097 FROM t1 1098 NATURAL INNER JOIN t2 1099 NATURAL RIGHT JOIN t3 1100 NATURAL LEFT JOIN t4 1101 NATURAL FULL JOIN t5 1102 WHERE b BETWEEN 12 AND 17 1103 ORDER BY 1 NULLS FIRST; 1104} { 1105} 1106do_execsql_test joinB-81 { 1107 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1108 FROM t1 1109 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 15 1116 19 - - 19 19 19 1117} 1118do_execsql_test joinB-82 { 1119 SELECT a, c, d, e, f, g 1120 FROM t1 1121 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<>13 1126 ORDER BY 1 NULLS FIRST; 1127} { 1128 15 35 35 35 35 35 1129 19 - - 39 39 39 1130} 1131do_execsql_test joinB-83 { 1132 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1133 FROM t1 1134 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 15 1143 19 - - 19 19 19 1144} 1145do_execsql_test joinB-84 { 1146 SELECT a, c, d, e, f, g 1147 FROM t1 1148 INNER JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a) 1149 RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 1150 WHERE a<=18 1151 ORDER BY 1 NULLS FIRST; 1152} { 1153 11 31 - 31 31 - 1154 13 - - - 33 - 1155 15 35 35 35 35 35 1156} 1157do_execsql_test joinB-85 { 1158 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1159 FROM t1 1160 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 15 1167 17 - - - - 17 1168 18 - - - - 18 1169 19 - - 19 19 19 1170} 1171do_execsql_test joinB-86 { 1172 SELECT a, b, c, d, e, f, g 1173 FROM t1 1174 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 NULL 1179 ORDER BY 1 NULLS FIRST; 1180} { 1181 15 25 35 35 35 35 35 1182 17 27 - - - - 37 1183 18 28 - - - - 38 1184 19 29 - - 39 39 39 1185} 1186do_execsql_test joinB-87 { 1187 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1188 FROM t1 1189 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 15 1198 17 - - - - 17 1199 18 - - - - 18 1200 19 - - 19 19 19 1201} 1202do_execsql_test joinB-88 { 1203 SELECT b, c, d, e, f, g 1204 FROM t1 1205 NATURAL INNER JOIN t2 1206 NATURAL RIGHT JOIN t3 1207 NATURAL RIGHT JOIN t4 1208 NATURAL FULL JOIN t5 1209 WHERE b BETWEEN 12 AND 17 1210 ORDER BY 1 NULLS FIRST; 1211} { 1212} 1213do_execsql_test joinB-89 { 1214 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1215 FROM t1 1216 INNER JOIN t2 USING(a) 1217 RIGHT JOIN t3 USING(a) 1218 FULL JOIN t4 USING(a) 1219 INNER JOIN t5 USING(a) 1220 ORDER BY 1 NULLS FIRST; 1221} { 1222 15 15 15 15 15 15 1223 17 - - 17 - 17 1224 18 - - 18 - 18 1225 19 - - 19 19 19 1226} 1227do_execsql_test joinB-90 { 1228 SELECT a, c, d, e, f, g 1229 FROM t1 1230 INNER JOIN t2 USING(a,b) 1231 RIGHT JOIN t3 USING(a,b) 1232 FULL JOIN t4 USING(a,b) 1233 INNER JOIN t5 USING(a,b) 1234 WHERE a<>13 1235 ORDER BY 1 NULLS FIRST; 1236} { 1237 15 35 35 35 35 35 1238 17 - - 37 - 37 1239 18 - - 38 - 38 1240 19 - - 39 39 39 1241} 1242do_execsql_test joinB-91 { 1243 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1244 FROM t1 1245 INNER JOIN t2 USING(a) 1246 RIGHT JOIN t3 USING(a) 1247 FULL JOIN t4 USING(a) 1248 LEFT JOIN t5 USING(a) 1249 ORDER BY 1 NULLS FIRST; 1250} { 1251 - - - - - - 1252 11 - - 11 11 - 1253 12 12 12 12 - - 1254 13 - - 13 13 - 1255 15 15 15 15 15 15 1256 17 - - 17 - 17 1257 18 - - 18 - 18 1258 19 - - 19 19 19 1259} 1260do_execsql_test joinB-92 { 1261 SELECT a, c, d, e, f, g 1262 FROM t1 1263 INNER JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a) 1264 FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 1265 WHERE a<=18 1266 ORDER BY 1 NULLS FIRST; 1267} { 1268 11 31 - 31 31 - 1269 12 32 32 32 - - 1270 13 - - - 33 - 1271 15 35 35 35 35 35 1272 17 37 - 37 - - 1273} 1274do_execsql_test joinB-93 { 1275 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1276 FROM t1 1277 INNER JOIN t2 USING(a) 1278 RIGHT JOIN t3 USING(a) 1279 FULL JOIN t4 USING(a) 1280 RIGHT JOIN t5 USING(a) 1281 ORDER BY 1 NULLS FIRST; 1282} { 1283 15 15 15 15 15 15 1284 17 - - 17 - 17 1285 18 - - 18 - 18 1286 19 - - 19 19 19 1287} 1288do_execsql_test joinB-94 { 1289 SELECT a, b, c, d, e, f, g 1290 FROM t1 1291 INNER JOIN t2 USING(a,b) 1292 RIGHT JOIN t3 USING(a,b) 1293 FULL JOIN t4 USING(a,b) 1294 RIGHT JOIN t5 USING(a,b) 1295 WHERE d<>33 OR d IS NULL 1296 ORDER BY 1 NULLS FIRST; 1297} { 1298 15 25 35 35 35 35 35 1299 17 27 - - 37 - 37 1300 18 28 - - 38 - 38 1301 19 29 - - 39 39 39 1302} 1303do_execsql_test joinB-95 { 1304 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1305 FROM t1 1306 INNER JOIN t2 USING(a) 1307 RIGHT JOIN t3 USING(a) 1308 FULL JOIN t4 USING(a) 1309 FULL JOIN t5 USING(a) 1310 ORDER BY 1 NULLS FIRST; 1311} { 1312 - - - - - - 1313 11 - - 11 11 - 1314 12 12 12 12 - - 1315 13 - - 13 13 - 1316 15 15 15 15 15 15 1317 17 - - 17 - 17 1318 18 - - 18 - 18 1319 19 - - 19 19 19 1320} 1321do_execsql_test joinB-96 { 1322 SELECT b, c, d, e, f, g 1323 FROM t1 1324 NATURAL INNER JOIN t2 1325 NATURAL RIGHT JOIN t3 1326 NATURAL FULL JOIN t4 1327 NATURAL FULL JOIN t5 1328 WHERE b BETWEEN 12 AND 17 1329 ORDER BY 1 NULLS FIRST; 1330} { 1331} 1332do_execsql_test joinB-97 { 1333 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1334 FROM t1 1335 INNER JOIN t2 USING(a) 1336 FULL JOIN t3 USING(a) 1337 INNER JOIN t4 USING(a) 1338 INNER JOIN t5 USING(a) 1339 ORDER BY 1 NULLS FIRST; 1340} { 1341 15 15 15 15 15 15 1342 19 - - 19 19 19 1343} 1344do_execsql_test joinB-98 { 1345 SELECT a, c, d, e, f, g 1346 FROM t1 1347 INNER JOIN t2 USING(a,b) 1348 FULL JOIN t3 USING(a,b) 1349 INNER JOIN t4 USING(a,b) 1350 INNER JOIN t5 USING(a,b) 1351 WHERE a<>13 1352 ORDER BY 1 NULLS FIRST; 1353} { 1354 15 35 35 35 35 35 1355 19 - - 39 39 39 1356} 1357do_execsql_test joinB-99 { 1358 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1359 FROM t1 1360 INNER JOIN t2 USING(a) 1361 FULL JOIN t3 USING(a) 1362 INNER JOIN t4 USING(a) 1363 LEFT JOIN t5 USING(a) 1364 ORDER BY 1 NULLS FIRST; 1365} { 1366 11 - - 11 11 - 1367 13 - - 13 13 - 1368 15 15 15 15 15 15 1369 19 - - 19 19 19 1370} 1371do_execsql_test joinB-100 { 1372 SELECT a, c, d, e, f, g 1373 FROM t1 1374 INNER JOIN (t2 FULL JOIN t3 USING(a)) USING(a) 1375 INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 1376 WHERE a<=18 1377 ORDER BY 1 NULLS FIRST; 1378} { 1379 11 31 - 31 31 - 1380 15 35 35 35 35 35 1381} 1382do_execsql_test joinB-101 { 1383 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1384 FROM t1 1385 INNER JOIN t2 USING(a) 1386 FULL JOIN t3 USING(a) 1387 INNER JOIN t4 USING(a) 1388 RIGHT JOIN t5 USING(a) 1389 ORDER BY 1 NULLS FIRST; 1390} { 1391 15 15 15 15 15 15 1392 17 - - - - 17 1393 18 - - - - 18 1394 19 - - 19 19 19 1395} 1396do_execsql_test joinB-102 { 1397 SELECT a, b, c, d, e, f, g 1398 FROM t1 1399 INNER JOIN t2 USING(a,b) 1400 FULL JOIN t3 USING(a,b) 1401 INNER JOIN t4 USING(a,b) 1402 RIGHT JOIN t5 USING(a,b) 1403 WHERE d<>33 OR d IS NULL 1404 ORDER BY 1 NULLS FIRST; 1405} { 1406 15 25 35 35 35 35 35 1407 17 27 - - - - 37 1408 18 28 - - - - 38 1409 19 29 - - 39 39 39 1410} 1411do_execsql_test joinB-103 { 1412 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1413 FROM t1 1414 INNER JOIN t2 USING(a) 1415 FULL JOIN t3 USING(a) 1416 INNER JOIN t4 USING(a) 1417 FULL JOIN t5 USING(a) 1418 ORDER BY 1 NULLS FIRST; 1419} { 1420 11 - - 11 11 - 1421 13 - - 13 13 - 1422 15 15 15 15 15 15 1423 17 - - - - 17 1424 18 - - - - 18 1425 19 - - 19 19 19 1426} 1427do_execsql_test joinB-104 { 1428 SELECT b, c, d, e, f, g 1429 FROM t1 1430 NATURAL INNER JOIN t2 1431 NATURAL FULL JOIN t3 1432 NATURAL INNER JOIN t4 1433 NATURAL FULL JOIN t5 1434 WHERE b BETWEEN 12 AND 17 1435 ORDER BY 1 NULLS FIRST; 1436} { 1437} 1438do_execsql_test joinB-105 { 1439 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1440 FROM t1 1441 INNER JOIN t2 USING(a) 1442 FULL JOIN t3 USING(a) 1443 LEFT JOIN t4 USING(a) 1444 INNER JOIN t5 USING(a) 1445 ORDER BY 1 NULLS FIRST; 1446} { 1447 15 15 15 15 15 15 1448 17 - - 17 - 17 1449 18 - - 18 - 18 1450 19 - - 19 19 19 1451} 1452do_execsql_test joinB-106 { 1453 SELECT a, c, d, e, f, g 1454 FROM t1 1455 INNER JOIN t2 USING(a,b) 1456 FULL JOIN t3 USING(a,b) 1457 LEFT JOIN t4 USING(a,b) 1458 INNER JOIN t5 USING(a,b) 1459 WHERE a<>13 1460 ORDER BY 1 NULLS FIRST; 1461} { 1462 15 35 35 35 35 35 1463 17 - - 37 - 37 1464 18 - - 38 - 38 1465 19 - - 39 39 39 1466} 1467do_execsql_test joinB-107 { 1468 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1469 FROM t1 1470 INNER JOIN t2 USING(a) 1471 FULL JOIN t3 USING(a) 1472 LEFT JOIN t4 USING(a) 1473 LEFT JOIN t5 USING(a) 1474 ORDER BY 1 NULLS FIRST; 1475} { 1476 - - - - - - 1477 11 - - 11 11 - 1478 12 12 12 12 - - 1479 13 - - 13 13 - 1480 15 15 15 15 15 15 1481 17 - - 17 - 17 1482 18 - - 18 - 18 1483 19 - - 19 19 19 1484} 1485do_execsql_test joinB-108 { 1486 SELECT a, c, d, e, f, g 1487 FROM t1 1488 INNER JOIN (t2 FULL JOIN t3 USING(a)) USING(a) 1489 LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 1490 WHERE a<=18 1491 ORDER BY 1 NULLS FIRST; 1492} { 1493 11 31 - 31 31 - 1494 12 32 32 32 - - 1495 15 35 35 35 35 35 1496 17 37 - 37 - - 1497} 1498do_execsql_test joinB-109 { 1499 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1500 FROM t1 1501 INNER JOIN t2 USING(a) 1502 FULL JOIN t3 USING(a) 1503 LEFT JOIN t4 USING(a) 1504 RIGHT JOIN t5 USING(a) 1505 ORDER BY 1 NULLS FIRST; 1506} { 1507 15 15 15 15 15 15 1508 17 - - 17 - 17 1509 18 - - 18 - 18 1510 19 - - 19 19 19 1511} 1512do_execsql_test joinB-110 { 1513 SELECT a, b, c, d, e, f, g 1514 FROM t1 1515 INNER JOIN t2 USING(a,b) 1516 FULL JOIN t3 USING(a,b) 1517 LEFT JOIN t4 USING(a,b) 1518 RIGHT JOIN t5 USING(a,b) 1519 WHERE d<>33 OR d IS NULL 1520 ORDER BY 1 NULLS FIRST; 1521} { 1522 15 25 35 35 35 35 35 1523 17 27 - - 37 - 37 1524 18 28 - - 38 - 38 1525 19 29 - - 39 39 39 1526} 1527do_execsql_test joinB-111 { 1528 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1529 FROM t1 1530 INNER JOIN t2 USING(a) 1531 FULL JOIN t3 USING(a) 1532 LEFT JOIN t4 USING(a) 1533 FULL JOIN t5 USING(a) 1534 ORDER BY 1 NULLS FIRST; 1535} { 1536 - - - - - - 1537 11 - - 11 11 - 1538 12 12 12 12 - - 1539 13 - - 13 13 - 1540 15 15 15 15 15 15 1541 17 - - 17 - 17 1542 18 - - 18 - 18 1543 19 - - 19 19 19 1544} 1545do_execsql_test joinB-112 { 1546 SELECT b, c, d, e, f, g 1547 FROM t1 1548 NATURAL INNER JOIN t2 1549 NATURAL FULL JOIN t3 1550 NATURAL LEFT JOIN t4 1551 NATURAL FULL JOIN t5 1552 WHERE b BETWEEN 12 AND 17 1553 ORDER BY 1 NULLS FIRST; 1554} { 1555} 1556do_execsql_test joinB-113 { 1557 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1558 FROM t1 1559 INNER JOIN t2 USING(a) 1560 FULL JOIN t3 USING(a) 1561 RIGHT JOIN t4 USING(a) 1562 INNER JOIN t5 USING(a) 1563 ORDER BY 1 NULLS FIRST; 1564} { 1565 15 15 15 15 15 15 1566 19 - - 19 19 19 1567} 1568do_execsql_test joinB-114 { 1569 SELECT a, c, d, e, f, g 1570 FROM t1 1571 INNER JOIN t2 USING(a,b) 1572 FULL JOIN t3 USING(a,b) 1573 RIGHT JOIN t4 USING(a,b) 1574 INNER JOIN t5 USING(a,b) 1575 WHERE a<>13 1576 ORDER BY 1 NULLS FIRST; 1577} { 1578 15 35 35 35 35 35 1579 19 - - 39 39 39 1580} 1581do_execsql_test joinB-115 { 1582 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1583 FROM t1 1584 INNER JOIN t2 USING(a) 1585 FULL JOIN t3 USING(a) 1586 RIGHT JOIN t4 USING(a) 1587 LEFT JOIN t5 USING(a) 1588 ORDER BY 1 NULLS FIRST; 1589} { 1590 11 - - 11 11 - 1591 13 - - 13 13 - 1592 15 15 15 15 15 15 1593 19 - - 19 19 19 1594} 1595do_execsql_test joinB-116 { 1596 SELECT a, c, d, e, f, g 1597 FROM t1 1598 INNER JOIN (t2 FULL JOIN t3 USING(a)) USING(a) 1599 RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 1600 WHERE a<=18 1601 ORDER BY 1 NULLS FIRST; 1602} { 1603 11 31 - 31 31 - 1604 13 - - - 33 - 1605 15 35 35 35 35 35 1606} 1607do_execsql_test joinB-117 { 1608 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1609 FROM t1 1610 INNER JOIN t2 USING(a) 1611 FULL JOIN t3 USING(a) 1612 RIGHT JOIN t4 USING(a) 1613 RIGHT JOIN t5 USING(a) 1614 ORDER BY 1 NULLS FIRST; 1615} { 1616 15 15 15 15 15 15 1617 17 - - - - 17 1618 18 - - - - 18 1619 19 - - 19 19 19 1620} 1621do_execsql_test joinB-118 { 1622 SELECT a, b, c, d, e, f, g 1623 FROM t1 1624 INNER JOIN t2 USING(a,b) 1625 FULL JOIN t3 USING(a,b) 1626 RIGHT JOIN t4 USING(a,b) 1627 RIGHT JOIN t5 USING(a,b) 1628 WHERE d<>33 OR d IS NULL 1629 ORDER BY 1 NULLS FIRST; 1630} { 1631 15 25 35 35 35 35 35 1632 17 27 - - - - 37 1633 18 28 - - - - 38 1634 19 29 - - 39 39 39 1635} 1636do_execsql_test joinB-119 { 1637 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1638 FROM t1 1639 INNER JOIN t2 USING(a) 1640 FULL JOIN t3 USING(a) 1641 RIGHT JOIN t4 USING(a) 1642 FULL JOIN t5 USING(a) 1643 ORDER BY 1 NULLS FIRST; 1644} { 1645 11 - - 11 11 - 1646 13 - - 13 13 - 1647 15 15 15 15 15 15 1648 17 - - - - 17 1649 18 - - - - 18 1650 19 - - 19 19 19 1651} 1652do_execsql_test joinB-120 { 1653 SELECT b, c, d, e, f, g 1654 FROM t1 1655 NATURAL INNER JOIN t2 1656 NATURAL FULL JOIN t3 1657 NATURAL RIGHT JOIN t4 1658 NATURAL FULL JOIN t5 1659 WHERE b BETWEEN 12 AND 17 1660 ORDER BY 1 NULLS FIRST; 1661} { 1662} 1663do_execsql_test joinB-121 { 1664 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1665 FROM t1 1666 INNER JOIN t2 USING(a) 1667 FULL JOIN t3 USING(a) 1668 FULL JOIN t4 USING(a) 1669 INNER JOIN t5 USING(a) 1670 ORDER BY 1 NULLS FIRST; 1671} { 1672 15 15 15 15 15 15 1673 17 - - 17 - 17 1674 18 - - 18 - 18 1675 19 - - 19 19 19 1676} 1677do_execsql_test joinB-122 { 1678 SELECT a, c, d, e, f, g 1679 FROM t1 1680 INNER JOIN t2 USING(a,b) 1681 FULL JOIN t3 USING(a,b) 1682 FULL JOIN t4 USING(a,b) 1683 INNER JOIN t5 USING(a,b) 1684 WHERE a<>13 1685 ORDER BY 1 NULLS FIRST; 1686} { 1687 15 35 35 35 35 35 1688 17 - - 37 - 37 1689 18 - - 38 - 38 1690 19 - - 39 39 39 1691} 1692do_execsql_test joinB-123 { 1693 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1694 FROM t1 1695 INNER JOIN t2 USING(a) 1696 FULL JOIN t3 USING(a) 1697 FULL JOIN t4 USING(a) 1698 LEFT JOIN t5 USING(a) 1699 ORDER BY 1 NULLS FIRST; 1700} { 1701 - - - - - - 1702 11 - - 11 11 - 1703 12 12 12 12 - - 1704 13 - - 13 13 - 1705 15 15 15 15 15 15 1706 17 - - 17 - 17 1707 18 - - 18 - 18 1708 19 - - 19 19 19 1709} 1710do_execsql_test joinB-124 { 1711 SELECT a, c, d, e, f, g 1712 FROM t1 1713 INNER JOIN (t2 FULL JOIN t3 USING(a)) USING(a) 1714 FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 1715 WHERE a<=18 1716 ORDER BY 1 NULLS FIRST; 1717} { 1718 11 31 - 31 31 - 1719 12 32 32 32 - - 1720 13 - - - 33 - 1721 15 35 35 35 35 35 1722 17 37 - 37 - - 1723} 1724do_execsql_test joinB-125 { 1725 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1726 FROM t1 1727 INNER JOIN t2 USING(a) 1728 FULL JOIN t3 USING(a) 1729 FULL JOIN t4 USING(a) 1730 RIGHT JOIN t5 USING(a) 1731 ORDER BY 1 NULLS FIRST; 1732} { 1733 15 15 15 15 15 15 1734 17 - - 17 - 17 1735 18 - - 18 - 18 1736 19 - - 19 19 19 1737} 1738do_execsql_test joinB-126 { 1739 SELECT a, b, c, d, e, f, g 1740 FROM t1 1741 INNER JOIN t2 USING(a,b) 1742 FULL JOIN t3 USING(a,b) 1743 FULL JOIN t4 USING(a,b) 1744 RIGHT JOIN t5 USING(a,b) 1745 WHERE d<>33 OR d IS NULL 1746 ORDER BY 1 NULLS FIRST; 1747} { 1748 15 25 35 35 35 35 35 1749 17 27 - - 37 - 37 1750 18 28 - - 38 - 38 1751 19 29 - - 39 39 39 1752} 1753do_execsql_test joinB-127 { 1754 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1755 FROM t1 1756 INNER JOIN t2 USING(a) 1757 FULL JOIN t3 USING(a) 1758 FULL JOIN t4 USING(a) 1759 FULL JOIN t5 USING(a) 1760 ORDER BY 1 NULLS FIRST; 1761} { 1762 - - - - - - 1763 11 - - 11 11 - 1764 12 12 12 12 - - 1765 13 - - 13 13 - 1766 15 15 15 15 15 15 1767 17 - - 17 - 17 1768 18 - - 18 - 18 1769 19 - - 19 19 19 1770} 1771do_execsql_test joinB-128 { 1772 SELECT b, c, d, e, f, g 1773 FROM t1 1774 NATURAL INNER JOIN t2 1775 NATURAL FULL JOIN t3 1776 NATURAL FULL JOIN t4 1777 NATURAL FULL JOIN t5 1778 WHERE b BETWEEN 12 AND 17 1779 ORDER BY 1 NULLS FIRST; 1780} { 1781} 1782do_execsql_test joinB-129 { 1783 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1784 FROM t1 1785 LEFT JOIN t2 USING(a) 1786 INNER JOIN t3 USING(a) 1787 INNER JOIN t4 USING(a) 1788 INNER JOIN t5 USING(a) 1789 ORDER BY 1 NULLS FIRST; 1790} { 1791 15 15 15 15 15 15 1792} 1793do_execsql_test joinB-130 { 1794 SELECT a, c, d, e, f, g 1795 FROM t1 1796 LEFT JOIN t2 USING(a,b) 1797 INNER JOIN t3 USING(a,b) 1798 INNER JOIN t4 USING(a,b) 1799 INNER JOIN t5 USING(a,b) 1800 WHERE a<>13 1801 ORDER BY 1 NULLS FIRST; 1802} { 1803 15 35 35 35 35 35 1804} 1805do_execsql_test joinB-131 { 1806 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1807 FROM t1 1808 LEFT JOIN t2 USING(a) 1809 INNER JOIN t3 USING(a) 1810 INNER JOIN t4 USING(a) 1811 LEFT JOIN t5 USING(a) 1812 ORDER BY 1 NULLS FIRST; 1813} { 1814 11 11 - 11 11 - 1815 15 15 15 15 15 15 1816} 1817do_execsql_test joinB-132 { 1818 SELECT a, c, d, e, f, g 1819 FROM t1 1820 LEFT JOIN (t2 INNER JOIN t3 USING(a)) USING(a) 1821 INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 1822 WHERE a<=18 1823 ORDER BY 1 NULLS FIRST; 1824} { 1825 11 31 - - 31 - 1826 15 35 35 35 35 35 1827} 1828do_execsql_test joinB-133 { 1829 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1830 FROM t1 1831 LEFT JOIN t2 USING(a) 1832 INNER JOIN t3 USING(a) 1833 INNER JOIN t4 USING(a) 1834 RIGHT JOIN t5 USING(a) 1835 ORDER BY 1 NULLS FIRST; 1836} { 1837 15 15 15 15 15 15 1838 17 - - - - 17 1839 18 - - - - 18 1840 19 - - - - 19 1841} 1842do_execsql_test joinB-134 { 1843 SELECT a, b, c, d, e, f, g 1844 FROM t1 1845 LEFT JOIN t2 USING(a,b) 1846 INNER JOIN t3 USING(a,b) 1847 INNER JOIN t4 USING(a,b) 1848 RIGHT JOIN t5 USING(a,b) 1849 WHERE d<>33 OR d IS NULL 1850 ORDER BY 1 NULLS FIRST; 1851} { 1852 15 25 35 35 35 35 35 1853 17 27 - - - - 37 1854 18 28 - - - - 38 1855 19 29 - - - - 39 1856} 1857do_execsql_test joinB-135 { 1858 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1859 FROM t1 1860 LEFT JOIN t2 USING(a) 1861 INNER JOIN t3 USING(a) 1862 INNER JOIN t4 USING(a) 1863 FULL JOIN t5 USING(a) 1864 ORDER BY 1 NULLS FIRST; 1865} { 1866 11 11 - 11 11 - 1867 15 15 15 15 15 15 1868 17 - - - - 17 1869 18 - - - - 18 1870 19 - - - - 19 1871} 1872do_execsql_test joinB-136 { 1873 SELECT b, c, d, e, f, g 1874 FROM t1 1875 NATURAL LEFT JOIN t2 1876 NATURAL INNER JOIN t3 1877 NATURAL INNER JOIN t4 1878 NATURAL FULL JOIN t5 1879 WHERE b BETWEEN 12 AND 17 1880 ORDER BY 1 NULLS FIRST; 1881} { 1882} 1883do_execsql_test joinB-137 { 1884 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1885 FROM t1 1886 LEFT JOIN t2 USING(a) 1887 INNER JOIN t3 USING(a) 1888 LEFT JOIN t4 USING(a) 1889 INNER JOIN t5 USING(a) 1890 ORDER BY 1 NULLS FIRST; 1891} { 1892 15 15 15 15 15 15 1893 17 17 - 17 - 17 1894} 1895do_execsql_test joinB-138 { 1896 SELECT a, c, d, e, f, g 1897 FROM t1 1898 LEFT JOIN t2 USING(a,b) 1899 INNER JOIN t3 USING(a,b) 1900 LEFT JOIN t4 USING(a,b) 1901 INNER JOIN t5 USING(a,b) 1902 WHERE a<>13 1903 ORDER BY 1 NULLS FIRST; 1904} { 1905 15 35 35 35 35 35 1906 17 37 - 37 - 37 1907} 1908do_execsql_test joinB-139 { 1909 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1910 FROM t1 1911 LEFT JOIN t2 USING(a) 1912 INNER JOIN t3 USING(a) 1913 LEFT JOIN t4 USING(a) 1914 LEFT JOIN t5 USING(a) 1915 ORDER BY 1 NULLS FIRST; 1916} { 1917 11 11 - 11 11 - 1918 12 12 12 12 - - 1919 15 15 15 15 15 15 1920 17 17 - 17 - 17 1921} 1922do_execsql_test joinB-140 { 1923 SELECT a, c, d, e, f, g 1924 FROM t1 1925 LEFT JOIN (t2 INNER JOIN t3 USING(a)) USING(a) 1926 LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 1927 WHERE a<=18 1928 ORDER BY 1 NULLS FIRST; 1929} { 1930 11 31 - - 31 - 1931 12 32 32 32 - - 1932 15 35 35 35 35 35 1933 17 37 - - - - 1934} 1935do_execsql_test joinB-141 { 1936 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1937 FROM t1 1938 LEFT JOIN t2 USING(a) 1939 INNER JOIN t3 USING(a) 1940 LEFT JOIN t4 USING(a) 1941 RIGHT JOIN t5 USING(a) 1942 ORDER BY 1 NULLS FIRST; 1943} { 1944 15 15 15 15 15 15 1945 17 17 - 17 - 17 1946 18 - - - - 18 1947 19 - - - - 19 1948} 1949do_execsql_test joinB-142 { 1950 SELECT a, b, c, d, e, f, g 1951 FROM t1 1952 LEFT JOIN t2 USING(a,b) 1953 INNER JOIN t3 USING(a,b) 1954 LEFT JOIN t4 USING(a,b) 1955 RIGHT JOIN t5 USING(a,b) 1956 WHERE d<>33 OR d IS NULL 1957 ORDER BY 1 NULLS FIRST; 1958} { 1959 15 25 35 35 35 35 35 1960 17 27 37 - 37 - 37 1961 18 28 - - - - 38 1962 19 29 - - - - 39 1963} 1964do_execsql_test joinB-143 { 1965 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1966 FROM t1 1967 LEFT JOIN t2 USING(a) 1968 INNER JOIN t3 USING(a) 1969 LEFT JOIN t4 USING(a) 1970 FULL JOIN t5 USING(a) 1971 ORDER BY 1 NULLS FIRST; 1972} { 1973 11 11 - 11 11 - 1974 12 12 12 12 - - 1975 15 15 15 15 15 15 1976 17 17 - 17 - 17 1977 18 - - - - 18 1978 19 - - - - 19 1979} 1980do_execsql_test joinB-144 { 1981 SELECT b, c, d, e, f, g 1982 FROM t1 1983 NATURAL LEFT JOIN t2 1984 NATURAL INNER JOIN t3 1985 NATURAL LEFT JOIN t4 1986 NATURAL FULL JOIN t5 1987 WHERE b BETWEEN 12 AND 17 1988 ORDER BY 1 NULLS FIRST; 1989} { 1990} 1991do_execsql_test joinB-145 { 1992 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 1993 FROM t1 1994 LEFT JOIN t2 USING(a) 1995 INNER JOIN t3 USING(a) 1996 RIGHT JOIN t4 USING(a) 1997 INNER JOIN t5 USING(a) 1998 ORDER BY 1 NULLS FIRST; 1999} { 2000 15 15 15 15 15 15 2001 19 - - - 19 19 2002} 2003do_execsql_test joinB-146 { 2004 SELECT a, c, d, e, f, g 2005 FROM t1 2006 LEFT JOIN t2 USING(a,b) 2007 INNER JOIN t3 USING(a,b) 2008 RIGHT JOIN t4 USING(a,b) 2009 INNER JOIN t5 USING(a,b) 2010 WHERE a<>13 2011 ORDER BY 1 NULLS FIRST; 2012} { 2013 15 35 35 35 35 35 2014 19 - - - 39 39 2015} 2016do_execsql_test joinB-147 { 2017 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2018 FROM t1 2019 LEFT JOIN t2 USING(a) 2020 INNER JOIN t3 USING(a) 2021 RIGHT JOIN t4 USING(a) 2022 LEFT JOIN t5 USING(a) 2023 ORDER BY 1 NULLS FIRST; 2024} { 2025 11 11 - 11 11 - 2026 13 - - - 13 - 2027 15 15 15 15 15 15 2028 19 - - - 19 19 2029} 2030do_execsql_test joinB-148 { 2031 SELECT a, c, d, e, f, g 2032 FROM t1 2033 LEFT JOIN (t2 INNER JOIN t3 USING(a)) USING(a) 2034 RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 2035 WHERE a<=18 2036 ORDER BY 1 NULLS FIRST; 2037} { 2038 11 31 - - 31 - 2039 13 - - - 33 - 2040 15 35 35 35 35 35 2041} 2042do_execsql_test joinB-149 { 2043 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2044 FROM t1 2045 LEFT JOIN t2 USING(a) 2046 INNER JOIN t3 USING(a) 2047 RIGHT JOIN t4 USING(a) 2048 RIGHT JOIN t5 USING(a) 2049 ORDER BY 1 NULLS FIRST; 2050} { 2051 15 15 15 15 15 15 2052 17 - - - - 17 2053 18 - - - - 18 2054 19 - - - 19 19 2055} 2056do_execsql_test joinB-150 { 2057 SELECT a, b, c, d, e, f, g 2058 FROM t1 2059 LEFT JOIN t2 USING(a,b) 2060 INNER JOIN t3 USING(a,b) 2061 RIGHT JOIN t4 USING(a,b) 2062 RIGHT JOIN t5 USING(a,b) 2063 WHERE d<>33 OR d IS NULL 2064 ORDER BY 1 NULLS FIRST; 2065} { 2066 15 25 35 35 35 35 35 2067 17 27 - - - - 37 2068 18 28 - - - - 38 2069 19 29 - - - 39 39 2070} 2071do_execsql_test joinB-151 { 2072 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2073 FROM t1 2074 LEFT JOIN t2 USING(a) 2075 INNER JOIN t3 USING(a) 2076 RIGHT JOIN t4 USING(a) 2077 FULL JOIN t5 USING(a) 2078 ORDER BY 1 NULLS FIRST; 2079} { 2080 11 11 - 11 11 - 2081 13 - - - 13 - 2082 15 15 15 15 15 15 2083 17 - - - - 17 2084 18 - - - - 18 2085 19 - - - 19 19 2086} 2087do_execsql_test joinB-152 { 2088 SELECT b, c, d, e, f, g 2089 FROM t1 2090 NATURAL LEFT JOIN t2 2091 NATURAL INNER JOIN t3 2092 NATURAL RIGHT JOIN t4 2093 NATURAL FULL JOIN t5 2094 WHERE b BETWEEN 12 AND 17 2095 ORDER BY 1 NULLS FIRST; 2096} { 2097} 2098do_execsql_test joinB-153 { 2099 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2100 FROM t1 2101 LEFT JOIN t2 USING(a) 2102 INNER JOIN t3 USING(a) 2103 FULL JOIN t4 USING(a) 2104 INNER JOIN t5 USING(a) 2105 ORDER BY 1 NULLS FIRST; 2106} { 2107 15 15 15 15 15 15 2108 17 17 - 17 - 17 2109 19 - - - 19 19 2110} 2111do_execsql_test joinB-154 { 2112 SELECT a, c, d, e, f, g 2113 FROM t1 2114 LEFT JOIN t2 USING(a,b) 2115 INNER JOIN t3 USING(a,b) 2116 FULL JOIN t4 USING(a,b) 2117 INNER JOIN t5 USING(a,b) 2118 WHERE a<>13 2119 ORDER BY 1 NULLS FIRST; 2120} { 2121 15 35 35 35 35 35 2122 17 37 - 37 - 37 2123 19 - - - 39 39 2124} 2125do_execsql_test joinB-155 { 2126 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2127 FROM t1 2128 LEFT JOIN t2 USING(a) 2129 INNER JOIN t3 USING(a) 2130 FULL JOIN t4 USING(a) 2131 LEFT JOIN t5 USING(a) 2132 ORDER BY 1 NULLS FIRST; 2133} { 2134 11 11 - 11 11 - 2135 12 12 12 12 - - 2136 13 - - - 13 - 2137 15 15 15 15 15 15 2138 17 17 - 17 - 17 2139 19 - - - 19 19 2140} 2141do_execsql_test joinB-156 { 2142 SELECT a, c, d, e, f, g 2143 FROM t1 2144 LEFT JOIN (t2 INNER JOIN t3 USING(a)) USING(a) 2145 FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 2146 WHERE a<=18 2147 ORDER BY 1 NULLS FIRST; 2148} { 2149 11 31 - - 31 - 2150 12 32 32 32 - - 2151 13 - - - 33 - 2152 15 35 35 35 35 35 2153 17 37 - - - - 2154} 2155do_execsql_test joinB-157 { 2156 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2157 FROM t1 2158 LEFT JOIN t2 USING(a) 2159 INNER JOIN t3 USING(a) 2160 FULL JOIN t4 USING(a) 2161 RIGHT JOIN t5 USING(a) 2162 ORDER BY 1 NULLS FIRST; 2163} { 2164 15 15 15 15 15 15 2165 17 17 - 17 - 17 2166 18 - - - - 18 2167 19 - - - 19 19 2168} 2169do_execsql_test joinB-158 { 2170 SELECT a, b, c, d, e, f, g 2171 FROM t1 2172 LEFT JOIN t2 USING(a,b) 2173 INNER JOIN t3 USING(a,b) 2174 FULL JOIN t4 USING(a,b) 2175 RIGHT JOIN t5 USING(a,b) 2176 WHERE d<>33 OR d IS NULL 2177 ORDER BY 1 NULLS FIRST; 2178} { 2179 15 25 35 35 35 35 35 2180 17 27 37 - 37 - 37 2181 18 28 - - - - 38 2182 19 29 - - - 39 39 2183} 2184do_execsql_test joinB-159 { 2185 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2186 FROM t1 2187 LEFT JOIN t2 USING(a) 2188 INNER JOIN t3 USING(a) 2189 FULL JOIN t4 USING(a) 2190 FULL JOIN t5 USING(a) 2191 ORDER BY 1 NULLS FIRST; 2192} { 2193 11 11 - 11 11 - 2194 12 12 12 12 - - 2195 13 - - - 13 - 2196 15 15 15 15 15 15 2197 17 17 - 17 - 17 2198 18 - - - - 18 2199 19 - - - 19 19 2200} 2201do_execsql_test joinB-160 { 2202 SELECT b, c, d, e, f, g 2203 FROM t1 2204 NATURAL LEFT JOIN t2 2205 NATURAL INNER JOIN t3 2206 NATURAL FULL JOIN t4 2207 NATURAL FULL JOIN t5 2208 WHERE b BETWEEN 12 AND 17 2209 ORDER BY 1 NULLS FIRST; 2210} { 2211} 2212do_execsql_test joinB-161 { 2213 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2214 FROM t1 2215 LEFT JOIN t2 USING(a) 2216 LEFT JOIN t3 USING(a) 2217 INNER JOIN t4 USING(a) 2218 INNER JOIN t5 USING(a) 2219 ORDER BY 1 NULLS FIRST; 2220} { 2221 15 15 15 15 15 15 2222} 2223do_execsql_test joinB-162 { 2224 SELECT a, c, d, e, f, g 2225 FROM t1 2226 LEFT JOIN t2 USING(a,b) 2227 LEFT JOIN t3 USING(a,b) 2228 INNER JOIN t4 USING(a,b) 2229 INNER JOIN t5 USING(a,b) 2230 WHERE a<>13 2231 ORDER BY 1 NULLS FIRST; 2232} { 2233 15 35 35 35 35 35 2234} 2235do_execsql_test joinB-163 { 2236 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2237 FROM t1 2238 LEFT JOIN t2 USING(a) 2239 LEFT JOIN t3 USING(a) 2240 INNER JOIN t4 USING(a) 2241 LEFT JOIN t5 USING(a) 2242 ORDER BY 1 NULLS FIRST; 2243} { 2244 11 11 - 11 11 - 2245 15 15 15 15 15 15 2246} 2247do_execsql_test joinB-164 { 2248 SELECT a, c, d, e, f, g 2249 FROM t1 2250 LEFT JOIN (t2 LEFT JOIN t3 USING(a)) USING(a) 2251 INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 2252 WHERE a<=18 2253 ORDER BY 1 NULLS FIRST; 2254} { 2255 11 31 - - 31 - 2256 15 35 35 35 35 35 2257} 2258do_execsql_test joinB-165 { 2259 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2260 FROM t1 2261 LEFT JOIN t2 USING(a) 2262 LEFT JOIN t3 USING(a) 2263 INNER JOIN t4 USING(a) 2264 RIGHT JOIN t5 USING(a) 2265 ORDER BY 1 NULLS FIRST; 2266} { 2267 15 15 15 15 15 15 2268 17 - - - - 17 2269 18 - - - - 18 2270 19 - - - - 19 2271} 2272do_execsql_test joinB-166 { 2273 SELECT a, b, c, d, e, f, g 2274 FROM t1 2275 LEFT JOIN t2 USING(a,b) 2276 LEFT JOIN t3 USING(a,b) 2277 INNER JOIN t4 USING(a,b) 2278 RIGHT JOIN t5 USING(a,b) 2279 WHERE d<>33 OR d IS NULL 2280 ORDER BY 1 NULLS FIRST; 2281} { 2282 15 25 35 35 35 35 35 2283 17 27 - - - - 37 2284 18 28 - - - - 38 2285 19 29 - - - - 39 2286} 2287do_execsql_test joinB-167 { 2288 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2289 FROM t1 2290 LEFT JOIN t2 USING(a) 2291 LEFT JOIN t3 USING(a) 2292 INNER JOIN t4 USING(a) 2293 FULL JOIN t5 USING(a) 2294 ORDER BY 1 NULLS FIRST; 2295} { 2296 11 11 - 11 11 - 2297 15 15 15 15 15 15 2298 17 - - - - 17 2299 18 - - - - 18 2300 19 - - - - 19 2301} 2302do_execsql_test joinB-168 { 2303 SELECT b, c, d, e, f, g 2304 FROM t1 2305 NATURAL LEFT JOIN t2 2306 NATURAL LEFT JOIN t3 2307 NATURAL INNER JOIN t4 2308 NATURAL FULL JOIN t5 2309 WHERE b BETWEEN 12 AND 17 2310 ORDER BY 1 NULLS FIRST; 2311} { 2312} 2313do_execsql_test joinB-169 { 2314 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2315 FROM t1 2316 LEFT JOIN t2 USING(a) 2317 LEFT JOIN t3 USING(a) 2318 LEFT JOIN t4 USING(a) 2319 INNER JOIN t5 USING(a) 2320 ORDER BY 1 NULLS FIRST; 2321} { 2322 15 15 15 15 15 15 2323 17 17 - 17 - 17 2324} 2325do_execsql_test joinB-170 { 2326 SELECT a, c, d, e, f, g 2327 FROM t1 2328 LEFT JOIN t2 USING(a,b) 2329 LEFT JOIN t3 USING(a,b) 2330 LEFT JOIN t4 USING(a,b) 2331 INNER JOIN t5 USING(a,b) 2332 WHERE a<>13 2333 ORDER BY 1 NULLS FIRST; 2334} { 2335 15 35 35 35 35 35 2336 17 37 - 37 - 37 2337} 2338do_execsql_test joinB-171 { 2339 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2340 FROM t1 2341 LEFT JOIN t2 USING(a) 2342 LEFT JOIN t3 USING(a) 2343 LEFT JOIN t4 USING(a) 2344 LEFT JOIN t5 USING(a) 2345 ORDER BY 1 NULLS FIRST; 2346} { 2347 11 11 - 11 11 - 2348 12 12 12 12 - - 2349 15 15 15 15 15 15 2350 17 17 - 17 - 17 2351} 2352do_execsql_test joinB-172 { 2353 SELECT a, c, d, e, f, g 2354 FROM t1 2355 LEFT JOIN (t2 LEFT JOIN t3 USING(a)) USING(a) 2356 LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 2357 WHERE a<=18 2358 ORDER BY 1 NULLS FIRST; 2359} { 2360 11 31 - - 31 - 2361 12 32 32 32 - - 2362 15 35 35 35 35 35 2363 17 37 - - - - 2364} 2365do_execsql_test joinB-173 { 2366 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2367 FROM t1 2368 LEFT JOIN t2 USING(a) 2369 LEFT JOIN t3 USING(a) 2370 LEFT JOIN t4 USING(a) 2371 RIGHT JOIN t5 USING(a) 2372 ORDER BY 1 NULLS FIRST; 2373} { 2374 15 15 15 15 15 15 2375 17 17 - 17 - 17 2376 18 - - - - 18 2377 19 - - - - 19 2378} 2379do_execsql_test joinB-174 { 2380 SELECT a, b, c, d, e, f, g 2381 FROM t1 2382 LEFT JOIN t2 USING(a,b) 2383 LEFT JOIN t3 USING(a,b) 2384 LEFT JOIN t4 USING(a,b) 2385 RIGHT JOIN t5 USING(a,b) 2386 WHERE d<>33 OR d IS NULL 2387 ORDER BY 1 NULLS FIRST; 2388} { 2389 15 25 35 35 35 35 35 2390 17 27 37 - 37 - 37 2391 18 28 - - - - 38 2392 19 29 - - - - 39 2393} 2394do_execsql_test joinB-175 { 2395 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2396 FROM t1 2397 LEFT JOIN t2 USING(a) 2398 LEFT JOIN t3 USING(a) 2399 LEFT JOIN t4 USING(a) 2400 FULL JOIN t5 USING(a) 2401 ORDER BY 1 NULLS FIRST; 2402} { 2403 11 11 - 11 11 - 2404 12 12 12 12 - - 2405 15 15 15 15 15 15 2406 17 17 - 17 - 17 2407 18 - - - - 18 2408 19 - - - - 19 2409} 2410do_execsql_test joinB-176 { 2411 SELECT b, c, d, e, f, g 2412 FROM t1 2413 NATURAL LEFT JOIN t2 2414 NATURAL LEFT JOIN t3 2415 NATURAL LEFT JOIN t4 2416 NATURAL FULL JOIN t5 2417 WHERE b BETWEEN 12 AND 17 2418 ORDER BY 1 NULLS FIRST; 2419} { 2420} 2421do_execsql_test joinB-177 { 2422 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2423 FROM t1 2424 LEFT JOIN t2 USING(a) 2425 LEFT JOIN t3 USING(a) 2426 RIGHT JOIN t4 USING(a) 2427 INNER JOIN t5 USING(a) 2428 ORDER BY 1 NULLS FIRST; 2429} { 2430 15 15 15 15 15 15 2431 19 - - - 19 19 2432} 2433do_execsql_test joinB-178 { 2434 SELECT a, c, d, e, f, g 2435 FROM t1 2436 LEFT JOIN t2 USING(a,b) 2437 LEFT JOIN t3 USING(a,b) 2438 RIGHT JOIN t4 USING(a,b) 2439 INNER JOIN t5 USING(a,b) 2440 WHERE a<>13 2441 ORDER BY 1 NULLS FIRST; 2442} { 2443 15 35 35 35 35 35 2444 19 - - - 39 39 2445} 2446do_execsql_test joinB-179 { 2447 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2448 FROM t1 2449 LEFT JOIN t2 USING(a) 2450 LEFT JOIN t3 USING(a) 2451 RIGHT JOIN t4 USING(a) 2452 LEFT JOIN t5 USING(a) 2453 ORDER BY 1 NULLS FIRST; 2454} { 2455 11 11 - 11 11 - 2456 13 - - - 13 - 2457 15 15 15 15 15 15 2458 19 - - - 19 19 2459} 2460do_execsql_test joinB-180 { 2461 SELECT a, c, d, e, f, g 2462 FROM t1 2463 LEFT JOIN (t2 LEFT JOIN t3 USING(a)) USING(a) 2464 RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 2465 WHERE a<=18 2466 ORDER BY 1 NULLS FIRST; 2467} { 2468 11 31 - - 31 - 2469 13 - - - 33 - 2470 15 35 35 35 35 35 2471} 2472do_execsql_test joinB-181 { 2473 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2474 FROM t1 2475 LEFT JOIN t2 USING(a) 2476 LEFT JOIN t3 USING(a) 2477 RIGHT JOIN t4 USING(a) 2478 RIGHT JOIN t5 USING(a) 2479 ORDER BY 1 NULLS FIRST; 2480} { 2481 15 15 15 15 15 15 2482 17 - - - - 17 2483 18 - - - - 18 2484 19 - - - 19 19 2485} 2486do_execsql_test joinB-182 { 2487 SELECT a, b, c, d, e, f, g 2488 FROM t1 2489 LEFT JOIN t2 USING(a,b) 2490 LEFT JOIN t3 USING(a,b) 2491 RIGHT JOIN t4 USING(a,b) 2492 RIGHT JOIN t5 USING(a,b) 2493 WHERE d<>33 OR d IS NULL 2494 ORDER BY 1 NULLS FIRST; 2495} { 2496 15 25 35 35 35 35 35 2497 17 27 - - - - 37 2498 18 28 - - - - 38 2499 19 29 - - - 39 39 2500} 2501do_execsql_test joinB-183 { 2502 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2503 FROM t1 2504 LEFT JOIN t2 USING(a) 2505 LEFT JOIN t3 USING(a) 2506 RIGHT JOIN t4 USING(a) 2507 FULL JOIN t5 USING(a) 2508 ORDER BY 1 NULLS FIRST; 2509} { 2510 11 11 - 11 11 - 2511 13 - - - 13 - 2512 15 15 15 15 15 15 2513 17 - - - - 17 2514 18 - - - - 18 2515 19 - - - 19 19 2516} 2517do_execsql_test joinB-184 { 2518 SELECT b, c, d, e, f, g 2519 FROM t1 2520 NATURAL LEFT JOIN t2 2521 NATURAL LEFT JOIN t3 2522 NATURAL RIGHT JOIN t4 2523 NATURAL FULL JOIN t5 2524 WHERE b BETWEEN 12 AND 17 2525 ORDER BY 1 NULLS FIRST; 2526} { 2527} 2528do_execsql_test joinB-185 { 2529 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2530 FROM t1 2531 LEFT JOIN t2 USING(a) 2532 LEFT JOIN t3 USING(a) 2533 FULL JOIN t4 USING(a) 2534 INNER JOIN t5 USING(a) 2535 ORDER BY 1 NULLS FIRST; 2536} { 2537 15 15 15 15 15 15 2538 17 17 - 17 - 17 2539 19 - - - 19 19 2540} 2541do_execsql_test joinB-186 { 2542 SELECT a, c, d, e, f, g 2543 FROM t1 2544 LEFT JOIN t2 USING(a,b) 2545 LEFT JOIN t3 USING(a,b) 2546 FULL JOIN t4 USING(a,b) 2547 INNER JOIN t5 USING(a,b) 2548 WHERE a<>13 2549 ORDER BY 1 NULLS FIRST; 2550} { 2551 15 35 35 35 35 35 2552 17 37 - 37 - 37 2553 19 - - - 39 39 2554} 2555do_execsql_test joinB-187 { 2556 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2557 FROM t1 2558 LEFT JOIN t2 USING(a) 2559 LEFT JOIN t3 USING(a) 2560 FULL JOIN t4 USING(a) 2561 LEFT JOIN t5 USING(a) 2562 ORDER BY 1 NULLS FIRST; 2563} { 2564 11 11 - 11 11 - 2565 12 12 12 12 - - 2566 13 - - - 13 - 2567 15 15 15 15 15 15 2568 17 17 - 17 - 17 2569 19 - - - 19 19 2570} 2571do_execsql_test joinB-188 { 2572 SELECT a, c, d, e, f, g 2573 FROM t1 2574 LEFT JOIN (t2 LEFT JOIN t3 USING(a)) USING(a) 2575 FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 2576 WHERE a<=18 2577 ORDER BY 1 NULLS FIRST; 2578} { 2579 11 31 - - 31 - 2580 12 32 32 32 - - 2581 13 - - - 33 - 2582 15 35 35 35 35 35 2583 17 37 - - - - 2584} 2585do_execsql_test joinB-189 { 2586 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2587 FROM t1 2588 LEFT JOIN t2 USING(a) 2589 LEFT JOIN t3 USING(a) 2590 FULL JOIN t4 USING(a) 2591 RIGHT JOIN t5 USING(a) 2592 ORDER BY 1 NULLS FIRST; 2593} { 2594 15 15 15 15 15 15 2595 17 17 - 17 - 17 2596 18 - - - - 18 2597 19 - - - 19 19 2598} 2599do_execsql_test joinB-190 { 2600 SELECT a, b, c, d, e, f, g 2601 FROM t1 2602 LEFT JOIN t2 USING(a,b) 2603 LEFT JOIN t3 USING(a,b) 2604 FULL JOIN t4 USING(a,b) 2605 RIGHT JOIN t5 USING(a,b) 2606 WHERE d<>33 OR d IS NULL 2607 ORDER BY 1 NULLS FIRST; 2608} { 2609 15 25 35 35 35 35 35 2610 17 27 37 - 37 - 37 2611 18 28 - - - - 38 2612 19 29 - - - 39 39 2613} 2614do_execsql_test joinB-191 { 2615 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2616 FROM t1 2617 LEFT JOIN t2 USING(a) 2618 LEFT JOIN t3 USING(a) 2619 FULL JOIN t4 USING(a) 2620 FULL JOIN t5 USING(a) 2621 ORDER BY 1 NULLS FIRST; 2622} { 2623 11 11 - 11 11 - 2624 12 12 12 12 - - 2625 13 - - - 13 - 2626 15 15 15 15 15 15 2627 17 17 - 17 - 17 2628 18 - - - - 18 2629 19 - - - 19 19 2630} 2631do_execsql_test joinB-192 { 2632 SELECT b, c, d, e, f, g 2633 FROM t1 2634 NATURAL LEFT JOIN t2 2635 NATURAL LEFT JOIN t3 2636 NATURAL FULL JOIN t4 2637 NATURAL FULL JOIN t5 2638 WHERE b BETWEEN 12 AND 17 2639 ORDER BY 1 NULLS FIRST; 2640} { 2641} 2642do_execsql_test joinB-193 { 2643 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2644 FROM t1 2645 LEFT JOIN t2 USING(a) 2646 RIGHT JOIN t3 USING(a) 2647 INNER JOIN t4 USING(a) 2648 INNER JOIN t5 USING(a) 2649 ORDER BY 1 NULLS FIRST; 2650} { 2651 15 15 15 15 15 15 2652 19 - - 19 19 19 2653} 2654do_execsql_test joinB-194 { 2655 SELECT a, c, d, e, f, g 2656 FROM t1 2657 LEFT JOIN t2 USING(a,b) 2658 RIGHT JOIN t3 USING(a,b) 2659 INNER JOIN t4 USING(a,b) 2660 INNER JOIN t5 USING(a,b) 2661 WHERE a<>13 2662 ORDER BY 1 NULLS FIRST; 2663} { 2664 15 35 35 35 35 35 2665 19 - - 39 39 39 2666} 2667do_execsql_test joinB-195 { 2668 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2669 FROM t1 2670 LEFT JOIN t2 USING(a) 2671 RIGHT JOIN t3 USING(a) 2672 INNER JOIN t4 USING(a) 2673 LEFT JOIN t5 USING(a) 2674 ORDER BY 1 NULLS FIRST; 2675} { 2676 11 11 - 11 11 - 2677 13 - - 13 13 - 2678 15 15 15 15 15 15 2679 19 - - 19 19 19 2680} 2681do_execsql_test joinB-196 { 2682 SELECT a, c, d, e, f, g 2683 FROM t1 2684 LEFT JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a) 2685 INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 2686 WHERE a<=18 2687 ORDER BY 1 NULLS FIRST; 2688} { 2689 11 31 - 31 31 - 2690 15 35 35 35 35 35 2691} 2692do_execsql_test joinB-197 { 2693 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2694 FROM t1 2695 LEFT JOIN t2 USING(a) 2696 RIGHT JOIN t3 USING(a) 2697 INNER JOIN t4 USING(a) 2698 RIGHT JOIN t5 USING(a) 2699 ORDER BY 1 NULLS FIRST; 2700} { 2701 15 15 15 15 15 15 2702 17 - - - - 17 2703 18 - - - - 18 2704 19 - - 19 19 19 2705} 2706do_execsql_test joinB-198 { 2707 SELECT a, b, c, d, e, f, g 2708 FROM t1 2709 LEFT JOIN t2 USING(a,b) 2710 RIGHT JOIN t3 USING(a,b) 2711 INNER JOIN t4 USING(a,b) 2712 RIGHT JOIN t5 USING(a,b) 2713 WHERE d<>33 OR d IS NULL 2714 ORDER BY 1 NULLS FIRST; 2715} { 2716 15 25 35 35 35 35 35 2717 17 27 - - - - 37 2718 18 28 - - - - 38 2719 19 29 - - 39 39 39 2720} 2721do_execsql_test joinB-199 { 2722 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2723 FROM t1 2724 LEFT JOIN t2 USING(a) 2725 RIGHT JOIN t3 USING(a) 2726 INNER JOIN t4 USING(a) 2727 FULL JOIN t5 USING(a) 2728 ORDER BY 1 NULLS FIRST; 2729} { 2730 11 11 - 11 11 - 2731 13 - - 13 13 - 2732 15 15 15 15 15 15 2733 17 - - - - 17 2734 18 - - - - 18 2735 19 - - 19 19 19 2736} 2737do_execsql_test joinB-200 { 2738 SELECT b, c, d, e, f, g 2739 FROM t1 2740 NATURAL LEFT JOIN t2 2741 NATURAL RIGHT JOIN t3 2742 NATURAL INNER JOIN t4 2743 NATURAL FULL JOIN t5 2744 WHERE b BETWEEN 12 AND 17 2745 ORDER BY 1 NULLS FIRST; 2746} { 2747} 2748do_execsql_test joinB-201 { 2749 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2750 FROM t1 2751 LEFT JOIN t2 USING(a) 2752 RIGHT JOIN t3 USING(a) 2753 LEFT JOIN t4 USING(a) 2754 INNER JOIN t5 USING(a) 2755 ORDER BY 1 NULLS FIRST; 2756} { 2757 15 15 15 15 15 15 2758 17 17 - 17 - 17 2759 18 - - 18 - 18 2760 19 - - 19 19 19 2761} 2762do_execsql_test joinB-202 { 2763 SELECT a, c, d, e, f, g 2764 FROM t1 2765 LEFT JOIN t2 USING(a,b) 2766 RIGHT JOIN t3 USING(a,b) 2767 LEFT JOIN t4 USING(a,b) 2768 INNER JOIN t5 USING(a,b) 2769 WHERE a<>13 2770 ORDER BY 1 NULLS FIRST; 2771} { 2772 15 35 35 35 35 35 2773 17 37 - 37 - 37 2774 18 - - 38 - 38 2775 19 - - 39 39 39 2776} 2777do_execsql_test joinB-203 { 2778 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2779 FROM t1 2780 LEFT JOIN t2 USING(a) 2781 RIGHT JOIN t3 USING(a) 2782 LEFT JOIN t4 USING(a) 2783 LEFT JOIN t5 USING(a) 2784 ORDER BY 1 NULLS FIRST; 2785} { 2786 - - - - - - 2787 11 11 - 11 11 - 2788 12 12 12 12 - - 2789 13 - - 13 13 - 2790 15 15 15 15 15 15 2791 17 17 - 17 - 17 2792 18 - - 18 - 18 2793 19 - - 19 19 19 2794} 2795do_execsql_test joinB-204 { 2796 SELECT a, c, d, e, f, g 2797 FROM t1 2798 LEFT JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a) 2799 LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 2800 WHERE a<=18 2801 ORDER BY 1 NULLS FIRST; 2802} { 2803 11 31 - 31 31 - 2804 12 32 32 32 - - 2805 15 35 35 35 35 35 2806 17 37 - 37 - - 2807} 2808do_execsql_test joinB-205 { 2809 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2810 FROM t1 2811 LEFT JOIN t2 USING(a) 2812 RIGHT JOIN t3 USING(a) 2813 LEFT JOIN t4 USING(a) 2814 RIGHT JOIN t5 USING(a) 2815 ORDER BY 1 NULLS FIRST; 2816} { 2817 15 15 15 15 15 15 2818 17 17 - 17 - 17 2819 18 - - 18 - 18 2820 19 - - 19 19 19 2821} 2822do_execsql_test joinB-206 { 2823 SELECT a, b, c, d, e, f, g 2824 FROM t1 2825 LEFT JOIN t2 USING(a,b) 2826 RIGHT JOIN t3 USING(a,b) 2827 LEFT JOIN t4 USING(a,b) 2828 RIGHT JOIN t5 USING(a,b) 2829 WHERE d<>33 OR d IS NULL 2830 ORDER BY 1 NULLS FIRST; 2831} { 2832 15 25 35 35 35 35 35 2833 17 27 37 - 37 - 37 2834 18 28 - - 38 - 38 2835 19 29 - - 39 39 39 2836} 2837do_execsql_test joinB-207 { 2838 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2839 FROM t1 2840 LEFT JOIN t2 USING(a) 2841 RIGHT JOIN t3 USING(a) 2842 LEFT JOIN t4 USING(a) 2843 FULL JOIN t5 USING(a) 2844 ORDER BY 1 NULLS FIRST; 2845} { 2846 - - - - - - 2847 11 11 - 11 11 - 2848 12 12 12 12 - - 2849 13 - - 13 13 - 2850 15 15 15 15 15 15 2851 17 17 - 17 - 17 2852 18 - - 18 - 18 2853 19 - - 19 19 19 2854} 2855do_execsql_test joinB-208 { 2856 SELECT b, c, d, e, f, g 2857 FROM t1 2858 NATURAL LEFT JOIN t2 2859 NATURAL RIGHT JOIN t3 2860 NATURAL LEFT JOIN t4 2861 NATURAL FULL JOIN t5 2862 WHERE b BETWEEN 12 AND 17 2863 ORDER BY 1 NULLS FIRST; 2864} { 2865} 2866do_execsql_test joinB-209 { 2867 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2868 FROM t1 2869 LEFT JOIN t2 USING(a) 2870 RIGHT JOIN t3 USING(a) 2871 RIGHT JOIN t4 USING(a) 2872 INNER JOIN t5 USING(a) 2873 ORDER BY 1 NULLS FIRST; 2874} { 2875 15 15 15 15 15 15 2876 19 - - 19 19 19 2877} 2878do_execsql_test joinB-210 { 2879 SELECT a, c, d, e, f, g 2880 FROM t1 2881 LEFT JOIN t2 USING(a,b) 2882 RIGHT JOIN t3 USING(a,b) 2883 RIGHT JOIN t4 USING(a,b) 2884 INNER JOIN t5 USING(a,b) 2885 WHERE a<>13 2886 ORDER BY 1 NULLS FIRST; 2887} { 2888 15 35 35 35 35 35 2889 19 - - 39 39 39 2890} 2891do_execsql_test joinB-211 { 2892 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2893 FROM t1 2894 LEFT JOIN t2 USING(a) 2895 RIGHT JOIN t3 USING(a) 2896 RIGHT JOIN t4 USING(a) 2897 LEFT JOIN t5 USING(a) 2898 ORDER BY 1 NULLS FIRST; 2899} { 2900 11 11 - 11 11 - 2901 13 - - 13 13 - 2902 15 15 15 15 15 15 2903 19 - - 19 19 19 2904} 2905do_execsql_test joinB-212 { 2906 SELECT a, c, d, e, f, g 2907 FROM t1 2908 LEFT JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a) 2909 RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 2910 WHERE a<=18 2911 ORDER BY 1 NULLS FIRST; 2912} { 2913 11 31 - 31 31 - 2914 13 - - - 33 - 2915 15 35 35 35 35 35 2916} 2917do_execsql_test joinB-213 { 2918 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2919 FROM t1 2920 LEFT JOIN t2 USING(a) 2921 RIGHT JOIN t3 USING(a) 2922 RIGHT JOIN t4 USING(a) 2923 RIGHT JOIN t5 USING(a) 2924 ORDER BY 1 NULLS FIRST; 2925} { 2926 15 15 15 15 15 15 2927 17 - - - - 17 2928 18 - - - - 18 2929 19 - - 19 19 19 2930} 2931do_execsql_test joinB-214 { 2932 SELECT a, b, c, d, e, f, g 2933 FROM t1 2934 LEFT JOIN t2 USING(a,b) 2935 RIGHT JOIN t3 USING(a,b) 2936 RIGHT JOIN t4 USING(a,b) 2937 RIGHT JOIN t5 USING(a,b) 2938 WHERE d<>33 OR d IS NULL 2939 ORDER BY 1 NULLS FIRST; 2940} { 2941 15 25 35 35 35 35 35 2942 17 27 - - - - 37 2943 18 28 - - - - 38 2944 19 29 - - 39 39 39 2945} 2946do_execsql_test joinB-215 { 2947 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2948 FROM t1 2949 LEFT JOIN t2 USING(a) 2950 RIGHT JOIN t3 USING(a) 2951 RIGHT JOIN t4 USING(a) 2952 FULL JOIN t5 USING(a) 2953 ORDER BY 1 NULLS FIRST; 2954} { 2955 11 11 - 11 11 - 2956 13 - - 13 13 - 2957 15 15 15 15 15 15 2958 17 - - - - 17 2959 18 - - - - 18 2960 19 - - 19 19 19 2961} 2962do_execsql_test joinB-216 { 2963 SELECT b, c, d, e, f, g 2964 FROM t1 2965 NATURAL LEFT JOIN t2 2966 NATURAL RIGHT JOIN t3 2967 NATURAL RIGHT JOIN t4 2968 NATURAL FULL JOIN t5 2969 WHERE b BETWEEN 12 AND 17 2970 ORDER BY 1 NULLS FIRST; 2971} { 2972} 2973do_execsql_test joinB-217 { 2974 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 2975 FROM t1 2976 LEFT JOIN t2 USING(a) 2977 RIGHT JOIN t3 USING(a) 2978 FULL JOIN t4 USING(a) 2979 INNER JOIN t5 USING(a) 2980 ORDER BY 1 NULLS FIRST; 2981} { 2982 15 15 15 15 15 15 2983 17 17 - 17 - 17 2984 18 - - 18 - 18 2985 19 - - 19 19 19 2986} 2987do_execsql_test joinB-218 { 2988 SELECT a, c, d, e, f, g 2989 FROM t1 2990 LEFT JOIN t2 USING(a,b) 2991 RIGHT JOIN t3 USING(a,b) 2992 FULL JOIN t4 USING(a,b) 2993 INNER JOIN t5 USING(a,b) 2994 WHERE a<>13 2995 ORDER BY 1 NULLS FIRST; 2996} { 2997 15 35 35 35 35 35 2998 17 37 - 37 - 37 2999 18 - - 38 - 38 3000 19 - - 39 39 39 3001} 3002do_execsql_test joinB-219 { 3003 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3004 FROM t1 3005 LEFT JOIN t2 USING(a) 3006 RIGHT JOIN t3 USING(a) 3007 FULL JOIN t4 USING(a) 3008 LEFT JOIN t5 USING(a) 3009 ORDER BY 1 NULLS FIRST; 3010} { 3011 - - - - - - 3012 11 11 - 11 11 - 3013 12 12 12 12 - - 3014 13 - - 13 13 - 3015 15 15 15 15 15 15 3016 17 17 - 17 - 17 3017 18 - - 18 - 18 3018 19 - - 19 19 19 3019} 3020do_execsql_test joinB-220 { 3021 SELECT a, c, d, e, f, g 3022 FROM t1 3023 LEFT JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a) 3024 FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 3025 WHERE a<=18 3026 ORDER BY 1 NULLS FIRST; 3027} { 3028 11 31 - 31 31 - 3029 12 32 32 32 - - 3030 13 - - - 33 - 3031 15 35 35 35 35 35 3032 17 37 - 37 - - 3033} 3034do_execsql_test joinB-221 { 3035 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3036 FROM t1 3037 LEFT JOIN t2 USING(a) 3038 RIGHT JOIN t3 USING(a) 3039 FULL JOIN t4 USING(a) 3040 RIGHT JOIN t5 USING(a) 3041 ORDER BY 1 NULLS FIRST; 3042} { 3043 15 15 15 15 15 15 3044 17 17 - 17 - 17 3045 18 - - 18 - 18 3046 19 - - 19 19 19 3047} 3048do_execsql_test joinB-222 { 3049 SELECT a, b, c, d, e, f, g 3050 FROM t1 3051 LEFT JOIN t2 USING(a,b) 3052 RIGHT JOIN t3 USING(a,b) 3053 FULL JOIN t4 USING(a,b) 3054 RIGHT JOIN t5 USING(a,b) 3055 WHERE d<>33 OR d IS NULL 3056 ORDER BY 1 NULLS FIRST; 3057} { 3058 15 25 35 35 35 35 35 3059 17 27 37 - 37 - 37 3060 18 28 - - 38 - 38 3061 19 29 - - 39 39 39 3062} 3063do_execsql_test joinB-223 { 3064 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3065 FROM t1 3066 LEFT JOIN t2 USING(a) 3067 RIGHT JOIN t3 USING(a) 3068 FULL JOIN t4 USING(a) 3069 FULL JOIN t5 USING(a) 3070 ORDER BY 1 NULLS FIRST; 3071} { 3072 - - - - - - 3073 11 11 - 11 11 - 3074 12 12 12 12 - - 3075 13 - - 13 13 - 3076 15 15 15 15 15 15 3077 17 17 - 17 - 17 3078 18 - - 18 - 18 3079 19 - - 19 19 19 3080} 3081do_execsql_test joinB-224 { 3082 SELECT b, c, d, e, f, g 3083 FROM t1 3084 NATURAL LEFT JOIN t2 3085 NATURAL RIGHT JOIN t3 3086 NATURAL FULL JOIN t4 3087 NATURAL FULL JOIN t5 3088 WHERE b BETWEEN 12 AND 17 3089 ORDER BY 1 NULLS FIRST; 3090} { 3091} 3092do_execsql_test joinB-225 { 3093 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3094 FROM t1 3095 LEFT JOIN t2 USING(a) 3096 FULL JOIN t3 USING(a) 3097 INNER JOIN t4 USING(a) 3098 INNER JOIN t5 USING(a) 3099 ORDER BY 1 NULLS FIRST; 3100} { 3101 15 15 15 15 15 15 3102 19 - - 19 19 19 3103} 3104do_execsql_test joinB-226 { 3105 SELECT a, c, d, e, f, g 3106 FROM t1 3107 LEFT JOIN t2 USING(a,b) 3108 FULL JOIN t3 USING(a,b) 3109 INNER JOIN t4 USING(a,b) 3110 INNER JOIN t5 USING(a,b) 3111 WHERE a<>13 3112 ORDER BY 1 NULLS FIRST; 3113} { 3114 15 35 35 35 35 35 3115 19 - - 39 39 39 3116} 3117do_execsql_test joinB-227 { 3118 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3119 FROM t1 3120 LEFT JOIN t2 USING(a) 3121 FULL JOIN t3 USING(a) 3122 INNER JOIN t4 USING(a) 3123 LEFT JOIN t5 USING(a) 3124 ORDER BY 1 NULLS FIRST; 3125} { 3126 11 11 - 11 11 - 3127 13 - - 13 13 - 3128 15 15 15 15 15 15 3129 19 - - 19 19 19 3130} 3131do_execsql_test joinB-228 { 3132 SELECT a, c, d, e, f, g 3133 FROM t1 3134 LEFT JOIN (t2 FULL JOIN t3 USING(a)) USING(a) 3135 INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 3136 WHERE a<=18 3137 ORDER BY 1 NULLS FIRST; 3138} { 3139 11 31 - 31 31 - 3140 15 35 35 35 35 35 3141} 3142do_execsql_test joinB-229 { 3143 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3144 FROM t1 3145 LEFT JOIN t2 USING(a) 3146 FULL JOIN t3 USING(a) 3147 INNER JOIN t4 USING(a) 3148 RIGHT JOIN t5 USING(a) 3149 ORDER BY 1 NULLS FIRST; 3150} { 3151 15 15 15 15 15 15 3152 17 - - - - 17 3153 18 - - - - 18 3154 19 - - 19 19 19 3155} 3156do_execsql_test joinB-230 { 3157 SELECT a, b, c, d, e, f, g 3158 FROM t1 3159 LEFT JOIN t2 USING(a,b) 3160 FULL JOIN t3 USING(a,b) 3161 INNER JOIN t4 USING(a,b) 3162 RIGHT JOIN t5 USING(a,b) 3163 WHERE d<>33 OR d IS NULL 3164 ORDER BY 1 NULLS FIRST; 3165} { 3166 15 25 35 35 35 35 35 3167 17 27 - - - - 37 3168 18 28 - - - - 38 3169 19 29 - - 39 39 39 3170} 3171do_execsql_test joinB-231 { 3172 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3173 FROM t1 3174 LEFT JOIN t2 USING(a) 3175 FULL JOIN t3 USING(a) 3176 INNER JOIN t4 USING(a) 3177 FULL JOIN t5 USING(a) 3178 ORDER BY 1 NULLS FIRST; 3179} { 3180 11 11 - 11 11 - 3181 13 - - 13 13 - 3182 15 15 15 15 15 15 3183 17 - - - - 17 3184 18 - - - - 18 3185 19 - - 19 19 19 3186} 3187do_execsql_test joinB-232 { 3188 SELECT b, c, d, e, f, g 3189 FROM t1 3190 NATURAL LEFT JOIN t2 3191 NATURAL FULL JOIN t3 3192 NATURAL INNER JOIN t4 3193 NATURAL FULL JOIN t5 3194 WHERE b BETWEEN 12 AND 17 3195 ORDER BY 1 NULLS FIRST; 3196} { 3197} 3198do_execsql_test joinB-233 { 3199 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3200 FROM t1 3201 LEFT JOIN t2 USING(a) 3202 FULL JOIN t3 USING(a) 3203 LEFT JOIN t4 USING(a) 3204 INNER JOIN t5 USING(a) 3205 ORDER BY 1 NULLS FIRST; 3206} { 3207 15 15 15 15 15 15 3208 17 17 - 17 - 17 3209 18 - - 18 - 18 3210 19 - - 19 19 19 3211} 3212do_execsql_test joinB-234 { 3213 SELECT a, c, d, e, f, g 3214 FROM t1 3215 LEFT JOIN t2 USING(a,b) 3216 FULL JOIN t3 USING(a,b) 3217 LEFT JOIN t4 USING(a,b) 3218 INNER JOIN t5 USING(a,b) 3219 WHERE a<>13 3220 ORDER BY 1 NULLS FIRST; 3221} { 3222 15 35 35 35 35 35 3223 17 37 - 37 - 37 3224 18 - - 38 - 38 3225 19 - - 39 39 39 3226} 3227do_execsql_test joinB-235 { 3228 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3229 FROM t1 3230 LEFT JOIN t2 USING(a) 3231 FULL JOIN t3 USING(a) 3232 LEFT JOIN t4 USING(a) 3233 LEFT JOIN t5 USING(a) 3234 ORDER BY 1 NULLS FIRST; 3235} { 3236 - - - - - - 3237 11 11 - 11 11 - 3238 12 12 12 12 - - 3239 13 - - 13 13 - 3240 15 15 15 15 15 15 3241 17 17 - 17 - 17 3242 18 - - 18 - 18 3243 19 - - 19 19 19 3244} 3245do_execsql_test joinB-236 { 3246 SELECT a, c, d, e, f, g 3247 FROM t1 3248 LEFT JOIN (t2 FULL JOIN t3 USING(a)) USING(a) 3249 LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 3250 WHERE a<=18 3251 ORDER BY 1 NULLS FIRST; 3252} { 3253 11 31 - 31 31 - 3254 12 32 32 32 - - 3255 15 35 35 35 35 35 3256 17 37 - 37 - - 3257} 3258do_execsql_test joinB-237 { 3259 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3260 FROM t1 3261 LEFT JOIN t2 USING(a) 3262 FULL JOIN t3 USING(a) 3263 LEFT JOIN t4 USING(a) 3264 RIGHT JOIN t5 USING(a) 3265 ORDER BY 1 NULLS FIRST; 3266} { 3267 15 15 15 15 15 15 3268 17 17 - 17 - 17 3269 18 - - 18 - 18 3270 19 - - 19 19 19 3271} 3272do_execsql_test joinB-238 { 3273 SELECT a, b, c, d, e, f, g 3274 FROM t1 3275 LEFT JOIN t2 USING(a,b) 3276 FULL JOIN t3 USING(a,b) 3277 LEFT JOIN t4 USING(a,b) 3278 RIGHT JOIN t5 USING(a,b) 3279 WHERE d<>33 OR d IS NULL 3280 ORDER BY 1 NULLS FIRST; 3281} { 3282 15 25 35 35 35 35 35 3283 17 27 37 - 37 - 37 3284 18 28 - - 38 - 38 3285 19 29 - - 39 39 39 3286} 3287do_execsql_test joinB-239 { 3288 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3289 FROM t1 3290 LEFT JOIN t2 USING(a) 3291 FULL JOIN t3 USING(a) 3292 LEFT JOIN t4 USING(a) 3293 FULL JOIN t5 USING(a) 3294 ORDER BY 1 NULLS FIRST; 3295} { 3296 - - - - - - 3297 11 11 - 11 11 - 3298 12 12 12 12 - - 3299 13 - - 13 13 - 3300 15 15 15 15 15 15 3301 17 17 - 17 - 17 3302 18 - - 18 - 18 3303 19 - - 19 19 19 3304} 3305do_execsql_test joinB-240 { 3306 SELECT b, c, d, e, f, g 3307 FROM t1 3308 NATURAL LEFT JOIN t2 3309 NATURAL FULL JOIN t3 3310 NATURAL LEFT JOIN t4 3311 NATURAL FULL JOIN t5 3312 WHERE b BETWEEN 12 AND 17 3313 ORDER BY 1 NULLS FIRST; 3314} { 3315} 3316do_execsql_test joinB-241 { 3317 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3318 FROM t1 3319 LEFT JOIN t2 USING(a) 3320 FULL JOIN t3 USING(a) 3321 RIGHT JOIN t4 USING(a) 3322 INNER JOIN t5 USING(a) 3323 ORDER BY 1 NULLS FIRST; 3324} { 3325 15 15 15 15 15 15 3326 19 - - 19 19 19 3327} 3328do_execsql_test joinB-242 { 3329 SELECT a, c, d, e, f, g 3330 FROM t1 3331 LEFT JOIN t2 USING(a,b) 3332 FULL JOIN t3 USING(a,b) 3333 RIGHT JOIN t4 USING(a,b) 3334 INNER JOIN t5 USING(a,b) 3335 WHERE a<>13 3336 ORDER BY 1 NULLS FIRST; 3337} { 3338 15 35 35 35 35 35 3339 19 - - 39 39 39 3340} 3341do_execsql_test joinB-243 { 3342 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3343 FROM t1 3344 LEFT JOIN t2 USING(a) 3345 FULL JOIN t3 USING(a) 3346 RIGHT JOIN t4 USING(a) 3347 LEFT JOIN t5 USING(a) 3348 ORDER BY 1 NULLS FIRST; 3349} { 3350 11 11 - 11 11 - 3351 13 - - 13 13 - 3352 15 15 15 15 15 15 3353 19 - - 19 19 19 3354} 3355do_execsql_test joinB-244 { 3356 SELECT a, c, d, e, f, g 3357 FROM t1 3358 LEFT JOIN (t2 FULL JOIN t3 USING(a)) USING(a) 3359 RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 3360 WHERE a<=18 3361 ORDER BY 1 NULLS FIRST; 3362} { 3363 11 31 - 31 31 - 3364 13 - - - 33 - 3365 15 35 35 35 35 35 3366} 3367do_execsql_test joinB-245 { 3368 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3369 FROM t1 3370 LEFT JOIN t2 USING(a) 3371 FULL JOIN t3 USING(a) 3372 RIGHT JOIN t4 USING(a) 3373 RIGHT JOIN t5 USING(a) 3374 ORDER BY 1 NULLS FIRST; 3375} { 3376 15 15 15 15 15 15 3377 17 - - - - 17 3378 18 - - - - 18 3379 19 - - 19 19 19 3380} 3381do_execsql_test joinB-246 { 3382 SELECT a, b, c, d, e, f, g 3383 FROM t1 3384 LEFT JOIN t2 USING(a,b) 3385 FULL JOIN t3 USING(a,b) 3386 RIGHT JOIN t4 USING(a,b) 3387 RIGHT JOIN t5 USING(a,b) 3388 WHERE d<>33 OR d IS NULL 3389 ORDER BY 1 NULLS FIRST; 3390} { 3391 15 25 35 35 35 35 35 3392 17 27 - - - - 37 3393 18 28 - - - - 38 3394 19 29 - - 39 39 39 3395} 3396do_execsql_test joinB-247 { 3397 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3398 FROM t1 3399 LEFT JOIN t2 USING(a) 3400 FULL JOIN t3 USING(a) 3401 RIGHT JOIN t4 USING(a) 3402 FULL JOIN t5 USING(a) 3403 ORDER BY 1 NULLS FIRST; 3404} { 3405 11 11 - 11 11 - 3406 13 - - 13 13 - 3407 15 15 15 15 15 15 3408 17 - - - - 17 3409 18 - - - - 18 3410 19 - - 19 19 19 3411} 3412do_execsql_test joinB-248 { 3413 SELECT b, c, d, e, f, g 3414 FROM t1 3415 NATURAL LEFT JOIN t2 3416 NATURAL FULL JOIN t3 3417 NATURAL RIGHT JOIN t4 3418 NATURAL FULL JOIN t5 3419 WHERE b BETWEEN 12 AND 17 3420 ORDER BY 1 NULLS FIRST; 3421} { 3422} 3423do_execsql_test joinB-249 { 3424 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3425 FROM t1 3426 LEFT JOIN t2 USING(a) 3427 FULL JOIN t3 USING(a) 3428 FULL JOIN t4 USING(a) 3429 INNER JOIN t5 USING(a) 3430 ORDER BY 1 NULLS FIRST; 3431} { 3432 15 15 15 15 15 15 3433 17 17 - 17 - 17 3434 18 - - 18 - 18 3435 19 - - 19 19 19 3436} 3437do_execsql_test joinB-250 { 3438 SELECT a, c, d, e, f, g 3439 FROM t1 3440 LEFT JOIN t2 USING(a,b) 3441 FULL JOIN t3 USING(a,b) 3442 FULL JOIN t4 USING(a,b) 3443 INNER JOIN t5 USING(a,b) 3444 WHERE a<>13 3445 ORDER BY 1 NULLS FIRST; 3446} { 3447 15 35 35 35 35 35 3448 17 37 - 37 - 37 3449 18 - - 38 - 38 3450 19 - - 39 39 39 3451} 3452do_execsql_test joinB-251 { 3453 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3454 FROM t1 3455 LEFT JOIN t2 USING(a) 3456 FULL JOIN t3 USING(a) 3457 FULL JOIN t4 USING(a) 3458 LEFT JOIN t5 USING(a) 3459 ORDER BY 1 NULLS FIRST; 3460} { 3461 - - - - - - 3462 11 11 - 11 11 - 3463 12 12 12 12 - - 3464 13 - - 13 13 - 3465 15 15 15 15 15 15 3466 17 17 - 17 - 17 3467 18 - - 18 - 18 3468 19 - - 19 19 19 3469} 3470do_execsql_test joinB-252 { 3471 SELECT a, c, d, e, f, g 3472 FROM t1 3473 LEFT JOIN (t2 FULL JOIN t3 USING(a)) USING(a) 3474 FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 3475 WHERE a<=18 3476 ORDER BY 1 NULLS FIRST; 3477} { 3478 11 31 - 31 31 - 3479 12 32 32 32 - - 3480 13 - - - 33 - 3481 15 35 35 35 35 35 3482 17 37 - 37 - - 3483} 3484do_execsql_test joinB-253 { 3485 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3486 FROM t1 3487 LEFT JOIN t2 USING(a) 3488 FULL JOIN t3 USING(a) 3489 FULL JOIN t4 USING(a) 3490 RIGHT JOIN t5 USING(a) 3491 ORDER BY 1 NULLS FIRST; 3492} { 3493 15 15 15 15 15 15 3494 17 17 - 17 - 17 3495 18 - - 18 - 18 3496 19 - - 19 19 19 3497} 3498do_execsql_test joinB-254 { 3499 SELECT a, b, c, d, e, f, g 3500 FROM t1 3501 LEFT JOIN t2 USING(a,b) 3502 FULL JOIN t3 USING(a,b) 3503 FULL JOIN t4 USING(a,b) 3504 RIGHT JOIN t5 USING(a,b) 3505 WHERE d<>33 OR d IS NULL 3506 ORDER BY 1 NULLS FIRST; 3507} { 3508 15 25 35 35 35 35 35 3509 17 27 37 - 37 - 37 3510 18 28 - - 38 - 38 3511 19 29 - - 39 39 39 3512} 3513do_execsql_test joinB-255 { 3514 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3515 FROM t1 3516 LEFT JOIN t2 USING(a) 3517 FULL JOIN t3 USING(a) 3518 FULL JOIN t4 USING(a) 3519 FULL JOIN t5 USING(a) 3520 ORDER BY 1 NULLS FIRST; 3521} { 3522 - - - - - - 3523 11 11 - 11 11 - 3524 12 12 12 12 - - 3525 13 - - 13 13 - 3526 15 15 15 15 15 15 3527 17 17 - 17 - 17 3528 18 - - 18 - 18 3529 19 - - 19 19 19 3530} 3531do_execsql_test joinB-256 { 3532 SELECT b, c, d, e, f, g 3533 FROM t1 3534 NATURAL LEFT JOIN t2 3535 NATURAL FULL JOIN t3 3536 NATURAL FULL JOIN t4 3537 NATURAL FULL JOIN t5 3538 WHERE b BETWEEN 12 AND 17 3539 ORDER BY 1 NULLS FIRST; 3540} { 3541} 3542do_execsql_test joinB-257 { 3543 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3544 FROM t1 3545 RIGHT JOIN t2 USING(a) 3546 INNER JOIN t3 USING(a) 3547 INNER JOIN t4 USING(a) 3548 INNER JOIN t5 USING(a) 3549 ORDER BY 1 NULLS FIRST; 3550} { 3551 15 15 15 15 15 15 3552} 3553do_execsql_test joinB-258 { 3554 SELECT a, c, d, e, f, g 3555 FROM t1 3556 RIGHT JOIN t2 USING(a,b) 3557 INNER JOIN t3 USING(a,b) 3558 INNER JOIN t4 USING(a,b) 3559 INNER JOIN t5 USING(a,b) 3560 WHERE a<>13 3561 ORDER BY 1 NULLS FIRST; 3562} { 3563 15 35 35 35 35 35 3564} 3565do_execsql_test joinB-259 { 3566 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3567 FROM t1 3568 RIGHT JOIN t2 USING(a) 3569 INNER JOIN t3 USING(a) 3570 INNER JOIN t4 USING(a) 3571 LEFT JOIN t5 USING(a) 3572 ORDER BY 1 NULLS FIRST; 3573} { 3574 13 - 13 13 13 - 3575 15 15 15 15 15 15 3576} 3577do_execsql_test joinB-260 { 3578 SELECT a, c, d, e, f, g 3579 FROM t1 3580 RIGHT JOIN (t2 INNER JOIN t3 USING(a)) USING(a) 3581 INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 3582 WHERE a<=18 3583 ORDER BY 1 NULLS FIRST; 3584} { 3585 13 - 33 33 33 - 3586 15 35 35 35 35 35 3587} 3588do_execsql_test joinB-261 { 3589 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3590 FROM t1 3591 RIGHT JOIN t2 USING(a) 3592 INNER JOIN t3 USING(a) 3593 INNER JOIN t4 USING(a) 3594 RIGHT JOIN t5 USING(a) 3595 ORDER BY 1 NULLS FIRST; 3596} { 3597 15 15 15 15 15 15 3598 17 - - - - 17 3599 18 - - - - 18 3600 19 - - - - 19 3601} 3602do_execsql_test joinB-262 { 3603 SELECT a, b, c, d, e, f, g 3604 FROM t1 3605 RIGHT JOIN t2 USING(a,b) 3606 INNER JOIN t3 USING(a,b) 3607 INNER JOIN t4 USING(a,b) 3608 RIGHT JOIN t5 USING(a,b) 3609 WHERE d<>33 OR d IS NULL 3610 ORDER BY 1 NULLS FIRST; 3611} { 3612 15 25 35 35 35 35 35 3613 17 27 - - - - 37 3614 18 28 - - - - 38 3615 19 29 - - - - 39 3616} 3617do_execsql_test joinB-263 { 3618 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3619 FROM t1 3620 RIGHT JOIN t2 USING(a) 3621 INNER JOIN t3 USING(a) 3622 INNER JOIN t4 USING(a) 3623 FULL JOIN t5 USING(a) 3624 ORDER BY 1 NULLS FIRST; 3625} { 3626 13 - 13 13 13 - 3627 15 15 15 15 15 15 3628 17 - - - - 17 3629 18 - - - - 18 3630 19 - - - - 19 3631} 3632do_execsql_test joinB-264 { 3633 SELECT b, c, d, e, f, g 3634 FROM t1 3635 NATURAL RIGHT JOIN t2 3636 NATURAL INNER JOIN t3 3637 NATURAL INNER JOIN t4 3638 NATURAL FULL JOIN t5 3639 WHERE b BETWEEN 12 AND 17 3640 ORDER BY 1 NULLS FIRST; 3641} { 3642} 3643do_execsql_test joinB-265 { 3644 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3645 FROM t1 3646 RIGHT JOIN t2 USING(a) 3647 INNER JOIN t3 USING(a) 3648 LEFT JOIN t4 USING(a) 3649 INNER JOIN t5 USING(a) 3650 ORDER BY 1 NULLS FIRST; 3651} { 3652 15 15 15 15 15 15 3653 18 - 18 18 - 18 3654} 3655do_execsql_test joinB-266 { 3656 SELECT a, c, d, e, f, g 3657 FROM t1 3658 RIGHT JOIN t2 USING(a,b) 3659 INNER JOIN t3 USING(a,b) 3660 LEFT JOIN t4 USING(a,b) 3661 INNER JOIN t5 USING(a,b) 3662 WHERE a<>13 3663 ORDER BY 1 NULLS FIRST; 3664} { 3665 15 35 35 35 35 35 3666 18 - 38 38 - 38 3667} 3668do_execsql_test joinB-267 { 3669 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3670 FROM t1 3671 RIGHT JOIN t2 USING(a) 3672 INNER JOIN t3 USING(a) 3673 LEFT JOIN t4 USING(a) 3674 LEFT JOIN t5 USING(a) 3675 ORDER BY 1 NULLS FIRST; 3676} { 3677 12 12 12 12 - - 3678 13 - 13 13 13 - 3679 15 15 15 15 15 15 3680 18 - 18 18 - 18 3681} 3682do_execsql_test joinB-268 { 3683 SELECT a, c, d, e, f, g 3684 FROM t1 3685 RIGHT JOIN (t2 INNER JOIN t3 USING(a)) USING(a) 3686 LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 3687 WHERE a<=18 3688 ORDER BY 1 NULLS FIRST; 3689} { 3690 12 32 32 32 - - 3691 13 - 33 33 33 - 3692 15 35 35 35 35 35 3693 18 - 38 38 - - 3694} 3695do_execsql_test joinB-269 { 3696 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3697 FROM t1 3698 RIGHT JOIN t2 USING(a) 3699 INNER JOIN t3 USING(a) 3700 LEFT JOIN t4 USING(a) 3701 RIGHT JOIN t5 USING(a) 3702 ORDER BY 1 NULLS FIRST; 3703} { 3704 15 15 15 15 15 15 3705 17 - - - - 17 3706 18 - 18 18 - 18 3707 19 - - - - 19 3708} 3709do_execsql_test joinB-270 { 3710 SELECT a, b, c, d, e, f, g 3711 FROM t1 3712 RIGHT JOIN t2 USING(a,b) 3713 INNER JOIN t3 USING(a,b) 3714 LEFT JOIN t4 USING(a,b) 3715 RIGHT JOIN t5 USING(a,b) 3716 WHERE d<>33 OR d IS NULL 3717 ORDER BY 1 NULLS FIRST; 3718} { 3719 15 25 35 35 35 35 35 3720 17 27 - - - - 37 3721 18 28 - 38 38 - 38 3722 19 29 - - - - 39 3723} 3724do_execsql_test joinB-271 { 3725 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3726 FROM t1 3727 RIGHT JOIN t2 USING(a) 3728 INNER JOIN t3 USING(a) 3729 LEFT JOIN t4 USING(a) 3730 FULL JOIN t5 USING(a) 3731 ORDER BY 1 NULLS FIRST; 3732} { 3733 12 12 12 12 - - 3734 13 - 13 13 13 - 3735 15 15 15 15 15 15 3736 17 - - - - 17 3737 18 - 18 18 - 18 3738 19 - - - - 19 3739} 3740do_execsql_test joinB-272 { 3741 SELECT b, c, d, e, f, g 3742 FROM t1 3743 NATURAL RIGHT JOIN t2 3744 NATURAL INNER JOIN t3 3745 NATURAL LEFT JOIN t4 3746 NATURAL FULL JOIN t5 3747 WHERE b BETWEEN 12 AND 17 3748 ORDER BY 1 NULLS FIRST; 3749} { 3750} 3751do_execsql_test joinB-273 { 3752 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3753 FROM t1 3754 RIGHT JOIN t2 USING(a) 3755 INNER JOIN t3 USING(a) 3756 RIGHT JOIN t4 USING(a) 3757 INNER JOIN t5 USING(a) 3758 ORDER BY 1 NULLS FIRST; 3759} { 3760 15 15 15 15 15 15 3761 19 - - - 19 19 3762} 3763do_execsql_test joinB-274 { 3764 SELECT a, c, d, e, f, g 3765 FROM t1 3766 RIGHT JOIN t2 USING(a,b) 3767 INNER JOIN t3 USING(a,b) 3768 RIGHT JOIN t4 USING(a,b) 3769 INNER JOIN t5 USING(a,b) 3770 WHERE a<>13 3771 ORDER BY 1 NULLS FIRST; 3772} { 3773 15 35 35 35 35 35 3774 19 - - - 39 39 3775} 3776do_execsql_test joinB-275 { 3777 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3778 FROM t1 3779 RIGHT JOIN t2 USING(a) 3780 INNER JOIN t3 USING(a) 3781 RIGHT JOIN t4 USING(a) 3782 LEFT JOIN t5 USING(a) 3783 ORDER BY 1 NULLS FIRST; 3784} { 3785 11 - - - 11 - 3786 13 - 13 13 13 - 3787 15 15 15 15 15 15 3788 19 - - - 19 19 3789} 3790do_execsql_test joinB-276 { 3791 SELECT a, c, d, e, f, g 3792 FROM t1 3793 RIGHT JOIN (t2 INNER JOIN t3 USING(a)) USING(a) 3794 RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 3795 WHERE a<=18 3796 ORDER BY 1 NULLS FIRST; 3797} { 3798 11 - - - 31 - 3799 13 - 33 33 33 - 3800 15 35 35 35 35 35 3801} 3802do_execsql_test joinB-277 { 3803 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3804 FROM t1 3805 RIGHT JOIN t2 USING(a) 3806 INNER JOIN t3 USING(a) 3807 RIGHT JOIN t4 USING(a) 3808 RIGHT JOIN t5 USING(a) 3809 ORDER BY 1 NULLS FIRST; 3810} { 3811 15 15 15 15 15 15 3812 17 - - - - 17 3813 18 - - - - 18 3814 19 - - - 19 19 3815} 3816do_execsql_test joinB-278 { 3817 SELECT a, b, c, d, e, f, g 3818 FROM t1 3819 RIGHT JOIN t2 USING(a,b) 3820 INNER JOIN t3 USING(a,b) 3821 RIGHT JOIN t4 USING(a,b) 3822 RIGHT JOIN t5 USING(a,b) 3823 WHERE d<>33 OR d IS NULL 3824 ORDER BY 1 NULLS FIRST; 3825} { 3826 15 25 35 35 35 35 35 3827 17 27 - - - - 37 3828 18 28 - - - - 38 3829 19 29 - - - 39 39 3830} 3831do_execsql_test joinB-279 { 3832 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3833 FROM t1 3834 RIGHT JOIN t2 USING(a) 3835 INNER JOIN t3 USING(a) 3836 RIGHT JOIN t4 USING(a) 3837 FULL JOIN t5 USING(a) 3838 ORDER BY 1 NULLS FIRST; 3839} { 3840 11 - - - 11 - 3841 13 - 13 13 13 - 3842 15 15 15 15 15 15 3843 17 - - - - 17 3844 18 - - - - 18 3845 19 - - - 19 19 3846} 3847do_execsql_test joinB-280 { 3848 SELECT b, c, d, e, f, g 3849 FROM t1 3850 NATURAL RIGHT JOIN t2 3851 NATURAL INNER JOIN t3 3852 NATURAL RIGHT JOIN t4 3853 NATURAL FULL JOIN t5 3854 WHERE b BETWEEN 12 AND 17 3855 ORDER BY 1 NULLS FIRST; 3856} { 3857} 3858do_execsql_test joinB-281 { 3859 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3860 FROM t1 3861 RIGHT JOIN t2 USING(a) 3862 INNER JOIN t3 USING(a) 3863 FULL JOIN t4 USING(a) 3864 INNER JOIN t5 USING(a) 3865 ORDER BY 1 NULLS FIRST; 3866} { 3867 15 15 15 15 15 15 3868 18 - 18 18 - 18 3869 19 - - - 19 19 3870} 3871do_execsql_test joinB-282 { 3872 SELECT a, c, d, e, f, g 3873 FROM t1 3874 RIGHT JOIN t2 USING(a,b) 3875 INNER JOIN t3 USING(a,b) 3876 FULL JOIN t4 USING(a,b) 3877 INNER JOIN t5 USING(a,b) 3878 WHERE a<>13 3879 ORDER BY 1 NULLS FIRST; 3880} { 3881 15 35 35 35 35 35 3882 18 - 38 38 - 38 3883 19 - - - 39 39 3884} 3885do_execsql_test joinB-283 { 3886 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3887 FROM t1 3888 RIGHT JOIN t2 USING(a) 3889 INNER JOIN t3 USING(a) 3890 FULL JOIN t4 USING(a) 3891 LEFT JOIN t5 USING(a) 3892 ORDER BY 1 NULLS FIRST; 3893} { 3894 11 - - - 11 - 3895 12 12 12 12 - - 3896 13 - 13 13 13 - 3897 15 15 15 15 15 15 3898 18 - 18 18 - 18 3899 19 - - - 19 19 3900} 3901do_execsql_test joinB-284 { 3902 SELECT a, c, d, e, f, g 3903 FROM t1 3904 RIGHT JOIN (t2 INNER JOIN t3 USING(a)) USING(a) 3905 FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 3906 WHERE a<=18 3907 ORDER BY 1 NULLS FIRST; 3908} { 3909 11 - - - 31 - 3910 12 32 32 32 - - 3911 13 - 33 33 33 - 3912 15 35 35 35 35 35 3913 18 - 38 38 - - 3914} 3915do_execsql_test joinB-285 { 3916 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3917 FROM t1 3918 RIGHT JOIN t2 USING(a) 3919 INNER JOIN t3 USING(a) 3920 FULL JOIN t4 USING(a) 3921 RIGHT JOIN t5 USING(a) 3922 ORDER BY 1 NULLS FIRST; 3923} { 3924 15 15 15 15 15 15 3925 17 - - - - 17 3926 18 - 18 18 - 18 3927 19 - - - 19 19 3928} 3929do_execsql_test joinB-286 { 3930 SELECT a, b, c, d, e, f, g 3931 FROM t1 3932 RIGHT JOIN t2 USING(a,b) 3933 INNER JOIN t3 USING(a,b) 3934 FULL JOIN t4 USING(a,b) 3935 RIGHT JOIN t5 USING(a,b) 3936 WHERE d<>33 OR d IS NULL 3937 ORDER BY 1 NULLS FIRST; 3938} { 3939 15 25 35 35 35 35 35 3940 17 27 - - - - 37 3941 18 28 - 38 38 - 38 3942 19 29 - - - 39 39 3943} 3944do_execsql_test joinB-287 { 3945 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3946 FROM t1 3947 RIGHT JOIN t2 USING(a) 3948 INNER JOIN t3 USING(a) 3949 FULL JOIN t4 USING(a) 3950 FULL JOIN t5 USING(a) 3951 ORDER BY 1 NULLS FIRST; 3952} { 3953 11 - - - 11 - 3954 12 12 12 12 - - 3955 13 - 13 13 13 - 3956 15 15 15 15 15 15 3957 17 - - - - 17 3958 18 - 18 18 - 18 3959 19 - - - 19 19 3960} 3961do_execsql_test joinB-288 { 3962 SELECT b, c, d, e, f, g 3963 FROM t1 3964 NATURAL RIGHT JOIN t2 3965 NATURAL INNER JOIN t3 3966 NATURAL FULL JOIN t4 3967 NATURAL FULL JOIN t5 3968 WHERE b BETWEEN 12 AND 17 3969 ORDER BY 1 NULLS FIRST; 3970} { 3971} 3972do_execsql_test joinB-289 { 3973 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3974 FROM t1 3975 RIGHT JOIN t2 USING(a) 3976 LEFT JOIN t3 USING(a) 3977 INNER JOIN t4 USING(a) 3978 INNER JOIN t5 USING(a) 3979 ORDER BY 1 NULLS FIRST; 3980} { 3981 15 15 15 15 15 15 3982} 3983do_execsql_test joinB-290 { 3984 SELECT a, c, d, e, f, g 3985 FROM t1 3986 RIGHT JOIN t2 USING(a,b) 3987 LEFT JOIN t3 USING(a,b) 3988 INNER JOIN t4 USING(a,b) 3989 INNER JOIN t5 USING(a,b) 3990 WHERE a<>13 3991 ORDER BY 1 NULLS FIRST; 3992} { 3993 15 35 35 35 35 35 3994} 3995do_execsql_test joinB-291 { 3996 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 3997 FROM t1 3998 RIGHT JOIN t2 USING(a) 3999 LEFT JOIN t3 USING(a) 4000 INNER JOIN t4 USING(a) 4001 LEFT JOIN t5 USING(a) 4002 ORDER BY 1 NULLS FIRST; 4003} { 4004 13 - 13 13 13 - 4005 15 15 15 15 15 15 4006} 4007do_execsql_test joinB-292 { 4008 SELECT a, c, d, e, f, g 4009 FROM t1 4010 RIGHT JOIN (t2 LEFT JOIN t3 USING(a)) USING(a) 4011 INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 4012 WHERE a<=18 4013 ORDER BY 1 NULLS FIRST; 4014} { 4015 13 - 33 33 33 - 4016 15 35 35 35 35 35 4017} 4018do_execsql_test joinB-293 { 4019 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4020 FROM t1 4021 RIGHT JOIN t2 USING(a) 4022 LEFT JOIN t3 USING(a) 4023 INNER JOIN t4 USING(a) 4024 RIGHT JOIN t5 USING(a) 4025 ORDER BY 1 NULLS FIRST; 4026} { 4027 15 15 15 15 15 15 4028 17 - - - - 17 4029 18 - - - - 18 4030 19 - - - - 19 4031} 4032do_execsql_test joinB-294 { 4033 SELECT a, b, c, d, e, f, g 4034 FROM t1 4035 RIGHT JOIN t2 USING(a,b) 4036 LEFT JOIN t3 USING(a,b) 4037 INNER JOIN t4 USING(a,b) 4038 RIGHT JOIN t5 USING(a,b) 4039 WHERE d<>33 OR d IS NULL 4040 ORDER BY 1 NULLS FIRST; 4041} { 4042 15 25 35 35 35 35 35 4043 17 27 - - - - 37 4044 18 28 - - - - 38 4045 19 29 - - - - 39 4046} 4047do_execsql_test joinB-295 { 4048 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4049 FROM t1 4050 RIGHT JOIN t2 USING(a) 4051 LEFT JOIN t3 USING(a) 4052 INNER JOIN t4 USING(a) 4053 FULL JOIN t5 USING(a) 4054 ORDER BY 1 NULLS FIRST; 4055} { 4056 13 - 13 13 13 - 4057 15 15 15 15 15 15 4058 17 - - - - 17 4059 18 - - - - 18 4060 19 - - - - 19 4061} 4062do_execsql_test joinB-296 { 4063 SELECT b, c, d, e, f, g 4064 FROM t1 4065 NATURAL RIGHT JOIN t2 4066 NATURAL LEFT JOIN t3 4067 NATURAL INNER JOIN t4 4068 NATURAL FULL JOIN t5 4069 WHERE b BETWEEN 12 AND 17 4070 ORDER BY 1 NULLS FIRST; 4071} { 4072} 4073do_execsql_test joinB-297 { 4074 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4075 FROM t1 4076 RIGHT JOIN t2 USING(a) 4077 LEFT JOIN t3 USING(a) 4078 LEFT JOIN t4 USING(a) 4079 INNER JOIN t5 USING(a) 4080 ORDER BY 1 NULLS FIRST; 4081} { 4082 15 15 15 15 15 15 4083 18 - 18 18 - 18 4084} 4085do_execsql_test joinB-298 { 4086 SELECT a, c, d, e, f, g 4087 FROM t1 4088 RIGHT JOIN t2 USING(a,b) 4089 LEFT JOIN t3 USING(a,b) 4090 LEFT JOIN t4 USING(a,b) 4091 INNER JOIN t5 USING(a,b) 4092 WHERE a<>13 4093 ORDER BY 1 NULLS FIRST; 4094} { 4095 15 35 35 35 35 35 4096 18 - 38 38 - 38 4097} 4098do_execsql_test joinB-299 { 4099 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4100 FROM t1 4101 RIGHT JOIN t2 USING(a) 4102 LEFT JOIN t3 USING(a) 4103 LEFT JOIN t4 USING(a) 4104 LEFT JOIN t5 USING(a) 4105 ORDER BY 1 NULLS FIRST; 4106} { 4107 - - - - - - 4108 12 12 12 12 - - 4109 13 - 13 13 13 - 4110 15 15 15 15 15 15 4111 18 - 18 18 - 18 4112} 4113do_execsql_test joinB-300 { 4114 SELECT a, c, d, e, f, g 4115 FROM t1 4116 RIGHT JOIN (t2 LEFT JOIN t3 USING(a)) USING(a) 4117 LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 4118 WHERE a<=18 4119 ORDER BY 1 NULLS FIRST; 4120} { 4121 12 32 32 32 - - 4122 13 - 33 33 33 - 4123 15 35 35 35 35 35 4124 18 - 38 38 - - 4125} 4126do_execsql_test joinB-301 { 4127 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4128 FROM t1 4129 RIGHT JOIN t2 USING(a) 4130 LEFT JOIN t3 USING(a) 4131 LEFT JOIN t4 USING(a) 4132 RIGHT JOIN t5 USING(a) 4133 ORDER BY 1 NULLS FIRST; 4134} { 4135 15 15 15 15 15 15 4136 17 - - - - 17 4137 18 - 18 18 - 18 4138 19 - - - - 19 4139} 4140do_execsql_test joinB-302 { 4141 SELECT a, b, c, d, e, f, g 4142 FROM t1 4143 RIGHT JOIN t2 USING(a,b) 4144 LEFT JOIN t3 USING(a,b) 4145 LEFT JOIN t4 USING(a,b) 4146 RIGHT JOIN t5 USING(a,b) 4147 WHERE d<>33 OR d IS NULL 4148 ORDER BY 1 NULLS FIRST; 4149} { 4150 15 25 35 35 35 35 35 4151 17 27 - - - - 37 4152 18 28 - 38 38 - 38 4153 19 29 - - - - 39 4154} 4155do_execsql_test joinB-303 { 4156 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4157 FROM t1 4158 RIGHT JOIN t2 USING(a) 4159 LEFT JOIN t3 USING(a) 4160 LEFT JOIN t4 USING(a) 4161 FULL JOIN t5 USING(a) 4162 ORDER BY 1 NULLS FIRST; 4163} { 4164 - - - - - - 4165 12 12 12 12 - - 4166 13 - 13 13 13 - 4167 15 15 15 15 15 15 4168 17 - - - - 17 4169 18 - 18 18 - 18 4170 19 - - - - 19 4171} 4172do_execsql_test joinB-304 { 4173 SELECT b, c, d, e, f, g 4174 FROM t1 4175 NATURAL RIGHT JOIN t2 4176 NATURAL LEFT JOIN t3 4177 NATURAL LEFT JOIN t4 4178 NATURAL FULL JOIN t5 4179 WHERE b BETWEEN 12 AND 17 4180 ORDER BY 1 NULLS FIRST; 4181} { 4182} 4183do_execsql_test joinB-305 { 4184 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4185 FROM t1 4186 RIGHT JOIN t2 USING(a) 4187 LEFT JOIN t3 USING(a) 4188 RIGHT JOIN t4 USING(a) 4189 INNER JOIN t5 USING(a) 4190 ORDER BY 1 NULLS FIRST; 4191} { 4192 15 15 15 15 15 15 4193 19 - - - 19 19 4194} 4195do_execsql_test joinB-306 { 4196 SELECT a, c, d, e, f, g 4197 FROM t1 4198 RIGHT JOIN t2 USING(a,b) 4199 LEFT JOIN t3 USING(a,b) 4200 RIGHT JOIN t4 USING(a,b) 4201 INNER JOIN t5 USING(a,b) 4202 WHERE a<>13 4203 ORDER BY 1 NULLS FIRST; 4204} { 4205 15 35 35 35 35 35 4206 19 - - - 39 39 4207} 4208do_execsql_test joinB-307 { 4209 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4210 FROM t1 4211 RIGHT JOIN t2 USING(a) 4212 LEFT JOIN t3 USING(a) 4213 RIGHT JOIN t4 USING(a) 4214 LEFT JOIN t5 USING(a) 4215 ORDER BY 1 NULLS FIRST; 4216} { 4217 11 - - - 11 - 4218 13 - 13 13 13 - 4219 15 15 15 15 15 15 4220 19 - - - 19 19 4221} 4222do_execsql_test joinB-308 { 4223 SELECT a, c, d, e, f, g 4224 FROM t1 4225 RIGHT JOIN (t2 LEFT JOIN t3 USING(a)) USING(a) 4226 RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 4227 WHERE a<=18 4228 ORDER BY 1 NULLS FIRST; 4229} { 4230 11 - - - 31 - 4231 13 - 33 33 33 - 4232 15 35 35 35 35 35 4233} 4234do_execsql_test joinB-309 { 4235 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4236 FROM t1 4237 RIGHT JOIN t2 USING(a) 4238 LEFT JOIN t3 USING(a) 4239 RIGHT JOIN t4 USING(a) 4240 RIGHT JOIN t5 USING(a) 4241 ORDER BY 1 NULLS FIRST; 4242} { 4243 15 15 15 15 15 15 4244 17 - - - - 17 4245 18 - - - - 18 4246 19 - - - 19 19 4247} 4248do_execsql_test joinB-310 { 4249 SELECT a, b, c, d, e, f, g 4250 FROM t1 4251 RIGHT JOIN t2 USING(a,b) 4252 LEFT JOIN t3 USING(a,b) 4253 RIGHT JOIN t4 USING(a,b) 4254 RIGHT JOIN t5 USING(a,b) 4255 WHERE d<>33 OR d IS NULL 4256 ORDER BY 1 NULLS FIRST; 4257} { 4258 15 25 35 35 35 35 35 4259 17 27 - - - - 37 4260 18 28 - - - - 38 4261 19 29 - - - 39 39 4262} 4263do_execsql_test joinB-311 { 4264 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4265 FROM t1 4266 RIGHT JOIN t2 USING(a) 4267 LEFT JOIN t3 USING(a) 4268 RIGHT JOIN t4 USING(a) 4269 FULL JOIN t5 USING(a) 4270 ORDER BY 1 NULLS FIRST; 4271} { 4272 11 - - - 11 - 4273 13 - 13 13 13 - 4274 15 15 15 15 15 15 4275 17 - - - - 17 4276 18 - - - - 18 4277 19 - - - 19 19 4278} 4279do_execsql_test joinB-312 { 4280 SELECT b, c, d, e, f, g 4281 FROM t1 4282 NATURAL RIGHT JOIN t2 4283 NATURAL LEFT JOIN t3 4284 NATURAL RIGHT JOIN t4 4285 NATURAL FULL JOIN t5 4286 WHERE b BETWEEN 12 AND 17 4287 ORDER BY 1 NULLS FIRST; 4288} { 4289} 4290do_execsql_test joinB-313 { 4291 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4292 FROM t1 4293 RIGHT JOIN t2 USING(a) 4294 LEFT JOIN t3 USING(a) 4295 FULL JOIN t4 USING(a) 4296 INNER JOIN t5 USING(a) 4297 ORDER BY 1 NULLS FIRST; 4298} { 4299 15 15 15 15 15 15 4300 18 - 18 18 - 18 4301 19 - - - 19 19 4302} 4303do_execsql_test joinB-314 { 4304 SELECT a, c, d, e, f, g 4305 FROM t1 4306 RIGHT JOIN t2 USING(a,b) 4307 LEFT JOIN t3 USING(a,b) 4308 FULL JOIN t4 USING(a,b) 4309 INNER JOIN t5 USING(a,b) 4310 WHERE a<>13 4311 ORDER BY 1 NULLS FIRST; 4312} { 4313 15 35 35 35 35 35 4314 18 - 38 38 - 38 4315 19 - - - 39 39 4316} 4317do_execsql_test joinB-315 { 4318 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4319 FROM t1 4320 RIGHT JOIN t2 USING(a) 4321 LEFT JOIN t3 USING(a) 4322 FULL JOIN t4 USING(a) 4323 LEFT JOIN t5 USING(a) 4324 ORDER BY 1 NULLS FIRST; 4325} { 4326 - - - - - - 4327 11 - - - 11 - 4328 12 12 12 12 - - 4329 13 - 13 13 13 - 4330 15 15 15 15 15 15 4331 18 - 18 18 - 18 4332 19 - - - 19 19 4333} 4334do_execsql_test joinB-316 { 4335 SELECT a, c, d, e, f, g 4336 FROM t1 4337 RIGHT JOIN (t2 LEFT JOIN t3 USING(a)) USING(a) 4338 FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 4339 WHERE a<=18 4340 ORDER BY 1 NULLS FIRST; 4341} { 4342 11 - - - 31 - 4343 12 32 32 32 - - 4344 13 - 33 33 33 - 4345 15 35 35 35 35 35 4346 18 - 38 38 - - 4347} 4348do_execsql_test joinB-317 { 4349 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4350 FROM t1 4351 RIGHT JOIN t2 USING(a) 4352 LEFT JOIN t3 USING(a) 4353 FULL JOIN t4 USING(a) 4354 RIGHT JOIN t5 USING(a) 4355 ORDER BY 1 NULLS FIRST; 4356} { 4357 15 15 15 15 15 15 4358 17 - - - - 17 4359 18 - 18 18 - 18 4360 19 - - - 19 19 4361} 4362do_execsql_test joinB-318 { 4363 SELECT a, b, c, d, e, f, g 4364 FROM t1 4365 RIGHT JOIN t2 USING(a,b) 4366 LEFT JOIN t3 USING(a,b) 4367 FULL JOIN t4 USING(a,b) 4368 RIGHT JOIN t5 USING(a,b) 4369 WHERE d<>33 OR d IS NULL 4370 ORDER BY 1 NULLS FIRST; 4371} { 4372 15 25 35 35 35 35 35 4373 17 27 - - - - 37 4374 18 28 - 38 38 - 38 4375 19 29 - - - 39 39 4376} 4377do_execsql_test joinB-319 { 4378 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4379 FROM t1 4380 RIGHT JOIN t2 USING(a) 4381 LEFT JOIN t3 USING(a) 4382 FULL JOIN t4 USING(a) 4383 FULL JOIN t5 USING(a) 4384 ORDER BY 1 NULLS FIRST; 4385} { 4386 - - - - - - 4387 11 - - - 11 - 4388 12 12 12 12 - - 4389 13 - 13 13 13 - 4390 15 15 15 15 15 15 4391 17 - - - - 17 4392 18 - 18 18 - 18 4393 19 - - - 19 19 4394} 4395do_execsql_test joinB-320 { 4396 SELECT b, c, d, e, f, g 4397 FROM t1 4398 NATURAL RIGHT JOIN t2 4399 NATURAL LEFT JOIN t3 4400 NATURAL FULL JOIN t4 4401 NATURAL FULL JOIN t5 4402 WHERE b BETWEEN 12 AND 17 4403 ORDER BY 1 NULLS FIRST; 4404} { 4405} 4406do_execsql_test joinB-321 { 4407 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4408 FROM t1 4409 RIGHT JOIN t2 USING(a) 4410 RIGHT JOIN t3 USING(a) 4411 INNER JOIN t4 USING(a) 4412 INNER JOIN t5 USING(a) 4413 ORDER BY 1 NULLS FIRST; 4414} { 4415 15 15 15 15 15 15 4416 19 - - 19 19 19 4417} 4418do_execsql_test joinB-322 { 4419 SELECT a, c, d, e, f, g 4420 FROM t1 4421 RIGHT JOIN t2 USING(a,b) 4422 RIGHT JOIN t3 USING(a,b) 4423 INNER JOIN t4 USING(a,b) 4424 INNER JOIN t5 USING(a,b) 4425 WHERE a<>13 4426 ORDER BY 1 NULLS FIRST; 4427} { 4428 15 35 35 35 35 35 4429 19 - - 39 39 39 4430} 4431do_execsql_test joinB-323 { 4432 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4433 FROM t1 4434 RIGHT JOIN t2 USING(a) 4435 RIGHT JOIN t3 USING(a) 4436 INNER JOIN t4 USING(a) 4437 LEFT JOIN t5 USING(a) 4438 ORDER BY 1 NULLS FIRST; 4439} { 4440 11 - - 11 11 - 4441 13 - 13 13 13 - 4442 15 15 15 15 15 15 4443 19 - - 19 19 19 4444} 4445do_execsql_test joinB-324 { 4446 SELECT a, c, d, e, f, g 4447 FROM t1 4448 RIGHT JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a) 4449 INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 4450 WHERE a<=18 4451 ORDER BY 1 NULLS FIRST; 4452} { 4453 11 31 - 31 31 - 4454 13 - 33 33 33 - 4455 15 35 35 35 35 35 4456} 4457do_execsql_test joinB-325 { 4458 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4459 FROM t1 4460 RIGHT JOIN t2 USING(a) 4461 RIGHT JOIN t3 USING(a) 4462 INNER JOIN t4 USING(a) 4463 RIGHT JOIN t5 USING(a) 4464 ORDER BY 1 NULLS FIRST; 4465} { 4466 15 15 15 15 15 15 4467 17 - - - - 17 4468 18 - - - - 18 4469 19 - - 19 19 19 4470} 4471do_execsql_test joinB-326 { 4472 SELECT a, b, c, d, e, f, g 4473 FROM t1 4474 RIGHT JOIN t2 USING(a,b) 4475 RIGHT JOIN t3 USING(a,b) 4476 INNER JOIN t4 USING(a,b) 4477 RIGHT JOIN t5 USING(a,b) 4478 WHERE d<>33 OR d IS NULL 4479 ORDER BY 1 NULLS FIRST; 4480} { 4481 15 25 35 35 35 35 35 4482 17 27 - - - - 37 4483 18 28 - - - - 38 4484 19 29 - - 39 39 39 4485} 4486do_execsql_test joinB-327 { 4487 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4488 FROM t1 4489 RIGHT JOIN t2 USING(a) 4490 RIGHT JOIN t3 USING(a) 4491 INNER JOIN t4 USING(a) 4492 FULL JOIN t5 USING(a) 4493 ORDER BY 1 NULLS FIRST; 4494} { 4495 11 - - 11 11 - 4496 13 - 13 13 13 - 4497 15 15 15 15 15 15 4498 17 - - - - 17 4499 18 - - - - 18 4500 19 - - 19 19 19 4501} 4502do_execsql_test joinB-328 { 4503 SELECT b, c, d, e, f, g 4504 FROM t1 4505 NATURAL RIGHT JOIN t2 4506 NATURAL RIGHT JOIN t3 4507 NATURAL INNER JOIN t4 4508 NATURAL FULL JOIN t5 4509 WHERE b BETWEEN 12 AND 17 4510 ORDER BY 1 NULLS FIRST; 4511} { 4512} 4513do_execsql_test joinB-329 { 4514 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4515 FROM t1 4516 RIGHT JOIN t2 USING(a) 4517 RIGHT JOIN t3 USING(a) 4518 LEFT JOIN t4 USING(a) 4519 INNER JOIN t5 USING(a) 4520 ORDER BY 1 NULLS FIRST; 4521} { 4522 15 15 15 15 15 15 4523 17 - - 17 - 17 4524 18 - 18 18 - 18 4525 19 - - 19 19 19 4526} 4527do_execsql_test joinB-330 { 4528 SELECT a, c, d, e, f, g 4529 FROM t1 4530 RIGHT JOIN t2 USING(a,b) 4531 RIGHT JOIN t3 USING(a,b) 4532 LEFT JOIN t4 USING(a,b) 4533 INNER JOIN t5 USING(a,b) 4534 WHERE a<>13 4535 ORDER BY 1 NULLS FIRST; 4536} { 4537 15 35 35 35 35 35 4538 17 - - 37 - 37 4539 18 - 38 38 - 38 4540 19 - - 39 39 39 4541} 4542do_execsql_test joinB-331 { 4543 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4544 FROM t1 4545 RIGHT JOIN t2 USING(a) 4546 RIGHT JOIN t3 USING(a) 4547 LEFT JOIN t4 USING(a) 4548 LEFT JOIN t5 USING(a) 4549 ORDER BY 1 NULLS FIRST; 4550} { 4551 - - - - - - 4552 11 - - 11 11 - 4553 12 12 12 12 - - 4554 13 - 13 13 13 - 4555 15 15 15 15 15 15 4556 17 - - 17 - 17 4557 18 - 18 18 - 18 4558 19 - - 19 19 19 4559} 4560do_execsql_test joinB-332 { 4561 SELECT a, c, d, e, f, g 4562 FROM t1 4563 RIGHT JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a) 4564 LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 4565 WHERE a<=18 4566 ORDER BY 1 NULLS FIRST; 4567} { 4568 11 31 - 31 31 - 4569 12 32 32 32 - - 4570 13 - 33 33 33 - 4571 15 35 35 35 35 35 4572 17 37 - 37 - - 4573 18 - 38 38 - - 4574} 4575do_execsql_test joinB-333 { 4576 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4577 FROM t1 4578 RIGHT JOIN t2 USING(a) 4579 RIGHT JOIN t3 USING(a) 4580 LEFT JOIN t4 USING(a) 4581 RIGHT JOIN t5 USING(a) 4582 ORDER BY 1 NULLS FIRST; 4583} { 4584 15 15 15 15 15 15 4585 17 - - 17 - 17 4586 18 - 18 18 - 18 4587 19 - - 19 19 19 4588} 4589do_execsql_test joinB-334 { 4590 SELECT a, b, c, d, e, f, g 4591 FROM t1 4592 RIGHT JOIN t2 USING(a,b) 4593 RIGHT JOIN t3 USING(a,b) 4594 LEFT JOIN t4 USING(a,b) 4595 RIGHT JOIN t5 USING(a,b) 4596 WHERE d<>33 OR d IS NULL 4597 ORDER BY 1 NULLS FIRST; 4598} { 4599 15 25 35 35 35 35 35 4600 17 27 - - 37 - 37 4601 18 28 - 38 38 - 38 4602 19 29 - - 39 39 39 4603} 4604do_execsql_test joinB-335 { 4605 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4606 FROM t1 4607 RIGHT JOIN t2 USING(a) 4608 RIGHT JOIN t3 USING(a) 4609 LEFT JOIN t4 USING(a) 4610 FULL JOIN t5 USING(a) 4611 ORDER BY 1 NULLS FIRST; 4612} { 4613 - - - - - - 4614 11 - - 11 11 - 4615 12 12 12 12 - - 4616 13 - 13 13 13 - 4617 15 15 15 15 15 15 4618 17 - - 17 - 17 4619 18 - 18 18 - 18 4620 19 - - 19 19 19 4621} 4622do_execsql_test joinB-336 { 4623 SELECT b, c, d, e, f, g 4624 FROM t1 4625 NATURAL RIGHT JOIN t2 4626 NATURAL RIGHT JOIN t3 4627 NATURAL LEFT JOIN t4 4628 NATURAL FULL JOIN t5 4629 WHERE b BETWEEN 12 AND 17 4630 ORDER BY 1 NULLS FIRST; 4631} { 4632} 4633do_execsql_test joinB-337 { 4634 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4635 FROM t1 4636 RIGHT JOIN t2 USING(a) 4637 RIGHT JOIN t3 USING(a) 4638 RIGHT JOIN t4 USING(a) 4639 INNER JOIN t5 USING(a) 4640 ORDER BY 1 NULLS FIRST; 4641} { 4642 15 15 15 15 15 15 4643 19 - - 19 19 19 4644} 4645do_execsql_test joinB-338 { 4646 SELECT a, c, d, e, f, g 4647 FROM t1 4648 RIGHT JOIN t2 USING(a,b) 4649 RIGHT JOIN t3 USING(a,b) 4650 RIGHT JOIN t4 USING(a,b) 4651 INNER JOIN t5 USING(a,b) 4652 WHERE a<>13 4653 ORDER BY 1 NULLS FIRST; 4654} { 4655 15 35 35 35 35 35 4656 19 - - 39 39 39 4657} 4658do_execsql_test joinB-339 { 4659 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4660 FROM t1 4661 RIGHT JOIN t2 USING(a) 4662 RIGHT JOIN t3 USING(a) 4663 RIGHT JOIN t4 USING(a) 4664 LEFT JOIN t5 USING(a) 4665 ORDER BY 1 NULLS FIRST; 4666} { 4667 11 - - 11 11 - 4668 13 - 13 13 13 - 4669 15 15 15 15 15 15 4670 19 - - 19 19 19 4671} 4672do_execsql_test joinB-340 { 4673 SELECT a, c, d, e, f, g 4674 FROM t1 4675 RIGHT JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a) 4676 RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 4677 WHERE a<=18 4678 ORDER BY 1 NULLS FIRST; 4679} { 4680 11 31 - 31 31 - 4681 13 - 33 33 33 - 4682 15 35 35 35 35 35 4683} 4684do_execsql_test joinB-341 { 4685 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4686 FROM t1 4687 RIGHT JOIN t2 USING(a) 4688 RIGHT JOIN t3 USING(a) 4689 RIGHT JOIN t4 USING(a) 4690 RIGHT JOIN t5 USING(a) 4691 ORDER BY 1 NULLS FIRST; 4692} { 4693 15 15 15 15 15 15 4694 17 - - - - 17 4695 18 - - - - 18 4696 19 - - 19 19 19 4697} 4698do_execsql_test joinB-342 { 4699 SELECT a, b, c, d, e, f, g 4700 FROM t1 4701 RIGHT JOIN t2 USING(a,b) 4702 RIGHT JOIN t3 USING(a,b) 4703 RIGHT JOIN t4 USING(a,b) 4704 RIGHT JOIN t5 USING(a,b) 4705 WHERE d<>33 OR d IS NULL 4706 ORDER BY 1 NULLS FIRST; 4707} { 4708 15 25 35 35 35 35 35 4709 17 27 - - - - 37 4710 18 28 - - - - 38 4711 19 29 - - 39 39 39 4712} 4713do_execsql_test joinB-343 { 4714 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4715 FROM t1 4716 RIGHT JOIN t2 USING(a) 4717 RIGHT JOIN t3 USING(a) 4718 RIGHT JOIN t4 USING(a) 4719 FULL JOIN t5 USING(a) 4720 ORDER BY 1 NULLS FIRST; 4721} { 4722 11 - - 11 11 - 4723 13 - 13 13 13 - 4724 15 15 15 15 15 15 4725 17 - - - - 17 4726 18 - - - - 18 4727 19 - - 19 19 19 4728} 4729do_execsql_test joinB-344 { 4730 SELECT b, c, d, e, f, g 4731 FROM t1 4732 NATURAL RIGHT JOIN t2 4733 NATURAL RIGHT JOIN t3 4734 NATURAL RIGHT JOIN t4 4735 NATURAL FULL JOIN t5 4736 WHERE b BETWEEN 12 AND 17 4737 ORDER BY 1 NULLS FIRST; 4738} { 4739} 4740do_execsql_test joinB-345 { 4741 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4742 FROM t1 4743 RIGHT JOIN t2 USING(a) 4744 RIGHT JOIN t3 USING(a) 4745 FULL JOIN t4 USING(a) 4746 INNER JOIN t5 USING(a) 4747 ORDER BY 1 NULLS FIRST; 4748} { 4749 15 15 15 15 15 15 4750 17 - - 17 - 17 4751 18 - 18 18 - 18 4752 19 - - 19 19 19 4753} 4754do_execsql_test joinB-346 { 4755 SELECT a, c, d, e, f, g 4756 FROM t1 4757 RIGHT JOIN t2 USING(a,b) 4758 RIGHT JOIN t3 USING(a,b) 4759 FULL JOIN t4 USING(a,b) 4760 INNER JOIN t5 USING(a,b) 4761 WHERE a<>13 4762 ORDER BY 1 NULLS FIRST; 4763} { 4764 15 35 35 35 35 35 4765 17 - - 37 - 37 4766 18 - 38 38 - 38 4767 19 - - 39 39 39 4768} 4769do_execsql_test joinB-347 { 4770 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4771 FROM t1 4772 RIGHT JOIN t2 USING(a) 4773 RIGHT JOIN t3 USING(a) 4774 FULL JOIN t4 USING(a) 4775 LEFT JOIN t5 USING(a) 4776 ORDER BY 1 NULLS FIRST; 4777} { 4778 - - - - - - 4779 11 - - 11 11 - 4780 12 12 12 12 - - 4781 13 - 13 13 13 - 4782 15 15 15 15 15 15 4783 17 - - 17 - 17 4784 18 - 18 18 - 18 4785 19 - - 19 19 19 4786} 4787do_execsql_test joinB-348 { 4788 SELECT a, c, d, e, f, g 4789 FROM t1 4790 RIGHT JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a) 4791 FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 4792 WHERE a<=18 4793 ORDER BY 1 NULLS FIRST; 4794} { 4795 11 31 - 31 31 - 4796 12 32 32 32 - - 4797 13 - 33 33 33 - 4798 15 35 35 35 35 35 4799 17 37 - 37 - - 4800 18 - 38 38 - - 4801} 4802do_execsql_test joinB-349 { 4803 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4804 FROM t1 4805 RIGHT JOIN t2 USING(a) 4806 RIGHT JOIN t3 USING(a) 4807 FULL JOIN t4 USING(a) 4808 RIGHT JOIN t5 USING(a) 4809 ORDER BY 1 NULLS FIRST; 4810} { 4811 15 15 15 15 15 15 4812 17 - - 17 - 17 4813 18 - 18 18 - 18 4814 19 - - 19 19 19 4815} 4816do_execsql_test joinB-350 { 4817 SELECT a, b, c, d, e, f, g 4818 FROM t1 4819 RIGHT JOIN t2 USING(a,b) 4820 RIGHT JOIN t3 USING(a,b) 4821 FULL JOIN t4 USING(a,b) 4822 RIGHT JOIN t5 USING(a,b) 4823 WHERE d<>33 OR d IS NULL 4824 ORDER BY 1 NULLS FIRST; 4825} { 4826 15 25 35 35 35 35 35 4827 17 27 - - 37 - 37 4828 18 28 - 38 38 - 38 4829 19 29 - - 39 39 39 4830} 4831do_execsql_test joinB-351 { 4832 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4833 FROM t1 4834 RIGHT JOIN t2 USING(a) 4835 RIGHT JOIN t3 USING(a) 4836 FULL JOIN t4 USING(a) 4837 FULL JOIN t5 USING(a) 4838 ORDER BY 1 NULLS FIRST; 4839} { 4840 - - - - - - 4841 11 - - 11 11 - 4842 12 12 12 12 - - 4843 13 - 13 13 13 - 4844 15 15 15 15 15 15 4845 17 - - 17 - 17 4846 18 - 18 18 - 18 4847 19 - - 19 19 19 4848} 4849do_execsql_test joinB-352 { 4850 SELECT b, c, d, e, f, g 4851 FROM t1 4852 NATURAL RIGHT JOIN t2 4853 NATURAL RIGHT JOIN t3 4854 NATURAL FULL JOIN t4 4855 NATURAL FULL JOIN t5 4856 WHERE b BETWEEN 12 AND 17 4857 ORDER BY 1 NULLS FIRST; 4858} { 4859} 4860do_execsql_test joinB-353 { 4861 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4862 FROM t1 4863 RIGHT JOIN t2 USING(a) 4864 FULL JOIN t3 USING(a) 4865 INNER JOIN t4 USING(a) 4866 INNER JOIN t5 USING(a) 4867 ORDER BY 1 NULLS FIRST; 4868} { 4869 15 15 15 15 15 15 4870 19 - - 19 19 19 4871} 4872do_execsql_test joinB-354 { 4873 SELECT a, c, d, e, f, g 4874 FROM t1 4875 RIGHT JOIN t2 USING(a,b) 4876 FULL JOIN t3 USING(a,b) 4877 INNER JOIN t4 USING(a,b) 4878 INNER JOIN t5 USING(a,b) 4879 WHERE a<>13 4880 ORDER BY 1 NULLS FIRST; 4881} { 4882 15 35 35 35 35 35 4883 19 - - 39 39 39 4884} 4885do_execsql_test joinB-355 { 4886 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4887 FROM t1 4888 RIGHT JOIN t2 USING(a) 4889 FULL JOIN t3 USING(a) 4890 INNER JOIN t4 USING(a) 4891 LEFT JOIN t5 USING(a) 4892 ORDER BY 1 NULLS FIRST; 4893} { 4894 11 - - 11 11 - 4895 13 - 13 13 13 - 4896 15 15 15 15 15 15 4897 19 - - 19 19 19 4898} 4899do_execsql_test joinB-356 { 4900 SELECT a, c, d, e, f, g 4901 FROM t1 4902 RIGHT JOIN (t2 FULL JOIN t3 USING(a)) USING(a) 4903 INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 4904 WHERE a<=18 4905 ORDER BY 1 NULLS FIRST; 4906} { 4907 11 31 - 31 31 - 4908 13 - 33 33 33 - 4909 15 35 35 35 35 35 4910} 4911do_execsql_test joinB-357 { 4912 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4913 FROM t1 4914 RIGHT JOIN t2 USING(a) 4915 FULL JOIN t3 USING(a) 4916 INNER JOIN t4 USING(a) 4917 RIGHT JOIN t5 USING(a) 4918 ORDER BY 1 NULLS FIRST; 4919} { 4920 15 15 15 15 15 15 4921 17 - - - - 17 4922 18 - - - - 18 4923 19 - - 19 19 19 4924} 4925do_execsql_test joinB-358 { 4926 SELECT a, b, c, d, e, f, g 4927 FROM t1 4928 RIGHT JOIN t2 USING(a,b) 4929 FULL JOIN t3 USING(a,b) 4930 INNER JOIN t4 USING(a,b) 4931 RIGHT JOIN t5 USING(a,b) 4932 WHERE d<>33 OR d IS NULL 4933 ORDER BY 1 NULLS FIRST; 4934} { 4935 15 25 35 35 35 35 35 4936 17 27 - - - - 37 4937 18 28 - - - - 38 4938 19 29 - - 39 39 39 4939} 4940do_execsql_test joinB-359 { 4941 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4942 FROM t1 4943 RIGHT JOIN t2 USING(a) 4944 FULL JOIN t3 USING(a) 4945 INNER JOIN t4 USING(a) 4946 FULL JOIN t5 USING(a) 4947 ORDER BY 1 NULLS FIRST; 4948} { 4949 11 - - 11 11 - 4950 13 - 13 13 13 - 4951 15 15 15 15 15 15 4952 17 - - - - 17 4953 18 - - - - 18 4954 19 - - 19 19 19 4955} 4956do_execsql_test joinB-360 { 4957 SELECT b, c, d, e, f, g 4958 FROM t1 4959 NATURAL RIGHT JOIN t2 4960 NATURAL FULL JOIN t3 4961 NATURAL INNER JOIN t4 4962 NATURAL FULL JOIN t5 4963 WHERE b BETWEEN 12 AND 17 4964 ORDER BY 1 NULLS FIRST; 4965} { 4966} 4967do_execsql_test joinB-361 { 4968 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4969 FROM t1 4970 RIGHT JOIN t2 USING(a) 4971 FULL JOIN t3 USING(a) 4972 LEFT JOIN t4 USING(a) 4973 INNER JOIN t5 USING(a) 4974 ORDER BY 1 NULLS FIRST; 4975} { 4976 15 15 15 15 15 15 4977 17 - - 17 - 17 4978 18 - 18 18 - 18 4979 19 - - 19 19 19 4980} 4981do_execsql_test joinB-362 { 4982 SELECT a, c, d, e, f, g 4983 FROM t1 4984 RIGHT JOIN t2 USING(a,b) 4985 FULL JOIN t3 USING(a,b) 4986 LEFT JOIN t4 USING(a,b) 4987 INNER JOIN t5 USING(a,b) 4988 WHERE a<>13 4989 ORDER BY 1 NULLS FIRST; 4990} { 4991 15 35 35 35 35 35 4992 17 - - 37 - 37 4993 18 - 38 38 - 38 4994 19 - - 39 39 39 4995} 4996do_execsql_test joinB-363 { 4997 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 4998 FROM t1 4999 RIGHT JOIN t2 USING(a) 5000 FULL JOIN t3 USING(a) 5001 LEFT JOIN t4 USING(a) 5002 LEFT JOIN t5 USING(a) 5003 ORDER BY 1 NULLS FIRST; 5004} { 5005 - - - - - - 5006 - - - - - - 5007 11 - - 11 11 - 5008 12 12 12 12 - - 5009 13 - 13 13 13 - 5010 15 15 15 15 15 15 5011 17 - - 17 - 17 5012 18 - 18 18 - 18 5013 19 - - 19 19 19 5014} 5015do_execsql_test joinB-364 { 5016 SELECT a, c, d, e, f, g 5017 FROM t1 5018 RIGHT JOIN (t2 FULL JOIN t3 USING(a)) USING(a) 5019 LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 5020 WHERE a<=18 5021 ORDER BY 1 NULLS FIRST; 5022} { 5023 11 31 - 31 31 - 5024 12 32 32 32 - - 5025 13 - 33 33 33 - 5026 15 35 35 35 35 35 5027 17 37 - 37 - - 5028 18 - 38 38 - - 5029} 5030do_execsql_test joinB-365 { 5031 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5032 FROM t1 5033 RIGHT JOIN t2 USING(a) 5034 FULL JOIN t3 USING(a) 5035 LEFT JOIN t4 USING(a) 5036 RIGHT JOIN t5 USING(a) 5037 ORDER BY 1 NULLS FIRST; 5038} { 5039 15 15 15 15 15 15 5040 17 - - 17 - 17 5041 18 - 18 18 - 18 5042 19 - - 19 19 19 5043} 5044do_execsql_test joinB-366 { 5045 SELECT a, b, c, d, e, f, g 5046 FROM t1 5047 RIGHT JOIN t2 USING(a,b) 5048 FULL JOIN t3 USING(a,b) 5049 LEFT JOIN t4 USING(a,b) 5050 RIGHT JOIN t5 USING(a,b) 5051 WHERE d<>33 OR d IS NULL 5052 ORDER BY 1 NULLS FIRST; 5053} { 5054 15 25 35 35 35 35 35 5055 17 27 - - 37 - 37 5056 18 28 - 38 38 - 38 5057 19 29 - - 39 39 39 5058} 5059do_execsql_test joinB-367 { 5060 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5061 FROM t1 5062 RIGHT JOIN t2 USING(a) 5063 FULL JOIN t3 USING(a) 5064 LEFT JOIN t4 USING(a) 5065 FULL JOIN t5 USING(a) 5066 ORDER BY 1 NULLS FIRST; 5067} { 5068 - - - - - - 5069 - - - - - - 5070 11 - - 11 11 - 5071 12 12 12 12 - - 5072 13 - 13 13 13 - 5073 15 15 15 15 15 15 5074 17 - - 17 - 17 5075 18 - 18 18 - 18 5076 19 - - 19 19 19 5077} 5078do_execsql_test joinB-368 { 5079 SELECT b, c, d, e, f, g 5080 FROM t1 5081 NATURAL RIGHT JOIN t2 5082 NATURAL FULL JOIN t3 5083 NATURAL LEFT JOIN t4 5084 NATURAL FULL JOIN t5 5085 WHERE b BETWEEN 12 AND 17 5086 ORDER BY 1 NULLS FIRST; 5087} { 5088} 5089do_execsql_test joinB-369 { 5090 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5091 FROM t1 5092 RIGHT JOIN t2 USING(a) 5093 FULL JOIN t3 USING(a) 5094 RIGHT JOIN t4 USING(a) 5095 INNER JOIN t5 USING(a) 5096 ORDER BY 1 NULLS FIRST; 5097} { 5098 15 15 15 15 15 15 5099 19 - - 19 19 19 5100} 5101do_execsql_test joinB-370 { 5102 SELECT a, c, d, e, f, g 5103 FROM t1 5104 RIGHT JOIN t2 USING(a,b) 5105 FULL JOIN t3 USING(a,b) 5106 RIGHT JOIN t4 USING(a,b) 5107 INNER JOIN t5 USING(a,b) 5108 WHERE a<>13 5109 ORDER BY 1 NULLS FIRST; 5110} { 5111 15 35 35 35 35 35 5112 19 - - 39 39 39 5113} 5114do_execsql_test joinB-371 { 5115 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5116 FROM t1 5117 RIGHT JOIN t2 USING(a) 5118 FULL JOIN t3 USING(a) 5119 RIGHT JOIN t4 USING(a) 5120 LEFT JOIN t5 USING(a) 5121 ORDER BY 1 NULLS FIRST; 5122} { 5123 11 - - 11 11 - 5124 13 - 13 13 13 - 5125 15 15 15 15 15 15 5126 19 - - 19 19 19 5127} 5128do_execsql_test joinB-372 { 5129 SELECT a, c, d, e, f, g 5130 FROM t1 5131 RIGHT JOIN (t2 FULL JOIN t3 USING(a)) USING(a) 5132 RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 5133 WHERE a<=18 5134 ORDER BY 1 NULLS FIRST; 5135} { 5136 11 31 - 31 31 - 5137 13 - 33 33 33 - 5138 15 35 35 35 35 35 5139} 5140do_execsql_test joinB-373 { 5141 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5142 FROM t1 5143 RIGHT JOIN t2 USING(a) 5144 FULL JOIN t3 USING(a) 5145 RIGHT JOIN t4 USING(a) 5146 RIGHT JOIN t5 USING(a) 5147 ORDER BY 1 NULLS FIRST; 5148} { 5149 15 15 15 15 15 15 5150 17 - - - - 17 5151 18 - - - - 18 5152 19 - - 19 19 19 5153} 5154do_execsql_test joinB-374 { 5155 SELECT a, b, c, d, e, f, g 5156 FROM t1 5157 RIGHT JOIN t2 USING(a,b) 5158 FULL JOIN t3 USING(a,b) 5159 RIGHT JOIN t4 USING(a,b) 5160 RIGHT JOIN t5 USING(a,b) 5161 WHERE d<>33 OR d IS NULL 5162 ORDER BY 1 NULLS FIRST; 5163} { 5164 15 25 35 35 35 35 35 5165 17 27 - - - - 37 5166 18 28 - - - - 38 5167 19 29 - - 39 39 39 5168} 5169do_execsql_test joinB-375 { 5170 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5171 FROM t1 5172 RIGHT JOIN t2 USING(a) 5173 FULL JOIN t3 USING(a) 5174 RIGHT JOIN t4 USING(a) 5175 FULL JOIN t5 USING(a) 5176 ORDER BY 1 NULLS FIRST; 5177} { 5178 11 - - 11 11 - 5179 13 - 13 13 13 - 5180 15 15 15 15 15 15 5181 17 - - - - 17 5182 18 - - - - 18 5183 19 - - 19 19 19 5184} 5185do_execsql_test joinB-376 { 5186 SELECT b, c, d, e, f, g 5187 FROM t1 5188 NATURAL RIGHT JOIN t2 5189 NATURAL FULL JOIN t3 5190 NATURAL RIGHT JOIN t4 5191 NATURAL FULL JOIN t5 5192 WHERE b BETWEEN 12 AND 17 5193 ORDER BY 1 NULLS FIRST; 5194} { 5195} 5196do_execsql_test joinB-377 { 5197 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5198 FROM t1 5199 RIGHT JOIN t2 USING(a) 5200 FULL JOIN t3 USING(a) 5201 FULL JOIN t4 USING(a) 5202 INNER JOIN t5 USING(a) 5203 ORDER BY 1 NULLS FIRST; 5204} { 5205 15 15 15 15 15 15 5206 17 - - 17 - 17 5207 18 - 18 18 - 18 5208 19 - - 19 19 19 5209} 5210do_execsql_test joinB-378 { 5211 SELECT a, c, d, e, f, g 5212 FROM t1 5213 RIGHT JOIN t2 USING(a,b) 5214 FULL JOIN t3 USING(a,b) 5215 FULL JOIN t4 USING(a,b) 5216 INNER JOIN t5 USING(a,b) 5217 WHERE a<>13 5218 ORDER BY 1 NULLS FIRST; 5219} { 5220 15 35 35 35 35 35 5221 17 - - 37 - 37 5222 18 - 38 38 - 38 5223 19 - - 39 39 39 5224} 5225do_execsql_test joinB-379 { 5226 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5227 FROM t1 5228 RIGHT JOIN t2 USING(a) 5229 FULL JOIN t3 USING(a) 5230 FULL JOIN t4 USING(a) 5231 LEFT JOIN t5 USING(a) 5232 ORDER BY 1 NULLS FIRST; 5233} { 5234 - - - - - - 5235 - - - - - - 5236 11 - - 11 11 - 5237 12 12 12 12 - - 5238 13 - 13 13 13 - 5239 15 15 15 15 15 15 5240 17 - - 17 - 17 5241 18 - 18 18 - 18 5242 19 - - 19 19 19 5243} 5244do_execsql_test joinB-380 { 5245 SELECT a, c, d, e, f, g 5246 FROM t1 5247 RIGHT JOIN (t2 FULL JOIN t3 USING(a)) USING(a) 5248 FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 5249 WHERE a<=18 5250 ORDER BY 1 NULLS FIRST; 5251} { 5252 11 31 - 31 31 - 5253 12 32 32 32 - - 5254 13 - 33 33 33 - 5255 15 35 35 35 35 35 5256 17 37 - 37 - - 5257 18 - 38 38 - - 5258} 5259do_execsql_test joinB-381 { 5260 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5261 FROM t1 5262 RIGHT JOIN t2 USING(a) 5263 FULL JOIN t3 USING(a) 5264 FULL JOIN t4 USING(a) 5265 RIGHT JOIN t5 USING(a) 5266 ORDER BY 1 NULLS FIRST; 5267} { 5268 15 15 15 15 15 15 5269 17 - - 17 - 17 5270 18 - 18 18 - 18 5271 19 - - 19 19 19 5272} 5273do_execsql_test joinB-382 { 5274 SELECT a, b, c, d, e, f, g 5275 FROM t1 5276 RIGHT JOIN t2 USING(a,b) 5277 FULL JOIN t3 USING(a,b) 5278 FULL JOIN t4 USING(a,b) 5279 RIGHT JOIN t5 USING(a,b) 5280 WHERE d<>33 OR d IS NULL 5281 ORDER BY 1 NULLS FIRST; 5282} { 5283 15 25 35 35 35 35 35 5284 17 27 - - 37 - 37 5285 18 28 - 38 38 - 38 5286 19 29 - - 39 39 39 5287} 5288do_execsql_test joinB-383 { 5289 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5290 FROM t1 5291 RIGHT JOIN t2 USING(a) 5292 FULL JOIN t3 USING(a) 5293 FULL JOIN t4 USING(a) 5294 FULL JOIN t5 USING(a) 5295 ORDER BY 1 NULLS FIRST; 5296} { 5297 - - - - - - 5298 - - - - - - 5299 11 - - 11 11 - 5300 12 12 12 12 - - 5301 13 - 13 13 13 - 5302 15 15 15 15 15 15 5303 17 - - 17 - 17 5304 18 - 18 18 - 18 5305 19 - - 19 19 19 5306} 5307do_execsql_test joinB-384 { 5308 SELECT b, c, d, e, f, g 5309 FROM t1 5310 NATURAL RIGHT JOIN t2 5311 NATURAL FULL JOIN t3 5312 NATURAL FULL JOIN t4 5313 NATURAL FULL JOIN t5 5314 WHERE b BETWEEN 12 AND 17 5315 ORDER BY 1 NULLS FIRST; 5316} { 5317} 5318do_execsql_test joinB-385 { 5319 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5320 FROM t1 5321 FULL JOIN t2 USING(a) 5322 INNER JOIN t3 USING(a) 5323 INNER JOIN t4 USING(a) 5324 INNER JOIN t5 USING(a) 5325 ORDER BY 1 NULLS FIRST; 5326} { 5327 15 15 15 15 15 15 5328} 5329do_execsql_test joinB-386 { 5330 SELECT a, c, d, e, f, g 5331 FROM t1 5332 FULL JOIN t2 USING(a,b) 5333 INNER JOIN t3 USING(a,b) 5334 INNER JOIN t4 USING(a,b) 5335 INNER JOIN t5 USING(a,b) 5336 WHERE a<>13 5337 ORDER BY 1 NULLS FIRST; 5338} { 5339 15 35 35 35 35 35 5340} 5341do_execsql_test joinB-387 { 5342 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5343 FROM t1 5344 FULL JOIN t2 USING(a) 5345 INNER JOIN t3 USING(a) 5346 INNER JOIN t4 USING(a) 5347 LEFT JOIN t5 USING(a) 5348 ORDER BY 1 NULLS FIRST; 5349} { 5350 11 11 - 11 11 - 5351 13 - 13 13 13 - 5352 15 15 15 15 15 15 5353} 5354do_execsql_test joinB-388 { 5355 SELECT a, c, d, e, f, g 5356 FROM t1 5357 FULL JOIN (t2 INNER JOIN t3 USING(a)) USING(a) 5358 INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 5359 WHERE a<=18 5360 ORDER BY 1 NULLS FIRST; 5361} { 5362 11 31 - - 31 - 5363 13 - 33 33 33 - 5364 15 35 35 35 35 35 5365} 5366do_execsql_test joinB-389 { 5367 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5368 FROM t1 5369 FULL JOIN t2 USING(a) 5370 INNER JOIN t3 USING(a) 5371 INNER JOIN t4 USING(a) 5372 RIGHT JOIN t5 USING(a) 5373 ORDER BY 1 NULLS FIRST; 5374} { 5375 15 15 15 15 15 15 5376 17 - - - - 17 5377 18 - - - - 18 5378 19 - - - - 19 5379} 5380do_execsql_test joinB-390 { 5381 SELECT a, b, c, d, e, f, g 5382 FROM t1 5383 FULL JOIN t2 USING(a,b) 5384 INNER JOIN t3 USING(a,b) 5385 INNER JOIN t4 USING(a,b) 5386 RIGHT JOIN t5 USING(a,b) 5387 WHERE d<>33 OR d IS NULL 5388 ORDER BY 1 NULLS FIRST; 5389} { 5390 15 25 35 35 35 35 35 5391 17 27 - - - - 37 5392 18 28 - - - - 38 5393 19 29 - - - - 39 5394} 5395do_execsql_test joinB-391 { 5396 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5397 FROM t1 5398 FULL JOIN t2 USING(a) 5399 INNER JOIN t3 USING(a) 5400 INNER JOIN t4 USING(a) 5401 FULL JOIN t5 USING(a) 5402 ORDER BY 1 NULLS FIRST; 5403} { 5404 11 11 - 11 11 - 5405 13 - 13 13 13 - 5406 15 15 15 15 15 15 5407 17 - - - - 17 5408 18 - - - - 18 5409 19 - - - - 19 5410} 5411do_execsql_test joinB-392 { 5412 SELECT b, c, d, e, f, g 5413 FROM t1 5414 NATURAL FULL JOIN t2 5415 NATURAL INNER JOIN t3 5416 NATURAL INNER JOIN t4 5417 NATURAL FULL JOIN t5 5418 WHERE b BETWEEN 12 AND 17 5419 ORDER BY 1 NULLS FIRST; 5420} { 5421} 5422do_execsql_test joinB-393 { 5423 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5424 FROM t1 5425 FULL JOIN t2 USING(a) 5426 INNER JOIN t3 USING(a) 5427 LEFT JOIN t4 USING(a) 5428 INNER JOIN t5 USING(a) 5429 ORDER BY 1 NULLS FIRST; 5430} { 5431 15 15 15 15 15 15 5432 17 17 - 17 - 17 5433 18 - 18 18 - 18 5434} 5435do_execsql_test joinB-394 { 5436 SELECT a, c, d, e, f, g 5437 FROM t1 5438 FULL JOIN t2 USING(a,b) 5439 INNER JOIN t3 USING(a,b) 5440 LEFT JOIN t4 USING(a,b) 5441 INNER JOIN t5 USING(a,b) 5442 WHERE a<>13 5443 ORDER BY 1 NULLS FIRST; 5444} { 5445 15 35 35 35 35 35 5446 17 37 - 37 - 37 5447 18 - 38 38 - 38 5448} 5449do_execsql_test joinB-395 { 5450 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5451 FROM t1 5452 FULL JOIN t2 USING(a) 5453 INNER JOIN t3 USING(a) 5454 LEFT JOIN t4 USING(a) 5455 LEFT JOIN t5 USING(a) 5456 ORDER BY 1 NULLS FIRST; 5457} { 5458 11 11 - 11 11 - 5459 12 12 12 12 - - 5460 13 - 13 13 13 - 5461 15 15 15 15 15 15 5462 17 17 - 17 - 17 5463 18 - 18 18 - 18 5464} 5465do_execsql_test joinB-396 { 5466 SELECT a, c, d, e, f, g 5467 FROM t1 5468 FULL JOIN (t2 INNER JOIN t3 USING(a)) USING(a) 5469 LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 5470 WHERE a<=18 5471 ORDER BY 1 NULLS FIRST; 5472} { 5473 11 31 - - 31 - 5474 12 32 32 32 - - 5475 13 - 33 33 33 - 5476 15 35 35 35 35 35 5477 17 37 - - - - 5478 18 - 38 38 - - 5479} 5480do_execsql_test joinB-397 { 5481 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5482 FROM t1 5483 FULL JOIN t2 USING(a) 5484 INNER JOIN t3 USING(a) 5485 LEFT JOIN t4 USING(a) 5486 RIGHT JOIN t5 USING(a) 5487 ORDER BY 1 NULLS FIRST; 5488} { 5489 15 15 15 15 15 15 5490 17 17 - 17 - 17 5491 18 - 18 18 - 18 5492 19 - - - - 19 5493} 5494do_execsql_test joinB-398 { 5495 SELECT a, b, c, d, e, f, g 5496 FROM t1 5497 FULL JOIN t2 USING(a,b) 5498 INNER JOIN t3 USING(a,b) 5499 LEFT JOIN t4 USING(a,b) 5500 RIGHT JOIN t5 USING(a,b) 5501 WHERE d<>33 OR d IS NULL 5502 ORDER BY 1 NULLS FIRST; 5503} { 5504 15 25 35 35 35 35 35 5505 17 27 37 - 37 - 37 5506 18 28 - 38 38 - 38 5507 19 29 - - - - 39 5508} 5509do_execsql_test joinB-399 { 5510 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5511 FROM t1 5512 FULL JOIN t2 USING(a) 5513 INNER JOIN t3 USING(a) 5514 LEFT JOIN t4 USING(a) 5515 FULL JOIN t5 USING(a) 5516 ORDER BY 1 NULLS FIRST; 5517} { 5518 11 11 - 11 11 - 5519 12 12 12 12 - - 5520 13 - 13 13 13 - 5521 15 15 15 15 15 15 5522 17 17 - 17 - 17 5523 18 - 18 18 - 18 5524 19 - - - - 19 5525} 5526do_execsql_test joinB-400 { 5527 SELECT b, c, d, e, f, g 5528 FROM t1 5529 NATURAL FULL JOIN t2 5530 NATURAL INNER JOIN t3 5531 NATURAL LEFT JOIN t4 5532 NATURAL FULL JOIN t5 5533 WHERE b BETWEEN 12 AND 17 5534 ORDER BY 1 NULLS FIRST; 5535} { 5536} 5537do_execsql_test joinB-401 { 5538 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5539 FROM t1 5540 FULL JOIN t2 USING(a) 5541 INNER JOIN t3 USING(a) 5542 RIGHT JOIN t4 USING(a) 5543 INNER JOIN t5 USING(a) 5544 ORDER BY 1 NULLS FIRST; 5545} { 5546 15 15 15 15 15 15 5547 19 - - - 19 19 5548} 5549do_execsql_test joinB-402 { 5550 SELECT a, c, d, e, f, g 5551 FROM t1 5552 FULL JOIN t2 USING(a,b) 5553 INNER JOIN t3 USING(a,b) 5554 RIGHT JOIN t4 USING(a,b) 5555 INNER JOIN t5 USING(a,b) 5556 WHERE a<>13 5557 ORDER BY 1 NULLS FIRST; 5558} { 5559 15 35 35 35 35 35 5560 19 - - - 39 39 5561} 5562do_execsql_test joinB-403 { 5563 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5564 FROM t1 5565 FULL JOIN t2 USING(a) 5566 INNER JOIN t3 USING(a) 5567 RIGHT JOIN t4 USING(a) 5568 LEFT JOIN t5 USING(a) 5569 ORDER BY 1 NULLS FIRST; 5570} { 5571 11 11 - 11 11 - 5572 13 - 13 13 13 - 5573 15 15 15 15 15 15 5574 19 - - - 19 19 5575} 5576do_execsql_test joinB-404 { 5577 SELECT a, c, d, e, f, g 5578 FROM t1 5579 FULL JOIN (t2 INNER JOIN t3 USING(a)) USING(a) 5580 RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 5581 WHERE a<=18 5582 ORDER BY 1 NULLS FIRST; 5583} { 5584 11 31 - - 31 - 5585 13 - 33 33 33 - 5586 15 35 35 35 35 35 5587} 5588do_execsql_test joinB-405 { 5589 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5590 FROM t1 5591 FULL JOIN t2 USING(a) 5592 INNER JOIN t3 USING(a) 5593 RIGHT JOIN t4 USING(a) 5594 RIGHT JOIN t5 USING(a) 5595 ORDER BY 1 NULLS FIRST; 5596} { 5597 15 15 15 15 15 15 5598 17 - - - - 17 5599 18 - - - - 18 5600 19 - - - 19 19 5601} 5602do_execsql_test joinB-406 { 5603 SELECT a, b, c, d, e, f, g 5604 FROM t1 5605 FULL JOIN t2 USING(a,b) 5606 INNER JOIN t3 USING(a,b) 5607 RIGHT JOIN t4 USING(a,b) 5608 RIGHT JOIN t5 USING(a,b) 5609 WHERE d<>33 OR d IS NULL 5610 ORDER BY 1 NULLS FIRST; 5611} { 5612 15 25 35 35 35 35 35 5613 17 27 - - - - 37 5614 18 28 - - - - 38 5615 19 29 - - - 39 39 5616} 5617do_execsql_test joinB-407 { 5618 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5619 FROM t1 5620 FULL JOIN t2 USING(a) 5621 INNER JOIN t3 USING(a) 5622 RIGHT JOIN t4 USING(a) 5623 FULL JOIN t5 USING(a) 5624 ORDER BY 1 NULLS FIRST; 5625} { 5626 11 11 - 11 11 - 5627 13 - 13 13 13 - 5628 15 15 15 15 15 15 5629 17 - - - - 17 5630 18 - - - - 18 5631 19 - - - 19 19 5632} 5633do_execsql_test joinB-408 { 5634 SELECT b, c, d, e, f, g 5635 FROM t1 5636 NATURAL FULL JOIN t2 5637 NATURAL INNER JOIN t3 5638 NATURAL RIGHT JOIN t4 5639 NATURAL FULL JOIN t5 5640 WHERE b BETWEEN 12 AND 17 5641 ORDER BY 1 NULLS FIRST; 5642} { 5643} 5644do_execsql_test joinB-409 { 5645 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5646 FROM t1 5647 FULL JOIN t2 USING(a) 5648 INNER JOIN t3 USING(a) 5649 FULL JOIN t4 USING(a) 5650 INNER JOIN t5 USING(a) 5651 ORDER BY 1 NULLS FIRST; 5652} { 5653 15 15 15 15 15 15 5654 17 17 - 17 - 17 5655 18 - 18 18 - 18 5656 19 - - - 19 19 5657} 5658do_execsql_test joinB-410 { 5659 SELECT a, c, d, e, f, g 5660 FROM t1 5661 FULL JOIN t2 USING(a,b) 5662 INNER JOIN t3 USING(a,b) 5663 FULL JOIN t4 USING(a,b) 5664 INNER JOIN t5 USING(a,b) 5665 WHERE a<>13 5666 ORDER BY 1 NULLS FIRST; 5667} { 5668 15 35 35 35 35 35 5669 17 37 - 37 - 37 5670 18 - 38 38 - 38 5671 19 - - - 39 39 5672} 5673do_execsql_test joinB-411 { 5674 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5675 FROM t1 5676 FULL JOIN t2 USING(a) 5677 INNER JOIN t3 USING(a) 5678 FULL JOIN t4 USING(a) 5679 LEFT JOIN t5 USING(a) 5680 ORDER BY 1 NULLS FIRST; 5681} { 5682 11 11 - 11 11 - 5683 12 12 12 12 - - 5684 13 - 13 13 13 - 5685 15 15 15 15 15 15 5686 17 17 - 17 - 17 5687 18 - 18 18 - 18 5688 19 - - - 19 19 5689} 5690do_execsql_test joinB-412 { 5691 SELECT a, c, d, e, f, g 5692 FROM t1 5693 FULL JOIN (t2 INNER JOIN t3 USING(a)) USING(a) 5694 FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 5695 WHERE a<=18 5696 ORDER BY 1 NULLS FIRST; 5697} { 5698 11 31 - - 31 - 5699 12 32 32 32 - - 5700 13 - 33 33 33 - 5701 15 35 35 35 35 35 5702 17 37 - - - - 5703 18 - 38 38 - - 5704} 5705do_execsql_test joinB-413 { 5706 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5707 FROM t1 5708 FULL JOIN t2 USING(a) 5709 INNER JOIN t3 USING(a) 5710 FULL JOIN t4 USING(a) 5711 RIGHT JOIN t5 USING(a) 5712 ORDER BY 1 NULLS FIRST; 5713} { 5714 15 15 15 15 15 15 5715 17 17 - 17 - 17 5716 18 - 18 18 - 18 5717 19 - - - 19 19 5718} 5719do_execsql_test joinB-414 { 5720 SELECT a, b, c, d, e, f, g 5721 FROM t1 5722 FULL JOIN t2 USING(a,b) 5723 INNER JOIN t3 USING(a,b) 5724 FULL JOIN t4 USING(a,b) 5725 RIGHT JOIN t5 USING(a,b) 5726 WHERE d<>33 OR d IS NULL 5727 ORDER BY 1 NULLS FIRST; 5728} { 5729 15 25 35 35 35 35 35 5730 17 27 37 - 37 - 37 5731 18 28 - 38 38 - 38 5732 19 29 - - - 39 39 5733} 5734do_execsql_test joinB-415 { 5735 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5736 FROM t1 5737 FULL JOIN t2 USING(a) 5738 INNER JOIN t3 USING(a) 5739 FULL JOIN t4 USING(a) 5740 FULL JOIN t5 USING(a) 5741 ORDER BY 1 NULLS FIRST; 5742} { 5743 11 11 - 11 11 - 5744 12 12 12 12 - - 5745 13 - 13 13 13 - 5746 15 15 15 15 15 15 5747 17 17 - 17 - 17 5748 18 - 18 18 - 18 5749 19 - - - 19 19 5750} 5751do_execsql_test joinB-416 { 5752 SELECT b, c, d, e, f, g 5753 FROM t1 5754 NATURAL FULL JOIN t2 5755 NATURAL INNER JOIN t3 5756 NATURAL FULL JOIN t4 5757 NATURAL FULL JOIN t5 5758 WHERE b BETWEEN 12 AND 17 5759 ORDER BY 1 NULLS FIRST; 5760} { 5761} 5762do_execsql_test joinB-417 { 5763 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5764 FROM t1 5765 FULL JOIN t2 USING(a) 5766 LEFT JOIN t3 USING(a) 5767 INNER JOIN t4 USING(a) 5768 INNER JOIN t5 USING(a) 5769 ORDER BY 1 NULLS FIRST; 5770} { 5771 15 15 15 15 15 15 5772} 5773do_execsql_test joinB-418 { 5774 SELECT a, c, d, e, f, g 5775 FROM t1 5776 FULL JOIN t2 USING(a,b) 5777 LEFT JOIN t3 USING(a,b) 5778 INNER JOIN t4 USING(a,b) 5779 INNER JOIN t5 USING(a,b) 5780 WHERE a<>13 5781 ORDER BY 1 NULLS FIRST; 5782} { 5783 15 35 35 35 35 35 5784} 5785do_execsql_test joinB-419 { 5786 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5787 FROM t1 5788 FULL JOIN t2 USING(a) 5789 LEFT JOIN t3 USING(a) 5790 INNER JOIN t4 USING(a) 5791 LEFT JOIN t5 USING(a) 5792 ORDER BY 1 NULLS FIRST; 5793} { 5794 11 11 - 11 11 - 5795 13 - 13 13 13 - 5796 15 15 15 15 15 15 5797} 5798do_execsql_test joinB-420 { 5799 SELECT a, c, d, e, f, g 5800 FROM t1 5801 FULL JOIN (t2 LEFT JOIN t3 USING(a)) USING(a) 5802 INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 5803 WHERE a<=18 5804 ORDER BY 1 NULLS FIRST; 5805} { 5806 11 31 - - 31 - 5807 13 - 33 33 33 - 5808 15 35 35 35 35 35 5809} 5810do_execsql_test joinB-421 { 5811 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5812 FROM t1 5813 FULL JOIN t2 USING(a) 5814 LEFT JOIN t3 USING(a) 5815 INNER JOIN t4 USING(a) 5816 RIGHT JOIN t5 USING(a) 5817 ORDER BY 1 NULLS FIRST; 5818} { 5819 15 15 15 15 15 15 5820 17 - - - - 17 5821 18 - - - - 18 5822 19 - - - - 19 5823} 5824do_execsql_test joinB-422 { 5825 SELECT a, b, c, d, e, f, g 5826 FROM t1 5827 FULL JOIN t2 USING(a,b) 5828 LEFT JOIN t3 USING(a,b) 5829 INNER JOIN t4 USING(a,b) 5830 RIGHT JOIN t5 USING(a,b) 5831 WHERE d<>33 OR d IS NULL 5832 ORDER BY 1 NULLS FIRST; 5833} { 5834 15 25 35 35 35 35 35 5835 17 27 - - - - 37 5836 18 28 - - - - 38 5837 19 29 - - - - 39 5838} 5839do_execsql_test joinB-423 { 5840 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5841 FROM t1 5842 FULL JOIN t2 USING(a) 5843 LEFT JOIN t3 USING(a) 5844 INNER JOIN t4 USING(a) 5845 FULL JOIN t5 USING(a) 5846 ORDER BY 1 NULLS FIRST; 5847} { 5848 11 11 - 11 11 - 5849 13 - 13 13 13 - 5850 15 15 15 15 15 15 5851 17 - - - - 17 5852 18 - - - - 18 5853 19 - - - - 19 5854} 5855do_execsql_test joinB-424 { 5856 SELECT b, c, d, e, f, g 5857 FROM t1 5858 NATURAL FULL JOIN t2 5859 NATURAL LEFT JOIN t3 5860 NATURAL INNER JOIN t4 5861 NATURAL FULL JOIN t5 5862 WHERE b BETWEEN 12 AND 17 5863 ORDER BY 1 NULLS FIRST; 5864} { 5865} 5866do_execsql_test joinB-425 { 5867 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5868 FROM t1 5869 FULL JOIN t2 USING(a) 5870 LEFT JOIN t3 USING(a) 5871 LEFT JOIN t4 USING(a) 5872 INNER JOIN t5 USING(a) 5873 ORDER BY 1 NULLS FIRST; 5874} { 5875 15 15 15 15 15 15 5876 17 17 - 17 - 17 5877 18 - 18 18 - 18 5878} 5879do_execsql_test joinB-426 { 5880 SELECT a, c, d, e, f, g 5881 FROM t1 5882 FULL JOIN t2 USING(a,b) 5883 LEFT JOIN t3 USING(a,b) 5884 LEFT JOIN t4 USING(a,b) 5885 INNER JOIN t5 USING(a,b) 5886 WHERE a<>13 5887 ORDER BY 1 NULLS FIRST; 5888} { 5889 15 35 35 35 35 35 5890 17 37 - 37 - 37 5891 18 - 38 38 - 38 5892} 5893do_execsql_test joinB-427 { 5894 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5895 FROM t1 5896 FULL JOIN t2 USING(a) 5897 LEFT JOIN t3 USING(a) 5898 LEFT JOIN t4 USING(a) 5899 LEFT JOIN t5 USING(a) 5900 ORDER BY 1 NULLS FIRST; 5901} { 5902 - - - - - - 5903 11 11 - 11 11 - 5904 12 12 12 12 - - 5905 13 - 13 13 13 - 5906 15 15 15 15 15 15 5907 17 17 - 17 - 17 5908 18 - 18 18 - 18 5909} 5910do_execsql_test joinB-428 { 5911 SELECT a, c, d, e, f, g 5912 FROM t1 5913 FULL JOIN (t2 LEFT JOIN t3 USING(a)) USING(a) 5914 LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 5915 WHERE a<=18 5916 ORDER BY 1 NULLS FIRST; 5917} { 5918 11 31 - - 31 - 5919 12 32 32 32 - - 5920 13 - 33 33 33 - 5921 15 35 35 35 35 35 5922 17 37 - - - - 5923 18 - 38 38 - - 5924} 5925do_execsql_test joinB-429 { 5926 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5927 FROM t1 5928 FULL JOIN t2 USING(a) 5929 LEFT JOIN t3 USING(a) 5930 LEFT JOIN t4 USING(a) 5931 RIGHT JOIN t5 USING(a) 5932 ORDER BY 1 NULLS FIRST; 5933} { 5934 15 15 15 15 15 15 5935 17 17 - 17 - 17 5936 18 - 18 18 - 18 5937 19 - - - - 19 5938} 5939do_execsql_test joinB-430 { 5940 SELECT a, b, c, d, e, f, g 5941 FROM t1 5942 FULL JOIN t2 USING(a,b) 5943 LEFT JOIN t3 USING(a,b) 5944 LEFT JOIN t4 USING(a,b) 5945 RIGHT JOIN t5 USING(a,b) 5946 WHERE d<>33 OR d IS NULL 5947 ORDER BY 1 NULLS FIRST; 5948} { 5949 15 25 35 35 35 35 35 5950 17 27 37 - 37 - 37 5951 18 28 - 38 38 - 38 5952 19 29 - - - - 39 5953} 5954do_execsql_test joinB-431 { 5955 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5956 FROM t1 5957 FULL JOIN t2 USING(a) 5958 LEFT JOIN t3 USING(a) 5959 LEFT JOIN t4 USING(a) 5960 FULL JOIN t5 USING(a) 5961 ORDER BY 1 NULLS FIRST; 5962} { 5963 - - - - - - 5964 11 11 - 11 11 - 5965 12 12 12 12 - - 5966 13 - 13 13 13 - 5967 15 15 15 15 15 15 5968 17 17 - 17 - 17 5969 18 - 18 18 - 18 5970 19 - - - - 19 5971} 5972do_execsql_test joinB-432 { 5973 SELECT b, c, d, e, f, g 5974 FROM t1 5975 NATURAL FULL JOIN t2 5976 NATURAL LEFT JOIN t3 5977 NATURAL LEFT JOIN t4 5978 NATURAL FULL JOIN t5 5979 WHERE b BETWEEN 12 AND 17 5980 ORDER BY 1 NULLS FIRST; 5981} { 5982} 5983do_execsql_test joinB-433 { 5984 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 5985 FROM t1 5986 FULL JOIN t2 USING(a) 5987 LEFT JOIN t3 USING(a) 5988 RIGHT JOIN t4 USING(a) 5989 INNER JOIN t5 USING(a) 5990 ORDER BY 1 NULLS FIRST; 5991} { 5992 15 15 15 15 15 15 5993 19 - - - 19 19 5994} 5995do_execsql_test joinB-434 { 5996 SELECT a, c, d, e, f, g 5997 FROM t1 5998 FULL JOIN t2 USING(a,b) 5999 LEFT JOIN t3 USING(a,b) 6000 RIGHT JOIN t4 USING(a,b) 6001 INNER JOIN t5 USING(a,b) 6002 WHERE a<>13 6003 ORDER BY 1 NULLS FIRST; 6004} { 6005 15 35 35 35 35 35 6006 19 - - - 39 39 6007} 6008do_execsql_test joinB-435 { 6009 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6010 FROM t1 6011 FULL JOIN t2 USING(a) 6012 LEFT JOIN t3 USING(a) 6013 RIGHT JOIN t4 USING(a) 6014 LEFT JOIN t5 USING(a) 6015 ORDER BY 1 NULLS FIRST; 6016} { 6017 11 11 - 11 11 - 6018 13 - 13 13 13 - 6019 15 15 15 15 15 15 6020 19 - - - 19 19 6021} 6022do_execsql_test joinB-436 { 6023 SELECT a, c, d, e, f, g 6024 FROM t1 6025 FULL JOIN (t2 LEFT JOIN t3 USING(a)) USING(a) 6026 RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 6027 WHERE a<=18 6028 ORDER BY 1 NULLS FIRST; 6029} { 6030 11 31 - - 31 - 6031 13 - 33 33 33 - 6032 15 35 35 35 35 35 6033} 6034do_execsql_test joinB-437 { 6035 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6036 FROM t1 6037 FULL JOIN t2 USING(a) 6038 LEFT JOIN t3 USING(a) 6039 RIGHT JOIN t4 USING(a) 6040 RIGHT JOIN t5 USING(a) 6041 ORDER BY 1 NULLS FIRST; 6042} { 6043 15 15 15 15 15 15 6044 17 - - - - 17 6045 18 - - - - 18 6046 19 - - - 19 19 6047} 6048do_execsql_test joinB-438 { 6049 SELECT a, b, c, d, e, f, g 6050 FROM t1 6051 FULL JOIN t2 USING(a,b) 6052 LEFT JOIN t3 USING(a,b) 6053 RIGHT JOIN t4 USING(a,b) 6054 RIGHT JOIN t5 USING(a,b) 6055 WHERE d<>33 OR d IS NULL 6056 ORDER BY 1 NULLS FIRST; 6057} { 6058 15 25 35 35 35 35 35 6059 17 27 - - - - 37 6060 18 28 - - - - 38 6061 19 29 - - - 39 39 6062} 6063do_execsql_test joinB-439 { 6064 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6065 FROM t1 6066 FULL JOIN t2 USING(a) 6067 LEFT JOIN t3 USING(a) 6068 RIGHT JOIN t4 USING(a) 6069 FULL JOIN t5 USING(a) 6070 ORDER BY 1 NULLS FIRST; 6071} { 6072 11 11 - 11 11 - 6073 13 - 13 13 13 - 6074 15 15 15 15 15 15 6075 17 - - - - 17 6076 18 - - - - 18 6077 19 - - - 19 19 6078} 6079do_execsql_test joinB-440 { 6080 SELECT b, c, d, e, f, g 6081 FROM t1 6082 NATURAL FULL JOIN t2 6083 NATURAL LEFT JOIN t3 6084 NATURAL RIGHT JOIN t4 6085 NATURAL FULL JOIN t5 6086 WHERE b BETWEEN 12 AND 17 6087 ORDER BY 1 NULLS FIRST; 6088} { 6089} 6090do_execsql_test joinB-441 { 6091 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6092 FROM t1 6093 FULL JOIN t2 USING(a) 6094 LEFT JOIN t3 USING(a) 6095 FULL JOIN t4 USING(a) 6096 INNER JOIN t5 USING(a) 6097 ORDER BY 1 NULLS FIRST; 6098} { 6099 15 15 15 15 15 15 6100 17 17 - 17 - 17 6101 18 - 18 18 - 18 6102 19 - - - 19 19 6103} 6104do_execsql_test joinB-442 { 6105 SELECT a, c, d, e, f, g 6106 FROM t1 6107 FULL JOIN t2 USING(a,b) 6108 LEFT JOIN t3 USING(a,b) 6109 FULL JOIN t4 USING(a,b) 6110 INNER JOIN t5 USING(a,b) 6111 WHERE a<>13 6112 ORDER BY 1 NULLS FIRST; 6113} { 6114 15 35 35 35 35 35 6115 17 37 - 37 - 37 6116 18 - 38 38 - 38 6117 19 - - - 39 39 6118} 6119do_execsql_test joinB-443 { 6120 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6121 FROM t1 6122 FULL JOIN t2 USING(a) 6123 LEFT JOIN t3 USING(a) 6124 FULL JOIN t4 USING(a) 6125 LEFT JOIN t5 USING(a) 6126 ORDER BY 1 NULLS FIRST; 6127} { 6128 - - - - - - 6129 11 11 - 11 11 - 6130 12 12 12 12 - - 6131 13 - 13 13 13 - 6132 15 15 15 15 15 15 6133 17 17 - 17 - 17 6134 18 - 18 18 - 18 6135 19 - - - 19 19 6136} 6137do_execsql_test joinB-444 { 6138 SELECT a, c, d, e, f, g 6139 FROM t1 6140 FULL JOIN (t2 LEFT JOIN t3 USING(a)) USING(a) 6141 FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 6142 WHERE a<=18 6143 ORDER BY 1 NULLS FIRST; 6144} { 6145 11 31 - - 31 - 6146 12 32 32 32 - - 6147 13 - 33 33 33 - 6148 15 35 35 35 35 35 6149 17 37 - - - - 6150 18 - 38 38 - - 6151} 6152do_execsql_test joinB-445 { 6153 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6154 FROM t1 6155 FULL JOIN t2 USING(a) 6156 LEFT JOIN t3 USING(a) 6157 FULL JOIN t4 USING(a) 6158 RIGHT JOIN t5 USING(a) 6159 ORDER BY 1 NULLS FIRST; 6160} { 6161 15 15 15 15 15 15 6162 17 17 - 17 - 17 6163 18 - 18 18 - 18 6164 19 - - - 19 19 6165} 6166do_execsql_test joinB-446 { 6167 SELECT a, b, c, d, e, f, g 6168 FROM t1 6169 FULL JOIN t2 USING(a,b) 6170 LEFT JOIN t3 USING(a,b) 6171 FULL JOIN t4 USING(a,b) 6172 RIGHT JOIN t5 USING(a,b) 6173 WHERE d<>33 OR d IS NULL 6174 ORDER BY 1 NULLS FIRST; 6175} { 6176 15 25 35 35 35 35 35 6177 17 27 37 - 37 - 37 6178 18 28 - 38 38 - 38 6179 19 29 - - - 39 39 6180} 6181do_execsql_test joinB-447 { 6182 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6183 FROM t1 6184 FULL JOIN t2 USING(a) 6185 LEFT JOIN t3 USING(a) 6186 FULL JOIN t4 USING(a) 6187 FULL JOIN t5 USING(a) 6188 ORDER BY 1 NULLS FIRST; 6189} { 6190 - - - - - - 6191 11 11 - 11 11 - 6192 12 12 12 12 - - 6193 13 - 13 13 13 - 6194 15 15 15 15 15 15 6195 17 17 - 17 - 17 6196 18 - 18 18 - 18 6197 19 - - - 19 19 6198} 6199do_execsql_test joinB-448 { 6200 SELECT b, c, d, e, f, g 6201 FROM t1 6202 NATURAL FULL JOIN t2 6203 NATURAL LEFT JOIN t3 6204 NATURAL FULL JOIN t4 6205 NATURAL FULL JOIN t5 6206 WHERE b BETWEEN 12 AND 17 6207 ORDER BY 1 NULLS FIRST; 6208} { 6209} 6210do_execsql_test joinB-449 { 6211 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6212 FROM t1 6213 FULL JOIN t2 USING(a) 6214 RIGHT JOIN t3 USING(a) 6215 INNER JOIN t4 USING(a) 6216 INNER JOIN t5 USING(a) 6217 ORDER BY 1 NULLS FIRST; 6218} { 6219 15 15 15 15 15 15 6220 19 - - 19 19 19 6221} 6222do_execsql_test joinB-450 { 6223 SELECT a, c, d, e, f, g 6224 FROM t1 6225 FULL JOIN t2 USING(a,b) 6226 RIGHT JOIN t3 USING(a,b) 6227 INNER JOIN t4 USING(a,b) 6228 INNER JOIN t5 USING(a,b) 6229 WHERE a<>13 6230 ORDER BY 1 NULLS FIRST; 6231} { 6232 15 35 35 35 35 35 6233 19 - - 39 39 39 6234} 6235do_execsql_test joinB-451 { 6236 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6237 FROM t1 6238 FULL JOIN t2 USING(a) 6239 RIGHT JOIN t3 USING(a) 6240 INNER JOIN t4 USING(a) 6241 LEFT JOIN t5 USING(a) 6242 ORDER BY 1 NULLS FIRST; 6243} { 6244 11 11 - 11 11 - 6245 13 - 13 13 13 - 6246 15 15 15 15 15 15 6247 19 - - 19 19 19 6248} 6249do_execsql_test joinB-452 { 6250 SELECT a, c, d, e, f, g 6251 FROM t1 6252 FULL JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a) 6253 INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 6254 WHERE a<=18 6255 ORDER BY 1 NULLS FIRST; 6256} { 6257 11 31 - 31 31 - 6258 13 - 33 33 33 - 6259 15 35 35 35 35 35 6260} 6261do_execsql_test joinB-453 { 6262 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6263 FROM t1 6264 FULL JOIN t2 USING(a) 6265 RIGHT JOIN t3 USING(a) 6266 INNER JOIN t4 USING(a) 6267 RIGHT JOIN t5 USING(a) 6268 ORDER BY 1 NULLS FIRST; 6269} { 6270 15 15 15 15 15 15 6271 17 - - - - 17 6272 18 - - - - 18 6273 19 - - 19 19 19 6274} 6275do_execsql_test joinB-454 { 6276 SELECT a, b, c, d, e, f, g 6277 FROM t1 6278 FULL JOIN t2 USING(a,b) 6279 RIGHT JOIN t3 USING(a,b) 6280 INNER JOIN t4 USING(a,b) 6281 RIGHT JOIN t5 USING(a,b) 6282 WHERE d<>33 OR d IS NULL 6283 ORDER BY 1 NULLS FIRST; 6284} { 6285 15 25 35 35 35 35 35 6286 17 27 - - - - 37 6287 18 28 - - - - 38 6288 19 29 - - 39 39 39 6289} 6290do_execsql_test joinB-455 { 6291 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6292 FROM t1 6293 FULL JOIN t2 USING(a) 6294 RIGHT JOIN t3 USING(a) 6295 INNER JOIN t4 USING(a) 6296 FULL JOIN t5 USING(a) 6297 ORDER BY 1 NULLS FIRST; 6298} { 6299 11 11 - 11 11 - 6300 13 - 13 13 13 - 6301 15 15 15 15 15 15 6302 17 - - - - 17 6303 18 - - - - 18 6304 19 - - 19 19 19 6305} 6306do_execsql_test joinB-456 { 6307 SELECT b, c, d, e, f, g 6308 FROM t1 6309 NATURAL FULL JOIN t2 6310 NATURAL RIGHT JOIN t3 6311 NATURAL INNER JOIN t4 6312 NATURAL FULL JOIN t5 6313 WHERE b BETWEEN 12 AND 17 6314 ORDER BY 1 NULLS FIRST; 6315} { 6316} 6317do_execsql_test joinB-457 { 6318 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6319 FROM t1 6320 FULL JOIN t2 USING(a) 6321 RIGHT JOIN t3 USING(a) 6322 LEFT JOIN t4 USING(a) 6323 INNER JOIN t5 USING(a) 6324 ORDER BY 1 NULLS FIRST; 6325} { 6326 15 15 15 15 15 15 6327 17 17 - 17 - 17 6328 18 - 18 18 - 18 6329 19 - - 19 19 19 6330} 6331do_execsql_test joinB-458 { 6332 SELECT a, c, d, e, f, g 6333 FROM t1 6334 FULL JOIN t2 USING(a,b) 6335 RIGHT JOIN t3 USING(a,b) 6336 LEFT JOIN t4 USING(a,b) 6337 INNER JOIN t5 USING(a,b) 6338 WHERE a<>13 6339 ORDER BY 1 NULLS FIRST; 6340} { 6341 15 35 35 35 35 35 6342 17 37 - 37 - 37 6343 18 - 38 38 - 38 6344 19 - - 39 39 39 6345} 6346do_execsql_test joinB-459 { 6347 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6348 FROM t1 6349 FULL JOIN t2 USING(a) 6350 RIGHT JOIN t3 USING(a) 6351 LEFT JOIN t4 USING(a) 6352 LEFT JOIN t5 USING(a) 6353 ORDER BY 1 NULLS FIRST; 6354} { 6355 - - - - - - 6356 11 11 - 11 11 - 6357 12 12 12 12 - - 6358 13 - 13 13 13 - 6359 15 15 15 15 15 15 6360 17 17 - 17 - 17 6361 18 - 18 18 - 18 6362 19 - - 19 19 19 6363} 6364do_execsql_test joinB-460 { 6365 SELECT a, c, d, e, f, g 6366 FROM t1 6367 FULL JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a) 6368 LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 6369 WHERE a<=18 6370 ORDER BY 1 NULLS FIRST; 6371} { 6372 11 31 - 31 31 - 6373 12 32 32 32 - - 6374 13 - 33 33 33 - 6375 15 35 35 35 35 35 6376 17 37 - 37 - - 6377 18 - 38 38 - - 6378} 6379do_execsql_test joinB-461 { 6380 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6381 FROM t1 6382 FULL JOIN t2 USING(a) 6383 RIGHT JOIN t3 USING(a) 6384 LEFT JOIN t4 USING(a) 6385 RIGHT JOIN t5 USING(a) 6386 ORDER BY 1 NULLS FIRST; 6387} { 6388 15 15 15 15 15 15 6389 17 17 - 17 - 17 6390 18 - 18 18 - 18 6391 19 - - 19 19 19 6392} 6393do_execsql_test joinB-462 { 6394 SELECT a, b, c, d, e, f, g 6395 FROM t1 6396 FULL JOIN t2 USING(a,b) 6397 RIGHT JOIN t3 USING(a,b) 6398 LEFT JOIN t4 USING(a,b) 6399 RIGHT JOIN t5 USING(a,b) 6400 WHERE d<>33 OR d IS NULL 6401 ORDER BY 1 NULLS FIRST; 6402} { 6403 15 25 35 35 35 35 35 6404 17 27 37 - 37 - 37 6405 18 28 - 38 38 - 38 6406 19 29 - - 39 39 39 6407} 6408do_execsql_test joinB-463 { 6409 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6410 FROM t1 6411 FULL JOIN t2 USING(a) 6412 RIGHT JOIN t3 USING(a) 6413 LEFT JOIN t4 USING(a) 6414 FULL JOIN t5 USING(a) 6415 ORDER BY 1 NULLS FIRST; 6416} { 6417 - - - - - - 6418 11 11 - 11 11 - 6419 12 12 12 12 - - 6420 13 - 13 13 13 - 6421 15 15 15 15 15 15 6422 17 17 - 17 - 17 6423 18 - 18 18 - 18 6424 19 - - 19 19 19 6425} 6426do_execsql_test joinB-464 { 6427 SELECT b, c, d, e, f, g 6428 FROM t1 6429 NATURAL FULL JOIN t2 6430 NATURAL RIGHT JOIN t3 6431 NATURAL LEFT JOIN t4 6432 NATURAL FULL JOIN t5 6433 WHERE b BETWEEN 12 AND 17 6434 ORDER BY 1 NULLS FIRST; 6435} { 6436} 6437do_execsql_test joinB-465 { 6438 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6439 FROM t1 6440 FULL JOIN t2 USING(a) 6441 RIGHT JOIN t3 USING(a) 6442 RIGHT JOIN t4 USING(a) 6443 INNER JOIN t5 USING(a) 6444 ORDER BY 1 NULLS FIRST; 6445} { 6446 15 15 15 15 15 15 6447 19 - - 19 19 19 6448} 6449do_execsql_test joinB-466 { 6450 SELECT a, c, d, e, f, g 6451 FROM t1 6452 FULL JOIN t2 USING(a,b) 6453 RIGHT JOIN t3 USING(a,b) 6454 RIGHT JOIN t4 USING(a,b) 6455 INNER JOIN t5 USING(a,b) 6456 WHERE a<>13 6457 ORDER BY 1 NULLS FIRST; 6458} { 6459 15 35 35 35 35 35 6460 19 - - 39 39 39 6461} 6462do_execsql_test joinB-467 { 6463 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6464 FROM t1 6465 FULL JOIN t2 USING(a) 6466 RIGHT JOIN t3 USING(a) 6467 RIGHT JOIN t4 USING(a) 6468 LEFT JOIN t5 USING(a) 6469 ORDER BY 1 NULLS FIRST; 6470} { 6471 11 11 - 11 11 - 6472 13 - 13 13 13 - 6473 15 15 15 15 15 15 6474 19 - - 19 19 19 6475} 6476do_execsql_test joinB-468 { 6477 SELECT a, c, d, e, f, g 6478 FROM t1 6479 FULL JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a) 6480 RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 6481 WHERE a<=18 6482 ORDER BY 1 NULLS FIRST; 6483} { 6484 11 31 - 31 31 - 6485 13 - 33 33 33 - 6486 15 35 35 35 35 35 6487} 6488do_execsql_test joinB-469 { 6489 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6490 FROM t1 6491 FULL JOIN t2 USING(a) 6492 RIGHT JOIN t3 USING(a) 6493 RIGHT JOIN t4 USING(a) 6494 RIGHT JOIN t5 USING(a) 6495 ORDER BY 1 NULLS FIRST; 6496} { 6497 15 15 15 15 15 15 6498 17 - - - - 17 6499 18 - - - - 18 6500 19 - - 19 19 19 6501} 6502do_execsql_test joinB-470 { 6503 SELECT a, b, c, d, e, f, g 6504 FROM t1 6505 FULL JOIN t2 USING(a,b) 6506 RIGHT JOIN t3 USING(a,b) 6507 RIGHT JOIN t4 USING(a,b) 6508 RIGHT JOIN t5 USING(a,b) 6509 WHERE d<>33 OR d IS NULL 6510 ORDER BY 1 NULLS FIRST; 6511} { 6512 15 25 35 35 35 35 35 6513 17 27 - - - - 37 6514 18 28 - - - - 38 6515 19 29 - - 39 39 39 6516} 6517do_execsql_test joinB-471 { 6518 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6519 FROM t1 6520 FULL JOIN t2 USING(a) 6521 RIGHT JOIN t3 USING(a) 6522 RIGHT JOIN t4 USING(a) 6523 FULL JOIN t5 USING(a) 6524 ORDER BY 1 NULLS FIRST; 6525} { 6526 11 11 - 11 11 - 6527 13 - 13 13 13 - 6528 15 15 15 15 15 15 6529 17 - - - - 17 6530 18 - - - - 18 6531 19 - - 19 19 19 6532} 6533do_execsql_test joinB-472 { 6534 SELECT b, c, d, e, f, g 6535 FROM t1 6536 NATURAL FULL JOIN t2 6537 NATURAL RIGHT JOIN t3 6538 NATURAL RIGHT JOIN t4 6539 NATURAL FULL JOIN t5 6540 WHERE b BETWEEN 12 AND 17 6541 ORDER BY 1 NULLS FIRST; 6542} { 6543} 6544do_execsql_test joinB-473 { 6545 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6546 FROM t1 6547 FULL JOIN t2 USING(a) 6548 RIGHT JOIN t3 USING(a) 6549 FULL JOIN t4 USING(a) 6550 INNER JOIN t5 USING(a) 6551 ORDER BY 1 NULLS FIRST; 6552} { 6553 15 15 15 15 15 15 6554 17 17 - 17 - 17 6555 18 - 18 18 - 18 6556 19 - - 19 19 19 6557} 6558do_execsql_test joinB-474 { 6559 SELECT a, c, d, e, f, g 6560 FROM t1 6561 FULL JOIN t2 USING(a,b) 6562 RIGHT JOIN t3 USING(a,b) 6563 FULL JOIN t4 USING(a,b) 6564 INNER JOIN t5 USING(a,b) 6565 WHERE a<>13 6566 ORDER BY 1 NULLS FIRST; 6567} { 6568 15 35 35 35 35 35 6569 17 37 - 37 - 37 6570 18 - 38 38 - 38 6571 19 - - 39 39 39 6572} 6573do_execsql_test joinB-475 { 6574 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6575 FROM t1 6576 FULL JOIN t2 USING(a) 6577 RIGHT JOIN t3 USING(a) 6578 FULL JOIN t4 USING(a) 6579 LEFT JOIN t5 USING(a) 6580 ORDER BY 1 NULLS FIRST; 6581} { 6582 - - - - - - 6583 11 11 - 11 11 - 6584 12 12 12 12 - - 6585 13 - 13 13 13 - 6586 15 15 15 15 15 15 6587 17 17 - 17 - 17 6588 18 - 18 18 - 18 6589 19 - - 19 19 19 6590} 6591do_execsql_test joinB-476 { 6592 SELECT a, c, d, e, f, g 6593 FROM t1 6594 FULL JOIN (t2 RIGHT JOIN t3 USING(a)) USING(a) 6595 FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 6596 WHERE a<=18 6597 ORDER BY 1 NULLS FIRST; 6598} { 6599 11 31 - 31 31 - 6600 12 32 32 32 - - 6601 13 - 33 33 33 - 6602 15 35 35 35 35 35 6603 17 37 - 37 - - 6604 18 - 38 38 - - 6605} 6606do_execsql_test joinB-477 { 6607 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6608 FROM t1 6609 FULL JOIN t2 USING(a) 6610 RIGHT JOIN t3 USING(a) 6611 FULL JOIN t4 USING(a) 6612 RIGHT JOIN t5 USING(a) 6613 ORDER BY 1 NULLS FIRST; 6614} { 6615 15 15 15 15 15 15 6616 17 17 - 17 - 17 6617 18 - 18 18 - 18 6618 19 - - 19 19 19 6619} 6620do_execsql_test joinB-478 { 6621 SELECT a, b, c, d, e, f, g 6622 FROM t1 6623 FULL JOIN t2 USING(a,b) 6624 RIGHT JOIN t3 USING(a,b) 6625 FULL JOIN t4 USING(a,b) 6626 RIGHT JOIN t5 USING(a,b) 6627 WHERE d<>33 OR d IS NULL 6628 ORDER BY 1 NULLS FIRST; 6629} { 6630 15 25 35 35 35 35 35 6631 17 27 37 - 37 - 37 6632 18 28 - 38 38 - 38 6633 19 29 - - 39 39 39 6634} 6635do_execsql_test joinB-479 { 6636 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6637 FROM t1 6638 FULL JOIN t2 USING(a) 6639 RIGHT JOIN t3 USING(a) 6640 FULL JOIN t4 USING(a) 6641 FULL JOIN t5 USING(a) 6642 ORDER BY 1 NULLS FIRST; 6643} { 6644 - - - - - - 6645 11 11 - 11 11 - 6646 12 12 12 12 - - 6647 13 - 13 13 13 - 6648 15 15 15 15 15 15 6649 17 17 - 17 - 17 6650 18 - 18 18 - 18 6651 19 - - 19 19 19 6652} 6653do_execsql_test joinB-480 { 6654 SELECT b, c, d, e, f, g 6655 FROM t1 6656 NATURAL FULL JOIN t2 6657 NATURAL RIGHT JOIN t3 6658 NATURAL FULL JOIN t4 6659 NATURAL FULL JOIN t5 6660 WHERE b BETWEEN 12 AND 17 6661 ORDER BY 1 NULLS FIRST; 6662} { 6663} 6664do_execsql_test joinB-481 { 6665 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6666 FROM t1 6667 FULL JOIN t2 USING(a) 6668 FULL JOIN t3 USING(a) 6669 INNER JOIN t4 USING(a) 6670 INNER JOIN t5 USING(a) 6671 ORDER BY 1 NULLS FIRST; 6672} { 6673 15 15 15 15 15 15 6674 19 - - 19 19 19 6675} 6676do_execsql_test joinB-482 { 6677 SELECT a, c, d, e, f, g 6678 FROM t1 6679 FULL JOIN t2 USING(a,b) 6680 FULL JOIN t3 USING(a,b) 6681 INNER JOIN t4 USING(a,b) 6682 INNER JOIN t5 USING(a,b) 6683 WHERE a<>13 6684 ORDER BY 1 NULLS FIRST; 6685} { 6686 15 35 35 35 35 35 6687 19 - - 39 39 39 6688} 6689do_execsql_test joinB-483 { 6690 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6691 FROM t1 6692 FULL JOIN t2 USING(a) 6693 FULL JOIN t3 USING(a) 6694 INNER JOIN t4 USING(a) 6695 LEFT JOIN t5 USING(a) 6696 ORDER BY 1 NULLS FIRST; 6697} { 6698 11 11 - 11 11 - 6699 13 - 13 13 13 - 6700 15 15 15 15 15 15 6701 19 - - 19 19 19 6702} 6703do_execsql_test joinB-484 { 6704 SELECT a, c, d, e, f, g 6705 FROM t1 6706 FULL JOIN (t2 FULL JOIN t3 USING(a)) USING(a) 6707 INNER JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 6708 WHERE a<=18 6709 ORDER BY 1 NULLS FIRST; 6710} { 6711 11 31 - 31 31 - 6712 13 - 33 33 33 - 6713 15 35 35 35 35 35 6714} 6715do_execsql_test joinB-485 { 6716 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6717 FROM t1 6718 FULL JOIN t2 USING(a) 6719 FULL JOIN t3 USING(a) 6720 INNER JOIN t4 USING(a) 6721 RIGHT JOIN t5 USING(a) 6722 ORDER BY 1 NULLS FIRST; 6723} { 6724 15 15 15 15 15 15 6725 17 - - - - 17 6726 18 - - - - 18 6727 19 - - 19 19 19 6728} 6729do_execsql_test joinB-486 { 6730 SELECT a, b, c, d, e, f, g 6731 FROM t1 6732 FULL JOIN t2 USING(a,b) 6733 FULL JOIN t3 USING(a,b) 6734 INNER JOIN t4 USING(a,b) 6735 RIGHT JOIN t5 USING(a,b) 6736 WHERE d<>33 OR d IS NULL 6737 ORDER BY 1 NULLS FIRST; 6738} { 6739 15 25 35 35 35 35 35 6740 17 27 - - - - 37 6741 18 28 - - - - 38 6742 19 29 - - 39 39 39 6743} 6744do_execsql_test joinB-487 { 6745 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6746 FROM t1 6747 FULL JOIN t2 USING(a) 6748 FULL JOIN t3 USING(a) 6749 INNER JOIN t4 USING(a) 6750 FULL JOIN t5 USING(a) 6751 ORDER BY 1 NULLS FIRST; 6752} { 6753 11 11 - 11 11 - 6754 13 - 13 13 13 - 6755 15 15 15 15 15 15 6756 17 - - - - 17 6757 18 - - - - 18 6758 19 - - 19 19 19 6759} 6760do_execsql_test joinB-488 { 6761 SELECT b, c, d, e, f, g 6762 FROM t1 6763 NATURAL FULL JOIN t2 6764 NATURAL FULL JOIN t3 6765 NATURAL INNER JOIN t4 6766 NATURAL FULL JOIN t5 6767 WHERE b BETWEEN 12 AND 17 6768 ORDER BY 1 NULLS FIRST; 6769} { 6770} 6771do_execsql_test joinB-489 { 6772 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6773 FROM t1 6774 FULL JOIN t2 USING(a) 6775 FULL JOIN t3 USING(a) 6776 LEFT JOIN t4 USING(a) 6777 INNER JOIN t5 USING(a) 6778 ORDER BY 1 NULLS FIRST; 6779} { 6780 15 15 15 15 15 15 6781 17 17 - 17 - 17 6782 18 - 18 18 - 18 6783 19 - - 19 19 19 6784} 6785do_execsql_test joinB-490 { 6786 SELECT a, c, d, e, f, g 6787 FROM t1 6788 FULL JOIN t2 USING(a,b) 6789 FULL JOIN t3 USING(a,b) 6790 LEFT JOIN t4 USING(a,b) 6791 INNER JOIN t5 USING(a,b) 6792 WHERE a<>13 6793 ORDER BY 1 NULLS FIRST; 6794} { 6795 15 35 35 35 35 35 6796 17 37 - 37 - 37 6797 18 - 38 38 - 38 6798 19 - - 39 39 39 6799} 6800do_execsql_test joinB-491 { 6801 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6802 FROM t1 6803 FULL JOIN t2 USING(a) 6804 FULL JOIN t3 USING(a) 6805 LEFT JOIN t4 USING(a) 6806 LEFT JOIN t5 USING(a) 6807 ORDER BY 1 NULLS FIRST; 6808} { 6809 - - - - - - 6810 - - - - - - 6811 11 11 - 11 11 - 6812 12 12 12 12 - - 6813 13 - 13 13 13 - 6814 15 15 15 15 15 15 6815 17 17 - 17 - 17 6816 18 - 18 18 - 18 6817 19 - - 19 19 19 6818} 6819do_execsql_test joinB-492 { 6820 SELECT a, c, d, e, f, g 6821 FROM t1 6822 FULL JOIN (t2 FULL JOIN t3 USING(a)) USING(a) 6823 LEFT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 6824 WHERE a<=18 6825 ORDER BY 1 NULLS FIRST; 6826} { 6827 11 31 - 31 31 - 6828 12 32 32 32 - - 6829 13 - 33 33 33 - 6830 15 35 35 35 35 35 6831 17 37 - 37 - - 6832 18 - 38 38 - - 6833} 6834do_execsql_test joinB-493 { 6835 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6836 FROM t1 6837 FULL JOIN t2 USING(a) 6838 FULL JOIN t3 USING(a) 6839 LEFT JOIN t4 USING(a) 6840 RIGHT JOIN t5 USING(a) 6841 ORDER BY 1 NULLS FIRST; 6842} { 6843 15 15 15 15 15 15 6844 17 17 - 17 - 17 6845 18 - 18 18 - 18 6846 19 - - 19 19 19 6847} 6848do_execsql_test joinB-494 { 6849 SELECT a, b, c, d, e, f, g 6850 FROM t1 6851 FULL JOIN t2 USING(a,b) 6852 FULL JOIN t3 USING(a,b) 6853 LEFT JOIN t4 USING(a,b) 6854 RIGHT JOIN t5 USING(a,b) 6855 WHERE d<>33 OR d IS NULL 6856 ORDER BY 1 NULLS FIRST; 6857} { 6858 15 25 35 35 35 35 35 6859 17 27 37 - 37 - 37 6860 18 28 - 38 38 - 38 6861 19 29 - - 39 39 39 6862} 6863do_execsql_test joinB-495 { 6864 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6865 FROM t1 6866 FULL JOIN t2 USING(a) 6867 FULL JOIN t3 USING(a) 6868 LEFT JOIN t4 USING(a) 6869 FULL JOIN t5 USING(a) 6870 ORDER BY 1 NULLS FIRST; 6871} { 6872 - - - - - - 6873 - - - - - - 6874 11 11 - 11 11 - 6875 12 12 12 12 - - 6876 13 - 13 13 13 - 6877 15 15 15 15 15 15 6878 17 17 - 17 - 17 6879 18 - 18 18 - 18 6880 19 - - 19 19 19 6881} 6882do_execsql_test joinB-496 { 6883 SELECT b, c, d, e, f, g 6884 FROM t1 6885 NATURAL FULL JOIN t2 6886 NATURAL FULL JOIN t3 6887 NATURAL LEFT JOIN t4 6888 NATURAL FULL JOIN t5 6889 WHERE b BETWEEN 12 AND 17 6890 ORDER BY 1 NULLS FIRST; 6891} { 6892} 6893do_execsql_test joinB-497 { 6894 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6895 FROM t1 6896 FULL JOIN t2 USING(a) 6897 FULL JOIN t3 USING(a) 6898 RIGHT JOIN t4 USING(a) 6899 INNER JOIN t5 USING(a) 6900 ORDER BY 1 NULLS FIRST; 6901} { 6902 15 15 15 15 15 15 6903 19 - - 19 19 19 6904} 6905do_execsql_test joinB-498 { 6906 SELECT a, c, d, e, f, g 6907 FROM t1 6908 FULL JOIN t2 USING(a,b) 6909 FULL JOIN t3 USING(a,b) 6910 RIGHT JOIN t4 USING(a,b) 6911 INNER JOIN t5 USING(a,b) 6912 WHERE a<>13 6913 ORDER BY 1 NULLS FIRST; 6914} { 6915 15 35 35 35 35 35 6916 19 - - 39 39 39 6917} 6918do_execsql_test joinB-499 { 6919 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6920 FROM t1 6921 FULL JOIN t2 USING(a) 6922 FULL JOIN t3 USING(a) 6923 RIGHT JOIN t4 USING(a) 6924 LEFT JOIN t5 USING(a) 6925 ORDER BY 1 NULLS FIRST; 6926} { 6927 11 11 - 11 11 - 6928 13 - 13 13 13 - 6929 15 15 15 15 15 15 6930 19 - - 19 19 19 6931} 6932do_execsql_test joinB-500 { 6933 SELECT a, c, d, e, f, g 6934 FROM t1 6935 FULL JOIN (t2 FULL JOIN t3 USING(a)) USING(a) 6936 RIGHT JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 6937 WHERE a<=18 6938 ORDER BY 1 NULLS FIRST; 6939} { 6940 11 31 - 31 31 - 6941 13 - 33 33 33 - 6942 15 35 35 35 35 35 6943} 6944do_execsql_test joinB-501 { 6945 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6946 FROM t1 6947 FULL JOIN t2 USING(a) 6948 FULL JOIN t3 USING(a) 6949 RIGHT JOIN t4 USING(a) 6950 RIGHT JOIN t5 USING(a) 6951 ORDER BY 1 NULLS FIRST; 6952} { 6953 15 15 15 15 15 15 6954 17 - - - - 17 6955 18 - - - - 18 6956 19 - - 19 19 19 6957} 6958do_execsql_test joinB-502 { 6959 SELECT a, b, c, d, e, f, g 6960 FROM t1 6961 FULL JOIN t2 USING(a,b) 6962 FULL JOIN t3 USING(a,b) 6963 RIGHT JOIN t4 USING(a,b) 6964 RIGHT JOIN t5 USING(a,b) 6965 WHERE d<>33 OR d IS NULL 6966 ORDER BY 1 NULLS FIRST; 6967} { 6968 15 25 35 35 35 35 35 6969 17 27 - - - - 37 6970 18 28 - - - - 38 6971 19 29 - - 39 39 39 6972} 6973do_execsql_test joinB-503 { 6974 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 6975 FROM t1 6976 FULL JOIN t2 USING(a) 6977 FULL JOIN t3 USING(a) 6978 RIGHT JOIN t4 USING(a) 6979 FULL JOIN t5 USING(a) 6980 ORDER BY 1 NULLS FIRST; 6981} { 6982 11 11 - 11 11 - 6983 13 - 13 13 13 - 6984 15 15 15 15 15 15 6985 17 - - - - 17 6986 18 - - - - 18 6987 19 - - 19 19 19 6988} 6989do_execsql_test joinB-504 { 6990 SELECT b, c, d, e, f, g 6991 FROM t1 6992 NATURAL FULL JOIN t2 6993 NATURAL FULL JOIN t3 6994 NATURAL RIGHT JOIN t4 6995 NATURAL FULL JOIN t5 6996 WHERE b BETWEEN 12 AND 17 6997 ORDER BY 1 NULLS FIRST; 6998} { 6999} 7000do_execsql_test joinB-505 { 7001 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 7002 FROM t1 7003 FULL JOIN t2 USING(a) 7004 FULL JOIN t3 USING(a) 7005 FULL JOIN t4 USING(a) 7006 INNER JOIN t5 USING(a) 7007 ORDER BY 1 NULLS FIRST; 7008} { 7009 15 15 15 15 15 15 7010 17 17 - 17 - 17 7011 18 - 18 18 - 18 7012 19 - - 19 19 19 7013} 7014do_execsql_test joinB-506 { 7015 SELECT a, c, d, e, f, g 7016 FROM t1 7017 FULL JOIN t2 USING(a,b) 7018 FULL JOIN t3 USING(a,b) 7019 FULL JOIN t4 USING(a,b) 7020 INNER JOIN t5 USING(a,b) 7021 WHERE a<>13 7022 ORDER BY 1 NULLS FIRST; 7023} { 7024 15 35 35 35 35 35 7025 17 37 - 37 - 37 7026 18 - 38 38 - 38 7027 19 - - 39 39 39 7028} 7029do_execsql_test joinB-507 { 7030 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 7031 FROM t1 7032 FULL JOIN t2 USING(a) 7033 FULL JOIN t3 USING(a) 7034 FULL JOIN t4 USING(a) 7035 LEFT JOIN t5 USING(a) 7036 ORDER BY 1 NULLS FIRST; 7037} { 7038 - - - - - - 7039 - - - - - - 7040 11 11 - 11 11 - 7041 12 12 12 12 - - 7042 13 - 13 13 13 - 7043 15 15 15 15 15 15 7044 17 17 - 17 - 17 7045 18 - 18 18 - 18 7046 19 - - 19 19 19 7047} 7048do_execsql_test joinB-508 { 7049 SELECT a, c, d, e, f, g 7050 FROM t1 7051 FULL JOIN (t2 FULL JOIN t3 USING(a)) USING(a) 7052 FULL JOIN (t4 LEFT JOIN t5 USING(a)) USING(a) 7053 WHERE a<=18 7054 ORDER BY 1 NULLS FIRST; 7055} { 7056 11 31 - 31 31 - 7057 12 32 32 32 - - 7058 13 - 33 33 33 - 7059 15 35 35 35 35 35 7060 17 37 - 37 - - 7061 18 - 38 38 - - 7062} 7063do_execsql_test joinB-509 { 7064 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 7065 FROM t1 7066 FULL JOIN t2 USING(a) 7067 FULL JOIN t3 USING(a) 7068 FULL JOIN t4 USING(a) 7069 RIGHT JOIN t5 USING(a) 7070 ORDER BY 1 NULLS FIRST; 7071} { 7072 15 15 15 15 15 15 7073 17 17 - 17 - 17 7074 18 - 18 18 - 18 7075 19 - - 19 19 19 7076} 7077do_execsql_test joinB-510 { 7078 SELECT a, b, c, d, e, f, g 7079 FROM t1 7080 FULL JOIN t2 USING(a,b) 7081 FULL JOIN t3 USING(a,b) 7082 FULL JOIN t4 USING(a,b) 7083 RIGHT JOIN t5 USING(a,b) 7084 WHERE d<>33 OR d IS NULL 7085 ORDER BY 1 NULLS FIRST; 7086} { 7087 15 25 35 35 35 35 35 7088 17 27 37 - 37 - 37 7089 18 28 - 38 38 - 38 7090 19 29 - - 39 39 39 7091} 7092do_execsql_test joinB-511 { 7093 SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a 7094 FROM t1 7095 FULL JOIN t2 USING(a) 7096 FULL JOIN t3 USING(a) 7097 FULL JOIN t4 USING(a) 7098 FULL JOIN t5 USING(a) 7099 ORDER BY 1 NULLS FIRST; 7100} { 7101 - - - - - - 7102 - - - - - - 7103 11 11 - 11 11 - 7104 12 12 12 12 - - 7105 13 - 13 13 13 - 7106 15 15 15 15 15 15 7107 17 17 - 17 - 17 7108 18 - 18 18 - 18 7109 19 - - 19 19 19 7110} 7111do_execsql_test joinB-512 { 7112 SELECT b, c, d, e, f, g 7113 FROM t1 7114 NATURAL FULL JOIN t2 7115 NATURAL FULL JOIN t3 7116 NATURAL FULL JOIN t4 7117 NATURAL FULL JOIN t5 7118 WHERE b BETWEEN 12 AND 17 7119 ORDER BY 1 NULLS FIRST; 7120} { 7121} 7122finish_test 7123 7124############################################################################## 7125# Here is the original TCL script that generated the psql input file: 7126# 7127# 7128# puts " 7129# \\pset border off 7130# \\pset tuples_only on 7131# \\pset null - 7132# 7133# DROP TABLE IF EXISTS t1; 7134# DROP TABLE IF EXISTS t2; 7135# DROP TABLE IF EXISTS t3; 7136# DROP TABLE IF EXISTS t4; 7137# DROP TABLE IF EXISTS t5; 7138# CREATE TABLE t1(a INT, b INT, c INT); 7139# CREATE TABLE t2(a INT, b INT, d INT); 7140# CREATE TABLE t3(a INT, b INT, e INT); 7141# CREATE TABLE t4(a INT, b INT, f INT); 7142# CREATE TABLE t5(a INT, b INT, g INT); 7143# INSERT INTO t1 VALUES(11,21,31),(12,22,32),(15,25,35),(17,27,37); 7144# INSERT INTO t2 VALUES(12,22,32),(13,23,33),(15,25,35),(18,28,38),(NULL,NULL,36); 7145# INSERT INTO t4 VALUES(11,21,31),(13,23,33),(15,25,35),(19,29,39); 7146# INSERT INTO t3 SELECT * FROM t1 UNION SELECT * FROM t2 UNION SELECT * FROM t4; 7147# INSERT INTO t5 SELECT * FROM t3 WHERE a>=15; 7148# " 7149# 7150# proc echo {prefix txt} { 7151# regsub -all {\n} $txt \n$prefix txt 7152# puts "$prefix$txt" 7153# } 7154# 7155# set n 0 7156# set k 0 7157# foreach j1 {INNER LEFT RIGHT FULL} { 7158# foreach j2 {INNER LEFT RIGHT FULL} { 7159# foreach j3 {INNER LEFT RIGHT FULL} { 7160# foreach j4 {INNER LEFT RIGHT FULL} { 7161# 7162# incr n 7163# incr k 7164# set q1 "" 7165# append q1 "SELECT a, t1.a, t2.a, t3.a, t4.a, t5.a\n" 7166# append q1 " FROM t1\n" 7167# append q1 " $j1 JOIN t2 USING(a)\n" 7168# append q1 " $j2 JOIN t3 USING(a)\n" 7169# append q1 " $j3 JOIN t4 USING(a)\n" 7170# append q1 " $j4 JOIN t5 USING(a)\n" 7171# append q1 " ORDER BY 1 NULLS FIRST;" 7172# 7173# echo "\\qecho " "do_execsql_test joinB-$n \{" 7174# echo "\\qecho X " $q1 7175# echo "\\qecho " "\} \{" 7176# puts $q1 7177# echo "\\qecho " "\}" 7178# 7179# switch [expr {$k%4}] { 7180# 0 { 7181# set q2 "" 7182# append q2 "SELECT b, c, d, e, f, g\n" 7183# append q2 " FROM t1\n" 7184# append q2 " NATURAL $j1 JOIN t2\n" 7185# append q2 " NATURAL $j2 JOIN t3\n" 7186# append q2 " NATURAL $j3 JOIN t4\n" 7187# append q2 " NATURAL $j4 JOIN t5\n" 7188# append q2 " WHERE b BETWEEN 12 AND 17\n" 7189# append q2 " ORDER BY 1 NULLS FIRST;" 7190# incr n 7191# echo "\\qecho " "do_execsql_test joinB-$n \{" 7192# echo "\\qecho X " $q2 7193# echo "\\qecho " "\} \{" 7194# puts $q2 7195# echo "\\qecho " "\}" 7196# } 7197# 1 { 7198# set q2 "" 7199# append q2 "SELECT a, c, d, e, f, g\n" 7200# append q2 " FROM t1\n" 7201# append q2 " $j1 JOIN t2 USING(a,b)\n" 7202# append q2 " $j2 JOIN t3 USING(a,b)\n" 7203# append q2 " $j3 JOIN t4 USING(a,b)\n" 7204# append q2 " $j4 JOIN t5 USING(a,b)\n" 7205# append q2 " WHERE a<>13\n" 7206# append q2 " ORDER BY 1 NULLS FIRST;" 7207# incr n 7208# echo "\\qecho " "do_execsql_test joinB-$n \{" 7209# echo "\\qecho X " $q2 7210# echo "\\qecho " "\} \{" 7211# puts $q2 7212# echo "\\qecho " "\}" 7213# } 7214# 2 { 7215# set q2 "" 7216# append q2 "SELECT a, c, d, e, f, g\n" 7217# append q2 " FROM t1\n" 7218# append q2 " $j1 JOIN (t2 $j2 JOIN t3 USING(a)) USING(a)\n" 7219# append q2 " $j3 JOIN (t4 $j4 JOIN t5 USING(a)) USING(a)\n" 7220# append q2 " WHERE a<=18\n" 7221# append q2 " ORDER BY 1 NULLS FIRST;" 7222# incr n 7223# echo "\\qecho " "do_execsql_test joinB-$n \{" 7224# echo "\\qecho X " $q2 7225# echo "\\qecho " "\} \{" 7226# puts $q2 7227# echo "\\qecho " "\}" 7228# } 7229# 3 { 7230# set q2 "" 7231# append q2 "SELECT a, b, c, d, e, f, g\n" 7232# append q2 " FROM t1\n" 7233# append q2 " $j1 JOIN t2 USING(a,b)\n" 7234# append q2 " $j2 JOIN t3 USING(a,b)\n" 7235# append q2 " $j3 JOIN t4 USING(a,b)\n" 7236# append q2 " $j4 JOIN t5 USING(a,b)\n" 7237# append q2 " WHERE d<>33 OR d IS NULL\n" 7238# append q2 " ORDER BY 1 NULLS FIRST;" 7239# incr n 7240# echo "\\qecho " "do_execsql_test joinB-$n \{" 7241# echo "\\qecho X " $q2 7242# echo "\\qecho " "\} \{" 7243# puts $q2 7244# echo "\\qecho " "\}" 7245# } 7246# } 7247# 7248# } 7249# } 7250# } 7251# } 7252############################################################################## 7253