github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/rowexec/joiner_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 rowexec
    12  
    13  import (
    14  	"github.com/cockroachdb/cockroach/pkg/sql/execinfrapb"
    15  	"github.com/cockroachdb/cockroach/pkg/sql/sem/tree"
    16  	"github.com/cockroachdb/cockroach/pkg/sql/sqlbase"
    17  	"github.com/cockroachdb/cockroach/pkg/sql/types"
    18  	"github.com/cockroachdb/errors"
    19  )
    20  
    21  type joinerTestCase struct {
    22  	leftEqCols  []uint32
    23  	rightEqCols []uint32
    24  	joinType    sqlbase.JoinType
    25  	onExpr      execinfrapb.Expression
    26  	outCols     []uint32
    27  	leftTypes   []*types.T
    28  	leftInput   sqlbase.EncDatumRows
    29  	rightTypes  []*types.T
    30  	rightInput  sqlbase.EncDatumRows
    31  	expected    sqlbase.EncDatumRows
    32  }
    33  
    34  func joinerTestCases() []joinerTestCase {
    35  	v := [10]sqlbase.EncDatum{}
    36  	for i := range v {
    37  		v[i] = sqlbase.DatumToEncDatum(types.Int, tree.NewDInt(tree.DInt(i)))
    38  	}
    39  	null := sqlbase.EncDatum{Datum: tree.DNull}
    40  
    41  	testCases := []joinerTestCase{
    42  		// Originally from HashJoiner tests.
    43  		{
    44  			leftEqCols:  []uint32{0},
    45  			rightEqCols: []uint32{0},
    46  			joinType:    sqlbase.InnerJoin,
    47  			// Implicit @1 = @3 constraint.
    48  			outCols:   []uint32{0, 3, 4},
    49  			leftTypes: sqlbase.TwoIntCols,
    50  			leftInput: sqlbase.EncDatumRows{
    51  				{v[0], v[0]},
    52  				{v[1], v[4]},
    53  				{v[2], v[4]},
    54  				{v[3], v[1]},
    55  				{v[4], v[5]},
    56  				{v[5], v[5]},
    57  			},
    58  			rightTypes: sqlbase.ThreeIntCols,
    59  			rightInput: sqlbase.EncDatumRows{
    60  				{v[1], v[0], v[4]},
    61  				{v[3], v[4], v[1]},
    62  				{v[4], v[4], v[5]},
    63  			},
    64  			expected: sqlbase.EncDatumRows{
    65  				{v[1], v[0], v[4]},
    66  				{v[3], v[4], v[1]},
    67  				{v[4], v[4], v[5]},
    68  			},
    69  		},
    70  		{
    71  			leftEqCols:  []uint32{0},
    72  			rightEqCols: []uint32{0},
    73  			joinType:    sqlbase.InnerJoin,
    74  			// Implicit @1 = @3 constraint.
    75  			outCols:   []uint32{0, 1, 3},
    76  			leftTypes: sqlbase.TwoIntCols,
    77  			leftInput: sqlbase.EncDatumRows{
    78  				{v[0], v[0]},
    79  				{v[0], v[1]},
    80  			},
    81  			rightTypes: sqlbase.TwoIntCols,
    82  			rightInput: sqlbase.EncDatumRows{
    83  				{v[0], v[4]},
    84  				{v[0], v[1]},
    85  				{v[0], v[0]},
    86  				{v[0], v[5]},
    87  				{v[0], v[4]},
    88  			},
    89  			expected: sqlbase.EncDatumRows{
    90  				{v[0], v[0], v[4]},
    91  				{v[0], v[0], v[1]},
    92  				{v[0], v[0], v[0]},
    93  				{v[0], v[0], v[5]},
    94  				{v[0], v[0], v[4]},
    95  				{v[0], v[1], v[4]},
    96  				{v[0], v[1], v[1]},
    97  				{v[0], v[1], v[0]},
    98  				{v[0], v[1], v[5]},
    99  				{v[0], v[1], v[4]},
   100  			},
   101  		},
   102  		// Test that inner joins work with filter expressions.
   103  		{
   104  			leftEqCols:  []uint32{0},
   105  			rightEqCols: []uint32{0},
   106  			joinType:    sqlbase.InnerJoin,
   107  			onExpr:      execinfrapb.Expression{Expr: "@4 >= 4"},
   108  			// Implicit AND @1 = @3 constraint.
   109  			outCols:   []uint32{0, 1, 3},
   110  			leftTypes: sqlbase.TwoIntCols,
   111  			leftInput: sqlbase.EncDatumRows{
   112  				{v[0], v[0]},
   113  				{v[0], v[1]},
   114  				{v[1], v[0]},
   115  				{v[1], v[1]},
   116  			},
   117  			rightTypes: sqlbase.TwoIntCols,
   118  			rightInput: sqlbase.EncDatumRows{
   119  				{v[0], v[4]},
   120  				{v[0], v[1]},
   121  				{v[0], v[0]},
   122  				{v[0], v[5]},
   123  				{v[0], v[4]},
   124  				{v[1], v[4]},
   125  				{v[1], v[1]},
   126  				{v[1], v[0]},
   127  				{v[1], v[5]},
   128  				{v[1], v[4]},
   129  			},
   130  			expected: sqlbase.EncDatumRows{
   131  				{v[0], v[0], v[4]},
   132  				{v[0], v[0], v[5]},
   133  				{v[0], v[0], v[4]},
   134  				{v[0], v[1], v[4]},
   135  				{v[0], v[1], v[5]},
   136  				{v[0], v[1], v[4]},
   137  				{v[1], v[0], v[4]},
   138  				{v[1], v[0], v[5]},
   139  				{v[1], v[0], v[4]},
   140  				{v[1], v[1], v[4]},
   141  				{v[1], v[1], v[5]},
   142  				{v[1], v[1], v[4]},
   143  			},
   144  		},
   145  		{
   146  			leftEqCols:  []uint32{0},
   147  			rightEqCols: []uint32{0},
   148  			joinType:    sqlbase.LeftOuterJoin,
   149  			// Implicit @1 = @3 constraint.
   150  			outCols:   []uint32{0, 3, 4},
   151  			leftTypes: sqlbase.TwoIntCols,
   152  			leftInput: sqlbase.EncDatumRows{
   153  				{v[0], v[0]},
   154  				{v[1], v[4]},
   155  				{v[2], v[4]},
   156  				{v[3], v[1]},
   157  				{v[4], v[5]},
   158  				{v[5], v[5]},
   159  			},
   160  			rightTypes: sqlbase.ThreeIntCols,
   161  			rightInput: sqlbase.EncDatumRows{
   162  				{v[1], v[0], v[4]},
   163  				{v[3], v[4], v[1]},
   164  				{v[4], v[4], v[5]},
   165  			},
   166  			expected: sqlbase.EncDatumRows{
   167  				{v[0], null, null},
   168  				{v[1], v[0], v[4]},
   169  				{v[2], null, null},
   170  				{v[3], v[4], v[1]},
   171  				{v[4], v[4], v[5]},
   172  				{v[5], null, null},
   173  			},
   174  		},
   175  		{
   176  			leftEqCols:  []uint32{0},
   177  			rightEqCols: []uint32{0},
   178  			joinType:    sqlbase.RightOuterJoin,
   179  			// Implicit @1 = @4 constraint.
   180  			outCols:   []uint32{3, 1, 2},
   181  			leftTypes: sqlbase.ThreeIntCols,
   182  			leftInput: sqlbase.EncDatumRows{
   183  				{v[1], v[0], v[4]},
   184  				{v[3], v[4], v[1]},
   185  				{v[4], v[4], v[5]},
   186  			},
   187  			rightTypes: sqlbase.TwoIntCols,
   188  			rightInput: sqlbase.EncDatumRows{
   189  				{v[0], v[0]},
   190  				{v[1], v[4]},
   191  				{v[2], v[4]},
   192  				{v[3], v[1]},
   193  				{v[4], v[5]},
   194  				{v[5], v[5]},
   195  			},
   196  			expected: sqlbase.EncDatumRows{
   197  				{v[0], null, null},
   198  				{v[1], v[0], v[4]},
   199  				{v[2], null, null},
   200  				{v[3], v[4], v[1]},
   201  				{v[4], v[4], v[5]},
   202  				{v[5], null, null},
   203  			},
   204  		},
   205  		{
   206  			leftEqCols:  []uint32{0},
   207  			rightEqCols: []uint32{0},
   208  			joinType:    sqlbase.FullOuterJoin,
   209  			// Implicit @1 = @3 constraint.
   210  			outCols:   []uint32{0, 3, 4},
   211  			leftTypes: sqlbase.TwoIntCols,
   212  			leftInput: sqlbase.EncDatumRows{
   213  				{v[0], v[0]},
   214  				{v[1], v[4]},
   215  				{v[2], v[4]},
   216  				{v[3], v[1]},
   217  				{v[4], v[5]},
   218  			},
   219  			rightTypes: sqlbase.ThreeIntCols,
   220  			rightInput: sqlbase.EncDatumRows{
   221  				{v[1], v[0], v[4]},
   222  				{v[3], v[4], v[1]},
   223  				{v[4], v[4], v[5]},
   224  				{v[5], v[5], v[1]},
   225  			},
   226  			expected: sqlbase.EncDatumRows{
   227  				{v[0], null, null},
   228  				{v[1], v[0], v[4]},
   229  				{v[2], null, null},
   230  				{v[3], v[4], v[1]},
   231  				{v[4], v[4], v[5]},
   232  				{null, v[5], v[1]},
   233  			},
   234  		},
   235  		{
   236  			leftEqCols:  []uint32{0},
   237  			rightEqCols: []uint32{0},
   238  			joinType:    sqlbase.InnerJoin,
   239  			// Implicit @1 = @3 constraint.
   240  			outCols:   []uint32{0, 3, 4},
   241  			leftTypes: sqlbase.TwoIntCols,
   242  			leftInput: sqlbase.EncDatumRows{
   243  				{v[0], v[0]},
   244  				{v[2], v[4]},
   245  				{v[3], v[1]},
   246  				{v[4], v[5]},
   247  				{v[5], v[5]},
   248  			},
   249  			rightTypes: sqlbase.ThreeIntCols,
   250  			rightInput: sqlbase.EncDatumRows{
   251  				{v[1], v[0], v[4]},
   252  				{v[3], v[4], v[1]},
   253  				{v[4], v[4], v[5]},
   254  			},
   255  			expected: sqlbase.EncDatumRows{
   256  				{v[3], v[4], v[1]},
   257  				{v[4], v[4], v[5]},
   258  			},
   259  		},
   260  		// Test that left outer joins work with filters as expected.
   261  		{
   262  			leftEqCols:  []uint32{0},
   263  			rightEqCols: []uint32{0},
   264  			joinType:    sqlbase.LeftOuterJoin,
   265  			onExpr:      execinfrapb.Expression{Expr: "@3 = 9"},
   266  			outCols:     []uint32{0, 1},
   267  			leftTypes:   sqlbase.OneIntCol,
   268  			leftInput: sqlbase.EncDatumRows{
   269  				{v[1]},
   270  				{v[2]},
   271  				{v[3]},
   272  				{v[5]},
   273  				{v[6]},
   274  				{v[7]},
   275  			},
   276  			rightTypes: sqlbase.TwoIntCols,
   277  			rightInput: sqlbase.EncDatumRows{
   278  				{v[2], v[8]},
   279  				{v[3], v[9]},
   280  				{v[4], v[9]},
   281  
   282  				// Rows that match v[5].
   283  				{v[5], v[9]},
   284  				{v[5], v[9]},
   285  
   286  				// Rows that match v[6] but the ON condition fails.
   287  				{v[6], v[8]},
   288  				{v[6], v[8]},
   289  
   290  				// Rows that match v[7], ON condition fails for one.
   291  				{v[7], v[8]},
   292  				{v[7], v[9]},
   293  			},
   294  			expected: sqlbase.EncDatumRows{
   295  				{v[1], null},
   296  				{v[2], null},
   297  				{v[3], v[3]},
   298  				{v[5], v[5]},
   299  				{v[5], v[5]},
   300  				{v[6], null},
   301  				{v[7], v[7]},
   302  			},
   303  		},
   304  		// Test that right outer joins work with filters as expected.
   305  		{
   306  			leftEqCols:  []uint32{0},
   307  			rightEqCols: []uint32{0},
   308  			joinType:    sqlbase.RightOuterJoin,
   309  			onExpr:      execinfrapb.Expression{Expr: "@2 > 1"},
   310  			outCols:     []uint32{0, 1},
   311  			leftTypes:   sqlbase.OneIntCol,
   312  			leftInput: sqlbase.EncDatumRows{
   313  				{v[0]},
   314  				{v[1]},
   315  				{v[2]},
   316  			},
   317  			rightTypes: sqlbase.OneIntCol,
   318  			rightInput: sqlbase.EncDatumRows{
   319  				{v[1]},
   320  				{v[2]},
   321  				{v[3]},
   322  			},
   323  			expected: sqlbase.EncDatumRows{
   324  				{null, v[1]},
   325  				{v[2], v[2]},
   326  				{null, v[3]},
   327  			},
   328  		},
   329  		// Test that full outer joins work with filters as expected.
   330  		{
   331  			leftEqCols:  []uint32{0},
   332  			rightEqCols: []uint32{0},
   333  			joinType:    sqlbase.FullOuterJoin,
   334  			onExpr:      execinfrapb.Expression{Expr: "@2 > 1"},
   335  			outCols:     []uint32{0, 1},
   336  			leftTypes:   sqlbase.OneIntCol,
   337  			leftInput: sqlbase.EncDatumRows{
   338  				{v[0]},
   339  				{v[1]},
   340  				{v[2]},
   341  			},
   342  			rightTypes: sqlbase.OneIntCol,
   343  			rightInput: sqlbase.EncDatumRows{
   344  				{v[1]},
   345  				{v[2]},
   346  				{v[3]},
   347  			},
   348  			expected: sqlbase.EncDatumRows{
   349  				{v[0], null},
   350  				{null, v[1]},
   351  				{v[1], null},
   352  				{v[2], v[2]},
   353  				{null, v[3]},
   354  			},
   355  		},
   356  
   357  		// Tests for behavior when input contains NULLs.
   358  		{
   359  			leftEqCols:  []uint32{0, 1},
   360  			rightEqCols: []uint32{0, 1},
   361  			joinType:    sqlbase.InnerJoin,
   362  			// Implicit @1,@2 = @3,@4 constraint.
   363  			outCols:   []uint32{0, 1, 2, 3, 4},
   364  			leftTypes: sqlbase.TwoIntCols,
   365  			leftInput: sqlbase.EncDatumRows{
   366  				{v[0], v[0]},
   367  				{v[1], null},
   368  				{null, v[2]},
   369  				{null, null},
   370  			},
   371  			rightTypes: sqlbase.ThreeIntCols,
   372  			rightInput: sqlbase.EncDatumRows{
   373  				{v[0], v[0], v[4]},
   374  				{v[1], null, v[5]},
   375  				{null, v[2], v[6]},
   376  				{null, null, v[7]},
   377  			},
   378  			expected: sqlbase.EncDatumRows{
   379  				{v[0], v[0], v[0], v[0], v[4]},
   380  			},
   381  		},
   382  
   383  		{
   384  			leftEqCols:  []uint32{0, 1},
   385  			rightEqCols: []uint32{0, 1},
   386  			joinType:    sqlbase.LeftOuterJoin,
   387  			// Implicit @1,@2 = @3,@4 constraint.
   388  			outCols:   []uint32{0, 1, 2, 3, 4},
   389  			leftTypes: sqlbase.TwoIntCols,
   390  			leftInput: sqlbase.EncDatumRows{
   391  				{v[0], v[0]},
   392  				{v[1], null},
   393  				{null, v[2]},
   394  				{null, null},
   395  			},
   396  			rightTypes: sqlbase.ThreeIntCols,
   397  			rightInput: sqlbase.EncDatumRows{
   398  				{v[0], v[0], v[4]},
   399  				{v[1], null, v[5]},
   400  				{null, v[2], v[6]},
   401  				{null, null, v[7]},
   402  			},
   403  			expected: sqlbase.EncDatumRows{
   404  				{v[0], v[0], v[0], v[0], v[4]},
   405  				{v[1], null, null, null, null},
   406  				{null, v[2], null, null, null},
   407  				{null, null, null, null, null},
   408  			},
   409  		},
   410  
   411  		{
   412  			leftEqCols:  []uint32{0, 1},
   413  			rightEqCols: []uint32{0, 1},
   414  			joinType:    sqlbase.RightOuterJoin,
   415  			// Implicit @1,@2 = @3,@4 constraint.
   416  			outCols:   []uint32{0, 1, 2, 3, 4},
   417  			leftTypes: sqlbase.TwoIntCols,
   418  			leftInput: sqlbase.EncDatumRows{
   419  				{v[0], v[0]},
   420  				{v[1], null},
   421  				{null, v[2]},
   422  				{null, null},
   423  			},
   424  			rightTypes: sqlbase.ThreeIntCols,
   425  			rightInput: sqlbase.EncDatumRows{
   426  				{v[0], v[0], v[4]},
   427  				{v[1], null, v[5]},
   428  				{null, v[2], v[6]},
   429  				{null, null, v[7]},
   430  			},
   431  			expected: sqlbase.EncDatumRows{
   432  				{v[0], v[0], v[0], v[0], v[4]},
   433  				{null, null, v[1], null, v[5]},
   434  				{null, null, null, v[2], v[6]},
   435  				{null, null, null, null, v[7]},
   436  			},
   437  		},
   438  
   439  		{
   440  			leftEqCols:  []uint32{0, 1},
   441  			rightEqCols: []uint32{0, 1},
   442  			joinType:    sqlbase.FullOuterJoin,
   443  			// Implicit @1,@2 = @3,@4 constraint.
   444  			outCols:   []uint32{0, 1, 2, 3, 4},
   445  			leftTypes: sqlbase.TwoIntCols,
   446  			leftInput: sqlbase.EncDatumRows{
   447  				{v[0], v[0]},
   448  				{v[1], null},
   449  				{null, v[2]},
   450  				{null, null},
   451  			},
   452  			rightTypes: sqlbase.ThreeIntCols,
   453  			rightInput: sqlbase.EncDatumRows{
   454  				{v[0], v[0], v[4]},
   455  				{v[1], null, v[5]},
   456  				{null, v[2], v[6]},
   457  				{null, null, v[7]},
   458  			},
   459  			expected: sqlbase.EncDatumRows{
   460  				{v[0], v[0], v[0], v[0], v[4]},
   461  				{null, null, v[1], null, v[5]},
   462  				{null, null, null, v[2], v[6]},
   463  				{null, null, null, null, v[7]},
   464  				{v[1], null, null, null, null},
   465  				{null, v[2], null, null, null},
   466  				{null, null, null, null, null},
   467  			},
   468  		},
   469  		{
   470  			// Ensure semi join doesn't emit extra rows when
   471  			// there are multiple matching rows in the
   472  			// rightInput and the rightInput is smaller.
   473  			leftEqCols:  []uint32{0},
   474  			rightEqCols: []uint32{0},
   475  			joinType:    sqlbase.LeftSemiJoin,
   476  			// Implicit @1 = @3 constraint.
   477  			outCols:   []uint32{0},
   478  			leftTypes: sqlbase.TwoIntCols,
   479  			leftInput: sqlbase.EncDatumRows{
   480  				{v[0], v[4]},
   481  				{v[2], v[0]},
   482  				{v[2], v[1]},
   483  				{v[3], v[5]},
   484  				{v[3], v[4]},
   485  				{v[3], v[3]},
   486  			},
   487  			rightTypes: sqlbase.TwoIntCols,
   488  			rightInput: sqlbase.EncDatumRows{
   489  				{v[0], v[0]},
   490  				{v[0], v[1]},
   491  				{v[1], v[1]},
   492  				{v[2], v[1]},
   493  			},
   494  			expected: sqlbase.EncDatumRows{
   495  				{v[0]},
   496  				{v[2]},
   497  				{v[2]},
   498  			},
   499  		},
   500  		{
   501  			// Ensure semi join doesn't emit extra rows when
   502  			// there are multiple matching rows in the
   503  			// rightInput and the leftInput is smaller
   504  			leftEqCols:  []uint32{0},
   505  			rightEqCols: []uint32{0},
   506  			joinType:    sqlbase.LeftSemiJoin,
   507  			// Implicit @1 = @3 constraint.
   508  			outCols:   []uint32{0},
   509  			leftTypes: sqlbase.TwoIntCols,
   510  			leftInput: sqlbase.EncDatumRows{
   511  				{v[0], v[0]},
   512  				{v[0], v[1]},
   513  				{v[1], v[1]},
   514  				{v[2], v[1]},
   515  			},
   516  			rightTypes: sqlbase.TwoIntCols,
   517  			rightInput: sqlbase.EncDatumRows{
   518  				{v[0], v[4]},
   519  				{v[2], v[0]},
   520  				{v[2], v[1]},
   521  				{v[3], v[5]},
   522  				{v[3], v[4]},
   523  				{v[3], v[3]},
   524  			},
   525  			expected: sqlbase.EncDatumRows{
   526  				{v[0]},
   527  				{v[0]},
   528  				{v[2]},
   529  			},
   530  		},
   531  		{
   532  			// Ensure nulls don't match with any value
   533  			// for semi joins.
   534  			leftEqCols:  []uint32{0},
   535  			rightEqCols: []uint32{0},
   536  			joinType:    sqlbase.LeftSemiJoin,
   537  			// Implicit @1 = @3 constraint.
   538  			outCols:   []uint32{0},
   539  			leftTypes: sqlbase.TwoIntCols,
   540  			leftInput: sqlbase.EncDatumRows{
   541  				{v[0], v[0]},
   542  				{v[0], v[1]},
   543  				{v[1], v[1]},
   544  				{v[2], v[1]},
   545  			},
   546  			rightTypes: sqlbase.TwoIntCols,
   547  			rightInput: sqlbase.EncDatumRows{
   548  				{v[0], v[4]},
   549  				{null, v[1]},
   550  				{v[3], v[5]},
   551  				{v[3], v[4]},
   552  				{v[3], v[3]},
   553  			},
   554  			expected: sqlbase.EncDatumRows{
   555  				{v[0]},
   556  				{v[0]},
   557  			},
   558  		},
   559  		{
   560  			// Ensure that nulls don't match
   561  			// with nulls for semiJoins
   562  			leftEqCols:  []uint32{0},
   563  			rightEqCols: []uint32{0},
   564  			joinType:    sqlbase.LeftSemiJoin,
   565  			// Implicit @1 = @3 constraint.
   566  			outCols:   []uint32{0},
   567  			leftTypes: sqlbase.TwoIntCols,
   568  			leftInput: sqlbase.EncDatumRows{
   569  				{v[0], v[0]},
   570  				{null, v[1]},
   571  				{v[1], v[1]},
   572  				{v[2], v[1]},
   573  			},
   574  			rightTypes: sqlbase.TwoIntCols,
   575  			rightInput: sqlbase.EncDatumRows{
   576  				{v[0], v[4]},
   577  				{null, v[1]},
   578  				{v[3], v[5]},
   579  				{v[3], v[4]},
   580  				{v[3], v[3]},
   581  			},
   582  			expected: sqlbase.EncDatumRows{
   583  				{v[0]},
   584  			},
   585  		},
   586  		{
   587  			// Ensure that semi joins respect OnExprs.
   588  			leftEqCols:  []uint32{0},
   589  			rightEqCols: []uint32{0},
   590  			joinType:    sqlbase.LeftSemiJoin,
   591  			onExpr:      execinfrapb.Expression{Expr: "@1 > 1"},
   592  			// Implicit @1 = @3 constraint.
   593  			outCols:   []uint32{0, 1},
   594  			leftTypes: sqlbase.TwoIntCols,
   595  			leftInput: sqlbase.EncDatumRows{
   596  				{v[0], v[0]},
   597  				{v[1], v[1]},
   598  				{v[2], v[1]},
   599  				{v[2], v[2]},
   600  			},
   601  			rightTypes: sqlbase.TwoIntCols,
   602  			rightInput: sqlbase.EncDatumRows{
   603  				{v[0], v[4]},
   604  				{v[0], v[4]},
   605  				{v[2], v[5]},
   606  				{v[2], v[6]},
   607  				{v[3], v[3]},
   608  			},
   609  			expected: sqlbase.EncDatumRows{
   610  				{v[2], v[1]},
   611  				{v[2], v[2]},
   612  			},
   613  		},
   614  		{
   615  			// Ensure that semi joins respect OnExprs on both inputs.
   616  			leftEqCols:  []uint32{0},
   617  			rightEqCols: []uint32{0},
   618  			joinType:    sqlbase.LeftSemiJoin,
   619  			onExpr:      execinfrapb.Expression{Expr: "@4 > 4 and @2 + @4 = 8"},
   620  			// Implicit @1 = @3 constraint.
   621  			outCols:   []uint32{0, 1},
   622  			leftTypes: sqlbase.TwoIntCols,
   623  			leftInput: sqlbase.EncDatumRows{
   624  				{v[0], v[4]},
   625  				{v[1], v[1]},
   626  				{v[2], v[1]},
   627  				{v[2], v[2]},
   628  			},
   629  			rightTypes: sqlbase.TwoIntCols,
   630  			rightInput: sqlbase.EncDatumRows{
   631  				{v[0], v[4]},
   632  				{v[0], v[4]},
   633  				{v[2], v[5]},
   634  				{v[2], v[6]},
   635  				{v[3], v[3]},
   636  			},
   637  			expected: sqlbase.EncDatumRows{
   638  				{v[2], v[2]},
   639  			},
   640  		},
   641  		{
   642  			// Ensure that anti-joins don't produce duplicates when left
   643  			// side is smaller.
   644  			leftEqCols:  []uint32{0},
   645  			rightEqCols: []uint32{0},
   646  			joinType:    sqlbase.LeftAntiJoin,
   647  			// Implicit @1 = @3 constraint.
   648  			outCols:   []uint32{0, 1},
   649  			leftTypes: sqlbase.TwoIntCols,
   650  			leftInput: sqlbase.EncDatumRows{
   651  				{v[0], v[0]},
   652  				{v[1], v[1]},
   653  				{v[2], v[1]},
   654  			},
   655  			rightTypes: sqlbase.TwoIntCols,
   656  			rightInput: sqlbase.EncDatumRows{
   657  				{v[0], v[4]},
   658  				{v[2], v[5]},
   659  				{v[2], v[6]},
   660  				{v[3], v[3]},
   661  			},
   662  			expected: sqlbase.EncDatumRows{
   663  				{v[1], v[1]},
   664  			},
   665  		},
   666  		{
   667  			// Ensure that anti-joins don't produce duplicates when right
   668  			// side is smaller.
   669  			leftEqCols:  []uint32{0},
   670  			rightEqCols: []uint32{0},
   671  			joinType:    sqlbase.LeftAntiJoin,
   672  			// Implicit @1 = @3 constraint.
   673  			outCols:   []uint32{0, 1},
   674  			leftTypes: sqlbase.TwoIntCols,
   675  			leftInput: sqlbase.EncDatumRows{
   676  				{v[0], v[0]},
   677  				{v[0], v[0]},
   678  				{v[1], v[1]},
   679  				{v[1], v[2]},
   680  				{v[2], v[1]},
   681  				{v[3], v[4]},
   682  			},
   683  			rightTypes: sqlbase.TwoIntCols,
   684  			rightInput: sqlbase.EncDatumRows{
   685  				{v[0], v[4]},
   686  				{v[2], v[5]},
   687  				{v[2], v[6]},
   688  				{v[3], v[3]},
   689  			},
   690  			expected: sqlbase.EncDatumRows{
   691  				{v[1], v[1]},
   692  				{v[1], v[2]},
   693  			},
   694  		},
   695  		{
   696  			// Ensure nulls aren't equal in anti-joins.
   697  			leftEqCols:  []uint32{0},
   698  			rightEqCols: []uint32{0},
   699  			joinType:    sqlbase.LeftAntiJoin,
   700  			// Implicit @1 = @3 constraint.
   701  			outCols:   []uint32{0, 1},
   702  			leftTypes: sqlbase.TwoIntCols,
   703  			leftInput: sqlbase.EncDatumRows{
   704  				{v[0], v[0]},
   705  				{v[0], v[0]},
   706  				{v[1], v[1]},
   707  				{null, v[2]},
   708  				{v[2], v[1]},
   709  				{v[3], v[4]},
   710  			},
   711  			rightTypes: sqlbase.TwoIntCols,
   712  			rightInput: sqlbase.EncDatumRows{
   713  				{v[0], v[4]},
   714  				{null, v[5]},
   715  				{v[2], v[6]},
   716  				{v[3], v[3]},
   717  			},
   718  			expected: sqlbase.EncDatumRows{
   719  				{v[1], v[1]},
   720  				{null, v[2]},
   721  			},
   722  		},
   723  		{
   724  			// Ensure nulls don't match to anything in anti-joins.
   725  			leftEqCols:  []uint32{0},
   726  			rightEqCols: []uint32{0},
   727  			joinType:    sqlbase.LeftAntiJoin,
   728  			// Implicit @1 = @3 constraint.
   729  			outCols:   []uint32{0, 1},
   730  			leftTypes: sqlbase.TwoIntCols,
   731  			leftInput: sqlbase.EncDatumRows{
   732  				{v[0], v[0]},
   733  				{v[0], v[0]},
   734  				{v[1], v[1]},
   735  				{null, v[2]},
   736  				{v[2], v[1]},
   737  				{v[3], v[4]},
   738  			},
   739  			rightTypes: sqlbase.TwoIntCols,
   740  			rightInput: sqlbase.EncDatumRows{
   741  				{v[0], v[4]},
   742  				{null, v[5]},
   743  				{v[2], v[6]},
   744  				{v[3], v[3]},
   745  			},
   746  			expected: sqlbase.EncDatumRows{
   747  				{v[1], v[1]},
   748  				{null, v[2]},
   749  			},
   750  		},
   751  		{
   752  			// Ensure anti-joins obey onExpr constraints on columns
   753  			// from both inputs.
   754  			leftEqCols:  []uint32{0},
   755  			rightEqCols: []uint32{0},
   756  			joinType:    sqlbase.LeftAntiJoin,
   757  			onExpr:      execinfrapb.Expression{Expr: "(@2 + @4) % 2 = 0"},
   758  			// Implicit @1 = @3 constraint.
   759  			outCols:   []uint32{0, 1},
   760  			leftTypes: sqlbase.TwoIntCols,
   761  			leftInput: sqlbase.EncDatumRows{
   762  				{v[0], v[4]},
   763  				{v[1], v[2]},
   764  				{v[1], v[3]},
   765  				{v[2], v[2]},
   766  				{v[2], v[3]},
   767  			},
   768  			rightTypes: sqlbase.TwoIntCols,
   769  			rightInput: sqlbase.EncDatumRows{
   770  				{v[0], v[2]},
   771  				{v[2], v[1]},
   772  				{v[3], v[3]},
   773  			},
   774  			expected: sqlbase.EncDatumRows{
   775  				{v[1], v[2]},
   776  				{v[1], v[3]},
   777  				{v[2], v[2]},
   778  			},
   779  		},
   780  		{
   781  			// Ensure anti-joins obey onExpr constraints on columns
   782  			// from both inputs when left input is smaller.
   783  			leftEqCols:  []uint32{0},
   784  			rightEqCols: []uint32{0},
   785  			joinType:    sqlbase.LeftAntiJoin,
   786  			onExpr:      execinfrapb.Expression{Expr: "(@2 + @4) % 2 = 0"},
   787  			// Implicit @1 = @3 constraint.
   788  			outCols:   []uint32{0, 1},
   789  			leftTypes: sqlbase.TwoIntCols,
   790  			leftInput: sqlbase.EncDatumRows{
   791  				{v[0], v[4]},
   792  				{v[1], v[2]},
   793  				{v[1], v[3]},
   794  				{v[2], v[2]},
   795  				{v[2], v[3]},
   796  			},
   797  			rightTypes: sqlbase.TwoIntCols,
   798  			rightInput: sqlbase.EncDatumRows{
   799  				{v[0], v[2]},
   800  				{v[2], v[1]},
   801  				{v[3], v[3]},
   802  				{v[4], v[1]},
   803  				{v[4], v[2]},
   804  				{v[4], v[3]},
   805  				{v[4], v[4]},
   806  			},
   807  			expected: sqlbase.EncDatumRows{
   808  				{v[1], v[2]},
   809  				{v[1], v[3]},
   810  				{v[2], v[2]},
   811  			},
   812  		},
   813  	}
   814  
   815  	return testCases
   816  }
   817  
   818  // joinerErrorTestCase specifies a test case where an error is expected.
   819  type joinerErrorTestCase struct {
   820  	description string
   821  	leftEqCols  []uint32
   822  	rightEqCols []uint32
   823  	joinType    sqlbase.JoinType
   824  	onExpr      execinfrapb.Expression
   825  	outCols     []uint32
   826  	leftTypes   []*types.T
   827  	leftInput   sqlbase.EncDatumRows
   828  	rightTypes  []*types.T
   829  	rightInput  sqlbase.EncDatumRows
   830  	expectedErr error
   831  }
   832  
   833  func joinerErrorTestCases() []joinerErrorTestCase {
   834  	v := [10]sqlbase.EncDatum{}
   835  	for i := range v {
   836  		v[i] = sqlbase.DatumToEncDatum(types.Int, tree.NewDInt(tree.DInt(i)))
   837  	}
   838  
   839  	testCases := []joinerErrorTestCase{
   840  		// Originally from HashJoiner tests.
   841  		{
   842  			description: "Ensure that columns from the right input cannot be in semi-join output.",
   843  			leftEqCols:  []uint32{0},
   844  			rightEqCols: []uint32{0},
   845  			joinType:    sqlbase.LeftSemiJoin,
   846  			// Implicit @1 = @3 constraint.
   847  			outCols:   []uint32{0, 1, 2},
   848  			leftTypes: sqlbase.TwoIntCols,
   849  			leftInput: sqlbase.EncDatumRows{
   850  				{v[0], v[0]},
   851  				{v[1], v[1]},
   852  				{v[2], v[1]},
   853  				{v[2], v[2]},
   854  			},
   855  			rightTypes: sqlbase.TwoIntCols,
   856  			rightInput: sqlbase.EncDatumRows{
   857  				{v[0], v[4]},
   858  				{v[0], v[4]},
   859  				{v[2], v[5]},
   860  				{v[2], v[6]},
   861  				{v[3], v[3]},
   862  			},
   863  			expectedErr: errors.Errorf("invalid output column %d (only %d available)", 2, 2),
   864  		},
   865  		{
   866  			description: "Ensure that columns from the right input cannot be in anti-join output.",
   867  			leftEqCols:  []uint32{0},
   868  			rightEqCols: []uint32{0},
   869  			joinType:    sqlbase.LeftAntiJoin,
   870  			// Implicit @1 = @3 constraint.
   871  			outCols:   []uint32{0, 1, 2},
   872  			leftTypes: sqlbase.TwoIntCols,
   873  			leftInput: sqlbase.EncDatumRows{
   874  				{v[0], v[0]},
   875  				{v[1], v[1]},
   876  				{v[2], v[1]},
   877  				{v[2], v[2]},
   878  			},
   879  			rightTypes: sqlbase.TwoIntCols,
   880  			rightInput: sqlbase.EncDatumRows{
   881  				{v[0], v[4]},
   882  				{v[0], v[4]},
   883  				{v[2], v[5]},
   884  				{v[2], v[6]},
   885  				{v[3], v[3]},
   886  			},
   887  			expectedErr: errors.Errorf("invalid output column %d (only %d available)", 2, 2),
   888  		},
   889  	}
   890  	return testCases
   891  }