vitess.io/vitess@v0.16.2/go/vt/vtgate/vindexes/lookup_test.go (about)

     1  /*
     2  Copyright 2019 The Vitess Authors.
     3  
     4  Licensed under the Apache License, Version 2.0 (the "License");
     5  you may not use this file except in compliance with the License.
     6  You may obtain a copy of the License at
     7  
     8      http://www.apache.org/licenses/LICENSE-2.0
     9  
    10  Unless required by applicable law or agreed to in writing, software
    11  distributed under the License is distributed on an "AS IS" BASIS,
    12  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    13  See the License for the specific language governing permissions and
    14  limitations under the License.
    15  */
    16  
    17  package vindexes
    18  
    19  import (
    20  	"context"
    21  	"errors"
    22  	"testing"
    23  
    24  	"vitess.io/vitess/go/test/utils"
    25  
    26  	"strings"
    27  
    28  	"github.com/stretchr/testify/assert"
    29  	"github.com/stretchr/testify/require"
    30  
    31  	"vitess.io/vitess/go/sqltypes"
    32  
    33  	"vitess.io/vitess/go/vt/key"
    34  	querypb "vitess.io/vitess/go/vt/proto/query"
    35  	topodatapb "vitess.io/vitess/go/vt/proto/topodata"
    36  	vtgatepb "vitess.io/vitess/go/vt/proto/vtgate"
    37  )
    38  
    39  // LookupNonUnique tests are more comprehensive than others.
    40  // They also test lookupInternal functionality.
    41  
    42  var _ VCursor = (*vcursor)(nil)
    43  
    44  type vcursor struct {
    45  	mustFail    bool
    46  	numRows     int
    47  	result      *sqltypes.Result
    48  	queries     []*querypb.BoundQuery
    49  	autocommits int
    50  	pre, post   int
    51  	keys        []sqltypes.Value
    52  }
    53  
    54  func (vc *vcursor) LookupRowLockShardSession() vtgatepb.CommitOrder {
    55  	panic("implement me")
    56  }
    57  
    58  func (vc *vcursor) InTransactionAndIsDML() bool {
    59  	return false
    60  }
    61  
    62  func (vc *vcursor) Execute(ctx context.Context, method string, query string, bindvars map[string]*querypb.BindVariable, rollbackOnError bool, co vtgatepb.CommitOrder) (*sqltypes.Result, error) {
    63  	switch co {
    64  	case vtgatepb.CommitOrder_PRE:
    65  		vc.pre++
    66  	case vtgatepb.CommitOrder_POST:
    67  		vc.post++
    68  	case vtgatepb.CommitOrder_AUTOCOMMIT:
    69  		vc.autocommits++
    70  	}
    71  	return vc.execute(query, bindvars)
    72  }
    73  
    74  func (vc *vcursor) ExecuteKeyspaceID(ctx context.Context, keyspace string, ksid []byte, query string, bindVars map[string]*querypb.BindVariable, rollbackOnError, autocommit bool) (*sqltypes.Result, error) {
    75  	return vc.execute(query, bindVars)
    76  }
    77  
    78  func (vc *vcursor) execute(query string, bindvars map[string]*querypb.BindVariable) (*sqltypes.Result, error) {
    79  	vc.queries = append(vc.queries, &querypb.BoundQuery{
    80  		Sql:           query,
    81  		BindVariables: bindvars,
    82  	})
    83  	if vc.mustFail {
    84  		return nil, errors.New("execute failed")
    85  	}
    86  	switch {
    87  	case strings.HasPrefix(query, "select"):
    88  		if vc.result != nil {
    89  			return vc.result, nil
    90  		}
    91  		result := &sqltypes.Result{
    92  			Fields:       sqltypes.MakeTestFields("key|col", "int64|int32"),
    93  			RowsAffected: uint64(vc.numRows),
    94  		}
    95  		for i := 0; i < vc.numRows; i++ {
    96  			for j := 0; j < vc.numRows; j++ {
    97  				k := sqltypes.NewInt64(int64(j + 1))
    98  				if vc.keys != nil {
    99  					k = vc.keys[j]
   100  				}
   101  				result.Rows = append(result.Rows, []sqltypes.Value{
   102  					k, sqltypes.NewInt64(int64(i + 1)),
   103  				})
   104  			}
   105  		}
   106  		return result, nil
   107  	case strings.HasPrefix(query, "insert"):
   108  		return &sqltypes.Result{InsertID: 1}, nil
   109  	case strings.HasPrefix(query, "delete"):
   110  		return &sqltypes.Result{}, nil
   111  	}
   112  	panic("unexpected")
   113  }
   114  
   115  func TestLookupNonUniqueNew(t *testing.T) {
   116  	l := createLookup(t, "lookup", false /* writeOnly */)
   117  	assert.False(t, l.(*LookupNonUnique).writeOnly, "Create(lookup, false)")
   118  
   119  	l = createLookup(t, "lookup", true)
   120  	assert.True(t, l.(*LookupNonUnique).writeOnly, "Create(lookup, false)")
   121  
   122  	_, err := CreateVindex("lookup", "lookup", map[string]string{
   123  		"table":      "t",
   124  		"from":       "fromc",
   125  		"to":         "toc",
   126  		"write_only": "invalid",
   127  	})
   128  	require.EqualError(t, err, "write_only value must be 'true' or 'false': 'invalid'")
   129  }
   130  
   131  func TestLookupNonUniqueInfo(t *testing.T) {
   132  	lookupNonUnique := createLookup(t, "lookup", false /* writeOnly */)
   133  	assert.Equal(t, 20, lookupNonUnique.Cost())
   134  	assert.Equal(t, "lookup", lookupNonUnique.String())
   135  	assert.False(t, lookupNonUnique.IsUnique())
   136  	assert.True(t, lookupNonUnique.NeedsVCursor())
   137  }
   138  
   139  func TestLookupNilVCursor(t *testing.T) {
   140  	lookupNonUnique := createLookup(t, "lookup", false /* writeOnly */)
   141  	_, err := lookupNonUnique.Map(context.Background(), nil, []sqltypes.Value{sqltypes.NewInt64(1), sqltypes.NewInt64(2)})
   142  	require.EqualError(t, err, "cannot perform lookup: no vcursor provided")
   143  }
   144  
   145  func TestLookupNonUniqueMap(t *testing.T) {
   146  	lookupNonUnique := createLookup(t, "lookup", false /* writeOnly */)
   147  	vc := &vcursor{numRows: 2}
   148  
   149  	got, err := lookupNonUnique.Map(context.Background(), vc, []sqltypes.Value{sqltypes.NewInt64(1), sqltypes.NewInt64(2)})
   150  	require.NoError(t, err)
   151  	want := []key.Destination{
   152  		key.DestinationKeyspaceIDs([][]byte{
   153  			[]byte("1"),
   154  			[]byte("2"),
   155  		}),
   156  		key.DestinationKeyspaceIDs([][]byte{
   157  			[]byte("1"),
   158  			[]byte("2"),
   159  		}),
   160  	}
   161  	utils.MustMatch(t, want, got)
   162  
   163  	vars, err := sqltypes.BuildBindVariable([]any{sqltypes.NewInt64(1), sqltypes.NewInt64(2)})
   164  	require.NoError(t, err)
   165  	wantqueries := []*querypb.BoundQuery{{
   166  		Sql: "select fromc, toc from t where fromc in ::fromc",
   167  		BindVariables: map[string]*querypb.BindVariable{
   168  			"fromc": vars,
   169  		},
   170  	}}
   171  	utils.MustMatch(t, wantqueries, vc.queries, "lookup.Map")
   172  
   173  	// Test query fail.
   174  	vc.mustFail = true
   175  	_, err = lookupNonUnique.Map(context.Background(), vc, []sqltypes.Value{sqltypes.NewInt64(1)})
   176  	require.EqualError(t, err, "lookup.Map: execute failed")
   177  }
   178  
   179  func TestLookupNonUniqueMapAutocommit(t *testing.T) {
   180  	vindex, err := CreateVindex("lookup", "lookup", map[string]string{
   181  		"table":      "t",
   182  		"from":       "fromc",
   183  		"to":         "toc",
   184  		"autocommit": "true",
   185  	})
   186  	require.NoError(t, err)
   187  	lookupNonUnique := vindex.(SingleColumn)
   188  	vc := &vcursor{numRows: 2}
   189  
   190  	got, err := lookupNonUnique.Map(context.Background(), vc, []sqltypes.Value{sqltypes.NewInt64(1), sqltypes.NewInt64(2)})
   191  	require.NoError(t, err)
   192  	want := []key.Destination{
   193  		key.DestinationKeyspaceIDs([][]byte{
   194  			[]byte("1"),
   195  			[]byte("2"),
   196  		}),
   197  		key.DestinationKeyspaceIDs([][]byte{
   198  			[]byte("1"),
   199  			[]byte("2"),
   200  		}),
   201  	}
   202  	utils.MustMatch(t, want, got)
   203  
   204  	vars, err := sqltypes.BuildBindVariable([]any{sqltypes.NewInt64(1), sqltypes.NewInt64(2)})
   205  	require.NoError(t, err)
   206  	wantqueries := []*querypb.BoundQuery{{
   207  		Sql: "select fromc, toc from t where fromc in ::fromc",
   208  		BindVariables: map[string]*querypb.BindVariable{
   209  			"fromc": vars,
   210  		},
   211  	}}
   212  	utils.MustMatch(t, wantqueries, vc.queries)
   213  	assert.Equal(t, 1, vc.autocommits, "autocommits")
   214  }
   215  
   216  func TestLookupNonUniqueMapWriteOnly(t *testing.T) {
   217  	lookupNonUnique := createLookup(t, "lookup", true)
   218  	vc := &vcursor{numRows: 0}
   219  
   220  	got, err := lookupNonUnique.Map(context.Background(), vc, []sqltypes.Value{sqltypes.NewInt64(1), sqltypes.NewInt64(2)})
   221  	require.NoError(t, err)
   222  	want := []key.Destination{
   223  		key.DestinationKeyRange{
   224  			KeyRange: &topodatapb.KeyRange{},
   225  		},
   226  		key.DestinationKeyRange{
   227  			KeyRange: &topodatapb.KeyRange{},
   228  		},
   229  	}
   230  	utils.MustMatch(t, want, got)
   231  }
   232  
   233  func TestLookupNonUniqueMapAbsent(t *testing.T) {
   234  	lookupNonUnique := createLookup(t, "lookup", false /* writeOnly */)
   235  	vc := &vcursor{numRows: 0}
   236  
   237  	got, err := lookupNonUnique.Map(context.Background(), vc, []sqltypes.Value{sqltypes.NewInt64(1), sqltypes.NewInt64(2)})
   238  	require.NoError(t, err)
   239  	want := []key.Destination{
   240  		key.DestinationNone{},
   241  		key.DestinationNone{},
   242  	}
   243  	utils.MustMatch(t, want, got)
   244  }
   245  
   246  func TestLookupNonUniqueVerify(t *testing.T) {
   247  	lookupNonUnique := createLookup(t, "lookup", false /* writeOnly */)
   248  	vc := &vcursor{numRows: 1}
   249  
   250  	_, err := lookupNonUnique.Verify(context.Background(), vc, []sqltypes.Value{sqltypes.NewInt64(1), sqltypes.NewInt64(2)}, [][]byte{[]byte("test1"), []byte("test2")})
   251  	require.NoError(t, err)
   252  
   253  	wantqueries := []*querypb.BoundQuery{{
   254  		Sql: "select fromc from t where fromc = :fromc and toc = :toc",
   255  		BindVariables: map[string]*querypb.BindVariable{
   256  			"fromc": sqltypes.Int64BindVariable(1),
   257  			"toc":   sqltypes.BytesBindVariable([]byte("test1")),
   258  		},
   259  	}, {
   260  		Sql: "select fromc from t where fromc = :fromc and toc = :toc",
   261  		BindVariables: map[string]*querypb.BindVariable{
   262  			"fromc": sqltypes.Int64BindVariable(2),
   263  			"toc":   sqltypes.BytesBindVariable([]byte("test2")),
   264  		},
   265  	}}
   266  	utils.MustMatch(t, wantqueries, vc.queries)
   267  
   268  	// Test query fail.
   269  	vc.mustFail = true
   270  	_, err = lookupNonUnique.Verify(context.Background(), vc, []sqltypes.Value{sqltypes.NewInt64(1)}, [][]byte{[]byte("\x16k@\xb4J\xbaK\xd6")})
   271  	require.EqualError(t, err, "lookup.Verify: execute failed")
   272  	vc.mustFail = false
   273  
   274  	// writeOnly true should always yield true.
   275  	lookupNonUnique = createLookup(t, "lookup", true)
   276  	vc.queries = nil
   277  
   278  	got, err := lookupNonUnique.Verify(context.Background(), vc, []sqltypes.Value{sqltypes.NewInt64(1), sqltypes.NewInt64(2)}, [][]byte{[]byte(""), []byte("")})
   279  	require.NoError(t, err)
   280  	assert.Empty(t, vc.queries, "lookup verify queries")
   281  	utils.MustMatch(t, []bool{true, true}, got)
   282  }
   283  
   284  func TestLookupNonUniqueNoVerify(t *testing.T) {
   285  	vindex, err := CreateVindex("lookup", "lookup", map[string]string{
   286  		"table":     "t",
   287  		"from":      "fromc",
   288  		"to":        "toc",
   289  		"no_verify": "true",
   290  	})
   291  	require.NoError(t, err)
   292  	lookupNonUnique := vindex.(SingleColumn)
   293  	vc := &vcursor{numRows: 1}
   294  
   295  	_, err = lookupNonUnique.Verify(context.Background(), vc, []sqltypes.Value{sqltypes.NewInt64(1), sqltypes.NewInt64(2)}, [][]byte{[]byte("test1"), []byte("test2")})
   296  	require.NoError(t, err)
   297  
   298  	var wantqueries []*querypb.BoundQuery
   299  	utils.MustMatch(t, vc.queries, wantqueries)
   300  
   301  	// Test query fail.
   302  	vc.mustFail = true
   303  	_, err = lookupNonUnique.Verify(context.Background(), vc, []sqltypes.Value{sqltypes.NewInt64(1)}, [][]byte{[]byte("\x16k@\xb4J\xbaK\xd6")})
   304  	require.NoError(t, err)
   305  }
   306  
   307  func TestLookupUniqueNoVerify(t *testing.T) {
   308  	vindex, err := CreateVindex("lookup_unique", "lookup_unique", map[string]string{
   309  		"table":     "t",
   310  		"from":      "fromc",
   311  		"to":        "toc",
   312  		"no_verify": "true",
   313  	})
   314  	require.NoError(t, err)
   315  	lookupUnique := vindex.(SingleColumn)
   316  	vc := &vcursor{numRows: 1}
   317  
   318  	_, err = lookupUnique.Verify(context.Background(), vc, []sqltypes.Value{sqltypes.NewInt64(1), sqltypes.NewInt64(2)}, [][]byte{[]byte("test1"), []byte("test2")})
   319  	require.NoError(t, err)
   320  
   321  	var wantqueries []*querypb.BoundQuery
   322  	utils.MustMatch(t, vc.queries, wantqueries)
   323  
   324  	// Test query fail.
   325  	vc.mustFail = true
   326  	_, err = lookupUnique.Verify(context.Background(), vc, []sqltypes.Value{sqltypes.NewInt64(1)}, [][]byte{[]byte("\x16k@\xb4J\xbaK\xd6")})
   327  	require.NoError(t, err)
   328  }
   329  
   330  func TestLookupNonUniqueVerifyAutocommit(t *testing.T) {
   331  	vindex, err := CreateVindex("lookup", "lookup", map[string]string{
   332  		"table":      "t",
   333  		"from":       "fromc",
   334  		"to":         "toc",
   335  		"autocommit": "true",
   336  	})
   337  	require.NoError(t, err)
   338  	lookupNonUnique := vindex.(SingleColumn)
   339  	vc := &vcursor{numRows: 1}
   340  
   341  	_, err = lookupNonUnique.Verify(context.Background(), vc, []sqltypes.Value{sqltypes.NewInt64(1), sqltypes.NewInt64(2)}, [][]byte{[]byte("test1"), []byte("test2")})
   342  	require.NoError(t, err)
   343  
   344  	wantqueries := []*querypb.BoundQuery{{
   345  		Sql: "select fromc from t where fromc = :fromc and toc = :toc",
   346  		BindVariables: map[string]*querypb.BindVariable{
   347  			"fromc": sqltypes.Int64BindVariable(1),
   348  			"toc":   sqltypes.BytesBindVariable([]byte("test1")),
   349  		},
   350  	}, {
   351  		Sql: "select fromc from t where fromc = :fromc and toc = :toc",
   352  		BindVariables: map[string]*querypb.BindVariable{
   353  			"fromc": sqltypes.Int64BindVariable(2),
   354  			"toc":   sqltypes.BytesBindVariable([]byte("test2")),
   355  		},
   356  	}}
   357  
   358  	utils.MustMatch(t, wantqueries, vc.queries)
   359  	assert.Equal(t, 2, vc.autocommits, "autocommits")
   360  }
   361  
   362  func TestLookupNonUniqueCreate(t *testing.T) {
   363  	lookupNonUnique := createLookup(t, "lookup", false /* writeOnly */)
   364  	vc := &vcursor{}
   365  
   366  	err := lookupNonUnique.(Lookup).Create(context.Background(), vc, [][]sqltypes.Value{{sqltypes.NewInt64(1)}, {sqltypes.NewInt64(2)}}, [][]byte{[]byte("test1"), []byte("test2")}, false /* ignoreMode */)
   367  	require.NoError(t, err)
   368  
   369  	wantqueries := []*querypb.BoundQuery{{
   370  		Sql: "insert into t(fromc, toc) values(:fromc_0, :toc_0), (:fromc_1, :toc_1)",
   371  		BindVariables: map[string]*querypb.BindVariable{
   372  			"fromc_0": sqltypes.Int64BindVariable(1),
   373  			"toc_0":   sqltypes.BytesBindVariable([]byte("test1")),
   374  			"fromc_1": sqltypes.Int64BindVariable(2),
   375  			"toc_1":   sqltypes.BytesBindVariable([]byte("test2")),
   376  		},
   377  	}}
   378  	utils.MustMatch(t, wantqueries, vc.queries)
   379  
   380  	// With ignore.
   381  	vc.queries = nil
   382  	err = lookupNonUnique.(Lookup).Create(context.Background(), vc, [][]sqltypes.Value{{sqltypes.NewInt64(2)}, {sqltypes.NewInt64(1)}}, [][]byte{[]byte("test2"), []byte("test1")}, true /* ignoreMode */)
   383  	require.NoError(t, err)
   384  	wantqueries[0].Sql = "insert ignore into t(fromc, toc) values(:fromc_0, :toc_0), (:fromc_1, :toc_1)"
   385  	utils.MustMatch(t, wantqueries, vc.queries)
   386  
   387  	// With ignore_nulls off
   388  	err = lookupNonUnique.(Lookup).Create(context.Background(), vc, [][]sqltypes.Value{{sqltypes.NewInt64(2)}, {sqltypes.NULL}}, [][]byte{[]byte("test2"), []byte("test1")}, true /* ignoreMode */)
   389  	assert.EqualError(t, err, "lookup.Create: input has null values: row: 1, col: 0")
   390  
   391  	// With ignore_nulls on
   392  	vc.queries = nil
   393  	lookupNonUnique.(*LookupNonUnique).lkp.IgnoreNulls = true
   394  	err = lookupNonUnique.(Lookup).Create(context.Background(), vc, [][]sqltypes.Value{{sqltypes.NewInt64(2)}, {sqltypes.NULL}}, [][]byte{[]byte("test2"), []byte("test1")}, true /* ignoreMode */)
   395  	require.NoError(t, err)
   396  	wantqueries = []*querypb.BoundQuery{{
   397  		Sql: "insert ignore into t(fromc, toc) values(:fromc_0, :toc_0)",
   398  		BindVariables: map[string]*querypb.BindVariable{
   399  			"fromc_0": sqltypes.Int64BindVariable(2),
   400  			"toc_0":   sqltypes.BytesBindVariable([]byte("test2")),
   401  		},
   402  	}}
   403  	utils.MustMatch(t, wantqueries, vc.queries)
   404  
   405  	// Test query fail.
   406  	vc.mustFail = true
   407  	err = lookupNonUnique.(Lookup).Create(context.Background(), vc, [][]sqltypes.Value{{sqltypes.NewInt64(1)}}, [][]byte{[]byte("\x16k@\xb4J\xbaK\xd6")}, false /* ignoreMode */)
   408  	assert.EqualError(t, err, "lookup.Create: execute failed")
   409  	vc.mustFail = false
   410  
   411  	// Test column mismatch.
   412  	err = lookupNonUnique.(Lookup).Create(context.Background(), vc, [][]sqltypes.Value{{sqltypes.NewInt64(1), sqltypes.NewInt64(2)}}, [][]byte{[]byte("\x16k@\xb4J\xbaK\xd6")}, false /* ignoreMode */)
   413  	assert.EqualError(t, err, "lookup.Create: column vindex count does not match the columns in the lookup: 2 vs [fromc]")
   414  }
   415  
   416  func TestLookupNonUniqueCreateAutocommit(t *testing.T) {
   417  	lookupNonUnique, err := CreateVindex("lookup", "lookup", map[string]string{
   418  		"table":      "t",
   419  		"from":       "from1,from2",
   420  		"to":         "toc",
   421  		"autocommit": "true",
   422  	})
   423  	require.NoError(t, err)
   424  	vc := &vcursor{}
   425  
   426  	err = lookupNonUnique.(Lookup).Create(context.Background(), vc, [][]sqltypes.Value{{
   427  		sqltypes.NewInt64(1), sqltypes.NewInt64(2),
   428  	}, {
   429  		sqltypes.NewInt64(3), sqltypes.NewInt64(4),
   430  	}}, [][]byte{[]byte("test1"), []byte("test2")}, false /* ignoreMode */)
   431  	require.NoError(t, err)
   432  
   433  	wantqueries := []*querypb.BoundQuery{{
   434  		Sql: "insert into t(from1, from2, toc) values(:from1_0, :from2_0, :toc_0), (:from1_1, :from2_1, :toc_1) on duplicate key update from1=values(from1), from2=values(from2), toc=values(toc)",
   435  		BindVariables: map[string]*querypb.BindVariable{
   436  			"from1_0": sqltypes.Int64BindVariable(1),
   437  			"from2_0": sqltypes.Int64BindVariable(2),
   438  			"toc_0":   sqltypes.BytesBindVariable([]byte("test1")),
   439  			"from1_1": sqltypes.Int64BindVariable(3),
   440  			"from2_1": sqltypes.Int64BindVariable(4),
   441  			"toc_1":   sqltypes.BytesBindVariable([]byte("test2")),
   442  		},
   443  	}}
   444  	utils.MustMatch(t, wantqueries, vc.queries)
   445  	assert.Equal(t, 1, vc.autocommits, "autocommits")
   446  }
   447  
   448  func TestLookupNonUniqueDelete(t *testing.T) {
   449  	lookupNonUnique := createLookup(t, "lookup", false /* writeOnly */)
   450  	vc := &vcursor{}
   451  
   452  	err := lookupNonUnique.(Lookup).Delete(context.Background(), vc, [][]sqltypes.Value{{sqltypes.NewInt64(1)}, {sqltypes.NewInt64(2)}}, []byte("test"))
   453  	require.NoError(t, err)
   454  
   455  	wantqueries := []*querypb.BoundQuery{{
   456  		Sql: "delete from t where fromc = :fromc and toc = :toc",
   457  		BindVariables: map[string]*querypb.BindVariable{
   458  			"fromc": sqltypes.Int64BindVariable(1),
   459  			"toc":   sqltypes.BytesBindVariable([]byte("test")),
   460  		},
   461  	}, {
   462  		Sql: "delete from t where fromc = :fromc and toc = :toc",
   463  		BindVariables: map[string]*querypb.BindVariable{
   464  			"fromc": sqltypes.Int64BindVariable(2),
   465  			"toc":   sqltypes.BytesBindVariable([]byte("test")),
   466  		},
   467  	}}
   468  	utils.MustMatch(t, wantqueries, vc.queries)
   469  
   470  	// Test query fail.
   471  	vc.mustFail = true
   472  	err = lookupNonUnique.(Lookup).Delete(context.Background(), vc, [][]sqltypes.Value{{sqltypes.NewInt64(1)}}, []byte("\x16k@\xb4J\xbaK\xd6"))
   473  	assert.EqualError(t, err, "lookup.Delete: execute failed")
   474  	vc.mustFail = false
   475  
   476  	// Test column count fail.
   477  	err = lookupNonUnique.(Lookup).Delete(context.Background(), vc, [][]sqltypes.Value{{sqltypes.NewInt64(1), sqltypes.NewInt64(2)}}, []byte("\x16k@\xb4J\xbaK\xd6"))
   478  	assert.EqualError(t, err, "lookup.Delete: column vindex count does not match the columns in the lookup: 2 vs [fromc]")
   479  }
   480  
   481  func TestLookupNonUniqueDeleteAutocommit(t *testing.T) {
   482  	lookupNonUnique, _ := CreateVindex("lookup", "lookup", map[string]string{
   483  		"table":      "t",
   484  		"from":       "fromc",
   485  		"to":         "toc",
   486  		"autocommit": "true",
   487  	})
   488  	vc := &vcursor{}
   489  
   490  	err := lookupNonUnique.(Lookup).Delete(context.Background(), vc, [][]sqltypes.Value{{sqltypes.NewInt64(1)}, {sqltypes.NewInt64(2)}}, []byte("test"))
   491  	require.NoError(t, err)
   492  
   493  	utils.MustMatch(t, []*querypb.BoundQuery(nil), vc.queries)
   494  }
   495  
   496  func TestLookupNonUniqueUpdate(t *testing.T) {
   497  	lookupNonUnique := createLookup(t, "lookup", false /* writeOnly */)
   498  	vc := &vcursor{}
   499  
   500  	err := lookupNonUnique.(Lookup).Update(context.Background(), vc, []sqltypes.Value{sqltypes.NewInt64(1)}, []byte("test"), []sqltypes.Value{sqltypes.NewInt64(2)})
   501  	require.NoError(t, err)
   502  
   503  	wantqueries := []*querypb.BoundQuery{{
   504  		Sql: "delete from t where fromc = :fromc and toc = :toc",
   505  		BindVariables: map[string]*querypb.BindVariable{
   506  			"fromc": sqltypes.Int64BindVariable(1),
   507  			"toc":   sqltypes.BytesBindVariable([]byte("test")),
   508  		},
   509  	}, {
   510  		Sql: "insert into t(fromc, toc) values(:fromc_0, :toc_0)",
   511  		BindVariables: map[string]*querypb.BindVariable{
   512  			"fromc_0": sqltypes.Int64BindVariable(2),
   513  			"toc_0":   sqltypes.BytesBindVariable([]byte("test")),
   514  		},
   515  	}}
   516  	utils.MustMatch(t, wantqueries, vc.queries)
   517  }
   518  
   519  func TestLookupMapResult(t *testing.T) {
   520  	lookup := createLookup(t, "lookup", false)
   521  
   522  	ids := []sqltypes.Value{sqltypes.NewInt64(1), sqltypes.NewInt64(2)}
   523  	results := []*sqltypes.Result{{
   524  		Fields:       sqltypes.MakeTestFields("key|col", "int64|int32"),
   525  		RowsAffected: 2,
   526  		Rows: []sqltypes.Row{
   527  			{sqltypes.NewInt64(1), sqltypes.NewInt64(3)},
   528  			{sqltypes.NewInt64(3), sqltypes.NewInt64(4)},
   529  			{sqltypes.NewInt64(5), sqltypes.NewInt64(6)},
   530  		},
   531  	}}
   532  
   533  	got, err := lookup.(LookupPlanable).MapResult(ids, results)
   534  	require.NoError(t, err)
   535  	want := []key.Destination{
   536  		key.DestinationKeyspaceIDs([][]byte{
   537  			[]byte("1"),
   538  			[]byte("3"),
   539  			[]byte("5"),
   540  		}),
   541  	}
   542  	utils.MustMatch(t, want, got)
   543  }
   544  
   545  func TestLookupUniqueMapResult(t *testing.T) {
   546  	lookup := createLookup(t, "lookup_unique", false)
   547  
   548  	ids := []sqltypes.Value{sqltypes.NewInt64(1), sqltypes.NewInt64(2)}
   549  	results := []*sqltypes.Result{{
   550  		Fields:       sqltypes.MakeTestFields("key|col", "int64|int32"),
   551  		RowsAffected: 2,
   552  		Rows: []sqltypes.Row{
   553  			{sqltypes.NewInt64(1), sqltypes.NewInt64(3)},
   554  		},
   555  	}}
   556  
   557  	got, err := lookup.(LookupPlanable).MapResult(ids, results)
   558  	require.NoError(t, err)
   559  	want := []key.Destination{
   560  		key.DestinationKeyspaceID("1"),
   561  	}
   562  	utils.MustMatch(t, want, got)
   563  
   564  	results[0].Rows = append(results[0].Rows, results[0].Rows...)
   565  	_, err = lookup.(LookupPlanable).MapResult(ids, results)
   566  	require.Error(t, err)
   567  }
   568  
   569  func TestLookupNonUniqueCreateMultiShardAutocommit(t *testing.T) {
   570  	lookupNonUnique, err := CreateVindex("lookup", "lookup", map[string]string{
   571  		"table":                  "t",
   572  		"from":                   "from1,from2",
   573  		"to":                     "toc",
   574  		"multi_shard_autocommit": "true",
   575  	})
   576  	require.NoError(t, err)
   577  
   578  	vc := &vcursor{}
   579  	err = lookupNonUnique.(Lookup).Create(context.Background(), vc, [][]sqltypes.Value{{
   580  		sqltypes.NewInt64(1), sqltypes.NewInt64(2),
   581  	}, {
   582  		sqltypes.NewInt64(3), sqltypes.NewInt64(4),
   583  	}}, [][]byte{[]byte("test1"), []byte("test2")}, false /* ignoreMode */)
   584  	require.NoError(t, err)
   585  
   586  	wantqueries := []*querypb.BoundQuery{{
   587  		Sql: "insert /*vt+ MULTI_SHARD_AUTOCOMMIT=1 */ into t(from1, from2, toc) values(:from1_0, :from2_0, :toc_0), (:from1_1, :from2_1, :toc_1) on duplicate key update from1=values(from1), from2=values(from2), toc=values(toc)",
   588  		BindVariables: map[string]*querypb.BindVariable{
   589  			"from1_0": sqltypes.Int64BindVariable(1),
   590  			"from2_0": sqltypes.Int64BindVariable(2),
   591  			"toc_0":   sqltypes.BytesBindVariable([]byte("test1")),
   592  			"from1_1": sqltypes.Int64BindVariable(3),
   593  			"from2_1": sqltypes.Int64BindVariable(4),
   594  			"toc_1":   sqltypes.BytesBindVariable([]byte("test2")),
   595  		},
   596  	}}
   597  	utils.MustMatch(t, vc.queries, wantqueries)
   598  	require.Equal(t, 1, vc.autocommits, "Create(autocommit) count")
   599  }
   600  
   601  func createLookup(t *testing.T, name string, writeOnly bool) SingleColumn {
   602  	t.Helper()
   603  	write := "false"
   604  	if writeOnly {
   605  		write = "true"
   606  	}
   607  	l, err := CreateVindex(name, name, map[string]string{
   608  		"table":      "t",
   609  		"from":       "fromc",
   610  		"to":         "toc",
   611  		"write_only": write,
   612  	})
   613  	require.NoError(t, err)
   614  	return l.(SingleColumn)
   615  }