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  }