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 }