vitess.io/vitess@v0.16.2/go/vt/vtgate/executor_set_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 vtgate 18 19 import ( 20 "context" 21 "fmt" 22 "testing" 23 24 "vitess.io/vitess/go/mysql" 25 "vitess.io/vitess/go/vt/sqlparser" 26 27 querypb "vitess.io/vitess/go/vt/proto/query" 28 29 "vitess.io/vitess/go/test/utils" 30 31 "vitess.io/vitess/go/vt/vterrors" 32 33 "vitess.io/vitess/go/sqltypes" 34 "vitess.io/vitess/go/vt/vtgate/vschemaacl" 35 36 vtgatepb "vitess.io/vitess/go/vt/proto/vtgate" 37 vtrpcpb "vitess.io/vitess/go/vt/proto/vtrpc" 38 39 "github.com/stretchr/testify/assert" 40 "github.com/stretchr/testify/require" 41 ) 42 43 func TestExecutorSet(t *testing.T) { 44 executorEnv, _, _, _ := createExecutorEnv() 45 46 testcases := []struct { 47 in string 48 out *vtgatepb.Session 49 err string 50 }{{ 51 in: "set @@autocommit = true", 52 out: &vtgatepb.Session{Autocommit: true}, 53 }, { 54 in: "set autocommit = 1, client_found_rows = 1", 55 out: &vtgatepb.Session{Autocommit: true, Options: &querypb.ExecuteOptions{ClientFoundRows: true}}, 56 }, { 57 in: "set @@session.autocommit = true", 58 out: &vtgatepb.Session{Autocommit: true}, 59 }, { 60 in: "set @@session.`autocommit` = true", 61 out: &vtgatepb.Session{Autocommit: true}, 62 }, { 63 in: "set autocommit = true", 64 out: &vtgatepb.Session{Autocommit: true}, 65 }, { 66 in: "set autocommit = on", 67 out: &vtgatepb.Session{Autocommit: true}, 68 }, { 69 in: "set autocommit = ON", 70 out: &vtgatepb.Session{Autocommit: true}, 71 }, { 72 in: "set autocommit = 'on'", 73 out: &vtgatepb.Session{Autocommit: true}, 74 }, { 75 in: "set autocommit = `on`", 76 out: &vtgatepb.Session{Autocommit: true}, 77 }, { 78 in: "set autocommit = \"on\"", 79 out: &vtgatepb.Session{Autocommit: true}, 80 }, { 81 in: "set autocommit = false", 82 out: &vtgatepb.Session{}, 83 }, { 84 in: "set autocommit = off", 85 out: &vtgatepb.Session{}, 86 }, { 87 in: "set autocommit = OFF", 88 out: &vtgatepb.Session{}, 89 }, { 90 in: "set AUTOCOMMIT = 0", 91 out: &vtgatepb.Session{}, 92 }, { 93 in: "set AUTOCOMMIT = 'aa'", 94 err: "variable 'autocommit' can't be set to the value: 'aa' is not a boolean", 95 }, { 96 in: "set autocommit = 2", 97 err: "variable 'autocommit' can't be set to the value: 2 is not a boolean", 98 }, { 99 in: "set client_found_rows = 1", 100 out: &vtgatepb.Session{Autocommit: true, Options: &querypb.ExecuteOptions{ClientFoundRows: true}}, 101 }, { 102 in: "set client_found_rows = true", 103 out: &vtgatepb.Session{Autocommit: true, Options: &querypb.ExecuteOptions{ClientFoundRows: true}}, 104 }, { 105 in: "set client_found_rows = 0", 106 out: &vtgatepb.Session{Autocommit: true, Options: &querypb.ExecuteOptions{}}, 107 }, { 108 in: "set client_found_rows = false", 109 out: &vtgatepb.Session{Autocommit: true, Options: &querypb.ExecuteOptions{}}, 110 }, { 111 in: "set global @@session.client_found_rows = 1", 112 err: "syntax error at position 39 near 'session.client_found_rows'", 113 }, { 114 in: "set client_found_rows = 'aa'", 115 err: "variable 'client_found_rows' can't be set to the value: 'aa' is not a boolean", 116 }, { 117 in: "set client_found_rows = 2", 118 err: "variable 'client_found_rows' can't be set to the value: 2 is not a boolean", 119 }, { 120 in: "set transaction_mode = 'unspecified'", 121 out: &vtgatepb.Session{Autocommit: true, TransactionMode: vtgatepb.TransactionMode_UNSPECIFIED}, 122 }, { 123 in: "set transaction_mode = 'single'", 124 out: &vtgatepb.Session{Autocommit: true, TransactionMode: vtgatepb.TransactionMode_SINGLE}, 125 }, { 126 in: "set transaction_mode = 'multi'", 127 out: &vtgatepb.Session{Autocommit: true, TransactionMode: vtgatepb.TransactionMode_MULTI}, 128 }, { 129 in: "set transaction_mode = 'twopc'", 130 out: &vtgatepb.Session{Autocommit: true, TransactionMode: vtgatepb.TransactionMode_TWOPC}, 131 }, { 132 in: "set transaction_mode = twopc", 133 out: &vtgatepb.Session{Autocommit: true, TransactionMode: vtgatepb.TransactionMode_TWOPC}, 134 }, { 135 in: "set transaction_mode = 'aa'", 136 err: "invalid transaction_mode: aa", 137 }, { 138 in: "set transaction_mode = 1", 139 err: "incorrect argument type to variable 'transaction_mode': INT64", 140 }, { 141 in: "set workload = 'unspecified'", 142 out: &vtgatepb.Session{Autocommit: true, Options: &querypb.ExecuteOptions{Workload: querypb.ExecuteOptions_UNSPECIFIED}}, 143 }, { 144 in: "set workload = 'oltp'", 145 out: &vtgatepb.Session{Autocommit: true, Options: &querypb.ExecuteOptions{Workload: querypb.ExecuteOptions_OLTP}}, 146 }, { 147 in: "set workload = 'olap'", 148 out: &vtgatepb.Session{Autocommit: true, Options: &querypb.ExecuteOptions{Workload: querypb.ExecuteOptions_OLAP}}, 149 }, { 150 in: "set workload = 'dba'", 151 out: &vtgatepb.Session{Autocommit: true, Options: &querypb.ExecuteOptions{Workload: querypb.ExecuteOptions_DBA}}, 152 }, { 153 in: "set workload = 'aa'", 154 err: "invalid workload: aa", 155 }, { 156 in: "set workload = 1", 157 err: "incorrect argument type to variable 'workload': INT64", 158 }, { 159 in: "set tx_isolation = 'read-committed'", 160 out: &vtgatepb.Session{Autocommit: true}, 161 }, { 162 in: "set transaction_isolation = 'read-committed'", 163 out: &vtgatepb.Session{Autocommit: true}, 164 }, { 165 in: "set transaction_mode = 'twopc', autocommit=1", 166 out: &vtgatepb.Session{Autocommit: true, TransactionMode: vtgatepb.TransactionMode_TWOPC}, 167 }, { 168 in: "set sql_select_limit = 5", 169 out: &vtgatepb.Session{Autocommit: true, Options: &querypb.ExecuteOptions{SqlSelectLimit: 5}}, 170 }, { 171 in: "set sql_select_limit = DEFAULT", 172 out: &vtgatepb.Session{Autocommit: true, Options: &querypb.ExecuteOptions{SqlSelectLimit: 0}}, 173 }, { 174 in: "set sql_select_limit = 'asdfasfd'", 175 err: "incorrect argument type to variable 'sql_select_limit': VARCHAR", 176 }, { 177 in: "set autocommit = 1+1", 178 err: "variable 'autocommit' can't be set to the value: 2 is not a boolean", 179 }, { 180 in: "set autocommit = 1+0", 181 out: &vtgatepb.Session{Autocommit: true}, 182 }, { 183 in: "set autocommit = default", 184 out: &vtgatepb.Session{Autocommit: true}, 185 }, { 186 in: "set foo = 1", 187 err: "VT05006: unknown system variable '@@foo = 1'", 188 }, { 189 in: "set names utf8", 190 out: &vtgatepb.Session{Autocommit: true}, 191 }, { 192 in: "set names ascii", 193 err: "charset/name ascii is not supported", 194 }, { 195 in: "set charset utf8", 196 out: &vtgatepb.Session{Autocommit: true}, 197 }, { 198 in: "set character set default", 199 out: &vtgatepb.Session{Autocommit: true}, 200 }, { 201 in: "set character set ascii", 202 err: "charset/name ascii is not supported", 203 }, { 204 in: "set skip_query_plan_cache = 1", 205 out: &vtgatepb.Session{Autocommit: true, Options: &querypb.ExecuteOptions{SkipQueryPlanCache: true}}, 206 }, { 207 in: "set skip_query_plan_cache = 0", 208 out: &vtgatepb.Session{Autocommit: true, Options: &querypb.ExecuteOptions{}}, 209 }, { 210 in: "set tx_read_only = 2", 211 err: "variable 'tx_read_only' can't be set to the value: 2 is not a boolean", 212 }, { 213 in: "set transaction_read_only = 2", 214 err: "variable 'transaction_read_only' can't be set to the value: 2 is not a boolean", 215 }, { 216 in: "set session transaction isolation level repeatable read", 217 out: &vtgatepb.Session{Autocommit: true}, 218 }, { 219 in: "set session transaction isolation level read committed", 220 out: &vtgatepb.Session{Autocommit: true}, 221 }, { 222 in: "set session transaction isolation level read uncommitted", 223 out: &vtgatepb.Session{Autocommit: true}, 224 }, { 225 in: "set session transaction isolation level serializable", 226 out: &vtgatepb.Session{Autocommit: true}, 227 }, { 228 in: "set transaction isolation level serializable", 229 out: &vtgatepb.Session{ 230 Autocommit: true, 231 Warnings: []*querypb.QueryWarning{{Code: mysql.ERNotSupportedYet, Message: "converted 'next transaction' scope to 'session' scope"}}, 232 }, 233 }, { 234 in: "set transaction read only", 235 out: &vtgatepb.Session{Autocommit: true, Warnings: []*querypb.QueryWarning{{Code: mysql.ERNotSupportedYet, Message: "converted 'next transaction' scope to 'session' scope"}}}, 236 }, { 237 in: "set transaction read write", 238 out: &vtgatepb.Session{Autocommit: true, Warnings: []*querypb.QueryWarning{{Code: mysql.ERNotSupportedYet, Message: "converted 'next transaction' scope to 'session' scope"}}}, 239 }, { 240 in: "set session transaction read write", 241 out: &vtgatepb.Session{Autocommit: true}, 242 }, { 243 in: "set @@enable_system_settings = on", 244 out: &vtgatepb.Session{Autocommit: true, EnableSystemSettings: true}, 245 }, { 246 in: "set @@enable_system_settings = off", 247 out: &vtgatepb.Session{Autocommit: true, EnableSystemSettings: false}, 248 }, { 249 in: "set @@enable_system_settings = 1", 250 out: &vtgatepb.Session{Autocommit: true, EnableSystemSettings: true}, 251 }, { 252 in: "set @@enable_system_settings = 0", 253 out: &vtgatepb.Session{Autocommit: true, EnableSystemSettings: false}, 254 }, { 255 in: "set @@enable_system_settings = true", 256 out: &vtgatepb.Session{Autocommit: true, EnableSystemSettings: true}, 257 }, { 258 in: "set @@enable_system_settings = false", 259 out: &vtgatepb.Session{Autocommit: true, EnableSystemSettings: false}, 260 }, { 261 in: "set @@socket = '/tmp/change.sock'", 262 err: "VT03010: variable 'socket' is a read only variable", 263 }, { 264 in: "set @@query_timeout = 50", 265 out: &vtgatepb.Session{Autocommit: true, QueryTimeout: 50}, 266 }, { 267 in: "set @@query_timeout = 50, query_timeout = 75", 268 out: &vtgatepb.Session{Autocommit: true, QueryTimeout: 75}, 269 }} 270 for i, tcase := range testcases { 271 t.Run(fmt.Sprintf("%d-%s", i, tcase.in), func(t *testing.T) { 272 session := NewSafeSession(&vtgatepb.Session{Autocommit: true}) 273 _, err := executorEnv.Execute(context.Background(), "TestExecute", session, tcase.in, nil) 274 if tcase.err == "" { 275 require.NoError(t, err) 276 utils.MustMatch(t, tcase.out, session.Session, "new executor") 277 } else { 278 require.EqualError(t, err, tcase.err) 279 } 280 }) 281 } 282 } 283 284 func TestExecutorSetOp(t *testing.T) { 285 executor, _, _, sbclookup := createExecutorEnv() 286 sysVarSetEnabled = true 287 288 returnResult := func(columnName, typ, value string) *sqltypes.Result { 289 return sqltypes.MakeTestResult(sqltypes.MakeTestFields(columnName, typ), value) 290 } 291 returnNoResult := func(columnName, typ string) *sqltypes.Result { 292 return sqltypes.MakeTestResult(sqltypes.MakeTestFields(columnName, typ)) 293 } 294 295 testcases := []struct { 296 in string 297 warning []*querypb.QueryWarning 298 sysVars map[string]string 299 disallowResConn bool 300 result *sqltypes.Result 301 }{{ 302 in: "set big_tables = 1", // ignore 303 }, { 304 in: "set sql_mode = 'STRICT_ALL_TABLES,NO_AUTO_UPDATES'", 305 sysVars: map[string]string{"sql_mode": "'STRICT_ALL_TABLES,NO_AUTO_UPDATES'"}, 306 result: sqltypes.MakeTestResult(sqltypes.MakeTestFields("orig|new", "varchar|varchar"), "|STRICT_ALL_TABLES,NO_AUTO_UPDATES"), 307 }, { 308 // even though the tablet is saying that the value has changed, 309 // useReservedConn is false, so we won't allow this change 310 in: "set sql_mode = 'STRICT_ALL_TABLES,NO_AUTO_UPDATES'", 311 result: returnResult("sql_mode", "varchar", "STRICT_ALL_TABLES,NO_AUTO_UPDATES"), 312 sysVars: nil, 313 disallowResConn: true, 314 }, { 315 in: "set sql_safe_updates = 1", 316 sysVars: map[string]string{"sql_safe_updates": "1"}, 317 result: returnResult("sql_safe_updates", "int64", "1"), 318 }, { 319 in: "set sql_quote_show_create = 0", 320 sysVars: map[string]string{"sql_quote_show_create": "0"}, 321 result: returnResult("sql_quote_show_create", "int64", "0"), 322 }, { 323 in: "set foreign_key_checks = 1", 324 result: returnNoResult("foreign_key_checks", "int64"), 325 }, { 326 in: "set unique_checks = 0", 327 sysVars: map[string]string{"unique_checks": "0"}, 328 result: returnResult("unique_checks", "int64", "0"), 329 }, { 330 in: "set net_write_timeout = 600", 331 result: returnResult("net_write_timeout", "int64", "600"), 332 }, { 333 in: "set net_read_timeout = 600", 334 result: returnResult("net_read_timeout", "int64", "300"), 335 }, { 336 in: "set character_set_client = utf8", 337 result: returnResult("character_set_client", "varchar", "utf8"), 338 }, { 339 in: "set character_set_results=null", 340 result: returnNoResult("character_set_results", "varchar"), 341 }, { 342 in: "set character_set_results='binary'", 343 result: returnNoResult("character_set_results", "varchar"), 344 }, { 345 in: "set character_set_results='utf8'", 346 result: returnNoResult("character_set_results", "varchar"), 347 }, { 348 in: "set character_set_results=utf8mb4", 349 result: returnNoResult("character_set_results", "varchar"), 350 }, { 351 in: "set character_set_results='latin1'", 352 result: returnNoResult("character_set_results", "varchar"), 353 }, { 354 in: "set character_set_results='abcd'", 355 result: returnNoResult("character_set_results", "varchar"), 356 }, { 357 in: "set @@global.client_found_rows = 1", 358 result: returnNoResult("client_found_rows", "int64"), 359 }, { 360 in: "set global client_found_rows = 1", 361 result: returnNoResult("client_found_rows", "int64"), 362 }, { 363 in: "set tx_isolation = 'read-committed'", 364 sysVars: map[string]string{"tx_isolation": "'read-committed'"}, 365 result: returnResult("tx_isolation", "varchar", "read-committed"), 366 }} 367 for _, tcase := range testcases { 368 t.Run(tcase.in, func(t *testing.T) { 369 session := NewAutocommitSession(primarySession) 370 session.TargetString = KsTestUnsharded 371 session.EnableSystemSettings = !tcase.disallowResConn 372 sbclookup.SetResults([]*sqltypes.Result{tcase.result}) 373 _, err := executor.Execute( 374 context.Background(), 375 "TestExecute", 376 session, 377 tcase.in, 378 nil) 379 require.NoError(t, err) 380 utils.MustMatch(t, tcase.warning, session.Warnings, "") 381 utils.MustMatch(t, tcase.sysVars, session.SystemVariables, "") 382 }) 383 } 384 } 385 386 func TestExecutorSetMetadata(t *testing.T) { 387 executor, _, _, _ := createExecutorEnv() 388 session := NewSafeSession(&vtgatepb.Session{TargetString: "@primary", Autocommit: true}) 389 390 set := "set @@vitess_metadata.app_keyspace_v1= '1'" 391 _, err := executor.Execute(context.Background(), "TestExecute", session, set, nil) 392 assert.Equalf(t, vtrpcpb.Code_PERMISSION_DENIED, vterrors.Code(err), "expected error %v, got error: %v", vtrpcpb.Code_PERMISSION_DENIED, err) 393 394 vschemaacl.AuthorizedDDLUsers = "%" 395 defer func() { 396 vschemaacl.AuthorizedDDLUsers = "" 397 }() 398 399 executor, _, _, _ = createExecutorEnv() 400 session = NewSafeSession(&vtgatepb.Session{TargetString: "@primary", Autocommit: true}) 401 402 set = "set @@vitess_metadata.app_keyspace_v1= '1'" 403 _, err = executor.Execute(context.Background(), "TestExecute", session, set, nil) 404 assert.NoError(t, err, "%s error: %v", set, err) 405 406 show := `show vitess_metadata variables like 'app\\_keyspace\\_v_'` 407 result, err := executor.Execute(context.Background(), "TestExecute", session, show, nil) 408 assert.NoError(t, err) 409 410 want := "1" 411 got := result.Rows[0][1].ToString() 412 assert.Equalf(t, want, got, "want migrations %s, result %s", want, got) 413 414 // Update metadata 415 set = "set @@vitess_metadata.app_keyspace_v2='2'" 416 _, err = executor.Execute(context.Background(), "TestExecute", session, set, nil) 417 assert.NoError(t, err, "%s error: %v", set, err) 418 419 show = `show vitess_metadata variables like 'app\\_keyspace\\_v%'` 420 gotqr, err := executor.Execute(context.Background(), "TestExecute", session, show, nil) 421 assert.NoError(t, err) 422 423 wantqr := &sqltypes.Result{ 424 Fields: buildVarCharFields("Key", "Value"), 425 Rows: [][]sqltypes.Value{ 426 buildVarCharRow("app_keyspace_v1", "1"), 427 buildVarCharRow("app_keyspace_v2", "2"), 428 }, 429 RowsAffected: 2, 430 } 431 432 assert.Equal(t, wantqr.Fields, gotqr.Fields) 433 assert.ElementsMatch(t, wantqr.Rows, gotqr.Rows) 434 435 show = "show vitess_metadata variables" 436 gotqr, err = executor.Execute(context.Background(), "TestExecute", session, show, nil) 437 require.NoError(t, err) 438 439 assert.Equal(t, wantqr.Fields, gotqr.Fields) 440 assert.ElementsMatch(t, wantqr.Rows, gotqr.Rows) 441 } 442 443 func TestPlanExecutorSetUDV(t *testing.T) { 444 executor, _, _, _ := createExecutorEnv() 445 446 testcases := []struct { 447 in string 448 out *vtgatepb.Session 449 err string 450 }{{ 451 in: "set @FOO = 'bar'", 452 out: &vtgatepb.Session{UserDefinedVariables: createMap([]string{"foo"}, []any{"bar"}), Autocommit: true}, 453 }, { 454 in: "set @foo = 2", 455 out: &vtgatepb.Session{UserDefinedVariables: createMap([]string{"foo"}, []any{2}), Autocommit: true}, 456 }, { 457 in: "set @foo = 2.1, @bar = 'baz'", 458 out: &vtgatepb.Session{UserDefinedVariables: createMap([]string{"foo", "bar"}, []any{sqltypes.DecimalFloat(2.1), "baz"}), Autocommit: true}, 459 }} 460 for _, tcase := range testcases { 461 t.Run(tcase.in, func(t *testing.T) { 462 session := NewSafeSession(&vtgatepb.Session{Autocommit: true}) 463 _, err := executor.Execute(context.Background(), "TestExecute", session, tcase.in, nil) 464 if err != nil { 465 require.EqualError(t, err, tcase.err) 466 } else { 467 utils.MustMatch(t, tcase.out, session.Session, "session output was not as expected") 468 } 469 }) 470 } 471 } 472 473 func TestSetUDVFromTabletInput(t *testing.T) { 474 executor, sbc1, _, _ := createExecutorEnv() 475 476 fields := sqltypes.MakeTestFields("some", "VARCHAR") 477 sbc1.SetResults([]*sqltypes.Result{ 478 sqltypes.MakeTestResult( 479 fields, 480 "abc", 481 ), 482 }) 483 484 primarySession.TargetString = "TestExecutor" 485 defer func() { 486 primarySession.TargetString = "" 487 }() 488 _, err := executorExec(executor, "set @foo = concat('a','b','c')", nil) 489 require.NoError(t, err) 490 491 want := map[string]*querypb.BindVariable{"foo": sqltypes.StringBindVariable("abc")} 492 utils.MustMatch(t, want, primarySession.UserDefinedVariables, "") 493 } 494 495 func createMap(keys []string, values []any) map[string]*querypb.BindVariable { 496 result := make(map[string]*querypb.BindVariable) 497 for i, key := range keys { 498 variable, err := sqltypes.BuildBindVariable(values[i]) 499 if err != nil { 500 panic(err) 501 } 502 result[key] = variable 503 } 504 return result 505 } 506 507 func TestSetVar(t *testing.T) { 508 executor, _, _, sbc := createExecutorEnv() 509 executor.normalize = true 510 511 oldVersion := sqlparser.GetParserVersion() 512 sqlparser.SetParserVersion("80000") 513 defer func() { 514 sqlparser.SetParserVersion(oldVersion) 515 }() 516 session := NewAutocommitSession(&vtgatepb.Session{EnableSystemSettings: true, TargetString: KsTestUnsharded}) 517 518 sbc.SetResults([]*sqltypes.Result{sqltypes.MakeTestResult( 519 sqltypes.MakeTestFields("orig|new", "varchar|varchar"), 520 "|only_full_group_by")}) 521 522 _, err := executor.Execute(context.Background(), "TestSetVar", session, "set @@sql_mode = only_full_group_by", map[string]*querypb.BindVariable{}) 523 require.NoError(t, err) 524 525 tcases := []struct { 526 sql string 527 rc bool 528 }{ 529 {sql: "select 1 from user"}, 530 {sql: "update user set col = 2"}, 531 {sql: "delete from user"}, 532 {sql: "insert into user (id) values (1)"}, 533 {sql: "replace into user(id, col) values (1, 'new')"}, 534 {sql: "set autocommit = 0"}, 535 {sql: "show create table user"}, // reserved connection should not be set. 536 {sql: "create table foo(bar bigint)", rc: true}, 537 } 538 539 for _, tc := range tcases { 540 t.Run(tc.sql, func(t *testing.T) { 541 // reset reserved conn need. 542 session.SetReservedConn(false) 543 544 _, err = executor.Execute(context.Background(), "TestSetVar", session, tc.sql, map[string]*querypb.BindVariable{}) 545 require.NoError(t, err) 546 assert.Equal(t, tc.rc, session.InReservedConn()) 547 }) 548 } 549 } 550 551 func TestSetVarShowVariables(t *testing.T) { 552 executor, _, _, sbc := createExecutorEnv() 553 executor.normalize = true 554 555 oldVersion := sqlparser.GetParserVersion() 556 sqlparser.SetParserVersion("80000") 557 defer func() { 558 sqlparser.SetParserVersion(oldVersion) 559 }() 560 session := NewAutocommitSession(&vtgatepb.Session{EnableSystemSettings: true, TargetString: KsTestUnsharded}) 561 562 sbc.SetResults([]*sqltypes.Result{ 563 // select query result for checking any change in system settings 564 sqltypes.MakeTestResult(sqltypes.MakeTestFields("orig|new", "varchar|varchar"), 565 "|only_full_group_by"), 566 // show query result 567 sqltypes.MakeTestResult(sqltypes.MakeTestFields("Variable_name|Value", "varchar|varchar"), 568 "sql_mode|ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE")}) 569 570 _, err := executor.Execute(context.Background(), "TestSetVar", session, "set @@sql_mode = only_full_group_by", map[string]*querypb.BindVariable{}) 571 require.NoError(t, err) 572 573 // this should return the updated value of sql_mode. 574 qr, err := executor.Execute(context.Background(), "TestSetVar", session, "show variables like 'sql_mode'", map[string]*querypb.BindVariable{}) 575 require.NoError(t, err) 576 assert.False(t, session.InReservedConn(), "reserved connection should not be used") 577 assert.Equal(t, `[[VARCHAR("sql_mode") VARCHAR("only_full_group_by")]]`, fmt.Sprintf("%v", qr.Rows)) 578 } 579 580 func TestExecutorSetAndSelect(t *testing.T) { 581 e, _, _, sbc := createExecutorEnv() 582 e.normalize = true 583 584 testcases := []struct { 585 sysVar string 586 val string 587 exp string 588 }{{ 589 sysVar: "transaction_isolation", 590 exp: `[[VARCHAR("REPEATABLE-READ")]]`, 591 }, { 592 sysVar: "transaction_isolation", 593 val: "READ-COMMITTED", 594 exp: `[[VARCHAR("READ-COMMITTED")]]`, 595 }, { 596 sysVar: "tx_isolation", 597 val: "READ-UNCOMMITTED", 598 exp: `[[VARCHAR("READ-UNCOMMITTED")]]`, 599 }, { 600 sysVar: "tx_isolation", 601 exp: `[[VARCHAR("READ-UNCOMMITTED")]]`, // this returns the value set in previous query. 602 }} 603 session := NewAutocommitSession(&vtgatepb.Session{TargetString: KsTestUnsharded, EnableSystemSettings: true}) 604 for _, tcase := range testcases { 605 t.Run(fmt.Sprintf("%s-%s", tcase.sysVar, tcase.val), func(t *testing.T) { 606 sbc.ExecCount.Set(0) // reset the value 607 608 if tcase.val != "" { 609 // check query result for `select <new_setting> from dual where @@transaction_isolation != <new_setting> 610 // not always the check query is the first query, so setting it two times, as it will use one of those results. 611 sbc.SetResults([]*sqltypes.Result{ 612 sqltypes.MakeTestResult(sqltypes.MakeTestFields(tcase.sysVar, "varchar"), tcase.val), // one for set prequeries 613 sqltypes.MakeTestResult(sqltypes.MakeTestFields(tcase.sysVar, "varchar"), tcase.val), // second for check query 614 sqltypes.MakeTestResult(nil)}) // third one for new set query 615 616 setQ := fmt.Sprintf("set %s = '%s'", tcase.sysVar, tcase.val) 617 _, err := e.Execute(context.Background(), "TestExecutorSetAndSelect", session, setQ, nil) 618 require.NoError(t, err) 619 } 620 621 selectQ := fmt.Sprintf("select @@%s", tcase.sysVar) 622 // if the query reaches the shard, it will return REPEATABLE-READ isolation level. 623 sbc.SetResults([]*sqltypes.Result{sqltypes.MakeTestResult(sqltypes.MakeTestFields(tcase.sysVar, "varchar"), "REPEATABLE-READ")}) 624 625 qr, err := e.Execute(context.Background(), "TestExecutorSetAndSelect", session, selectQ, nil) 626 require.NoError(t, err) 627 assert.Equal(t, tcase.exp, fmt.Sprintf("%v", qr.Rows)) 628 }) 629 } 630 }