github.com/hasnat/dolt/go@v0.0.0-20210628190320-9eb5d843fbb7/libraries/doltcore/sqle/dolt_index_test.go (about) 1 // Copyright 2020 Dolthub, Inc. 2 // 3 // Licensed under the Apache License, Version 2.0 (the "License"); 4 // you may not use this file except in compliance with the License. 5 // You may obtain a copy of the License at 6 // 7 // http://www.apache.org/licenses/LICENSE-2.0 8 // 9 // Unless required by applicable law or agreed to in writing, software 10 // distributed under the License is distributed on an "AS IS" BASIS, 11 // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 12 // See the License for the specific language governing permissions and 13 // limitations under the License. 14 15 package sqle 16 17 import ( 18 "context" 19 "errors" 20 "fmt" 21 "io" 22 "sort" 23 "strings" 24 "testing" 25 "time" 26 27 "github.com/dolthub/go-mysql-server/sql" 28 "github.com/stretchr/testify/require" 29 30 "github.com/dolthub/dolt/go/libraries/doltcore/doltdb" 31 "github.com/dolthub/dolt/go/libraries/doltcore/dtestutils" 32 "github.com/dolthub/dolt/go/libraries/doltcore/schema" 33 "github.com/dolthub/dolt/go/store/types" 34 ) 35 36 type doltIndexTestCase struct { 37 indexName string 38 keys []interface{} 39 expectedRows []sql.Row 40 } 41 42 type doltIndexBetweenTestCase struct { 43 indexName string 44 greaterThanOrEqual []interface{} 45 lessThanOrEqual []interface{} 46 expectedRows []sql.Row 47 } 48 49 var typesTests = []struct { 50 indexName string 51 belowfirstValue []interface{} 52 firstValue []interface{} 53 secondValue []interface{} 54 thirdValue []interface{} 55 fourthValue []interface{} 56 fifthValue []interface{} 57 abovefifthValue []interface{} 58 }{ 59 { 60 "types:primaryKey", 61 []interface{}{-4}, 62 []interface{}{-3}, 63 []interface{}{-1}, 64 []interface{}{0}, 65 []interface{}{1}, 66 []interface{}{3}, 67 []interface{}{4}, 68 }, 69 { 70 "types:idx_bit", 71 []interface{}{0}, 72 []interface{}{1}, 73 []interface{}{2}, 74 []interface{}{3}, 75 []interface{}{4}, 76 []interface{}{5}, 77 []interface{}{6}, 78 }, 79 { 80 "types:idx_datetime", 81 []interface{}{"2020-05-14 11:59:59"}, 82 []interface{}{"2020-05-14 12:00:00"}, 83 []interface{}{"2020-05-14 12:00:01"}, 84 []interface{}{"2020-05-14 12:00:02"}, 85 []interface{}{"2020-05-14 12:00:03"}, 86 []interface{}{"2020-05-14 12:00:04"}, 87 []interface{}{"2020-05-14 12:00:05"}, 88 }, 89 { 90 "types:idx_decimal", 91 []interface{}{"-4"}, 92 []interface{}{"-3.3"}, 93 []interface{}{"-1.1"}, 94 []interface{}{0}, 95 []interface{}{"1.1"}, 96 []interface{}{"3.3"}, 97 []interface{}{4}, 98 }, 99 { 100 "types:idx_enum", 101 []interface{}{"_"}, 102 []interface{}{"a"}, 103 []interface{}{"b"}, 104 []interface{}{"c"}, 105 []interface{}{"d"}, 106 []interface{}{"e"}, 107 []interface{}{"."}, 108 }, 109 { 110 "types:idx_double", 111 []interface{}{-4}, 112 []interface{}{-3.3}, 113 []interface{}{-1.1}, 114 []interface{}{0}, 115 []interface{}{1.1}, 116 []interface{}{3.3}, 117 []interface{}{4}, 118 }, 119 { 120 "types:idx_set", 121 []interface{}{""}, 122 []interface{}{"a"}, 123 []interface{}{"a,b"}, 124 []interface{}{"c"}, 125 []interface{}{"a,c"}, 126 []interface{}{"b,c"}, 127 []interface{}{"a,b,c"}, 128 }, 129 { 130 "types:idx_time", 131 []interface{}{"-00:04:04"}, 132 []interface{}{"-00:03:03"}, 133 []interface{}{"-00:01:01"}, 134 []interface{}{"00:00:00"}, 135 []interface{}{"00:01:01"}, 136 []interface{}{"00:03:03"}, 137 []interface{}{"00:04:04"}, 138 }, 139 { 140 "types:idx_varchar", 141 []interface{}{"_"}, 142 []interface{}{"a"}, 143 []interface{}{"b"}, 144 []interface{}{"c"}, 145 []interface{}{"d"}, 146 []interface{}{"e"}, 147 []interface{}{"f"}, 148 }, 149 { 150 "types:idx_year", 151 []interface{}{1975}, 152 []interface{}{1980}, 153 []interface{}{1990}, 154 []interface{}{2000}, 155 []interface{}{2010}, 156 []interface{}{2020}, 157 []interface{}{2025}, 158 }, 159 } 160 161 var ( 162 typesTableRow1 = sql.Row{int32(-3), uint64(1), forceParseTime("2020-05-14 12:00:00"), "-3.30000", "a", -3.3, "a", "-00:03:03", "a", int16(1980)} 163 typesTableRow2 = sql.Row{int32(-1), uint64(2), forceParseTime("2020-05-14 12:00:01"), "-1.10000", "b", -1.1, "a,b", "-00:01:01", "b", int16(1990)} 164 typesTableRow3 = sql.Row{int32(0), uint64(3), forceParseTime("2020-05-14 12:00:02"), "0.00000", "c", 0.0, "c", "00:00:00", "c", int16(2000)} 165 typesTableRow4 = sql.Row{int32(1), uint64(4), forceParseTime("2020-05-14 12:00:03"), "1.10000", "d", 1.1, "a,c", "00:01:01", "d", int16(2010)} 166 typesTableRow5 = sql.Row{int32(3), uint64(5), forceParseTime("2020-05-14 12:00:04"), "3.30000", "e", 3.3, "b,c", "00:03:03", "e", int16(2020)} 167 ) 168 169 func TestDoltIndexEqual(t *testing.T) { 170 indexMap := doltIndexSetup(t) 171 172 tests := []doltIndexTestCase{ 173 { 174 "onepk:primaryKey", 175 []interface{}{1}, 176 []sql.Row{{1, 1, 1}}, 177 }, 178 { 179 "onepk:primaryKey", 180 []interface{}{3}, 181 []sql.Row{{3, 3, 3}}, 182 }, 183 { 184 "onepk:primaryKey", 185 []interface{}{0}, 186 nil, 187 }, 188 { 189 "onepk:primaryKey", 190 []interface{}{5}, 191 nil, 192 }, 193 { 194 "onepk:idx_v1", 195 []interface{}{1}, 196 []sql.Row{{1, 1, 1}, {2, 1, 2}}, 197 }, 198 { 199 "onepk:idx_v1", 200 []interface{}{3}, 201 []sql.Row{{3, 3, 3}}, 202 }, 203 { 204 "twopk:primaryKey", 205 []interface{}{1, 1}, 206 []sql.Row{{1, 1, 3, 3}}, 207 }, 208 { 209 "twopk:primaryKey", 210 []interface{}{2, 0}, 211 nil, 212 }, 213 { 214 "twopk:primaryKey", 215 []interface{}{2, 1}, 216 []sql.Row{{2, 1, 4, 4}}, 217 }, 218 { 219 "twopk:idx_v2v1", 220 []interface{}{3, 4}, 221 []sql.Row{{2, 2, 4, 3}}, 222 }, 223 { 224 "twopk:idx_v2v1", 225 []interface{}{4, 3}, 226 []sql.Row{{1, 2, 3, 4}}, 227 }, 228 { 229 "twopk:idx_v2v1_PARTIAL_1", 230 []interface{}{3}, 231 []sql.Row{{1, 1, 3, 3}, {2, 2, 4, 3}}, 232 }, 233 { 234 "twopk:idx_v2v1_PARTIAL_1", 235 []interface{}{4}, 236 []sql.Row{{1, 2, 3, 4}, {2, 1, 4, 4}}, 237 }, 238 } 239 240 for _, typesTest := range typesTests { 241 tests = append(tests, doltIndexTestCase{ 242 typesTest.indexName, 243 typesTest.belowfirstValue, 244 nil, 245 }, doltIndexTestCase{ 246 typesTest.indexName, 247 typesTest.firstValue, 248 []sql.Row{ 249 typesTableRow1, 250 }, 251 }, doltIndexTestCase{ 252 typesTest.indexName, 253 typesTest.secondValue, 254 []sql.Row{ 255 typesTableRow2, 256 }, 257 }, doltIndexTestCase{ 258 typesTest.indexName, 259 typesTest.thirdValue, 260 []sql.Row{ 261 typesTableRow3, 262 }, 263 }, doltIndexTestCase{ 264 typesTest.indexName, 265 typesTest.fourthValue, 266 []sql.Row{ 267 typesTableRow4, 268 }, 269 }, doltIndexTestCase{ 270 typesTest.indexName, 271 typesTest.fifthValue, 272 []sql.Row{ 273 typesTableRow5, 274 }, 275 }, doltIndexTestCase{ 276 typesTest.indexName, 277 typesTest.abovefifthValue, 278 nil, 279 }) 280 } 281 282 for _, test := range tests { 283 t.Run(fmt.Sprintf("%s|%v", test.indexName, test.keys), func(t *testing.T) { 284 index, ok := indexMap[test.indexName] 285 require.True(t, ok) 286 testDoltIndex(t, test.keys, test.expectedRows, index.Get) 287 }) 288 } 289 } 290 291 func TestDoltIndexGreaterThan(t *testing.T) { 292 indexMap := doltIndexSetup(t) 293 294 tests := []struct { 295 indexName string 296 keys []interface{} 297 expectedRows []sql.Row 298 }{ 299 { 300 "onepk:primaryKey", 301 []interface{}{1}, 302 []sql.Row{{2, 1, 2}, {3, 3, 3}, {4, 4, 3}}, 303 }, 304 { 305 "onepk:primaryKey", 306 []interface{}{3}, 307 []sql.Row{{4, 4, 3}}, 308 }, 309 { 310 "onepk:primaryKey", 311 []interface{}{4}, 312 nil, 313 }, 314 { 315 "onepk:idx_v1", 316 []interface{}{1}, 317 []sql.Row{{3, 3, 3}, {4, 4, 3}}, 318 }, 319 { 320 "onepk:idx_v1", 321 []interface{}{3}, 322 []sql.Row{{4, 4, 3}}, 323 }, 324 { 325 "onepk:idx_v1", 326 []interface{}{4}, 327 nil, 328 }, 329 { 330 "twopk:primaryKey", 331 []interface{}{1, 1}, 332 []sql.Row{{1, 2, 3, 4}, {2, 1, 4, 4}, {2, 2, 4, 3}}, 333 }, 334 { 335 "twopk:primaryKey", 336 []interface{}{2, 1}, 337 []sql.Row{{2, 2, 4, 3}}, 338 }, 339 { 340 "twopk:idx_v2v1", 341 []interface{}{3, 4}, 342 []sql.Row{{1, 2, 3, 4}, {2, 1, 4, 4}}, 343 }, 344 { 345 "twopk:idx_v2v1", 346 []interface{}{4, 3}, 347 []sql.Row{{2, 1, 4, 4}}, 348 }, 349 { 350 "twopk:idx_v2v1_PARTIAL_1", 351 []interface{}{3}, 352 []sql.Row{{1, 2, 3, 4}, {2, 1, 4, 4}}, 353 }, 354 { 355 "twopk:idx_v2v1_PARTIAL_1", 356 []interface{}{4}, 357 nil, 358 }, 359 } 360 361 for _, typesTest := range typesTests { 362 tests = append(tests, doltIndexTestCase{ 363 typesTest.indexName, 364 typesTest.belowfirstValue, 365 []sql.Row{ 366 typesTableRow1, 367 typesTableRow2, 368 typesTableRow3, 369 typesTableRow4, 370 typesTableRow5, 371 }, 372 }, doltIndexTestCase{ 373 typesTest.indexName, 374 typesTest.firstValue, 375 []sql.Row{ 376 typesTableRow2, 377 typesTableRow3, 378 typesTableRow4, 379 typesTableRow5, 380 }, 381 }, doltIndexTestCase{ 382 typesTest.indexName, 383 typesTest.secondValue, 384 []sql.Row{ 385 typesTableRow3, 386 typesTableRow4, 387 typesTableRow5, 388 }, 389 }, doltIndexTestCase{ 390 typesTest.indexName, 391 typesTest.thirdValue, 392 []sql.Row{ 393 typesTableRow4, 394 typesTableRow5, 395 }, 396 }, doltIndexTestCase{ 397 typesTest.indexName, 398 typesTest.fourthValue, 399 []sql.Row{ 400 typesTableRow5, 401 }, 402 }, doltIndexTestCase{ 403 typesTest.indexName, 404 typesTest.fifthValue, 405 nil, 406 }, doltIndexTestCase{ 407 typesTest.indexName, 408 typesTest.abovefifthValue, 409 nil, 410 }) 411 } 412 413 for _, test := range tests { 414 t.Run(fmt.Sprintf("%s|%v", test.indexName, test.keys), func(t *testing.T) { 415 index, ok := indexMap[test.indexName] 416 require.True(t, ok) 417 testDoltIndex(t, test.keys, test.expectedRows, index.DescendGreater) 418 }) 419 } 420 } 421 422 func TestDoltIndexGreaterThanOrEqual(t *testing.T) { 423 indexMap := doltIndexSetup(t) 424 425 tests := []struct { 426 indexName string 427 keys []interface{} 428 expectedRows []sql.Row 429 }{ 430 { 431 "onepk:primaryKey", 432 []interface{}{1}, 433 []sql.Row{{1, 1, 1}, {2, 1, 2}, {3, 3, 3}, {4, 4, 3}}, 434 }, 435 { 436 "onepk:primaryKey", 437 []interface{}{3}, 438 []sql.Row{{3, 3, 3}, {4, 4, 3}}, 439 }, 440 { 441 "onepk:primaryKey", 442 []interface{}{4}, 443 []sql.Row{{4, 4, 3}}, 444 }, 445 { 446 "onepk:idx_v1", 447 []interface{}{1}, 448 []sql.Row{{1, 1, 1}, {2, 1, 2}, {3, 3, 3}, {4, 4, 3}}, 449 }, 450 { 451 "onepk:idx_v1", 452 []interface{}{3}, 453 []sql.Row{{3, 3, 3}, {4, 4, 3}}, 454 }, 455 { 456 "onepk:idx_v1", 457 []interface{}{4}, 458 []sql.Row{{4, 4, 3}}, 459 }, 460 { 461 "twopk:primaryKey", 462 []interface{}{1, 1}, 463 []sql.Row{{1, 1, 3, 3}, {1, 2, 3, 4}, {2, 1, 4, 4}, {2, 2, 4, 3}}, 464 }, 465 { 466 "twopk:primaryKey", 467 []interface{}{2, 1}, 468 []sql.Row{{2, 1, 4, 4}, {2, 2, 4, 3}}, 469 }, 470 { 471 "twopk:idx_v2v1", 472 []interface{}{3, 4}, 473 []sql.Row{{2, 2, 4, 3}, {1, 2, 3, 4}, {2, 1, 4, 4}}, 474 }, 475 { 476 "twopk:idx_v2v1", 477 []interface{}{4, 3}, 478 []sql.Row{{1, 2, 3, 4}, {2, 1, 4, 4}}, 479 }, 480 { 481 "twopk:idx_v2v1_PARTIAL_1", 482 []interface{}{3}, 483 []sql.Row{{1, 1, 3, 3}, {1, 2, 3, 4}, {2, 1, 4, 4}, {2, 2, 4, 3}}, 484 }, 485 { 486 "twopk:idx_v2v1_PARTIAL_1", 487 []interface{}{4}, 488 []sql.Row{{1, 2, 3, 4}, {2, 1, 4, 4}}, 489 }, 490 } 491 492 for _, typesTest := range typesTests { 493 tests = append(tests, doltIndexTestCase{ 494 typesTest.indexName, 495 typesTest.belowfirstValue, 496 []sql.Row{ 497 typesTableRow1, 498 typesTableRow2, 499 typesTableRow3, 500 typesTableRow4, 501 typesTableRow5, 502 }, 503 }, doltIndexTestCase{ 504 typesTest.indexName, 505 typesTest.firstValue, 506 []sql.Row{ 507 typesTableRow1, 508 typesTableRow2, 509 typesTableRow3, 510 typesTableRow4, 511 typesTableRow5, 512 }, 513 }, doltIndexTestCase{ 514 typesTest.indexName, 515 typesTest.secondValue, 516 []sql.Row{ 517 typesTableRow2, 518 typesTableRow3, 519 typesTableRow4, 520 typesTableRow5, 521 }, 522 }, doltIndexTestCase{ 523 typesTest.indexName, 524 typesTest.thirdValue, 525 []sql.Row{ 526 typesTableRow3, 527 typesTableRow4, 528 typesTableRow5, 529 }, 530 }, doltIndexTestCase{ 531 typesTest.indexName, 532 typesTest.fourthValue, 533 []sql.Row{ 534 typesTableRow4, 535 typesTableRow5, 536 }, 537 }, doltIndexTestCase{ 538 typesTest.indexName, 539 typesTest.fifthValue, 540 []sql.Row{ 541 typesTableRow5, 542 }, 543 }, doltIndexTestCase{ 544 typesTest.indexName, 545 typesTest.abovefifthValue, 546 nil, 547 }) 548 } 549 550 for _, test := range tests { 551 t.Run(fmt.Sprintf("%s|%v", test.indexName, test.keys), func(t *testing.T) { 552 index, ok := indexMap[test.indexName] 553 require.True(t, ok) 554 testDoltIndex(t, test.keys, test.expectedRows, index.AscendGreaterOrEqual) 555 }) 556 } 557 } 558 559 func TestDoltIndexLessThan(t *testing.T) { 560 indexMap := doltIndexSetup(t) 561 562 tests := []struct { 563 indexName string 564 keys []interface{} 565 expectedRows []sql.Row 566 }{ 567 { 568 "onepk:primaryKey", 569 []interface{}{1}, 570 nil, 571 }, 572 { 573 "onepk:primaryKey", 574 []interface{}{3}, 575 []sql.Row{{2, 1, 2}, {1, 1, 1}}, 576 }, 577 { 578 "onepk:primaryKey", 579 []interface{}{4}, 580 []sql.Row{{3, 3, 3}, {2, 1, 2}, {1, 1, 1}}, 581 }, 582 { 583 "onepk:idx_v1", 584 []interface{}{1}, 585 nil, 586 }, 587 { 588 "onepk:idx_v1", 589 []interface{}{3}, 590 []sql.Row{{2, 1, 2}, {1, 1, 1}}, 591 }, 592 { 593 "onepk:idx_v1", 594 []interface{}{4}, 595 []sql.Row{{3, 3, 3}, {2, 1, 2}, {1, 1, 1}}, 596 }, 597 { 598 "twopk:primaryKey", 599 []interface{}{1, 1}, 600 nil, 601 }, 602 { 603 "twopk:primaryKey", 604 []interface{}{2, 1}, 605 []sql.Row{{1, 2, 3, 4}, {1, 1, 3, 3}}, 606 }, 607 { 608 "twopk:idx_v2v1", 609 []interface{}{3, 4}, 610 []sql.Row{{1, 1, 3, 3}}, 611 }, 612 { 613 "twopk:idx_v2v1", 614 []interface{}{4, 3}, 615 []sql.Row{{2, 2, 4, 3}, {1, 1, 3, 3}}, 616 }, 617 { 618 "twopk:idx_v2v1_PARTIAL_1", 619 []interface{}{3}, 620 nil, 621 }, 622 { 623 "twopk:idx_v2v1_PARTIAL_1", 624 []interface{}{4}, 625 []sql.Row{{2, 2, 4, 3}, {1, 1, 3, 3}}, 626 }, 627 } 628 629 for _, typesTest := range typesTests { 630 tests = append(tests, doltIndexTestCase{ 631 typesTest.indexName, 632 typesTest.belowfirstValue, 633 nil, 634 }, doltIndexTestCase{ 635 typesTest.indexName, 636 typesTest.firstValue, 637 nil, 638 }, doltIndexTestCase{ 639 typesTest.indexName, 640 typesTest.secondValue, 641 []sql.Row{ 642 typesTableRow1, 643 }, 644 }, doltIndexTestCase{ 645 typesTest.indexName, 646 typesTest.thirdValue, 647 []sql.Row{ 648 typesTableRow2, 649 typesTableRow1, 650 }, 651 }, doltIndexTestCase{ 652 typesTest.indexName, 653 typesTest.fourthValue, 654 []sql.Row{ 655 typesTableRow3, 656 typesTableRow2, 657 typesTableRow1, 658 }, 659 }, doltIndexTestCase{ 660 typesTest.indexName, 661 typesTest.fifthValue, 662 []sql.Row{ 663 typesTableRow4, 664 typesTableRow3, 665 typesTableRow2, 666 typesTableRow1, 667 }, 668 }, doltIndexTestCase{ 669 typesTest.indexName, 670 typesTest.abovefifthValue, 671 []sql.Row{ 672 typesTableRow5, 673 typesTableRow4, 674 typesTableRow3, 675 typesTableRow2, 676 typesTableRow1, 677 }, 678 }) 679 } 680 681 for _, test := range tests { 682 t.Run(fmt.Sprintf("%s|%v", test.indexName, test.keys), func(t *testing.T) { 683 index, ok := indexMap[test.indexName] 684 require.True(t, ok) 685 testDoltIndex(t, test.keys, test.expectedRows, index.AscendLessThan) 686 }) 687 } 688 } 689 690 func TestDoltIndexLessThanOrEqual(t *testing.T) { 691 indexMap := doltIndexSetup(t) 692 693 tests := []struct { 694 indexName string 695 keys []interface{} 696 expectedRows []sql.Row 697 }{ 698 { 699 "onepk:primaryKey", 700 []interface{}{1}, 701 []sql.Row{{1, 1, 1}}, 702 }, 703 { 704 "onepk:primaryKey", 705 []interface{}{3}, 706 []sql.Row{{3, 3, 3}, {2, 1, 2}, {1, 1, 1}}, 707 }, 708 { 709 "onepk:primaryKey", 710 []interface{}{4}, 711 []sql.Row{{4, 4, 3}, {3, 3, 3}, {2, 1, 2}, {1, 1, 1}}, 712 }, 713 { 714 "onepk:idx_v1", 715 []interface{}{1}, 716 []sql.Row{{2, 1, 2}, {1, 1, 1}}, 717 }, 718 { 719 "onepk:idx_v1", 720 []interface{}{3}, 721 []sql.Row{{3, 3, 3}, {2, 1, 2}, {1, 1, 1}}, 722 }, 723 { 724 "onepk:idx_v1", 725 []interface{}{4}, 726 []sql.Row{{4, 4, 3}, {3, 3, 3}, {2, 1, 2}, {1, 1, 1}}, 727 }, 728 { 729 "twopk:primaryKey", 730 []interface{}{1, 1}, 731 []sql.Row{{1, 1, 3, 3}}, 732 }, 733 { 734 "twopk:primaryKey", 735 []interface{}{2, 1}, 736 []sql.Row{{2, 1, 4, 4}, {1, 2, 3, 4}, {1, 1, 3, 3}}, 737 }, 738 { 739 "twopk:idx_v2v1", 740 []interface{}{3, 4}, 741 []sql.Row{{2, 2, 4, 3}, {1, 1, 3, 3}}, 742 }, 743 { 744 "twopk:idx_v2v1", 745 []interface{}{4, 3}, 746 []sql.Row{{1, 2, 3, 4}, {2, 2, 4, 3}, {1, 1, 3, 3}}, 747 }, 748 { 749 "twopk:idx_v2v1_PARTIAL_1", 750 []interface{}{3}, 751 []sql.Row{{1, 1, 3, 3}, {2, 2, 4, 3}}, 752 }, 753 { 754 "twopk:idx_v2v1_PARTIAL_1", 755 []interface{}{4}, 756 []sql.Row{{1, 1, 3, 3}, {1, 2, 3, 4}, {2, 1, 4, 4}, {2, 2, 4, 3}}, 757 }, 758 } 759 760 for _, typesTest := range typesTests { 761 tests = append(tests, doltIndexTestCase{ 762 typesTest.indexName, 763 typesTest.belowfirstValue, 764 nil, 765 }, doltIndexTestCase{ 766 typesTest.indexName, 767 typesTest.firstValue, 768 []sql.Row{ 769 typesTableRow1, 770 }, 771 }, doltIndexTestCase{ 772 typesTest.indexName, 773 typesTest.secondValue, 774 []sql.Row{ 775 typesTableRow2, 776 typesTableRow1, 777 }, 778 }, doltIndexTestCase{ 779 typesTest.indexName, 780 typesTest.thirdValue, 781 []sql.Row{ 782 typesTableRow3, 783 typesTableRow2, 784 typesTableRow1, 785 }, 786 }, doltIndexTestCase{ 787 typesTest.indexName, 788 typesTest.fourthValue, 789 []sql.Row{ 790 typesTableRow4, 791 typesTableRow3, 792 typesTableRow2, 793 typesTableRow1, 794 }, 795 }, doltIndexTestCase{ 796 typesTest.indexName, 797 typesTest.fifthValue, 798 []sql.Row{ 799 typesTableRow5, 800 typesTableRow4, 801 typesTableRow3, 802 typesTableRow2, 803 typesTableRow1, 804 }, 805 }, doltIndexTestCase{ 806 typesTest.indexName, 807 typesTest.abovefifthValue, 808 []sql.Row{ 809 typesTableRow5, 810 typesTableRow4, 811 typesTableRow3, 812 typesTableRow2, 813 typesTableRow1, 814 }, 815 }) 816 } 817 818 for _, test := range tests { 819 t.Run(fmt.Sprintf("%s|%v", test.indexName, test.keys), func(t *testing.T) { 820 index, ok := indexMap[test.indexName] 821 require.True(t, ok) 822 testDoltIndex(t, test.keys, test.expectedRows, index.DescendLessOrEqual) 823 }) 824 } 825 } 826 827 func TestDoltIndexBetween(t *testing.T) { 828 indexMap := doltIndexSetup(t) 829 830 tests := []doltIndexBetweenTestCase{ 831 { 832 "onepk:primaryKey", 833 []interface{}{1}, 834 []interface{}{2}, 835 []sql.Row{{1, 1, 1}, {2, 1, 2}}, 836 }, 837 { 838 "onepk:primaryKey", 839 []interface{}{3}, 840 []interface{}{3}, 841 []sql.Row{{3, 3, 3}}, 842 }, 843 { 844 "onepk:primaryKey", 845 []interface{}{4}, 846 []interface{}{6}, 847 []sql.Row{{4, 4, 3}}, 848 }, 849 { 850 "onepk:primaryKey", 851 []interface{}{0}, 852 []interface{}{10}, 853 []sql.Row{{1, 1, 1}, {2, 1, 2}, {3, 3, 3}, {4, 4, 3}}, 854 }, 855 { 856 "onepk:idx_v1", 857 []interface{}{1}, 858 []interface{}{2}, 859 []sql.Row{{1, 1, 1}, {2, 1, 2}}, 860 }, 861 { 862 "onepk:idx_v1", 863 []interface{}{2}, 864 []interface{}{4}, 865 []sql.Row{{3, 3, 3}, {4, 4, 3}}, 866 }, 867 { 868 "onepk:idx_v1", 869 []interface{}{1}, 870 []interface{}{4}, 871 []sql.Row{{1, 1, 1}, {2, 1, 2}, {3, 3, 3}, {4, 4, 3}}, 872 }, 873 { 874 "twopk:primaryKey", 875 []interface{}{1, 1}, 876 []interface{}{1, 1}, 877 []sql.Row{{1, 1, 3, 3}}, 878 }, 879 { 880 "twopk:primaryKey", 881 []interface{}{1, 1}, 882 []interface{}{2, 1}, 883 []sql.Row{{1, 1, 3, 3}, {1, 2, 3, 4}, {2, 1, 4, 4}}, 884 }, 885 { 886 "twopk:primaryKey", 887 []interface{}{1, 1}, 888 []interface{}{2, 5}, 889 []sql.Row{{1, 1, 3, 3}, {1, 2, 3, 4}, {2, 1, 4, 4}, {2, 2, 4, 3}}, 890 }, 891 { 892 "twopk:idx_v2v1", 893 []interface{}{3, 3}, 894 []interface{}{3, 4}, 895 []sql.Row{{1, 1, 3, 3}, {2, 2, 4, 3}}, 896 }, 897 { 898 "twopk:idx_v2v1", 899 []interface{}{3, 4}, 900 []interface{}{4, 4}, 901 []sql.Row{{2, 2, 4, 3}, {1, 2, 3, 4}, {2, 1, 4, 4}}, 902 }, 903 { 904 "twopk:idx_v2v1_PARTIAL_1", 905 []interface{}{3}, 906 []interface{}{3}, 907 []sql.Row{{1, 1, 3, 3}, {2, 2, 4, 3}}, 908 }, 909 { 910 "twopk:idx_v2v1_PARTIAL_1", 911 []interface{}{4}, 912 []interface{}{4}, 913 []sql.Row{{1, 2, 3, 4}, {2, 1, 4, 4}}, 914 }, 915 { 916 "twopk:idx_v2v1_PARTIAL_1", 917 []interface{}{3}, 918 []interface{}{4}, 919 []sql.Row{{1, 1, 3, 3}, {1, 2, 3, 4}, {2, 1, 4, 4}, {2, 2, 4, 3}}, 920 }, 921 } 922 923 for _, typesTest := range typesTests { 924 tests = append(tests, doltIndexBetweenTestCase{ 925 typesTest.indexName, 926 typesTest.belowfirstValue, 927 typesTest.belowfirstValue, 928 nil, 929 }, doltIndexBetweenTestCase{ 930 typesTest.indexName, 931 typesTest.belowfirstValue, 932 typesTest.firstValue, 933 []sql.Row{ 934 typesTableRow1, 935 }, 936 }, doltIndexBetweenTestCase{ 937 typesTest.indexName, 938 typesTest.belowfirstValue, 939 typesTest.secondValue, 940 []sql.Row{ 941 typesTableRow1, 942 typesTableRow2, 943 }, 944 }, doltIndexBetweenTestCase{ 945 typesTest.indexName, 946 typesTest.belowfirstValue, 947 typesTest.thirdValue, 948 []sql.Row{ 949 typesTableRow1, 950 typesTableRow2, 951 typesTableRow3, 952 }, 953 }, doltIndexBetweenTestCase{ 954 typesTest.indexName, 955 typesTest.secondValue, 956 typesTest.secondValue, 957 []sql.Row{ 958 typesTableRow2, 959 }, 960 }, doltIndexBetweenTestCase{ 961 typesTest.indexName, 962 typesTest.thirdValue, 963 typesTest.fifthValue, 964 []sql.Row{ 965 typesTableRow3, 966 typesTableRow4, 967 typesTableRow5, 968 }, 969 }, doltIndexBetweenTestCase{ 970 typesTest.indexName, 971 typesTest.fifthValue, 972 typesTest.abovefifthValue, 973 []sql.Row{ 974 typesTableRow5, 975 }, 976 }, doltIndexBetweenTestCase{ 977 typesTest.indexName, 978 typesTest.abovefifthValue, 979 typesTest.abovefifthValue, 980 nil, 981 }) 982 } 983 984 for _, test := range tests { 985 t.Run(fmt.Sprintf("%s|%v%v", test.indexName, test.greaterThanOrEqual, test.lessThanOrEqual), func(t *testing.T) { 986 index, ok := indexMap[test.indexName] 987 require.True(t, ok) 988 989 expectedRows := convertSqlRowToInt64(test.expectedRows) 990 991 indexLookup, err := index.AscendRange(test.greaterThanOrEqual, test.lessThanOrEqual) 992 require.NoError(t, err) 993 dil, ok := indexLookup.(*doltIndexLookup) 994 require.True(t, ok) 995 indexIter, err := dil.RowIter(NewTestSQLCtx(context.Background()), dil.IndexRowData(), nil) 996 require.NoError(t, err) 997 998 var readRows []sql.Row 999 var nextRow sql.Row 1000 for nextRow, err = indexIter.Next(); err == nil; nextRow, err = indexIter.Next() { 1001 readRows = append(readRows, nextRow) 1002 } 1003 require.Equal(t, io.EOF, err) 1004 1005 requireUnorderedRowsEqual(t, expectedRows, readRows) 1006 1007 indexLookup, err = index.DescendRange(test.lessThanOrEqual, test.greaterThanOrEqual) 1008 require.NoError(t, err) 1009 dil, ok = indexLookup.(*doltIndexLookup) 1010 require.True(t, ok) 1011 indexIter, err = dil.RowIter(NewTestSQLCtx(context.Background()), dil.IndexRowData(), nil) 1012 require.NoError(t, err) 1013 1014 readRows = nil 1015 for nextRow, err = indexIter.Next(); err == nil; nextRow, err = indexIter.Next() { 1016 readRows = append(readRows, nextRow) 1017 } 1018 require.Equal(t, io.EOF, err) 1019 1020 requireUnorderedRowsEqual(t, expectedRows, readRows) 1021 }) 1022 } 1023 } 1024 1025 type rowSlice struct { 1026 rows []sql.Row 1027 sortErr error 1028 } 1029 1030 func (r *rowSlice) setSortErr(err error) { 1031 if err == nil || r.sortErr != nil { 1032 return 1033 } 1034 1035 r.sortErr = err 1036 } 1037 1038 func (r *rowSlice) Len() int { 1039 return len(r.rows) 1040 } 1041 1042 func (r *rowSlice) Less(i, j int) bool { 1043 r1 := r.rows[i] 1044 r2 := r.rows[j] 1045 1046 longerLen := len(r1) 1047 if len(r2) > longerLen { 1048 longerLen = len(r2) 1049 } 1050 1051 for pos := 0; pos < longerLen; pos++ { 1052 if pos == len(r1) { 1053 return true 1054 } 1055 1056 if pos == len(r2) { 1057 return false 1058 } 1059 1060 c1, c2 := r1[pos], r2[pos] 1061 1062 var cmp int 1063 var err error 1064 switch typedVal := c1.(type) { 1065 case int: 1066 cmp, err = signedCompare(int64(typedVal), c2) 1067 case int64: 1068 cmp, err = signedCompare(typedVal, c2) 1069 case int32: 1070 cmp, err = signedCompare(int64(typedVal), c2) 1071 case int16: 1072 cmp, err = signedCompare(int64(typedVal), c2) 1073 case int8: 1074 cmp, err = signedCompare(int64(typedVal), c2) 1075 1076 case uint: 1077 cmp, err = unsignedCompare(uint64(typedVal), c2) 1078 case uint64: 1079 cmp, err = unsignedCompare(typedVal, c2) 1080 case uint32: 1081 cmp, err = unsignedCompare(uint64(typedVal), c2) 1082 case uint16: 1083 cmp, err = unsignedCompare(uint64(typedVal), c2) 1084 case uint8: 1085 cmp, err = unsignedCompare(uint64(typedVal), c2) 1086 1087 case float64: 1088 cmp, err = floatCompare(float64(typedVal), c2) 1089 case float32: 1090 cmp, err = floatCompare(float64(typedVal), c2) 1091 1092 case string: 1093 cmp, err = stringCompare(typedVal, c2) 1094 1095 default: 1096 panic("not implemented please add") 1097 } 1098 1099 if err != nil { 1100 r.setSortErr(err) 1101 return false 1102 } 1103 1104 if cmp != 0 { 1105 return cmp < 0 1106 } 1107 } 1108 1109 // equal 1110 return false 1111 } 1112 1113 func signedCompare(n1 int64, c interface{}) (int, error) { 1114 var n2 int64 1115 switch typedVal := c.(type) { 1116 case int: 1117 n2 = int64(typedVal) 1118 case int64: 1119 n2 = typedVal 1120 case int32: 1121 n2 = int64(typedVal) 1122 case int16: 1123 n2 = int64(typedVal) 1124 case int8: 1125 n2 = int64(typedVal) 1126 default: 1127 return 0, errors.New("comparing rows with different schemas") 1128 } 1129 1130 return int(n1 - n2), nil 1131 } 1132 1133 func unsignedCompare(n1 uint64, c interface{}) (int, error) { 1134 var n2 uint64 1135 switch typedVal := c.(type) { 1136 case uint: 1137 n2 = uint64(typedVal) 1138 case uint64: 1139 n2 = typedVal 1140 case uint32: 1141 n2 = uint64(typedVal) 1142 case uint16: 1143 n2 = uint64(typedVal) 1144 case uint8: 1145 n2 = uint64(typedVal) 1146 default: 1147 return 0, errors.New("comparing rows with different schemas") 1148 } 1149 1150 if n1 == n2 { 1151 return 0, nil 1152 } else if n1 < n2 { 1153 return -1, nil 1154 } else { 1155 return 1, nil 1156 } 1157 } 1158 1159 func floatCompare(n1 float64, c interface{}) (int, error) { 1160 var n2 float64 1161 switch typedVal := c.(type) { 1162 case float32: 1163 n2 = float64(typedVal) 1164 case float64: 1165 n2 = typedVal 1166 default: 1167 return 0, errors.New("comparing rows with different schemas") 1168 } 1169 1170 if n1 == n2 { 1171 return 0, nil 1172 } else if n1 < n2 { 1173 return -1, nil 1174 } else { 1175 return 1, nil 1176 } 1177 } 1178 1179 func stringCompare(s1 string, c interface{}) (int, error) { 1180 s2, ok := c.(string) 1181 if !ok { 1182 return 0, errors.New("comparing rows with different schemas") 1183 } 1184 1185 return strings.Compare(s1, s2), nil 1186 } 1187 1188 func (r *rowSlice) Swap(i, j int) { 1189 r.rows[i], r.rows[j] = r.rows[j], r.rows[i] 1190 } 1191 1192 func requireUnorderedRowsEqual(t *testing.T, rows1, rows2 []sql.Row) { 1193 slice1 := &rowSlice{rows: rows1} 1194 sort.Stable(slice1) 1195 require.NoError(t, slice1.sortErr) 1196 1197 slice2 := &rowSlice{rows: rows2} 1198 sort.Stable(slice2) 1199 require.NoError(t, slice2.sortErr) 1200 1201 require.Equal(t, rows1, rows2) 1202 } 1203 1204 func testDoltIndex(t *testing.T, keys []interface{}, expectedRows []sql.Row, indexLookupFn func(keys ...interface{}) (sql.IndexLookup, error)) { 1205 indexLookup, err := indexLookupFn(keys...) 1206 require.NoError(t, err) 1207 dil, ok := indexLookup.(*doltIndexLookup) 1208 require.True(t, ok) 1209 indexIter, err := dil.RowIter(NewTestSQLCtx(context.Background()), dil.IndexRowData(), nil) 1210 require.NoError(t, err) 1211 1212 var readRows []sql.Row 1213 var nextRow sql.Row 1214 for nextRow, err = indexIter.Next(); err == nil; nextRow, err = indexIter.Next() { 1215 readRows = append(readRows, nextRow) 1216 } 1217 require.Equal(t, io.EOF, err) 1218 1219 requireUnorderedRowsEqual(t, convertSqlRowToInt64(expectedRows), readRows) 1220 } 1221 1222 func doltIndexSetup(t *testing.T) map[string]DoltIndex { 1223 ctx := NewTestSQLCtx(context.Background()) 1224 dEnv := dtestutils.CreateTestEnv() 1225 db := NewDatabase("dolt", dEnv.DbData()) 1226 root, err := dEnv.WorkingRoot(ctx) 1227 if err != nil { 1228 panic(err) 1229 } 1230 root, err = ExecuteSql(dEnv, root, ` 1231 CREATE TABLE onepk ( 1232 pk1 BIGINT PRIMARY KEY, 1233 v1 BIGINT, 1234 v2 BIGINT 1235 ); 1236 CREATE TABLE twopk ( 1237 pk1 BIGINT, 1238 pk2 BIGINT, 1239 v1 BIGINT, 1240 v2 BIGINT, 1241 PRIMARY KEY (pk1,pk2) 1242 ); 1243 CREATE TABLE types ( 1244 pk1 MEDIUMINT PRIMARY KEY, 1245 v1 BIT(4), 1246 v2 DATETIME, 1247 v3 DECIMAL(10, 5), 1248 v4 ENUM('_', 'a', 'b', 'c', 'd', 'e', '.'), 1249 v5 DOUBLE, 1250 v6 SET('a', 'b', 'c'), 1251 v7 TIME, 1252 v8 VARCHAR(2), 1253 v9 YEAR 1254 ); 1255 CREATE INDEX idx_v1 ON onepk(v1); 1256 CREATE INDEX idx_v2v1 ON twopk(v2, v1); 1257 CREATE INDEX idx_bit ON types(v1); 1258 CREATE INDEX idx_datetime ON types(v2); 1259 CREATE INDEX idx_decimal ON types(v3); 1260 CREATE INDEX idx_enum ON types(v4); 1261 CREATE INDEX idx_double ON types(v5); 1262 CREATE INDEX idx_set ON types(v6); 1263 CREATE INDEX idx_time ON types(v7); 1264 CREATE INDEX idx_varchar ON types(v8); 1265 CREATE INDEX idx_year ON types(v9); 1266 INSERT INTO onepk VALUES (1, 1, 1), (2, 1, 2), (3, 3, 3), (4, 4, 3); 1267 INSERT INTO twopk VALUES (1, 1, 3, 3), (1, 2, 3, 4), (2, 1, 4, 4), (2, 2, 4, 3); 1268 INSERT INTO types VALUES (-3, 1, '2020-05-14 12:00:00', -3.3, 'a', -3.3, 'a', '-00:03:03', 'a', 1980); 1269 INSERT INTO types VALUES (3, 5, '2020-05-14 12:00:04', 3.3, 'e', 3.3, 'b,c', '00:03:03', 'e', 2020); 1270 INSERT INTO types VALUES (0, 3, '2020-05-14 12:00:02', 0.0, 'c', 0.0, 'c', '00:00:00', 'c', 2000); 1271 INSERT INTO types VALUES (-1, 2, '2020-05-14 12:00:01', -1.1, 'b', -1.1, 'a,b', '-00:01:01', 'b', 1990); 1272 INSERT INTO types VALUES (1, 4, '2020-05-14 12:00:03', 1.1, 'd', 1.1, 'a,c', '00:01:01', 'd', 2010); 1273 `) 1274 require.NoError(t, err) 1275 1276 tableMap := make(map[string]*doltdb.Table) 1277 tableDataMap := make(map[string]types.Map) 1278 tableSchemaMap := make(map[string]schema.Schema) 1279 1280 var ok bool 1281 tableMap["onepk"], ok, err = root.GetTable(ctx, "onepk") 1282 require.NoError(t, err) 1283 require.True(t, ok) 1284 tableSchemaMap["onepk"], err = tableMap["onepk"].GetSchema(ctx) 1285 require.NoError(t, err) 1286 tableDataMap["onepk"], err = tableMap["onepk"].GetRowData(ctx) 1287 require.NoError(t, err) 1288 1289 tableMap["twopk"], ok, err = root.GetTable(ctx, "twopk") 1290 require.NoError(t, err) 1291 require.True(t, ok) 1292 tableSchemaMap["twopk"], err = tableMap["twopk"].GetSchema(ctx) 1293 require.NoError(t, err) 1294 tableDataMap["twopk"], err = tableMap["twopk"].GetRowData(ctx) 1295 require.NoError(t, err) 1296 1297 tableMap["types"], ok, err = root.GetTable(ctx, "types") 1298 require.NoError(t, err) 1299 require.True(t, ok) 1300 tableSchemaMap["types"], err = tableMap["types"].GetSchema(ctx) 1301 require.NoError(t, err) 1302 tableDataMap["types"], err = tableMap["types"].GetRowData(ctx) 1303 require.NoError(t, err) 1304 1305 indexMap := map[string]DoltIndex{ 1306 "onepk:primaryKey": &doltIndex{ 1307 cols: tableSchemaMap["onepk"].GetPKCols().GetColumns(), 1308 db: db, 1309 id: "onepk:primaryKey", 1310 indexRowData: tableDataMap["onepk"], 1311 indexSch: tableSchemaMap["onepk"], 1312 table: tableMap["onepk"], 1313 tableData: tableDataMap["onepk"], 1314 tableName: "onepk", 1315 tableSch: tableSchemaMap["onepk"], 1316 }, 1317 "twopk:primaryKey": &doltIndex{ 1318 cols: tableSchemaMap["twopk"].GetPKCols().GetColumns(), 1319 db: db, 1320 id: "twopk:primaryKey", 1321 indexRowData: tableDataMap["twopk"], 1322 indexSch: tableSchemaMap["twopk"], 1323 table: tableMap["twopk"], 1324 tableData: tableDataMap["twopk"], 1325 tableName: "twopk", 1326 tableSch: tableSchemaMap["twopk"], 1327 }, 1328 "types:primaryKey": &doltIndex{ 1329 cols: tableSchemaMap["types"].GetPKCols().GetColumns(), 1330 db: db, 1331 id: "types:primaryKey", 1332 indexRowData: tableDataMap["types"], 1333 indexSch: tableSchemaMap["types"], 1334 table: tableMap["types"], 1335 tableData: tableDataMap["types"], 1336 tableName: "types", 1337 tableSch: tableSchemaMap["types"], 1338 }, 1339 } 1340 1341 for _, indexDetails := range []struct { 1342 indexName string 1343 tableName string 1344 }{ 1345 { 1346 "idx_v1", 1347 "onepk", 1348 }, 1349 { 1350 "idx_v2v1", 1351 "twopk", 1352 }, 1353 { 1354 "idx_bit", 1355 "types", 1356 }, 1357 { 1358 "idx_datetime", 1359 "types", 1360 }, 1361 { 1362 "idx_decimal", 1363 "types", 1364 }, 1365 { 1366 "idx_enum", 1367 "types", 1368 }, 1369 { 1370 "idx_double", 1371 "types", 1372 }, 1373 { 1374 "idx_set", 1375 "types", 1376 }, 1377 { 1378 "idx_time", 1379 "types", 1380 }, 1381 { 1382 "idx_varchar", 1383 "types", 1384 }, 1385 { 1386 "idx_year", 1387 "types", 1388 }, 1389 } { 1390 index := tableSchemaMap[indexDetails.tableName].Indexes().GetByName(indexDetails.indexName) 1391 indexData, err := tableMap[indexDetails.tableName].GetIndexRowData(ctx, index.Name()) 1392 require.NoError(t, err) 1393 indexCols := make([]schema.Column, index.Count()) 1394 for i, tag := range index.IndexedColumnTags() { 1395 indexCols[i], _ = index.GetColumn(tag) 1396 } 1397 1398 indexId := indexDetails.tableName + ":" + index.Name() 1399 indexMap[indexId] = &doltIndex{ 1400 cols: indexCols, 1401 db: db, 1402 id: indexId, 1403 indexRowData: indexData, 1404 indexSch: index.Schema(), 1405 table: tableMap[indexDetails.tableName], 1406 tableData: tableDataMap[indexDetails.tableName], 1407 tableName: indexDetails.tableName, 1408 tableSch: tableSchemaMap[indexDetails.tableName], 1409 } 1410 for i := 1; i < len(indexCols); i++ { 1411 indexId := fmt.Sprintf("%s:%s_PARTIAL_%d", indexDetails.tableName, index.Name(), i) 1412 indexMap[indexId] = &doltIndex{ 1413 cols: indexCols[:i], 1414 db: db, 1415 id: indexId, 1416 indexRowData: indexData, 1417 indexSch: index.Schema(), 1418 table: tableMap[indexDetails.tableName], 1419 tableData: tableDataMap[indexDetails.tableName], 1420 tableName: indexDetails.tableName, 1421 tableSch: tableSchemaMap[indexDetails.tableName], 1422 generated: true, 1423 } 1424 } 1425 } 1426 1427 return indexMap 1428 } 1429 1430 func forceParseTime(timeString string) time.Time { 1431 tim, _ := time.Parse("2006-01-02 15:04:05", timeString) 1432 return tim 1433 }