vitess.io/vitess@v0.16.2/go/test/endtoend/vtgate/queries/misc/misc_test.go (about)

     1  /*
     2  Copyright 2022 The Vitess Authors.
     3  
     4  Licensed under the Apache License, Version 2.0 (the "License");
     5  you may not use this file except in compliance with the License.
     6  You may obtain a copy of the License at
     7  
     8      http://www.apache.org/licenses/LICENSE-2.0
     9  
    10  Unless required by applicable law or agreed to in writing, software
    11  distributed under the License is distributed on an "AS IS" BASIS,
    12  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    13  See the License for the specific language governing permissions and
    14  limitations under the License.
    15  */
    16  
    17  package misc
    18  
    19  import (
    20  	"database/sql"
    21  	"fmt"
    22  	"strconv"
    23  	"strings"
    24  	"testing"
    25  
    26  	_ "github.com/go-sql-driver/mysql"
    27  
    28  	"github.com/stretchr/testify/assert"
    29  	"github.com/stretchr/testify/require"
    30  
    31  	"vitess.io/vitess/go/test/endtoend/cluster"
    32  	"vitess.io/vitess/go/test/endtoend/utils"
    33  )
    34  
    35  func start(t *testing.T) (utils.MySQLCompare, func()) {
    36  	mcmp, err := utils.NewMySQLCompare(t, vtParams, mysqlParams)
    37  	require.NoError(t, err)
    38  
    39  	deleteAll := func() {
    40  		tables := []string{"t1"}
    41  		for _, table := range tables {
    42  			_, _ = mcmp.ExecAndIgnore("delete from " + table)
    43  		}
    44  	}
    45  
    46  	deleteAll()
    47  
    48  	return mcmp, func() {
    49  		deleteAll()
    50  		mcmp.Close()
    51  		cluster.PanicHandler(t)
    52  	}
    53  }
    54  
    55  func TestBitVals(t *testing.T) {
    56  	mcmp, closer := start(t)
    57  	defer closer()
    58  
    59  	mcmp.Exec("insert into t1(id1, id2) values (0,0)")
    60  
    61  	mcmp.AssertMatches(`select b'1001', 0x9, B'010011011010'`, `[[VARBINARY("\t") VARBINARY("\t") VARBINARY("\x04\xda")]]`)
    62  	mcmp.AssertMatches(`select b'1001', 0x9, B'010011011010' from t1`, `[[VARBINARY("\t") VARBINARY("\t") VARBINARY("\x04\xda")]]`)
    63  	mcmp.AssertMatchesNoCompare(`select 1 + b'1001', 2 + 0x9, 3 + B'010011011010'`, `[[INT64(10) UINT64(11) INT64(1245)]]`, `[[UINT64(10) UINT64(11) UINT64(1245)]]`)
    64  	mcmp.AssertMatchesNoCompare(`select 1 + b'1001', 2 + 0x9, 3 + B'010011011010' from t1`, `[[INT64(10) UINT64(11) INT64(1245)]]`, `[[UINT64(10) UINT64(11) UINT64(1245)]]`)
    65  }
    66  
    67  func TestHexVals(t *testing.T) {
    68  	mcmp, closer := start(t)
    69  	defer closer()
    70  
    71  	mcmp.Exec("insert into t1(id1, id2) values (0,0)")
    72  
    73  	mcmp.AssertMatches(`select x'09', 0x9`, `[[VARBINARY("\t") VARBINARY("\t")]]`)
    74  	mcmp.AssertMatches(`select X'09', 0x9 from t1`, `[[VARBINARY("\t") VARBINARY("\t")]]`)
    75  	mcmp.AssertMatches(`select 1 + x'09', 2 + 0x9`, `[[UINT64(10) UINT64(11)]]`)
    76  	mcmp.AssertMatches(`select 1 + X'09', 2 + 0x9 from t1`, `[[UINT64(10) UINT64(11)]]`)
    77  }
    78  
    79  func TestDateTimeTimestampVals(t *testing.T) {
    80  	mcmp, closer := start(t)
    81  	defer closer()
    82  
    83  	mcmp.AssertMatches(`select date'2022-08-03'`, `[[DATE("2022-08-03")]]`)
    84  	mcmp.AssertMatches(`select time'12:34:56'`, `[[TIME("12:34:56")]]`)
    85  	mcmp.AssertMatches(`select timestamp'2012-12-31 11:30:45'`, `[[DATETIME("2012-12-31 11:30:45")]]`)
    86  }
    87  
    88  func TestInvalidDateTimeTimestampVals(t *testing.T) {
    89  	mcmp, closer := start(t)
    90  	defer closer()
    91  
    92  	_, err := mcmp.ExecAllowAndCompareError(`select date'2022'`)
    93  	require.Error(t, err)
    94  	_, err = mcmp.ExecAllowAndCompareError(`select time'12:34:56:78'`)
    95  	require.Error(t, err)
    96  	_, err = mcmp.ExecAllowAndCompareError(`select timestamp'2022'`)
    97  	require.Error(t, err)
    98  }
    99  
   100  func TestQueryTimeoutWithDual(t *testing.T) {
   101  	mcmp, closer := start(t)
   102  	defer closer()
   103  
   104  	_, err := utils.ExecAllowError(t, mcmp.VtConn, "select /*vt+ PLANNER=gen4 */ sleep(0.04) from dual")
   105  	assert.NoError(t, err)
   106  	_, err = utils.ExecAllowError(t, mcmp.VtConn, "select /*vt+ PLANNER=gen4 */ sleep(0.24) from dual")
   107  	assert.Error(t, err)
   108  	_, err = utils.ExecAllowError(t, mcmp.VtConn, "set @@session.query_timeout=20")
   109  	require.NoError(t, err)
   110  	_, err = utils.ExecAllowError(t, mcmp.VtConn, "select /*vt+ PLANNER=gen4 */ sleep(0.04) from dual")
   111  	assert.Error(t, err)
   112  	_, err = utils.ExecAllowError(t, mcmp.VtConn, "select /*vt+ PLANNER=gen4 */ sleep(0.01) from dual")
   113  	assert.NoError(t, err)
   114  	_, err = utils.ExecAllowError(t, mcmp.VtConn, "select /*vt+ PLANNER=gen4 QUERY_TIMEOUT_MS=500 */ sleep(0.24) from dual")
   115  	assert.NoError(t, err)
   116  	_, err = utils.ExecAllowError(t, mcmp.VtConn, "select /*vt+ PLANNER=gen4 QUERY_TIMEOUT_MS=10 */ sleep(0.04) from dual")
   117  	assert.Error(t, err)
   118  	_, err = utils.ExecAllowError(t, mcmp.VtConn, "select /*vt+ PLANNER=gen4 QUERY_TIMEOUT_MS=10 */ sleep(0.001) from dual")
   119  	assert.NoError(t, err)
   120  }
   121  
   122  func TestQueryTimeoutWithTables(t *testing.T) {
   123  	mcmp, closer := start(t)
   124  	defer closer()
   125  
   126  	// unsharded
   127  	utils.Exec(t, mcmp.VtConn, "insert /*vt+ QUERY_TIMEOUT_MS=1000 */ into uks.unsharded(id1) values (1),(2),(3),(4),(5)")
   128  	for i := 0; i < 12; i++ {
   129  		utils.Exec(t, mcmp.VtConn, "insert /*vt+ QUERY_TIMEOUT_MS=1000 */ into uks.unsharded(id1) select id1+5 from uks.unsharded")
   130  	}
   131  
   132  	utils.Exec(t, mcmp.VtConn, "select count(*) from uks.unsharded where id1 > 31")
   133  	utils.Exec(t, mcmp.VtConn, "select /*vt+ PLANNER=gen4 QUERY_TIMEOUT_MS=100 */ count(*) from uks.unsharded where id1 > 31")
   134  
   135  	// the query usually takes more than 5ms to return. So this should fail.
   136  	_, err := utils.ExecAllowError(t, mcmp.VtConn, "select /*vt+ PLANNER=gen4 QUERY_TIMEOUT_MS=1 */ count(*) from uks.unsharded where id1 > 31")
   137  	require.Error(t, err)
   138  	assert.Contains(t, err.Error(), "context deadline exceeded")
   139  	assert.Contains(t, err.Error(), "(errno 1317) (sqlstate 70100)")
   140  
   141  	// sharded
   142  	for i := 0; i < 300000; i += 1000 {
   143  		var str strings.Builder
   144  		for j := 1; j <= 1000; j++ {
   145  			if j == 1 {
   146  				str.WriteString(fmt.Sprintf("(%d)", i*1000+j))
   147  				continue
   148  			}
   149  			str.WriteString(fmt.Sprintf(",(%d)", i*1000+j))
   150  		}
   151  		utils.Exec(t, mcmp.VtConn, fmt.Sprintf("insert /*vt+ QUERY_TIMEOUT_MS=1000 */ into t1(id1) values %s", str.String()))
   152  	}
   153  	// too much data added in the loop, do drop and recreate the table.
   154  	defer func() {
   155  		mcmp.Exec("drop table t1")
   156  		mcmp.Exec(schemaSQL)
   157  	}()
   158  
   159  	utils.Exec(t, mcmp.VtConn, "select count(*) from t1 where id1 > 31")
   160  	utils.Exec(t, mcmp.VtConn, "select /*vt+ PLANNER=gen4 QUERY_TIMEOUT_MS=100 */ count(*) from t1 where id1 > 31")
   161  
   162  	// the query usually takes more than 5ms to return. So this should fail.
   163  	_, err = utils.ExecAllowError(t, mcmp.VtConn, "select /*vt+ PLANNER=gen4 QUERY_TIMEOUT_MS=1 */ count(*) from t1 where id1 > 31")
   164  	require.Error(t, err)
   165  	assert.Contains(t, err.Error(), "context deadline exceeded")
   166  	assert.Contains(t, err.Error(), "(errno 1317) (sqlstate 70100)")
   167  }
   168  
   169  // TestIntervalWithMathFunctions tests that the Interval keyword can be used with math functions.
   170  func TestIntervalWithMathFunctions(t *testing.T) {
   171  	mcmp, closer := start(t)
   172  	defer closer()
   173  
   174  	// Set the time zone explicitly to UTC, otherwise the output of FROM_UNIXTIME is going to be dependent
   175  	// on the time zone of the system.
   176  	mcmp.Exec("SET time_zone = '+00:00'")
   177  	mcmp.AssertMatches("select '2020-01-01' + interval month(DATE_SUB(FROM_UNIXTIME(1234), interval 1 month))-1 month", `[[CHAR("2020-12-01")]]`)
   178  	mcmp.AssertMatches("select DATE_ADD(MIN(FROM_UNIXTIME(1673444922)),interval -DAYOFWEEK(MIN(FROM_UNIXTIME(1673444922)))+1 DAY)", `[[DATETIME("2023-01-08 13:48:42")]]`)
   179  }
   180  
   181  // TestCast tests the queries that contain the cast function.
   182  func TestCast(t *testing.T) {
   183  	mcmp, closer := start(t)
   184  	defer closer()
   185  
   186  	mcmp.AssertMatches("select cast('2023-01-07 12:34:56' as date) limit 1", `[[DATE("2023-01-07")]]`)
   187  	mcmp.AssertMatches("select cast('2023-01-07 12:34:56' as date)", `[[DATE("2023-01-07")]]`)
   188  	mcmp.AssertMatches("select cast('3.2' as float)", `[[FLOAT32(3.2)]]`)
   189  	mcmp.AssertMatches("select cast('3.2' as double)", `[[FLOAT64(3.2)]]`)
   190  	mcmp.AssertMatches("select cast('3.2' as unsigned)", `[[UINT64(3)]]`)
   191  }
   192  
   193  func TestOuterJoinWithPredicate(t *testing.T) {
   194  	mcmp, closer := start(t)
   195  	defer closer()
   196  
   197  	// This test uses a predicate on the outer side.
   198  	// These can't be pushed down to MySQL and have
   199  	// to be evaluated on the vtgate, so we are checking
   200  	// that evalengine handles the predicate correctly
   201  
   202  	mcmp.Exec("insert into t1(id1, id2) values (0,0), (1,10), (2,20), (3,30), (4,40)")
   203  
   204  	mcmp.AssertMatchesNoOrder("select A.id1, B.id2 from t1 as A left join t1 as B on A.id1*10 = B.id2 WHERE B.id2 BETWEEN 20 AND 30",
   205  		`[[INT64(2) INT64(20)] [INT64(3) INT64(30)]]`)
   206  	mcmp.AssertMatchesNoOrder("select A.id1, B.id2 from t1 as A left join t1 as B on A.id1*10 = B.id2 WHERE B.id2 NOT BETWEEN 20 AND 30",
   207  		`[[INT64(0) INT64(0)] [INT64(1) INT64(10)] [INT64(4) INT64(40)]]`)
   208  }
   209  
   210  // This test ensures that we support PREPARE statement with 65530 parameters.
   211  // It opens a MySQL connection using the go-mysql driver and execute a select query
   212  // it then checks the result contains the proper rows and that it's not failing.
   213  func TestHighNumberOfParams(t *testing.T) {
   214  	mcmp, closer := start(t)
   215  	defer closer()
   216  
   217  	mcmp.Exec("insert into t1(id1) values (0), (1), (2), (3), (4)")
   218  
   219  	paramCount := 65530
   220  
   221  	// create the value and argument slices used to build the prepare stmt
   222  	var vals []any
   223  	var params []string
   224  	for i := 0; i < paramCount; i++ {
   225  		vals = append(vals, strconv.Itoa(i))
   226  		params = append(params, "?")
   227  	}
   228  
   229  	// connect to the vitess cluster
   230  	db, err := sql.Open("mysql", fmt.Sprintf("@tcp(%s:%v)/%s", vtParams.Host, vtParams.Port, vtParams.DbName))
   231  	require.NoError(t, err)
   232  
   233  	// run the query
   234  	r, err := db.Query(fmt.Sprintf("SELECT /*vt+ QUERY_TIMEOUT_MS=10000 */ id1 FROM t1 WHERE id1 in (%s) ORDER BY id1 ASC", strings.Join(params, ", ")), vals...)
   235  	require.NoError(t, err)
   236  
   237  	// check the results we got, we should get 5 rows with each: 0, 1, 2, 3, 4
   238  	// count is the row number we are currently visiting, also correspond to the
   239  	// column value we expect.
   240  	count := 0
   241  	for r.Next() {
   242  		j := -1
   243  		err := r.Scan(&j)
   244  		require.NoError(t, err)
   245  		require.Equal(t, j, count)
   246  		count++
   247  	}
   248  	require.Equal(t, 5, count)
   249  }