vitess.io/vitess@v0.16.2/go/test/endtoend/vtgate/gen4/gen4_test.go (about)

     1  /*
     2  Copyright 2021 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 vtgate
    18  
    19  import (
    20  	"context"
    21  	"fmt"
    22  	"strconv"
    23  	"testing"
    24  
    25  	"vitess.io/vitess/go/test/endtoend/utils"
    26  
    27  	"github.com/stretchr/testify/assert"
    28  
    29  	"github.com/stretchr/testify/require"
    30  
    31  	"vitess.io/vitess/go/mysql"
    32  )
    33  
    34  func TestOrderBy(t *testing.T) {
    35  	mcmp, closer := start(t)
    36  	defer closer()
    37  
    38  	// insert some data.
    39  	utils.Exec(t, mcmp.VtConn, `insert into t1(id, col) values (100, 123),(10, 12),(1, 13),(1000, 1234)`)
    40  
    41  	// Gen4 only supported query.
    42  	utils.AssertMatches(t, mcmp.VtConn, `select col from t1 order by id`, `[[INT64(13)] [INT64(12)] [INT64(123)] [INT64(1234)]]`)
    43  
    44  	// Gen4 unsupported query. v3 supported.
    45  	utils.AssertMatches(t, mcmp.VtConn, `select col from t1 order by 1`, `[[INT64(12)] [INT64(13)] [INT64(123)] [INT64(1234)]]`)
    46  }
    47  
    48  func TestCorrelatedExistsSubquery(t *testing.T) {
    49  	mcmp, closer := start(t)
    50  	defer closer()
    51  
    52  	// insert some data.
    53  	utils.Exec(t, mcmp.VtConn, `insert into t1(id, col) values (100, 123), (10, 12), (1, 13), (4, 13), (1000, 1234)`)
    54  	utils.Exec(t, mcmp.VtConn, `insert into t2(id, tcol1, tcol2) values (100, 13, 1),(9, 7, 15),(1, 123, 123),(1004, 134, 123)`)
    55  
    56  	utils.AssertMatches(t, mcmp.VtConn, `select id from t1 where exists(select 1 from t2 where t1.col = t2.tcol2)`,
    57  		`[[INT64(100)]]`)
    58  	utils.AssertMatches(t, mcmp.VtConn, `select id from t1 where exists(select 1 from t2 where t1.col = t2.tcol1) order by id`,
    59  		`[[INT64(1)] [INT64(4)] [INT64(100)]]`)
    60  	utils.AssertMatches(t, mcmp.VtConn, `select id from t1 where id in (select id from t2) order by id`,
    61  		`[[INT64(1)] [INT64(100)]]`)
    62  
    63  	utils.AssertMatches(t, mcmp.VtConn, `
    64  select id 
    65  from t1 
    66  where exists(
    67  	select t2.id, count(*) 
    68  	from t2 
    69  	where t1.col = t2.tcol2
    70      having count(*) > 0
    71  )`,
    72  		`[[INT64(100)]]`)
    73  	utils.AssertMatches(t, mcmp.VtConn, `
    74  select id 
    75  from t1 
    76  where exists(
    77  	select t2.id, count(*) 
    78  	from t2 
    79  	where t1.col = t2.tcol1
    80  ) order by id`,
    81  		`[[INT64(1)] [INT64(4)] [INT64(100)]]`)
    82  	utils.AssertMatchesNoOrder(t, mcmp.VtConn, `
    83  select id 
    84  from t1 
    85  where exists(
    86  	select count(*) 
    87  	from t2 
    88  	where t1.col = t2.tcol1
    89  ) order by id`,
    90  		`[[INT64(1)] [INT64(4)] [INT64(100)] [INT64(1000)] [INT64(10)]]`)
    91  }
    92  
    93  func TestGroupBy(t *testing.T) {
    94  	mcmp, closer := start(t)
    95  	defer closer()
    96  
    97  	// insert some data.
    98  	utils.Exec(t, mcmp.VtConn, `insert into t1(id, col) values (1, 123),(2, 12),(3, 13),(4, 1234)`)
    99  	utils.Exec(t, mcmp.VtConn, `insert into t2(id, tcol1, tcol2) values (1, 'A', 'A'),(2, 'B', 'C'),(3, 'A', 'C'),(4, 'C', 'A'),(5, 'A', 'A'),(6, 'B', 'C'),(7, 'B', 'A'),(8, 'C', 'B')`)
   100  
   101  	// Gen4 only supported query.
   102  	utils.AssertMatches(t, mcmp.VtConn, `select tcol2, tcol1, count(id) from t2 group by tcol2, tcol1`,
   103  		`[[VARCHAR("A") VARCHAR("A") INT64(2)] [VARCHAR("A") VARCHAR("B") INT64(1)] [VARCHAR("A") VARCHAR("C") INT64(1)] [VARCHAR("B") VARCHAR("C") INT64(1)] [VARCHAR("C") VARCHAR("A") INT64(1)] [VARCHAR("C") VARCHAR("B") INT64(2)]]`)
   104  
   105  	utils.AssertMatches(t, mcmp.VtConn, `select tcol1, tcol1 from t2 order by tcol1`,
   106  		`[[VARCHAR("A") VARCHAR("A")] [VARCHAR("A") VARCHAR("A")] [VARCHAR("A") VARCHAR("A")] [VARCHAR("B") VARCHAR("B")] [VARCHAR("B") VARCHAR("B")] [VARCHAR("B") VARCHAR("B")] [VARCHAR("C") VARCHAR("C")] [VARCHAR("C") VARCHAR("C")]]`)
   107  
   108  	utils.AssertMatches(t, mcmp.VtConn, `select tcol1, tcol1 from t1 join t2 on t1.id = t2.id order by tcol1`,
   109  		`[[VARCHAR("A") VARCHAR("A")] [VARCHAR("A") VARCHAR("A")] [VARCHAR("B") VARCHAR("B")] [VARCHAR("C") VARCHAR("C")]]`)
   110  
   111  	utils.AssertMatches(t, mcmp.VtConn, `select count(*) k, tcol1, tcol2, "abc" b from t2 group by tcol1, tcol2, b order by k, tcol2, tcol1`,
   112  		`[[INT64(1) VARCHAR("B") VARCHAR("A") VARCHAR("abc")] `+
   113  			`[INT64(1) VARCHAR("C") VARCHAR("A") VARCHAR("abc")] `+
   114  			`[INT64(1) VARCHAR("C") VARCHAR("B") VARCHAR("abc")] `+
   115  			`[INT64(1) VARCHAR("A") VARCHAR("C") VARCHAR("abc")] `+
   116  			`[INT64(2) VARCHAR("A") VARCHAR("A") VARCHAR("abc")] `+
   117  			`[INT64(2) VARCHAR("B") VARCHAR("C") VARCHAR("abc")]]`)
   118  }
   119  
   120  func TestJoinBindVars(t *testing.T) {
   121  	mcmp, closer := start(t)
   122  	defer closer()
   123  
   124  	utils.Exec(t, mcmp.VtConn, `insert into t2(id, tcol1, tcol2) values (1, 'A', 'A'),(2, 'B', 'C'),(3, 'A', 'C'),(4, 'C', 'A'),(5, 'A', 'A'),(6, 'B', 'C'),(7, 'B', 'A'),(8, 'C', 'B')`)
   125  	utils.Exec(t, mcmp.VtConn, `insert into t3(id, tcol1, tcol2) values (1, 'A', 'A'),(2, 'B', 'C'),(3, 'A', 'C'),(4, 'C', 'A'),(5, 'A', 'A'),(6, 'B', 'C'),(7, 'B', 'A'),(8, 'C', 'B')`)
   126  
   127  	utils.AssertMatches(t, mcmp.VtConn, `select t2.tcol1 from t2 join t3 on t2.tcol2 = t3.tcol2 where t2.tcol1 = 'A'`, `[[VARCHAR("A")] [VARCHAR("A")] [VARCHAR("A")] [VARCHAR("A")] [VARCHAR("A")] [VARCHAR("A")] [VARCHAR("A")] [VARCHAR("A")] [VARCHAR("A")] [VARCHAR("A")] [VARCHAR("A")]]`)
   128  }
   129  
   130  func TestDistinctAggregationFunc(t *testing.T) {
   131  	mcmp, closer := start(t)
   132  	defer closer()
   133  
   134  	// insert some data.
   135  	utils.Exec(t, mcmp.VtConn, `insert into t2(id, tcol1, tcol2) values (1, 'A', 'A'),(2, 'B', 'C'),(3, 'A', 'C'),(4, 'C', 'A'),(5, 'A', 'A'),(6, 'B', 'C'),(7, 'B', 'A'),(8, 'C', 'A')`)
   136  
   137  	// count on primary vindex
   138  	utils.AssertMatches(t, mcmp.VtConn, `select tcol1, count(distinct id) from t2 group by tcol1`,
   139  		`[[VARCHAR("A") INT64(3)] [VARCHAR("B") INT64(3)] [VARCHAR("C") INT64(2)]]`)
   140  
   141  	// count on any column
   142  	utils.AssertMatches(t, mcmp.VtConn, `select tcol1, count(distinct tcol2) from t2 group by tcol1`,
   143  		`[[VARCHAR("A") INT64(2)] [VARCHAR("B") INT64(2)] [VARCHAR("C") INT64(1)]]`)
   144  
   145  	// sum of columns
   146  	utils.AssertMatches(t, mcmp.VtConn, `select sum(id), sum(tcol1) from t2`,
   147  		`[[DECIMAL(36) FLOAT64(0)]]`)
   148  
   149  	// sum on primary vindex
   150  	utils.AssertMatches(t, mcmp.VtConn, `select tcol1, sum(distinct id) from t2 group by tcol1`,
   151  		`[[VARCHAR("A") DECIMAL(9)] [VARCHAR("B") DECIMAL(15)] [VARCHAR("C") DECIMAL(12)]]`)
   152  
   153  	// sum on any column
   154  	utils.AssertMatches(t, mcmp.VtConn, `select tcol1, sum(distinct tcol2) from t2 group by tcol1`,
   155  		`[[VARCHAR("A") DECIMAL(0)] [VARCHAR("B") DECIMAL(0)] [VARCHAR("C") DECIMAL(0)]]`)
   156  
   157  	// insert more data to get values on sum
   158  	utils.Exec(t, mcmp.VtConn, `insert into t2(id, tcol1, tcol2) values (9, 'AA', null),(10, 'AA', '4'),(11, 'AA', '4'),(12, null, '5'),(13, null, '6'),(14, 'BB', '10'),(15, 'BB', '20'),(16, 'BB', 'X')`)
   159  
   160  	// multi distinct
   161  	utils.AssertMatches(t, mcmp.VtConn, `select tcol1, count(distinct tcol2), sum(distinct tcol2) from t2 group by tcol1`,
   162  		`[[NULL INT64(2) DECIMAL(11)] [VARCHAR("A") INT64(2) DECIMAL(0)] [VARCHAR("AA") INT64(1) DECIMAL(4)] [VARCHAR("B") INT64(2) DECIMAL(0)] [VARCHAR("BB") INT64(3) DECIMAL(30)] [VARCHAR("C") INT64(1) DECIMAL(0)]]`)
   163  }
   164  
   165  func TestDistinct(t *testing.T) {
   166  	mcmp, closer := start(t)
   167  	defer closer()
   168  
   169  	// insert some data.
   170  	utils.Exec(t, mcmp.VtConn, `insert into t2(id, tcol1, tcol2) values (1, 'A', 'A'),(2, 'B', 'C'),(3, 'A', 'C'),(4, 'C', 'A'),(5, 'A', 'A'),(6, 'B', 'C'),(7, 'B', 'A'),(8, 'C', 'A')`)
   171  
   172  	// multi distinct
   173  	utils.AssertMatches(t, mcmp.VtConn, `select distinct tcol1, tcol2 from t2`,
   174  		`[[VARCHAR("A") VARCHAR("A")] [VARCHAR("A") VARCHAR("C")] [VARCHAR("B") VARCHAR("A")] [VARCHAR("B") VARCHAR("C")] [VARCHAR("C") VARCHAR("A")]]`)
   175  }
   176  
   177  func TestSubQueries(t *testing.T) {
   178  	mcmp, closer := start(t)
   179  	defer closer()
   180  
   181  	utils.Exec(t, mcmp.VtConn, `insert into t2(id, tcol1, tcol2) values (1, 'A', 'A'),(2, 'B', 'C'),(3, 'A', 'C'),(4, 'C', 'A'),(5, 'A', 'A'),(6, 'B', 'C'),(7, 'B', 'A'),(8, 'C', 'B')`)
   182  	utils.Exec(t, mcmp.VtConn, `insert into t3(id, tcol1, tcol2) values (1, 'A', 'A'),(2, 'B', 'C'),(3, 'A', 'C'),(4, 'C', 'A'),(5, 'A', 'A'),(6, 'B', 'C'),(7, 'B', 'A'),(8, 'C', 'B')`)
   183  
   184  	utils.AssertMatches(t, mcmp.VtConn, `select t2.tcol1, t2.tcol2 from t2 where t2.id IN (select id from t3) order by t2.id`, `[[VARCHAR("A") VARCHAR("A")] [VARCHAR("B") VARCHAR("C")] [VARCHAR("A") VARCHAR("C")] [VARCHAR("C") VARCHAR("A")] [VARCHAR("A") VARCHAR("A")] [VARCHAR("B") VARCHAR("C")] [VARCHAR("B") VARCHAR("A")] [VARCHAR("C") VARCHAR("B")]]`)
   185  	utils.AssertMatches(t, mcmp.VtConn, `select t2.tcol1, t2.tcol2 from t2 where t2.id IN (select t3.id from t3 join t2 on t2.id = t3.id) order by t2.id`, `[[VARCHAR("A") VARCHAR("A")] [VARCHAR("B") VARCHAR("C")] [VARCHAR("A") VARCHAR("C")] [VARCHAR("C") VARCHAR("A")] [VARCHAR("A") VARCHAR("A")] [VARCHAR("B") VARCHAR("C")] [VARCHAR("B") VARCHAR("A")] [VARCHAR("C") VARCHAR("B")]]`)
   186  
   187  	utils.AssertMatches(t, mcmp.VtConn, `select u_a.a from u_a left join t2 on t2.id IN (select id from t2)`, `[]`)
   188  	// inserting some data in u_a
   189  	utils.Exec(t, mcmp.VtConn, `insert into u_a(id, a) values (1, 1)`)
   190  
   191  	// execute same query again.
   192  	qr := utils.Exec(t, mcmp.VtConn, `select u_a.a from u_a left join t2 on t2.id IN (select id from t2)`)
   193  	assert.EqualValues(t, 8, len(qr.Rows))
   194  	for index, row := range qr.Rows {
   195  		assert.EqualValues(t, `[INT64(1)]`, fmt.Sprintf("%v", row), "does not match for row: %d", index+1)
   196  	}
   197  
   198  	// fail as projection subquery is not scalar
   199  	_, err := utils.ExecAllowError(t, mcmp.VtConn, `select (select id from t2) from t2 order by id`)
   200  	assert.EqualError(t, err, "subquery returned more than one row (errno 1105) (sqlstate HY000) during query: select (select id from t2) from t2 order by id")
   201  
   202  	utils.AssertMatches(t, mcmp.VtConn, `select (select id from t2 order by id limit 1) from t2 order by id limit 2`, `[[INT64(1)] [INT64(1)]]`)
   203  }
   204  
   205  func TestPlannerWarning(t *testing.T) {
   206  	mcmp, closer := start(t)
   207  	defer closer()
   208  
   209  	// straight_join query
   210  	_ = utils.Exec(t, mcmp.VtConn, `select 1 from t1 straight_join t2 on t1.id = t2.id`)
   211  	utils.AssertMatches(t, mcmp.VtConn, `show warnings`, `[[VARCHAR("Warning") UINT16(1235) VARCHAR("straight join is converted to normal join")]]`)
   212  
   213  	// execute same query again.
   214  	_ = utils.Exec(t, mcmp.VtConn, `select 1 from t1 straight_join t2 on t1.id = t2.id`)
   215  	utils.AssertMatches(t, mcmp.VtConn, `show warnings`, `[[VARCHAR("Warning") UINT16(1235) VARCHAR("straight join is converted to normal join")]]`)
   216  
   217  	// random query to reset the warning.
   218  	_ = utils.Exec(t, mcmp.VtConn, `select 1 from t1`)
   219  
   220  	// execute same query again.
   221  	_ = utils.Exec(t, mcmp.VtConn, `select 1 from t1 straight_join t2 on t1.id = t2.id`)
   222  	utils.AssertMatches(t, mcmp.VtConn, `show warnings`, `[[VARCHAR("Warning") UINT16(1235) VARCHAR("straight join is converted to normal join")]]`)
   223  }
   224  
   225  func TestHashJoin(t *testing.T) {
   226  	mcmp, closer := start(t)
   227  	defer closer()
   228  
   229  	utils.Exec(t, mcmp.VtConn, `insert into t1(id, col) values (1, 1),(2, 3),(3, 4),(4, 7)`)
   230  
   231  	utils.AssertMatches(t, mcmp.VtConn, `select /*vt+ ALLOW_HASH_JOIN */ t1.id from t1 x join t1 where x.col = t1.col and x.id <= 3 and t1.id >= 3`, `[[INT64(3)]]`)
   232  
   233  	utils.Exec(t, mcmp.VtConn, `set workload = olap`)
   234  	defer utils.Exec(t, mcmp.VtConn, `set workload = oltp`)
   235  	utils.AssertMatches(t, mcmp.VtConn, `select /*vt+ ALLOW_HASH_JOIN */ t1.id from t1 x join t1 where x.col = t1.col and x.id <= 3 and t1.id >= 3`, `[[INT64(3)]]`)
   236  }
   237  
   238  func TestMultiColumnVindex(t *testing.T) {
   239  	mcmp, closer := start(t)
   240  	defer closer()
   241  	mcmp.Exec(`insert into user_region(id, cola, colb) values (1, 1, 2),(2, 30, 40),(3, 500, 600),(4, 30, 40),(5, 10000, 30000),(6, 422333, 40),(7, 30, 60)`)
   242  
   243  	for _, workload := range []string{"olap", "oltp"} {
   244  		t.Run(workload, func(t *testing.T) {
   245  			utils.Exec(t, mcmp.VtConn, fmt.Sprintf(`set workload = %s`, workload))
   246  			utils.AssertMatches(t, mcmp.VtConn, `select id from user_region where cola = 1 and colb = 2`, `[[INT64(1)]]`)
   247  			utils.AssertMatches(t, mcmp.VtConn, `select id from user_region where cola in (30,422333) and colb = 40 order by id`, `[[INT64(2)] [INT64(4)] [INT64(6)]]`)
   248  			utils.AssertMatches(t, mcmp.VtConn, `select id from user_region where cola in (30,422333) and colb in (40,60) order by id`, `[[INT64(2)] [INT64(4)] [INT64(6)] [INT64(7)]]`)
   249  			utils.AssertMatches(t, mcmp.VtConn, `select id from user_region where cola in (30,422333) and colb in (40,60) and cola = 422333`, `[[INT64(6)]]`)
   250  			utils.AssertMatches(t, mcmp.VtConn, `select id from user_region where cola in (30,422333) and colb in (40,60) and cola = 30 and colb = 60`, `[[INT64(7)]]`)
   251  		})
   252  	}
   253  }
   254  
   255  func TestFanoutVindex(t *testing.T) {
   256  	mcmp, closer := start(t)
   257  	defer closer()
   258  
   259  	tcases := []struct {
   260  		regionID int
   261  		exp      string
   262  	}{{
   263  		regionID: 24,
   264  		exp:      `[[INT64(24) INT64(1) VARCHAR("shard--19a0")]]`,
   265  	}, {
   266  		regionID: 25,
   267  		exp:      `[[INT64(25) INT64(2) VARCHAR("shard--19a0")] [INT64(25) INT64(7) VARCHAR("shard-19a0-20")]]`,
   268  	}, {
   269  		regionID: 31,
   270  		exp:      `[[INT64(31) INT64(8) VARCHAR("shard-19a0-20")]]`,
   271  	}, {
   272  		regionID: 32,
   273  		exp:      `[[INT64(32) INT64(14) VARCHAR("shard-20-20c0")] [INT64(32) INT64(19) VARCHAR("shard-20c0-")]]`,
   274  	}, {
   275  		regionID: 33,
   276  		exp:      `[[INT64(33) INT64(20) VARCHAR("shard-20c0-")]]`,
   277  	}}
   278  
   279  	defer utils.ExecAllowError(t, mcmp.VtConn, `delete from region_tbl`)
   280  	uid := 1
   281  	// insert data in all shards to know where the query fan-out
   282  	for _, s := range shardedKsShards {
   283  		utils.Exec(t, mcmp.VtConn, fmt.Sprintf("use `%s:%s`", shardedKs, s))
   284  		for _, tcase := range tcases {
   285  			utils.Exec(t, mcmp.VtConn, fmt.Sprintf("insert into region_tbl(rg,uid,msg) values(%d,%d,'shard-%s')", tcase.regionID, uid, s))
   286  			uid++
   287  		}
   288  	}
   289  
   290  	newConn, err := mysql.Connect(context.Background(), &vtParams)
   291  	require.NoError(t, err)
   292  	defer newConn.Close()
   293  
   294  	for _, workload := range []string{"olap", "oltp"} {
   295  		utils.Exec(t, newConn, fmt.Sprintf(`set workload = %s`, workload))
   296  		for _, tcase := range tcases {
   297  			t.Run(workload+strconv.Itoa(tcase.regionID), func(t *testing.T) {
   298  				sql := fmt.Sprintf("select rg, uid, msg from region_tbl where rg = %d order by uid", tcase.regionID)
   299  				assert.Equal(t, tcase.exp, fmt.Sprintf("%v", utils.Exec(t, newConn, sql).Rows))
   300  			})
   301  		}
   302  	}
   303  }
   304  
   305  func TestSubShardVindex(t *testing.T) {
   306  	mcmp, closer := start(t)
   307  	defer closer()
   308  
   309  	tcases := []struct {
   310  		regionID int
   311  		exp      string
   312  	}{{
   313  		regionID: 140,
   314  		exp:      `[[INT64(140) VARBINARY("1") VARCHAR("1") VARCHAR("shard--19a0")]]`,
   315  	}, {
   316  		regionID: 412,
   317  		exp:      `[[INT64(412) VARBINARY("2") VARCHAR("2") VARCHAR("shard--19a0")] [INT64(412) VARBINARY("9") VARCHAR("9") VARCHAR("shard-19a0-20")]]`,
   318  	}, {
   319  		regionID: 24,
   320  		exp:      `[[INT64(24) VARBINARY("10") VARCHAR("10") VARCHAR("shard-19a0-20")]]`,
   321  	}, {
   322  		regionID: 116,
   323  		exp:      `[[INT64(116) VARBINARY("11") VARCHAR("11") VARCHAR("shard-19a0-20")]]`,
   324  	}, {
   325  		regionID: 239,
   326  		exp:      `[[INT64(239) VARBINARY("12") VARCHAR("12") VARCHAR("shard-19a0-20")]]`,
   327  	}, {
   328  		regionID: 89,
   329  		exp:      `[[INT64(89) VARBINARY("20") VARCHAR("20") VARCHAR("shard-20-20c0")] [INT64(89) VARBINARY("27") VARCHAR("27") VARCHAR("shard-20c0-")]]`,
   330  	}, {
   331  		regionID: 109,
   332  		exp:      `[[INT64(109) VARBINARY("28") VARCHAR("28") VARCHAR("shard-20c0-")]]`,
   333  	}}
   334  
   335  	uid := 1
   336  	// insert data in all shards to know where the query fan-out
   337  	for _, s := range shardedKsShards {
   338  		utils.Exec(t, mcmp.VtConn, fmt.Sprintf("use `%s:%s`", shardedKs, s))
   339  		for _, tcase := range tcases {
   340  			utils.Exec(t, mcmp.VtConn, fmt.Sprintf("insert into multicol_tbl(cola,colb,colc,msg) values(%d,_binary '%d','%d','shard-%s')", tcase.regionID, uid, uid, s))
   341  			uid++
   342  		}
   343  	}
   344  
   345  	newConn, err := mysql.Connect(context.Background(), &vtParams)
   346  	require.NoError(t, err)
   347  	defer newConn.Close()
   348  
   349  	defer utils.ExecAllowError(t, newConn, `delete from multicol_tbl`)
   350  	for _, workload := range []string{"olap", "oltp"} {
   351  		utils.Exec(t, newConn, fmt.Sprintf(`set workload = %s`, workload))
   352  		for _, tcase := range tcases {
   353  			t.Run(workload+strconv.Itoa(tcase.regionID), func(t *testing.T) {
   354  				sql := fmt.Sprintf("select cola, colb, colc, msg from multicol_tbl where cola = %d order by cola,msg", tcase.regionID)
   355  				assert.Equal(t, tcase.exp, fmt.Sprintf("%v", utils.Exec(t, newConn, sql).Rows))
   356  			})
   357  		}
   358  	}
   359  }
   360  
   361  func TestSubShardVindexDML(t *testing.T) {
   362  	mcmp, closer := start(t)
   363  	defer closer()
   364  
   365  	tcases := []struct {
   366  		regionID       int
   367  		shardsAffected int
   368  	}{{
   369  		regionID:       140, // shard--19a0
   370  		shardsAffected: 1,
   371  	}, {
   372  		regionID:       412, // shard--19a0 and shard-19a0-20
   373  		shardsAffected: 2,
   374  	}, {
   375  		regionID:       24, // shard-19a0-20
   376  		shardsAffected: 1,
   377  	}, {
   378  		regionID:       89, // shard-20-20c0 and shard-20c0-
   379  		shardsAffected: 2,
   380  	}, {
   381  		regionID:       109, // shard-20c0-
   382  		shardsAffected: 1,
   383  	}}
   384  
   385  	uid := 1
   386  	// insert data in all shards to know where the query fan-out
   387  	for _, s := range shardedKsShards {
   388  		utils.Exec(t, mcmp.VtConn, fmt.Sprintf("use `%s:%s`", shardedKs, s))
   389  		for _, tcase := range tcases {
   390  			utils.Exec(t, mcmp.VtConn, fmt.Sprintf("insert into multicol_tbl(cola,colb,colc,msg) values(%d,_binary '%d','%d','shard-%s')", tcase.regionID, uid, uid, s))
   391  			uid++
   392  		}
   393  	}
   394  
   395  	newConn, err := mysql.Connect(context.Background(), &vtParams)
   396  	require.NoError(t, err)
   397  	defer newConn.Close()
   398  
   399  	defer utils.ExecAllowError(t, newConn, `delete from multicol_tbl`)
   400  	for _, tcase := range tcases {
   401  		t.Run(strconv.Itoa(tcase.regionID), func(t *testing.T) {
   402  			qr := utils.Exec(t, newConn, fmt.Sprintf("update multicol_tbl set msg = 'bar' where cola = %d", tcase.regionID))
   403  			assert.EqualValues(t, tcase.shardsAffected, qr.RowsAffected)
   404  		})
   405  	}
   406  
   407  	for _, tcase := range tcases {
   408  		t.Run(strconv.Itoa(tcase.regionID), func(t *testing.T) {
   409  			qr := utils.Exec(t, newConn, fmt.Sprintf("delete from multicol_tbl where cola = %d", tcase.regionID))
   410  			assert.EqualValues(t, tcase.shardsAffected, qr.RowsAffected)
   411  		})
   412  	}
   413  }
   414  
   415  func TestOuterJoin(t *testing.T) {
   416  	mcmp, closer := start(t)
   417  	defer closer()
   418  
   419  	// insert some data.
   420  	utils.Exec(t, mcmp.VtConn, `insert into t1(id, col) values (100, 123), (10, 123), (1, 13), (1000, 1234)`)
   421  	utils.Exec(t, mcmp.VtConn, `insert into t2(id, tcol1, tcol2) values (12, 13, 1),(123, 7, 15),(1, 123, 123),(1004, 134, 123)`)
   422  
   423  	// Gen4 only supported query.
   424  	utils.AssertMatchesNoOrder(t, mcmp.VtConn, `select t1.id, t2.tcol1+t2.tcol2 from t1 left join t2 on t1.col = t2.id`, `[[INT64(10) FLOAT64(22)] [INT64(1) NULL] [INT64(100) FLOAT64(22)] [INT64(1000) NULL]]`)
   425  	utils.AssertMatchesNoOrder(t, mcmp.VtConn, `select t1.id, t2.id, t2.tcol1+t1.col+t2.tcol2 from t1 left join t2 on t1.col = t2.id`,
   426  		`[[INT64(10) INT64(123) FLOAT64(145)]`+
   427  			` [INT64(1) NULL NULL]`+
   428  			` [INT64(100) INT64(123) FLOAT64(145)]`+
   429  			` [INT64(1000) NULL NULL]]`)
   430  }
   431  
   432  func TestUsingJoin(t *testing.T) {
   433  	require.NoError(t, utils.WaitForAuthoritative(t, clusterInstance.VtgateProcess, shardedKs, "t1"))
   434  	require.NoError(t, utils.WaitForAuthoritative(t, clusterInstance.VtgateProcess, shardedKs, "t2"))
   435  	require.NoError(t, utils.WaitForAuthoritative(t, clusterInstance.VtgateProcess, shardedKs, "t3"))
   436  
   437  	mcmp, closer := start(t)
   438  	defer closer()
   439  
   440  	// insert some data.
   441  	mcmp.Exec(`insert into t1(id, col) values          (1, 1), (2, 2), (3, 3), (5, 5)`)
   442  	mcmp.Exec(`insert into t2(id, tcol1, tcol2) values (1, 12, 12),(3, 3, 13),(4, 123, 123),(5, 134, 123)`)
   443  	mcmp.Exec(`insert into t3(id, tcol1, tcol2) values (1, 12, 12),(4, 123, 123),(5, 134, 123)`)
   444  
   445  	// Gen4 only supported query.
   446  	mcmp.AssertMatchesNoOrderInclColumnNames(`select t1.id from t1 join t2 using(id)`,
   447  		`[[INT64(1)] [INT64(3)] [INT64(5)]]`)
   448  	mcmp.AssertMatchesNoOrderInclColumnNames(`select t2.id from t2 join t3 using (id, tcol1, tcol2)`,
   449  		`[[INT64(1)] [INT64(4)] [INT64(5)]]`)
   450  	mcmp.AssertMatchesNoOrderInclColumnNames(`select * from t2 join t3 using (tcol1)`,
   451  		`[[VARCHAR("12") INT64(1) VARCHAR("12") INT64(1) VARCHAR("12")] `+
   452  			`[VARCHAR("123") INT64(4) VARCHAR("123") INT64(4) VARCHAR("123")] `+
   453  			`[VARCHAR("134") INT64(5) VARCHAR("123") INT64(5) VARCHAR("123")]]`)
   454  	mcmp.AssertMatchesNoOrderInclColumnNames(`select * from t2 join t3 using (tcol1) having tcol1 = 12`,
   455  		`[[VARCHAR("12") INT64(1) VARCHAR("12") INT64(1) VARCHAR("12")]]`)
   456  }
   457  
   458  // TestInsertFunction tests the INSERT function
   459  func TestInsertFunction(t *testing.T) {
   460  	mcmp, closer := start(t)
   461  	defer closer()
   462  
   463  	mcmp.Exec(`insert into t2(id, tcol1, tcol2) values (1, "Test", "This"),(2, "Testing", "Is"),(3, "TEST", "A")`)
   464  	mcmp.AssertMatches(`SELECT INSERT('Quadratic', 3, 4, 'What')`, `[[VARCHAR("QuWhattic")]]`)
   465  	mcmp.AssertMatches(`SELECT INSERT(tcol1, id, 3, tcol2) from t2`, `[[VARCHAR("Thist")] [VARCHAR("TIsing")] [VARCHAR("TEA")]]`)
   466  }
   467  
   468  // TestGTIDFunctions tests the gtid functions
   469  func TestGTIDFunctions(t *testing.T) {
   470  	mcmp, closer := start(t)
   471  	defer closer()
   472  
   473  	mcmp.AssertMatches(`select gtid_subset('3E11FA47-71CA-11E1-9E33-C80AA9429562:23','3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57')`, `[[INT64(1)]]`)
   474  	mcmp.AssertMatches(`select gtid_subtract('3E11FA47-71CA-11E1-9E33-C80AA9429562:23-78','3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57')`, `[[VARCHAR("3e11fa47-71ca-11e1-9e33-c80aa9429562:58-78")]]`)
   475  }
   476  
   477  func TestFilterOnLeftOuterJoin(t *testing.T) {
   478  	mcmp, closer := start(t)
   479  	defer closer()
   480  
   481  	// insert some data.
   482  	mcmp.Exec(`insert into team (id, name) values (11, 'Acme'), (22, 'B'), (33, 'C')`)
   483  	mcmp.Exec(`insert into team_fact (id, team, fact) values (1, 11, 'A'), (2, 22, 'A'), (3, 33, 'A')`)
   484  
   485  	// Gen4 only supported query.
   486  	query := `select team.id
   487  				from team_fact
   488  				  join team on team.id = team_fact.team
   489  				  left outer join team_member on team_member.team = team.id
   490  				where (
   491  				  team_fact.fact = 'A'
   492  				  and team_member.user is null
   493  				  and team_fact.team >= 22
   494  				)`
   495  
   496  	mcmp.AssertMatches(query, "[[INT32(22)] [INT32(33)]]")
   497  }
   498  
   499  func TestPercentageAndUnderscore(t *testing.T) {
   500  	mcmp, closer := start(t)
   501  	defer closer()
   502  
   503  	// insert some data.
   504  	mcmp.Exec(`insert into t2(id, tcol1, tcol2) values (1, 'A%B', 'A%B'),(2, 'C_D', 'E'),(3, 'AB', 'C1D'),(4, 'E', 'A%B'),(5, 'A%B', 'AB'),(6, 'C1D', 'E'),(7, 'C_D', 'A%B'),(8, 'E', 'C_D')`)
   505  
   506  	// Verify that %, _ and their escaped counter-parts work in Vitess in the like clause as well as equality clause
   507  	mcmp.Exec(`select * from t2 where tcol1 like "A%B"`)
   508  	mcmp.Exec(`select * from t2 where tcol1 like "A\%B"`)
   509  	mcmp.Exec(`select * from t2 where tcol1 like "C_D"`)
   510  	mcmp.Exec(`select * from t2 where tcol1 like "C\_D"`)
   511  
   512  	mcmp.Exec(`select * from t2 where tcol1 = "A%B"`)
   513  	mcmp.Exec(`select * from t2 where tcol1 = "A\%B"`)
   514  	mcmp.Exec(`select * from t2 where tcol1 = "C_D"`)
   515  	mcmp.Exec(`select * from t2 where tcol1 = "C\_D"`)
   516  
   517  	// Verify that %, _ and their escaped counter-parts work with filtering on VTGate level
   518  	mcmp.Exec(`select a.tcol1 from t2 a join t2 b where a.tcol1 = b.tcol2 group by a.tcol1 having repeat(a.tcol1,min(a.id)) like "A\%B" order by a.tcol1`)
   519  	mcmp.Exec(`select a.tcol1 from t2 a join t2 b where a.tcol1 = b.tcol2 group by a.tcol1 having repeat(a.tcol1,min(a.id)) like "A%B" order by a.tcol1`)
   520  	mcmp.Exec(`select a.tcol1 from t2 a join t2 b where a.tcol1 = b.tcol2 group by a.tcol1 having repeat(a.tcol1,min(a.id)) = "A\%B" order by a.tcol1`)
   521  	mcmp.Exec(`select a.tcol1 from t2 a join t2 b where a.tcol1 = b.tcol2 group by a.tcol1 having repeat(a.tcol1,min(a.id)) = "A%B" order by a.tcol1`)
   522  	mcmp.Exec(`select a.tcol1 from t2 a join t2 b where a.tcol1 = b.tcol2 group by a.tcol1 having repeat(a.tcol1,min(a.id)) like "C_D%" order by a.tcol1`)
   523  	mcmp.Exec(`select a.tcol1 from t2 a join t2 b where a.tcol1 = b.tcol2 group by a.tcol1 having repeat(a.tcol1,min(a.id)) like "C\_D%" order by a.tcol1`)
   524  	mcmp.Exec(`select a.tcol1 from t2 a join t2 b where a.tcol1 = b.tcol2 group by a.tcol1 having repeat(a.tcol1,min(a.id)) = "C_DC_D" order by a.tcol1`)
   525  	mcmp.Exec(`select a.tcol1 from t2 a join t2 b where a.tcol1 = b.tcol2 group by a.tcol1 having repeat(a.tcol1,min(a.id)) = "C\_DC\_D" order by a.tcol1`)
   526  }