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 }