github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/ordering/lookup_join_test.go (about)

     1  // Copyright 2018 The Cockroach Authors.
     2  //
     3  // Use of this software is governed by the Business Source License
     4  // included in the file licenses/BSL.txt.
     5  //
     6  // As of the Change Date specified in that file, in accordance with
     7  // the Business Source License, use of this software will be governed
     8  // by the Apache License, Version 2.0, included in the file
     9  // licenses/APL.txt.
    10  
    11  package ordering
    12  
    13  import (
    14  	"fmt"
    15  	"testing"
    16  
    17  	"github.com/cockroachdb/cockroach/pkg/sql/opt"
    18  	"github.com/cockroachdb/cockroach/pkg/sql/opt/cat"
    19  	"github.com/cockroachdb/cockroach/pkg/sql/opt/memo"
    20  	"github.com/cockroachdb/cockroach/pkg/sql/opt/norm"
    21  	"github.com/cockroachdb/cockroach/pkg/sql/opt/props"
    22  	"github.com/cockroachdb/cockroach/pkg/sql/opt/props/physical"
    23  	"github.com/cockroachdb/cockroach/pkg/sql/opt/testutils/testcat"
    24  	"github.com/cockroachdb/cockroach/pkg/sql/opt/testutils/testexpr"
    25  	"github.com/cockroachdb/cockroach/pkg/sql/sem/tree"
    26  )
    27  
    28  func TestLookupJoinProvided(t *testing.T) {
    29  	tc := testcat.New()
    30  	if _, err := tc.ExecuteDDL(
    31  		"CREATE TABLE t (c1 INT, c2 INT, c3 INT, c4 INT, PRIMARY KEY(c1, c2))",
    32  	); err != nil {
    33  		t.Fatal(err)
    34  	}
    35  	evalCtx := tree.NewTestingEvalContext(nil /* st */)
    36  	var f norm.Factory
    37  	f.Init(evalCtx, tc)
    38  	md := f.Metadata()
    39  	tn := tree.NewUnqualifiedTableName("t")
    40  	tab := md.AddTable(tc.Table(tn), tn)
    41  
    42  	if c1 := tab.ColumnID(0); c1 != 1 {
    43  		t.Fatalf("unexpected ID for column c1: %d\n", c1)
    44  	}
    45  
    46  	c := func(cols ...opt.ColumnID) opt.ColSet {
    47  		return opt.MakeColSet(cols...)
    48  	}
    49  
    50  	testCases := []struct {
    51  		keyCols  opt.ColList
    52  		outCols  opt.ColSet
    53  		required string
    54  		input    string
    55  		provided string
    56  	}{
    57  		// In these tests, the input (left side of the join) has columns 5,6 and the
    58  		// table (right side) has columns 1,2,3,4 and the join has condition
    59  		// (c5, c6) = (c1, c2).
    60  		//
    61  		{ // case 1: the lookup join adds columns 3,4 from the table and retains the
    62  			// input columns.
    63  			keyCols:  opt.ColList{5, 6},
    64  			outCols:  c(3, 4, 5, 6),
    65  			required: "+5,+6",
    66  			input:    "+5,+6",
    67  			provided: "+5,+6",
    68  		},
    69  		{ // case 2: the lookup join produces all columns. The provided ordering
    70  			// on 5,6 is equivalent to an ordering on 1,2.
    71  			keyCols:  opt.ColList{5, 6},
    72  			outCols:  c(1, 2, 3, 4, 5, 6),
    73  			required: "-1,+2",
    74  			input:    "-5,+6",
    75  			provided: "-5,+6",
    76  		},
    77  		{ // case 3: the lookup join does not produce input columns 5,6; we must
    78  			// remap the input ordering to refer to output columns 1,2 instead.
    79  			keyCols:  opt.ColList{5, 6},
    80  			outCols:  c(1, 2, 3, 4),
    81  			required: "+1,-2",
    82  			input:    "+5,-6",
    83  			provided: "+1,-2",
    84  		},
    85  		{ // case 4: a hybrid of the two cases above (we need to remap column 6).
    86  			keyCols:  opt.ColList{5, 6},
    87  			outCols:  c(1, 2, 3, 4, 5),
    88  			required: "-1,-2",
    89  			input:    "-5,-6",
    90  			provided: "-5,-2",
    91  		},
    92  	}
    93  
    94  	for tcIdx, tc := range testCases {
    95  		t.Run(fmt.Sprintf("case%d", tcIdx+1), func(t *testing.T) {
    96  			input := &testexpr.Instance{
    97  				Rel: &props.Relational{},
    98  				Provided: &physical.Provided{
    99  					Ordering: physical.ParseOrdering(tc.input),
   100  				},
   101  			}
   102  			lookupJoin := f.Memo().MemoizeLookupJoin(
   103  				input,
   104  				nil, /* FiltersExpr */
   105  				&memo.LookupJoinPrivate{
   106  					JoinType: opt.InnerJoinOp,
   107  					Table:    tab,
   108  					Index:    cat.PrimaryIndex,
   109  					KeyCols:  tc.keyCols,
   110  					Cols:     tc.outCols,
   111  				},
   112  			)
   113  			req := physical.ParseOrderingChoice(tc.required)
   114  			res := lookupJoinBuildProvided(lookupJoin, &req).String()
   115  			if res != tc.provided {
   116  				t.Errorf("expected '%s', got '%s'", tc.provided, res)
   117  			}
   118  		})
   119  	}
   120  }