github.com/mithrandie/csvq@v1.18.1/lib/query/join_test.go (about) 1 package query 2 3 import ( 4 "context" 5 "reflect" 6 "testing" 7 8 "github.com/mithrandie/csvq/lib/parser" 9 "github.com/mithrandie/csvq/lib/value" 10 ) 11 12 func naturalJoinTestFieldReference(view string, column string) parser.FieldReference { 13 return parser.FieldReference{ 14 BaseExpr: parser.NewBaseExpr(parser.Token{}), 15 View: parser.Identifier{Literal: view}, 16 Column: parser.Identifier{BaseExpr: parser.NewBaseExpr(parser.Token{}), Literal: column}, 17 } 18 } 19 20 func joinUsingTestFieldReference(view string, column string) parser.FieldReference { 21 return parser.FieldReference{ 22 View: parser.Identifier{Literal: view}, 23 Column: parser.Identifier{Literal: column}, 24 } 25 } 26 27 var parseJoinConditionTests = []struct { 28 Name string 29 Join parser.Join 30 View *View 31 JoinView *View 32 ResultValue parser.QueryExpression 33 IncludeFields []parser.FieldReference 34 ExcludeFields []parser.FieldReference 35 Error string 36 }{ 37 { 38 Name: "No Condition", 39 Join: parser.Join{ 40 Table: parser.Table{Alias: parser.Identifier{Literal: "t1"}}, 41 JoinTable: parser.Table{Alias: parser.Identifier{Literal: "t2"}}, 42 }, 43 View: &View{Header: NewHeaderWithId("table1", []string{"key1", "key2", "key3", "value1", "value2", "value3"})}, 44 JoinView: &View{Header: NewHeaderWithId("table2", []string{"key1", "key2", "key3", "value4"})}, 45 ResultValue: nil, 46 }, 47 { 48 Name: "Natural Join", 49 Join: parser.Join{ 50 Table: parser.Table{Alias: parser.Identifier{Literal: "t1"}}, 51 JoinTable: parser.Table{Alias: parser.Identifier{Literal: "t2"}}, 52 Natural: parser.Token{Token: parser.NATURAL, Literal: "natural"}, 53 }, 54 View: &View{Header: NewHeaderWithId("t1", []string{"key1", "key2", "key3", "value1", "value2", "value3"})}, 55 JoinView: &View{Header: NewHeaderWithId("t2", []string{"key1", "key2", "key3", "value4"})}, 56 ResultValue: parser.Logic{ 57 LHS: parser.Logic{ 58 LHS: parser.Comparison{ 59 LHS: naturalJoinTestFieldReference("t1", "key1"), 60 RHS: naturalJoinTestFieldReference("t2", "key1"), 61 Operator: parser.Token{Token: parser.COMPARISON_OP, Literal: "="}, 62 }, 63 RHS: parser.Comparison{ 64 LHS: naturalJoinTestFieldReference("t1", "key2"), 65 RHS: naturalJoinTestFieldReference("t2", "key2"), 66 Operator: parser.Token{Token: parser.COMPARISON_OP, Literal: "="}, 67 }, 68 Operator: parser.Token{Token: parser.AND, Literal: "AND"}, 69 }, 70 RHS: parser.Comparison{ 71 LHS: naturalJoinTestFieldReference("t1", "key3"), 72 RHS: naturalJoinTestFieldReference("t2", "key3"), 73 Operator: parser.Token{Token: parser.COMPARISON_OP, Literal: "="}, 74 }, 75 Operator: parser.Token{Token: parser.AND, Literal: "AND"}, 76 }, 77 IncludeFields: []parser.FieldReference{ 78 naturalJoinTestFieldReference("t1", "key1"), 79 naturalJoinTestFieldReference("t1", "key2"), 80 naturalJoinTestFieldReference("t1", "key3"), 81 }, 82 ExcludeFields: []parser.FieldReference{ 83 naturalJoinTestFieldReference("t2", "key1"), 84 naturalJoinTestFieldReference("t2", "key2"), 85 naturalJoinTestFieldReference("t2", "key3"), 86 }, 87 }, 88 { 89 Name: "Using Condition", 90 Join: parser.Join{ 91 Table: parser.Table{Alias: parser.Identifier{Literal: "t1"}}, 92 JoinTable: parser.Table{Alias: parser.Identifier{Literal: "t2"}}, 93 Condition: parser.JoinCondition{ 94 Using: []parser.QueryExpression{ 95 parser.Identifier{Literal: "key1"}, 96 }, 97 }, 98 }, 99 View: &View{Header: NewHeaderWithId("t1", []string{"key1", "key2", "key3", "value1", "value2", "value3"})}, 100 JoinView: &View{Header: NewHeaderWithId("t2", []string{"key1", "key2", "key3", "value4"})}, 101 ResultValue: parser.Comparison{ 102 LHS: joinUsingTestFieldReference("t1", "key1"), 103 RHS: joinUsingTestFieldReference("t2", "key1"), 104 Operator: parser.Token{Token: parser.COMPARISON_OP, Literal: "="}, 105 }, 106 IncludeFields: []parser.FieldReference{ 107 joinUsingTestFieldReference("t1", "key1"), 108 }, 109 ExcludeFields: []parser.FieldReference{ 110 joinUsingTestFieldReference("t2", "key1"), 111 }, 112 }, 113 { 114 Name: "Right Outer Join Using Condition", 115 Join: parser.Join{ 116 Table: parser.Table{Alias: parser.Identifier{Literal: "t1"}}, 117 JoinTable: parser.Table{Alias: parser.Identifier{Literal: "t2"}}, 118 JoinType: parser.Token{Token: parser.OUTER, Literal: "outer"}, 119 Direction: parser.Token{Token: parser.RIGHT, Literal: "right"}, 120 Condition: parser.JoinCondition{ 121 Using: []parser.QueryExpression{ 122 parser.Identifier{Literal: "key1"}, 123 }, 124 }, 125 }, 126 View: &View{Header: NewHeaderWithId("t1", []string{"key1", "key2", "key3", "value1", "value2", "value3"})}, 127 JoinView: &View{Header: NewHeaderWithId("t2", []string{"key1", "key2", "key3", "value4"})}, 128 ResultValue: parser.Comparison{ 129 LHS: joinUsingTestFieldReference("t1", "key1"), 130 RHS: joinUsingTestFieldReference("t2", "key1"), 131 Operator: parser.Token{Token: parser.COMPARISON_OP, Literal: "="}, 132 }, 133 IncludeFields: []parser.FieldReference{ 134 joinUsingTestFieldReference("t2", "key1"), 135 }, 136 ExcludeFields: []parser.FieldReference{ 137 joinUsingTestFieldReference("t1", "key1"), 138 }, 139 }, 140 { 141 Name: "On Condition", 142 Join: parser.Join{ 143 Table: parser.Table{Alias: parser.Identifier{Literal: "t1"}}, 144 JoinTable: parser.Table{Alias: parser.Identifier{Literal: "t2"}}, 145 Condition: parser.JoinCondition{ 146 On: parser.Comparison{ 147 LHS: parser.FieldReference{View: parser.Identifier{Literal: "t1"}, Column: parser.Identifier{Literal: "key1"}}, 148 RHS: parser.FieldReference{View: parser.Identifier{Literal: "t2"}, Column: parser.Identifier{Literal: "key1"}}, 149 Operator: parser.Token{Token: '=', Literal: "="}, 150 }, 151 }, 152 }, 153 View: &View{Header: NewHeaderWithId("table1", []string{"key1", "key2", "key3", "value1", "value2", "value3"})}, 154 JoinView: &View{Header: NewHeaderWithId("table2", []string{"key1", "key2", "key3", "value4"})}, 155 ResultValue: parser.Comparison{ 156 LHS: parser.FieldReference{View: parser.Identifier{Literal: "t1"}, Column: parser.Identifier{Literal: "key1"}}, 157 RHS: parser.FieldReference{View: parser.Identifier{Literal: "t2"}, Column: parser.Identifier{Literal: "key1"}}, 158 Operator: parser.Token{Token: '=', Literal: "="}, 159 }, 160 }, 161 { 162 Name: "Natural Join Fields Does Not Duplicate", 163 Join: parser.Join{ 164 Table: parser.Table{Alias: parser.Identifier{Literal: "t1"}}, 165 JoinTable: parser.Table{Alias: parser.Identifier{Literal: "t2"}}, 166 Natural: parser.Token{Token: parser.NATURAL, Literal: "natural"}, 167 }, 168 View: &View{Header: NewHeaderWithId("table1", []string{"value1", "value2", "value3"})}, 169 JoinView: &View{Header: NewHeaderWithId("table2", []string{"value4"})}, 170 ResultValue: nil, 171 }, 172 { 173 Name: "Using Condition View Field Error", 174 Join: parser.Join{ 175 Table: parser.Table{Alias: parser.Identifier{Literal: "t1"}}, 176 JoinTable: parser.Table{Alias: parser.Identifier{Literal: "t2"}}, 177 Condition: parser.JoinCondition{ 178 Using: []parser.QueryExpression{ 179 parser.Identifier{Literal: "key1"}, 180 }, 181 }, 182 }, 183 View: &View{Header: NewHeaderWithId("t1", []string{"key1", "key2", "key3", "key1", "value1", "value2", "value3"})}, 184 JoinView: &View{Header: NewHeaderWithId("t2", []string{"key1", "key2", "key3", "value4"})}, 185 Error: "field key1 is ambiguous", 186 }, 187 { 188 Name: "Using Condition JoinView Field Error", 189 Join: parser.Join{ 190 Table: parser.Table{Alias: parser.Identifier{Literal: "t1"}}, 191 JoinTable: parser.Table{Alias: parser.Identifier{Literal: "t2"}}, 192 Condition: parser.JoinCondition{ 193 Using: []parser.QueryExpression{ 194 parser.Identifier{Literal: "key1"}, 195 }, 196 }, 197 }, 198 View: &View{Header: NewHeaderWithId("t1", []string{"key1", "key2", "key3", "value1", "value2", "value3"})}, 199 JoinView: &View{Header: NewHeaderWithId("t2", []string{"key2", "key3", "value4"})}, 200 Error: "field key1 does not exist", 201 }, 202 } 203 204 func TestParseJoinCondition(t *testing.T) { 205 for _, v := range parseJoinConditionTests { 206 r, ifields, xfields, err := ParseJoinCondition(v.Join, v.View, v.JoinView) 207 if err != nil { 208 if len(v.Error) < 1 { 209 t.Errorf("%s: unexpected error %q", v.Name, err) 210 } else if err.Error() != v.Error { 211 t.Errorf("%s: error %q, want error %q", v.Name, err.Error(), v.Error) 212 } 213 continue 214 } 215 if 0 < len(v.Error) { 216 t.Errorf("%s: no error, want error %q", v.Name, v.Error) 217 continue 218 } 219 if !reflect.DeepEqual(r, v.ResultValue) { 220 t.Errorf("%s: condition = %q, want %q", v.Name, r, v.ResultValue) 221 } 222 if !reflect.DeepEqual(ifields, v.IncludeFields) { 223 t.Errorf("%s: include fields = %q, want %q", v.Name, ifields, v.IncludeFields) 224 } 225 if !reflect.DeepEqual(xfields, v.ExcludeFields) { 226 t.Errorf("%s: exclude fields = %q, want %q", v.Name, xfields, v.ExcludeFields) 227 } 228 } 229 } 230 231 func TestCrossJoin(t *testing.T) { 232 view := &View{ 233 Header: NewHeaderWithId("table1", []string{"column1", "column2"}), 234 RecordSet: []Record{ 235 NewRecordWithId(1, []value.Primary{ 236 value.NewInteger(1), 237 value.NewString("str1"), 238 }), 239 NewRecordWithId(2, []value.Primary{ 240 value.NewInteger(2), 241 value.NewString("str2"), 242 }), 243 }, 244 } 245 joinView := &View{ 246 Header: NewHeaderWithId("table2", []string{"column3", "column4"}), 247 RecordSet: []Record{ 248 NewRecordWithId(1, []value.Primary{ 249 value.NewInteger(3), 250 value.NewString("str3"), 251 }), 252 NewRecordWithId(2, []value.Primary{ 253 value.NewInteger(4), 254 value.NewString("str4"), 255 }), 256 }, 257 } 258 expect := &View{ 259 Header: []HeaderField{ 260 {View: "table1", Column: InternalIdColumn}, 261 {View: "table1", Column: "column1", Number: 1, IsFromTable: true}, 262 {View: "table1", Column: "column2", Number: 2, IsFromTable: true}, 263 {View: "table2", Column: InternalIdColumn}, 264 {View: "table2", Column: "column3", Number: 1, IsFromTable: true}, 265 {View: "table2", Column: "column4", Number: 2, IsFromTable: true}, 266 }, 267 RecordSet: []Record{ 268 NewRecord([]value.Primary{ 269 value.NewInteger(1), 270 value.NewInteger(1), 271 value.NewString("str1"), 272 value.NewInteger(1), 273 value.NewInteger(3), 274 value.NewString("str3"), 275 }), 276 NewRecord([]value.Primary{ 277 value.NewInteger(1), 278 value.NewInteger(1), 279 value.NewString("str1"), 280 value.NewInteger(2), 281 value.NewInteger(4), 282 value.NewString("str4"), 283 }), 284 NewRecord([]value.Primary{ 285 value.NewInteger(2), 286 value.NewInteger(2), 287 value.NewString("str2"), 288 value.NewInteger(1), 289 value.NewInteger(3), 290 value.NewString("str3"), 291 }), 292 NewRecord([]value.Primary{ 293 value.NewInteger(2), 294 value.NewInteger(2), 295 value.NewString("str2"), 296 value.NewInteger(2), 297 value.NewInteger(4), 298 value.NewString("str4"), 299 }), 300 }, 301 } 302 303 _ = CrossJoin(context.Background(), NewReferenceScope(TestTx), view, joinView) 304 if !reflect.DeepEqual(view, expect) { 305 t.Errorf("Cross Join: result = %v, want %v", view, expect) 306 } 307 } 308 309 var innerJoinTests = []struct { 310 Name string 311 CPU int 312 View *View 313 JoinView *View 314 Condition parser.QueryExpression 315 Scope *ReferenceScope 316 Result *View 317 Error string 318 }{ 319 { 320 Name: "Inner Join", 321 View: &View{ 322 Header: NewHeaderWithId("table1", []string{"column1", "column2"}), 323 RecordSet: []Record{ 324 NewRecordWithId(1, []value.Primary{ 325 value.NewInteger(1), 326 value.NewString("str1"), 327 }), 328 NewRecordWithId(2, []value.Primary{ 329 value.NewInteger(2), 330 value.NewString("str2"), 331 }), 332 NewRecordWithId(3, []value.Primary{ 333 value.NewInteger(3), 334 value.NewString("str3"), 335 }), 336 }, 337 }, 338 JoinView: &View{ 339 Header: NewHeaderWithId("table2", []string{"column1", "column3"}), 340 RecordSet: []Record{ 341 NewRecordWithId(1, []value.Primary{ 342 value.NewInteger(1), 343 value.NewString("str1"), 344 }), 345 NewRecordWithId(2, []value.Primary{ 346 value.NewInteger(2), 347 value.NewString("str22"), 348 }), 349 }, 350 }, 351 Condition: parser.Comparison{ 352 LHS: parser.FieldReference{View: parser.Identifier{Literal: "table1"}, Column: parser.Identifier{Literal: "column1"}}, 353 RHS: parser.FieldReference{View: parser.Identifier{Literal: "table2"}, Column: parser.Identifier{Literal: "column1"}}, 354 Operator: parser.Token{Token: '=', Literal: "="}, 355 }, 356 Result: &View{ 357 Header: []HeaderField{ 358 {View: "table1", Column: InternalIdColumn}, 359 {View: "table1", Column: "column1", Number: 1, IsFromTable: true}, 360 {View: "table1", Column: "column2", Number: 2, IsFromTable: true}, 361 {View: "table2", Column: InternalIdColumn}, 362 {View: "table2", Column: "column1", Number: 1, IsFromTable: true}, 363 {View: "table2", Column: "column3", Number: 2, IsFromTable: true}, 364 }, 365 RecordSet: []Record{ 366 NewRecord([]value.Primary{ 367 value.NewInteger(1), 368 value.NewInteger(1), 369 value.NewString("str1"), 370 value.NewInteger(1), 371 value.NewInteger(1), 372 value.NewString("str1"), 373 }), 374 NewRecord([]value.Primary{ 375 value.NewInteger(2), 376 value.NewInteger(2), 377 value.NewString("str2"), 378 value.NewInteger(2), 379 value.NewInteger(2), 380 value.NewString("str22"), 381 }), 382 }, 383 }, 384 }, 385 { 386 Name: "Inner Join in Multi Threading", 387 CPU: 2, 388 View: &View{ 389 Header: NewHeaderWithId("table1", []string{"column1", "column2"}), 390 RecordSet: []Record{ 391 NewRecordWithId(1, []value.Primary{ 392 value.NewInteger(1), 393 value.NewString("str1"), 394 }), 395 NewRecordWithId(2, []value.Primary{ 396 value.NewInteger(2), 397 value.NewString("str2"), 398 }), 399 NewRecordWithId(3, []value.Primary{ 400 value.NewInteger(3), 401 value.NewString("str3"), 402 }), 403 }, 404 }, 405 JoinView: &View{ 406 Header: NewHeaderWithId("table2", []string{"column1", "column3"}), 407 RecordSet: []Record{ 408 NewRecordWithId(1, []value.Primary{ 409 value.NewInteger(1), 410 value.NewString("str1"), 411 }), 412 NewRecordWithId(2, []value.Primary{ 413 value.NewInteger(2), 414 value.NewString("str22"), 415 }), 416 }, 417 }, 418 Condition: parser.Comparison{ 419 LHS: parser.FieldReference{View: parser.Identifier{Literal: "table1"}, Column: parser.Identifier{Literal: "column1"}}, 420 RHS: parser.FieldReference{View: parser.Identifier{Literal: "table2"}, Column: parser.Identifier{Literal: "column1"}}, 421 Operator: parser.Token{Token: '=', Literal: "="}, 422 }, 423 Result: &View{ 424 Header: []HeaderField{ 425 {View: "table1", Column: InternalIdColumn}, 426 {View: "table1", Column: "column1", Number: 1, IsFromTable: true}, 427 {View: "table1", Column: "column2", Number: 2, IsFromTable: true}, 428 {View: "table2", Column: InternalIdColumn}, 429 {View: "table2", Column: "column1", Number: 1, IsFromTable: true}, 430 {View: "table2", Column: "column3", Number: 2, IsFromTable: true}, 431 }, 432 RecordSet: []Record{ 433 NewRecord([]value.Primary{ 434 value.NewInteger(1), 435 value.NewInteger(1), 436 value.NewString("str1"), 437 value.NewInteger(1), 438 value.NewInteger(1), 439 value.NewString("str1"), 440 }), 441 NewRecord([]value.Primary{ 442 value.NewInteger(2), 443 value.NewInteger(2), 444 value.NewString("str2"), 445 value.NewInteger(2), 446 value.NewInteger(2), 447 value.NewString("str22"), 448 }), 449 }, 450 }, 451 }, 452 { 453 Name: "Inner Join With No Condition", 454 View: &View{ 455 Header: NewHeaderWithId("table1", []string{"column1", "column2"}), 456 RecordSet: []Record{ 457 NewRecordWithId(1, []value.Primary{ 458 value.NewInteger(1), 459 value.NewString("str1"), 460 }), 461 NewRecordWithId(2, []value.Primary{ 462 value.NewInteger(2), 463 value.NewString("str2"), 464 }), 465 }, 466 }, 467 JoinView: &View{ 468 Header: NewHeaderWithId("table2", []string{"column3", "column4"}), 469 RecordSet: []Record{ 470 NewRecordWithId(1, []value.Primary{ 471 value.NewInteger(3), 472 value.NewString("str3"), 473 }), 474 NewRecordWithId(2, []value.Primary{ 475 value.NewInteger(4), 476 value.NewString("str4"), 477 }), 478 }, 479 }, 480 Condition: nil, 481 Result: &View{ 482 Header: []HeaderField{ 483 {View: "table1", Column: InternalIdColumn}, 484 {View: "table1", Column: "column1", Number: 1, IsFromTable: true}, 485 {View: "table1", Column: "column2", Number: 2, IsFromTable: true}, 486 {View: "table2", Column: InternalIdColumn}, 487 {View: "table2", Column: "column3", Number: 1, IsFromTable: true}, 488 {View: "table2", Column: "column4", Number: 2, IsFromTable: true}, 489 }, 490 RecordSet: []Record{ 491 NewRecord([]value.Primary{ 492 value.NewInteger(1), 493 value.NewInteger(1), 494 value.NewString("str1"), 495 value.NewInteger(1), 496 value.NewInteger(3), 497 value.NewString("str3"), 498 }), 499 NewRecord([]value.Primary{ 500 value.NewInteger(1), 501 value.NewInteger(1), 502 value.NewString("str1"), 503 value.NewInteger(2), 504 value.NewInteger(4), 505 value.NewString("str4"), 506 }), 507 NewRecord([]value.Primary{ 508 value.NewInteger(2), 509 value.NewInteger(2), 510 value.NewString("str2"), 511 value.NewInteger(1), 512 value.NewInteger(3), 513 value.NewString("str3"), 514 }), 515 NewRecord([]value.Primary{ 516 value.NewInteger(2), 517 value.NewInteger(2), 518 value.NewString("str2"), 519 value.NewInteger(2), 520 value.NewInteger(4), 521 value.NewString("str4"), 522 }), 523 }, 524 }, 525 }, 526 { 527 Name: "Inner Join Filter Error", 528 View: &View{ 529 Header: NewHeaderWithId("table1", []string{"column1", "column2"}), 530 RecordSet: []Record{ 531 NewRecordWithId(1, []value.Primary{ 532 value.NewInteger(1), 533 value.NewString("str1"), 534 }), 535 NewRecordWithId(1, []value.Primary{ 536 value.NewInteger(2), 537 value.NewString("str2"), 538 }), 539 NewRecordWithId(1, []value.Primary{ 540 value.NewInteger(3), 541 value.NewString("str3"), 542 }), 543 NewRecordWithId(1, []value.Primary{ 544 value.NewInteger(4), 545 value.NewString("str4"), 546 }), 547 }, 548 }, 549 JoinView: &View{ 550 Header: NewHeaderWithId("table2", []string{"column1", "column3"}), 551 RecordSet: []Record{ 552 NewRecordWithId(1, []value.Primary{ 553 value.NewInteger(1), 554 value.NewString("str1"), 555 }), 556 }, 557 }, 558 Condition: parser.Comparison{ 559 LHS: parser.FieldReference{View: parser.Identifier{Literal: "table1"}, Column: parser.Identifier{Literal: "column1"}}, 560 RHS: parser.FieldReference{View: parser.Identifier{Literal: "table2"}, Column: parser.Identifier{Literal: "notexist"}}, 561 Operator: parser.Token{Token: '=', Literal: "="}, 562 }, 563 Error: "field table2.notexist does not exist", 564 }, 565 } 566 567 func TestInnerJoin(t *testing.T) { 568 defer initFlag(TestTx.Flags) 569 570 for _, v := range innerJoinTests { 571 TestTx.Flags.CPU = 1 572 if v.CPU != 0 { 573 TestTx.Flags.CPU = v.CPU 574 } 575 576 if v.Scope == nil { 577 v.Scope = NewReferenceScope(TestTx) 578 } 579 580 err := InnerJoin(context.Background(), v.Scope, v.View, v.JoinView, v.Condition) 581 if err != nil { 582 if len(v.Error) < 1 { 583 t.Errorf("%s: unexpected error %q", v.Name, err) 584 } else if err.Error() != v.Error { 585 t.Errorf("%s: error %q, want error %q", v.Name, err.Error(), v.Error) 586 } 587 continue 588 } 589 if 0 < len(v.Error) { 590 t.Errorf("%s: no error, want error %q", v.Name, v.Error) 591 continue 592 } 593 if !reflect.DeepEqual(v.View, v.Result) { 594 t.Errorf("%s: result = %v, want %v", v.Name, v.View, v.Result) 595 } 596 } 597 } 598 599 var outerJoinTests = []struct { 600 Name string 601 View *View 602 JoinView *View 603 Condition parser.QueryExpression 604 Direction int 605 Scope *ReferenceScope 606 Result *View 607 Error string 608 }{ 609 { 610 Name: "Left Outer Join", 611 View: &View{ 612 Header: NewHeaderWithId("table1", []string{"column1", "column2"}), 613 RecordSet: []Record{ 614 NewRecordWithId(1, []value.Primary{ 615 value.NewInteger(1), 616 value.NewString("str1"), 617 }), 618 NewRecordWithId(2, []value.Primary{ 619 value.NewInteger(2), 620 value.NewString("str2"), 621 }), 622 NewRecordWithId(3, []value.Primary{ 623 value.NewInteger(3), 624 value.NewString("str3"), 625 }), 626 }, 627 }, 628 JoinView: &View{ 629 Header: NewHeaderWithId("table2", []string{"column1", "column3"}), 630 RecordSet: []Record{ 631 NewRecordWithId(1, []value.Primary{ 632 value.NewInteger(2), 633 value.NewString("str22"), 634 }), 635 NewRecordWithId(2, []value.Primary{ 636 value.NewInteger(3), 637 value.NewString("str33"), 638 }), 639 NewRecordWithId(3, []value.Primary{ 640 value.NewInteger(4), 641 value.NewString("str44"), 642 }), 643 }, 644 }, 645 Condition: parser.Comparison{ 646 LHS: parser.FieldReference{View: parser.Identifier{Literal: "table1"}, Column: parser.Identifier{Literal: "column1"}}, 647 RHS: parser.FieldReference{View: parser.Identifier{Literal: "table2"}, Column: parser.Identifier{Literal: "column1"}}, 648 Operator: parser.Token{Token: '=', Literal: "="}, 649 }, 650 Direction: parser.LEFT, 651 Result: &View{ 652 Header: []HeaderField{ 653 {View: "table1", Column: InternalIdColumn}, 654 {View: "table1", Column: "column1", Number: 1, IsFromTable: true}, 655 {View: "table1", Column: "column2", Number: 2, IsFromTable: true}, 656 {View: "table2", Column: InternalIdColumn}, 657 {View: "table2", Column: "column1", Number: 1, IsFromTable: true}, 658 {View: "table2", Column: "column3", Number: 2, IsFromTable: true}, 659 }, 660 RecordSet: []Record{ 661 NewRecord([]value.Primary{ 662 value.NewInteger(1), 663 value.NewInteger(1), 664 value.NewString("str1"), 665 value.NewNull(), 666 value.NewNull(), 667 value.NewNull(), 668 }), 669 NewRecord([]value.Primary{ 670 value.NewInteger(2), 671 value.NewInteger(2), 672 value.NewString("str2"), 673 value.NewInteger(1), 674 value.NewInteger(2), 675 value.NewString("str22"), 676 }), 677 NewRecord([]value.Primary{ 678 value.NewInteger(3), 679 value.NewInteger(3), 680 value.NewString("str3"), 681 value.NewInteger(2), 682 value.NewInteger(3), 683 value.NewString("str33"), 684 }), 685 }, 686 }, 687 }, 688 { 689 Name: "Right Outer Join", 690 View: &View{ 691 Header: NewHeaderWithId("table1", []string{"column1", "column2"}), 692 RecordSet: []Record{ 693 NewRecordWithId(1, []value.Primary{ 694 value.NewInteger(1), 695 value.NewString("str1"), 696 }), 697 NewRecordWithId(2, []value.Primary{ 698 value.NewInteger(2), 699 value.NewString("str2"), 700 }), 701 NewRecordWithId(3, []value.Primary{ 702 value.NewInteger(3), 703 value.NewString("str3"), 704 }), 705 }, 706 }, 707 JoinView: &View{ 708 Header: NewHeaderWithId("table2", []string{"column1", "column3"}), 709 RecordSet: []Record{ 710 NewRecordWithId(1, []value.Primary{ 711 value.NewInteger(2), 712 value.NewString("str22"), 713 }), 714 NewRecordWithId(2, []value.Primary{ 715 value.NewInteger(3), 716 value.NewString("str33"), 717 }), 718 NewRecordWithId(3, []value.Primary{ 719 value.NewInteger(4), 720 value.NewString("str44"), 721 }), 722 }, 723 }, 724 Condition: parser.Comparison{ 725 LHS: parser.FieldReference{View: parser.Identifier{Literal: "table1"}, Column: parser.Identifier{Literal: "column1"}}, 726 RHS: parser.FieldReference{View: parser.Identifier{Literal: "table2"}, Column: parser.Identifier{Literal: "column1"}}, 727 Operator: parser.Token{Token: '=', Literal: "="}, 728 }, 729 Direction: parser.RIGHT, 730 Result: &View{ 731 Header: []HeaderField{ 732 {View: "table1", Column: InternalIdColumn}, 733 {View: "table1", Column: "column1", Number: 1, IsFromTable: true}, 734 {View: "table1", Column: "column2", Number: 2, IsFromTable: true}, 735 {View: "table2", Column: InternalIdColumn}, 736 {View: "table2", Column: "column1", Number: 1, IsFromTable: true}, 737 {View: "table2", Column: "column3", Number: 2, IsFromTable: true}, 738 }, 739 RecordSet: []Record{ 740 NewRecord([]value.Primary{ 741 value.NewInteger(2), 742 value.NewInteger(2), 743 value.NewString("str2"), 744 value.NewInteger(1), 745 value.NewInteger(2), 746 value.NewString("str22"), 747 }), 748 NewRecord([]value.Primary{ 749 value.NewInteger(3), 750 value.NewInteger(3), 751 value.NewString("str3"), 752 value.NewInteger(2), 753 value.NewInteger(3), 754 value.NewString("str33"), 755 }), 756 NewRecord([]value.Primary{ 757 value.NewNull(), 758 value.NewNull(), 759 value.NewNull(), 760 value.NewInteger(3), 761 value.NewInteger(4), 762 value.NewString("str44"), 763 }), 764 }, 765 }, 766 }, 767 { 768 Name: "Full Outer Join", 769 View: &View{ 770 Header: NewHeaderWithId("table1", []string{"column1", "column2"}), 771 RecordSet: []Record{ 772 NewRecordWithId(1, []value.Primary{ 773 value.NewInteger(1), 774 value.NewString("str1"), 775 }), 776 NewRecordWithId(2, []value.Primary{ 777 value.NewInteger(2), 778 value.NewString("str2"), 779 }), 780 NewRecordWithId(3, []value.Primary{ 781 value.NewInteger(3), 782 value.NewString("str3"), 783 }), 784 }, 785 }, 786 JoinView: &View{ 787 Header: NewHeaderWithId("table2", []string{"column1", "column3"}), 788 RecordSet: []Record{ 789 NewRecordWithId(1, []value.Primary{ 790 value.NewInteger(2), 791 value.NewString("str22"), 792 }), 793 NewRecordWithId(2, []value.Primary{ 794 value.NewInteger(3), 795 value.NewString("str33"), 796 }), 797 NewRecordWithId(3, []value.Primary{ 798 value.NewInteger(4), 799 value.NewString("str44"), 800 }), 801 }, 802 }, 803 Condition: parser.Comparison{ 804 LHS: parser.FieldReference{View: parser.Identifier{Literal: "table1"}, Column: parser.Identifier{Literal: "column1"}}, 805 RHS: parser.FieldReference{View: parser.Identifier{Literal: "table2"}, Column: parser.Identifier{Literal: "column1"}}, 806 Operator: parser.Token{Token: '=', Literal: "="}, 807 }, 808 Direction: parser.FULL, 809 Result: &View{ 810 Header: []HeaderField{ 811 {View: "table1", Column: InternalIdColumn}, 812 {View: "table1", Column: "column1", Number: 1, IsFromTable: true}, 813 {View: "table1", Column: "column2", Number: 2, IsFromTable: true}, 814 {View: "table2", Column: InternalIdColumn}, 815 {View: "table2", Column: "column1", Number: 1, IsFromTable: true}, 816 {View: "table2", Column: "column3", Number: 2, IsFromTable: true}, 817 }, 818 RecordSet: []Record{ 819 NewRecord([]value.Primary{ 820 value.NewInteger(1), 821 value.NewInteger(1), 822 value.NewString("str1"), 823 value.NewNull(), 824 value.NewNull(), 825 value.NewNull(), 826 }), 827 NewRecord([]value.Primary{ 828 value.NewInteger(2), 829 value.NewInteger(2), 830 value.NewString("str2"), 831 value.NewInteger(1), 832 value.NewInteger(2), 833 value.NewString("str22"), 834 }), 835 NewRecord([]value.Primary{ 836 value.NewInteger(3), 837 value.NewInteger(3), 838 value.NewString("str3"), 839 value.NewInteger(2), 840 value.NewInteger(3), 841 value.NewString("str33"), 842 }), 843 NewRecord([]value.Primary{ 844 value.NewNull(), 845 value.NewNull(), 846 value.NewNull(), 847 value.NewInteger(3), 848 value.NewInteger(4), 849 value.NewString("str44"), 850 }), 851 }, 852 }, 853 }, 854 { 855 Name: "Left Outer Join Filter Error", 856 View: &View{ 857 Header: NewHeaderWithId("table1", []string{"column1", "column2"}), 858 RecordSet: []Record{ 859 NewRecordWithId(1, []value.Primary{ 860 value.NewInteger(1), 861 value.NewString("str1"), 862 }), 863 NewRecordWithId(2, []value.Primary{ 864 value.NewInteger(2), 865 value.NewString("str2"), 866 }), 867 NewRecordWithId(3, []value.Primary{ 868 value.NewInteger(3), 869 value.NewString("str3"), 870 }), 871 }, 872 }, 873 JoinView: &View{ 874 Header: NewHeaderWithId("table2", []string{"column1", "column3"}), 875 RecordSet: []Record{ 876 NewRecordWithId(1, []value.Primary{ 877 value.NewInteger(2), 878 value.NewString("str22"), 879 }), 880 NewRecordWithId(2, []value.Primary{ 881 value.NewInteger(3), 882 value.NewString("str33"), 883 }), 884 NewRecordWithId(3, []value.Primary{ 885 value.NewInteger(4), 886 value.NewString("str44"), 887 }), 888 }, 889 }, 890 Condition: parser.Comparison{ 891 LHS: parser.FieldReference{View: parser.Identifier{Literal: "table1"}, Column: parser.Identifier{Literal: "notexist"}}, 892 RHS: parser.FieldReference{View: parser.Identifier{Literal: "table2"}, Column: parser.Identifier{Literal: "column1"}}, 893 Operator: parser.Token{Token: '=', Literal: "="}, 894 }, 895 Direction: parser.LEFT, 896 Error: "field table1.notexist does not exist", 897 }, 898 { 899 Name: "Outer Join Direction Undefined", 900 View: &View{ 901 Header: NewHeaderWithId("table1", []string{"column1", "column2"}), 902 RecordSet: []Record{ 903 NewRecordWithId(1, []value.Primary{ 904 value.NewInteger(1), 905 value.NewString("str1"), 906 }), 907 NewRecordWithId(2, []value.Primary{ 908 value.NewInteger(2), 909 value.NewString("str2"), 910 }), 911 NewRecordWithId(3, []value.Primary{ 912 value.NewInteger(3), 913 value.NewString("str3"), 914 }), 915 }, 916 }, 917 JoinView: &View{ 918 Header: NewHeaderWithId("table2", []string{"column1", "column3"}), 919 RecordSet: []Record{ 920 NewRecordWithId(1, []value.Primary{ 921 value.NewInteger(2), 922 value.NewString("str22"), 923 }), 924 NewRecordWithId(2, []value.Primary{ 925 value.NewInteger(3), 926 value.NewString("str33"), 927 }), 928 NewRecordWithId(3, []value.Primary{ 929 value.NewInteger(4), 930 value.NewString("str44"), 931 }), 932 }, 933 }, 934 Condition: parser.Comparison{ 935 LHS: parser.FieldReference{View: parser.Identifier{Literal: "table1"}, Column: parser.Identifier{Literal: "column1"}}, 936 RHS: parser.FieldReference{View: parser.Identifier{Literal: "table2"}, Column: parser.Identifier{Literal: "column1"}}, 937 Operator: parser.Token{Token: '=', Literal: "="}, 938 }, 939 Direction: parser.TokenUndefined, 940 Result: &View{ 941 Header: []HeaderField{ 942 {View: "table1", Column: InternalIdColumn}, 943 {View: "table1", Column: "column1", Number: 1, IsFromTable: true}, 944 {View: "table1", Column: "column2", Number: 2, IsFromTable: true}, 945 {View: "table2", Column: InternalIdColumn}, 946 {View: "table2", Column: "column1", Number: 1, IsFromTable: true}, 947 {View: "table2", Column: "column3", Number: 2, IsFromTable: true}, 948 }, 949 RecordSet: []Record{ 950 NewRecord([]value.Primary{ 951 value.NewInteger(1), 952 value.NewInteger(1), 953 value.NewString("str1"), 954 value.NewNull(), 955 value.NewNull(), 956 value.NewNull(), 957 }), 958 NewRecord([]value.Primary{ 959 value.NewInteger(2), 960 value.NewInteger(2), 961 value.NewString("str2"), 962 value.NewInteger(1), 963 value.NewInteger(2), 964 value.NewString("str22"), 965 }), 966 NewRecord([]value.Primary{ 967 value.NewInteger(3), 968 value.NewInteger(3), 969 value.NewString("str3"), 970 value.NewInteger(2), 971 value.NewInteger(3), 972 value.NewString("str33"), 973 }), 974 }, 975 }, 976 }, 977 } 978 979 func TestOuterJoin(t *testing.T) { 980 for _, v := range outerJoinTests { 981 if v.Scope == nil { 982 v.Scope = NewReferenceScope(TestTx) 983 } 984 985 err := OuterJoin(context.Background(), v.Scope, v.View, v.JoinView, v.Condition, v.Direction) 986 if err != nil { 987 if len(v.Error) < 1 { 988 t.Errorf("%s: unexpected error %q", v.Name, err) 989 } else if err.Error() != v.Error { 990 t.Errorf("%s: error %q, want error %q", v.Name, err.Error(), v.Error) 991 } 992 continue 993 } 994 if 0 < len(v.Error) { 995 t.Errorf("%s: no error, want error %q", v.Name, v.Error) 996 continue 997 } 998 if !reflect.DeepEqual(v.View, v.Result) { 999 t.Errorf("%s: result = %v, want %v", v.Name, v.View, v.Result) 1000 t.Log(v.View.RecordSet) 1001 t.Log(v.Result.RecordSet) 1002 } 1003 } 1004 } 1005 1006 var calcMinimumRequiredTests = []struct { 1007 Int1 int 1008 Int2 int 1009 Default int 1010 Expect int 1011 }{ 1012 { 1013 Int1: 13, 1014 Int2: 20, 1015 Default: 80, 1016 Expect: 5, 1017 }, 1018 { 1019 Int1: 1, 1020 Int2: 200, 1021 Default: 80, 1022 Expect: 1, 1023 }, 1024 { 1025 Int1: 199, 1026 Int2: 1, 1027 Default: 80, 1028 Expect: 100, 1029 }, 1030 { 1031 Int1: 1, 1032 Int2: 0, 1033 Default: 80, 1034 Expect: 80, 1035 }, 1036 { 1037 Int1: 1, 1038 Int2: 1, 1039 Default: 80, 1040 Expect: 80, 1041 }, 1042 } 1043 1044 func TestCalcMinimumRequired(t *testing.T) { 1045 for _, v := range calcMinimumRequiredTests { 1046 result := CalcMinimumRequired(v.Int1, v.Int2, v.Default) 1047 if result != v.Expect { 1048 t.Errorf("result = %d, want %d for %d, %d, %d", result, v.Expect, v.Int1, v.Int2, v.Default) 1049 } 1050 } 1051 } 1052 1053 func GenerateBenchView(tableName string, records int, startIdx int) *View { 1054 view := &View{ 1055 Header: NewHeader(tableName, []string{"c1"}), 1056 RecordSet: make(RecordSet, records), 1057 } 1058 1059 for i := 0; i < records; i++ { 1060 view.RecordSet[i] = NewRecord([]value.Primary{value.NewInteger(int64(i + startIdx))}) 1061 } 1062 1063 return view 1064 } 1065 1066 func BenchmarkCrossJoin(b *testing.B) { 1067 ctx := context.Background() 1068 scope := NewReferenceScope(TestTx) 1069 1070 for i := 0; i < b.N; i++ { 1071 view := GenerateBenchView("t1", 100, 0) 1072 joinView := GenerateBenchView("t2", 100, 50) 1073 1074 _ = CrossJoin(ctx, scope, view, joinView) 1075 } 1076 } 1077 1078 func BenchmarkInnerJoin(b *testing.B) { 1079 condition := parser.Comparison{ 1080 LHS: parser.FieldReference{View: parser.Identifier{Literal: "t1"}, Column: parser.Identifier{Literal: "c1"}}, 1081 RHS: parser.FieldReference{View: parser.Identifier{Literal: "t2"}, Column: parser.Identifier{Literal: "c1"}}, 1082 Operator: parser.Token{Token: '=', Literal: "="}, 1083 } 1084 1085 ctx := context.Background() 1086 scope := NewReferenceScope(TestTx) 1087 1088 for i := 0; i < b.N; i++ { 1089 view := GenerateBenchView("t1", 100, 0) 1090 joinView := GenerateBenchView("t2", 100, 50) 1091 1092 _ = InnerJoin(ctx, scope, view, joinView, condition) 1093 } 1094 } 1095 1096 func BenchmarkOuterJoin(b *testing.B) { 1097 condition := parser.Comparison{ 1098 LHS: parser.FieldReference{View: parser.Identifier{Literal: "t1"}, Column: parser.Identifier{Literal: "c1"}}, 1099 RHS: parser.FieldReference{View: parser.Identifier{Literal: "t2"}, Column: parser.Identifier{Literal: "c1"}}, 1100 Operator: parser.Token{Token: '=', Literal: "="}, 1101 } 1102 1103 ctx := context.Background() 1104 scope := NewReferenceScope(TestTx) 1105 1106 for i := 0; i < b.N; i++ { 1107 view := GenerateBenchView("t1", 100, 0) 1108 joinView := GenerateBenchView("t2", 100, 50) 1109 1110 _ = OuterJoin(ctx, scope, view, joinView, condition, parser.LEFT) 1111 } 1112 }