github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/postgresjoin (about) 1 # These are postgres regress sql join test suite 2 # https://github.com/postgres/postgres/blob/master/src/test/regress/sql/join.sql 3 # Adapted to sqllogictest format 4 5 statement ok 6 CREATE TABLE J1_TBL ( i integer, j integer, t text ) 7 8 statement ok 9 CREATE TABLE J2_TBL ( i integer, k integer ) 10 11 statement ok 12 INSERT INTO J1_TBL VALUES (1, 4, 'one') 13 14 statement ok 15 INSERT INTO J1_TBL VALUES (2, 3, 'two') 16 17 statement ok 18 INSERT INTO J1_TBL VALUES (3, 2, 'three') 19 20 statement ok 21 INSERT INTO J1_TBL VALUES (4, 1, 'four') 22 23 statement ok 24 INSERT INTO J1_TBL VALUES (5, 0, 'five') 25 26 statement ok 27 INSERT INTO J1_TBL VALUES (6, 6, 'six') 28 29 statement ok 30 INSERT INTO J1_TBL VALUES (7, 7, 'seven') 31 32 statement ok 33 INSERT INTO J1_TBL VALUES (8, 8, 'eight') 34 35 statement ok 36 INSERT INTO J1_TBL VALUES (0, NULL, 'zero') 37 38 statement ok 39 INSERT INTO J1_TBL VALUES (NULL, NULL, 'null') 40 41 statement ok 42 INSERT INTO J1_TBL VALUES (NULL, 0, 'zero') 43 44 statement ok 45 INSERT INTO J2_TBL VALUES (1, -1) 46 47 statement ok 48 INSERT INTO J2_TBL VALUES (2, 2) 49 50 statement ok 51 INSERT INTO J2_TBL VALUES (3, -3) 52 53 statement ok 54 INSERT INTO J2_TBL VALUES (2, 4) 55 56 statement ok 57 INSERT INTO J2_TBL VALUES (5, -5) 58 59 statement ok 60 INSERT INTO J2_TBL VALUES (5, -5) 61 62 statement ok 63 INSERT INTO J2_TBL VALUES (0, NULL) 64 65 statement ok 66 INSERT INTO J2_TBL VALUES (NULL, NULL) 67 68 statement ok 69 INSERT INTO J2_TBL VALUES (NULL, 0) 70 71 query TIIT rowsort 72 SELECT 'x' AS "xxx", * FROM J1_TBL AS tx 73 ---- 74 x 1 4 one 75 x 2 3 two 76 x 3 2 three 77 x 4 1 four 78 x 5 0 five 79 x 6 6 six 80 x 7 7 seven 81 x 8 8 eight 82 x 0 NULL zero 83 x NULL NULL null 84 x NULL 0 zero 85 86 query TIIT rowsort 87 SELECT 'x' AS "xxx", * FROM J1_TBL tx 88 ---- 89 x 1 4 one 90 x 2 3 two 91 x 3 2 three 92 x 4 1 four 93 x 5 0 five 94 x 6 6 six 95 x 7 7 seven 96 x 8 8 eight 97 x 0 NULL zero 98 x NULL NULL null 99 x NULL 0 zero 100 101 query TIIT rowsort 102 SELECT 'x' AS "xxx", * FROM J1_TBL AS t1 (a, b, c) 103 ---- 104 x 1 4 one 105 x 2 3 two 106 x 3 2 three 107 x 4 1 four 108 x 5 0 five 109 x 6 6 six 110 x 7 7 seven 111 x 8 8 eight 112 x 0 NULL zero 113 x NULL NULL null 114 x NULL 0 zero 115 116 query TIIT rowsort 117 SELECT 'x' AS "xxx", * FROM J1_TBL t1 (a, b, c) 118 ---- 119 x 1 4 one 120 x 2 3 two 121 x 3 2 three 122 x 4 1 four 123 x 5 0 five 124 x 6 6 six 125 x 7 7 seven 126 x 8 8 eight 127 x 0 NULL zero 128 x NULL NULL null 129 x NULL 0 zero 130 131 query TIITII rowsort 132 SELECT 'x' AS "xxx", * FROM J1_TBL t1 (a, b, c), J2_TBL t2 (d, e) 133 ---- 134 x 1 4 one 1 -1 135 x 2 3 two 1 -1 136 x 3 2 three 1 -1 137 x 4 1 four 1 -1 138 x 5 0 five 1 -1 139 x 6 6 six 1 -1 140 x 7 7 seven 1 -1 141 x 8 8 eight 1 -1 142 x 0 NULL zero 1 -1 143 x NULL NULL null 1 -1 144 x NULL 0 zero 1 -1 145 x 1 4 one 2 2 146 x 2 3 two 2 2 147 x 3 2 three 2 2 148 x 4 1 four 2 2 149 x 5 0 five 2 2 150 x 6 6 six 2 2 151 x 7 7 seven 2 2 152 x 8 8 eight 2 2 153 x 0 NULL zero 2 2 154 x NULL NULL null 2 2 155 x NULL 0 zero 2 2 156 x 1 4 one 3 -3 157 x 2 3 two 3 -3 158 x 3 2 three 3 -3 159 x 4 1 four 3 -3 160 x 5 0 five 3 -3 161 x 6 6 six 3 -3 162 x 7 7 seven 3 -3 163 x 8 8 eight 3 -3 164 x 0 NULL zero 3 -3 165 x NULL NULL null 3 -3 166 x NULL 0 zero 3 -3 167 x 1 4 one 2 4 168 x 2 3 two 2 4 169 x 3 2 three 2 4 170 x 4 1 four 2 4 171 x 5 0 five 2 4 172 x 6 6 six 2 4 173 x 7 7 seven 2 4 174 x 8 8 eight 2 4 175 x 0 NULL zero 2 4 176 x NULL NULL null 2 4 177 x NULL 0 zero 2 4 178 x 1 4 one 5 -5 179 x 2 3 two 5 -5 180 x 3 2 three 5 -5 181 x 4 1 four 5 -5 182 x 5 0 five 5 -5 183 x 6 6 six 5 -5 184 x 7 7 seven 5 -5 185 x 8 8 eight 5 -5 186 x 0 NULL zero 5 -5 187 x NULL NULL null 5 -5 188 x NULL 0 zero 5 -5 189 x 1 4 one 5 -5 190 x 2 3 two 5 -5 191 x 3 2 three 5 -5 192 x 4 1 four 5 -5 193 x 5 0 five 5 -5 194 x 6 6 six 5 -5 195 x 7 7 seven 5 -5 196 x 8 8 eight 5 -5 197 x 0 NULL zero 5 -5 198 x NULL NULL null 5 -5 199 x NULL 0 zero 5 -5 200 x 1 4 one 0 NULL 201 x 2 3 two 0 NULL 202 x 3 2 three 0 NULL 203 x 4 1 four 0 NULL 204 x 5 0 five 0 NULL 205 x 6 6 six 0 NULL 206 x 7 7 seven 0 NULL 207 x 8 8 eight 0 NULL 208 x 0 NULL zero 0 NULL 209 x NULL NULL null 0 NULL 210 x NULL 0 zero 0 NULL 211 x 1 4 one NULL NULL 212 x 2 3 two NULL NULL 213 x 3 2 three NULL NULL 214 x 4 1 four NULL NULL 215 x 5 0 five NULL NULL 216 x 6 6 six NULL NULL 217 x 7 7 seven NULL NULL 218 x 8 8 eight NULL NULL 219 x 0 NULL zero NULL NULL 220 x NULL NULL null NULL NULL 221 x NULL 0 zero NULL NULL 222 x 1 4 one NULL 0 223 x 2 3 two NULL 0 224 x 3 2 three NULL 0 225 x 4 1 four NULL 0 226 x 5 0 five NULL 0 227 x 6 6 six NULL 0 228 x 7 7 seven NULL 0 229 x 8 8 eight NULL 0 230 x 0 NULL zero NULL 0 231 x NULL NULL null NULL 0 232 x NULL 0 zero NULL 0 233 234 query TII rowsort 235 SELECT 'x' AS "xxx", t1.a, t2.e FROM J1_TBL t1 (a, b, c), J2_TBL t2 (d, e) WHERE t1.a = t2.d 236 ---- 237 x 0 NULL 238 x 1 -1 239 x 2 2 240 x 2 4 241 x 3 -3 242 x 5 -5 243 x 5 -5 244 245 query TIITII rowsort 246 SELECT 'x' AS "xxx", * FROM J1_TBL CROSS JOIN J2_TBL 247 ---- 248 x 1 4 one 1 -1 249 x 2 3 two 1 -1 250 x 3 2 three 1 -1 251 x 4 1 four 1 -1 252 x 5 0 five 1 -1 253 x 6 6 six 1 -1 254 x 7 7 seven 1 -1 255 x 8 8 eight 1 -1 256 x 0 NULL zero 1 -1 257 x NULL NULL null 1 -1 258 x NULL 0 zero 1 -1 259 x 1 4 one 2 2 260 x 2 3 two 2 2 261 x 3 2 three 2 2 262 x 4 1 four 2 2 263 x 5 0 five 2 2 264 x 6 6 six 2 2 265 x 7 7 seven 2 2 266 x 8 8 eight 2 2 267 x 0 NULL zero 2 2 268 x NULL NULL null 2 2 269 x NULL 0 zero 2 2 270 x 1 4 one 3 -3 271 x 2 3 two 3 -3 272 x 3 2 three 3 -3 273 x 4 1 four 3 -3 274 x 5 0 five 3 -3 275 x 6 6 six 3 -3 276 x 7 7 seven 3 -3 277 x 8 8 eight 3 -3 278 x 0 NULL zero 3 -3 279 x NULL NULL null 3 -3 280 x NULL 0 zero 3 -3 281 x 1 4 one 2 4 282 x 2 3 two 2 4 283 x 3 2 three 2 4 284 x 4 1 four 2 4 285 x 5 0 five 2 4 286 x 6 6 six 2 4 287 x 7 7 seven 2 4 288 x 8 8 eight 2 4 289 x 0 NULL zero 2 4 290 x NULL NULL null 2 4 291 x NULL 0 zero 2 4 292 x 1 4 one 5 -5 293 x 2 3 two 5 -5 294 x 3 2 three 5 -5 295 x 4 1 four 5 -5 296 x 5 0 five 5 -5 297 x 6 6 six 5 -5 298 x 7 7 seven 5 -5 299 x 8 8 eight 5 -5 300 x 0 NULL zero 5 -5 301 x NULL NULL null 5 -5 302 x NULL 0 zero 5 -5 303 x 1 4 one 5 -5 304 x 2 3 two 5 -5 305 x 3 2 three 5 -5 306 x 4 1 four 5 -5 307 x 5 0 five 5 -5 308 x 6 6 six 5 -5 309 x 7 7 seven 5 -5 310 x 8 8 eight 5 -5 311 x 0 NULL zero 5 -5 312 x NULL NULL null 5 -5 313 x NULL 0 zero 5 -5 314 x 1 4 one 0 NULL 315 x 2 3 two 0 NULL 316 x 3 2 three 0 NULL 317 x 4 1 four 0 NULL 318 x 5 0 five 0 NULL 319 x 6 6 six 0 NULL 320 x 7 7 seven 0 NULL 321 x 8 8 eight 0 NULL 322 x 0 NULL zero 0 NULL 323 x NULL NULL null 0 NULL 324 x NULL 0 zero 0 NULL 325 x 1 4 one NULL NULL 326 x 2 3 two NULL NULL 327 x 3 2 three NULL NULL 328 x 4 1 four NULL NULL 329 x 5 0 five NULL NULL 330 x 6 6 six NULL NULL 331 x 7 7 seven NULL NULL 332 x 8 8 eight NULL NULL 333 x 0 NULL zero NULL NULL 334 x NULL NULL null NULL NULL 335 x NULL 0 zero NULL NULL 336 x 1 4 one NULL 0 337 x 2 3 two NULL 0 338 x 3 2 three NULL 0 339 x 4 1 four NULL 0 340 x 5 0 five NULL 0 341 x 6 6 six NULL 0 342 x 7 7 seven NULL 0 343 x 8 8 eight NULL 0 344 x 0 NULL zero NULL 0 345 x NULL NULL null NULL 0 346 x NULL 0 zero NULL 0 347 348 statement error column reference "i" is ambiguous 349 SELECT 'x' AS "xxx", i, k, t FROM J1_TBL CROSS JOIN J2_TBL 350 351 query TIIT rowsort 352 SELECT 'x' AS "xxx", t1.i, k, t FROM J1_TBL t1 CROSS JOIN J2_TBL t2 353 ---- 354 x 1 -1 one 355 x 2 -1 two 356 x 3 -1 three 357 x 4 -1 four 358 x 5 -1 five 359 x 6 -1 six 360 x 7 -1 seven 361 x 8 -1 eight 362 x 0 -1 zero 363 x NULL -1 null 364 x NULL -1 zero 365 x 1 2 one 366 x 2 2 two 367 x 3 2 three 368 x 4 2 four 369 x 5 2 five 370 x 6 2 six 371 x 7 2 seven 372 x 8 2 eight 373 x 0 2 zero 374 x NULL 2 null 375 x NULL 2 zero 376 x 1 -3 one 377 x 2 -3 two 378 x 3 -3 three 379 x 4 -3 four 380 x 5 -3 five 381 x 6 -3 six 382 x 7 -3 seven 383 x 8 -3 eight 384 x 0 -3 zero 385 x NULL -3 null 386 x NULL -3 zero 387 x 1 4 one 388 x 2 4 two 389 x 3 4 three 390 x 4 4 four 391 x 5 4 five 392 x 6 4 six 393 x 7 4 seven 394 x 8 4 eight 395 x 0 4 zero 396 x NULL 4 null 397 x NULL 4 zero 398 x 1 -5 one 399 x 2 -5 two 400 x 3 -5 three 401 x 4 -5 four 402 x 5 -5 five 403 x 6 -5 six 404 x 7 -5 seven 405 x 8 -5 eight 406 x 0 -5 zero 407 x NULL -5 null 408 x NULL -5 zero 409 x 1 -5 one 410 x 2 -5 two 411 x 3 -5 three 412 x 4 -5 four 413 x 5 -5 five 414 x 6 -5 six 415 x 7 -5 seven 416 x 8 -5 eight 417 x 0 -5 zero 418 x NULL -5 null 419 x NULL -5 zero 420 x 1 NULL one 421 x 2 NULL two 422 x 3 NULL three 423 x 4 NULL four 424 x 5 NULL five 425 x 6 NULL six 426 x 7 NULL seven 427 x 8 NULL eight 428 x 0 NULL zero 429 x NULL NULL null 430 x NULL NULL zero 431 x 1 NULL one 432 x 2 NULL two 433 x 3 NULL three 434 x 4 NULL four 435 x 5 NULL five 436 x 6 NULL six 437 x 7 NULL seven 438 x 8 NULL eight 439 x 0 NULL zero 440 x NULL NULL null 441 x NULL NULL zero 442 x 1 0 one 443 x 2 0 two 444 x 3 0 three 445 x 4 0 four 446 x 5 0 five 447 x 6 0 six 448 x 7 0 seven 449 x 8 0 eight 450 x 0 0 zero 451 x NULL 0 null 452 x NULL 0 zero 453 454 query TITI rowsort 455 SELECT 'x' AS "xxx", ii, tt, kk FROM (J1_TBL CROSS JOIN J2_TBL) AS tx (ii, jj, tt, ii2, kk) 456 ---- 457 x 1 one -1 458 x 2 two -1 459 x 3 three -1 460 x 4 four -1 461 x 5 five -1 462 x 6 six -1 463 x 7 seven -1 464 x 8 eight -1 465 x 0 zero -1 466 x NULL null -1 467 x NULL zero -1 468 x 1 one 2 469 x 2 two 2 470 x 3 three 2 471 x 4 four 2 472 x 5 five 2 473 x 6 six 2 474 x 7 seven 2 475 x 8 eight 2 476 x 0 zero 2 477 x NULL null 2 478 x NULL zero 2 479 x 1 one -3 480 x 2 two -3 481 x 3 three -3 482 x 4 four -3 483 x 5 five -3 484 x 6 six -3 485 x 7 seven -3 486 x 8 eight -3 487 x 0 zero -3 488 x NULL null -3 489 x NULL zero -3 490 x 1 one 4 491 x 2 two 4 492 x 3 three 4 493 x 4 four 4 494 x 5 five 4 495 x 6 six 4 496 x 7 seven 4 497 x 8 eight 4 498 x 0 zero 4 499 x NULL null 4 500 x NULL zero 4 501 x 1 one -5 502 x 2 two -5 503 x 3 three -5 504 x 4 four -5 505 x 5 five -5 506 x 6 six -5 507 x 7 seven -5 508 x 8 eight -5 509 x 0 zero -5 510 x NULL null -5 511 x NULL zero -5 512 x 1 one -5 513 x 2 two -5 514 x 3 three -5 515 x 4 four -5 516 x 5 five -5 517 x 6 six -5 518 x 7 seven -5 519 x 8 eight -5 520 x 0 zero -5 521 x NULL null -5 522 x NULL zero -5 523 x 1 one NULL 524 x 2 two NULL 525 x 3 three NULL 526 x 4 four NULL 527 x 5 five NULL 528 x 6 six NULL 529 x 7 seven NULL 530 x 8 eight NULL 531 x 0 zero NULL 532 x NULL null NULL 533 x NULL zero NULL 534 x 1 one NULL 535 x 2 two NULL 536 x 3 three NULL 537 x 4 four NULL 538 x 5 five NULL 539 x 6 six NULL 540 x 7 seven NULL 541 x 8 eight NULL 542 x 0 zero NULL 543 x NULL null NULL 544 x NULL zero NULL 545 x 1 one 0 546 x 2 two 0 547 x 3 three 0 548 x 4 four 0 549 x 5 five 0 550 x 6 six 0 551 x 7 seven 0 552 x 8 eight 0 553 x 0 zero 0 554 x NULL null 0 555 x NULL zero 0 556 557 query TIII rowsort 558 SELECT 'x' AS "xxx", tx.ii, tx.jj, tx.kk FROM (J1_TBL t1 (a, b, c) CROSS JOIN J2_TBL t2 (d, e)) AS tx (ii, jj, tt, ii2, kk) 559 ---- 560 x 1 4 -1 561 x 2 3 -1 562 x 3 2 -1 563 x 4 1 -1 564 x 5 0 -1 565 x 6 6 -1 566 x 7 7 -1 567 x 8 8 -1 568 x 0 NULL -1 569 x NULL NULL -1 570 x NULL 0 -1 571 x 1 4 2 572 x 2 3 2 573 x 3 2 2 574 x 4 1 2 575 x 5 0 2 576 x 6 6 2 577 x 7 7 2 578 x 8 8 2 579 x 0 NULL 2 580 x NULL NULL 2 581 x NULL 0 2 582 x 1 4 -3 583 x 2 3 -3 584 x 3 2 -3 585 x 4 1 -3 586 x 5 0 -3 587 x 6 6 -3 588 x 7 7 -3 589 x 8 8 -3 590 x 0 NULL -3 591 x NULL NULL -3 592 x NULL 0 -3 593 x 1 4 4 594 x 2 3 4 595 x 3 2 4 596 x 4 1 4 597 x 5 0 4 598 x 6 6 4 599 x 7 7 4 600 x 8 8 4 601 x 0 NULL 4 602 x NULL NULL 4 603 x NULL 0 4 604 x 1 4 -5 605 x 2 3 -5 606 x 3 2 -5 607 x 4 1 -5 608 x 5 0 -5 609 x 6 6 -5 610 x 7 7 -5 611 x 8 8 -5 612 x 0 NULL -5 613 x NULL NULL -5 614 x NULL 0 -5 615 x 1 4 -5 616 x 2 3 -5 617 x 3 2 -5 618 x 4 1 -5 619 x 5 0 -5 620 x 6 6 -5 621 x 7 7 -5 622 x 8 8 -5 623 x 0 NULL -5 624 x NULL NULL -5 625 x NULL 0 -5 626 x 1 4 NULL 627 x 2 3 NULL 628 x 3 2 NULL 629 x 4 1 NULL 630 x 5 0 NULL 631 x 6 6 NULL 632 x 7 7 NULL 633 x 8 8 NULL 634 x 0 NULL NULL 635 x NULL NULL NULL 636 x NULL 0 NULL 637 x 1 4 NULL 638 x 2 3 NULL 639 x 3 2 NULL 640 x 4 1 NULL 641 x 5 0 NULL 642 x 6 6 NULL 643 x 7 7 NULL 644 x 8 8 NULL 645 x 0 NULL NULL 646 x NULL NULL NULL 647 x NULL 0 NULL 648 x 1 4 0 649 x 2 3 0 650 x 3 2 0 651 x 4 1 0 652 x 5 0 0 653 x 6 6 0 654 x 7 7 0 655 x 8 8 0 656 x 0 NULL 0 657 x NULL NULL 0 658 x NULL 0 0 659 660 query TIITIIII rowsort 661 SELECT 'x' AS "xxx", * FROM J1_TBL CROSS JOIN J2_TBL a CROSS JOIN J2_TBL b 662 ---- 663 x 1 4 one 1 -1 1 -1 664 x 1 4 one 1 -1 2 2 665 x 1 4 one 1 -1 3 -3 666 x 1 4 one 1 -1 2 4 667 x 1 4 one 1 -1 5 -5 668 x 1 4 one 1 -1 5 -5 669 x 1 4 one 1 -1 0 NULL 670 x 1 4 one 1 -1 NULL NULL 671 x 1 4 one 1 -1 NULL 0 672 x 2 3 two 1 -1 1 -1 673 x 2 3 two 1 -1 2 2 674 x 2 3 two 1 -1 3 -3 675 x 2 3 two 1 -1 2 4 676 x 2 3 two 1 -1 5 -5 677 x 2 3 two 1 -1 5 -5 678 x 2 3 two 1 -1 0 NULL 679 x 2 3 two 1 -1 NULL NULL 680 x 2 3 two 1 -1 NULL 0 681 x 3 2 three 1 -1 1 -1 682 x 3 2 three 1 -1 2 2 683 x 3 2 three 1 -1 3 -3 684 x 3 2 three 1 -1 2 4 685 x 3 2 three 1 -1 5 -5 686 x 3 2 three 1 -1 5 -5 687 x 3 2 three 1 -1 0 NULL 688 x 3 2 three 1 -1 NULL NULL 689 x 3 2 three 1 -1 NULL 0 690 x 4 1 four 1 -1 1 -1 691 x 4 1 four 1 -1 2 2 692 x 4 1 four 1 -1 3 -3 693 x 4 1 four 1 -1 2 4 694 x 4 1 four 1 -1 5 -5 695 x 4 1 four 1 -1 5 -5 696 x 4 1 four 1 -1 0 NULL 697 x 4 1 four 1 -1 NULL NULL 698 x 4 1 four 1 -1 NULL 0 699 x 5 0 five 1 -1 1 -1 700 x 5 0 five 1 -1 2 2 701 x 5 0 five 1 -1 3 -3 702 x 5 0 five 1 -1 2 4 703 x 5 0 five 1 -1 5 -5 704 x 5 0 five 1 -1 5 -5 705 x 5 0 five 1 -1 0 NULL 706 x 5 0 five 1 -1 NULL NULL 707 x 5 0 five 1 -1 NULL 0 708 x 6 6 six 1 -1 1 -1 709 x 6 6 six 1 -1 2 2 710 x 6 6 six 1 -1 3 -3 711 x 6 6 six 1 -1 2 4 712 x 6 6 six 1 -1 5 -5 713 x 6 6 six 1 -1 5 -5 714 x 6 6 six 1 -1 0 NULL 715 x 6 6 six 1 -1 NULL NULL 716 x 6 6 six 1 -1 NULL 0 717 x 7 7 seven 1 -1 1 -1 718 x 7 7 seven 1 -1 2 2 719 x 7 7 seven 1 -1 3 -3 720 x 7 7 seven 1 -1 2 4 721 x 7 7 seven 1 -1 5 -5 722 x 7 7 seven 1 -1 5 -5 723 x 7 7 seven 1 -1 0 NULL 724 x 7 7 seven 1 -1 NULL NULL 725 x 7 7 seven 1 -1 NULL 0 726 x 8 8 eight 1 -1 1 -1 727 x 8 8 eight 1 -1 2 2 728 x 8 8 eight 1 -1 3 -3 729 x 8 8 eight 1 -1 2 4 730 x 8 8 eight 1 -1 5 -5 731 x 8 8 eight 1 -1 5 -5 732 x 8 8 eight 1 -1 0 NULL 733 x 8 8 eight 1 -1 NULL NULL 734 x 8 8 eight 1 -1 NULL 0 735 x 0 NULL zero 1 -1 1 -1 736 x 0 NULL zero 1 -1 2 2 737 x 0 NULL zero 1 -1 3 -3 738 x 0 NULL zero 1 -1 2 4 739 x 0 NULL zero 1 -1 5 -5 740 x 0 NULL zero 1 -1 5 -5 741 x 0 NULL zero 1 -1 0 NULL 742 x 0 NULL zero 1 -1 NULL NULL 743 x 0 NULL zero 1 -1 NULL 0 744 x NULL NULL null 1 -1 1 -1 745 x NULL NULL null 1 -1 2 2 746 x NULL NULL null 1 -1 3 -3 747 x NULL NULL null 1 -1 2 4 748 x NULL NULL null 1 -1 5 -5 749 x NULL NULL null 1 -1 5 -5 750 x NULL NULL null 1 -1 0 NULL 751 x NULL NULL null 1 -1 NULL NULL 752 x NULL NULL null 1 -1 NULL 0 753 x NULL 0 zero 1 -1 1 -1 754 x NULL 0 zero 1 -1 2 2 755 x NULL 0 zero 1 -1 3 -3 756 x NULL 0 zero 1 -1 2 4 757 x NULL 0 zero 1 -1 5 -5 758 x NULL 0 zero 1 -1 5 -5 759 x NULL 0 zero 1 -1 0 NULL 760 x NULL 0 zero 1 -1 NULL NULL 761 x NULL 0 zero 1 -1 NULL 0 762 x 1 4 one 2 2 1 -1 763 x 1 4 one 2 2 2 2 764 x 1 4 one 2 2 3 -3 765 x 1 4 one 2 2 2 4 766 x 1 4 one 2 2 5 -5 767 x 1 4 one 2 2 5 -5 768 x 1 4 one 2 2 0 NULL 769 x 1 4 one 2 2 NULL NULL 770 x 1 4 one 2 2 NULL 0 771 x 2 3 two 2 2 1 -1 772 x 2 3 two 2 2 2 2 773 x 2 3 two 2 2 3 -3 774 x 2 3 two 2 2 2 4 775 x 2 3 two 2 2 5 -5 776 x 2 3 two 2 2 5 -5 777 x 2 3 two 2 2 0 NULL 778 x 2 3 two 2 2 NULL NULL 779 x 2 3 two 2 2 NULL 0 780 x 3 2 three 2 2 1 -1 781 x 3 2 three 2 2 2 2 782 x 3 2 three 2 2 3 -3 783 x 3 2 three 2 2 2 4 784 x 3 2 three 2 2 5 -5 785 x 3 2 three 2 2 5 -5 786 x 3 2 three 2 2 0 NULL 787 x 3 2 three 2 2 NULL NULL 788 x 3 2 three 2 2 NULL 0 789 x 4 1 four 2 2 1 -1 790 x 4 1 four 2 2 2 2 791 x 4 1 four 2 2 3 -3 792 x 4 1 four 2 2 2 4 793 x 4 1 four 2 2 5 -5 794 x 4 1 four 2 2 5 -5 795 x 4 1 four 2 2 0 NULL 796 x 4 1 four 2 2 NULL NULL 797 x 4 1 four 2 2 NULL 0 798 x 5 0 five 2 2 1 -1 799 x 5 0 five 2 2 2 2 800 x 5 0 five 2 2 3 -3 801 x 5 0 five 2 2 2 4 802 x 5 0 five 2 2 5 -5 803 x 5 0 five 2 2 5 -5 804 x 5 0 five 2 2 0 NULL 805 x 5 0 five 2 2 NULL NULL 806 x 5 0 five 2 2 NULL 0 807 x 6 6 six 2 2 1 -1 808 x 6 6 six 2 2 2 2 809 x 6 6 six 2 2 3 -3 810 x 6 6 six 2 2 2 4 811 x 6 6 six 2 2 5 -5 812 x 6 6 six 2 2 5 -5 813 x 6 6 six 2 2 0 NULL 814 x 6 6 six 2 2 NULL NULL 815 x 6 6 six 2 2 NULL 0 816 x 7 7 seven 2 2 1 -1 817 x 7 7 seven 2 2 2 2 818 x 7 7 seven 2 2 3 -3 819 x 7 7 seven 2 2 2 4 820 x 7 7 seven 2 2 5 -5 821 x 7 7 seven 2 2 5 -5 822 x 7 7 seven 2 2 0 NULL 823 x 7 7 seven 2 2 NULL NULL 824 x 7 7 seven 2 2 NULL 0 825 x 8 8 eight 2 2 1 -1 826 x 8 8 eight 2 2 2 2 827 x 8 8 eight 2 2 3 -3 828 x 8 8 eight 2 2 2 4 829 x 8 8 eight 2 2 5 -5 830 x 8 8 eight 2 2 5 -5 831 x 8 8 eight 2 2 0 NULL 832 x 8 8 eight 2 2 NULL NULL 833 x 8 8 eight 2 2 NULL 0 834 x 0 NULL zero 2 2 1 -1 835 x 0 NULL zero 2 2 2 2 836 x 0 NULL zero 2 2 3 -3 837 x 0 NULL zero 2 2 2 4 838 x 0 NULL zero 2 2 5 -5 839 x 0 NULL zero 2 2 5 -5 840 x 0 NULL zero 2 2 0 NULL 841 x 0 NULL zero 2 2 NULL NULL 842 x 0 NULL zero 2 2 NULL 0 843 x NULL NULL null 2 2 1 -1 844 x NULL NULL null 2 2 2 2 845 x NULL NULL null 2 2 3 -3 846 x NULL NULL null 2 2 2 4 847 x NULL NULL null 2 2 5 -5 848 x NULL NULL null 2 2 5 -5 849 x NULL NULL null 2 2 0 NULL 850 x NULL NULL null 2 2 NULL NULL 851 x NULL NULL null 2 2 NULL 0 852 x NULL 0 zero 2 2 1 -1 853 x NULL 0 zero 2 2 2 2 854 x NULL 0 zero 2 2 3 -3 855 x NULL 0 zero 2 2 2 4 856 x NULL 0 zero 2 2 5 -5 857 x NULL 0 zero 2 2 5 -5 858 x NULL 0 zero 2 2 0 NULL 859 x NULL 0 zero 2 2 NULL NULL 860 x NULL 0 zero 2 2 NULL 0 861 x 1 4 one 3 -3 1 -1 862 x 1 4 one 3 -3 2 2 863 x 1 4 one 3 -3 3 -3 864 x 1 4 one 3 -3 2 4 865 x 1 4 one 3 -3 5 -5 866 x 1 4 one 3 -3 5 -5 867 x 1 4 one 3 -3 0 NULL 868 x 1 4 one 3 -3 NULL NULL 869 x 1 4 one 3 -3 NULL 0 870 x 2 3 two 3 -3 1 -1 871 x 2 3 two 3 -3 2 2 872 x 2 3 two 3 -3 3 -3 873 x 2 3 two 3 -3 2 4 874 x 2 3 two 3 -3 5 -5 875 x 2 3 two 3 -3 5 -5 876 x 2 3 two 3 -3 0 NULL 877 x 2 3 two 3 -3 NULL NULL 878 x 2 3 two 3 -3 NULL 0 879 x 3 2 three 3 -3 1 -1 880 x 3 2 three 3 -3 2 2 881 x 3 2 three 3 -3 3 -3 882 x 3 2 three 3 -3 2 4 883 x 3 2 three 3 -3 5 -5 884 x 3 2 three 3 -3 5 -5 885 x 3 2 three 3 -3 0 NULL 886 x 3 2 three 3 -3 NULL NULL 887 x 3 2 three 3 -3 NULL 0 888 x 4 1 four 3 -3 1 -1 889 x 4 1 four 3 -3 2 2 890 x 4 1 four 3 -3 3 -3 891 x 4 1 four 3 -3 2 4 892 x 4 1 four 3 -3 5 -5 893 x 4 1 four 3 -3 5 -5 894 x 4 1 four 3 -3 0 NULL 895 x 4 1 four 3 -3 NULL NULL 896 x 4 1 four 3 -3 NULL 0 897 x 5 0 five 3 -3 1 -1 898 x 5 0 five 3 -3 2 2 899 x 5 0 five 3 -3 3 -3 900 x 5 0 five 3 -3 2 4 901 x 5 0 five 3 -3 5 -5 902 x 5 0 five 3 -3 5 -5 903 x 5 0 five 3 -3 0 NULL 904 x 5 0 five 3 -3 NULL NULL 905 x 5 0 five 3 -3 NULL 0 906 x 6 6 six 3 -3 1 -1 907 x 6 6 six 3 -3 2 2 908 x 6 6 six 3 -3 3 -3 909 x 6 6 six 3 -3 2 4 910 x 6 6 six 3 -3 5 -5 911 x 6 6 six 3 -3 5 -5 912 x 6 6 six 3 -3 0 NULL 913 x 6 6 six 3 -3 NULL NULL 914 x 6 6 six 3 -3 NULL 0 915 x 7 7 seven 3 -3 1 -1 916 x 7 7 seven 3 -3 2 2 917 x 7 7 seven 3 -3 3 -3 918 x 7 7 seven 3 -3 2 4 919 x 7 7 seven 3 -3 5 -5 920 x 7 7 seven 3 -3 5 -5 921 x 7 7 seven 3 -3 0 NULL 922 x 7 7 seven 3 -3 NULL NULL 923 x 7 7 seven 3 -3 NULL 0 924 x 8 8 eight 3 -3 1 -1 925 x 8 8 eight 3 -3 2 2 926 x 8 8 eight 3 -3 3 -3 927 x 8 8 eight 3 -3 2 4 928 x 8 8 eight 3 -3 5 -5 929 x 8 8 eight 3 -3 5 -5 930 x 8 8 eight 3 -3 0 NULL 931 x 8 8 eight 3 -3 NULL NULL 932 x 8 8 eight 3 -3 NULL 0 933 x 0 NULL zero 3 -3 1 -1 934 x 0 NULL zero 3 -3 2 2 935 x 0 NULL zero 3 -3 3 -3 936 x 0 NULL zero 3 -3 2 4 937 x 0 NULL zero 3 -3 5 -5 938 x 0 NULL zero 3 -3 5 -5 939 x 0 NULL zero 3 -3 0 NULL 940 x 0 NULL zero 3 -3 NULL NULL 941 x 0 NULL zero 3 -3 NULL 0 942 x NULL NULL null 3 -3 1 -1 943 x NULL NULL null 3 -3 2 2 944 x NULL NULL null 3 -3 3 -3 945 x NULL NULL null 3 -3 2 4 946 x NULL NULL null 3 -3 5 -5 947 x NULL NULL null 3 -3 5 -5 948 x NULL NULL null 3 -3 0 NULL 949 x NULL NULL null 3 -3 NULL NULL 950 x NULL NULL null 3 -3 NULL 0 951 x NULL 0 zero 3 -3 1 -1 952 x NULL 0 zero 3 -3 2 2 953 x NULL 0 zero 3 -3 3 -3 954 x NULL 0 zero 3 -3 2 4 955 x NULL 0 zero 3 -3 5 -5 956 x NULL 0 zero 3 -3 5 -5 957 x NULL 0 zero 3 -3 0 NULL 958 x NULL 0 zero 3 -3 NULL NULL 959 x NULL 0 zero 3 -3 NULL 0 960 x 1 4 one 2 4 1 -1 961 x 1 4 one 2 4 2 2 962 x 1 4 one 2 4 3 -3 963 x 1 4 one 2 4 2 4 964 x 1 4 one 2 4 5 -5 965 x 1 4 one 2 4 5 -5 966 x 1 4 one 2 4 0 NULL 967 x 1 4 one 2 4 NULL NULL 968 x 1 4 one 2 4 NULL 0 969 x 2 3 two 2 4 1 -1 970 x 2 3 two 2 4 2 2 971 x 2 3 two 2 4 3 -3 972 x 2 3 two 2 4 2 4 973 x 2 3 two 2 4 5 -5 974 x 2 3 two 2 4 5 -5 975 x 2 3 two 2 4 0 NULL 976 x 2 3 two 2 4 NULL NULL 977 x 2 3 two 2 4 NULL 0 978 x 3 2 three 2 4 1 -1 979 x 3 2 three 2 4 2 2 980 x 3 2 three 2 4 3 -3 981 x 3 2 three 2 4 2 4 982 x 3 2 three 2 4 5 -5 983 x 3 2 three 2 4 5 -5 984 x 3 2 three 2 4 0 NULL 985 x 3 2 three 2 4 NULL NULL 986 x 3 2 three 2 4 NULL 0 987 x 4 1 four 2 4 1 -1 988 x 4 1 four 2 4 2 2 989 x 4 1 four 2 4 3 -3 990 x 4 1 four 2 4 2 4 991 x 4 1 four 2 4 5 -5 992 x 4 1 four 2 4 5 -5 993 x 4 1 four 2 4 0 NULL 994 x 4 1 four 2 4 NULL NULL 995 x 4 1 four 2 4 NULL 0 996 x 5 0 five 2 4 1 -1 997 x 5 0 five 2 4 2 2 998 x 5 0 five 2 4 3 -3 999 x 5 0 five 2 4 2 4 1000 x 5 0 five 2 4 5 -5 1001 x 5 0 five 2 4 5 -5 1002 x 5 0 five 2 4 0 NULL 1003 x 5 0 five 2 4 NULL NULL 1004 x 5 0 five 2 4 NULL 0 1005 x 6 6 six 2 4 1 -1 1006 x 6 6 six 2 4 2 2 1007 x 6 6 six 2 4 3 -3 1008 x 6 6 six 2 4 2 4 1009 x 6 6 six 2 4 5 -5 1010 x 6 6 six 2 4 5 -5 1011 x 6 6 six 2 4 0 NULL 1012 x 6 6 six 2 4 NULL NULL 1013 x 6 6 six 2 4 NULL 0 1014 x 7 7 seven 2 4 1 -1 1015 x 7 7 seven 2 4 2 2 1016 x 7 7 seven 2 4 3 -3 1017 x 7 7 seven 2 4 2 4 1018 x 7 7 seven 2 4 5 -5 1019 x 7 7 seven 2 4 5 -5 1020 x 7 7 seven 2 4 0 NULL 1021 x 7 7 seven 2 4 NULL NULL 1022 x 7 7 seven 2 4 NULL 0 1023 x 8 8 eight 2 4 1 -1 1024 x 8 8 eight 2 4 2 2 1025 x 8 8 eight 2 4 3 -3 1026 x 8 8 eight 2 4 2 4 1027 x 8 8 eight 2 4 5 -5 1028 x 8 8 eight 2 4 5 -5 1029 x 8 8 eight 2 4 0 NULL 1030 x 8 8 eight 2 4 NULL NULL 1031 x 8 8 eight 2 4 NULL 0 1032 x 0 NULL zero 2 4 1 -1 1033 x 0 NULL zero 2 4 2 2 1034 x 0 NULL zero 2 4 3 -3 1035 x 0 NULL zero 2 4 2 4 1036 x 0 NULL zero 2 4 5 -5 1037 x 0 NULL zero 2 4 5 -5 1038 x 0 NULL zero 2 4 0 NULL 1039 x 0 NULL zero 2 4 NULL NULL 1040 x 0 NULL zero 2 4 NULL 0 1041 x NULL NULL null 2 4 1 -1 1042 x NULL NULL null 2 4 2 2 1043 x NULL NULL null 2 4 3 -3 1044 x NULL NULL null 2 4 2 4 1045 x NULL NULL null 2 4 5 -5 1046 x NULL NULL null 2 4 5 -5 1047 x NULL NULL null 2 4 0 NULL 1048 x NULL NULL null 2 4 NULL NULL 1049 x NULL NULL null 2 4 NULL 0 1050 x NULL 0 zero 2 4 1 -1 1051 x NULL 0 zero 2 4 2 2 1052 x NULL 0 zero 2 4 3 -3 1053 x NULL 0 zero 2 4 2 4 1054 x NULL 0 zero 2 4 5 -5 1055 x NULL 0 zero 2 4 5 -5 1056 x NULL 0 zero 2 4 0 NULL 1057 x NULL 0 zero 2 4 NULL NULL 1058 x NULL 0 zero 2 4 NULL 0 1059 x 1 4 one 5 -5 1 -1 1060 x 1 4 one 5 -5 2 2 1061 x 1 4 one 5 -5 3 -3 1062 x 1 4 one 5 -5 2 4 1063 x 1 4 one 5 -5 5 -5 1064 x 1 4 one 5 -5 5 -5 1065 x 1 4 one 5 -5 0 NULL 1066 x 1 4 one 5 -5 NULL NULL 1067 x 1 4 one 5 -5 NULL 0 1068 x 2 3 two 5 -5 1 -1 1069 x 2 3 two 5 -5 2 2 1070 x 2 3 two 5 -5 3 -3 1071 x 2 3 two 5 -5 2 4 1072 x 2 3 two 5 -5 5 -5 1073 x 2 3 two 5 -5 5 -5 1074 x 2 3 two 5 -5 0 NULL 1075 x 2 3 two 5 -5 NULL NULL 1076 x 2 3 two 5 -5 NULL 0 1077 x 3 2 three 5 -5 1 -1 1078 x 3 2 three 5 -5 2 2 1079 x 3 2 three 5 -5 3 -3 1080 x 3 2 three 5 -5 2 4 1081 x 3 2 three 5 -5 5 -5 1082 x 3 2 three 5 -5 5 -5 1083 x 3 2 three 5 -5 0 NULL 1084 x 3 2 three 5 -5 NULL NULL 1085 x 3 2 three 5 -5 NULL 0 1086 x 4 1 four 5 -5 1 -1 1087 x 4 1 four 5 -5 2 2 1088 x 4 1 four 5 -5 3 -3 1089 x 4 1 four 5 -5 2 4 1090 x 4 1 four 5 -5 5 -5 1091 x 4 1 four 5 -5 5 -5 1092 x 4 1 four 5 -5 0 NULL 1093 x 4 1 four 5 -5 NULL NULL 1094 x 4 1 four 5 -5 NULL 0 1095 x 5 0 five 5 -5 1 -1 1096 x 5 0 five 5 -5 2 2 1097 x 5 0 five 5 -5 3 -3 1098 x 5 0 five 5 -5 2 4 1099 x 5 0 five 5 -5 5 -5 1100 x 5 0 five 5 -5 5 -5 1101 x 5 0 five 5 -5 0 NULL 1102 x 5 0 five 5 -5 NULL NULL 1103 x 5 0 five 5 -5 NULL 0 1104 x 6 6 six 5 -5 1 -1 1105 x 6 6 six 5 -5 2 2 1106 x 6 6 six 5 -5 3 -3 1107 x 6 6 six 5 -5 2 4 1108 x 6 6 six 5 -5 5 -5 1109 x 6 6 six 5 -5 5 -5 1110 x 6 6 six 5 -5 0 NULL 1111 x 6 6 six 5 -5 NULL NULL 1112 x 6 6 six 5 -5 NULL 0 1113 x 7 7 seven 5 -5 1 -1 1114 x 7 7 seven 5 -5 2 2 1115 x 7 7 seven 5 -5 3 -3 1116 x 7 7 seven 5 -5 2 4 1117 x 7 7 seven 5 -5 5 -5 1118 x 7 7 seven 5 -5 5 -5 1119 x 7 7 seven 5 -5 0 NULL 1120 x 7 7 seven 5 -5 NULL NULL 1121 x 7 7 seven 5 -5 NULL 0 1122 x 8 8 eight 5 -5 1 -1 1123 x 8 8 eight 5 -5 2 2 1124 x 8 8 eight 5 -5 3 -3 1125 x 8 8 eight 5 -5 2 4 1126 x 8 8 eight 5 -5 5 -5 1127 x 8 8 eight 5 -5 5 -5 1128 x 8 8 eight 5 -5 0 NULL 1129 x 8 8 eight 5 -5 NULL NULL 1130 x 8 8 eight 5 -5 NULL 0 1131 x 0 NULL zero 5 -5 1 -1 1132 x 0 NULL zero 5 -5 2 2 1133 x 0 NULL zero 5 -5 3 -3 1134 x 0 NULL zero 5 -5 2 4 1135 x 0 NULL zero 5 -5 5 -5 1136 x 0 NULL zero 5 -5 5 -5 1137 x 0 NULL zero 5 -5 0 NULL 1138 x 0 NULL zero 5 -5 NULL NULL 1139 x 0 NULL zero 5 -5 NULL 0 1140 x NULL NULL null 5 -5 1 -1 1141 x NULL NULL null 5 -5 2 2 1142 x NULL NULL null 5 -5 3 -3 1143 x NULL NULL null 5 -5 2 4 1144 x NULL NULL null 5 -5 5 -5 1145 x NULL NULL null 5 -5 5 -5 1146 x NULL NULL null 5 -5 0 NULL 1147 x NULL NULL null 5 -5 NULL NULL 1148 x NULL NULL null 5 -5 NULL 0 1149 x NULL 0 zero 5 -5 1 -1 1150 x NULL 0 zero 5 -5 2 2 1151 x NULL 0 zero 5 -5 3 -3 1152 x NULL 0 zero 5 -5 2 4 1153 x NULL 0 zero 5 -5 5 -5 1154 x NULL 0 zero 5 -5 5 -5 1155 x NULL 0 zero 5 -5 0 NULL 1156 x NULL 0 zero 5 -5 NULL NULL 1157 x NULL 0 zero 5 -5 NULL 0 1158 x 1 4 one 5 -5 1 -1 1159 x 1 4 one 5 -5 2 2 1160 x 1 4 one 5 -5 3 -3 1161 x 1 4 one 5 -5 2 4 1162 x 1 4 one 5 -5 5 -5 1163 x 1 4 one 5 -5 5 -5 1164 x 1 4 one 5 -5 0 NULL 1165 x 1 4 one 5 -5 NULL NULL 1166 x 1 4 one 5 -5 NULL 0 1167 x 2 3 two 5 -5 1 -1 1168 x 2 3 two 5 -5 2 2 1169 x 2 3 two 5 -5 3 -3 1170 x 2 3 two 5 -5 2 4 1171 x 2 3 two 5 -5 5 -5 1172 x 2 3 two 5 -5 5 -5 1173 x 2 3 two 5 -5 0 NULL 1174 x 2 3 two 5 -5 NULL NULL 1175 x 2 3 two 5 -5 NULL 0 1176 x 3 2 three 5 -5 1 -1 1177 x 3 2 three 5 -5 2 2 1178 x 3 2 three 5 -5 3 -3 1179 x 3 2 three 5 -5 2 4 1180 x 3 2 three 5 -5 5 -5 1181 x 3 2 three 5 -5 5 -5 1182 x 3 2 three 5 -5 0 NULL 1183 x 3 2 three 5 -5 NULL NULL 1184 x 3 2 three 5 -5 NULL 0 1185 x 4 1 four 5 -5 1 -1 1186 x 4 1 four 5 -5 2 2 1187 x 4 1 four 5 -5 3 -3 1188 x 4 1 four 5 -5 2 4 1189 x 4 1 four 5 -5 5 -5 1190 x 4 1 four 5 -5 5 -5 1191 x 4 1 four 5 -5 0 NULL 1192 x 4 1 four 5 -5 NULL NULL 1193 x 4 1 four 5 -5 NULL 0 1194 x 5 0 five 5 -5 1 -1 1195 x 5 0 five 5 -5 2 2 1196 x 5 0 five 5 -5 3 -3 1197 x 5 0 five 5 -5 2 4 1198 x 5 0 five 5 -5 5 -5 1199 x 5 0 five 5 -5 5 -5 1200 x 5 0 five 5 -5 0 NULL 1201 x 5 0 five 5 -5 NULL NULL 1202 x 5 0 five 5 -5 NULL 0 1203 x 6 6 six 5 -5 1 -1 1204 x 6 6 six 5 -5 2 2 1205 x 6 6 six 5 -5 3 -3 1206 x 6 6 six 5 -5 2 4 1207 x 6 6 six 5 -5 5 -5 1208 x 6 6 six 5 -5 5 -5 1209 x 6 6 six 5 -5 0 NULL 1210 x 6 6 six 5 -5 NULL NULL 1211 x 6 6 six 5 -5 NULL 0 1212 x 7 7 seven 5 -5 1 -1 1213 x 7 7 seven 5 -5 2 2 1214 x 7 7 seven 5 -5 3 -3 1215 x 7 7 seven 5 -5 2 4 1216 x 7 7 seven 5 -5 5 -5 1217 x 7 7 seven 5 -5 5 -5 1218 x 7 7 seven 5 -5 0 NULL 1219 x 7 7 seven 5 -5 NULL NULL 1220 x 7 7 seven 5 -5 NULL 0 1221 x 8 8 eight 5 -5 1 -1 1222 x 8 8 eight 5 -5 2 2 1223 x 8 8 eight 5 -5 3 -3 1224 x 8 8 eight 5 -5 2 4 1225 x 8 8 eight 5 -5 5 -5 1226 x 8 8 eight 5 -5 5 -5 1227 x 8 8 eight 5 -5 0 NULL 1228 x 8 8 eight 5 -5 NULL NULL 1229 x 8 8 eight 5 -5 NULL 0 1230 x 0 NULL zero 5 -5 1 -1 1231 x 0 NULL zero 5 -5 2 2 1232 x 0 NULL zero 5 -5 3 -3 1233 x 0 NULL zero 5 -5 2 4 1234 x 0 NULL zero 5 -5 5 -5 1235 x 0 NULL zero 5 -5 5 -5 1236 x 0 NULL zero 5 -5 0 NULL 1237 x 0 NULL zero 5 -5 NULL NULL 1238 x 0 NULL zero 5 -5 NULL 0 1239 x NULL NULL null 5 -5 1 -1 1240 x NULL NULL null 5 -5 2 2 1241 x NULL NULL null 5 -5 3 -3 1242 x NULL NULL null 5 -5 2 4 1243 x NULL NULL null 5 -5 5 -5 1244 x NULL NULL null 5 -5 5 -5 1245 x NULL NULL null 5 -5 0 NULL 1246 x NULL NULL null 5 -5 NULL NULL 1247 x NULL NULL null 5 -5 NULL 0 1248 x NULL 0 zero 5 -5 1 -1 1249 x NULL 0 zero 5 -5 2 2 1250 x NULL 0 zero 5 -5 3 -3 1251 x NULL 0 zero 5 -5 2 4 1252 x NULL 0 zero 5 -5 5 -5 1253 x NULL 0 zero 5 -5 5 -5 1254 x NULL 0 zero 5 -5 0 NULL 1255 x NULL 0 zero 5 -5 NULL NULL 1256 x NULL 0 zero 5 -5 NULL 0 1257 x 1 4 one 0 NULL 1 -1 1258 x 1 4 one 0 NULL 2 2 1259 x 1 4 one 0 NULL 3 -3 1260 x 1 4 one 0 NULL 2 4 1261 x 1 4 one 0 NULL 5 -5 1262 x 1 4 one 0 NULL 5 -5 1263 x 1 4 one 0 NULL 0 NULL 1264 x 1 4 one 0 NULL NULL NULL 1265 x 1 4 one 0 NULL NULL 0 1266 x 2 3 two 0 NULL 1 -1 1267 x 2 3 two 0 NULL 2 2 1268 x 2 3 two 0 NULL 3 -3 1269 x 2 3 two 0 NULL 2 4 1270 x 2 3 two 0 NULL 5 -5 1271 x 2 3 two 0 NULL 5 -5 1272 x 2 3 two 0 NULL 0 NULL 1273 x 2 3 two 0 NULL NULL NULL 1274 x 2 3 two 0 NULL NULL 0 1275 x 3 2 three 0 NULL 1 -1 1276 x 3 2 three 0 NULL 2 2 1277 x 3 2 three 0 NULL 3 -3 1278 x 3 2 three 0 NULL 2 4 1279 x 3 2 three 0 NULL 5 -5 1280 x 3 2 three 0 NULL 5 -5 1281 x 3 2 three 0 NULL 0 NULL 1282 x 3 2 three 0 NULL NULL NULL 1283 x 3 2 three 0 NULL NULL 0 1284 x 4 1 four 0 NULL 1 -1 1285 x 4 1 four 0 NULL 2 2 1286 x 4 1 four 0 NULL 3 -3 1287 x 4 1 four 0 NULL 2 4 1288 x 4 1 four 0 NULL 5 -5 1289 x 4 1 four 0 NULL 5 -5 1290 x 4 1 four 0 NULL 0 NULL 1291 x 4 1 four 0 NULL NULL NULL 1292 x 4 1 four 0 NULL NULL 0 1293 x 5 0 five 0 NULL 1 -1 1294 x 5 0 five 0 NULL 2 2 1295 x 5 0 five 0 NULL 3 -3 1296 x 5 0 five 0 NULL 2 4 1297 x 5 0 five 0 NULL 5 -5 1298 x 5 0 five 0 NULL 5 -5 1299 x 5 0 five 0 NULL 0 NULL 1300 x 5 0 five 0 NULL NULL NULL 1301 x 5 0 five 0 NULL NULL 0 1302 x 6 6 six 0 NULL 1 -1 1303 x 6 6 six 0 NULL 2 2 1304 x 6 6 six 0 NULL 3 -3 1305 x 6 6 six 0 NULL 2 4 1306 x 6 6 six 0 NULL 5 -5 1307 x 6 6 six 0 NULL 5 -5 1308 x 6 6 six 0 NULL 0 NULL 1309 x 6 6 six 0 NULL NULL NULL 1310 x 6 6 six 0 NULL NULL 0 1311 x 7 7 seven 0 NULL 1 -1 1312 x 7 7 seven 0 NULL 2 2 1313 x 7 7 seven 0 NULL 3 -3 1314 x 7 7 seven 0 NULL 2 4 1315 x 7 7 seven 0 NULL 5 -5 1316 x 7 7 seven 0 NULL 5 -5 1317 x 7 7 seven 0 NULL 0 NULL 1318 x 7 7 seven 0 NULL NULL NULL 1319 x 7 7 seven 0 NULL NULL 0 1320 x 8 8 eight 0 NULL 1 -1 1321 x 8 8 eight 0 NULL 2 2 1322 x 8 8 eight 0 NULL 3 -3 1323 x 8 8 eight 0 NULL 2 4 1324 x 8 8 eight 0 NULL 5 -5 1325 x 8 8 eight 0 NULL 5 -5 1326 x 8 8 eight 0 NULL 0 NULL 1327 x 8 8 eight 0 NULL NULL NULL 1328 x 8 8 eight 0 NULL NULL 0 1329 x 0 NULL zero 0 NULL 1 -1 1330 x 0 NULL zero 0 NULL 2 2 1331 x 0 NULL zero 0 NULL 3 -3 1332 x 0 NULL zero 0 NULL 2 4 1333 x 0 NULL zero 0 NULL 5 -5 1334 x 0 NULL zero 0 NULL 5 -5 1335 x 0 NULL zero 0 NULL 0 NULL 1336 x 0 NULL zero 0 NULL NULL NULL 1337 x 0 NULL zero 0 NULL NULL 0 1338 x NULL NULL null 0 NULL 1 -1 1339 x NULL NULL null 0 NULL 2 2 1340 x NULL NULL null 0 NULL 3 -3 1341 x NULL NULL null 0 NULL 2 4 1342 x NULL NULL null 0 NULL 5 -5 1343 x NULL NULL null 0 NULL 5 -5 1344 x NULL NULL null 0 NULL 0 NULL 1345 x NULL NULL null 0 NULL NULL NULL 1346 x NULL NULL null 0 NULL NULL 0 1347 x NULL 0 zero 0 NULL 1 -1 1348 x NULL 0 zero 0 NULL 2 2 1349 x NULL 0 zero 0 NULL 3 -3 1350 x NULL 0 zero 0 NULL 2 4 1351 x NULL 0 zero 0 NULL 5 -5 1352 x NULL 0 zero 0 NULL 5 -5 1353 x NULL 0 zero 0 NULL 0 NULL 1354 x NULL 0 zero 0 NULL NULL NULL 1355 x NULL 0 zero 0 NULL NULL 0 1356 x 1 4 one NULL NULL 1 -1 1357 x 1 4 one NULL NULL 2 2 1358 x 1 4 one NULL NULL 3 -3 1359 x 1 4 one NULL NULL 2 4 1360 x 1 4 one NULL NULL 5 -5 1361 x 1 4 one NULL NULL 5 -5 1362 x 1 4 one NULL NULL 0 NULL 1363 x 1 4 one NULL NULL NULL NULL 1364 x 1 4 one NULL NULL NULL 0 1365 x 2 3 two NULL NULL 1 -1 1366 x 2 3 two NULL NULL 2 2 1367 x 2 3 two NULL NULL 3 -3 1368 x 2 3 two NULL NULL 2 4 1369 x 2 3 two NULL NULL 5 -5 1370 x 2 3 two NULL NULL 5 -5 1371 x 2 3 two NULL NULL 0 NULL 1372 x 2 3 two NULL NULL NULL NULL 1373 x 2 3 two NULL NULL NULL 0 1374 x 3 2 three NULL NULL 1 -1 1375 x 3 2 three NULL NULL 2 2 1376 x 3 2 three NULL NULL 3 -3 1377 x 3 2 three NULL NULL 2 4 1378 x 3 2 three NULL NULL 5 -5 1379 x 3 2 three NULL NULL 5 -5 1380 x 3 2 three NULL NULL 0 NULL 1381 x 3 2 three NULL NULL NULL NULL 1382 x 3 2 three NULL NULL NULL 0 1383 x 4 1 four NULL NULL 1 -1 1384 x 4 1 four NULL NULL 2 2 1385 x 4 1 four NULL NULL 3 -3 1386 x 4 1 four NULL NULL 2 4 1387 x 4 1 four NULL NULL 5 -5 1388 x 4 1 four NULL NULL 5 -5 1389 x 4 1 four NULL NULL 0 NULL 1390 x 4 1 four NULL NULL NULL NULL 1391 x 4 1 four NULL NULL NULL 0 1392 x 5 0 five NULL NULL 1 -1 1393 x 5 0 five NULL NULL 2 2 1394 x 5 0 five NULL NULL 3 -3 1395 x 5 0 five NULL NULL 2 4 1396 x 5 0 five NULL NULL 5 -5 1397 x 5 0 five NULL NULL 5 -5 1398 x 5 0 five NULL NULL 0 NULL 1399 x 5 0 five NULL NULL NULL NULL 1400 x 5 0 five NULL NULL NULL 0 1401 x 6 6 six NULL NULL 1 -1 1402 x 6 6 six NULL NULL 2 2 1403 x 6 6 six NULL NULL 3 -3 1404 x 6 6 six NULL NULL 2 4 1405 x 6 6 six NULL NULL 5 -5 1406 x 6 6 six NULL NULL 5 -5 1407 x 6 6 six NULL NULL 0 NULL 1408 x 6 6 six NULL NULL NULL NULL 1409 x 6 6 six NULL NULL NULL 0 1410 x 7 7 seven NULL NULL 1 -1 1411 x 7 7 seven NULL NULL 2 2 1412 x 7 7 seven NULL NULL 3 -3 1413 x 7 7 seven NULL NULL 2 4 1414 x 7 7 seven NULL NULL 5 -5 1415 x 7 7 seven NULL NULL 5 -5 1416 x 7 7 seven NULL NULL 0 NULL 1417 x 7 7 seven NULL NULL NULL NULL 1418 x 7 7 seven NULL NULL NULL 0 1419 x 8 8 eight NULL NULL 1 -1 1420 x 8 8 eight NULL NULL 2 2 1421 x 8 8 eight NULL NULL 3 -3 1422 x 8 8 eight NULL NULL 2 4 1423 x 8 8 eight NULL NULL 5 -5 1424 x 8 8 eight NULL NULL 5 -5 1425 x 8 8 eight NULL NULL 0 NULL 1426 x 8 8 eight NULL NULL NULL NULL 1427 x 8 8 eight NULL NULL NULL 0 1428 x 0 NULL zero NULL NULL 1 -1 1429 x 0 NULL zero NULL NULL 2 2 1430 x 0 NULL zero NULL NULL 3 -3 1431 x 0 NULL zero NULL NULL 2 4 1432 x 0 NULL zero NULL NULL 5 -5 1433 x 0 NULL zero NULL NULL 5 -5 1434 x 0 NULL zero NULL NULL 0 NULL 1435 x 0 NULL zero NULL NULL NULL NULL 1436 x 0 NULL zero NULL NULL NULL 0 1437 x NULL NULL null NULL NULL 1 -1 1438 x NULL NULL null NULL NULL 2 2 1439 x NULL NULL null NULL NULL 3 -3 1440 x NULL NULL null NULL NULL 2 4 1441 x NULL NULL null NULL NULL 5 -5 1442 x NULL NULL null NULL NULL 5 -5 1443 x NULL NULL null NULL NULL 0 NULL 1444 x NULL NULL null NULL NULL NULL NULL 1445 x NULL NULL null NULL NULL NULL 0 1446 x NULL 0 zero NULL NULL 1 -1 1447 x NULL 0 zero NULL NULL 2 2 1448 x NULL 0 zero NULL NULL 3 -3 1449 x NULL 0 zero NULL NULL 2 4 1450 x NULL 0 zero NULL NULL 5 -5 1451 x NULL 0 zero NULL NULL 5 -5 1452 x NULL 0 zero NULL NULL 0 NULL 1453 x NULL 0 zero NULL NULL NULL NULL 1454 x NULL 0 zero NULL NULL NULL 0 1455 x 1 4 one NULL 0 1 -1 1456 x 1 4 one NULL 0 2 2 1457 x 1 4 one NULL 0 3 -3 1458 x 1 4 one NULL 0 2 4 1459 x 1 4 one NULL 0 5 -5 1460 x 1 4 one NULL 0 5 -5 1461 x 1 4 one NULL 0 0 NULL 1462 x 1 4 one NULL 0 NULL NULL 1463 x 1 4 one NULL 0 NULL 0 1464 x 2 3 two NULL 0 1 -1 1465 x 2 3 two NULL 0 2 2 1466 x 2 3 two NULL 0 3 -3 1467 x 2 3 two NULL 0 2 4 1468 x 2 3 two NULL 0 5 -5 1469 x 2 3 two NULL 0 5 -5 1470 x 2 3 two NULL 0 0 NULL 1471 x 2 3 two NULL 0 NULL NULL 1472 x 2 3 two NULL 0 NULL 0 1473 x 3 2 three NULL 0 1 -1 1474 x 3 2 three NULL 0 2 2 1475 x 3 2 three NULL 0 3 -3 1476 x 3 2 three NULL 0 2 4 1477 x 3 2 three NULL 0 5 -5 1478 x 3 2 three NULL 0 5 -5 1479 x 3 2 three NULL 0 0 NULL 1480 x 3 2 three NULL 0 NULL NULL 1481 x 3 2 three NULL 0 NULL 0 1482 x 4 1 four NULL 0 1 -1 1483 x 4 1 four NULL 0 2 2 1484 x 4 1 four NULL 0 3 -3 1485 x 4 1 four NULL 0 2 4 1486 x 4 1 four NULL 0 5 -5 1487 x 4 1 four NULL 0 5 -5 1488 x 4 1 four NULL 0 0 NULL 1489 x 4 1 four NULL 0 NULL NULL 1490 x 4 1 four NULL 0 NULL 0 1491 x 5 0 five NULL 0 1 -1 1492 x 5 0 five NULL 0 2 2 1493 x 5 0 five NULL 0 3 -3 1494 x 5 0 five NULL 0 2 4 1495 x 5 0 five NULL 0 5 -5 1496 x 5 0 five NULL 0 5 -5 1497 x 5 0 five NULL 0 0 NULL 1498 x 5 0 five NULL 0 NULL NULL 1499 x 5 0 five NULL 0 NULL 0 1500 x 6 6 six NULL 0 1 -1 1501 x 6 6 six NULL 0 2 2 1502 x 6 6 six NULL 0 3 -3 1503 x 6 6 six NULL 0 2 4 1504 x 6 6 six NULL 0 5 -5 1505 x 6 6 six NULL 0 5 -5 1506 x 6 6 six NULL 0 0 NULL 1507 x 6 6 six NULL 0 NULL NULL 1508 x 6 6 six NULL 0 NULL 0 1509 x 7 7 seven NULL 0 1 -1 1510 x 7 7 seven NULL 0 2 2 1511 x 7 7 seven NULL 0 3 -3 1512 x 7 7 seven NULL 0 2 4 1513 x 7 7 seven NULL 0 5 -5 1514 x 7 7 seven NULL 0 5 -5 1515 x 7 7 seven NULL 0 0 NULL 1516 x 7 7 seven NULL 0 NULL NULL 1517 x 7 7 seven NULL 0 NULL 0 1518 x 8 8 eight NULL 0 1 -1 1519 x 8 8 eight NULL 0 2 2 1520 x 8 8 eight NULL 0 3 -3 1521 x 8 8 eight NULL 0 2 4 1522 x 8 8 eight NULL 0 5 -5 1523 x 8 8 eight NULL 0 5 -5 1524 x 8 8 eight NULL 0 0 NULL 1525 x 8 8 eight NULL 0 NULL NULL 1526 x 8 8 eight NULL 0 NULL 0 1527 x 0 NULL zero NULL 0 1 -1 1528 x 0 NULL zero NULL 0 2 2 1529 x 0 NULL zero NULL 0 3 -3 1530 x 0 NULL zero NULL 0 2 4 1531 x 0 NULL zero NULL 0 5 -5 1532 x 0 NULL zero NULL 0 5 -5 1533 x 0 NULL zero NULL 0 0 NULL 1534 x 0 NULL zero NULL 0 NULL NULL 1535 x 0 NULL zero NULL 0 NULL 0 1536 x NULL NULL null NULL 0 1 -1 1537 x NULL NULL null NULL 0 2 2 1538 x NULL NULL null NULL 0 3 -3 1539 x NULL NULL null NULL 0 2 4 1540 x NULL NULL null NULL 0 5 -5 1541 x NULL NULL null NULL 0 5 -5 1542 x NULL NULL null NULL 0 0 NULL 1543 x NULL NULL null NULL 0 NULL NULL 1544 x NULL NULL null NULL 0 NULL 0 1545 x NULL 0 zero NULL 0 1 -1 1546 x NULL 0 zero NULL 0 2 2 1547 x NULL 0 zero NULL 0 3 -3 1548 x NULL 0 zero NULL 0 2 4 1549 x NULL 0 zero NULL 0 5 -5 1550 x NULL 0 zero NULL 0 5 -5 1551 x NULL 0 zero NULL 0 0 NULL 1552 x NULL 0 zero NULL 0 NULL NULL 1553 x NULL 0 zero NULL 0 NULL 0 1554 1555 query TIITI rowsort 1556 SELECT 'x' AS "xxx", * FROM J1_TBL INNER JOIN J2_TBL USING (i) 1557 ---- 1558 x 0 NULL zero NULL 1559 x 1 4 one -1 1560 x 2 3 two 2 1561 x 2 3 two 4 1562 x 3 2 three -3 1563 x 5 0 five -5 1564 x 5 0 five -5 1565 1566 query TIITI rowsort 1567 SELECT 'x' AS "xxx", * FROM J1_TBL JOIN J2_TBL USING (i) 1568 ---- 1569 x 0 NULL zero NULL 1570 x 1 4 one -1 1571 x 2 3 two 2 1572 x 2 3 two 4 1573 x 3 2 three -3 1574 x 5 0 five -5 1575 x 5 0 five -5 1576 1577 query TIITI 1578 SELECT 'x' AS "xxx", * FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, d) USING (a) ORDER BY a, d 1579 ---- 1580 x 0 NULL zero NULL 1581 x 1 4 one -1 1582 x 2 3 two 2 1583 x 2 3 two 4 1584 x 3 2 three -3 1585 x 5 0 five -5 1586 x 5 0 five -5 1587 1588 query TIITI rowsort 1589 SELECT 'x' AS "xxx", * FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, b) USING (b) ORDER BY b, t1.a 1590 ---- 1591 x 0 5 five NULL 1592 x 0 NULL zero NULL 1593 x 2 3 three 2 1594 x 4 1 one 2 1595 1596 query TIITI rowsort 1597 SELECT 'x' AS "xxx", * FROM J1_TBL NATURAL JOIN J2_TBL 1598 ---- 1599 x 0 NULL zero NULL 1600 x 1 4 one -1 1601 x 2 3 two 2 1602 x 2 3 two 4 1603 x 3 2 three -3 1604 x 5 0 five -5 1605 x 5 0 five -5 1606 1607 query TIITI rowsort 1608 SELECT 'x' AS "xxx", * FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (a, d) 1609 ---- 1610 x 0 NULL zero NULL 1611 x 1 4 one -1 1612 x 2 3 two 2 1613 x 2 3 two 4 1614 x 3 2 three -3 1615 x 5 0 five -5 1616 x 5 0 five -5 1617 1618 query TIITI rowsort 1619 SELECT 'x' AS "xxx", * FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (d, a) 1620 ---- 1621 x 0 NULL zero NULL 1622 x 2 3 two 2 1623 x 4 1 four 2 1624 1625 query TIITI rowsort 1626 SELECT 'x' AS "xxx", * FROM J1_TBL t1 (a, b) NATURAL JOIN J2_TBL t2 (a) 1627 ---- 1628 x 0 NULL zero NULL 1629 x 1 4 one -1 1630 x 2 3 two 2 1631 x 2 3 two 4 1632 x 3 2 three -3 1633 x 5 0 five -5 1634 x 5 0 five -5 1635 1636 query TIITII rowsort 1637 SELECT 'x' AS "xxx", * FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.i) 1638 ---- 1639 x 0 NULL zero 0 NULL 1640 x 1 4 one 1 -1 1641 x 2 3 two 2 2 1642 x 2 3 two 2 4 1643 x 3 2 three 3 -3 1644 x 5 0 five 5 -5 1645 x 5 0 five 5 -5 1646 1647 query TIITII rowsort 1648 SELECT 'x' AS "xxx", * FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.k) 1649 ---- 1650 x 0 NULL zero NULL 0 1651 x 2 3 two 2 2 1652 x 4 1 four 2 4 1653 1654 query TIITII rowsort 1655 SELECT 'x' AS "xxx", * FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i <= J2_TBL.k) 1656 ---- 1657 x 1 4 one 2 2 1658 x 2 3 two 2 2 1659 x 0 NULL zero 2 2 1660 x 1 4 one 2 4 1661 x 2 3 two 2 4 1662 x 3 2 three 2 4 1663 x 4 1 four 2 4 1664 x 0 NULL zero 2 4 1665 x 0 NULL zero NULL 0 1666 1667 query TIITI rowsort 1668 SELECT 'x' AS "xxx", * FROM J1_TBL LEFT OUTER JOIN J2_TBL USING (i) ORDER BY i, k, t 1669 ---- 1670 x 0 NULL zero NULL 1671 x 1 4 one -1 1672 x 2 3 two 2 1673 x 2 3 two 4 1674 x 3 2 three -3 1675 x 4 1 four NULL 1676 x 5 0 five -5 1677 x 5 0 five -5 1678 x 6 6 six NULL 1679 x 7 7 seven NULL 1680 x 8 8 eight NULL 1681 x NULL NULL null NULL 1682 x NULL 0 zero NULL 1683 1684 query TIITI rowsort 1685 SELECT 'x' AS "xxx", * FROM J1_TBL LEFT JOIN J2_TBL USING (i) ORDER BY i, k, t 1686 ---- 1687 x 0 NULL zero NULL 1688 x 1 4 one -1 1689 x 2 3 two 2 1690 x 2 3 two 4 1691 x 3 2 three -3 1692 x 4 1 four NULL 1693 x 5 0 five -5 1694 x 5 0 five -5 1695 x 6 6 six NULL 1696 x 7 7 seven NULL 1697 x 8 8 eight NULL 1698 x NULL NULL null NULL 1699 x NULL 0 zero NULL 1700 1701 query TIITI rowsort 1702 SELECT 'x' AS "xxx", * FROM J1_TBL RIGHT OUTER JOIN J2_TBL USING (i) 1703 ---- 1704 x 0 NULL zero NULL 1705 x 1 4 one -1 1706 x 2 3 two 2 1707 x 2 3 two 4 1708 x 3 2 three -3 1709 x 5 0 five -5 1710 x 5 0 five -5 1711 x NULL NULL NULL NULL 1712 x NULL NULL NULL 0 1713 1714 query TIITI rowsort 1715 SELECT 'x' AS "xxx", * FROM J1_TBL RIGHT JOIN J2_TBL USING (i) 1716 ---- 1717 x 0 NULL zero NULL 1718 x 1 4 one -1 1719 x 2 3 two 2 1720 x 2 3 two 4 1721 x 3 2 three -3 1722 x 5 0 five -5 1723 x 5 0 five -5 1724 x NULL NULL NULL NULL 1725 x NULL NULL NULL 0 1726 1727 query TIITI rowsort 1728 SELECT 'x' AS "xxx", * FROM J1_TBL FULL OUTER JOIN J2_TBL USING (i) ORDER BY i, k, t 1729 ---- 1730 x 0 NULL zero NULL 1731 x 1 4 one -1 1732 x 2 3 two 2 1733 x 2 3 two 4 1734 x 3 2 three -3 1735 x 4 1 four NULL 1736 x 5 0 five -5 1737 x 5 0 five -5 1738 x 6 6 six NULL 1739 x 7 7 seven NULL 1740 x 8 8 eight NULL 1741 x NULL NULL NULL 0 1742 x NULL NULL null NULL 1743 x NULL 0 zero NULL 1744 x NULL NULL NULL NULL 1745 1746 query TIITI rowsort 1747 SELECT 'x' AS "xxx", * FROM J1_TBL FULL JOIN J2_TBL USING (i) ORDER BY i, k, t 1748 ---- 1749 x 0 NULL zero NULL 1750 x 1 4 one -1 1751 x 2 3 two 2 1752 x 2 3 two 4 1753 x 3 2 three -3 1754 x 4 1 four NULL 1755 x 5 0 five -5 1756 x 5 0 five -5 1757 x 6 6 six NULL 1758 x 7 7 seven NULL 1759 x 8 8 eight NULL 1760 x NULL NULL NULL 0 1761 x NULL NULL null NULL 1762 x NULL 0 zero NULL 1763 x NULL NULL NULL NULL 1764 1765 query TIITI rowsort 1766 SELECT 'x' AS "xxx", * FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (k = 1) 1767 ---- 1768 1769 query TIITI rowsort 1770 SELECT 'x' AS "xxx", * FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (i = 1) 1771 ---- 1772 x 1 4 one -1 1773 1774 statement ok 1775 CREATE TABLE t1 (name TEXT, n INTEGER) 1776 1777 statement ok 1778 CREATE TABLE t2 (name TEXT, n INTEGER) 1779 1780 statement ok 1781 CREATE TABLE t3 (name TEXT, n INTEGER) 1782 1783 statement ok 1784 INSERT INTO t1 VALUES ( 'bb', 11 ) 1785 1786 statement ok 1787 INSERT INTO t2 VALUES ( 'bb', 12 ) 1788 1789 statement ok 1790 INSERT INTO t2 VALUES ( 'cc', 22 ) 1791 1792 statement ok 1793 INSERT INTO t2 VALUES ( 'ee', 42 ) 1794 1795 statement ok 1796 INSERT INTO t3 VALUES ( 'bb', 13 ) 1797 1798 statement ok 1799 INSERT INTO t3 VALUES ( 'cc', 23 ) 1800 1801 statement ok 1802 INSERT INTO t3 VALUES ( 'dd', 33 ) 1803 1804 query TIII rowsort 1805 SELECT * FROM t1 FULL JOIN t2 USING (name) FULL JOIN t3 USING (name) 1806 ---- 1807 bb 11 12 13 1808 cc NULL 22 23 1809 dd NULL NULL 33 1810 ee NULL 42 NULL 1811 1812 query TII rowsort 1813 SELECT * FROM (SELECT * FROM t2) as s2 INNER JOIN (SELECT * FROM t3) s3 USING (name) 1814 ---- 1815 bb 12 13 1816 cc 22 23 1817 1818 query TII rowsort 1819 SELECT * FROM (SELECT * FROM t2) as s2 LEFT JOIN (SELECT * FROM t3) s3 USING (name) 1820 ---- 1821 bb 12 13 1822 cc 22 23 1823 ee 42 NULL 1824 1825 query TII rowsort 1826 SELECT * FROM (SELECT * FROM t2) as s2 FULL JOIN (SELECT * FROM t3) s3 USING (name) 1827 ---- 1828 bb 12 13 1829 cc 22 23 1830 dd NULL 33 1831 ee 42 NULL 1832 1833 query TIIII rowsort 1834 SELECT * FROM (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 NATURAL INNER JOIN (SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3 1835 ---- 1836 bb 12 2 13 3 1837 cc 22 2 23 3 1838 1839 query TIIII rowsort 1840 SELECT * FROM (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 NATURAL LEFT JOIN (SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3 1841 ---- 1842 bb 12 2 13 3 1843 cc 22 2 23 3 1844 ee 42 2 NULL NULL 1845 1846 query TIIII rowsort 1847 SELECT * FROM (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 NATURAL FULL JOIN (SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3 1848 ---- 1849 bb 12 2 13 3 1850 cc 22 2 23 3 1851 dd NULL NULL 33 3 1852 ee 42 2 NULL NULL 1853 1854 query TIIIIII rowsort 1855 SELECT * FROM (SELECT name, n as s1_n, 1 as s1_1 FROM t1) as s1 NATURAL INNER JOIN (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 NATURAL INNER JOIN (SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3 1856 ---- 1857 bb 11 1 12 2 13 3 1858 1859 query TIIIIII rowsort 1860 SELECT * FROM (SELECT name, n as s1_n, 1 as s1_1 FROM t1) as s1 NATURAL FULL JOIN (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 NATURAL FULL JOIN (SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3 1861 ---- 1862 bb 11 1 12 2 13 3 1863 cc NULL NULL 22 2 23 3 1864 dd NULL NULL NULL NULL 33 3 1865 ee NULL NULL 42 2 NULL NULL 1866 1867 query TIII rowsort 1868 SELECT * FROM (SELECT name, n as s1_n FROM t1) as s1 NATURAL FULL JOIN (SELECT * FROM (SELECT name, n as s2_n FROM t2) as s2 NATURAL FULL JOIN (SELECT name, n as s3_n FROM t3) as s3 ) ss2 1869 ---- 1870 bb 11 12 13 1871 cc NULL 22 23 1872 dd NULL NULL 33 1873 ee NULL 42 NULL 1874 1875 query TIIII rowsort 1876 SELECT * FROM (SELECT name, n as s1_n FROM t1) as s1 NATURAL FULL JOIN (SELECT * FROM (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 NATURAL FULL JOIN (SELECT name, n as s3_n FROM t3) as s3 ) ss2 1877 ---- 1878 bb 11 12 2 13 1879 cc NULL 22 2 23 1880 dd NULL NULL NULL 33 1881 ee NULL 42 2 NULL 1882 1883 statement ok 1884 create table xt (x1 int, x2 int) 1885 1886 statement ok 1887 insert into xt values (1,11) 1888 1889 statement ok 1890 insert into xt values (2,22) 1891 1892 statement ok 1893 insert into xt values (3,null) 1894 1895 statement ok 1896 insert into xt values (4,44) 1897 1898 statement ok 1899 insert into xt values (5,null) 1900 1901 statement ok 1902 create table yt (y1 int, y2 int) 1903 1904 statement ok 1905 insert into yt values (1,111) 1906 1907 statement ok 1908 insert into yt values (2,222) 1909 1910 statement ok 1911 insert into yt values (3,333) 1912 1913 statement ok 1914 insert into yt values (4,null) 1915 1916 statement ok 1917 select * from xt left join yt on (x1 = y1 and x2 is not null) 1918 1919 statement ok 1920 select * from xt left join yt on (x1 = y1 and y2 is not null) 1921 1922 statement ok 1923 select * from (xt left join yt on (x1 = y1)) left join xt xx(xx1,xx2) on (x1 = xx1) 1924 1925 statement ok 1926 select * from (xt left join yt on (x1 = y1)) left join xt xx(xx1,xx2) on (x1 = xx1 and x2 is not null) 1927 1928 statement ok 1929 select * from (xt left join yt on (x1 = y1)) left join xt xx(xx1,xx2) on (x1 = xx1 and y2 is not null) 1930 1931 statement ok 1932 select * from (xt left join yt on (x1 = y1)) left join xt xx(xx1,xx2) on (x1 = xx1 and xx2 is not null) 1933 1934 statement ok 1935 select * from (xt left join yt on (x1 = y1)) left join xt xx(xx1,xx2) on (x1 = xx1) where (x2 is not null) 1936 1937 statement ok 1938 select * from (xt left join yt on (x1 = y1)) left join xt xx(xx1,xx2) on (x1 = xx1) where (y2 is not null) 1939 1940 statement ok 1941 select * from (xt left join yt on (x1 = y1)) left join xt xx(xx1,xx2) on (x1 = xx1) where (xx2 is not null) 1942 1943 statement ok 1944 DROP TABLE t1 1945 1946 statement ok 1947 DROP TABLE t2 1948 1949 statement ok 1950 DROP TABLE t3 1951 1952 statement ok 1953 DROP TABLE xt 1954 1955 statement ok 1956 DROP TABLE yt 1957 1958 statement ok 1959 DROP TABLE J1_TBL 1960 1961 statement ok 1962 DROP TABLE J2_TBL