vitess.io/vitess@v0.16.2/go/vt/vttablet/tabletmanager/vdiff/workflow_differ_test.go (about)

     1  /*
     2  Copyright 2022 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 vdiff
    18  
    19  import (
    20  	"context"
    21  	"fmt"
    22  	"strings"
    23  	"testing"
    24  
    25  	"github.com/google/uuid"
    26  	"github.com/stretchr/testify/assert"
    27  	"github.com/stretchr/testify/require"
    28  
    29  	"vitess.io/vitess/go/mysql/collations"
    30  	"vitess.io/vitess/go/sqltypes"
    31  	"vitess.io/vitess/go/vt/binlog/binlogplayer"
    32  	binlogdatapb "vitess.io/vitess/go/vt/proto/binlogdata"
    33  	tabletmanagerdatapb "vitess.io/vitess/go/vt/proto/tabletmanagerdata"
    34  	"vitess.io/vitess/go/vt/sqlparser"
    35  	"vitess.io/vitess/go/vt/vtgate/engine"
    36  )
    37  
    38  func TestBuildPlanSuccess(t *testing.T) {
    39  	vdenv := newTestVDiffEnv(t)
    40  	defer vdenv.close()
    41  	UUID := uuid.New()
    42  	controllerQR := sqltypes.MakeTestResult(sqltypes.MakeTestFields(
    43  		vdiffTestCols,
    44  		vdiffTestColTypes,
    45  	),
    46  		fmt.Sprintf("1|%s|%s|%s|%s|%s|%s|%s|", UUID, vdiffenv.workflow, tstenv.KeyspaceName, tstenv.ShardName, vdiffDBName, PendingState, optionsJS),
    47  	)
    48  
    49  	vdiffenv.dbClient.ExpectRequest("select * from _vt.vdiff where id = 1", noResults, nil)
    50  	ct, err := newController(context.Background(), controllerQR.Named().Row(), vdiffenv.dbClientFactory, tstenv.TopoServ, vdiffenv.vde, vdiffenv.opts)
    51  	require.NoError(t, err)
    52  
    53  	testcases := []struct {
    54  		input          *binlogdatapb.Rule
    55  		table          string
    56  		tablePlan      *tablePlan
    57  		sourceTimeZone string
    58  	}{{
    59  		input: &binlogdatapb.Rule{
    60  			Match: "t1",
    61  		},
    62  		table: "t1",
    63  		tablePlan: &tablePlan{
    64  			table:       testSchema.TableDefinitions[tableDefMap["t1"]],
    65  			sourceQuery: "select c1, c2 from t1 order by c1 asc",
    66  			targetQuery: "select c1, c2 from t1 order by c1 asc",
    67  			compareCols: []compareColInfo{{0, collations.Collation(nil), true, "c1"}, {1, collations.Collation(nil), false, "c2"}},
    68  			comparePKs:  []compareColInfo{{0, collations.Collation(nil), true, "c1"}},
    69  			pkCols:      []int{0},
    70  			selectPks:   []int{0},
    71  			orderBy: sqlparser.OrderBy{&sqlparser.Order{
    72  				Expr:      &sqlparser.ColName{Name: sqlparser.NewIdentifierCI("c1")},
    73  				Direction: sqlparser.AscOrder,
    74  			}},
    75  		},
    76  	}, {
    77  		input: &binlogdatapb.Rule{
    78  			Match:  "t1",
    79  			Filter: "-80",
    80  		},
    81  		table: "t1",
    82  		tablePlan: &tablePlan{
    83  			table:       testSchema.TableDefinitions[tableDefMap["t1"]],
    84  			sourceQuery: "select c1, c2 from t1 where in_keyrange('-80') order by c1 asc",
    85  			targetQuery: "select c1, c2 from t1 order by c1 asc",
    86  			compareCols: []compareColInfo{{0, collations.Collation(nil), true, "c1"}, {1, collations.Collation(nil), false, "c2"}},
    87  			comparePKs:  []compareColInfo{{0, collations.Collation(nil), true, "c1"}},
    88  			pkCols:      []int{0},
    89  			selectPks:   []int{0},
    90  			orderBy: sqlparser.OrderBy{&sqlparser.Order{
    91  				Expr:      &sqlparser.ColName{Name: sqlparser.NewIdentifierCI("c1")},
    92  				Direction: sqlparser.AscOrder,
    93  			}},
    94  		},
    95  	}, {
    96  		input: &binlogdatapb.Rule{
    97  			Match:  "t1",
    98  			Filter: "select * from t1",
    99  		},
   100  		table: "t1",
   101  		tablePlan: &tablePlan{
   102  			table:       testSchema.TableDefinitions[tableDefMap["t1"]],
   103  			sourceQuery: "select c1, c2 from t1 order by c1 asc",
   104  			targetQuery: "select c1, c2 from t1 order by c1 asc",
   105  			compareCols: []compareColInfo{{0, collations.Collation(nil), true, "c1"}, {1, collations.Collation(nil), false, "c2"}},
   106  			comparePKs:  []compareColInfo{{0, collations.Collation(nil), true, "c1"}},
   107  			pkCols:      []int{0},
   108  			selectPks:   []int{0},
   109  			orderBy: sqlparser.OrderBy{&sqlparser.Order{
   110  				Expr:      &sqlparser.ColName{Name: sqlparser.NewIdentifierCI("c1")},
   111  				Direction: sqlparser.AscOrder,
   112  			}},
   113  		},
   114  	}, {
   115  		input: &binlogdatapb.Rule{
   116  			Match:  "t1",
   117  			Filter: "select c2, c1 from t1",
   118  		},
   119  		table: "t1",
   120  		tablePlan: &tablePlan{
   121  			table:       testSchema.TableDefinitions[tableDefMap["t1"]],
   122  			sourceQuery: "select c2, c1 from t1 order by c1 asc",
   123  			targetQuery: "select c2, c1 from t1 order by c1 asc",
   124  			compareCols: []compareColInfo{{0, collations.Collation(nil), false, "c2"}, {1, collations.Collation(nil), true, "c1"}},
   125  			comparePKs:  []compareColInfo{{1, collations.Collation(nil), true, "c1"}},
   126  			pkCols:      []int{1},
   127  			selectPks:   []int{1},
   128  			orderBy: sqlparser.OrderBy{&sqlparser.Order{
   129  				Expr:      &sqlparser.ColName{Name: sqlparser.NewIdentifierCI("c1")},
   130  				Direction: sqlparser.AscOrder,
   131  			}},
   132  		},
   133  	}, {
   134  		input: &binlogdatapb.Rule{
   135  			Match:  "t1",
   136  			Filter: "select c0 as c1, c2 from t2",
   137  		},
   138  		table: "t1",
   139  		tablePlan: &tablePlan{
   140  			table:       testSchema.TableDefinitions[tableDefMap["t1"]],
   141  			sourceQuery: "select c0 as c1, c2 from t2 order by c1 asc",
   142  			targetQuery: "select c1, c2 from t1 order by c1 asc",
   143  			compareCols: []compareColInfo{{0, collations.Collation(nil), true, "c1"}, {1, collations.Collation(nil), false, "c2"}},
   144  			comparePKs:  []compareColInfo{{0, collations.Collation(nil), true, "c1"}},
   145  			pkCols:      []int{0},
   146  			selectPks:   []int{0},
   147  			orderBy: sqlparser.OrderBy{&sqlparser.Order{
   148  				Expr:      &sqlparser.ColName{Name: sqlparser.NewIdentifierCI("c1")},
   149  				Direction: sqlparser.AscOrder,
   150  			}},
   151  		},
   152  	}, {
   153  		// non-pk text column.
   154  		input: &binlogdatapb.Rule{
   155  			Match:  "nonpktext",
   156  			Filter: "select c1, textcol from nonpktext",
   157  		},
   158  		table: "nonpktext",
   159  		tablePlan: &tablePlan{
   160  			table:       testSchema.TableDefinitions[tableDefMap["nonpktext"]],
   161  			sourceQuery: "select c1, textcol from nonpktext order by c1 asc",
   162  			targetQuery: "select c1, textcol from nonpktext order by c1 asc",
   163  			compareCols: []compareColInfo{{0, collations.Collation(nil), true, "c1"}, {1, collations.Collation(nil), false, "textcol"}},
   164  			comparePKs:  []compareColInfo{{0, collations.Collation(nil), true, "c1"}},
   165  			pkCols:      []int{0},
   166  			selectPks:   []int{0},
   167  			orderBy: sqlparser.OrderBy{&sqlparser.Order{
   168  				Expr:      &sqlparser.ColName{Name: sqlparser.NewIdentifierCI("c1")},
   169  				Direction: sqlparser.AscOrder,
   170  			}},
   171  		},
   172  	}, {
   173  		// non-pk text column, different order.
   174  		input: &binlogdatapb.Rule{
   175  			Match:  "nonpktext",
   176  			Filter: "select textcol, c1 from nonpktext",
   177  		},
   178  		table: "nonpktext",
   179  		tablePlan: &tablePlan{
   180  			table:       testSchema.TableDefinitions[tableDefMap["nonpktext"]],
   181  			sourceQuery: "select textcol, c1 from nonpktext order by c1 asc",
   182  			targetQuery: "select textcol, c1 from nonpktext order by c1 asc",
   183  			compareCols: []compareColInfo{{0, collations.Collation(nil), false, "textcol"}, {1, collations.Collation(nil), true, "c1"}},
   184  			comparePKs:  []compareColInfo{{1, collations.Collation(nil), true, "c1"}},
   185  			pkCols:      []int{1},
   186  			selectPks:   []int{1},
   187  			orderBy: sqlparser.OrderBy{&sqlparser.Order{
   188  				Expr:      &sqlparser.ColName{Name: sqlparser.NewIdentifierCI("c1")},
   189  				Direction: sqlparser.AscOrder,
   190  			}},
   191  		},
   192  	}, {
   193  		// pk text column.
   194  		input: &binlogdatapb.Rule{
   195  			Match:  "pktext",
   196  			Filter: "select textcol, c2 from pktext",
   197  		},
   198  		table: "pktext",
   199  		tablePlan: &tablePlan{
   200  			table:       testSchema.TableDefinitions[tableDefMap["pktext"]],
   201  			sourceQuery: "select textcol, c2 from pktext order by textcol asc",
   202  			targetQuery: "select textcol, c2 from pktext order by textcol asc",
   203  			compareCols: []compareColInfo{{0, collations.Collation(nil), true, "textcol"}, {1, collations.Collation(nil), false, "c2"}},
   204  			comparePKs:  []compareColInfo{{0, collations.Collation(nil), true, "textcol"}},
   205  			pkCols:      []int{0},
   206  			selectPks:   []int{0},
   207  			orderBy: sqlparser.OrderBy{&sqlparser.Order{
   208  				Expr:      &sqlparser.ColName{Name: sqlparser.NewIdentifierCI("textcol")},
   209  				Direction: sqlparser.AscOrder,
   210  			}},
   211  		},
   212  	}, {
   213  		// pk text column, different order.
   214  		input: &binlogdatapb.Rule{
   215  			Match:  "pktext",
   216  			Filter: "select c2, textcol from pktext",
   217  		},
   218  		table: "pktext",
   219  		tablePlan: &tablePlan{
   220  			table:       testSchema.TableDefinitions[tableDefMap["pktext"]],
   221  			sourceQuery: "select c2, textcol from pktext order by textcol asc",
   222  			targetQuery: "select c2, textcol from pktext order by textcol asc",
   223  			compareCols: []compareColInfo{{0, collations.Collation(nil), false, "c2"}, {1, collations.Collation(nil), true, "textcol"}},
   224  			comparePKs:  []compareColInfo{{1, collations.Collation(nil), true, "textcol"}},
   225  			pkCols:      []int{1},
   226  			selectPks:   []int{1},
   227  			orderBy: sqlparser.OrderBy{&sqlparser.Order{
   228  				Expr:      &sqlparser.ColName{Name: sqlparser.NewIdentifierCI("textcol")},
   229  				Direction: sqlparser.AscOrder,
   230  			}},
   231  		},
   232  	}, {
   233  		// text column as expression.
   234  		input: &binlogdatapb.Rule{
   235  			Match:  "pktext",
   236  			Filter: "select c2, a+b as textcol from pktext",
   237  		},
   238  		table: "pktext",
   239  		tablePlan: &tablePlan{
   240  			table:       testSchema.TableDefinitions[tableDefMap["pktext"]],
   241  			sourceQuery: "select c2, a + b as textcol from pktext order by textcol asc",
   242  			targetQuery: "select c2, textcol from pktext order by textcol asc",
   243  			compareCols: []compareColInfo{{0, collations.Collation(nil), false, "c2"}, {1, collations.Collation(nil), true, "textcol"}},
   244  			comparePKs:  []compareColInfo{{1, collations.Collation(nil), true, "textcol"}},
   245  			pkCols:      []int{1},
   246  			selectPks:   []int{1},
   247  			orderBy: sqlparser.OrderBy{&sqlparser.Order{
   248  				Expr:      &sqlparser.ColName{Name: sqlparser.NewIdentifierCI("textcol")},
   249  				Direction: sqlparser.AscOrder,
   250  			}},
   251  		},
   252  	}, {
   253  		// multiple pk columns.
   254  		input: &binlogdatapb.Rule{
   255  			Match: "multipk",
   256  		},
   257  		table: "multipk",
   258  		tablePlan: &tablePlan{
   259  			table:       testSchema.TableDefinitions[tableDefMap["multipk"]],
   260  			sourceQuery: "select c1, c2 from multipk order by c1 asc, c2 asc",
   261  			targetQuery: "select c1, c2 from multipk order by c1 asc, c2 asc",
   262  			compareCols: []compareColInfo{{0, collations.Collation(nil), true, "c1"}, {1, collations.Collation(nil), true, "c2"}},
   263  			comparePKs:  []compareColInfo{{0, collations.Collation(nil), true, "c1"}, {1, collations.Collation(nil), true, "c2"}},
   264  			pkCols:      []int{0, 1},
   265  			selectPks:   []int{0, 1},
   266  			orderBy: sqlparser.OrderBy{
   267  				&sqlparser.Order{
   268  					Expr:      &sqlparser.ColName{Name: sqlparser.NewIdentifierCI("c1")},
   269  					Direction: sqlparser.AscOrder,
   270  				},
   271  				&sqlparser.Order{
   272  					Expr:      &sqlparser.ColName{Name: sqlparser.NewIdentifierCI("c2")},
   273  					Direction: sqlparser.AscOrder,
   274  				},
   275  			},
   276  		},
   277  	}, {
   278  		// in_keyrange
   279  		input: &binlogdatapb.Rule{
   280  			Match:  "t1",
   281  			Filter: "select * from t1 where in_keyrange('-80')",
   282  		},
   283  		table: "t1",
   284  		tablePlan: &tablePlan{
   285  			table:       testSchema.TableDefinitions[tableDefMap["t1"]],
   286  			sourceQuery: "select c1, c2 from t1 where in_keyrange('-80') order by c1 asc",
   287  			targetQuery: "select c1, c2 from t1 order by c1 asc",
   288  			compareCols: []compareColInfo{{0, collations.Collation(nil), true, "c1"}, {1, collations.Collation(nil), false, "c2"}},
   289  			comparePKs:  []compareColInfo{{0, collations.Collation(nil), true, "c1"}},
   290  			pkCols:      []int{0},
   291  			selectPks:   []int{0},
   292  			orderBy: sqlparser.OrderBy{&sqlparser.Order{
   293  				Expr:      &sqlparser.ColName{Name: sqlparser.NewIdentifierCI("c1")},
   294  				Direction: sqlparser.AscOrder,
   295  			}},
   296  		},
   297  	}, {
   298  		// in_keyrange on RHS of AND.
   299  		// This is currently not a valid construct, but will be supported in the future.
   300  		input: &binlogdatapb.Rule{
   301  			Match:  "t1",
   302  			Filter: "select * from t1 where c2 = 2 and in_keyrange('-80')",
   303  		},
   304  		table: "t1",
   305  		tablePlan: &tablePlan{
   306  			table:       testSchema.TableDefinitions[tableDefMap["t1"]],
   307  			sourceQuery: "select c1, c2 from t1 where c2 = 2 and in_keyrange('-80') order by c1 asc",
   308  			targetQuery: "select c1, c2 from t1 order by c1 asc",
   309  			compareCols: []compareColInfo{{0, collations.Collation(nil), true, "c1"}, {1, collations.Collation(nil), false, "c2"}},
   310  			comparePKs:  []compareColInfo{{0, collations.Collation(nil), true, "c1"}},
   311  			pkCols:      []int{0},
   312  			selectPks:   []int{0},
   313  			orderBy: sqlparser.OrderBy{&sqlparser.Order{
   314  				Expr:      &sqlparser.ColName{Name: sqlparser.NewIdentifierCI("c1")},
   315  				Direction: sqlparser.AscOrder,
   316  			}},
   317  		},
   318  	}, {
   319  		// in_keyrange on LHS of AND.
   320  		// This is currently not a valid construct, but will be supported in the future.
   321  		input: &binlogdatapb.Rule{
   322  			Match:  "t1",
   323  			Filter: "select * from t1 where in_keyrange('-80') and c2 = 2",
   324  		},
   325  		table: "t1",
   326  		tablePlan: &tablePlan{
   327  			table:       testSchema.TableDefinitions[tableDefMap["t1"]],
   328  			sourceQuery: "select c1, c2 from t1 where in_keyrange('-80') and c2 = 2 order by c1 asc",
   329  			targetQuery: "select c1, c2 from t1 order by c1 asc",
   330  			compareCols: []compareColInfo{{0, collations.Collation(nil), true, "c1"}, {1, collations.Collation(nil), false, "c2"}},
   331  			comparePKs:  []compareColInfo{{0, collations.Collation(nil), true, "c1"}},
   332  			pkCols:      []int{0},
   333  			selectPks:   []int{0},
   334  			orderBy: sqlparser.OrderBy{&sqlparser.Order{
   335  				Expr:      &sqlparser.ColName{Name: sqlparser.NewIdentifierCI("c1")},
   336  				Direction: sqlparser.AscOrder,
   337  			}},
   338  		},
   339  	}, {
   340  		// in_keyrange on cascaded AND expression.
   341  		// This is currently not a valid construct, but will be supported in the future.
   342  		input: &binlogdatapb.Rule{
   343  			Match:  "t1",
   344  			Filter: "select * from t1 where c2 = 2 and c1 = 1 and in_keyrange('-80')",
   345  		},
   346  		table: "t1",
   347  		tablePlan: &tablePlan{
   348  			table:       testSchema.TableDefinitions[tableDefMap["t1"]],
   349  			sourceQuery: "select c1, c2 from t1 where c2 = 2 and c1 = 1 and in_keyrange('-80') order by c1 asc",
   350  			targetQuery: "select c1, c2 from t1 order by c1 asc",
   351  			compareCols: []compareColInfo{{0, collations.Collation(nil), true, "c1"}, {1, collations.Collation(nil), false, "c2"}},
   352  			comparePKs:  []compareColInfo{{0, collations.Collation(nil), true, "c1"}},
   353  			pkCols:      []int{0},
   354  			selectPks:   []int{0},
   355  			orderBy: sqlparser.OrderBy{&sqlparser.Order{
   356  				Expr:      &sqlparser.ColName{Name: sqlparser.NewIdentifierCI("c1")},
   357  				Direction: sqlparser.AscOrder,
   358  			}},
   359  		},
   360  	}, {
   361  		// in_keyrange parenthesized.
   362  		// This is currently not a valid construct, but will be supported in the future.
   363  		input: &binlogdatapb.Rule{
   364  			Match:  "t1",
   365  			Filter: "select * from t1 where (c2 = 2 and in_keyrange('-80'))",
   366  		},
   367  		table: "t1",
   368  		tablePlan: &tablePlan{
   369  			table:       testSchema.TableDefinitions[tableDefMap["t1"]],
   370  			sourceQuery: "select c1, c2 from t1 where c2 = 2 and in_keyrange('-80') order by c1 asc",
   371  			targetQuery: "select c1, c2 from t1 order by c1 asc",
   372  			compareCols: []compareColInfo{{0, collations.Collation(nil), true, "c1"}, {1, collations.Collation(nil), false, "c2"}},
   373  			comparePKs:  []compareColInfo{{0, collations.Collation(nil), true, "c1"}},
   374  			pkCols:      []int{0},
   375  			selectPks:   []int{0},
   376  			orderBy: sqlparser.OrderBy{&sqlparser.Order{
   377  				Expr:      &sqlparser.ColName{Name: sqlparser.NewIdentifierCI("c1")},
   378  				Direction: sqlparser.AscOrder,
   379  			}},
   380  		},
   381  	}, {
   382  		// group by
   383  		input: &binlogdatapb.Rule{
   384  			Match:  "t1",
   385  			Filter: "select * from t1 group by c1",
   386  		},
   387  		table: "t1",
   388  		tablePlan: &tablePlan{
   389  			table:       testSchema.TableDefinitions[tableDefMap["t1"]],
   390  			sourceQuery: "select c1, c2 from t1 group by c1 order by c1 asc",
   391  			targetQuery: "select c1, c2 from t1 order by c1 asc",
   392  			compareCols: []compareColInfo{{0, collations.Collation(nil), true, "c1"}, {1, collations.Collation(nil), false, "c2"}},
   393  			comparePKs:  []compareColInfo{{0, collations.Collation(nil), true, "c1"}},
   394  			pkCols:      []int{0},
   395  			selectPks:   []int{0},
   396  			orderBy: sqlparser.OrderBy{&sqlparser.Order{
   397  				Expr:      &sqlparser.ColName{Name: sqlparser.NewIdentifierCI("c1")},
   398  				Direction: sqlparser.AscOrder,
   399  			}},
   400  		},
   401  	}, {
   402  		// aggregations
   403  		input: &binlogdatapb.Rule{
   404  			Match:  "aggr",
   405  			Filter: "select c1, c2, count(*) as c3, sum(c4) as c4 from t1 group by c1",
   406  		},
   407  		table: "aggr",
   408  		tablePlan: &tablePlan{
   409  			table:       testSchema.TableDefinitions[tableDefMap["aggr"]],
   410  			sourceQuery: "select c1, c2, count(*) as c3, sum(c4) as c4 from t1 group by c1 order by c1 asc",
   411  			targetQuery: "select c1, c2, c3, c4 from aggr order by c1 asc",
   412  			compareCols: []compareColInfo{{0, collations.Collation(nil), true, "c1"}, {1, collations.Collation(nil), false, "c2"}, {2, collations.Collation(nil), false, "c3"}, {3, collations.Collation(nil), false, "c4"}},
   413  			comparePKs:  []compareColInfo{{0, collations.Collation(nil), true, "c1"}},
   414  			pkCols:      []int{0},
   415  			selectPks:   []int{0},
   416  			orderBy: sqlparser.OrderBy{&sqlparser.Order{
   417  				Expr:      &sqlparser.ColName{Name: sqlparser.NewIdentifierCI("c1")},
   418  				Direction: sqlparser.AscOrder,
   419  			}},
   420  			aggregates: []*engine.AggregateParams{{
   421  				Opcode: engine.AggregateSum,
   422  				Col:    2,
   423  			}, {
   424  				Opcode: engine.AggregateSum,
   425  				Col:    3,
   426  			}},
   427  		},
   428  	}, {
   429  		// date conversion on import.
   430  		input: &binlogdatapb.Rule{
   431  			Match: "datze",
   432  		},
   433  		sourceTimeZone: "US/Pacific",
   434  		table:          "datze",
   435  		tablePlan: &tablePlan{
   436  			table:       testSchema.TableDefinitions[tableDefMap["datze"]],
   437  			sourceQuery: "select id, dt from datze order by id asc",
   438  			targetQuery: "select id, convert_tz(dt, 'UTC', 'US/Pacific') as dt from datze order by id asc",
   439  			compareCols: []compareColInfo{{0, collations.Collation(nil), true, "id"}, {1, collations.Collation(nil), false, "dt"}},
   440  			comparePKs:  []compareColInfo{{0, collations.Collation(nil), true, "id"}},
   441  			pkCols:      []int{0},
   442  			selectPks:   []int{0},
   443  			orderBy: sqlparser.OrderBy{&sqlparser.Order{
   444  				Expr:      &sqlparser.ColName{Name: sqlparser.NewIdentifierCI("id")},
   445  				Direction: sqlparser.AscOrder,
   446  			}},
   447  		},
   448  	}}
   449  
   450  	for _, tcase := range testcases {
   451  		t.Run(tcase.input.Filter, func(t *testing.T) {
   452  			if tcase.sourceTimeZone != "" {
   453  				ct.targetTimeZone = "UTC"
   454  				ct.sourceTimeZone = tcase.sourceTimeZone
   455  				defer func() {
   456  					ct.targetTimeZone = ""
   457  					ct.sourceTimeZone = ""
   458  				}()
   459  			}
   460  			dbc := binlogplayer.NewMockDBClient(t)
   461  			filter := &binlogdatapb.Filter{Rules: []*binlogdatapb.Rule{tcase.input}}
   462  			vdiffenv.opts.CoreOptions.Tables = tcase.table
   463  			wd, err := newWorkflowDiffer(ct, vdiffenv.opts)
   464  			require.NoError(t, err)
   465  			dbc.ExpectRequestRE("select vdt.lastpk as lastpk, vdt.mismatch as mismatch, vdt.report as report", noResults, nil)
   466  			err = wd.buildPlan(dbc, filter, testSchema)
   467  			require.NoError(t, err, tcase.input)
   468  			require.Equal(t, 1, len(wd.tableDiffers), tcase.input)
   469  			assert.Equal(t, tcase.tablePlan, wd.tableDiffers[tcase.table].tablePlan, tcase.input)
   470  
   471  			// Confirm that the options are passed through.
   472  			for _, td := range wd.tableDiffers {
   473  				require.Equal(t, vdiffenv.opts, td.wd.opts)
   474  			}
   475  		})
   476  	}
   477  }
   478  
   479  func TestBuildPlanInclude(t *testing.T) {
   480  	vdenv := newTestVDiffEnv(t)
   481  	defer vdenv.close()
   482  
   483  	controllerQR := sqltypes.MakeTestResult(sqltypes.MakeTestFields(
   484  		vdiffTestCols,
   485  		vdiffTestColTypes,
   486  	),
   487  		fmt.Sprintf("1|%s|%s|%s|%s|%s|%s|%s|", uuid.New(), vdiffenv.workflow, tstenv.KeyspaceName, tstenv.ShardName, vdiffDBName, PendingState, optionsJS),
   488  	)
   489  	vdiffenv.dbClient.ExpectRequest("select * from _vt.vdiff where id = 1", noResults, nil)
   490  	ct, err := newController(context.Background(), controllerQR.Named().Row(), vdiffenv.dbClientFactory, tstenv.TopoServ, vdiffenv.vde, vdiffenv.opts)
   491  	require.NoError(t, err)
   492  
   493  	schm := &tabletmanagerdatapb.SchemaDefinition{
   494  		TableDefinitions: []*tabletmanagerdatapb.TableDefinition{{
   495  			Name:              "t1",
   496  			Columns:           []string{"c1", "c2"},
   497  			PrimaryKeyColumns: []string{"c1"},
   498  			Fields:            sqltypes.MakeTestFields("c1|c2", "int64|int64"),
   499  		}, {
   500  			Name:              "t2",
   501  			Columns:           []string{"c1", "c2"},
   502  			PrimaryKeyColumns: []string{"c1"},
   503  			Fields:            sqltypes.MakeTestFields("c1|c2", "int64|int64"),
   504  		}, {
   505  			Name:              "t3",
   506  			Columns:           []string{"c1", "c2"},
   507  			PrimaryKeyColumns: []string{"c1"},
   508  			Fields:            sqltypes.MakeTestFields("c1|c2", "int64|int64"),
   509  		}, {
   510  			Name:              "t4",
   511  			Columns:           []string{"c1", "c2"},
   512  			PrimaryKeyColumns: []string{"c1"},
   513  			Fields:            sqltypes.MakeTestFields("c1|c2", "int64|int64"),
   514  		}},
   515  	}
   516  	vdiffenv.tmc.schema = schm
   517  	defer func() {
   518  		vdiffenv.tmc.schema = testSchema
   519  	}()
   520  	rule := &binlogdatapb.Rule{
   521  		Match: "/.*",
   522  	}
   523  	filter := &binlogdatapb.Filter{Rules: []*binlogdatapb.Rule{rule}}
   524  
   525  	testcases := []struct {
   526  		tables []string
   527  	}{
   528  		{tables: []string{"t2"}},
   529  		{tables: []string{"t2", "t3"}},
   530  		{tables: []string{"t1", "t2", "t3", "t4"}},
   531  		{tables: []string{"t1", "t2", "t3", "t4"}},
   532  	}
   533  
   534  	for _, tcase := range testcases {
   535  		dbc := binlogplayer.NewMockDBClient(t)
   536  		vdiffenv.opts.CoreOptions.Tables = strings.Join(tcase.tables, ",")
   537  		wd, err := newWorkflowDiffer(ct, vdiffenv.opts)
   538  		require.NoError(t, err)
   539  		for _, table := range tcase.tables {
   540  			query := fmt.Sprintf(`select vdt.lastpk as lastpk, vdt.mismatch as mismatch, vdt.report as report
   541  						from _vt.vdiff as vd inner join _vt.vdiff_table as vdt on (vd.id = vdt.vdiff_id)
   542  						where vdt.vdiff_id = 1 and vdt.table_name = '%s'`, table)
   543  			dbc.ExpectRequest(query, noResults, nil)
   544  		}
   545  		err = wd.buildPlan(dbc, filter, schm)
   546  		require.NoError(t, err)
   547  		require.Equal(t, len(tcase.tables), len(wd.tableDiffers))
   548  	}
   549  }
   550  
   551  func TestBuildPlanFailure(t *testing.T) {
   552  	vdenv := newTestVDiffEnv(t)
   553  	defer vdenv.close()
   554  	UUID := uuid.New()
   555  
   556  	controllerQR := sqltypes.MakeTestResult(sqltypes.MakeTestFields(
   557  		vdiffTestCols,
   558  		vdiffTestColTypes,
   559  	),
   560  		fmt.Sprintf("1|%s|%s|%s|%s|%s|%s|%s|", UUID, vdiffenv.workflow, tstenv.KeyspaceName, tstenv.ShardName, vdiffDBName, PendingState, optionsJS),
   561  	)
   562  	vdiffenv.dbClient.ExpectRequest("select * from _vt.vdiff where id = 1", noResults, nil)
   563  	ct, err := newController(context.Background(), controllerQR.Named().Row(), vdiffenv.dbClientFactory, tstenv.TopoServ, vdiffenv.vde, vdiffenv.opts)
   564  	require.NoError(t, err)
   565  
   566  	testcases := []struct {
   567  		input *binlogdatapb.Rule
   568  		err   string
   569  	}{{
   570  		input: &binlogdatapb.Rule{
   571  			Match:  "t1",
   572  			Filter: "bad query",
   573  		},
   574  		err: "syntax error at position 4 near 'bad'",
   575  	}, {
   576  		input: &binlogdatapb.Rule{
   577  			Match:  "t1",
   578  			Filter: "update t1 set c1=2",
   579  		},
   580  		err: "unexpected: update t1 set c1 = 2",
   581  	}, {
   582  		input: &binlogdatapb.Rule{
   583  			Match:  "t1",
   584  			Filter: "select c1+1 from t1",
   585  		},
   586  		err: "expression needs an alias: c1 + 1",
   587  	}, {
   588  		input: &binlogdatapb.Rule{
   589  			Match:  "t1",
   590  			Filter: "select next 2 values from t1",
   591  		},
   592  		err: "unexpected: select next 2 values from t1",
   593  	}, {
   594  		input: &binlogdatapb.Rule{
   595  			Match:  "t1",
   596  			Filter: "select c3 from t1",
   597  		},
   598  		err: "column c3 not found in table t1 on tablet cell:\"cell1\" uid:100",
   599  	}}
   600  	for _, tcase := range testcases {
   601  		dbc := binlogplayer.NewMockDBClient(t)
   602  		filter := &binlogdatapb.Filter{Rules: []*binlogdatapb.Rule{tcase.input}}
   603  		vdiffenv.opts.CoreOptions.Tables = tcase.input.Match
   604  		wd, err := newWorkflowDiffer(ct, vdiffenv.opts)
   605  		require.NoError(t, err)
   606  		dbc.ExpectRequestRE("select vdt.lastpk as lastpk, vdt.mismatch as mismatch, vdt.report as report", noResults, nil)
   607  		err = wd.buildPlan(dbc, filter, testSchema)
   608  		assert.EqualError(t, err, tcase.err, tcase.input)
   609  	}
   610  }