github.com/XiaoMi/Gaea@v1.2.5/parser/parser_test.go (about) 1 // Copyright 2015 PingCAP, Inc. 2 // 3 // Licensed under the Apache License, Version 2.0 (the "License"); 4 // you may not use this file except in compliance with the License. 5 // You may obtain a copy of the License at 6 // 7 // http://www.apache.org/licenses/LICENSE-2.0 8 // 9 // Unless required by applicable law or agreed to in writing, software 10 // distributed under the License is distributed on an "AS IS" BASIS, 11 // See the License for the specific language governing permissions and 12 // limitations under the License. 13 14 package parser 15 16 import ( 17 "fmt" 18 "runtime" 19 "strings" 20 "testing" 21 22 . "github.com/pingcap/check" 23 "github.com/pingcap/errors" 24 25 "github.com/XiaoMi/Gaea/mysql" 26 "github.com/XiaoMi/Gaea/parser/ast" 27 . "github.com/XiaoMi/Gaea/parser/format" 28 "github.com/XiaoMi/Gaea/parser/model" 29 "github.com/XiaoMi/Gaea/parser/terror" 30 types "github.com/XiaoMi/Gaea/parser/tidb-types" 31 driver "github.com/XiaoMi/Gaea/parser/tidb-types/parser_driver" 32 ) 33 34 func TestT(t *testing.T) { 35 CustomVerboseFlag = true 36 TestingT(t) 37 } 38 39 var _ = Suite(&testParserSuite{}) 40 41 type testParserSuite struct { 42 enableWindowFunc bool 43 } 44 45 func (s *testParserSuite) TestSimple(c *C) { 46 parser := New() 47 48 reservedKws := []string{ 49 "add", "all", "alter", "analyze", "and", "as", "asc", "between", "bigint", 50 "binary", "blob", "both", "by", "cascade", "case", "change", "character", "check", "collate", 51 "column", "constraint", "convert", "create", "cross", "current_date", "current_time", 52 "current_timestamp", "current_user", "database", "databases", "day_hour", "day_microsecond", 53 "day_minute", "day_second", "decimal", "default", "delete", "desc", "describe", 54 "distinct", "distinctRow", "div", "double", "drop", "dual", "else", "enclosed", "escaped", 55 "exists", "explain", "false", "float", "for", "force", "foreign", "from", 56 "fulltext", "grant", "group", "having", "hour_microsecond", "hour_minute", 57 "hour_second", "if", "ignore", "in", "index", "infile", "inner", "insert", "int", "into", "integer", 58 "interval", "is", "join", "key", "keys", "kill", "leading", "left", "like", "limit", "lines", "load", 59 "localtime", "localtimestamp", "lock", "longblob", "longtext", "mediumblob", "maxvalue", "mediumint", "mediumtext", 60 "minute_microsecond", "minute_second", "mod", "not", "no_write_to_binlog", "null", "numeric", 61 "on", "option", "optionally", "or", "order", "outer", "partition", "precision", "primary", "procedure", "range", "read", "real", 62 "references", "regexp", "rename", "repeat", "replace", "revoke", "restrict", "right", "rlike", 63 "schema", "schemas", "second_microsecond", "select", "set", "show", "smallint", 64 "starting", "table", "terminated", "then", "tinyblob", "tinyint", "tinytext", "to", 65 "trailing", "true", "union", "unique", "unlock", "unsigned", 66 "update", "use", "using", "utc_date", "values", "varbinary", "varchar", 67 "when", "where", "write", "xor", "year_month", "zerofill", 68 "generated", "virtual", "stored", "usage", 69 "delayed", "high_priority", "low_priority", 70 "cumeDist", "denseRank", "firstValue", "lag", "lastValue", "lead", "nthValue", "ntile", 71 "over", "percentRank", "rank", "row", "rows", "rowNumber", "window", 72 // TODO: support the following keywords 73 // "with", 74 } 75 for _, kw := range reservedKws { 76 src := fmt.Sprintf("SELECT * FROM db.%s;", kw) 77 _, err := parser.ParseOneStmt(src, "", "") 78 c.Assert(err, IsNil, Commentf("source %s", src)) 79 80 src = fmt.Sprintf("SELECT * FROM %s.desc", kw) 81 _, err = parser.ParseOneStmt(src, "", "") 82 c.Assert(err, IsNil, Commentf("source %s", src)) 83 84 src = fmt.Sprintf("SELECT t.%s FROM t", kw) 85 _, err = parser.ParseOneStmt(src, "", "") 86 c.Assert(err, IsNil, Commentf("source %s", src)) 87 } 88 89 // Testcase for unreserved keywords 90 unreservedKws := []string{ 91 "auto_increment", "after", "begin", "bit", "bool", "boolean", "charset", "columns", "commit", 92 "date", "datediff", "datetime", "deallocate", "do", "from_days", "end", "engine", "engines", "execute", "first", "full", 93 "local", "names", "offset", "password", "prepare", "quick", "rollback", "session", "signed", 94 "start", "global", "tables", "tablespace", "text", "time", "timestamp", "tidb", "transaction", "truncate", "unknown", 95 "value", "warnings", "year", "now", "substr", "subpartition", "subpartitions", "substring", "mode", "any", "some", "user", "identified", 96 "collation", "comment", "avg_row_length", "checksum", "compression", "connection", "key_block_size", 97 "max_rows", "min_rows", "national", "quarter", "escape", "grants", "status", "fields", "triggers", 98 "delay_key_write", "isolation", "partitions", "repeatable", "committed", "uncommitted", "only", "serializable", "level", 99 "curtime", "variables", "dayname", "version", "btree", "hash", "row_format", "dynamic", "fixed", "compressed", 100 "compact", "redundant", "sql_no_cache sql_no_cache", "sql_cache sql_cache", "action", "round", 101 "enable", "disable", "reverse", "space", "privileges", "get_lock", "release_lock", "sleep", "no", "greatest", "least", 102 "binlog", "hex", "unhex", "function", "indexes", "from_unixtime", "processlist", "events", "less", "than", "timediff", 103 "ln", "log", "log2", "log10", "timestampdiff", "pi", "quote", "none", "super", "shared", "exclusive", 104 "always", "stats", "stats_meta", "stats_histogram", "stats_buckets", "stats_healthy", "tidb_version", "replication", "slave", "client", 105 "max_connections_per_hour", "max_queries_per_hour", "max_updates_per_hour", "max_user_connections", "event", "reload", "routine", "temporary", 106 "following", "preceding", "unbounded", "respect", "nulls", "current", "last", 107 } 108 for _, kw := range unreservedKws { 109 src := fmt.Sprintf("SELECT %s FROM tbl;", kw) 110 _, err := parser.ParseOneStmt(src, "", "") 111 c.Assert(err, IsNil, Commentf("source %s", src)) 112 } 113 114 // Testcase for prepared statement 115 src := "SELECT id+?, id+? from t;" 116 _, err := parser.ParseOneStmt(src, "", "") 117 c.Assert(err, IsNil) 118 119 // Testcase for -- Comment and unary -- operator 120 src = "CREATE TABLE foo (a SMALLINT UNSIGNED, b INT UNSIGNED); -- foo\nSelect --1 from foo;" 121 stmts, _, err := parser.Parse(src, "", "") 122 c.Assert(err, IsNil) 123 c.Assert(stmts, HasLen, 2) 124 125 // Testcase for /*! xx */ 126 // See http://dev.mysql.com/doc/refman/5.7/en/comments.html 127 // Fix: https://github.com/pingcap/tidb/issues/971 128 src = "/*!40101 SET character_set_client = utf8 */;" 129 stmts, _, err = parser.Parse(src, "", "") 130 c.Assert(err, IsNil) 131 c.Assert(stmts, HasLen, 1) 132 stmt := stmts[0] 133 _, ok := stmt.(*ast.SetStmt) 134 c.Assert(ok, IsTrue) 135 136 // for issue #2017 137 src = "insert into blobtable (a) values ('/*! truncated */');" 138 stmt, err = parser.ParseOneStmt(src, "", "") 139 c.Assert(err, IsNil) 140 is, ok := stmt.(*ast.InsertStmt) 141 c.Assert(ok, IsTrue) 142 c.Assert(is.Lists, HasLen, 1) 143 c.Assert(is.Lists[0], HasLen, 1) 144 c.Assert(is.Lists[0][0].(ast.ValueExpr).GetDatumString(), Equals, "/*! truncated */") 145 146 // Testcase for CONVERT(expr,type) 147 src = "SELECT CONVERT('111', SIGNED);" 148 st, err := parser.ParseOneStmt(src, "", "") 149 c.Assert(err, IsNil) 150 ss, ok := st.(*ast.SelectStmt) 151 c.Assert(ok, IsTrue) 152 c.Assert(len(ss.Fields.Fields), Equals, 1) 153 cv, ok := ss.Fields.Fields[0].Expr.(*ast.FuncCastExpr) 154 c.Assert(ok, IsTrue) 155 c.Assert(cv.FunctionType, Equals, ast.CastConvertFunction) 156 157 // for query start with comment 158 srcs := []string{ 159 "/* some comments */ SELECT CONVERT('111', SIGNED) ;", 160 "/* some comments */ /*comment*/ SELECT CONVERT('111', SIGNED) ;", 161 "SELECT /*comment*/ CONVERT('111', SIGNED) ;", 162 "SELECT CONVERT('111', /*comment*/ SIGNED) ;", 163 "SELECT CONVERT('111', SIGNED) /*comment*/;", 164 } 165 for _, src := range srcs { 166 st, err = parser.ParseOneStmt(src, "", "") 167 c.Assert(err, IsNil) 168 ss, ok = st.(*ast.SelectStmt) 169 c.Assert(ok, IsTrue) 170 } 171 172 // for issue #961 173 src = "create table t (c int key);" 174 st, err = parser.ParseOneStmt(src, "", "") 175 c.Assert(err, IsNil) 176 cs, ok := st.(*ast.CreateTableStmt) 177 c.Assert(ok, IsTrue) 178 c.Assert(cs.Cols, HasLen, 1) 179 c.Assert(cs.Cols[0].Options, HasLen, 1) 180 c.Assert(cs.Cols[0].Options[0].Tp, Equals, ast.ColumnOptionPrimaryKey) 181 182 // for issue #4497 183 src = "create table t1(a NVARCHAR(100));" 184 _, err = parser.ParseOneStmt(src, "", "") 185 c.Assert(err, IsNil) 186 187 // for issue 2803 188 src = "use quote;" 189 _, err = parser.ParseOneStmt(src, "", "") 190 c.Assert(err, IsNil) 191 192 // issue #4354 193 src = "select b'';" 194 _, err = parser.ParseOneStmt(src, "", "") 195 c.Assert(err, IsNil) 196 197 src = "select B'';" 198 _, err = parser.ParseOneStmt(src, "", "") 199 c.Assert(err, IsNil) 200 201 // src = "select 0b'';" 202 // _, err = parser.ParseOneStmt(src, "", "") 203 // c.Assert(err, NotNil) 204 205 // for #4909, support numericType `signed` filedOpt. 206 src = "CREATE TABLE t(_sms smallint signed, _smu smallint unsigned);" 207 _, err = parser.ParseOneStmt(src, "", "") 208 c.Assert(err, IsNil) 209 210 // for #7371, support NATIONAL CHARACTER 211 // reference link: https://dev.mysql.com/doc/refman/5.7/en/charset-national.html 212 src = "CREATE TABLE t(c1 NATIONAL CHARACTER(10));" 213 _, err = parser.ParseOneStmt(src, "", "") 214 c.Assert(err, IsNil) 215 216 src = `CREATE TABLE t(a tinyint signed, 217 b smallint signed, 218 c mediumint signed, 219 d int signed, 220 e int1 signed, 221 f int2 signed, 222 g int3 signed, 223 h int4 signed, 224 i int8 signed, 225 j integer signed, 226 k bigint signed, 227 l bool signed, 228 m boolean signed 229 );` 230 231 st, err = parser.ParseOneStmt(src, "", "") 232 c.Assert(err, IsNil) 233 ct, ok := st.(*ast.CreateTableStmt) 234 c.Assert(ok, IsTrue) 235 for _, col := range ct.Cols { 236 c.Assert(col.Tp.Flag&mysql.UnsignedFlag, Equals, uint(0)) 237 } 238 239 // for issue #4006 240 src = `insert into tb(v) (select v from tb);` 241 _, err = parser.ParseOneStmt(src, "", "") 242 c.Assert(err, IsNil) 243 } 244 245 type testCase struct { 246 src string 247 ok bool 248 restore string 249 } 250 251 type testErrMsgCase struct { 252 src string 253 ok bool 254 err error 255 } 256 257 func (s *testParserSuite) RunTest(c *C, table []testCase) { 258 parser := New() 259 parser.EnableWindowFunc(s.enableWindowFunc) 260 for _, t := range table { 261 _, _, err := parser.Parse(t.src, "", "") 262 comment := Commentf("source %v", t.src) 263 if !t.ok { 264 c.Assert(err, NotNil, comment) 265 continue 266 } 267 c.Assert(err, IsNil, comment) 268 // restore correctness test 269 if t.ok { 270 s.RunRestoreTest(c, t.src, t.restore) 271 } 272 } 273 } 274 275 func (s *testParserSuite) RunRestoreTest(c *C, sourceSQLs, expectSQLs string) { 276 var sb strings.Builder 277 parser := New() 278 parser.EnableWindowFunc(s.enableWindowFunc) 279 comment := Commentf("source %v", sourceSQLs) 280 stmts, _, err := parser.Parse(sourceSQLs, "", "") 281 c.Assert(err, IsNil, comment) 282 restoreSQLs := "" 283 for _, stmt := range stmts { 284 sb.Reset() 285 err = stmt.Restore(NewRestoreCtx(DefaultRestoreFlags, &sb)) 286 c.Assert(err, IsNil, comment) 287 restoreSQL := sb.String() 288 comment = Commentf("source %v; restore %v", sourceSQLs, restoreSQL) 289 restoreStmt, err := parser.ParseOneStmt(restoreSQL, "", "") 290 c.Assert(err, IsNil, comment) 291 CleanNodeText(stmt) 292 CleanNodeText(restoreStmt) 293 c.Assert(restoreStmt, DeepEquals, stmt, comment) 294 if restoreSQLs != "" { 295 restoreSQLs += "; " 296 } 297 restoreSQLs += restoreSQL 298 } 299 comment = Commentf("restore %v; expect %v", restoreSQLs, expectSQLs) 300 c.Assert(restoreSQLs, Equals, expectSQLs, comment) 301 } 302 303 func (s *testParserSuite) RunErrMsgTest(c *C, table []testErrMsgCase) { 304 parser := New() 305 for _, t := range table { 306 _, _, err := parser.Parse(t.src, "", "") 307 comment := Commentf("source %v", t.src) 308 if t.err != nil { 309 c.Assert(terror.ErrorEqual(err, t.err), IsTrue, comment) 310 } else { 311 c.Assert(err, IsNil, comment) 312 } 313 } 314 } 315 316 func (s *testParserSuite) TestDMLStmt(c *C) { 317 table := []testCase{ 318 {"", true, ""}, 319 {";", true, ""}, 320 {"INSERT INTO foo VALUES (1234)", true, "INSERT INTO `foo` VALUES (1234)"}, 321 {"INSERT INTO foo VALUES (1234, 5678)", true, "INSERT INTO `foo` VALUES (1234,5678)"}, 322 {"INSERT INTO t1 (SELECT * FROM t2)", true, "INSERT INTO `t1` SELECT * FROM `t2`"}, 323 // 15 324 {"INSERT INTO foo VALUES (1 || 2)", true, "INSERT INTO `foo` VALUES (1 OR 2)"}, 325 {"INSERT INTO foo VALUES (1 | 2)", true, "INSERT INTO `foo` VALUES (1|2)"}, 326 {"INSERT INTO foo VALUES (false || true)", true, "INSERT INTO `foo` VALUES (FALSE OR TRUE)"}, 327 {"INSERT INTO foo VALUES (bar(5678))", true, "INSERT INTO `foo` VALUES (BAR(5678))"}, 328 // 20 329 {"INSERT INTO foo VALUES ()", true, "INSERT INTO `foo` VALUES ()"}, 330 {"SELECT * FROM t", true, "SELECT * FROM `t`"}, 331 {"SELECT * FROM t AS u", true, "SELECT * FROM `t` AS `u`"}, 332 // 25 333 {"SELECT * FROM t, v", true, "SELECT * FROM (`t`) JOIN `v`"}, 334 {"SELECT * FROM t AS u, v", true, "SELECT * FROM (`t` AS `u`) JOIN `v`"}, 335 {"SELECT * FROM t, v AS w", true, "SELECT * FROM (`t`) JOIN `v` AS `w`"}, 336 {"SELECT * FROM t AS u, v AS w", true, "SELECT * FROM (`t` AS `u`) JOIN `v` AS `w`"}, 337 {"SELECT * FROM foo, bar, foo", true, "SELECT * FROM ((`foo`) JOIN `bar`) JOIN `foo`"}, 338 // 30 339 {"SELECT DISTINCTS * FROM t", false, ""}, 340 {"SELECT DISTINCT * FROM t", true, "SELECT DISTINCT * FROM `t`"}, 341 {"SELECT DISTINCTROW * FROM t", true, "SELECT DISTINCT * FROM `t`"}, 342 {"SELECT ALL * FROM t", true, "SELECT * FROM `t`"}, 343 {"SELECT DISTINCT ALL * FROM t", false, ""}, 344 {"SELECT DISTINCTROW ALL * FROM t", false, ""}, 345 {"INSERT INTO foo (a) VALUES (42)", true, "INSERT INTO `foo` (`a`) VALUES (42)"}, 346 {"INSERT INTO foo (a,) VALUES (42,)", false, ""}, 347 // 35 348 {"INSERT INTO foo (a,b) VALUES (42,314)", true, "INSERT INTO `foo` (`a`,`b`) VALUES (42,314)"}, 349 {"INSERT INTO foo (a,b,) VALUES (42,314)", false, ""}, 350 {"INSERT INTO foo (a,b,) VALUES (42,314,)", false, ""}, 351 {"INSERT INTO foo () VALUES ()", true, "INSERT INTO `foo` () VALUES ()"}, 352 {"INSERT INTO foo VALUE ()", true, "INSERT INTO `foo` VALUES ()"}, 353 354 // for issue 2402 355 {"INSERT INTO tt VALUES (01000001783);", true, "INSERT INTO `tt` VALUES (1000001783)"}, 356 {"INSERT INTO tt VALUES (default);", true, "INSERT INTO `tt` VALUES (DEFAULT)"}, 357 358 {"REPLACE INTO foo VALUES (1 || 2)", true, "REPLACE INTO `foo` VALUES (1 OR 2)"}, 359 {"REPLACE INTO foo VALUES (1 | 2)", true, "REPLACE INTO `foo` VALUES (1|2)"}, 360 {"REPLACE INTO foo VALUES (false || true)", true, "REPLACE INTO `foo` VALUES (FALSE OR TRUE)"}, 361 {"REPLACE INTO foo VALUES (bar(5678))", true, "REPLACE INTO `foo` VALUES (BAR(5678))"}, 362 {"REPLACE INTO foo VALUES ()", true, "REPLACE INTO `foo` VALUES ()"}, 363 {"REPLACE INTO foo (a,b) VALUES (42,314)", true, "REPLACE INTO `foo` (`a`,`b`) VALUES (42,314)"}, 364 {"REPLACE INTO foo (a,b,) VALUES (42,314)", false, ""}, 365 {"REPLACE INTO foo (a,b,) VALUES (42,314,)", false, ""}, 366 {"REPLACE INTO foo () VALUES ()", true, "REPLACE INTO `foo` () VALUES ()"}, 367 {"REPLACE INTO foo VALUE ()", true, "REPLACE INTO `foo` VALUES ()"}, 368 // 40 369 {`SELECT stuff.id 370 FROM stuff 371 WHERE stuff.value >= ALL (SELECT stuff.value 372 FROM stuff)`, true, "SELECT `stuff`.`id` FROM `stuff` WHERE `stuff`.`value`>=ALL (SELECT `stuff`.`value` FROM `stuff`)"}, 373 {"BEGIN", true, "START TRANSACTION"}, 374 {"START TRANSACTION", true, "START TRANSACTION"}, 375 // 45 376 {"COMMIT", true, "COMMIT"}, 377 {"ROLLBACK", true, "ROLLBACK"}, 378 {`BEGIN; 379 INSERT INTO foo VALUES (42, 3.14); 380 INSERT INTO foo VALUES (-1, 2.78); 381 COMMIT;`, true, "START TRANSACTION; INSERT INTO `foo` VALUES (42,3.14); INSERT INTO `foo` VALUES (-1,2.78); COMMIT"}, 382 {`BEGIN; 383 INSERT INTO tmp SELECT * from bar; 384 SELECT * from tmp; 385 ROLLBACK;`, true, "START TRANSACTION; INSERT INTO `tmp` SELECT * FROM `bar`; SELECT * FROM `tmp`; ROLLBACK"}, 386 387 // qualified select 388 {"SELECT a.b.c FROM t", true, "SELECT `a`.`b`.`c` FROM `t`"}, 389 {"SELECT a.b.*.c FROM t", false, ""}, 390 {"SELECT a.b.* FROM t", true, "SELECT `a`.`b`.* FROM `t`"}, 391 {"SELECT a FROM t", true, "SELECT `a` FROM `t`"}, 392 {"SELECT a.b.c.d FROM t", false, ""}, 393 394 // do statement 395 {"DO 1", true, "DO 1"}, 396 {"DO 1, sleep(1)", true, "DO 1, SLEEP(1)"}, 397 {"DO 1 from t", false, ""}, 398 399 // load data 400 {"load data local infile '/tmp/tmp.csv' into table t1 fields terminated by ',' optionally enclosed by '\"' ignore 1 lines", true, "LOAD DATA LOCAL INFILE '/tmp/tmp.csv' INTO TABLE `t1` FIELDS TERMINATED BY ',' ENCLOSED BY '\"' IGNORE 1 LINES"}, 401 {"load data infile '/tmp/t.csv' into table t", true, "LOAD DATA INFILE '/tmp/t.csv' INTO TABLE `t`"}, 402 {"load data infile '/tmp/t.csv' into table t character set utf8", true, "LOAD DATA INFILE '/tmp/t.csv' INTO TABLE `t`"}, 403 {"load data infile '/tmp/t.csv' into table t fields terminated by 'ab'", true, "LOAD DATA INFILE '/tmp/t.csv' INTO TABLE `t` FIELDS TERMINATED BY 'ab'"}, 404 {"load data infile '/tmp/t.csv' into table t columns terminated by 'ab'", true, "LOAD DATA INFILE '/tmp/t.csv' INTO TABLE `t` FIELDS TERMINATED BY 'ab'"}, 405 {"load data infile '/tmp/t.csv' into table t fields terminated by 'ab' enclosed by 'b'", true, "LOAD DATA INFILE '/tmp/t.csv' INTO TABLE `t` FIELDS TERMINATED BY 'ab' ENCLOSED BY 'b'"}, 406 {"load data infile '/tmp/t.csv' into table t fields terminated by 'ab' enclosed by 'b' escaped by '*'", true, "LOAD DATA INFILE '/tmp/t.csv' INTO TABLE `t` FIELDS TERMINATED BY 'ab' ENCLOSED BY 'b' ESCAPED BY '*'"}, 407 {"load data infile '/tmp/t.csv' into table t lines starting by 'ab'", true, "LOAD DATA INFILE '/tmp/t.csv' INTO TABLE `t` LINES STARTING BY 'ab'"}, 408 {"load data infile '/tmp/t.csv' into table t lines starting by 'ab' terminated by 'xy'", true, "LOAD DATA INFILE '/tmp/t.csv' INTO TABLE `t` LINES STARTING BY 'ab' TERMINATED BY 'xy'"}, 409 {"load data infile '/tmp/t.csv' into table t fields terminated by 'ab' lines terminated by 'xy'", true, "LOAD DATA INFILE '/tmp/t.csv' INTO TABLE `t` FIELDS TERMINATED BY 'ab' LINES TERMINATED BY 'xy'"}, 410 {"load data infile '/tmp/t.csv' into table t terminated by 'xy' fields terminated by 'ab'", false, ""}, 411 {"load data local infile '/tmp/t.csv' into table t", true, "LOAD DATA LOCAL INFILE '/tmp/t.csv' INTO TABLE `t`"}, 412 {"load data local infile '/tmp/t.csv' into table t fields terminated by 'ab'", true, "LOAD DATA LOCAL INFILE '/tmp/t.csv' INTO TABLE `t` FIELDS TERMINATED BY 'ab'"}, 413 {"load data local infile '/tmp/t.csv' into table t columns terminated by 'ab'", true, "LOAD DATA LOCAL INFILE '/tmp/t.csv' INTO TABLE `t` FIELDS TERMINATED BY 'ab'"}, 414 {"load data local infile '/tmp/t.csv' into table t fields terminated by 'ab' enclosed by 'b'", true, "LOAD DATA LOCAL INFILE '/tmp/t.csv' INTO TABLE `t` FIELDS TERMINATED BY 'ab' ENCLOSED BY 'b'"}, 415 {"load data local infile '/tmp/t.csv' into table t fields terminated by 'ab' enclosed by 'b' escaped by '*'", true, "LOAD DATA LOCAL INFILE '/tmp/t.csv' INTO TABLE `t` FIELDS TERMINATED BY 'ab' ENCLOSED BY 'b' ESCAPED BY '*'"}, 416 {"load data local infile '/tmp/t.csv' into table t character set utf8 fields terminated by 'ab' enclosed by 'b' escaped by '*'", true, "LOAD DATA LOCAL INFILE '/tmp/t.csv' INTO TABLE `t` FIELDS TERMINATED BY 'ab' ENCLOSED BY 'b' ESCAPED BY '*'"}, 417 {"load data local infile '/tmp/t.csv' into table t lines starting by 'ab'", true, "LOAD DATA LOCAL INFILE '/tmp/t.csv' INTO TABLE `t` LINES STARTING BY 'ab'"}, 418 {"load data local infile '/tmp/t.csv' into table t lines starting by 'ab' terminated by 'xy'", true, "LOAD DATA LOCAL INFILE '/tmp/t.csv' INTO TABLE `t` LINES STARTING BY 'ab' TERMINATED BY 'xy'"}, 419 {"load data local infile '/tmp/t.csv' into table t fields terminated by 'ab' lines terminated by 'xy'", true, "LOAD DATA LOCAL INFILE '/tmp/t.csv' INTO TABLE `t` FIELDS TERMINATED BY 'ab' LINES TERMINATED BY 'xy'"}, 420 {"load data local infile '/tmp/t.csv' into table t terminated by 'xy' fields terminated by 'ab'", false, ""}, 421 {"load data infile '/tmp/t.csv' into table t (a,b)", true, "LOAD DATA INFILE '/tmp/t.csv' INTO TABLE `t` (`a`,`b`)"}, 422 {"load data local infile '/tmp/t.csv' into table t (a,b)", true, "LOAD DATA LOCAL INFILE '/tmp/t.csv' INTO TABLE `t` (`a`,`b`)"}, 423 {"load data local infile '/tmp/t.csv' into table t fields terminated by 'ab' (a,b)", true, "LOAD DATA LOCAL INFILE '/tmp/t.csv' INTO TABLE `t` FIELDS TERMINATED BY 'ab' (`a`,`b`)"}, 424 {"load data local infile '/tmp/t.csv' into table t columns terminated by 'ab' (a,b)", true, "LOAD DATA LOCAL INFILE '/tmp/t.csv' INTO TABLE `t` FIELDS TERMINATED BY 'ab' (`a`,`b`)"}, 425 {"load data local infile '/tmp/t.csv' into table t fields terminated by 'ab' enclosed by 'b' (a,b)", true, "LOAD DATA LOCAL INFILE '/tmp/t.csv' INTO TABLE `t` FIELDS TERMINATED BY 'ab' ENCLOSED BY 'b' (`a`,`b`)"}, 426 {"load data local infile '/tmp/t.csv' into table t fields terminated by 'ab' enclosed by 'b' escaped by '*' (a,b)", true, "LOAD DATA LOCAL INFILE '/tmp/t.csv' INTO TABLE `t` FIELDS TERMINATED BY 'ab' ENCLOSED BY 'b' ESCAPED BY '*' (`a`,`b`)"}, 427 {"load data local infile '/tmp/t.csv' into table t character set utf8 fields terminated by 'ab' enclosed by 'b' escaped by '*' (a,b)", true, "LOAD DATA LOCAL INFILE '/tmp/t.csv' INTO TABLE `t` FIELDS TERMINATED BY 'ab' ENCLOSED BY 'b' ESCAPED BY '*' (`a`,`b`)"}, 428 {"load data local infile '/tmp/t.csv' into table t lines starting by 'ab' (a,b)", true, "LOAD DATA LOCAL INFILE '/tmp/t.csv' INTO TABLE `t` LINES STARTING BY 'ab' (`a`,`b`)"}, 429 {"load data local infile '/tmp/t.csv' into table t lines starting by 'ab' terminated by 'xy' (a,b)", true, "LOAD DATA LOCAL INFILE '/tmp/t.csv' INTO TABLE `t` LINES STARTING BY 'ab' TERMINATED BY 'xy' (`a`,`b`)"}, 430 {"load data local infile '/tmp/t.csv' into table t character set utf8 fields terminated by 'ab' lines terminated by 'xy' (a,b)", true, "LOAD DATA LOCAL INFILE '/tmp/t.csv' INTO TABLE `t` FIELDS TERMINATED BY 'ab' LINES TERMINATED BY 'xy' (`a`,`b`)"}, 431 {"load data local infile '/tmp/t.csv' into table t fields terminated by 'ab' lines terminated by 'xy' (a,b)", true, "LOAD DATA LOCAL INFILE '/tmp/t.csv' INTO TABLE `t` FIELDS TERMINATED BY 'ab' LINES TERMINATED BY 'xy' (`a`,`b`)"}, 432 {"load data local infile '/tmp/t.csv' into table t (a,b) fields terminated by 'ab'", false, ""}, 433 {"load data local infile '/tmp/t.csv' into table t ignore 1 lines", true, "LOAD DATA LOCAL INFILE '/tmp/t.csv' INTO TABLE `t` IGNORE 1 LINES"}, 434 {"load data local infile '/tmp/t.csv' into table t ignore -1 lines", false, ""}, 435 {"load data local infile '/tmp/t.csv' into table t fields terminated by 'ab' enclosed by 'b' (a,b) ignore 1 lines", false, ""}, 436 {"load data local infile '/tmp/t.csv' into table t lines starting by 'ab' terminated by 'xy' ignore 1 lines", true, "LOAD DATA LOCAL INFILE '/tmp/t.csv' INTO TABLE `t` LINES STARTING BY 'ab' TERMINATED BY 'xy' IGNORE 1 LINES"}, 437 {"load data local infile '/tmp/t.csv' into table t fields terminated by 'ab' enclosed by 'b' escaped by '*' ignore 1 lines (a,b)", true, "LOAD DATA LOCAL INFILE '/tmp/t.csv' INTO TABLE `t` FIELDS TERMINATED BY 'ab' ENCLOSED BY 'b' ESCAPED BY '*' IGNORE 1 LINES (`a`,`b`)"}, 438 {"load data local infile '/tmp/t.csv' into table t fields terminated by 'ab' enclosed by 'b' escaped by ''", true, "LOAD DATA LOCAL INFILE '/tmp/t.csv' INTO TABLE `t` FIELDS TERMINATED BY 'ab' ENCLOSED BY 'b' ESCAPED BY ''"}, 439 440 // select for update 441 {"SELECT * from t for update", true, "SELECT * FROM `t` FOR UPDATE"}, 442 {"SELECT * from t lock in share mode", true, "SELECT * FROM `t` LOCK IN SHARE MODE"}, 443 444 // from join 445 {"SELECT * from t1, t2, t3", true, "SELECT * FROM ((`t1`) JOIN `t2`) JOIN `t3`"}, 446 {"select * from t1 join t2 left join t3 on t2.id = t3.id", true, "SELECT * FROM (`t1` JOIN `t2`) LEFT JOIN `t3` ON `t2`.`id`=`t3`.`id`"}, 447 {"select * from t1 right join t2 on t1.id = t2.id left join t3 on t3.id = t2.id", true, "SELECT * FROM (`t1` RIGHT JOIN `t2` ON `t1`.`id`=`t2`.`id`) LEFT JOIN `t3` ON `t3`.`id`=`t2`.`id`"}, 448 {"select * from t1 right join t2 on t1.id = t2.id left join t3", false, ""}, 449 {"select * from t1 join t2 left join t3 using (id)", true, "SELECT * FROM (`t1` JOIN `t2`) LEFT JOIN `t3` USING (`id`)"}, 450 {"select * from t1 right join t2 using (id) left join t3 using (id)", true, "SELECT * FROM (`t1` RIGHT JOIN `t2` USING (`id`)) LEFT JOIN `t3` USING (`id`)"}, 451 {"select * from t1 right join t2 using (id) left join t3", false, ""}, 452 {"select * from t1 natural join t2", true, "SELECT * FROM `t1` NATURAL JOIN `t2`"}, 453 {"select * from t1 natural right join t2", true, "SELECT * FROM `t1` NATURAL RIGHT JOIN `t2`"}, 454 {"select * from t1 natural left outer join t2", true, "SELECT * FROM `t1` NATURAL LEFT JOIN `t2`"}, 455 {"select * from t1 natural inner join t2", false, ""}, 456 {"select * from t1 natural cross join t2", false, ""}, 457 458 // for straight_join 459 {"select * from t1 straight_join t2 on t1.id = t2.id", true, "SELECT * FROM `t1` STRAIGHT_JOIN `t2` ON `t1`.`id`=`t2`.`id`"}, 460 {"select straight_join * from t1 join t2 on t1.id = t2.id", true, "SELECT STRAIGHT_JOIN * FROM `t1` JOIN `t2` ON `t1`.`id`=`t2`.`id`"}, 461 {"select straight_join * from t1 left join t2 on t1.id = t2.id", true, "SELECT STRAIGHT_JOIN * FROM `t1` LEFT JOIN `t2` ON `t1`.`id`=`t2`.`id`"}, 462 {"select straight_join * from t1 right join t2 on t1.id = t2.id", true, "SELECT STRAIGHT_JOIN * FROM `t1` RIGHT JOIN `t2` ON `t1`.`id`=`t2`.`id`"}, 463 {"select straight_join * from t1 straight_join t2 on t1.id = t2.id", true, "SELECT STRAIGHT_JOIN * FROM `t1` STRAIGHT_JOIN `t2` ON `t1`.`id`=`t2`.`id`"}, 464 465 // delete statement 466 // single table syntax 467 {"DELETE from t1", true, "DELETE FROM `t1`"}, 468 {"DELETE LOW_priORITY from t1", true, "DELETE LOW_PRIORITY FROM `t1`"}, 469 {"DELETE quick from t1", true, "DELETE QUICK FROM `t1`"}, 470 {"DELETE ignore from t1", true, "DELETE IGNORE FROM `t1`"}, 471 {"DELETE low_priority quick ignore from t1", true, "DELETE LOW_PRIORITY QUICK IGNORE FROM `t1`"}, 472 {"DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a", true, "DELETE FROM `t1` WHERE `t1`.`a`>0 ORDER BY `t1`.`a`"}, 473 {"delete from t1 where a=26", true, "DELETE FROM `t1` WHERE `a`=26"}, 474 {"DELETE from t1 where a=1 limit 1", true, "DELETE FROM `t1` WHERE `a`=1 LIMIT 1"}, 475 {"DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a LIMIT 1", true, "DELETE FROM `t1` WHERE `t1`.`a`>0 ORDER BY `t1`.`a` LIMIT 1"}, 476 477 // multi table syntax: before from 478 {"delete low_priority t1, t2 from t1, t2", true, "DELETE LOW_PRIORITY `t1`,`t2` FROM (`t1`) JOIN `t2`"}, 479 {"delete quick t1, t2 from t1, t2", true, "DELETE QUICK `t1`,`t2` FROM (`t1`) JOIN `t2`"}, 480 {"delete ignore t1, t2 from t1, t2", true, "DELETE IGNORE `t1`,`t2` FROM (`t1`) JOIN `t2`"}, 481 {"delete low_priority quick ignore t1, t2 from t1, t2 where t1.a > 5", true, "DELETE LOW_PRIORITY QUICK IGNORE `t1`,`t2` FROM (`t1`) JOIN `t2` WHERE `t1`.`a`>5"}, 482 {"delete t1, t2 from t1, t2", true, "DELETE `t1`,`t2` FROM (`t1`) JOIN `t2`"}, 483 {"delete t1, t2 from t1, t2 where t1.a = 1 and t2.b <> 1", true, "DELETE `t1`,`t2` FROM (`t1`) JOIN `t2` WHERE `t1`.`a`=1 AND `t2`.`b`!=1"}, 484 {"delete t1 from t1, t2", true, "DELETE `t1` FROM (`t1`) JOIN `t2`"}, 485 {"delete t2 from t1, t2", true, "DELETE `t2` FROM (`t1`) JOIN `t2`"}, 486 {"delete t1 from t1", true, "DELETE `t1` FROM `t1`"}, 487 {"delete t1,t2,t3 from t1, t2, t3", true, "DELETE `t1`,`t2`,`t3` FROM ((`t1`) JOIN `t2`) JOIN `t3`"}, 488 {"delete t1,t2,t3 from t1, t2, t3 where t3.c < 5 and t1.a = 3", true, "DELETE `t1`,`t2`,`t3` FROM ((`t1`) JOIN `t2`) JOIN `t3` WHERE `t3`.`c`<5 AND `t1`.`a`=3"}, 489 {"delete t1 from t1, t1 as t2 where t1.b = t2.b and t1.a > t2.a", true, "DELETE `t1` FROM (`t1`) JOIN `t1` AS `t2` WHERE `t1`.`b`=`t2`.`b` AND `t1`.`a`>`t2`.`a`"}, 490 {"delete t1.*,t2 from t1, t2", true, "DELETE `t1`,`t2` FROM (`t1`) JOIN `t2`"}, 491 {"delete t1.*, t2.* from t1, t2", true, "DELETE `t1`,`t2` FROM (`t1`) JOIN `t2`"}, 492 {"delete t11.*, t12.* from t11, t12 where t11.a = t12.a and t11.b <> 1", true, "DELETE `t11`,`t12` FROM (`t11`) JOIN `t12` WHERE `t11`.`a`=`t12`.`a` AND `t11`.`b`!=1"}, 493 494 // multi table syntax: with using 495 {"DELETE quick FROM t1,t2 USING t1,t2", true, "DELETE QUICK FROM `t1`,`t2` USING (`t1`) JOIN `t2`"}, 496 {"DELETE low_priority ignore FROM t1,t2 USING t1,t2", true, "DELETE LOW_PRIORITY IGNORE FROM `t1`,`t2` USING (`t1`) JOIN `t2`"}, 497 {"DELETE low_priority quick ignore FROM t1,t2 USING t1,t2", true, "DELETE LOW_PRIORITY QUICK IGNORE FROM `t1`,`t2` USING (`t1`) JOIN `t2`"}, 498 {"DELETE FROM t1 USING t1 WHERE post='1'", true, "DELETE FROM `t1` USING `t1` WHERE `post`='1'"}, 499 {"DELETE FROM t1,t2 USING t1,t2", true, "DELETE FROM `t1`,`t2` USING (`t1`) JOIN `t2`"}, 500 {"DELETE FROM t1,t2,t3 USING t1,t2,t3 where t3.a = 1", true, "DELETE FROM `t1`,`t2`,`t3` USING ((`t1`) JOIN `t2`) JOIN `t3` WHERE `t3`.`a`=1"}, 501 {"DELETE FROM t2,t3 USING t1,t2,t3 where t1.a = 1", true, "DELETE FROM `t2`,`t3` USING ((`t1`) JOIN `t2`) JOIN `t3` WHERE `t1`.`a`=1"}, 502 {"DELETE FROM t2.*,t3.* USING t1,t2,t3 where t1.a = 1", true, "DELETE FROM `t2`,`t3` USING ((`t1`) JOIN `t2`) JOIN `t3` WHERE `t1`.`a`=1"}, 503 {"DELETE FROM t1,t2.*,t3.* USING t1,t2,t3 where t1.a = 1", true, "DELETE FROM `t1`,`t2`,`t3` USING ((`t1`) JOIN `t2`) JOIN `t3` WHERE `t1`.`a`=1"}, 504 505 // for delete statement 506 {"DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id;", true, "DELETE `t1`,`t2` FROM (`t1` JOIN `t2`) JOIN `t3` WHERE `t1`.`id`=`t2`.`id` AND `t2`.`id`=`t3`.`id`"}, 507 {"DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id;", true, "DELETE FROM `t1`,`t2` USING (`t1` JOIN `t2`) JOIN `t3` WHERE `t1`.`id`=`t2`.`id` AND `t2`.`id`=`t3`.`id`"}, 508 // for optimizer hint in delete statement 509 {"DELETE /*+ TiDB_INLJ(t1, t2) */ t1, t2 from t1, t2 where t1.id=t2.id;", true, "DELETE /*+ TIDB_INLJ(`t1`, `t2`)*/ `t1`,`t2` FROM (`t1`) JOIN `t2` WHERE `t1`.`id`=`t2`.`id`"}, 510 {"DELETE /*+ TiDB_HJ(t1, t2) */ t1, t2 from t1, t2 where t1.id=t2.id", true, "DELETE /*+ TIDB_HJ(`t1`, `t2`)*/ `t1`,`t2` FROM (`t1`) JOIN `t2` WHERE `t1`.`id`=`t2`.`id`"}, 511 {"DELETE /*+ TiDB_SMJ(t1, t2) */ t1, t2 from t1, t2 where t1.id=t2.id", true, "DELETE /*+ TIDB_SMJ(`t1`, `t2`)*/ `t1`,`t2` FROM (`t1`) JOIN `t2` WHERE `t1`.`id`=`t2`.`id`"}, 512 // for "USE INDEX" in delete statement 513 {"DELETE FROM t1 USE INDEX(idx_a) WHERE t1.id=1;", true, "DELETE FROM `t1` USE INDEX (`idx_a`) WHERE `t1`.`id`=1"}, 514 {"DELETE t1, t2 FROM t1 USE INDEX(idx_a) JOIN t2 WHERE t1.id=t2.id;", true, "DELETE `t1`,`t2` FROM `t1` USE INDEX (`idx_a`) JOIN `t2` WHERE `t1`.`id`=`t2`.`id`"}, 515 {"DELETE t1, t2 FROM t1 USE INDEX(idx_a) JOIN t2 USE INDEX(idx_a) WHERE t1.id=t2.id;", true, "DELETE `t1`,`t2` FROM `t1` USE INDEX (`idx_a`) JOIN `t2` USE INDEX (`idx_a`) WHERE `t1`.`id`=`t2`.`id`"}, 516 517 // for fail case 518 {"DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id limit 10;", false, ""}, 519 {"DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id order by t1.id;", false, ""}, 520 521 // for admin 522 {"admin show ddl;", true, "ADMIN SHOW DDL"}, 523 {"admin show ddl jobs;", true, "ADMIN SHOW DDL JOBS"}, 524 {"admin show ddl jobs 20;", true, "ADMIN SHOW DDL JOBS 20"}, 525 {"admin show ddl jobs -1;", false, ""}, 526 {"admin show ddl job queries 1", true, "ADMIN SHOW DDL JOB QUERIES 1"}, 527 {"admin show ddl job queries 1, 2, 3, 4", true, "ADMIN SHOW DDL JOB QUERIES 1, 2, 3, 4"}, 528 {"admin show t1 next_row_id", true, "ADMIN SHOW `t1` NEXT_ROW_ID"}, 529 {"admin check table t1, t2;", true, "ADMIN CHECK TABLE `t1`, `t2`"}, 530 {"admin check index tableName idxName;", true, "ADMIN CHECK INDEX `tableName` idxName"}, 531 {"admin check index tableName idxName (1, 2), (4, 5);", true, "ADMIN CHECK INDEX `tableName` idxName (1,2), (4,5)"}, 532 {"admin checksum table t1, t2;", true, "ADMIN CHECKSUM TABLE `t1`, `t2`"}, 533 {"admin cancel ddl jobs 1", true, "ADMIN CANCEL DDL JOBS 1"}, 534 {"admin cancel ddl jobs 1, 2", true, "ADMIN CANCEL DDL JOBS 1, 2"}, 535 {"admin recover index t1 idx_a", true, "ADMIN RECOVER INDEX `t1` idx_a"}, 536 {"admin cleanup index t1 idx_a", true, "ADMIN CLEANUP INDEX `t1` idx_a"}, 537 {"admin show slow top 3", true, "ADMIN SHOW SLOW TOP 3"}, 538 {"admin show slow top internal 7", true, "ADMIN SHOW SLOW TOP INTERNAL 7"}, 539 {"admin show slow top all 9", true, "ADMIN SHOW SLOW TOP ALL 9"}, 540 {"admin show slow recent 11", true, "ADMIN SHOW SLOW RECENT 11"}, 541 {"admin restore table by job 11", true, "ADMIN RESTORE TABLE BY JOB 11"}, 542 {"admin restore table by job 11,12,13", false, ""}, 543 {"admin restore table by job", false, ""}, 544 {"admin restore table t1", true, "ADMIN RESTORE TABLE `t1`"}, 545 {"admin restore table t1,t2", false, ""}, 546 {"admin restore table ", false, ""}, 547 {"admin restore table t1 100", true, "ADMIN RESTORE TABLE `t1` 100"}, 548 {"admin restore table t1 abc", false, ""}, 549 550 // for on duplicate key update 551 {"INSERT INTO t (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);", true, "INSERT INTO `t` (`a`,`b`,`c`) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE `c`=VALUES(`a`)+VALUES(`b`)"}, 552 {"INSERT IGNORE INTO t (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);", true, "INSERT IGNORE INTO `t` (`a`,`b`,`c`) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE `c`=VALUES(`a`)+VALUES(`b`)"}, 553 554 // for insert ... set 555 {"INSERT INTO t SET a=1,b=2", true, "INSERT INTO `t` SET `a`=1,`b`=2"}, 556 {"INSERT INTO t (a) SET a=1", false, ""}, 557 558 // for update statement 559 {"UPDATE LOW_PRIORITY IGNORE t SET id = id + 1 ORDER BY id DESC;", true, "UPDATE LOW_PRIORITY IGNORE `t` SET `id`=`id`+1 ORDER BY `id` DESC"}, 560 {"UPDATE t SET id = id + 1 ORDER BY id DESC;", true, "UPDATE `t` SET `id`=`id`+1 ORDER BY `id` DESC"}, 561 {"UPDATE t SET id = id + 1 ORDER BY id DESC limit 3 ;", true, "UPDATE `t` SET `id`=`id`+1 ORDER BY `id` DESC LIMIT 3"}, 562 {"UPDATE t SET id = id + 1, name = 'jojo';", true, "UPDATE `t` SET `id`=`id`+1, `name`='jojo'"}, 563 {"UPDATE items,month SET items.price=month.price WHERE items.id=month.id;", true, "UPDATE (`items`) JOIN `month` SET `items`.`price`=`month`.`price` WHERE `items`.`id`=`month`.`id`"}, 564 {"UPDATE user T0 LEFT OUTER JOIN user_profile T1 ON T1.id = T0.profile_id SET T0.profile_id = 1 WHERE T0.profile_id IN (1);", true, "UPDATE `user` AS `T0` LEFT JOIN `user_profile` AS `T1` ON `T1`.`id`=`T0`.`profile_id` SET `T0`.`profile_id`=1 WHERE `T0`.`profile_id` IN (1)"}, 565 {"UPDATE t1, t2 set t1.profile_id = 1, t2.profile_id = 1 where ta.a=t.ba", true, "UPDATE (`t1`) JOIN `t2` SET `t1`.`profile_id`=1, `t2`.`profile_id`=1 WHERE `ta`.`a`=`t`.`ba`"}, 566 // for optimizer hint in update statement 567 {"UPDATE /*+ TiDB_INLJ(t1, t2) */ t1, t2 set t1.profile_id = 1, t2.profile_id = 1 where ta.a=t.ba", true, "UPDATE /*+ TIDB_INLJ(`t1`, `t2`)*/ (`t1`) JOIN `t2` SET `t1`.`profile_id`=1, `t2`.`profile_id`=1 WHERE `ta`.`a`=`t`.`ba`"}, 568 {"UPDATE /*+ TiDB_SMJ(t1, t2) */ t1, t2 set t1.profile_id = 1, t2.profile_id = 1 where ta.a=t.ba", true, "UPDATE /*+ TIDB_SMJ(`t1`, `t2`)*/ (`t1`) JOIN `t2` SET `t1`.`profile_id`=1, `t2`.`profile_id`=1 WHERE `ta`.`a`=`t`.`ba`"}, 569 {"UPDATE /*+ TiDB_HJ(t1, t2) */ t1, t2 set t1.profile_id = 1, t2.profile_id = 1 where ta.a=t.ba", true, "UPDATE /*+ TIDB_HJ(`t1`, `t2`)*/ (`t1`) JOIN `t2` SET `t1`.`profile_id`=1, `t2`.`profile_id`=1 WHERE `ta`.`a`=`t`.`ba`"}, 570 // fail case for update statement 571 {"UPDATE items,month SET items.price=month.price WHERE items.id=month.id LIMIT 10;", false, ""}, 572 {"UPDATE items,month SET items.price=month.price WHERE items.id=month.id order by month.id;", false, ""}, 573 // for "USE INDEX" in delete statement 574 {"UPDATE t1 USE INDEX(idx_a) SET t1.price=3.25 WHERE t1.id=1;", true, "UPDATE `t1` USE INDEX (`idx_a`) SET `t1`.`price`=3.25 WHERE `t1`.`id`=1"}, 575 {"UPDATE t1 USE INDEX(idx_a) JOIN t2 SET t1.price=t2.price WHERE t1.id=t2.id;", true, "UPDATE `t1` USE INDEX (`idx_a`) JOIN `t2` SET `t1`.`price`=`t2`.`price` WHERE `t1`.`id`=`t2`.`id`"}, 576 {"UPDATE t1 USE INDEX(idx_a) JOIN t2 USE INDEX(idx_a) SET t1.price=t2.price WHERE t1.id=t2.id;", true, "UPDATE `t1` USE INDEX (`idx_a`) JOIN `t2` USE INDEX (`idx_a`) SET `t1`.`price`=`t2`.`price` WHERE `t1`.`id`=`t2`.`id`"}, 577 578 // for select with where clause 579 {"SELECT * FROM t WHERE 1 = 1", true, "SELECT * FROM `t` WHERE 1=1"}, 580 581 // for dual 582 {"select 1 from dual", true, "SELECT 1"}, 583 {"select 1 from dual limit 1", true, "SELECT 1 LIMIT 1"}, 584 {"select 1 where exists (select 2)", false, ""}, 585 {"select 1 from dual where not exists (select 2)", true, "SELECT 1 FROM DUAL WHERE NOT EXISTS (SELECT 2)"}, 586 {"select 1 as a from dual order by a", true, "SELECT 1 AS `a` ORDER BY `a`"}, 587 {"select 1 as a from dual where 1 < any (select 2) order by a", true, "SELECT 1 AS `a` FROM DUAL WHERE 1<ANY (SELECT 2) ORDER BY `a`"}, 588 {"select 1 order by 1", true, "SELECT 1 ORDER BY 1"}, 589 590 // for https://github.com/pingcap/tidb/issues/320 591 {`(select 1);`, true, "SELECT 1"}, 592 593 // for https://github.com/pingcap/tidb/issues/1050 594 {`SELECT /*!40001 SQL_NO_CACHE */ * FROM test WHERE 1 limit 0, 2000;`, true, "SELECT SQL_NO_CACHE * FROM `test` WHERE 1 LIMIT 0,2000"}, 595 596 {`ANALYZE TABLE t`, true, "ANALYZE TABLE `t`"}, 597 598 // for comments 599 {`/** 20180417 **/ show databases;`, true, "SHOW DATABASES"}, 600 {`/* 20180417 **/ show databases;`, true, "SHOW DATABASES"}, 601 {`/** 20180417 */ show databases;`, true, "SHOW DATABASES"}, 602 {`/** 20180417 ******/ show databases;`, true, "SHOW DATABASES"}, 603 604 // for Binlog stmt 605 {`BINLOG ' 606 BxSFVw8JAAAA8QAAAPUAAAAAAAQANS41LjQ0LU1hcmlhREItbG9nAAAAAAAAAAAAAAAAAAAAAAAA 607 AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA 608 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 609 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 610 AAAAAAAAAAAA5gm5Mg== 611 '/*!*/;`, true, `BINLOG ' 612 BxSFVw8JAAAA8QAAAPUAAAAAAAQANS41LjQ0LU1hcmlhREItbG9nAAAAAAAAAAAAAAAAAAAAAAAA 613 AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA 614 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 615 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 616 AAAAAAAAAAAA5gm5Mg== 617 '`}, 618 619 // for partition table dml 620 {"select * from t1 partition (p1)", true, "SELECT * FROM `t1` PARTITION(`p1`)"}, 621 {"select * from t1 partition (p1,p2)", true, "SELECT * FROM `t1` PARTITION(`p1`, `p2`)"}, 622 {"select * from t1 partition (`p1`, p2, p3)", true, "SELECT * FROM `t1` PARTITION(`p1`, `p2`, `p3`)"}, 623 {`select * from t1 partition ()`, false, ""}, 624 } 625 s.RunTest(c, table) 626 } 627 628 func (s *testParserSuite) TestDBAStmt(c *C) { 629 table := []testCase{ 630 // for SHOW statement 631 {"SHOW VARIABLES LIKE 'character_set_results'", true, "SHOW SESSION VARIABLES LIKE 'character_set_results'"}, 632 {"SHOW GLOBAL VARIABLES LIKE 'character_set_results'", true, "SHOW GLOBAL VARIABLES LIKE 'character_set_results'"}, 633 {"SHOW SESSION VARIABLES LIKE 'character_set_results'", true, "SHOW SESSION VARIABLES LIKE 'character_set_results'"}, 634 {"SHOW VARIABLES", true, "SHOW SESSION VARIABLES"}, 635 {"SHOW GLOBAL VARIABLES", true, "SHOW GLOBAL VARIABLES"}, 636 {"SHOW GLOBAL VARIABLES WHERE Variable_name = 'autocommit'", true, "SHOW GLOBAL VARIABLES WHERE `Variable_name`='autocommit'"}, 637 {"SHOW STATUS", true, "SHOW SESSION STATUS"}, 638 {"SHOW GLOBAL STATUS", true, "SHOW GLOBAL STATUS"}, 639 {"SHOW SESSION STATUS", true, "SHOW SESSION STATUS"}, 640 {`SHOW STATUS LIKE 'Up%'`, true, "SHOW SESSION STATUS LIKE 'Up%'"}, 641 {`SHOW STATUS WHERE Variable_name`, true, "SHOW SESSION STATUS WHERE `Variable_name`"}, 642 {`SHOW STATUS WHERE Variable_name LIKE 'Up%'`, true, "SHOW SESSION STATUS WHERE `Variable_name` LIKE 'Up%'"}, 643 {`SHOW FULL TABLES FROM icar_qa LIKE play_evolutions`, true, "SHOW FULL TABLES IN `icar_qa` LIKE `play_evolutions`"}, 644 {`SHOW FULL TABLES WHERE Table_Type != 'VIEW'`, true, "SHOW FULL TABLES WHERE `Table_Type`!='VIEW'"}, 645 {`SHOW GRANTS`, true, "SHOW GRANTS"}, 646 {`SHOW GRANTS FOR 'test'@'localhost'`, true, "SHOW GRANTS FOR `test`@`localhost`"}, 647 {`SHOW GRANTS FOR current_user()`, true, "SHOW GRANTS FOR CURRENT_USER"}, 648 {`SHOW GRANTS FOR current_user`, true, "SHOW GRANTS FOR CURRENT_USER"}, 649 {`SHOW COLUMNS FROM City;`, true, "SHOW COLUMNS IN `City`"}, 650 {`SHOW COLUMNS FROM tv189.1_t_1_x;`, true, "SHOW COLUMNS IN `tv189`.`1_t_1_x`"}, 651 {`SHOW FIELDS FROM City;`, true, "SHOW COLUMNS IN `City`"}, 652 {`SHOW TRIGGERS LIKE 't'`, true, "SHOW TRIGGERS LIKE 't'"}, 653 {`SHOW DATABASES LIKE 'test2'`, true, "SHOW DATABASES LIKE 'test2'"}, 654 // PROCEDURE and FUNCTION are currently not supported. 655 // And FUNCTION reuse show procedure status process logic. 656 {`SHOW PROCEDURE STATUS WHERE Db='test'`, true, "SHOW PROCEDURE STATUS WHERE `Db`='test'"}, 657 {`SHOW FUNCTION STATUS WHERE Db='test'`, true, "SHOW PROCEDURE STATUS WHERE `Db`='test'"}, 658 {`SHOW INDEX FROM t;`, true, "SHOW INDEX IN `t`"}, 659 {`SHOW KEYS FROM t;`, true, "SHOW INDEX IN `t`"}, 660 {`SHOW INDEX IN t;`, true, "SHOW INDEX IN `t`"}, 661 {`SHOW KEYS IN t;`, true, "SHOW INDEX IN `t`"}, 662 {`SHOW INDEXES IN t where true;`, true, "SHOW INDEX IN `t` WHERE TRUE"}, 663 {`SHOW KEYS FROM t FROM test where true;`, true, "SHOW INDEX IN `test`.`t` WHERE TRUE"}, 664 {`SHOW EVENTS FROM test_db WHERE definer = 'current_user'`, true, "SHOW EVENTS IN `test_db` WHERE `definer`='current_user'"}, 665 {`SHOW PLUGINS`, true, "SHOW PLUGINS"}, 666 {`SHOW PROFILES`, true, "SHOW PROFILES"}, 667 {`SHOW MASTER STATUS`, true, "SHOW MASTER STATUS"}, 668 {`SHOW PRIVILEGES`, true, "SHOW PRIVILEGES"}, 669 // for show character set 670 {"show character set;", true, "SHOW CHARSET"}, 671 {"show charset", true, "SHOW CHARSET"}, 672 // for show collation 673 {"show collation", true, "SHOW COLLATION"}, 674 {`show collation like 'utf8%'`, true, "SHOW COLLATION LIKE 'utf8%'"}, 675 {"show collation where Charset = 'utf8' and Collation = 'utf8_bin'", true, "SHOW COLLATION WHERE `Charset`='utf8' AND `Collation`='utf8_bin'"}, 676 // for show full columns 677 {"show columns in t;", true, "SHOW COLUMNS IN `t`"}, 678 {"show full columns in t;", true, "SHOW FULL COLUMNS IN `t`"}, 679 // for show create table 680 {"show create table test.t", true, "SHOW CREATE TABLE `test`.`t`"}, 681 {"show create table t", true, "SHOW CREATE TABLE `t`"}, 682 // for show create view 683 {"show create view test.t", true, "SHOW CREATE VIEW `test`.`t`"}, 684 {"show create view t", true, "SHOW CREATE VIEW `t`"}, 685 // for show create database 686 {"show create database d1", true, "SHOW CREATE DATABASE `d1`"}, 687 {"show create database if not exists d1", true, "SHOW CREATE DATABASE IF NOT EXISTS `d1`"}, 688 // for show stats_meta. 689 {"show stats_meta", true, "SHOW STATS_META"}, 690 {"show stats_meta where table_name = 't'", true, "SHOW STATS_META WHERE `table_name`='t'"}, 691 // for show stats_histograms 692 {"show stats_histograms", true, "SHOW STATS_HISTOGRAMS"}, 693 {"show stats_histograms where col_name = 'a'", true, "SHOW STATS_HISTOGRAMS WHERE `col_name`='a'"}, 694 // for show stats_buckets 695 {"show stats_buckets", true, "SHOW STATS_BUCKETS"}, 696 {"show stats_buckets where col_name = 'a'", true, "SHOW STATS_BUCKETS WHERE `col_name`='a'"}, 697 // for show stats_healthy. 698 {"show stats_healthy", true, "SHOW STATS_HEALTHY"}, 699 {"show stats_healthy where table_name = 't'", true, "SHOW STATS_HEALTHY WHERE `table_name`='t'"}, 700 701 // for load stats 702 {"load stats '/tmp/stats.json'", true, "LOAD STATS '/tmp/stats.json'"}, 703 // set 704 // user defined 705 {"SET @ = 1", true, "SET @``=1"}, 706 {"SET @' ' = 1", true, "SET @` `=1"}, 707 {"SET @! = 1", false, ""}, 708 {"SET @1 = 1", true, "SET @`1`=1"}, 709 {"SET @a = 1", true, "SET @`a`=1"}, 710 {"SET @b := 1", true, "SET @`b`=1"}, 711 {"SET @.c = 1", true, "SET @`.c`=1"}, 712 {"SET @_d = 1", true, "SET @`_d`=1"}, 713 {"SET @_e._$. = 1", true, "SET @`_e._$.`=1"}, 714 {"SET @~f = 1", false, ""}, 715 {"SET @`g,` = 1", true, "SET @`g,`=1"}, 716 // session system variables 717 {"SET SESSION autocommit = 1", true, "SET @@SESSION.`autocommit`=1"}, 718 {"SET @@session.autocommit = 1", true, "SET @@SESSION.`autocommit`=1"}, 719 {"SET @@SESSION.autocommit = 1", true, "SET @@SESSION.`autocommit`=1"}, 720 {"SET @@GLOBAL.GTID_PURGED = '123'", true, "SET @@GLOBAL.`gtid_purged`='123'"}, 721 {"SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN", true, "SET @`MYSQLDUMP_TEMP_LOG_BIN`=@@SESSION.`sql_log_bin`"}, 722 {"SET LOCAL autocommit = 1", true, "SET @@SESSION.`autocommit`=1"}, 723 {"SET @@local.autocommit = 1", true, "SET @@SESSION.`autocommit`=1"}, 724 {"SET @@autocommit = 1", true, "SET @@SESSION.`autocommit`=1"}, 725 {"SET autocommit = 1", true, "SET @@SESSION.`autocommit`=1"}, 726 // global system variables 727 {"SET GLOBAL autocommit = 1", true, "SET @@GLOBAL.`autocommit`=1"}, 728 {"SET @@global.autocommit = 1", true, "SET @@GLOBAL.`autocommit`=1"}, 729 // set default value 730 {"SET @@global.autocommit = default", true, "SET @@GLOBAL.`autocommit`=DEFAULT"}, 731 {"SET @@session.autocommit = default", true, "SET @@SESSION.`autocommit`=DEFAULT"}, 732 // SET CHARACTER SET 733 {"SET CHARACTER SET utf8mb4;", true, "SET NAMES 'utf8mb4'"}, 734 {"SET CHARACTER SET 'utf8mb4';", true, "SET NAMES 'utf8mb4'"}, 735 // set password 736 {"SET PASSWORD = 'password';", true, "SET PASSWORD='password'"}, 737 {"SET PASSWORD FOR 'root'@'localhost' = 'password';", true, "SET PASSWORD FOR `root`@`localhost`='password'"}, 738 // SET TRANSACTION Syntax 739 {"SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ", true, "SET @@SESSION.`tx_isolation`='REPEATABLE-READ'"}, 740 {"SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ", true, "SET @@GLOBAL.`tx_isolation`='REPEATABLE-READ'"}, 741 {"SET SESSION TRANSACTION READ WRITE", true, "SET @@SESSION.`tx_read_only`='0'"}, 742 {"SET SESSION TRANSACTION READ ONLY", true, "SET @@SESSION.`tx_read_only`='1'"}, 743 {"SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED", true, "SET @@SESSION.`tx_isolation`='READ-COMMITTED'"}, 744 {"SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED", true, "SET @@SESSION.`tx_isolation`='READ-UNCOMMITTED'"}, 745 {"SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE", true, "SET @@SESSION.`tx_isolation`='SERIALIZABLE'"}, 746 {"SET TRANSACTION ISOLATION LEVEL REPEATABLE READ", true, "SET @@SESSION.`tx_isolation_one_shot`='REPEATABLE-READ'"}, 747 {"SET TRANSACTION READ WRITE", true, "SET @@SESSION.`tx_read_only`='0'"}, 748 {"SET TRANSACTION READ ONLY", true, "SET @@SESSION.`tx_read_only`='1'"}, 749 {"SET TRANSACTION ISOLATION LEVEL READ COMMITTED", true, "SET @@SESSION.`tx_isolation_one_shot`='READ-COMMITTED'"}, 750 {"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED", true, "SET @@SESSION.`tx_isolation_one_shot`='READ-UNCOMMITTED'"}, 751 {"SET TRANSACTION ISOLATION LEVEL SERIALIZABLE", true, "SET @@SESSION.`tx_isolation_one_shot`='SERIALIZABLE'"}, 752 // for set names 753 {"set names utf8", true, "SET NAMES 'utf8'"}, 754 {"set names utf8 collate utf8_unicode_ci", true, "SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'"}, 755 {"set names binary", true, "SET NAMES 'binary'"}, 756 {"set role `role1`", true, ""}, 757 {"SET ROLE DEFAULT;", true, ""}, 758 {"SET ROLE ALL;", true, ""}, 759 {"SET ROLE ALL EXCEPT 'role1', 'role2';", true, ""}, 760 {"SET DEFAULT ROLE administrator, developer TO 'joe'@'10.0.0.1';", true, ""}, 761 // for set names and set vars 762 {"set names utf8, @@session.sql_mode=1;", true, "SET NAMES 'utf8', @@SESSION.`sql_mode`=1"}, 763 {"set @@session.sql_mode=1, names utf8, charset utf8;", true, "SET @@SESSION.`sql_mode`=1, NAMES 'utf8', NAMES 'utf8'"}, 764 765 // for FLUSH statement 766 {"flush no_write_to_binlog tables tbl1 with read lock", true, "FLUSH NO_WRITE_TO_BINLOG TABLES `tbl1` WITH READ LOCK"}, 767 {"flush table", true, "FLUSH TABLES"}, 768 {"flush tables", true, "FLUSH TABLES"}, 769 {"flush tables tbl1", true, "FLUSH TABLES `tbl1`"}, 770 {"flush no_write_to_binlog tables tbl1", true, "FLUSH NO_WRITE_TO_BINLOG TABLES `tbl1`"}, 771 {"flush local tables tbl1", true, "FLUSH NO_WRITE_TO_BINLOG TABLES `tbl1`"}, 772 {"flush table with read lock", true, "FLUSH TABLES WITH READ LOCK"}, 773 {"flush tables tbl1, tbl2, tbl3", true, "FLUSH TABLES `tbl1`, `tbl2`, `tbl3`"}, 774 {"flush tables tbl1, tbl2, tbl3 with read lock", true, "FLUSH TABLES `tbl1`, `tbl2`, `tbl3` WITH READ LOCK"}, 775 {"flush privileges", true, "FLUSH PRIVILEGES"}, 776 {"flush status", true, "FLUSH STATUS"}, 777 {"flush tidb plugins plugin1", true, "FLUSH TIDB PLUGINS plugin1"}, 778 {"flush tidb plugins plugin1, plugin2", true, "FLUSH TIDB PLUGINS plugin1, plugin2"}, 779 } 780 s.RunTest(c, table) 781 } 782 783 func (s *testParserSuite) TestFlushTable(c *C) { 784 parser := New() 785 stmt, _, err := parser.Parse("flush local tables tbl1,tbl2 with read lock", "", "") 786 c.Assert(err, IsNil) 787 flushTable := stmt[0].(*ast.FlushStmt) 788 c.Assert(flushTable.Tp, Equals, ast.FlushTables) 789 c.Assert(flushTable.Tables[0].Name.L, Equals, "tbl1") 790 c.Assert(flushTable.Tables[1].Name.L, Equals, "tbl2") 791 c.Assert(flushTable.NoWriteToBinLog, IsTrue) 792 c.Assert(flushTable.ReadLock, IsTrue) 793 } 794 795 func (s *testParserSuite) TestFlushPrivileges(c *C) { 796 parser := New() 797 stmt, _, err := parser.Parse("flush privileges", "", "") 798 c.Assert(err, IsNil) 799 flushPrivilege := stmt[0].(*ast.FlushStmt) 800 c.Assert(flushPrivilege.Tp, Equals, ast.FlushPrivileges) 801 } 802 803 func (s *testParserSuite) TestExpression(c *C) { 804 table := []testCase{ 805 // sign expression 806 {"SELECT ++1", true, "SELECT ++1"}, 807 {"SELECT -*1", false, "SELECT -*1"}, 808 {"SELECT -+1", true, "SELECT -+1"}, 809 {"SELECT -1", true, "SELECT -1"}, 810 {"SELECT --1", true, "SELECT --1"}, 811 812 // for string literal 813 {`select '''a''', """a"""`, true, "SELECT '''a''','\"a\"'"}, 814 {`select ''a''`, false, ""}, 815 {`select ""a""`, false, ""}, 816 {`select '''a''';`, true, "SELECT '''a'''"}, 817 {`select '\'a\'';`, true, "SELECT '''a'''"}, 818 {`select "\"a\"";`, true, "SELECT '\"a\"'"}, 819 {`select """a""";`, true, "SELECT '\"a\"'"}, 820 {`select _utf8"string";`, true, "SELECT _UTF8'string'"}, 821 {`select _binary"string";`, true, "SELECT _BINARY'string'"}, 822 {"select N'string'", true, "SELECT _UTF8'string'"}, 823 {"select n'string'", true, "SELECT _UTF8'string'"}, 824 // for comparison 825 {"select 1 <=> 0, 1 <=> null, 1 = null", true, "SELECT 1<=>0,1<=>NULL,1=NULL"}, 826 // for date literal 827 {"select date'1989-09-10'", true, "SELECT DATELITERAL('1989-09-10')"}, 828 {"select date 19890910", false, ""}, 829 // for time literal 830 {"select time '00:00:00.111'", true, "SELECT TIMELITERAL('00:00:00.111')"}, 831 {"select time 19890910", false, ""}, 832 // for timestamp literal 833 {"select timestamp '1989-09-10 11:11:11'", true, "SELECT TIMESTAMPLITERAL('1989-09-10 11:11:11')"}, 834 {"select timestamp 19890910", false, ""}, 835 836 // The ODBC syntax for time/date/timestamp literal. 837 // See: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-literals.html 838 {"select {ts '1989-09-10 11:11:11'}", true, "SELECT '1989-09-10 11:11:11'"}, 839 {"select {d '1989-09-10'}", true, "SELECT '1989-09-10'"}, 840 {"select {t '00:00:00.111'}", true, "SELECT '00:00:00.111'"}, 841 // If the identifier is not in (t, d, ts), we just ignore it and consider the following expression as the value. 842 // See: https://dev.mysql.com/doc/refman/5.7/en/expressions.html 843 {"select {ts123 '1989-09-10 11:11:11'}", true, "SELECT '1989-09-10 11:11:11'"}, 844 {"select {ts123 123}", true, "SELECT 123"}, 845 {"select {ts123 1 xor 1}", true, "SELECT 1 XOR 1"}, 846 } 847 s.RunTest(c, table) 848 } 849 850 func (s *testParserSuite) TestBuiltin(c *C) { 851 table := []testCase{ 852 // for builtin functions 853 {"SELECT POW(1, 2)", true, "SELECT POW(1, 2)"}, 854 {"SELECT POW(1, 2, 1)", true, "SELECT POW(1, 2, 1)"}, // illegal number of arguments shall pass too 855 {"SELECT POW(1, 0.5)", true, "SELECT POW(1, 0.5)"}, 856 {"SELECT POW(1, -1)", true, "SELECT POW(1, -1)"}, 857 {"SELECT POW(-1, 1)", true, "SELECT POW(-1, 1)"}, 858 {"SELECT RAND();", true, "SELECT RAND()"}, 859 {"SELECT RAND(1);", true, "SELECT RAND(1)"}, 860 {"SELECT MOD(10, 2);", true, "SELECT 10%2"}, 861 {"SELECT ROUND(-1.23);", true, "SELECT ROUND(-1.23)"}, 862 {"SELECT ROUND(1.23, 1);", true, "SELECT ROUND(1.23, 1)"}, 863 {"SELECT ROUND(1.23, 1, 1);", true, "SELECT ROUND(1.23, 1, 1)"}, 864 {"SELECT CEIL(-1.23);", true, "SELECT CEIL(-1.23)"}, 865 {"SELECT CEILING(1.23);", true, "SELECT CEILING(1.23)"}, 866 {"SELECT FLOOR(-1.23);", true, "SELECT FLOOR(-1.23)"}, 867 {"SELECT LN(1);", true, "SELECT LN(1)"}, 868 {"SELECT LN(1, 2);", true, "SELECT LN(1, 2)"}, 869 {"SELECT LOG(-2);", true, "SELECT LOG(-2)"}, 870 {"SELECT LOG(2, 65536);", true, "SELECT LOG(2, 65536)"}, 871 {"SELECT LOG(2, 65536, 1);", true, "SELECT LOG(2, 65536, 1)"}, 872 {"SELECT LOG2(2);", true, "SELECT LOG2(2)"}, 873 {"SELECT LOG2(2, 2);", true, "SELECT LOG2(2, 2)"}, 874 {"SELECT LOG10(10);", true, "SELECT LOG10(10)"}, 875 {"SELECT LOG10(10, 1);", true, "SELECT LOG10(10, 1)"}, 876 {"SELECT ABS(10, 1);", true, "SELECT ABS(10, 1)"}, 877 {"SELECT ABS(10);", true, "SELECT ABS(10)"}, 878 {"SELECT ABS();", true, "SELECT ABS()"}, 879 {"SELECT CONV(10+'10'+'10'+X'0a',10,10);", true, "SELECT CONV(10+'10'+'10'+x'0a', 10, 10)"}, 880 {"SELECT CONV();", true, "SELECT CONV()"}, 881 {"SELECT CRC32('MySQL');", true, "SELECT CRC32('MySQL')"}, 882 {"SELECT CRC32();", true, "SELECT CRC32()"}, 883 {"SELECT SIGN();", true, "SELECT SIGN()"}, 884 {"SELECT SIGN(0);", true, "SELECT SIGN(0)"}, 885 {"SELECT SQRT(0);", true, "SELECT SQRT(0)"}, 886 {"SELECT SQRT();", true, "SELECT SQRT()"}, 887 {"SELECT ACOS();", true, "SELECT ACOS()"}, 888 {"SELECT ACOS(1);", true, "SELECT ACOS(1)"}, 889 {"SELECT ACOS(1, 2);", true, "SELECT ACOS(1, 2)"}, 890 {"SELECT ASIN();", true, "SELECT ASIN()"}, 891 {"SELECT ASIN(1);", true, "SELECT ASIN(1)"}, 892 {"SELECT ASIN(1, 2);", true, "SELECT ASIN(1, 2)"}, 893 {"SELECT ATAN(0), ATAN(1), ATAN(1, 2);", true, "SELECT ATAN(0),ATAN(1),ATAN(1, 2)"}, 894 {"SELECT ATAN2(), ATAN2(1,2);", true, "SELECT ATAN2(),ATAN2(1, 2)"}, 895 {"SELECT COS(0);", true, "SELECT COS(0)"}, 896 {"SELECT COS(1);", true, "SELECT COS(1)"}, 897 {"SELECT COS(1, 2);", true, "SELECT COS(1, 2)"}, 898 {"SELECT COT();", true, "SELECT COT()"}, 899 {"SELECT COT(1);", true, "SELECT COT(1)"}, 900 {"SELECT COT(1, 2);", true, "SELECT COT(1, 2)"}, 901 {"SELECT DEGREES();", true, "SELECT DEGREES()"}, 902 {"SELECT DEGREES(0);", true, "SELECT DEGREES(0)"}, 903 {"SELECT EXP();", true, "SELECT EXP()"}, 904 {"SELECT EXP(1);", true, "SELECT EXP(1)"}, 905 {"SELECT PI();", true, "SELECT PI()"}, 906 {"SELECT PI(1);", true, "SELECT PI(1)"}, 907 {"SELECT RADIANS();", true, "SELECT RADIANS()"}, 908 {"SELECT RADIANS(1);", true, "SELECT RADIANS(1)"}, 909 {"SELECT SIN();", true, "SELECT SIN()"}, 910 {"SELECT SIN(1);", true, "SELECT SIN(1)"}, 911 {"SELECT TAN(1);", true, "SELECT TAN(1)"}, 912 {"SELECT TAN();", true, "SELECT TAN()"}, 913 {"SELECT TRUNCATE(1.223,1);", true, "SELECT TRUNCATE(1.223, 1)"}, 914 {"SELECT TRUNCATE();", true, "SELECT TRUNCATE()"}, 915 916 {"SELECT SUBSTR('Quadratically',5);", true, "SELECT SUBSTR('Quadratically', 5)"}, 917 {"SELECT SUBSTR('Quadratically',5, 3);", true, "SELECT SUBSTR('Quadratically', 5, 3)"}, 918 {"SELECT SUBSTR('Quadratically' FROM 5);", true, "SELECT SUBSTR('Quadratically', 5)"}, 919 {"SELECT SUBSTR('Quadratically' FROM 5 FOR 3);", true, "SELECT SUBSTR('Quadratically', 5, 3)"}, 920 921 {"SELECT SUBSTRING('Quadratically',5);", true, "SELECT SUBSTRING('Quadratically', 5)"}, 922 {"SELECT SUBSTRING('Quadratically',5, 3);", true, "SELECT SUBSTRING('Quadratically', 5, 3)"}, 923 {"SELECT SUBSTRING('Quadratically' FROM 5);", true, "SELECT SUBSTRING('Quadratically', 5)"}, 924 {"SELECT SUBSTRING('Quadratically' FROM 5 FOR 3);", true, "SELECT SUBSTRING('Quadratically', 5, 3)"}, 925 926 {"SELECT CONVERT('111', SIGNED);", true, "SELECT CONVERT('111', SIGNED)"}, 927 928 {"SELECT LEAST(), LEAST(1, 2, 3);", true, "SELECT LEAST(),LEAST(1, 2, 3)"}, 929 930 {"SELECT INTERVAL(1, 0, 1, 2)", true, "SELECT INTERVAL(1, 0, 1, 2)"}, 931 {"SELECT DATE_ADD('2008-01-02', INTERVAL INTERVAL(1, 0, 1) DAY);", true, "SELECT DATE_ADD('2008-01-02', INTERVAL INTERVAL(1, 0, 1) DAY)"}, 932 933 // information functions 934 {"SELECT DATABASE();", true, "SELECT DATABASE()"}, 935 {"SELECT SCHEMA();", true, "SELECT SCHEMA()"}, 936 {"SELECT USER();", true, "SELECT USER()"}, 937 {"SELECT USER(1);", true, "SELECT USER(1)"}, 938 {"SELECT CURRENT_USER();", true, "SELECT CURRENT_USER()"}, 939 {"SELECT CURRENT_USER;", true, "SELECT CURRENT_USER()"}, 940 {"SELECT CONNECTION_ID();", true, "SELECT CONNECTION_ID()"}, 941 {"SELECT VERSION();", true, "SELECT VERSION()"}, 942 {"SELECT BENCHMARK(1000000, AES_ENCRYPT('text',UNHEX('F3229A0B371ED2D9441B830D21A390C3')));", true, "SELECT BENCHMARK(1000000, AES_ENCRYPT('text', UNHEX('F3229A0B371ED2D9441B830D21A390C3')))"}, 943 {"SELECT BENCHMARK(AES_ENCRYPT('text',UNHEX('F3229A0B371ED2D9441B830D21A390C3')));", true, "SELECT BENCHMARK(AES_ENCRYPT('text', UNHEX('F3229A0B371ED2D9441B830D21A390C3')))"}, 944 {"SELECT CHARSET('abc');", true, "SELECT CHARSET('abc')"}, 945 {"SELECT COERCIBILITY('abc');", true, "SELECT COERCIBILITY('abc')"}, 946 {"SELECT COERCIBILITY('abc', 'a');", true, "SELECT COERCIBILITY('abc', 'a')"}, 947 {"SELECT COLLATION('abc');", true, "SELECT COLLATION('abc')"}, 948 {"SELECT ROW_COUNT();", true, "SELECT ROW_COUNT()"}, 949 {"SELECT SESSION_USER();", true, "SELECT SESSION_USER()"}, 950 {"SELECT SYSTEM_USER();", true, "SELECT SYSTEM_USER()"}, 951 952 {"SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);", true, "SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2)"}, 953 {"SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);", true, "SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2)"}, 954 955 {`SELECT ASCII(), ASCII(""), ASCII("A"), ASCII(1);`, true, "SELECT ASCII(),ASCII(''),ASCII('A'),ASCII(1)"}, 956 957 {`SELECT LOWER("A"), UPPER("a")`, true, "SELECT LOWER('A'),UPPER('a')"}, 958 {`SELECT LCASE("A"), UCASE("a")`, true, "SELECT LCASE('A'),UCASE('a')"}, 959 960 {`SELECT REPLACE('www.mysql.com', 'w', 'Ww')`, true, "SELECT REPLACE('www.mysql.com', 'w', 'Ww')"}, 961 962 {`SELECT LOCATE('bar', 'foobarbar');`, true, "SELECT LOCATE('bar', 'foobarbar')"}, 963 {`SELECT LOCATE('bar', 'foobarbar', 5);`, true, "SELECT LOCATE('bar', 'foobarbar', 5)"}, 964 965 {`SELECT tidb_version();`, true, "SELECT TIDB_VERSION()"}, 966 {`SELECT tidb_is_ddl_owner();`, true, "SELECT TIDB_IS_DDL_OWNER()"}, 967 968 // for time fsp 969 {"CREATE TABLE t( c1 TIME(2), c2 DATETIME(2), c3 TIMESTAMP(2) );", true, "CREATE TABLE `t` (`c1` TIME(2),`c2` DATETIME(2),`c3` TIMESTAMP(2))"}, 970 971 // for row 972 {"select row(1)", false, ""}, 973 {"select row(1, 1,)", false, ""}, 974 {"select (1, 1,)", false, ""}, 975 {"select row(1, 1) > row(1, 1), row(1, 1, 1) > row(1, 1, 1)", true, "SELECT ROW(1,1)>ROW(1,1),ROW(1,1,1)>ROW(1,1,1)"}, 976 {"Select (1, 1) > (1, 1)", true, "SELECT ROW(1,1)>ROW(1,1)"}, 977 {"create table t (`row` int)", true, "CREATE TABLE `t` (`row` INT)"}, 978 {"create table t (row int)", false, ""}, 979 980 // for cast with charset 981 {"SELECT *, CAST(data AS CHAR CHARACTER SET utf8) FROM t;", true, "SELECT *,CAST(`data` AS CHAR CHARACTER SET utf8) FROM `t`"}, 982 983 // for cast as JSON 984 {"SELECT *, CAST(data AS JSON) FROM t;", true, "SELECT *,CAST(`data` AS JSON) FROM `t`"}, 985 986 // for cast as signed int, fix issue #3691. 987 {"select cast(1 as signed int);", true, "SELECT CAST(1 AS SIGNED)"}, 988 989 // for last_insert_id 990 {"SELECT last_insert_id();", true, "SELECT LAST_INSERT_ID()"}, 991 {"SELECT last_insert_id(1);", true, "SELECT LAST_INSERT_ID(1)"}, 992 993 // for binary operator 994 {"SELECT binary 'a';", true, "SELECT BINARY 'a'"}, 995 996 // for bit_count 997 {`SELECT BIT_COUNT(1);`, true, "SELECT BIT_COUNT(1)"}, 998 999 // select time 1000 {"select current_timestamp", true, "SELECT CURRENT_TIMESTAMP()"}, 1001 {"select current_timestamp()", true, "SELECT CURRENT_TIMESTAMP()"}, 1002 {"select current_timestamp(6)", true, "SELECT CURRENT_TIMESTAMP(6)"}, 1003 {"select current_timestamp(null)", false, ""}, 1004 {"select current_timestamp(-1)", false, ""}, 1005 {"select current_timestamp(1.0)", false, ""}, 1006 {"select current_timestamp('2')", false, ""}, 1007 {"select now()", true, "SELECT NOW()"}, 1008 {"select now(6)", true, "SELECT NOW(6)"}, 1009 {"select sysdate(), sysdate(6)", true, "SELECT SYSDATE(),SYSDATE(6)"}, 1010 {"SELECT time('01:02:03');", true, "SELECT TIME('01:02:03')"}, 1011 {"SELECT time('01:02:03.1')", true, "SELECT TIME('01:02:03.1')"}, 1012 {"SELECT time('20.1')", true, "SELECT TIME('20.1')"}, 1013 {"SELECT TIMEDIFF('2000:01:01 00:00:00', '2000:01:01 00:00:00.000001');", true, "SELECT TIMEDIFF('2000:01:01 00:00:00', '2000:01:01 00:00:00.000001')"}, 1014 {"SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');", true, "SELECT TIMESTAMPDIFF(MONTH, '2003-02-01', '2003-05-01')"}, 1015 {"SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');", true, "SELECT TIMESTAMPDIFF(YEAR, '2002-05-01', '2001-01-01')"}, 1016 {"SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');", true, "SELECT TIMESTAMPDIFF(MINUTE, '2003-02-01', '2003-05-01 12:05:55')"}, 1017 1018 // select current_time 1019 {"select current_time", true, "SELECT CURRENT_TIME()"}, 1020 {"select current_time()", true, "SELECT CURRENT_TIME()"}, 1021 {"select current_time(6)", true, "SELECT CURRENT_TIME(6)"}, 1022 {"select current_time(-1)", false, ""}, 1023 {"select current_time(1.0)", false, ""}, 1024 {"select current_time('1')", false, ""}, 1025 {"select current_time(null)", false, ""}, 1026 {"select curtime()", true, "SELECT CURTIME()"}, 1027 {"select curtime(6)", true, "SELECT CURTIME(6)"}, 1028 {"select curtime(-1)", false, ""}, 1029 {"select curtime(1.0)", false, ""}, 1030 {"select curtime('1')", false, ""}, 1031 {"select curtime(null)", false, ""}, 1032 1033 // select utc_timestamp 1034 {"select utc_timestamp", true, "SELECT UTC_TIMESTAMP()"}, 1035 {"select utc_timestamp()", true, "SELECT UTC_TIMESTAMP()"}, 1036 {"select utc_timestamp(6)", true, "SELECT UTC_TIMESTAMP(6)"}, 1037 {"select utc_timestamp(-1)", false, ""}, 1038 {"select utc_timestamp(1.0)", false, ""}, 1039 {"select utc_timestamp('1')", false, ""}, 1040 {"select utc_timestamp(null)", false, ""}, 1041 1042 // select utc_time 1043 {"select utc_time", true, "SELECT UTC_TIME()"}, 1044 {"select utc_time()", true, "SELECT UTC_TIME()"}, 1045 {"select utc_time(6)", true, "SELECT UTC_TIME(6)"}, 1046 {"select utc_time(-1)", false, ""}, 1047 {"select utc_time(1.0)", false, ""}, 1048 {"select utc_time('1')", false, ""}, 1049 {"select utc_time(null)", false, ""}, 1050 1051 // for microsecond, second, minute, hour 1052 {"SELECT MICROSECOND('2009-12-31 23:59:59.000010');", true, "SELECT MICROSECOND('2009-12-31 23:59:59.000010')"}, 1053 {"SELECT SECOND('10:05:03');", true, "SELECT SECOND('10:05:03')"}, 1054 {"SELECT MINUTE('2008-02-03 10:05:03');", true, "SELECT MINUTE('2008-02-03 10:05:03')"}, 1055 {"SELECT HOUR(), HOUR('10:05:03');", true, "SELECT HOUR(),HOUR('10:05:03')"}, 1056 1057 // for date, day, weekday 1058 {"SELECT CURRENT_DATE, CURRENT_DATE(), CURDATE()", true, "SELECT CURRENT_DATE(),CURRENT_DATE(),CURDATE()"}, 1059 {"SELECT CURRENT_DATE, CURRENT_DATE(), CURDATE(1)", false, ""}, 1060 {"SELECT DATEDIFF('2003-12-31', '2003-12-30');", true, "SELECT DATEDIFF('2003-12-31', '2003-12-30')"}, 1061 {"SELECT DATE('2003-12-31 01:02:03');", true, "SELECT DATE('2003-12-31 01:02:03')"}, 1062 {"SELECT DATE();", true, "SELECT DATE()"}, 1063 {"SELECT DATE('2003-12-31 01:02:03', '');", true, "SELECT DATE('2003-12-31 01:02:03', '')"}, 1064 {`SELECT DATE_FORMAT('2003-12-31 01:02:03', '%W %M %Y');`, true, "SELECT DATE_FORMAT('2003-12-31 01:02:03', '%W %M %Y')"}, 1065 {"SELECT DAY('2007-02-03');", true, "SELECT DAY('2007-02-03')"}, 1066 {"SELECT DAYOFMONTH('2007-02-03');", true, "SELECT DAYOFMONTH('2007-02-03')"}, 1067 {"SELECT DAYOFWEEK('2007-02-03');", true, "SELECT DAYOFWEEK('2007-02-03')"}, 1068 {"SELECT DAYOFYEAR('2007-02-03');", true, "SELECT DAYOFYEAR('2007-02-03')"}, 1069 {"SELECT DAYNAME('2007-02-03');", true, "SELECT DAYNAME('2007-02-03')"}, 1070 {"SELECT FROM_DAYS(1423);", true, "SELECT FROM_DAYS(1423)"}, 1071 {"SELECT WEEKDAY('2007-02-03');", true, "SELECT WEEKDAY('2007-02-03')"}, 1072 1073 // for utc_date 1074 {"SELECT UTC_DATE, UTC_DATE();", true, "SELECT UTC_DATE(),UTC_DATE()"}, 1075 {"SELECT UTC_DATE(), UTC_DATE()+0", true, "SELECT UTC_DATE(),UTC_DATE()+0"}, 1076 1077 // for week, month, year 1078 {"SELECT WEEK();", true, "SELECT WEEK()"}, 1079 {"SELECT WEEK('2007-02-03');", true, "SELECT WEEK('2007-02-03')"}, 1080 {"SELECT WEEK('2007-02-03', 0);", true, "SELECT WEEK('2007-02-03', 0)"}, 1081 {"SELECT WEEKOFYEAR('2007-02-03');", true, "SELECT WEEKOFYEAR('2007-02-03')"}, 1082 {"SELECT MONTH('2007-02-03');", true, "SELECT MONTH('2007-02-03')"}, 1083 {"SELECT MONTHNAME('2007-02-03');", true, "SELECT MONTHNAME('2007-02-03')"}, 1084 {"SELECT YEAR('2007-02-03');", true, "SELECT YEAR('2007-02-03')"}, 1085 {"SELECT YEARWEEK('2007-02-03');", true, "SELECT YEARWEEK('2007-02-03')"}, 1086 {"SELECT YEARWEEK('2007-02-03', 0);", true, "SELECT YEARWEEK('2007-02-03', 0)"}, 1087 1088 // for ADDTIME, SUBTIME 1089 {"SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');", true, "SELECT ADDTIME('01:00:00.999999', '02:00:00.999998')"}, 1090 {"SELECT ADDTIME('02:00:00.999998');", true, "SELECT ADDTIME('02:00:00.999998')"}, 1091 {"SELECT ADDTIME();", true, "SELECT ADDTIME()"}, 1092 {"SELECT SUBTIME('01:00:00.999999', '02:00:00.999998');", true, "SELECT SUBTIME('01:00:00.999999', '02:00:00.999998')"}, 1093 1094 // for CONVERT_TZ 1095 {"SELECT CONVERT_TZ();", true, "SELECT CONVERT_TZ()"}, 1096 {"SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');", true, "SELECT CONVERT_TZ('2004-01-01 12:00:00', '+00:00', '+10:00')"}, 1097 {"SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00', '+10:00');", true, "SELECT CONVERT_TZ('2004-01-01 12:00:00', '+00:00', '+10:00', '+10:00')"}, 1098 1099 // for GET_FORMAT 1100 {"SELECT GET_FORMAT(DATE, 'USA');", true, "SELECT GET_FORMAT(DATE, 'USA')"}, 1101 {"SELECT GET_FORMAT(DATETIME, 'USA');", true, "SELECT GET_FORMAT(DATETIME, 'USA')"}, 1102 {"SELECT GET_FORMAT(TIME, 'USA');", true, "SELECT GET_FORMAT(TIME, 'USA')"}, 1103 {"SELECT GET_FORMAT(TIMESTAMP, 'USA');", true, "SELECT GET_FORMAT(TIMESTAMP, 'USA')"}, 1104 1105 // for LOCALTIME, LOCALTIMESTAMP 1106 {"SELECT LOCALTIME(), LOCALTIME(1)", true, "SELECT LOCALTIME(),LOCALTIME(1)"}, 1107 {"SELECT LOCALTIMESTAMP(), LOCALTIMESTAMP(2)", true, "SELECT LOCALTIMESTAMP(),LOCALTIMESTAMP(2)"}, 1108 1109 // for MAKEDATE, MAKETIME 1110 {"SELECT MAKEDATE(2011,31);", true, "SELECT MAKEDATE(2011, 31)"}, 1111 {"SELECT MAKETIME(12,15,30);", true, "SELECT MAKETIME(12, 15, 30)"}, 1112 {"SELECT MAKEDATE();", true, "SELECT MAKEDATE()"}, 1113 {"SELECT MAKETIME();", true, "SELECT MAKETIME()"}, 1114 1115 // for PERIOD_ADD, PERIOD_DIFF 1116 {"SELECT PERIOD_ADD(200801,2)", true, "SELECT PERIOD_ADD(200801, 2)"}, 1117 {"SELECT PERIOD_DIFF(200802,200703)", true, "SELECT PERIOD_DIFF(200802, 200703)"}, 1118 1119 // for QUARTER 1120 {"SELECT QUARTER('2008-04-01');", true, "SELECT QUARTER('2008-04-01')"}, 1121 1122 // for SEC_TO_TIME 1123 {"SELECT SEC_TO_TIME(2378)", true, "SELECT SEC_TO_TIME(2378)"}, 1124 1125 // for TIME_FORMAT 1126 {`SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l')`, true, "SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l')"}, 1127 1128 // for TIME_TO_SEC 1129 {"SELECT TIME_TO_SEC('22:23:00')", true, "SELECT TIME_TO_SEC('22:23:00')"}, 1130 1131 // for TIMESTAMPADD 1132 {"SELECT TIMESTAMPADD(WEEK,1,'2003-01-02');", true, "SELECT TIMESTAMPADD(WEEK, 1, '2003-01-02')"}, 1133 1134 // for TO_DAYS, TO_SECONDS 1135 {"SELECT TO_DAYS('2007-10-07')", true, "SELECT TO_DAYS('2007-10-07')"}, 1136 {"SELECT TO_SECONDS('2009-11-29')", true, "SELECT TO_SECONDS('2009-11-29')"}, 1137 1138 // for LAST_DAY 1139 {"SELECT LAST_DAY('2003-02-05');", true, "SELECT LAST_DAY('2003-02-05')"}, 1140 1141 // for UTC_TIME 1142 {"SELECT UTC_TIME(), UTC_TIME(1)", true, "SELECT UTC_TIME(),UTC_TIME(1)"}, 1143 1144 // for time extract 1145 {`select extract(microsecond from "2011-11-11 10:10:10.123456")`, true, "SELECT EXTRACT(MICROSECOND FROM '2011-11-11 10:10:10.123456')"}, 1146 {`select extract(second from "2011-11-11 10:10:10.123456")`, true, "SELECT EXTRACT(SECOND FROM '2011-11-11 10:10:10.123456')"}, 1147 {`select extract(minute from "2011-11-11 10:10:10.123456")`, true, "SELECT EXTRACT(MINUTE FROM '2011-11-11 10:10:10.123456')"}, 1148 {`select extract(hour from "2011-11-11 10:10:10.123456")`, true, "SELECT EXTRACT(HOUR FROM '2011-11-11 10:10:10.123456')"}, 1149 {`select extract(day from "2011-11-11 10:10:10.123456")`, true, "SELECT EXTRACT(DAY FROM '2011-11-11 10:10:10.123456')"}, 1150 {`select extract(week from "2011-11-11 10:10:10.123456")`, true, "SELECT EXTRACT(WEEK FROM '2011-11-11 10:10:10.123456')"}, 1151 {`select extract(month from "2011-11-11 10:10:10.123456")`, true, "SELECT EXTRACT(MONTH FROM '2011-11-11 10:10:10.123456')"}, 1152 {`select extract(quarter from "2011-11-11 10:10:10.123456")`, true, "SELECT EXTRACT(QUARTER FROM '2011-11-11 10:10:10.123456')"}, 1153 {`select extract(year from "2011-11-11 10:10:10.123456")`, true, "SELECT EXTRACT(YEAR FROM '2011-11-11 10:10:10.123456')"}, 1154 {`select extract(second_microsecond from "2011-11-11 10:10:10.123456")`, true, "SELECT EXTRACT(SECOND_MICROSECOND FROM '2011-11-11 10:10:10.123456')"}, 1155 {`select extract(minute_microsecond from "2011-11-11 10:10:10.123456")`, true, "SELECT EXTRACT(MINUTE_MICROSECOND FROM '2011-11-11 10:10:10.123456')"}, 1156 {`select extract(minute_second from "2011-11-11 10:10:10.123456")`, true, "SELECT EXTRACT(MINUTE_SECOND FROM '2011-11-11 10:10:10.123456')"}, 1157 {`select extract(hour_microsecond from "2011-11-11 10:10:10.123456")`, true, "SELECT EXTRACT(HOUR_MICROSECOND FROM '2011-11-11 10:10:10.123456')"}, 1158 {`select extract(hour_second from "2011-11-11 10:10:10.123456")`, true, "SELECT EXTRACT(HOUR_SECOND FROM '2011-11-11 10:10:10.123456')"}, 1159 {`select extract(hour_minute from "2011-11-11 10:10:10.123456")`, true, "SELECT EXTRACT(HOUR_MINUTE FROM '2011-11-11 10:10:10.123456')"}, 1160 {`select extract(day_microsecond from "2011-11-11 10:10:10.123456")`, true, "SELECT EXTRACT(DAY_MICROSECOND FROM '2011-11-11 10:10:10.123456')"}, 1161 {`select extract(day_second from "2011-11-11 10:10:10.123456")`, true, "SELECT EXTRACT(DAY_SECOND FROM '2011-11-11 10:10:10.123456')"}, 1162 {`select extract(day_minute from "2011-11-11 10:10:10.123456")`, true, "SELECT EXTRACT(DAY_MINUTE FROM '2011-11-11 10:10:10.123456')"}, 1163 {`select extract(day_hour from "2011-11-11 10:10:10.123456")`, true, "SELECT EXTRACT(DAY_HOUR FROM '2011-11-11 10:10:10.123456')"}, 1164 {`select extract(year_month from "2011-11-11 10:10:10.123456")`, true, "SELECT EXTRACT(YEAR_MONTH FROM '2011-11-11 10:10:10.123456')"}, 1165 1166 // for from_unixtime 1167 {`select from_unixtime(1447430881)`, true, "SELECT FROM_UNIXTIME(1447430881)"}, 1168 {`select from_unixtime(1447430881.123456)`, true, "SELECT FROM_UNIXTIME(1447430881.123456)"}, 1169 {`select from_unixtime(1447430881.1234567)`, true, "SELECT FROM_UNIXTIME(1447430881.1234567)"}, 1170 {`select from_unixtime(1447430881.9999999)`, true, "SELECT FROM_UNIXTIME(1447430881.9999999)"}, 1171 {`select from_unixtime(1447430881, "%Y %D %M %h:%i:%s %x")`, true, "SELECT FROM_UNIXTIME(1447430881, '%Y %D %M %h:%i:%s %x')"}, 1172 {`select from_unixtime(1447430881.123456, "%Y %D %M %h:%i:%s %x")`, true, "SELECT FROM_UNIXTIME(1447430881.123456, '%Y %D %M %h:%i:%s %x')"}, 1173 {`select from_unixtime(1447430881.1234567, "%Y %D %M %h:%i:%s %x")`, true, "SELECT FROM_UNIXTIME(1447430881.1234567, '%Y %D %M %h:%i:%s %x')"}, 1174 1175 // for issue 224 1176 {`SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin;`, true, "SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8)"}, 1177 1178 // for string functions 1179 // trim 1180 {`SELECT TRIM(' bar ');`, true, "SELECT TRIM(' bar ')"}, 1181 {`SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');`, true, "SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx')"}, 1182 {`SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');`, true, "SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx')"}, 1183 {`SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');`, true, "SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz')"}, 1184 {`SELECT LTRIM(' foo ');`, true, "SELECT LTRIM(' foo ')"}, 1185 {`SELECT RTRIM(' bar ');`, true, "SELECT RTRIM(' bar ')"}, 1186 1187 {`SELECT RPAD('hi', 6, 'c');`, true, "SELECT RPAD('hi', 6, 'c')"}, 1188 {`SELECT BIT_LENGTH('hi');`, true, "SELECT BIT_LENGTH('hi')"}, 1189 {`SELECT CHAR(65);`, true, "SELECT CHAR_FUNC(65, NULL)"}, 1190 {`SELECT CHAR_LENGTH('abc');`, true, "SELECT CHAR_LENGTH('abc')"}, 1191 {`SELECT CHARACTER_LENGTH('abc');`, true, "SELECT CHARACTER_LENGTH('abc')"}, 1192 {`SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');`, true, "SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo')"}, 1193 {`SELECT FIND_IN_SET('foo', 'foo,bar')`, true, "SELECT FIND_IN_SET('foo', 'foo,bar')"}, 1194 {`SELECT FIND_IN_SET('foo')`, true, "SELECT FIND_IN_SET('foo')"}, // illegal number of argument still pass 1195 {`SELECT MAKE_SET(1,'a'), MAKE_SET(1,'a','b','c')`, true, "SELECT MAKE_SET(1, 'a'),MAKE_SET(1, 'a', 'b', 'c')"}, 1196 {`SELECT MID('Sakila', -5, 3)`, true, "SELECT MID('Sakila', -5, 3)"}, 1197 {`SELECT OCT(12)`, true, "SELECT OCT(12)"}, 1198 {`SELECT OCTET_LENGTH('text')`, true, "SELECT OCTET_LENGTH('text')"}, 1199 {`SELECT ORD('2')`, true, "SELECT ORD('2')"}, 1200 {`SELECT POSITION('bar' IN 'foobarbar')`, true, "SELECT POSITION('bar' IN 'foobarbar')"}, 1201 {`SELECT QUOTE('Don\'t!')`, true, "SELECT QUOTE('Don''t!')"}, 1202 {`SELECT BIN(12)`, true, "SELECT BIN(12)"}, 1203 {`SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo')`, true, "SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo')"}, 1204 {`SELECT EXPORT_SET(5,'Y','N'), EXPORT_SET(5,'Y','N',','), EXPORT_SET(5,'Y','N',',',4)`, true, "SELECT EXPORT_SET(5, 'Y', 'N'),EXPORT_SET(5, 'Y', 'N', ','),EXPORT_SET(5, 'Y', 'N', ',', 4)"}, 1205 {`SELECT FORMAT(), FORMAT(12332.2,2,'de_DE'), FORMAT(12332.123456, 4)`, true, "SELECT FORMAT(),FORMAT(12332.2, 2, 'de_DE'),FORMAT(12332.123456, 4)"}, 1206 {`SELECT FROM_BASE64('abc')`, true, "SELECT FROM_BASE64('abc')"}, 1207 {`SELECT TO_BASE64('abc')`, true, "SELECT TO_BASE64('abc')"}, 1208 {`SELECT INSERT(), INSERT('Quadratic', 3, 4, 'What'), INSTR('foobarbar', 'bar')`, true, "SELECT INSERT_FUNC(),INSERT_FUNC('Quadratic', 3, 4, 'What'),INSTR('foobarbar', 'bar')"}, 1209 {`SELECT LOAD_FILE('/tmp/picture')`, true, "SELECT LOAD_FILE('/tmp/picture')"}, 1210 {`SELECT LPAD('hi',4,'??')`, true, "SELECT LPAD('hi', 4, '??')"}, 1211 {`SELECT LEFT("foobar", 3)`, true, "SELECT LEFT('foobar', 3)"}, 1212 {`SELECT RIGHT("foobar", 3)`, true, "SELECT RIGHT('foobar', 3)"}, 1213 1214 // repeat 1215 {`SELECT REPEAT("a", 10);`, true, "SELECT REPEAT('a', 10)"}, 1216 1217 // for miscellaneous functions 1218 {`SELECT SLEEP(10);`, true, "SELECT SLEEP(10)"}, 1219 {`SELECT ANY_VALUE(@arg);`, true, "SELECT ANY_VALUE(@`arg`)"}, 1220 {`SELECT INET_ATON('10.0.5.9');`, true, "SELECT INET_ATON('10.0.5.9')"}, 1221 {`SELECT INET_NTOA(167773449);`, true, "SELECT INET_NTOA(167773449)"}, 1222 {`SELECT INET6_ATON('fdfe::5a55:caff:fefa:9089');`, true, "SELECT INET6_ATON('fdfe::5a55:caff:fefa:9089')"}, 1223 {`SELECT INET6_NTOA(INET_NTOA(167773449));`, true, "SELECT INET6_NTOA(INET_NTOA(167773449))"}, 1224 {`SELECT IS_FREE_LOCK(@str);`, true, "SELECT IS_FREE_LOCK(@`str`)"}, 1225 {`SELECT IS_IPV4('10.0.5.9');`, true, "SELECT IS_IPV4('10.0.5.9')"}, 1226 {`SELECT IS_IPV4_COMPAT(INET6_ATON('::10.0.5.9'));`, true, "SELECT IS_IPV4_COMPAT(INET6_ATON('::10.0.5.9'))"}, 1227 {`SELECT IS_IPV4_MAPPED(INET6_ATON('::10.0.5.9'));`, true, "SELECT IS_IPV4_MAPPED(INET6_ATON('::10.0.5.9'))"}, 1228 {`SELECT IS_IPV6('10.0.5.9');`, true, "SELECT IS_IPV6('10.0.5.9')"}, 1229 {`SELECT IS_USED_LOCK(@str);`, true, "SELECT IS_USED_LOCK(@`str`)"}, 1230 {`SELECT MASTER_POS_WAIT(@log_name, @log_pos), MASTER_POS_WAIT(@log_name, @log_pos, @timeout), MASTER_POS_WAIT(@log_name, @log_pos, @timeout, @channel_name);`, true, "SELECT MASTER_POS_WAIT(@`log_name`, @`log_pos`),MASTER_POS_WAIT(@`log_name`, @`log_pos`, @`timeout`),MASTER_POS_WAIT(@`log_name`, @`log_pos`, @`timeout`, @`channel_name`)"}, 1231 {`SELECT NAME_CONST('myname', 14);`, true, "SELECT NAME_CONST('myname', 14)"}, 1232 {`SELECT RELEASE_ALL_LOCKS();`, true, "SELECT RELEASE_ALL_LOCKS()"}, 1233 {`SELECT UUID();`, true, "SELECT UUID()"}, 1234 {`SELECT UUID_SHORT()`, true, "SELECT UUID_SHORT()"}, 1235 // test illegal arguments 1236 {`SELECT SLEEP();`, true, "SELECT SLEEP()"}, 1237 {`SELECT ANY_VALUE();`, true, "SELECT ANY_VALUE()"}, 1238 {`SELECT INET_ATON();`, true, "SELECT INET_ATON()"}, 1239 {`SELECT INET_NTOA();`, true, "SELECT INET_NTOA()"}, 1240 {`SELECT INET6_ATON();`, true, "SELECT INET6_ATON()"}, 1241 {`SELECT INET6_NTOA(INET_NTOA());`, true, "SELECT INET6_NTOA(INET_NTOA())"}, 1242 {`SELECT IS_FREE_LOCK();`, true, "SELECT IS_FREE_LOCK()"}, 1243 {`SELECT IS_IPV4();`, true, "SELECT IS_IPV4()"}, 1244 {`SELECT IS_IPV4_COMPAT(INET6_ATON());`, true, "SELECT IS_IPV4_COMPAT(INET6_ATON())"}, 1245 {`SELECT IS_IPV4_MAPPED(INET6_ATON());`, true, "SELECT IS_IPV4_MAPPED(INET6_ATON())"}, 1246 {`SELECT IS_IPV6()`, true, "SELECT IS_IPV6()"}, 1247 {`SELECT IS_USED_LOCK();`, true, "SELECT IS_USED_LOCK()"}, 1248 {`SELECT MASTER_POS_WAIT();`, true, "SELECT MASTER_POS_WAIT()"}, 1249 {`SELECT NAME_CONST();`, true, "SELECT NAME_CONST()"}, 1250 {`SELECT RELEASE_ALL_LOCKS(1);`, true, "SELECT RELEASE_ALL_LOCKS(1)"}, 1251 {`SELECT UUID(1);`, true, "SELECT UUID(1)"}, 1252 {`SELECT UUID_SHORT(1)`, true, "SELECT UUID_SHORT(1)"}, 1253 // interval 1254 {`select "2011-11-11 10:10:10.123456" + interval 10 second`, true, "SELECT DATE_ADD('2011-11-11 10:10:10.123456', INTERVAL 10 SECOND)"}, 1255 {`select "2011-11-11 10:10:10.123456" - interval 10 second`, true, "SELECT DATE_SUB('2011-11-11 10:10:10.123456', INTERVAL 10 SECOND)"}, 1256 // for date_add 1257 {`select date_add("2011-11-11 10:10:10.123456", interval 10 microsecond)`, true, "SELECT DATE_ADD('2011-11-11 10:10:10.123456', INTERVAL 10 MICROSECOND)"}, 1258 {`select date_add("2011-11-11 10:10:10.123456", interval 10 second)`, true, "SELECT DATE_ADD('2011-11-11 10:10:10.123456', INTERVAL 10 SECOND)"}, 1259 {`select date_add("2011-11-11 10:10:10.123456", interval 10 minute)`, true, "SELECT DATE_ADD('2011-11-11 10:10:10.123456', INTERVAL 10 MINUTE)"}, 1260 {`select date_add("2011-11-11 10:10:10.123456", interval 10 hour)`, true, "SELECT DATE_ADD('2011-11-11 10:10:10.123456', INTERVAL 10 HOUR)"}, 1261 {`select date_add("2011-11-11 10:10:10.123456", interval 10 day)`, true, "SELECT DATE_ADD('2011-11-11 10:10:10.123456', INTERVAL 10 DAY)"}, 1262 {`select date_add("2011-11-11 10:10:10.123456", interval 1 week)`, true, "SELECT DATE_ADD('2011-11-11 10:10:10.123456', INTERVAL 1 WEEK)"}, 1263 {`select date_add("2011-11-11 10:10:10.123456", interval 1 month)`, true, "SELECT DATE_ADD('2011-11-11 10:10:10.123456', INTERVAL 1 MONTH)"}, 1264 {`select date_add("2011-11-11 10:10:10.123456", interval 1 quarter)`, true, "SELECT DATE_ADD('2011-11-11 10:10:10.123456', INTERVAL 1 QUARTER)"}, 1265 {`select date_add("2011-11-11 10:10:10.123456", interval 1 year)`, true, "SELECT DATE_ADD('2011-11-11 10:10:10.123456', INTERVAL 1 YEAR)"}, 1266 {`select date_add("2011-11-11 10:10:10.123456", interval "10.10" second_microsecond)`, true, "SELECT DATE_ADD('2011-11-11 10:10:10.123456', INTERVAL '10.10' SECOND_MICROSECOND)"}, 1267 {`select date_add("2011-11-11 10:10:10.123456", interval "10:10.10" minute_microsecond)`, true, "SELECT DATE_ADD('2011-11-11 10:10:10.123456', INTERVAL '10:10.10' MINUTE_MICROSECOND)"}, 1268 {`select date_add("2011-11-11 10:10:10.123456", interval "10:10" minute_second)`, true, "SELECT DATE_ADD('2011-11-11 10:10:10.123456', INTERVAL '10:10' MINUTE_SECOND)"}, 1269 {`select date_add("2011-11-11 10:10:10.123456", interval "10:10:10.10" hour_microsecond)`, true, "SELECT DATE_ADD('2011-11-11 10:10:10.123456', INTERVAL '10:10:10.10' HOUR_MICROSECOND)"}, 1270 {`select date_add("2011-11-11 10:10:10.123456", interval "10:10:10" hour_second)`, true, "SELECT DATE_ADD('2011-11-11 10:10:10.123456', INTERVAL '10:10:10' HOUR_SECOND)"}, 1271 {`select date_add("2011-11-11 10:10:10.123456", interval "10:10" hour_minute)`, true, "SELECT DATE_ADD('2011-11-11 10:10:10.123456', INTERVAL '10:10' HOUR_MINUTE)"}, 1272 {`select date_add("2011-11-11 10:10:10.123456", interval 10.10 hour_minute)`, true, "SELECT DATE_ADD('2011-11-11 10:10:10.123456', INTERVAL 10.10 HOUR_MINUTE)"}, 1273 {`select date_add("2011-11-11 10:10:10.123456", interval "11 10:10:10.10" day_microsecond)`, true, "SELECT DATE_ADD('2011-11-11 10:10:10.123456', INTERVAL '11 10:10:10.10' DAY_MICROSECOND)"}, 1274 {`select date_add("2011-11-11 10:10:10.123456", interval "11 10:10:10" day_second)`, true, "SELECT DATE_ADD('2011-11-11 10:10:10.123456', INTERVAL '11 10:10:10' DAY_SECOND)"}, 1275 {`select date_add("2011-11-11 10:10:10.123456", interval "11 10:10" day_minute)`, true, "SELECT DATE_ADD('2011-11-11 10:10:10.123456', INTERVAL '11 10:10' DAY_MINUTE)"}, 1276 {`select date_add("2011-11-11 10:10:10.123456", interval "11 10" day_hour)`, true, "SELECT DATE_ADD('2011-11-11 10:10:10.123456', INTERVAL '11 10' DAY_HOUR)"}, 1277 {`select date_add("2011-11-11 10:10:10.123456", interval "11-11" year_month)`, true, "SELECT DATE_ADD('2011-11-11 10:10:10.123456', INTERVAL '11-11' YEAR_MONTH)"}, 1278 {`select date_add("2011-11-11 10:10:10.123456", 10)`, false, ""}, 1279 {`select date_add("2011-11-11 10:10:10.123456", 0.10)`, false, ""}, 1280 {`select date_add("2011-11-11 10:10:10.123456", "11,11")`, false, ""}, 1281 1282 // for strcmp 1283 {`select strcmp('abc', 'def')`, true, "SELECT STRCMP('abc', 'def')"}, 1284 1285 // for adddate 1286 {`select adddate("2011-11-11 10:10:10.123456", interval 10 microsecond)`, true, "SELECT ADDDATE('2011-11-11 10:10:10.123456', INTERVAL 10 MICROSECOND)"}, 1287 {`select adddate("2011-11-11 10:10:10.123456", interval 10 second)`, true, "SELECT ADDDATE('2011-11-11 10:10:10.123456', INTERVAL 10 SECOND)"}, 1288 {`select adddate("2011-11-11 10:10:10.123456", interval 10 minute)`, true, "SELECT ADDDATE('2011-11-11 10:10:10.123456', INTERVAL 10 MINUTE)"}, 1289 {`select adddate("2011-11-11 10:10:10.123456", interval 10 hour)`, true, "SELECT ADDDATE('2011-11-11 10:10:10.123456', INTERVAL 10 HOUR)"}, 1290 {`select adddate("2011-11-11 10:10:10.123456", interval 10 day)`, true, "SELECT ADDDATE('2011-11-11 10:10:10.123456', INTERVAL 10 DAY)"}, 1291 {`select adddate("2011-11-11 10:10:10.123456", interval 1 week)`, true, "SELECT ADDDATE('2011-11-11 10:10:10.123456', INTERVAL 1 WEEK)"}, 1292 {`select adddate("2011-11-11 10:10:10.123456", interval 1 month)`, true, "SELECT ADDDATE('2011-11-11 10:10:10.123456', INTERVAL 1 MONTH)"}, 1293 {`select adddate("2011-11-11 10:10:10.123456", interval 1 quarter)`, true, "SELECT ADDDATE('2011-11-11 10:10:10.123456', INTERVAL 1 QUARTER)"}, 1294 {`select adddate("2011-11-11 10:10:10.123456", interval 1 year)`, true, "SELECT ADDDATE('2011-11-11 10:10:10.123456', INTERVAL 1 YEAR)"}, 1295 {`select adddate("2011-11-11 10:10:10.123456", interval "10.10" second_microsecond)`, true, "SELECT ADDDATE('2011-11-11 10:10:10.123456', INTERVAL '10.10' SECOND_MICROSECOND)"}, 1296 {`select adddate("2011-11-11 10:10:10.123456", interval "10:10.10" minute_microsecond)`, true, "SELECT ADDDATE('2011-11-11 10:10:10.123456', INTERVAL '10:10.10' MINUTE_MICROSECOND)"}, 1297 {`select adddate("2011-11-11 10:10:10.123456", interval "10:10" minute_second)`, true, "SELECT ADDDATE('2011-11-11 10:10:10.123456', INTERVAL '10:10' MINUTE_SECOND)"}, 1298 {`select adddate("2011-11-11 10:10:10.123456", interval "10:10:10.10" hour_microsecond)`, true, "SELECT ADDDATE('2011-11-11 10:10:10.123456', INTERVAL '10:10:10.10' HOUR_MICROSECOND)"}, 1299 {`select adddate("2011-11-11 10:10:10.123456", interval "10:10:10" hour_second)`, true, "SELECT ADDDATE('2011-11-11 10:10:10.123456', INTERVAL '10:10:10' HOUR_SECOND)"}, 1300 {`select adddate("2011-11-11 10:10:10.123456", interval "10:10" hour_minute)`, true, "SELECT ADDDATE('2011-11-11 10:10:10.123456', INTERVAL '10:10' HOUR_MINUTE)"}, 1301 {`select adddate("2011-11-11 10:10:10.123456", interval 10.10 hour_minute)`, true, "SELECT ADDDATE('2011-11-11 10:10:10.123456', INTERVAL 10.10 HOUR_MINUTE)"}, 1302 {`select adddate("2011-11-11 10:10:10.123456", interval "11 10:10:10.10" day_microsecond)`, true, "SELECT ADDDATE('2011-11-11 10:10:10.123456', INTERVAL '11 10:10:10.10' DAY_MICROSECOND)"}, 1303 {`select adddate("2011-11-11 10:10:10.123456", interval "11 10:10:10" day_second)`, true, "SELECT ADDDATE('2011-11-11 10:10:10.123456', INTERVAL '11 10:10:10' DAY_SECOND)"}, 1304 {`select adddate("2011-11-11 10:10:10.123456", interval "11 10:10" day_minute)`, true, "SELECT ADDDATE('2011-11-11 10:10:10.123456', INTERVAL '11 10:10' DAY_MINUTE)"}, 1305 {`select adddate("2011-11-11 10:10:10.123456", interval "11 10" day_hour)`, true, "SELECT ADDDATE('2011-11-11 10:10:10.123456', INTERVAL '11 10' DAY_HOUR)"}, 1306 {`select adddate("2011-11-11 10:10:10.123456", interval "11-11" year_month)`, true, "SELECT ADDDATE('2011-11-11 10:10:10.123456', INTERVAL '11-11' YEAR_MONTH)"}, 1307 {`select adddate("2011-11-11 10:10:10.123456", 10)`, true, "SELECT ADDDATE('2011-11-11 10:10:10.123456', INTERVAL 10 DAY)"}, 1308 {`select adddate("2011-11-11 10:10:10.123456", 0.10)`, true, "SELECT ADDDATE('2011-11-11 10:10:10.123456', INTERVAL 0.10 DAY)"}, 1309 {`select adddate("2011-11-11 10:10:10.123456", "11,11")`, true, "SELECT ADDDATE('2011-11-11 10:10:10.123456', INTERVAL '11,11' DAY)"}, 1310 1311 // for date_sub 1312 {`select date_sub("2011-11-11 10:10:10.123456", interval 10 microsecond)`, true, "SELECT DATE_SUB('2011-11-11 10:10:10.123456', INTERVAL 10 MICROSECOND)"}, 1313 {`select date_sub("2011-11-11 10:10:10.123456", interval 10 second)`, true, "SELECT DATE_SUB('2011-11-11 10:10:10.123456', INTERVAL 10 SECOND)"}, 1314 {`select date_sub("2011-11-11 10:10:10.123456", interval 10 minute)`, true, "SELECT DATE_SUB('2011-11-11 10:10:10.123456', INTERVAL 10 MINUTE)"}, 1315 {`select date_sub("2011-11-11 10:10:10.123456", interval 10 hour)`, true, "SELECT DATE_SUB('2011-11-11 10:10:10.123456', INTERVAL 10 HOUR)"}, 1316 {`select date_sub("2011-11-11 10:10:10.123456", interval 10 day)`, true, "SELECT DATE_SUB('2011-11-11 10:10:10.123456', INTERVAL 10 DAY)"}, 1317 {`select date_sub("2011-11-11 10:10:10.123456", interval 1 week)`, true, "SELECT DATE_SUB('2011-11-11 10:10:10.123456', INTERVAL 1 WEEK)"}, 1318 {`select date_sub("2011-11-11 10:10:10.123456", interval 1 month)`, true, "SELECT DATE_SUB('2011-11-11 10:10:10.123456', INTERVAL 1 MONTH)"}, 1319 {`select date_sub("2011-11-11 10:10:10.123456", interval 1 quarter)`, true, "SELECT DATE_SUB('2011-11-11 10:10:10.123456', INTERVAL 1 QUARTER)"}, 1320 {`select date_sub("2011-11-11 10:10:10.123456", interval 1 year)`, true, "SELECT DATE_SUB('2011-11-11 10:10:10.123456', INTERVAL 1 YEAR)"}, 1321 {`select date_sub("2011-11-11 10:10:10.123456", interval "10.10" second_microsecond)`, true, "SELECT DATE_SUB('2011-11-11 10:10:10.123456', INTERVAL '10.10' SECOND_MICROSECOND)"}, 1322 {`select date_sub("2011-11-11 10:10:10.123456", interval "10:10.10" minute_microsecond)`, true, "SELECT DATE_SUB('2011-11-11 10:10:10.123456', INTERVAL '10:10.10' MINUTE_MICROSECOND)"}, 1323 {`select date_sub("2011-11-11 10:10:10.123456", interval "10:10" minute_second)`, true, "SELECT DATE_SUB('2011-11-11 10:10:10.123456', INTERVAL '10:10' MINUTE_SECOND)"}, 1324 {`select date_sub("2011-11-11 10:10:10.123456", interval "10:10:10.10" hour_microsecond)`, true, "SELECT DATE_SUB('2011-11-11 10:10:10.123456', INTERVAL '10:10:10.10' HOUR_MICROSECOND)"}, 1325 {`select date_sub("2011-11-11 10:10:10.123456", interval "10:10:10" hour_second)`, true, "SELECT DATE_SUB('2011-11-11 10:10:10.123456', INTERVAL '10:10:10' HOUR_SECOND)"}, 1326 {`select date_sub("2011-11-11 10:10:10.123456", interval "10:10" hour_minute)`, true, "SELECT DATE_SUB('2011-11-11 10:10:10.123456', INTERVAL '10:10' HOUR_MINUTE)"}, 1327 {`select date_sub("2011-11-11 10:10:10.123456", interval 10.10 hour_minute)`, true, "SELECT DATE_SUB('2011-11-11 10:10:10.123456', INTERVAL 10.10 HOUR_MINUTE)"}, 1328 {`select date_sub("2011-11-11 10:10:10.123456", interval "11 10:10:10.10" day_microsecond)`, true, "SELECT DATE_SUB('2011-11-11 10:10:10.123456', INTERVAL '11 10:10:10.10' DAY_MICROSECOND)"}, 1329 {`select date_sub("2011-11-11 10:10:10.123456", interval "11 10:10:10" day_second)`, true, "SELECT DATE_SUB('2011-11-11 10:10:10.123456', INTERVAL '11 10:10:10' DAY_SECOND)"}, 1330 {`select date_sub("2011-11-11 10:10:10.123456", interval "11 10:10" day_minute)`, true, "SELECT DATE_SUB('2011-11-11 10:10:10.123456', INTERVAL '11 10:10' DAY_MINUTE)"}, 1331 {`select date_sub("2011-11-11 10:10:10.123456", interval "11 10" day_hour)`, true, "SELECT DATE_SUB('2011-11-11 10:10:10.123456', INTERVAL '11 10' DAY_HOUR)"}, 1332 {`select date_sub("2011-11-11 10:10:10.123456", interval "11-11" year_month)`, true, "SELECT DATE_SUB('2011-11-11 10:10:10.123456', INTERVAL '11-11' YEAR_MONTH)"}, 1333 {`select date_sub("2011-11-11 10:10:10.123456", 10)`, false, ""}, 1334 {`select date_sub("2011-11-11 10:10:10.123456", 0.10)`, false, ""}, 1335 {`select date_sub("2011-11-11 10:10:10.123456", "11,11")`, false, ""}, 1336 1337 // for subdate 1338 {`select subdate("2011-11-11 10:10:10.123456", interval 10 microsecond)`, true, "SELECT SUBDATE('2011-11-11 10:10:10.123456', INTERVAL 10 MICROSECOND)"}, 1339 {`select subdate("2011-11-11 10:10:10.123456", interval 10 second)`, true, "SELECT SUBDATE('2011-11-11 10:10:10.123456', INTERVAL 10 SECOND)"}, 1340 {`select subdate("2011-11-11 10:10:10.123456", interval 10 minute)`, true, "SELECT SUBDATE('2011-11-11 10:10:10.123456', INTERVAL 10 MINUTE)"}, 1341 {`select subdate("2011-11-11 10:10:10.123456", interval 10 hour)`, true, "SELECT SUBDATE('2011-11-11 10:10:10.123456', INTERVAL 10 HOUR)"}, 1342 {`select subdate("2011-11-11 10:10:10.123456", interval 10 day)`, true, "SELECT SUBDATE('2011-11-11 10:10:10.123456', INTERVAL 10 DAY)"}, 1343 {`select subdate("2011-11-11 10:10:10.123456", interval 1 week)`, true, "SELECT SUBDATE('2011-11-11 10:10:10.123456', INTERVAL 1 WEEK)"}, 1344 {`select subdate("2011-11-11 10:10:10.123456", interval 1 month)`, true, "SELECT SUBDATE('2011-11-11 10:10:10.123456', INTERVAL 1 MONTH)"}, 1345 {`select subdate("2011-11-11 10:10:10.123456", interval 1 quarter)`, true, "SELECT SUBDATE('2011-11-11 10:10:10.123456', INTERVAL 1 QUARTER)"}, 1346 {`select subdate("2011-11-11 10:10:10.123456", interval 1 year)`, true, "SELECT SUBDATE('2011-11-11 10:10:10.123456', INTERVAL 1 YEAR)"}, 1347 {`select subdate("2011-11-11 10:10:10.123456", interval "10.10" second_microsecond)`, true, "SELECT SUBDATE('2011-11-11 10:10:10.123456', INTERVAL '10.10' SECOND_MICROSECOND)"}, 1348 {`select subdate("2011-11-11 10:10:10.123456", interval "10:10.10" minute_microsecond)`, true, "SELECT SUBDATE('2011-11-11 10:10:10.123456', INTERVAL '10:10.10' MINUTE_MICROSECOND)"}, 1349 {`select subdate("2011-11-11 10:10:10.123456", interval "10:10" minute_second)`, true, "SELECT SUBDATE('2011-11-11 10:10:10.123456', INTERVAL '10:10' MINUTE_SECOND)"}, 1350 {`select subdate("2011-11-11 10:10:10.123456", interval "10:10:10.10" hour_microsecond)`, true, "SELECT SUBDATE('2011-11-11 10:10:10.123456', INTERVAL '10:10:10.10' HOUR_MICROSECOND)"}, 1351 {`select subdate("2011-11-11 10:10:10.123456", interval "10:10:10" hour_second)`, true, "SELECT SUBDATE('2011-11-11 10:10:10.123456', INTERVAL '10:10:10' HOUR_SECOND)"}, 1352 {`select subdate("2011-11-11 10:10:10.123456", interval "10:10" hour_minute)`, true, "SELECT SUBDATE('2011-11-11 10:10:10.123456', INTERVAL '10:10' HOUR_MINUTE)"}, 1353 {`select subdate("2011-11-11 10:10:10.123456", interval 10.10 hour_minute)`, true, "SELECT SUBDATE('2011-11-11 10:10:10.123456', INTERVAL 10.10 HOUR_MINUTE)"}, 1354 {`select subdate("2011-11-11 10:10:10.123456", interval "11 10:10:10.10" day_microsecond)`, true, "SELECT SUBDATE('2011-11-11 10:10:10.123456', INTERVAL '11 10:10:10.10' DAY_MICROSECOND)"}, 1355 {`select subdate("2011-11-11 10:10:10.123456", interval "11 10:10:10" day_second)`, true, "SELECT SUBDATE('2011-11-11 10:10:10.123456', INTERVAL '11 10:10:10' DAY_SECOND)"}, 1356 {`select subdate("2011-11-11 10:10:10.123456", interval "11 10:10" day_minute)`, true, "SELECT SUBDATE('2011-11-11 10:10:10.123456', INTERVAL '11 10:10' DAY_MINUTE)"}, 1357 {`select subdate("2011-11-11 10:10:10.123456", interval "11 10" day_hour)`, true, "SELECT SUBDATE('2011-11-11 10:10:10.123456', INTERVAL '11 10' DAY_HOUR)"}, 1358 {`select subdate("2011-11-11 10:10:10.123456", interval "11-11" year_month)`, true, "SELECT SUBDATE('2011-11-11 10:10:10.123456', INTERVAL '11-11' YEAR_MONTH)"}, 1359 {`select subdate("2011-11-11 10:10:10.123456", 10)`, true, "SELECT SUBDATE('2011-11-11 10:10:10.123456', INTERVAL 10 DAY)"}, 1360 {`select subdate("2011-11-11 10:10:10.123456", 0.10)`, true, "SELECT SUBDATE('2011-11-11 10:10:10.123456', INTERVAL 0.10 DAY)"}, 1361 {`select subdate("2011-11-11 10:10:10.123456", "11,11")`, true, "SELECT SUBDATE('2011-11-11 10:10:10.123456', INTERVAL '11,11' DAY)"}, 1362 1363 // for unix_timestamp 1364 {`select unix_timestamp()`, true, "SELECT UNIX_TIMESTAMP()"}, 1365 {`select unix_timestamp('2015-11-13 10:20:19.012')`, true, "SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19.012')"}, 1366 1367 // for misc functions 1368 {`SELECT GET_LOCK('lock1',10);`, true, "SELECT GET_LOCK('lock1', 10)"}, 1369 {`SELECT RELEASE_LOCK('lock1');`, true, "SELECT RELEASE_LOCK('lock1')"}, 1370 1371 // for aggregate functions 1372 {`select avg(), avg(c1,c2) from t;`, false, "SELECT AVG(),AVG(`c1`, `c2`) FROM `t`"}, 1373 {`select avg(distinct c1) from t;`, true, "SELECT AVG(DISTINCT `c1`) FROM `t`"}, 1374 {`select avg(distinctrow c1) from t;`, true, "SELECT AVG(DISTINCT `c1`) FROM `t`"}, 1375 {`select avg(distinct all c1) from t;`, true, "SELECT AVG(DISTINCT `c1`) FROM `t`"}, 1376 {`select avg(distinctrow all c1) from t;`, true, "SELECT AVG(DISTINCT `c1`) FROM `t`"}, 1377 {`select avg(c2) from t;`, true, "SELECT AVG(`c2`) FROM `t`"}, 1378 {`select bit_and(c1) from t;`, true, "SELECT BIT_AND(`c1`) FROM `t`"}, 1379 {`select bit_and(all c1) from t;`, true, "SELECT BIT_AND(`c1`) FROM `t`"}, 1380 {`select bit_and(distinct c1) from t;`, false, ""}, 1381 {`select bit_and(distinctrow c1) from t;`, false, ""}, 1382 {`select bit_and(distinctrow all c1) from t;`, false, ""}, 1383 {`select bit_and(distinct all c1) from t;`, false, ""}, 1384 {`select bit_and(), bit_and(distinct c1) from t;`, false, ""}, 1385 {`select bit_and(), bit_and(distinctrow c1) from t;`, false, ""}, 1386 {`select bit_and(), bit_and(all c1) from t;`, false, ""}, 1387 {`select bit_or(c1) from t;`, true, "SELECT BIT_OR(`c1`) FROM `t`"}, 1388 {`select bit_or(all c1) from t;`, true, "SELECT BIT_OR(`c1`) FROM `t`"}, 1389 {`select bit_or(distinct c1) from t;`, false, ""}, 1390 {`select bit_or(distinctrow c1) from t;`, false, ""}, 1391 {`select bit_or(distinctrow all c1) from t;`, false, ""}, 1392 {`select bit_or(distinct all c1) from t;`, false, ""}, 1393 {`select bit_or(), bit_or(distinct c1) from t;`, false, ""}, 1394 {`select bit_or(), bit_or(distinctrow c1) from t;`, false, ""}, 1395 {`select bit_or(), bit_or(all c1) from t;`, false, ""}, 1396 {`select bit_xor(c1) from t;`, true, "SELECT BIT_XOR(`c1`) FROM `t`"}, 1397 {`select bit_xor(all c1) from t;`, true, "SELECT BIT_XOR(`c1`) FROM `t`"}, 1398 {`select bit_xor(distinct c1) from t;`, false, ""}, 1399 {`select bit_xor(distinctrow c1) from t;`, false, ""}, 1400 {`select bit_xor(distinctrow all c1) from t;`, false, ""}, 1401 {`select bit_xor(), bit_xor(distinct c1) from t;`, false, ""}, 1402 {`select bit_xor(), bit_xor(distinctrow c1) from t;`, false, ""}, 1403 {`select bit_xor(), bit_xor(all c1) from t;`, false, ""}, 1404 {`select max(c1,c2) from t;`, false, ""}, 1405 {`select max(distinct c1) from t;`, true, "SELECT MAX(DISTINCT `c1`) FROM `t`"}, 1406 {`select max(distinctrow c1) from t;`, true, "SELECT MAX(DISTINCT `c1`) FROM `t`"}, 1407 {`select max(distinct all c1) from t;`, true, "SELECT MAX(DISTINCT `c1`) FROM `t`"}, 1408 {`select max(distinctrow all c1) from t;`, true, "SELECT MAX(DISTINCT `c1`) FROM `t`"}, 1409 {`select max(c2) from t;`, true, "SELECT MAX(`c2`) FROM `t`"}, 1410 {`select min(c1,c2) from t;`, false, ""}, 1411 {`select min(distinct c1) from t;`, true, "SELECT MIN(DISTINCT `c1`) FROM `t`"}, 1412 {`select min(distinctrow c1) from t;`, true, "SELECT MIN(DISTINCT `c1`) FROM `t`"}, 1413 {`select min(distinct all c1) from t;`, true, "SELECT MIN(DISTINCT `c1`) FROM `t`"}, 1414 {`select min(distinctrow all c1) from t;`, true, "SELECT MIN(DISTINCT `c1`) FROM `t`"}, 1415 {`select min(c2) from t;`, true, "SELECT MIN(`c2`) FROM `t`"}, 1416 {`select sum(c1,c2) from t;`, false, ""}, 1417 {`select sum(distinct c1) from t;`, true, "SELECT SUM(DISTINCT `c1`) FROM `t`"}, 1418 {`select sum(distinctrow c1) from t;`, true, "SELECT SUM(DISTINCT `c1`) FROM `t`"}, 1419 {`select sum(distinct all c1) from t;`, true, "SELECT SUM(DISTINCT `c1`) FROM `t`"}, 1420 {`select sum(distinctrow all c1) from t;`, true, "SELECT SUM(DISTINCT `c1`) FROM `t`"}, 1421 {`select sum(c2) from t;`, true, "SELECT SUM(`c2`) FROM `t`"}, 1422 {`select count(c1) from t;`, true, "SELECT COUNT(`c1`) FROM `t`"}, 1423 {`select count(distinct *) from t;`, false, ""}, 1424 {`select count(distinctrow *) from t;`, false, ""}, 1425 {`select count(*) from t;`, true, "SELECT COUNT(1) FROM `t`"}, 1426 {`select count(distinct c1, c2) from t;`, true, "SELECT COUNT(DISTINCT `c1`, `c2`) FROM `t`"}, 1427 {`select count(distinctrow c1, c2) from t;`, true, "SELECT COUNT(DISTINCT `c1`, `c2`) FROM `t`"}, 1428 {`select count(c1, c2) from t;`, false, ""}, 1429 {`select count(all c1) from t;`, true, "SELECT COUNT(`c1`) FROM `t`"}, 1430 {`select count(distinct all c1) from t;`, false, ""}, 1431 {`select count(distinctrow all c1) from t;`, false, ""}, 1432 {`select group_concat(c2,c1) from t group by c1;`, true, "SELECT GROUP_CONCAT(`c2`, `c1` SEPARATOR ',') FROM `t` GROUP BY `c1`"}, 1433 {`select group_concat(c2,c1 SEPARATOR ';') from t group by c1;`, true, "SELECT GROUP_CONCAT(`c2`, `c1` SEPARATOR ';') FROM `t` GROUP BY `c1`"}, 1434 {`select group_concat(distinct c2,c1) from t group by c1;`, true, "SELECT GROUP_CONCAT(DISTINCT `c2`, `c1` SEPARATOR ',') FROM `t` GROUP BY `c1`"}, 1435 {`select group_concat(distinctrow c2,c1) from t group by c1;`, true, "SELECT GROUP_CONCAT(DISTINCT `c2`, `c1` SEPARATOR ',') FROM `t` GROUP BY `c1`"}, 1436 {`SELECT student_name, GROUP_CONCAT(DISTINCT test_score ORDER BY test_score DESC SEPARATOR ' ') FROM student GROUP BY student_name;`, true, "SELECT `student_name`,GROUP_CONCAT(DISTINCT `test_score` SEPARATOR ' ') FROM `student` GROUP BY `student_name`"}, 1437 {`select std(c1), std(all c1), std(distinct c1) from t`, true, "SELECT STD(`c1`),STD(`c1`),STD(DISTINCT `c1`) FROM `t`"}, 1438 {`select std(c1, c2) from t`, false, ""}, 1439 {`select stddev(c1), stddev(all c1), stddev(distinct c1) from t`, true, "SELECT STDDEV(`c1`),STDDEV(`c1`),STDDEV(DISTINCT `c1`) FROM `t`"}, 1440 {`select stddev(c1, c2) from t`, false, ""}, 1441 {`select stddev_pop(c1), stddev_pop(all c1), stddev_pop(distinct c1) from t`, true, "SELECT STDDEV_POP(`c1`),STDDEV_POP(`c1`),STDDEV_POP(DISTINCT `c1`) FROM `t`"}, 1442 {`select stddev_pop(c1, c2) from t`, false, ""}, 1443 {`select stddev_samp(c1), stddev_samp(all c1), stddev_samp(distinct c1) from t`, true, "SELECT STDDEV_SAMP(`c1`),STDDEV_SAMP(`c1`),STDDEV_SAMP(DISTINCT `c1`) FROM `t`"}, 1444 {`select stddev_samp(c1, c2) from t`, false, ""}, 1445 {`select variance(c1), variance(all c1), variance(distinct c1) from t`, true, "SELECT VAR_POP(`c1`),VAR_POP(`c1`),VAR_POP(DISTINCT `c1`) FROM `t`"}, 1446 {`select variance(c1, c2) from t`, false, ""}, 1447 {`select var_pop(c1), var_pop(all c1), var_pop(distinct c1) from t`, true, "SELECT VAR_POP(`c1`),VAR_POP(`c1`),VAR_POP(DISTINCT `c1`) FROM `t`"}, 1448 {`select var_pop(c1, c2) from t`, false, ""}, 1449 {`select var_samp(c1), var_samp(all c1), var_samp(distinct c1) from t`, true, "SELECT VAR_SAMP(`c1`),VAR_SAMP(`c1`),VAR_SAMP(DISTINCT `c1`) FROM `t`"}, 1450 {`select var_samp(c1, c2) from t`, false, ""}, 1451 1452 // for encryption and compression functions 1453 {`select AES_ENCRYPT('text',UNHEX('F3229A0B371ED2D9441B830D21A390C3'))`, true, "SELECT AES_ENCRYPT('text', UNHEX('F3229A0B371ED2D9441B830D21A390C3'))"}, 1454 {`select AES_DECRYPT(@crypt_str,@key_str)`, true, "SELECT AES_DECRYPT(@`crypt_str`, @`key_str`)"}, 1455 {`select AES_DECRYPT(@crypt_str,@key_str,@init_vector);`, true, "SELECT AES_DECRYPT(@`crypt_str`, @`key_str`, @`init_vector`)"}, 1456 {`SELECT COMPRESS('');`, true, "SELECT COMPRESS('')"}, 1457 {`SELECT DECODE(@crypt_str, @pass_str);`, true, "SELECT DECODE(@`crypt_str`, @`pass_str`)"}, 1458 {`SELECT DES_DECRYPT(@crypt_str), DES_DECRYPT(@crypt_str, @key_str);`, true, "SELECT DES_DECRYPT(@`crypt_str`),DES_DECRYPT(@`crypt_str`, @`key_str`)"}, 1459 {`SELECT DES_ENCRYPT(@str), DES_ENCRYPT(@key_num);`, true, "SELECT DES_ENCRYPT(@`str`),DES_ENCRYPT(@`key_num`)"}, 1460 {`SELECT ENCODE('cleartext', CONCAT('my_random_salt','my_secret_password'));`, true, "SELECT ENCODE('cleartext', CONCAT('my_random_salt', 'my_secret_password'))"}, 1461 {`SELECT ENCRYPT('hello'), ENCRYPT('hello', @salt);`, true, "SELECT ENCRYPT('hello'),ENCRYPT('hello', @`salt`)"}, 1462 {`SELECT MD5('testing');`, true, "SELECT MD5('testing')"}, 1463 {`SELECT OLD_PASSWORD(@str);`, true, "SELECT OLD_PASSWORD(@`str`)"}, 1464 {`SELECT PASSWORD(@str);`, true, "SELECT PASSWORD_FUNC(@`str`)"}, 1465 {`SELECT RANDOM_BYTES(@len);`, true, "SELECT RANDOM_BYTES(@`len`)"}, 1466 {`SELECT SHA1('abc');`, true, "SELECT SHA1('abc')"}, 1467 {`SELECT SHA('abc');`, true, "SELECT SHA('abc')"}, 1468 {`SELECT SHA2('abc', 224);`, true, "SELECT SHA2('abc', 224)"}, 1469 {`SELECT UNCOMPRESS('any string');`, true, "SELECT UNCOMPRESS('any string')"}, 1470 {`SELECT UNCOMPRESSED_LENGTH(@compressed_string);`, true, "SELECT UNCOMPRESSED_LENGTH(@`compressed_string`)"}, 1471 {`SELECT VALIDATE_PASSWORD_STRENGTH(@str);`, true, "SELECT VALIDATE_PASSWORD_STRENGTH(@`str`)"}, 1472 1473 // For JSON functions. 1474 {`SELECT JSON_EXTRACT();`, true, "SELECT JSON_EXTRACT()"}, 1475 {`SELECT JSON_UNQUOTE();`, true, "SELECT JSON_UNQUOTE()"}, 1476 {`SELECT JSON_TYPE('[123]');`, true, "SELECT JSON_TYPE('[123]')"}, 1477 {`SELECT JSON_TYPE();`, true, "SELECT JSON_TYPE()"}, 1478 1479 // For two json grammar sugar. 1480 {`SELECT a->'$.a' FROM t`, true, "SELECT JSON_EXTRACT(`a`, '$.a') FROM `t`"}, 1481 {`SELECT a->>'$.a' FROM t`, true, "SELECT JSON_UNQUOTE(JSON_EXTRACT(`a`, '$.a')) FROM `t`"}, 1482 {`SELECT '{}'->'$.a' FROM t`, false, ""}, 1483 {`SELECT '{}'->>'$.a' FROM t`, false, ""}, 1484 {`SELECT a->3 FROM t`, false, ""}, 1485 {`SELECT a->>3 FROM t`, false, ""}, 1486 1487 // Test that quoted identifier can be a function name. 1488 {"SELECT `uuid`()", true, "SELECT UUID()"}, 1489 } 1490 s.RunTest(c, table) 1491 } 1492 1493 func (s *testParserSuite) TestIdentifier(c *C) { 1494 table := []testCase{ 1495 // for quote identifier 1496 {"select `a`, `a.b`, `a b` from t", true, "SELECT `a`,`a.b`,`a b` FROM `t`"}, 1497 // for unquoted identifier 1498 {"create table MergeContextTest$Simple (value integer not null, primary key (value))", true, "CREATE TABLE `MergeContextTest$Simple` (`value` INT NOT NULL,PRIMARY KEY(`value`))"}, 1499 // for as 1500 {"select 1 as a, 1 as `a`, 1 as \"a\", 1 as 'a'", true, "SELECT 1 AS `a`,1 AS `a`,1 AS `a`,1 AS `a`"}, 1501 {`select 1 as a, 1 as "a", 1 as 'a'`, true, "SELECT 1 AS `a`,1 AS `a`,1 AS `a`"}, 1502 {`select 1 a, 1 "a", 1 'a'`, true, "SELECT 1 AS `a`,1 AS `a`,1 AS `a`"}, 1503 {`select * from t as "a"`, false, ""}, 1504 {`select * from t a`, true, "SELECT * FROM `t` AS `a`"}, 1505 // reserved keyword can't be used as identifier directly, but A.B pattern is an exception 1506 {`select COUNT from DESC`, false, ""}, 1507 {`select COUNT from SELECT.DESC`, true, "SELECT `COUNT` FROM `SELECT`.`DESC`"}, 1508 {"use `select`", true, "USE `select`"}, 1509 {"use `sel``ect`", true, "USE `sel``ect`"}, 1510 {"use select", false, "USE `select`"}, 1511 {`select * from t as a`, true, "SELECT * FROM `t` AS `a`"}, 1512 {"select 1 full, 1 row, 1 abs", false, ""}, 1513 {"select 1 full, 1 `row`, 1 abs", true, "SELECT 1 AS `full`,1 AS `row`,1 AS `abs`"}, 1514 {"select * from t full, t1 row, t2 abs", false, ""}, 1515 {"select * from t full, t1 `row`, t2 abs", true, "SELECT * FROM ((`t` AS `full`) JOIN `t1` AS `row`) JOIN `t2` AS `abs`"}, 1516 // for issue 1878, identifiers may begin with digit. 1517 {"create database 123test", true, "CREATE DATABASE `123test`"}, 1518 {"create database 123", false, "CREATE DATABASE `123`"}, 1519 {"create database `123`", true, "CREATE DATABASE `123`"}, 1520 {"create database `12``3`", true, "CREATE DATABASE `12``3`"}, 1521 {"create table `123` (123a1 int)", true, "CREATE TABLE `123` (`123a1` INT)"}, 1522 {"create table 123 (123a1 int)", false, ""}, 1523 {fmt.Sprintf("select * from t%cble", 0), false, ""}, 1524 // for issue 3954, should NOT be recognized as identifiers. 1525 {`select .78+123`, true, "SELECT 0.78+123"}, 1526 {`select .78+.21`, true, "SELECT 0.78+0.21"}, 1527 {`select .78-123`, true, "SELECT 0.78-123"}, 1528 {`select .78-.21`, true, "SELECT 0.78-0.21"}, 1529 {`select .78--123`, true, "SELECT 0.78--123"}, 1530 {`select .78*123`, true, "SELECT 0.78*123"}, 1531 {`select .78*.21`, true, "SELECT 0.78*0.21"}, 1532 {`select .78/123`, true, "SELECT 0.78/123"}, 1533 {`select .78/.21`, true, "SELECT 0.78/0.21"}, 1534 {`select .78,123`, true, "SELECT 0.78,123"}, 1535 {`select .78,.21`, true, "SELECT 0.78,0.21"}, 1536 {`select .78 , 123`, true, "SELECT 0.78,123"}, 1537 {`select .78.123`, false, ""}, 1538 {`select .78#123`, true, "SELECT 0.78"}, 1539 {`insert float_test values(.67, 'string');`, true, "INSERT INTO `float_test` VALUES (0.67,'string')"}, 1540 {`select .78'123'`, true, "SELECT 0.78 AS `123`"}, 1541 {"select .78`123`", true, "SELECT 0.78 AS `123`"}, 1542 {`select .78"123"`, true, "SELECT 0.78 AS `123`"}, 1543 } 1544 s.RunTest(c, table) 1545 } 1546 1547 func (s *testParserSuite) TestDDL(c *C) { 1548 table := []testCase{ 1549 {"CREATE", false, ""}, 1550 {"CREATE TABLE", false, ""}, 1551 {"CREATE TABLE foo (", false, ""}, 1552 {"CREATE TABLE foo ()", false, ""}, 1553 {"CREATE TABLE foo ();", false, ""}, 1554 {"CREATE TABLE foo (a varchar(50), b int);", true, "CREATE TABLE `foo` (`a` VARCHAR(50),`b` INT)"}, 1555 {"CREATE TABLE foo (a TINYINT UNSIGNED);", true, "CREATE TABLE `foo` (`a` TINYINT UNSIGNED)"}, 1556 {"CREATE TABLE foo (a SMALLINT UNSIGNED, b INT UNSIGNED)", true, "CREATE TABLE `foo` (`a` SMALLINT UNSIGNED,`b` INT UNSIGNED)"}, 1557 {"CREATE TABLE foo (a bigint unsigned, b bool);", true, "CREATE TABLE `foo` (`a` BIGINT UNSIGNED,`b` TINYINT(1))"}, 1558 {"CREATE TABLE foo (a TINYINT, b SMALLINT) CREATE TABLE bar (x INT, y int64)", false, ""}, 1559 {"CREATE TABLE foo (a int, b float); CREATE TABLE bar (x double, y float)", true, "CREATE TABLE `foo` (`a` INT,`b` FLOAT); CREATE TABLE `bar` (`x` DOUBLE,`y` FLOAT)"}, 1560 {"CREATE TABLE foo (a bytes)", false, ""}, 1561 {"CREATE TABLE foo (a SMALLINT UNSIGNED, b INT UNSIGNED)", true, "CREATE TABLE `foo` (`a` SMALLINT UNSIGNED,`b` INT UNSIGNED)"}, 1562 {"CREATE TABLE foo (a SMALLINT UNSIGNED, b INT UNSIGNED) -- foo", true, "CREATE TABLE `foo` (`a` SMALLINT UNSIGNED,`b` INT UNSIGNED)"}, 1563 {"CREATE TABLE foo (a SMALLINT UNSIGNED, b INT UNSIGNED) // foo", false, ""}, 1564 {"CREATE TABLE foo (a SMALLINT UNSIGNED, b INT UNSIGNED) /* foo */", true, "CREATE TABLE `foo` (`a` SMALLINT UNSIGNED,`b` INT UNSIGNED)"}, 1565 {"CREATE TABLE foo /* foo */ (a SMALLINT UNSIGNED, b INT UNSIGNED) /* foo */", true, "CREATE TABLE `foo` (`a` SMALLINT UNSIGNED,`b` INT UNSIGNED)"}, 1566 {"CREATE TABLE foo (name CHAR(50) BINARY);", true, "CREATE TABLE `foo` (`name` CHAR(50) BINARY)"}, 1567 {"CREATE TABLE foo (name CHAR(50) COLLATE utf8_bin)", true, "CREATE TABLE `foo` (`name` CHAR(50) COLLATE utf8_bin)"}, 1568 {"CREATE TABLE foo (id varchar(50) collate utf8);", true, "CREATE TABLE `foo` (`id` VARCHAR(50) COLLATE utf8)"}, 1569 {"CREATE TABLE foo (name CHAR(50) CHARACTER SET UTF8)", true, "CREATE TABLE `foo` (`name` CHAR(50) CHARACTER SET UTF8)"}, 1570 {"CREATE TABLE foo (name CHAR(50) CHARACTER SET utf8 BINARY)", true, "CREATE TABLE `foo` (`name` CHAR(50) BINARY CHARACTER SET UTF8)"}, 1571 {"CREATE TABLE foo (name CHAR(50) CHARACTER SET utf8 BINARY CHARACTER set utf8)", false, ""}, 1572 {"CREATE TABLE foo (name CHAR(50) BINARY CHARACTER SET utf8 COLLATE utf8_bin)", true, "CREATE TABLE `foo` (`name` CHAR(50) BINARY CHARACTER SET UTF8 COLLATE utf8_bin)"}, 1573 {"CREATE TABLE foo (a.b, b);", false, ""}, 1574 {"CREATE TABLE foo (a, b.c);", false, ""}, 1575 {"CREATE TABLE (name CHAR(50) BINARY)", false, ""}, 1576 // for table option 1577 {"create table t (c int) avg_row_length = 3", true, "CREATE TABLE `t` (`c` INT) AVG_ROW_LENGTH = 3"}, 1578 {"create table t (c int) avg_row_length 3", true, "CREATE TABLE `t` (`c` INT) AVG_ROW_LENGTH = 3"}, 1579 {"create table t (c int) checksum = 0", true, "CREATE TABLE `t` (`c` INT) CHECKSUM = 0"}, 1580 {"create table t (c int) checksum 1", true, "CREATE TABLE `t` (`c` INT) CHECKSUM = 1"}, 1581 {"create table t (c int) compression = 'NONE'", true, "CREATE TABLE `t` (`c` INT) COMPRESSION = 'NONE'"}, 1582 {"create table t (c int) compression 'lz4'", true, "CREATE TABLE `t` (`c` INT) COMPRESSION = 'lz4'"}, 1583 {"create table t (c int) connection = 'abc'", true, "CREATE TABLE `t` (`c` INT) CONNECTION = 'abc'"}, 1584 {"create table t (c int) connection 'abc'", true, "CREATE TABLE `t` (`c` INT) CONNECTION = 'abc'"}, 1585 {"create table t (c int) key_block_size = 1024", true, "CREATE TABLE `t` (`c` INT) KEY_BLOCK_SIZE = 1024"}, 1586 {"create table t (c int) key_block_size 1024", true, "CREATE TABLE `t` (`c` INT) KEY_BLOCK_SIZE = 1024"}, 1587 {"create table t (c int) max_rows = 1000", true, "CREATE TABLE `t` (`c` INT) MAX_ROWS = 1000"}, 1588 {"create table t (c int) max_rows 1000", true, "CREATE TABLE `t` (`c` INT) MAX_ROWS = 1000"}, 1589 {"create table t (c int) min_rows = 1000", true, "CREATE TABLE `t` (`c` INT) MIN_ROWS = 1000"}, 1590 {"create table t (c int) min_rows 1000", true, "CREATE TABLE `t` (`c` INT) MIN_ROWS = 1000"}, 1591 {"create table t (c int) password = 'abc'", true, "CREATE TABLE `t` (`c` INT) PASSWORD = 'abc'"}, 1592 {"create table t (c int) password 'abc'", true, "CREATE TABLE `t` (`c` INT) PASSWORD = 'abc'"}, 1593 {"create table t (c int) DELAY_KEY_WRITE=1", true, "CREATE TABLE `t` (`c` INT) DELAY_KEY_WRITE = 1"}, 1594 {"create table t (c int) DELAY_KEY_WRITE 1", true, "CREATE TABLE `t` (`c` INT) DELAY_KEY_WRITE = 1"}, 1595 {"create table t (c int) ROW_FORMAT = default", true, "CREATE TABLE `t` (`c` INT) ROW_FORMAT = DEFAULT"}, 1596 {"create table t (c int) ROW_FORMAT default", true, "CREATE TABLE `t` (`c` INT) ROW_FORMAT = DEFAULT"}, 1597 {"create table t (c int) ROW_FORMAT = fixed", true, "CREATE TABLE `t` (`c` INT) ROW_FORMAT = FIXED"}, 1598 {"create table t (c int) ROW_FORMAT = compressed", true, "CREATE TABLE `t` (`c` INT) ROW_FORMAT = COMPRESSED"}, 1599 {"create table t (c int) ROW_FORMAT = compact", true, "CREATE TABLE `t` (`c` INT) ROW_FORMAT = COMPACT"}, 1600 {"create table t (c int) ROW_FORMAT = redundant", true, "CREATE TABLE `t` (`c` INT) ROW_FORMAT = REDUNDANT"}, 1601 {"create table t (c int) ROW_FORMAT = dynamic", true, "CREATE TABLE `t` (`c` INT) ROW_FORMAT = DYNAMIC"}, 1602 {"create table t (c int) STATS_PERSISTENT = default", true, "CREATE TABLE `t` (`c` INT) STATS_PERSISTENT = DEFAULT /* TableOptionStatsPersistent is not supported */ "}, 1603 {"create table t (c int) STATS_PERSISTENT = 0", true, "CREATE TABLE `t` (`c` INT) STATS_PERSISTENT = DEFAULT /* TableOptionStatsPersistent is not supported */ "}, 1604 {"create table t (c int) STATS_PERSISTENT = 1", true, "CREATE TABLE `t` (`c` INT) STATS_PERSISTENT = DEFAULT /* TableOptionStatsPersistent is not supported */ "}, 1605 {"create table t (c int) PACK_KEYS = 1", true, "CREATE TABLE `t` (`c` INT) PACK_KEYS = DEFAULT /* TableOptionPackKeys is not supported */ "}, 1606 {"create table t (c int) PACK_KEYS = 0", true, "CREATE TABLE `t` (`c` INT) PACK_KEYS = DEFAULT /* TableOptionPackKeys is not supported */ "}, 1607 {"create table t (c int) PACK_KEYS = DEFAULT", true, "CREATE TABLE `t` (`c` INT) PACK_KEYS = DEFAULT /* TableOptionPackKeys is not supported */ "}, 1608 {`create table testTableCompression (c VARCHAR(15000)) compression="ZLIB";`, true, "CREATE TABLE `testTableCompression` (`c` VARCHAR(15000)) COMPRESSION = 'ZLIB'"}, 1609 {`create table t1 (c1 int) compression="zlib";`, true, "CREATE TABLE `t1` (`c1` INT) COMPRESSION = 'zlib'"}, 1610 1611 // partition option 1612 {"CREATE TABLE t (id int) ENGINE = INNDB PARTITION BY RANGE (id) (PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20));", true, "CREATE TABLE `t` (`id` INT) ENGINE = INNDB PARTITION BY RANGE (`id`) (PARTITION `p0` VALUES LESS THAN (10),PARTITION `p1` VALUES LESS THAN (20))"}, 1613 {"create table t (c int) PARTITION BY HASH (c) PARTITIONS 32;", true, "CREATE TABLE `t` (`c` INT) PARTITION BY HASH (`c`) PARTITIONS 32"}, 1614 {"create table t (c int) PARTITION BY HASH (Year(VDate)) (PARTITION p1980 VALUES LESS THAN (1980) ENGINE = MyISAM, PARTITION p1990 VALUES LESS THAN (1990) ENGINE = MyISAM, PARTITION pothers VALUES LESS THAN MAXVALUE ENGINE = MyISAM)", false, ""}, 1615 {"create table t (c int) PARTITION BY RANGE (Year(VDate)) (PARTITION p1980 VALUES LESS THAN (1980) ENGINE = MyISAM, PARTITION p1990 VALUES LESS THAN (1990) ENGINE = MyISAM, PARTITION pothers VALUES LESS THAN MAXVALUE ENGINE = MyISAM)", true, "CREATE TABLE `t` (`c` INT) PARTITION BY RANGE (YEAR(`VDate`)) (PARTITION `p1980` VALUES LESS THAN (1980),PARTITION `p1990` VALUES LESS THAN (1990),PARTITION `pothers` VALUES LESS THAN (MAXVALUE))"}, 1616 {"create table t (c int, `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '') PARTITION BY RANGE (UNIX_TIMESTAMP(create_time)) (PARTITION p201610 VALUES LESS THAN(1477929600), PARTITION p201611 VALUES LESS THAN(1480521600),PARTITION p201612 VALUES LESS THAN(1483200000),PARTITION p201701 VALUES LESS THAN(1485878400),PARTITION p201702 VALUES LESS THAN(1488297600),PARTITION p201703 VALUES LESS THAN(1490976000))", true, "CREATE TABLE `t` (`c` INT,`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() COMMENT '') PARTITION BY RANGE (UNIX_TIMESTAMP(`create_time`)) (PARTITION `p201610` VALUES LESS THAN (1477929600),PARTITION `p201611` VALUES LESS THAN (1480521600),PARTITION `p201612` VALUES LESS THAN (1483200000),PARTITION `p201701` VALUES LESS THAN (1485878400),PARTITION `p201702` VALUES LESS THAN (1488297600),PARTITION `p201703` VALUES LESS THAN (1490976000))"}, 1617 {"CREATE TABLE `md_product_shop` (`shopCode` varchar(4) DEFAULT NULL COMMENT '地点') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 /*!50100 PARTITION BY KEY (shopCode) PARTITIONS 19 */;", true, "CREATE TABLE `md_product_shop` (`shopCode` VARCHAR(4) DEFAULT NULL COMMENT '地点') ENGINE = InnoDB DEFAULT CHARACTER SET = UTF8MB4"}, 1618 {"CREATE TABLE `payinfo1` (`id` bigint(20) NOT NULL AUTO_INCREMENT, `oderTime` datetime NOT NULL) ENGINE=InnoDB AUTO_INCREMENT=641533032 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 /*!50500 PARTITION BY RANGE COLUMNS(oderTime) (PARTITION P2011 VALUES LESS THAN ('2012-01-01 00:00:00') ENGINE = InnoDB, PARTITION P1201 VALUES LESS THAN ('2012-02-01 00:00:00') ENGINE = InnoDB, PARTITION PMAX VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB)*/;", true, "CREATE TABLE `payinfo1` (`id` BIGINT(20) NOT NULL AUTO_INCREMENT,`oderTime` DATETIME NOT NULL) ENGINE = InnoDB AUTO_INCREMENT = 641533032 DEFAULT CHARACTER SET = UTF8 ROW_FORMAT = COMPRESSED KEY_BLOCK_SIZE = 8 PARTITION BY RANGE COLUMNS(`oderTime`) (PARTITION `P2011` VALUES LESS THAN ('2012-01-01 00:00:00'),PARTITION `P1201` VALUES LESS THAN ('2012-02-01 00:00:00'),PARTITION `PMAX` VALUES LESS THAN (MAXVALUE))"}, 1619 {`CREATE TABLE app_channel_daily_report (id bigint(20) NOT NULL AUTO_INCREMENT, app_version varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'default', gmt_create datetime NOT NULL COMMENT '创建时间', PRIMARY KEY (id)) ENGINE=InnoDB AUTO_INCREMENT=33703438 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 1620 /*!50100 PARTITION BY RANGE (month(gmt_create)-1) 1621 (PARTITION part0 VALUES LESS THAN (1) COMMENT = '1月份' ENGINE = InnoDB, 1622 PARTITION part1 VALUES LESS THAN (2) COMMENT = '2月份' ENGINE = InnoDB, 1623 PARTITION part2 VALUES LESS THAN (3) COMMENT = '3月份' ENGINE = InnoDB, 1624 PARTITION part3 VALUES LESS THAN (4) COMMENT = '4月份' ENGINE = InnoDB, 1625 PARTITION part4 VALUES LESS THAN (5) COMMENT = '5月份' ENGINE = InnoDB, 1626 PARTITION part5 VALUES LESS THAN (6) COMMENT = '6月份' ENGINE = InnoDB, 1627 PARTITION part6 VALUES LESS THAN (7) COMMENT = '7月份' ENGINE = InnoDB, 1628 PARTITION part7 VALUES LESS THAN (8) COMMENT = '8月份' ENGINE = InnoDB, 1629 PARTITION part8 VALUES LESS THAN (9) COMMENT = '9月份' ENGINE = InnoDB, 1630 PARTITION part9 VALUES LESS THAN (10) COMMENT = '10月份' ENGINE = InnoDB, 1631 PARTITION part10 VALUES LESS THAN (11) COMMENT = '11月份' ENGINE = InnoDB, 1632 PARTITION part11 VALUES LESS THAN (12) COMMENT = '12月份' ENGINE = InnoDB) */ ;`, true, "CREATE TABLE `app_channel_daily_report` (`id` BIGINT(20) NOT NULL AUTO_INCREMENT,`app_version` VARCHAR(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'default',`gmt_create` DATETIME NOT NULL COMMENT '创建时间',PRIMARY KEY(`id`)) ENGINE = InnoDB AUTO_INCREMENT = 33703438 DEFAULT CHARACTER SET = UTF8 DEFAULT COLLATE = UTF8_UNICODE_CI PARTITION BY RANGE (MONTH(`gmt_create`)-1) (PARTITION `part0` VALUES LESS THAN (1) COMMENT = '1月份',PARTITION `part1` VALUES LESS THAN (2) COMMENT = '2月份',PARTITION `part2` VALUES LESS THAN (3) COMMENT = '3月份',PARTITION `part3` VALUES LESS THAN (4) COMMENT = '4月份',PARTITION `part4` VALUES LESS THAN (5) COMMENT = '5月份',PARTITION `part5` VALUES LESS THAN (6) COMMENT = '6月份',PARTITION `part6` VALUES LESS THAN (7) COMMENT = '7月份',PARTITION `part7` VALUES LESS THAN (8) COMMENT = '8月份',PARTITION `part8` VALUES LESS THAN (9) COMMENT = '9月份',PARTITION `part9` VALUES LESS THAN (10) COMMENT = '10月份',PARTITION `part10` VALUES LESS THAN (11) COMMENT = '11月份',PARTITION `part11` VALUES LESS THAN (12) COMMENT = '12月份')"}, 1633 1634 // for check clause 1635 {"create table t (c1 bool, c2 bool, check (c1 in (0, 1)), check (c2 in (0, 1)))", true, "CREATE TABLE `t` (`c1` TINYINT(1),`c2` TINYINT(1))"}, //TODO: Check in ColumnOption, yacc is not implemented 1636 {"CREATE TABLE Customer (SD integer CHECK (SD > 0), First_Name varchar(30));", true, "CREATE TABLE `Customer` (`SD` INT ,`First_Name` VARCHAR(30))"}, //TODO: Check in ColumnOption, yacc is not implemented 1637 1638 {"create database xxx", true, "CREATE DATABASE `xxx`"}, 1639 {"create database if exists xxx", false, ""}, 1640 {"create database if not exists xxx", true, "CREATE DATABASE IF NOT EXISTS `xxx`"}, 1641 {"create schema xxx", true, "CREATE DATABASE `xxx`"}, 1642 {"create schema if exists xxx", false, ""}, 1643 {"create schema if not exists xxx", true, "CREATE DATABASE IF NOT EXISTS `xxx`"}, 1644 // for drop database/schema/table/view/stats 1645 {"drop database xxx", true, "DROP DATABASE `xxx`"}, 1646 {"drop database if exists xxx", true, "DROP DATABASE IF EXISTS `xxx`"}, 1647 {"drop database if not exists xxx", false, ""}, 1648 {"drop schema xxx", true, "DROP DATABASE `xxx`"}, 1649 {"drop schema if exists xxx", true, "DROP DATABASE IF EXISTS `xxx`"}, 1650 {"drop schema if not exists xxx", false, ""}, 1651 {"drop table", false, "DROP TABLE"}, 1652 {"drop table xxx", true, "DROP TABLE `xxx`"}, 1653 {"drop table xxx, yyy", true, "DROP TABLE `xxx`, `yyy`"}, 1654 {"drop tables xxx", true, "DROP TABLE `xxx`"}, 1655 {"drop tables xxx, yyy", true, "DROP TABLE `xxx`, `yyy`"}, 1656 {"drop table if exists xxx", true, "DROP TABLE IF EXISTS `xxx`"}, 1657 {"drop table if exists xxx, yyy", true, "DROP TABLE IF EXISTS `xxx`, `yyy`"}, 1658 {"drop table if not exists xxx", false, ""}, 1659 {"drop table xxx restrict", true, "DROP TABLE `xxx`"}, 1660 {"drop table xxx, yyy cascade", true, "DROP TABLE `xxx`, `yyy`"}, 1661 {"drop table if exists xxx restrict", true, "DROP TABLE IF EXISTS `xxx`"}, 1662 {"drop view", false, "DROP VIEW"}, 1663 {"drop view xxx", true, "DROP VIEW `xxx`"}, 1664 {"drop view xxx, yyy", true, "DROP VIEW `xxx`, `yyy`"}, 1665 {"drop view if exists xxx", true, "DROP VIEW IF EXISTS `xxx`"}, 1666 {"drop view if exists xxx, yyy", true, "DROP VIEW IF EXISTS `xxx`, `yyy`"}, 1667 {"drop stats t", true, "DROP STATS `t`"}, 1668 // for issue 974 1669 {`CREATE TABLE address ( 1670 id bigint(20) NOT NULL AUTO_INCREMENT, 1671 create_at datetime NOT NULL, 1672 deleted tinyint(1) NOT NULL, 1673 update_at datetime NOT NULL, 1674 version bigint(20) DEFAULT NULL, 1675 address varchar(128) NOT NULL, 1676 address_detail varchar(128) NOT NULL, 1677 cellphone varchar(16) NOT NULL, 1678 latitude double NOT NULL, 1679 longitude double NOT NULL, 1680 name varchar(16) NOT NULL, 1681 sex tinyint(1) NOT NULL, 1682 user_id bigint(20) NOT NULL, 1683 PRIMARY KEY (id), 1684 CONSTRAINT FK_7rod8a71yep5vxasb0ms3osbg FOREIGN KEY (user_id) REFERENCES waimaiqa.user (id), 1685 INDEX FK_7rod8a71yep5vxasb0ms3osbg (user_id) comment '' 1686 ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI ROW_FORMAT=COMPACT COMMENT='' CHECKSUM=0 DELAY_KEY_WRITE=0;`, true, "CREATE TABLE `address` (`id` BIGINT(20) NOT NULL AUTO_INCREMENT,`create_at` DATETIME NOT NULL,`deleted` TINYINT(1) NOT NULL,`update_at` DATETIME NOT NULL,`version` BIGINT(20) DEFAULT NULL,`address` VARCHAR(128) NOT NULL,`address_detail` VARCHAR(128) NOT NULL,`cellphone` VARCHAR(16) NOT NULL,`latitude` DOUBLE NOT NULL,`longitude` DOUBLE NOT NULL,`name` VARCHAR(16) NOT NULL,`sex` TINYINT(1) NOT NULL,`user_id` BIGINT(20) NOT NULL,PRIMARY KEY(`id`),CONSTRAINT `FK_7rod8a71yep5vxasb0ms3osbg` FOREIGN KEY (`user_id`) REFERENCES `waimaiqa`.`user`(`id`),INDEX `FK_7rod8a71yep5vxasb0ms3osbg`(`user_id`) ) ENGINE = InnoDB AUTO_INCREMENT = 30 DEFAULT CHARACTER SET = UTF8 DEFAULT COLLATE = UTF8_GENERAL_CI ROW_FORMAT = COMPACT COMMENT = '' CHECKSUM = 0 DELAY_KEY_WRITE = 0"}, 1687 // for issue 975 1688 {`CREATE TABLE test_data ( 1689 id bigint(20) NOT NULL AUTO_INCREMENT, 1690 create_at datetime NOT NULL, 1691 deleted tinyint(1) NOT NULL, 1692 update_at datetime NOT NULL, 1693 version bigint(20) DEFAULT NULL, 1694 address varchar(255) NOT NULL, 1695 amount decimal(19,2) DEFAULT NULL, 1696 charge_id varchar(32) DEFAULT NULL, 1697 paid_amount decimal(19,2) DEFAULT NULL, 1698 transaction_no varchar(64) DEFAULT NULL, 1699 wx_mp_app_id varchar(32) DEFAULT NULL, 1700 contacts varchar(50) DEFAULT NULL, 1701 deliver_fee decimal(19,2) DEFAULT NULL, 1702 deliver_info varchar(255) DEFAULT NULL, 1703 deliver_time varchar(255) DEFAULT NULL, 1704 description varchar(255) DEFAULT NULL, 1705 invoice varchar(255) DEFAULT NULL, 1706 order_from int(11) DEFAULT NULL, 1707 order_state int(11) NOT NULL, 1708 packing_fee decimal(19,2) DEFAULT NULL, 1709 payment_time datetime DEFAULT NULL, 1710 payment_type int(11) DEFAULT NULL, 1711 phone varchar(50) NOT NULL, 1712 store_employee_id bigint(20) DEFAULT NULL, 1713 store_id bigint(20) NOT NULL, 1714 user_id bigint(20) NOT NULL, 1715 payment_mode int(11) NOT NULL, 1716 current_latitude double NOT NULL, 1717 current_longitude double NOT NULL, 1718 address_latitude double NOT NULL, 1719 address_longitude double NOT NULL, 1720 PRIMARY KEY (id), 1721 CONSTRAINT food_order_ibfk_1 FOREIGN KEY (user_id) REFERENCES waimaiqa.user (id), 1722 CONSTRAINT food_order_ibfk_2 FOREIGN KEY (store_id) REFERENCES waimaiqa.store (id), 1723 CONSTRAINT food_order_ibfk_3 FOREIGN KEY (store_employee_id) REFERENCES waimaiqa.store_employee (id), 1724 UNIQUE FK_UNIQUE_charge_id USING BTREE (charge_id) comment '', 1725 INDEX FK_eqst2x1xisn3o0wbrlahnnqq8 USING BTREE (store_employee_id) comment '', 1726 INDEX FK_8jcmec4kb03f4dod0uqwm54o9 USING BTREE (store_id) comment '', 1727 INDEX FK_a3t0m9apja9jmrn60uab30pqd USING BTREE (user_id) comment '' 1728 ) ENGINE=InnoDB AUTO_INCREMENT=95 DEFAULT CHARACTER SET utf8 COLLATE UTF8_GENERAL_CI ROW_FORMAT=COMPACT COMMENT='' CHECKSUM=0 DELAY_KEY_WRITE=0;`, true, "CREATE TABLE `test_data` (`id` BIGINT(20) NOT NULL AUTO_INCREMENT,`create_at` DATETIME NOT NULL,`deleted` TINYINT(1) NOT NULL,`update_at` DATETIME NOT NULL,`version` BIGINT(20) DEFAULT NULL,`address` VARCHAR(255) NOT NULL,`amount` DECIMAL(19,2) DEFAULT NULL,`charge_id` VARCHAR(32) DEFAULT NULL,`paid_amount` DECIMAL(19,2) DEFAULT NULL,`transaction_no` VARCHAR(64) DEFAULT NULL,`wx_mp_app_id` VARCHAR(32) DEFAULT NULL,`contacts` VARCHAR(50) DEFAULT NULL,`deliver_fee` DECIMAL(19,2) DEFAULT NULL,`deliver_info` VARCHAR(255) DEFAULT NULL,`deliver_time` VARCHAR(255) DEFAULT NULL,`description` VARCHAR(255) DEFAULT NULL,`invoice` VARCHAR(255) DEFAULT NULL,`order_from` INT(11) DEFAULT NULL,`order_state` INT(11) NOT NULL,`packing_fee` DECIMAL(19,2) DEFAULT NULL,`payment_time` DATETIME DEFAULT NULL,`payment_type` INT(11) DEFAULT NULL,`phone` VARCHAR(50) NOT NULL,`store_employee_id` BIGINT(20) DEFAULT NULL,`store_id` BIGINT(20) NOT NULL,`user_id` BIGINT(20) NOT NULL,`payment_mode` INT(11) NOT NULL,`current_latitude` DOUBLE NOT NULL,`current_longitude` DOUBLE NOT NULL,`address_latitude` DOUBLE NOT NULL,`address_longitude` DOUBLE NOT NULL,PRIMARY KEY(`id`),CONSTRAINT `food_order_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `waimaiqa`.`user`(`id`),CONSTRAINT `food_order_ibfk_2` FOREIGN KEY (`store_id`) REFERENCES `waimaiqa`.`store`(`id`),CONSTRAINT `food_order_ibfk_3` FOREIGN KEY (`store_employee_id`) REFERENCES `waimaiqa`.`store_employee`(`id`),UNIQUE `FK_UNIQUE_charge_id`(`charge_id`) USING BTREE,INDEX `FK_eqst2x1xisn3o0wbrlahnnqq8`(`store_employee_id`) USING BTREE,INDEX `FK_8jcmec4kb03f4dod0uqwm54o9`(`store_id`) USING BTREE,INDEX `FK_a3t0m9apja9jmrn60uab30pqd`(`user_id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 95 DEFAULT CHARACTER SET = UTF8 DEFAULT COLLATE = UTF8_GENERAL_CI ROW_FORMAT = COMPACT COMMENT = '' CHECKSUM = 0 DELAY_KEY_WRITE = 0"}, 1729 {`create table t (c int KEY);`, true, "CREATE TABLE `t` (`c` INT PRIMARY KEY)"}, 1730 {`CREATE TABLE address ( 1731 id bigint(20) NOT NULL AUTO_INCREMENT, 1732 create_at datetime NOT NULL, 1733 deleted tinyint(1) NOT NULL, 1734 update_at datetime NOT NULL, 1735 version bigint(20) DEFAULT NULL, 1736 address varchar(128) NOT NULL, 1737 address_detail varchar(128) NOT NULL, 1738 cellphone varchar(16) NOT NULL, 1739 latitude double NOT NULL, 1740 longitude double NOT NULL, 1741 name varchar(16) NOT NULL, 1742 sex tinyint(1) NOT NULL, 1743 user_id bigint(20) NOT NULL, 1744 PRIMARY KEY (id), 1745 CONSTRAINT FK_7rod8a71yep5vxasb0ms3osbg FOREIGN KEY (user_id) REFERENCES waimaiqa.user (id) ON DELETE CASCADE ON UPDATE NO ACTION, 1746 INDEX FK_7rod8a71yep5vxasb0ms3osbg (user_id) comment '' 1747 ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARACTER SET utf8 COLLATE UTF8_GENERAL_CI ROW_FORMAT=COMPACT COMMENT='' CHECKSUM=0 DELAY_KEY_WRITE=0;`, true, "CREATE TABLE `address` (`id` BIGINT(20) NOT NULL AUTO_INCREMENT,`create_at` DATETIME NOT NULL,`deleted` TINYINT(1) NOT NULL,`update_at` DATETIME NOT NULL,`version` BIGINT(20) DEFAULT NULL,`address` VARCHAR(128) NOT NULL,`address_detail` VARCHAR(128) NOT NULL,`cellphone` VARCHAR(16) NOT NULL,`latitude` DOUBLE NOT NULL,`longitude` DOUBLE NOT NULL,`name` VARCHAR(16) NOT NULL,`sex` TINYINT(1) NOT NULL,`user_id` BIGINT(20) NOT NULL,PRIMARY KEY(`id`),CONSTRAINT `FK_7rod8a71yep5vxasb0ms3osbg` FOREIGN KEY (`user_id`) REFERENCES `waimaiqa`.`user`(`id`) ON DELETE CASCADE ON UPDATE NO ACTION,INDEX `FK_7rod8a71yep5vxasb0ms3osbg`(`user_id`) ) ENGINE = InnoDB AUTO_INCREMENT = 30 DEFAULT CHARACTER SET = UTF8 DEFAULT COLLATE = UTF8_GENERAL_CI ROW_FORMAT = COMPACT COMMENT = '' CHECKSUM = 0 DELAY_KEY_WRITE = 0"}, 1748 {"CREATE TABLE address (\r\nid bigint(20) NOT NULL AUTO_INCREMENT,\r\ncreate_at datetime NOT NULL,\r\ndeleted tinyint(1) NOT NULL,\r\nupdate_at datetime NOT NULL,\r\nversion bigint(20) DEFAULT NULL,\r\naddress varchar(128) NOT NULL,\r\naddress_detail varchar(128) NOT NULL,\r\ncellphone varchar(16) NOT NULL,\r\nlatitude double NOT NULL,\r\nlongitude double NOT NULL,\r\nname varchar(16) NOT NULL,\r\nsex tinyint(1) NOT NULL,\r\nuser_id bigint(20) NOT NULL,\r\nPRIMARY KEY (id),\r\nCONSTRAINT FK_7rod8a71yep5vxasb0ms3osbg FOREIGN KEY (user_id) REFERENCES waimaiqa.user (id) ON DELETE CASCADE ON UPDATE NO ACTION,\r\nINDEX FK_7rod8a71yep5vxasb0ms3osbg (user_id) comment ''\r\n) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ROW_FORMAT=COMPACT COMMENT='' CHECKSUM=0 DELAY_KEY_WRITE=0;", true, "CREATE TABLE `address` (`id` BIGINT(20) NOT NULL AUTO_INCREMENT,`create_at` DATETIME NOT NULL,`deleted` TINYINT(1) NOT NULL,`update_at` DATETIME NOT NULL,`version` BIGINT(20) DEFAULT NULL,`address` VARCHAR(128) NOT NULL,`address_detail` VARCHAR(128) NOT NULL,`cellphone` VARCHAR(16) NOT NULL,`latitude` DOUBLE NOT NULL,`longitude` DOUBLE NOT NULL,`name` VARCHAR(16) NOT NULL,`sex` TINYINT(1) NOT NULL,`user_id` BIGINT(20) NOT NULL,PRIMARY KEY(`id`),CONSTRAINT `FK_7rod8a71yep5vxasb0ms3osbg` FOREIGN KEY (`user_id`) REFERENCES `waimaiqa`.`user`(`id`) ON DELETE CASCADE ON UPDATE NO ACTION,INDEX `FK_7rod8a71yep5vxasb0ms3osbg`(`user_id`) ) ENGINE = InnoDB AUTO_INCREMENT = 30 DEFAULT CHARACTER SET = UTF8 DEFAULT COLLATE = UTF8_GENERAL_CI ROW_FORMAT = COMPACT COMMENT = '' CHECKSUM = 0 DELAY_KEY_WRITE = 0"}, 1749 // for issue 1802 1750 {`CREATE TABLE t1 ( 1751 accout_id int(11) DEFAULT '0', 1752 summoner_id int(11) DEFAULT '0', 1753 union_name varbinary(52) NOT NULL, 1754 union_id int(11) DEFAULT '0', 1755 PRIMARY KEY (union_name)) ENGINE=MyISAM DEFAULT CHARSET=binary;`, true, "CREATE TABLE `t1` (`accout_id` INT(11) DEFAULT '0',`summoner_id` INT(11) DEFAULT '0',`union_name` VARBINARY(52) NOT NULL,`union_id` INT(11) DEFAULT '0',PRIMARY KEY(`union_name`)) ENGINE = MyISAM DEFAULT CHARACTER SET = BINARY"}, 1756 // Create table with multiple index options. 1757 {`create table t (c int, index ci (c) USING BTREE COMMENT "123");`, true, "CREATE TABLE `t` (`c` INT,INDEX `ci`(`c`) USING BTREE COMMENT '123')"}, 1758 // for default value 1759 {"CREATE TABLE sbtest (id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, k integer UNSIGNED DEFAULT '0' NOT NULL, c char(120) DEFAULT '' NOT NULL, pad char(60) DEFAULT '' NOT NULL, PRIMARY KEY (id) )", true, "CREATE TABLE `sbtest` (`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,`k` INT UNSIGNED DEFAULT '0' NOT NULL,`c` CHAR(120) DEFAULT '' NOT NULL,`pad` CHAR(60) DEFAULT '' NOT NULL,PRIMARY KEY(`id`))"}, 1760 {"create table test (create_date TIMESTAMP NOT NULL COMMENT '创建日期 create date' DEFAULT now());", true, "CREATE TABLE `test` (`create_date` TIMESTAMP NOT NULL COMMENT '创建日期 create date' DEFAULT CURRENT_TIMESTAMP())"}, 1761 {"create table ts (t int, v timestamp(3) default CURRENT_TIMESTAMP(3));", true, "CREATE TABLE `ts` (`t` INT,`v` TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3))"}, //TODO: The number yacc in parentheses has not been implemented yet. 1762 // Create table with primary key name. 1763 {"create table if not exists `t` (`id` int not null auto_increment comment '消息ID', primary key `pk_id` (`id`) );", true, "CREATE TABLE IF NOT EXISTS `t` (`id` INT NOT NULL AUTO_INCREMENT COMMENT '消息ID',PRIMARY KEY(`id`))"}, 1764 // Create table with like. 1765 {"create table a like b", true, "CREATE TABLE `a` LIKE `b`"}, 1766 {"create table a (like b)", true, "CREATE TABLE `a` LIKE `b`"}, 1767 {"create table if not exists a like b", true, "CREATE TABLE IF NOT EXISTS `a` LIKE `b`"}, 1768 {"create table if not exists a (like b)", true, "CREATE TABLE IF NOT EXISTS `a` LIKE `b`"}, 1769 {"create table if not exists a like (b)", false, ""}, 1770 {"create table a (t int) like b", false, ""}, 1771 {"create table a (t int) like (b)", false, ""}, 1772 // Create table with select statement 1773 {"create table a select * from b", true, "CREATE TABLE `a` AS SELECT * FROM `b`"}, 1774 {"create table a as select * from b", true, "CREATE TABLE `a` AS SELECT * FROM `b`"}, 1775 {"create table a (m int, n datetime) as select * from b", true, "CREATE TABLE `a` (`m` INT,`n` DATETIME) AS SELECT * FROM `b`"}, 1776 {"create table a (unique(n)) as select n from b", true, "CREATE TABLE `a` (UNIQUE(`n`)) AS SELECT `n` FROM `b`"}, 1777 {"create table a ignore as select n from b", true, "CREATE TABLE `a` IGNORE AS SELECT `n` FROM `b`"}, 1778 {"create table a replace as select n from b", true, "CREATE TABLE `a` REPLACE AS SELECT `n` FROM `b`"}, 1779 {"create table a (m int) replace as (select n as m from b union select n+1 as m from c group by 1 limit 2)", true, "CREATE TABLE `a` (`m` INT) REPLACE AS (SELECT `n` AS `m` FROM `b` UNION SELECT `n`+1 AS `m` FROM `c` GROUP BY 1 LIMIT 2)"}, 1780 1781 // Create table with no option is valid for parser 1782 {"create table a", true, "CREATE TABLE `a` "}, 1783 1784 {"create table t (a timestamp default now)", false, ""}, 1785 {"create table t (a timestamp default now())", true, "CREATE TABLE `t` (`a` TIMESTAMP DEFAULT CURRENT_TIMESTAMP())"}, 1786 {"create table t (a timestamp default now() on update now)", false, ""}, 1787 {"create table t (a timestamp default now() on update now())", true, "CREATE TABLE `t` (`a` TIMESTAMP DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP())"}, 1788 {"CREATE TABLE t (c TEXT) default CHARACTER SET utf8, default COLLATE utf8_general_ci;", true, "CREATE TABLE `t` (`c` TEXT) DEFAULT CHARACTER SET = UTF8 DEFAULT COLLATE = UTF8_GENERAL_CI"}, 1789 {"CREATE TABLE t (c TEXT) shard_row_id_bits = 1;", true, "CREATE TABLE `t` (`c` TEXT) SHARD_ROW_ID_BITS = 1"}, 1790 // Create table with ON UPDATE CURRENT_TIMESTAMP(6), specify fraction part. 1791 {"CREATE TABLE IF NOT EXISTS `general_log` (`event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),`user_host` mediumtext NOT NULL,`thread_id` bigint(20) unsigned NOT NULL,`server_id` int(10) unsigned NOT NULL,`command_type` varchar(64) NOT NULL,`argument` mediumblob NOT NULL) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'", true, "CREATE TABLE IF NOT EXISTS `general_log` (`event_time` TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(),`user_host` MEDIUMTEXT NOT NULL,`thread_id` BIGINT(20) UNSIGNED NOT NULL,`server_id` INT(10) UNSIGNED NOT NULL,`command_type` VARCHAR(64) NOT NULL,`argument` MEDIUMBLOB NOT NULL) ENGINE = CSV DEFAULT CHARACTER SET = UTF8 COMMENT = 'General log'"}, //TODO: The number yacc in parentheses has not been implemented yet. 1792 // For reference_definition in column_definition. 1793 {"CREATE TABLE followers ( f1 int NOT NULL REFERENCES user_profiles (uid) );", true, "CREATE TABLE `followers` (`f1` INT NOT NULL REFERENCES `user_profiles`(`uid`))"}, 1794 1795 // for alter table 1796 {"ALTER TABLE t ADD COLUMN (a SMALLINT UNSIGNED)", true, "ALTER TABLE `t` ADD COLUMN (`a` SMALLINT UNSIGNED)"}, 1797 {"ALTER TABLE ADD COLUMN (a SMALLINT UNSIGNED)", false, ""}, 1798 {"ALTER TABLE t ADD COLUMN (a SMALLINT UNSIGNED, b varchar(255))", true, "ALTER TABLE `t` ADD COLUMN (`a` SMALLINT UNSIGNED, `b` VARCHAR(255))"}, 1799 {"ALTER TABLE t ADD COLUMN (a SMALLINT UNSIGNED FIRST)", false, ""}, 1800 {"ALTER TABLE t ADD COLUMN a SMALLINT UNSIGNED", true, "ALTER TABLE `t` ADD COLUMN `a` SMALLINT UNSIGNED"}, 1801 {"ALTER TABLE t ADD COLUMN a SMALLINT UNSIGNED FIRST", true, "ALTER TABLE `t` ADD COLUMN `a` SMALLINT UNSIGNED FIRST"}, 1802 {"ALTER TABLE t ADD COLUMN a SMALLINT UNSIGNED AFTER b", true, "ALTER TABLE `t` ADD COLUMN `a` SMALLINT UNSIGNED AFTER `b`"}, 1803 {"ALTER TABLE employees ADD PARTITION", true, "ALTER TABLE `employees` ADD PARTITION"}, 1804 {"ALTER TABLE employees ADD PARTITION ( PARTITION P1 VALUES LESS THAN (2010))", true, "ALTER TABLE `employees` ADD PARTITION (PARTITION `P1` VALUES LESS THAN (2010))"}, 1805 {"ALTER TABLE employees ADD PARTITION ( PARTITION P2 VALUES LESS THAN MAXVALUE)", true, "ALTER TABLE `employees` ADD PARTITION (PARTITION `P2` VALUES LESS THAN (MAXVALUE))"}, 1806 {`ALTER TABLE employees ADD PARTITION ( 1807 PARTITION P1 VALUES LESS THAN (2010), 1808 PARTITION P2 VALUES LESS THAN (2015), 1809 PARTITION P3 VALUES LESS THAN MAXVALUE)`, true, "ALTER TABLE `employees` ADD PARTITION (PARTITION `P1` VALUES LESS THAN (2010), PARTITION `P2` VALUES LESS THAN (2015), PARTITION `P3` VALUES LESS THAN (MAXVALUE))"}, 1810 // For drop table partition statement. 1811 {"alter table t drop partition p1;", true, "ALTER TABLE `t` DROP PARTITION `p1`"}, 1812 {"alter table t drop partition p2;", true, "ALTER TABLE `t` DROP PARTITION `p2`"}, 1813 {"alter table employees add partition partitions 1;", true, "ALTER TABLE `employees` ADD PARTITION PARTITIONS 1"}, 1814 {"alter table employees add partition partitions 2;", true, "ALTER TABLE `employees` ADD PARTITION PARTITIONS 2"}, 1815 {"alter table clients coalesce partition 3;", true, "ALTER TABLE `clients` COALESCE PARTITION 3"}, 1816 {"alter table clients coalesce partition 4;", true, "ALTER TABLE `clients` COALESCE PARTITION 4"}, 1817 {"ALTER TABLE t DISABLE KEYS", true, "ALTER TABLE `t` /* AlterTableType(0) is not supported */ "}, 1818 {"ALTER TABLE t ENABLE KEYS", true, "ALTER TABLE `t` /* AlterTableType(0) is not supported */ "}, 1819 {"ALTER TABLE t MODIFY COLUMN a varchar(255)", true, "ALTER TABLE `t` MODIFY COLUMN `a` VARCHAR(255)"}, 1820 {"ALTER TABLE t CHANGE COLUMN a b varchar(255)", true, "ALTER TABLE `t` CHANGE COLUMN `a` `b` VARCHAR(255)"}, 1821 {"ALTER TABLE t CHANGE COLUMN a b varchar(255) CHARACTER SET UTF8 BINARY", true, "ALTER TABLE `t` CHANGE COLUMN `a` `b` VARCHAR(255) BINARY CHARACTER SET UTF8"}, 1822 {"ALTER TABLE t CHANGE COLUMN a b varchar(255) FIRST", true, "ALTER TABLE `t` CHANGE COLUMN `a` `b` VARCHAR(255) FIRST"}, 1823 {"ALTER TABLE db.t RENAME to db1.t1", true, "ALTER TABLE `db`.`t` RENAME AS `db1`.`t1`"}, 1824 {"ALTER TABLE db.t RENAME db1.t1", true, "ALTER TABLE `db`.`t` RENAME AS `db1`.`t1`"}, 1825 {"ALTER TABLE t RENAME as t1", true, "ALTER TABLE `t` RENAME AS `t1`"}, 1826 {"ALTER TABLE t ALTER COLUMN a SET DEFAULT 1", true, "ALTER TABLE `t` ALTER COLUMN `a` SET DEFAULT 1"}, 1827 {"ALTER TABLE t ALTER a SET DEFAULT 1", true, "ALTER TABLE `t` ALTER COLUMN `a` SET DEFAULT 1"}, 1828 {"ALTER TABLE t ALTER COLUMN a SET DEFAULT CURRENT_TIMESTAMP", false, ""}, 1829 {"ALTER TABLE t ALTER COLUMN a SET DEFAULT NOW()", false, ""}, 1830 {"ALTER TABLE t ALTER COLUMN a SET DEFAULT 1+1", false, ""}, 1831 {"ALTER TABLE t ALTER COLUMN a DROP DEFAULT", true, "ALTER TABLE `t` ALTER COLUMN `a` DROP DEFAULT"}, 1832 {"ALTER TABLE t ALTER a DROP DEFAULT", true, "ALTER TABLE `t` ALTER COLUMN `a` DROP DEFAULT"}, 1833 {"ALTER TABLE t ADD COLUMN a SMALLINT UNSIGNED, lock=none", true, "ALTER TABLE `t` ADD COLUMN `a` SMALLINT UNSIGNED, LOCK = NONE"}, 1834 {"ALTER TABLE t ADD COLUMN a SMALLINT UNSIGNED, lock=default", true, "ALTER TABLE `t` ADD COLUMN `a` SMALLINT UNSIGNED, LOCK = DEFAULT"}, 1835 {"ALTER TABLE t ADD COLUMN a SMALLINT UNSIGNED, lock=shared", true, "ALTER TABLE `t` ADD COLUMN `a` SMALLINT UNSIGNED, LOCK = SHARED"}, 1836 {"ALTER TABLE t ADD COLUMN a SMALLINT UNSIGNED, lock=exclusive", true, "ALTER TABLE `t` ADD COLUMN `a` SMALLINT UNSIGNED, LOCK = EXCLUSIVE"}, 1837 {"ALTER TABLE t ADD COLUMN a SMALLINT UNSIGNED, LOCK=NONE", true, "ALTER TABLE `t` ADD COLUMN `a` SMALLINT UNSIGNED, LOCK = NONE"}, 1838 {"ALTER TABLE t ADD COLUMN a SMALLINT UNSIGNED, LOCK=DEFAULT", true, "ALTER TABLE `t` ADD COLUMN `a` SMALLINT UNSIGNED, LOCK = DEFAULT"}, 1839 {"ALTER TABLE t ADD COLUMN a SMALLINT UNSIGNED, LOCK=SHARED", true, "ALTER TABLE `t` ADD COLUMN `a` SMALLINT UNSIGNED, LOCK = SHARED"}, 1840 {"ALTER TABLE t ADD COLUMN a SMALLINT UNSIGNED, LOCK=EXCLUSIVE", true, "ALTER TABLE `t` ADD COLUMN `a` SMALLINT UNSIGNED, LOCK = EXCLUSIVE"}, 1841 {"ALTER TABLE t ADD FULLTEXT KEY `FullText` (`name` ASC)", true, "ALTER TABLE `t` ADD FULLTEXT `FullText`(`name`)"}, 1842 {"ALTER TABLE t ADD FULLTEXT `FullText` (`name` ASC)", true, "ALTER TABLE `t` ADD FULLTEXT `FullText`(`name`)"}, 1843 {"ALTER TABLE t ADD FULLTEXT INDEX `FullText` (`name` ASC)", true, "ALTER TABLE `t` ADD FULLTEXT `FullText`(`name`)"}, 1844 {"ALTER TABLE t ADD INDEX (a) USING BTREE COMMENT 'a'", true, "ALTER TABLE `t` ADD INDEX(`a`) USING BTREE COMMENT 'a'"}, 1845 {"ALTER TABLE t ADD KEY (a) USING HASH COMMENT 'a'", true, "ALTER TABLE `t` ADD INDEX(`a`) USING HASH COMMENT 'a'"}, 1846 {"ALTER TABLE t ADD PRIMARY KEY (a) COMMENT 'a'", true, "ALTER TABLE `t` ADD PRIMARY KEY(`a`) COMMENT 'a'"}, 1847 {"ALTER TABLE t ADD UNIQUE (a) COMMENT 'a'", true, "ALTER TABLE `t` ADD UNIQUE(`a`) COMMENT 'a'"}, 1848 {"ALTER TABLE t ADD UNIQUE KEY (a) COMMENT 'a'", true, "ALTER TABLE `t` ADD UNIQUE(`a`) COMMENT 'a'"}, 1849 {"ALTER TABLE t ADD UNIQUE INDEX (a) COMMENT 'a'", true, "ALTER TABLE `t` ADD UNIQUE(`a`) COMMENT 'a'"}, 1850 {"ALTER TABLE t ENGINE ''", true, "ALTER TABLE `t` ENGINE = ''"}, 1851 {"ALTER TABLE t ENGINE = ''", true, "ALTER TABLE `t` ENGINE = ''"}, 1852 {"ALTER TABLE t ENGINE = 'innodb'", true, "ALTER TABLE `t` ENGINE = innodb"}, 1853 {"ALTER TABLE t ENGINE = innodb", true, "ALTER TABLE `t` ENGINE = innodb"}, 1854 {"ALTER TABLE `db`.`t` ENGINE = ``", true, "ALTER TABLE `db`.`t` ENGINE = ''"}, 1855 {"ALTER TABLE t ADD COLUMN a SMALLINT UNSIGNED, ADD COLUMN a SMALLINT", true, "ALTER TABLE `t` ADD COLUMN `a` SMALLINT UNSIGNED, ADD COLUMN `a` SMALLINT"}, 1856 {"ALTER TABLE t ADD COLUMN a SMALLINT, ENGINE = '', default COLLATE = UTF8_GENERAL_CI", true, "ALTER TABLE `t` ADD COLUMN `a` SMALLINT, ENGINE = '', DEFAULT COLLATE = UTF8_GENERAL_CI"}, 1857 {"ALTER TABLE t ENGINE = '', COMMENT='', default COLLATE = UTF8_GENERAL_CI", true, "ALTER TABLE `t` ENGINE = '', COMMENT = '', DEFAULT COLLATE = UTF8_GENERAL_CI"}, 1858 {"ALTER TABLE t ENGINE = '', ADD COLUMN a SMALLINT", true, "ALTER TABLE `t` ENGINE = '', ADD COLUMN `a` SMALLINT"}, 1859 {"ALTER TABLE t default COLLATE = UTF8_GENERAL_CI, ENGINE = '', ADD COLUMN a SMALLINT", true, "ALTER TABLE `t` DEFAULT COLLATE = UTF8_GENERAL_CI, ENGINE = '', ADD COLUMN `a` SMALLINT"}, 1860 {"ALTER TABLE t shard_row_id_bits = 1", true, "ALTER TABLE `t` SHARD_ROW_ID_BITS = 1"}, 1861 {"ALTER TABLE t AUTO_INCREMENT 3", true, "ALTER TABLE `t` AUTO_INCREMENT = 3"}, 1862 {"ALTER TABLE t AUTO_INCREMENT = 3", true, "ALTER TABLE `t` AUTO_INCREMENT = 3"}, 1863 {"ALTER TABLE `hello-world@dev`.`User` ADD COLUMN `name` mediumtext CHARACTER SET UTF8MB4 COLLATE UTF8MB4_UNICODE_CI NOT NULL , ALGORITHM = DEFAULT;", true, "ALTER TABLE `hello-world@dev`.`User` ADD COLUMN `name` MEDIUMTEXT CHARACTER SET UTF8MB4 COLLATE UTF8MB4_UNICODE_CI NOT NULL, ALGORITHM = DEFAULT"}, 1864 {"ALTER TABLE `hello-world@dev`.`User` ADD COLUMN `name` mediumtext CHARACTER SET UTF8MB4 COLLATE UTF8MB4_UNICODE_CI NOT NULL , ALGORITHM = INPLACE;", true, "ALTER TABLE `hello-world@dev`.`User` ADD COLUMN `name` MEDIUMTEXT CHARACTER SET UTF8MB4 COLLATE UTF8MB4_UNICODE_CI NOT NULL, ALGORITHM = INPLACE"}, 1865 {"ALTER TABLE `hello-world@dev`.`User` ADD COLUMN `name` mediumtext CHARACTER SET UTF8MB4 COLLATE UTF8MB4_UNICODE_CI NOT NULL , ALGORITHM = COPY;", true, "ALTER TABLE `hello-world@dev`.`User` ADD COLUMN `name` MEDIUMTEXT CHARACTER SET UTF8MB4 COLLATE UTF8MB4_UNICODE_CI NOT NULL, ALGORITHM = COPY"}, 1866 {"ALTER TABLE `hello-world@dev`.`User` ADD COLUMN `name` MEDIUMTEXT CHARACTER SET UTF8MB4 COLLATE UTF8MB4_UNICODE_CI NOT NULL, ALGORITHM = INSTANT;", true, "ALTER TABLE `hello-world@dev`.`User` ADD COLUMN `name` MEDIUMTEXT CHARACTER SET UTF8MB4 COLLATE UTF8MB4_UNICODE_CI NOT NULL, ALGORITHM = INSTANT"}, 1867 {"ALTER TABLE t CONVERT TO CHARACTER SET UTF8;", true, "ALTER TABLE `t` DEFAULT CHARACTER SET = UTF8"}, 1868 {"ALTER TABLE t CONVERT TO CHARSET UTF8;", true, "ALTER TABLE `t` DEFAULT CHARACTER SET = UTF8"}, 1869 {"ALTER TABLE t CONVERT TO CHARACTER SET UTF8 COLLATE UTF8_BIN;", true, "ALTER TABLE `t` CONVERT TO CHARACTER SET UTF8 COLLATE UTF8_BIN"}, 1870 {"ALTER TABLE t CONVERT TO CHARSET UTF8 COLLATE UTF8_BIN;", true, "ALTER TABLE `t` CONVERT TO CHARACTER SET UTF8 COLLATE UTF8_BIN"}, 1871 {"ALTER TABLE t FORCE", true, "ALTER TABLE `t` FORCE /* AlterTableForce is not supported */ "}, 1872 {"ALTER TABLE t DROP INDEX;", false, "ALTER TABLE `t` DROP INDEX"}, 1873 {"ALTER TABLE t DROP COLUMN a CASCADE", true, "ALTER TABLE `t` DROP COLUMN `a`"}, 1874 {`ALTER TABLE testTableCompression COMPRESSION="LZ4";`, true, "ALTER TABLE `testTableCompression` COMPRESSION = 'LZ4'"}, 1875 {`ALTER TABLE t1 COMPRESSION="zlib";`, true, "ALTER TABLE `t1` COMPRESSION = 'zlib'"}, 1876 1877 // For #6405 1878 {"ALTER TABLE t RENAME KEY a TO b;", true, "ALTER TABLE `t` RENAME INDEX `a` TO `b`"}, 1879 {"ALTER TABLE t RENAME INDEX a TO b;", true, "ALTER TABLE `t` RENAME INDEX `a` TO `b`"}, 1880 1881 {"alter table t analyze partition a", true, "ANALYZE TABLE `t` PARTITION `a`"}, 1882 {"alter table t analyze partition a with 4 buckets", true, "ANALYZE TABLE `t` PARTITION `a` WITH 4 BUCKETS"}, 1883 {"alter table t analyze partition a index b", true, "ANALYZE TABLE `t` PARTITION `a` INDEX `b`"}, 1884 {"alter table t analyze partition a index b with 4 buckets", true, "ANALYZE TABLE `t` PARTITION `a` INDEX `b` WITH 4 BUCKETS"}, 1885 1886 // For create index statement 1887 {"CREATE INDEX idx ON t (a)", true, "CREATE INDEX `idx` ON `t` (`a`)"}, 1888 {"CREATE UNIQUE INDEX idx ON t (a)", true, "CREATE UNIQUE INDEX `idx` ON `t` (`a`)"}, 1889 {"CREATE INDEX idx ON t (a) USING HASH", true, "CREATE INDEX `idx` ON `t` (`a`) USING HASH"}, 1890 {"CREATE INDEX idx ON t (a) COMMENT 'foo'", true, "CREATE INDEX `idx` ON `t` (`a`) COMMENT 'foo'"}, 1891 {"CREATE INDEX idx ON t (a) USING HASH COMMENT 'foo'", true, "CREATE INDEX `idx` ON `t` (`a`) USING HASH COMMENT 'foo'"}, 1892 {"CREATE INDEX idx ON t (a) LOCK=NONE", true, "CREATE INDEX `idx` ON `t` (`a`)"}, 1893 {"CREATE INDEX idx USING BTREE ON t (a) USING HASH COMMENT 'foo'", true, "CREATE INDEX `idx` ON `t` (`a`) USING HASH COMMENT 'foo'"}, 1894 {"CREATE INDEX idx USING BTREE ON t (a)", true, "CREATE INDEX `idx` ON `t` (`a`) USING BTREE"}, 1895 1896 //For dorp index statement 1897 {"drop index a on t", true, "DROP INDEX `a` ON `t`"}, 1898 {"drop index a on db.t", true, "DROP INDEX `a` ON `db`.`t`"}, 1899 {"drop index a on db.`tb-ttb`", true, "DROP INDEX `a` ON `db`.`tb-ttb`"}, 1900 {"drop index if exists a on t", true, "DROP INDEX IF EXISTS `a` ON `t`"}, 1901 {"drop index if exists a on db.t", true, "DROP INDEX IF EXISTS `a` ON `db`.`t`"}, 1902 {"drop index if exists a on db.`tb-ttb`", true, "DROP INDEX IF EXISTS `a` ON `db`.`tb-ttb`"}, 1903 1904 // for rename table statement 1905 {"RENAME TABLE t TO t1", true, "RENAME TABLE `t` TO `t1`"}, 1906 {"RENAME TABLE t t1", false, "RENAME TABLE `t` TO `t1`"}, 1907 {"RENAME TABLE d.t TO d1.t1", true, "RENAME TABLE `d`.`t` TO `d1`.`t1`"}, 1908 {"RENAME TABLE t1 TO t2, t3 TO t4", true, "RENAME TABLE `t1` TO `t2`, `t3` TO `t4`"}, 1909 1910 // for truncate statement 1911 {"TRUNCATE TABLE t1", true, "TRUNCATE TABLE `t1`"}, 1912 {"TRUNCATE t1", true, "TRUNCATE TABLE `t1`"}, 1913 1914 // for empty alert table index 1915 {"ALTER TABLE t ADD INDEX () ", false, ""}, 1916 {"ALTER TABLE t ADD UNIQUE ()", false, ""}, 1917 {"ALTER TABLE t ADD UNIQUE INDEX ()", false, ""}, 1918 {"ALTER TABLE t ADD UNIQUE KEY ()", false, ""}, 1919 1920 // for issue 4538 1921 {"create table a (process double)", true, "CREATE TABLE `a` (`process` DOUBLE)"}, 1922 1923 // for issue 4740 1924 {"create table t (a int1, b int2, c int3, d int4, e int8)", true, "CREATE TABLE `t` (`a` TINYINT,`b` SMALLINT,`c` MEDIUMINT,`d` INT,`e` BIGINT)"}, 1925 1926 // for issue 5918 1927 {"create table t (lv long varchar null)", true, "CREATE TABLE `t` (`lv` MEDIUMTEXT NULL)"}, 1928 1929 // special table name 1930 {"CREATE TABLE cdp_test.`test2-1` (id int(11) DEFAULT NULL,key(id));", true, "CREATE TABLE `cdp_test`.`test2-1` (`id` INT(11) DEFAULT NULL,INDEX(`id`))"}, 1931 {"CREATE TABLE miantiao (`扁豆焖面` INT(11));", true, "CREATE TABLE `miantiao` (`扁豆焖面` INT(11))"}, 1932 1933 // for create table select 1934 {"CREATE TABLE bar (m INT) SELECT n FROM foo;", true, "CREATE TABLE `bar` (`m` INT) AS SELECT `n` FROM `foo`"}, 1935 {"CREATE TABLE bar (m INT) IGNORE SELECT n FROM foo;", true, "CREATE TABLE `bar` (`m` INT) IGNORE AS SELECT `n` FROM `foo`"}, 1936 {"CREATE TABLE bar (m INT) REPLACE SELECT n FROM foo;", true, "CREATE TABLE `bar` (`m` INT) REPLACE AS SELECT `n` FROM `foo`"}, 1937 } 1938 s.RunTest(c, table) 1939 } 1940 1941 func (s *testParserSuite) TestHintError(c *C) { 1942 parser := New() 1943 stmt, warns, err := parser.Parse("select /*+ tidb_unknow(T1,t2) */ c1, c2 from t1, t2 where t1.c1 = t2.c1", "", "") 1944 c.Assert(err, IsNil) 1945 c.Assert(len(warns), Equals, 1) 1946 c.Assert(warns[0].Error(), Equals, "line 1 column 32 near \"tidb_unknow(T1,t2) */ c1, c2 from t1, t2 where t1.c1 = t2.c1\" ") 1947 c.Assert(len(stmt[0].(*ast.SelectStmt).TableHints), Equals, 0) 1948 stmt, warns, err = parser.Parse("select /*+ tidb_unknow(T1,t2, 1) TIDB_INLJ(t1, T2) */ c1, c2 from t1, t2 where t1.c1 = t2.c1", "", "") 1949 c.Assert(len(stmt[0].(*ast.SelectStmt).TableHints), Equals, 0) 1950 c.Assert(err, IsNil) 1951 c.Assert(len(warns), Equals, 1) 1952 c.Assert(warns[0].Error(), Equals, "line 1 column 53 near \"tidb_unknow(T1,t2, 1) TIDB_INLJ(t1, T2) */ c1, c2 from t1, t2 where t1.c1 = t2.c1\" ") 1953 stmt, _, err = parser.Parse("select c1, c2 from /*+ tidb_unknow(T1,t2) */ t1, t2 where t1.c1 = t2.c1", "", "") 1954 c.Assert(err, NotNil) 1955 stmt, _, err = parser.Parse("select1 /*+ TIDB_INLJ(t1, T2) */ c1, c2 from t1, t2 where t1.c1 = t2.c1", "", "") 1956 c.Assert(err, NotNil) 1957 c.Assert(err.Error(), Equals, "line 1 column 7 near \"select1 /*+ TIDB_INLJ(t1, T2) */ c1, c2 from t1, t2 where t1.c1 = t2.c1\" ") 1958 stmt, _, err = parser.Parse("select /*+ TIDB_INLJ(t1, T2) */ c1, c2 fromt t1, t2 where t1.c1 = t2.c1", "", "") 1959 c.Assert(err, NotNil) 1960 c.Assert(err.Error(), Equals, "line 1 column 47 near \"t1, t2 where t1.c1 = t2.c1\" ") 1961 _, _, err = parser.Parse("SELECT 1 FROM DUAL WHERE 1 IN (SELECT /*+ DEBUG_HINT3 */ 1)", "", "") 1962 c.Assert(err, IsNil) 1963 } 1964 1965 func (s *testParserSuite) TestErrorMsg(c *C) { 1966 parser := New() 1967 _, _, err := parser.Parse("select1 1", "", "") 1968 c.Assert(err.Error(), Equals, "line 1 column 7 near \"select1 1\" ") 1969 _, _, err = parser.Parse("select 1 from1 dual", "", "") 1970 c.Assert(err.Error(), Equals, "line 1 column 19 near \"dual\" ") 1971 _, _, err = parser.Parse("select * from t1 join t2 from t1.a = t2.a;", "", "") 1972 c.Assert(err.Error(), Equals, "line 1 column 29 near \"from t1.a = t2.a;\" ") 1973 _, _, err = parser.Parse("select * from t1 join t2 one t1.a = t2.a;", "", "") 1974 c.Assert(err.Error(), Equals, "line 1 column 31 near \"t1.a = t2.a;\" ") 1975 _, _, err = parser.Parse("select * from t1 join t2 on t1.a >>> t2.a;", "", "") 1976 c.Assert(err.Error(), Equals, "line 1 column 36 near \"> t2.a;\" ") 1977 } 1978 1979 func (s *testParserSuite) TestOptimizerHints(c *C) { 1980 parser := New() 1981 stmt, _, err := parser.Parse("select /*+ tidb_SMJ(T1,t2) tidb_smj(T3,t4) */ c1, c2 from t1, t2 where t1.c1 = t2.c1", "", "") 1982 c.Assert(err, IsNil) 1983 selectStmt := stmt[0].(*ast.SelectStmt) 1984 1985 hints := selectStmt.TableHints 1986 c.Assert(len(hints), Equals, 2) 1987 c.Assert(hints[0].HintName.L, Equals, "tidb_smj") 1988 c.Assert(len(hints[0].Tables), Equals, 2) 1989 c.Assert(hints[0].Tables[0].L, Equals, "t1") 1990 c.Assert(hints[0].Tables[1].L, Equals, "t2") 1991 1992 c.Assert(hints[1].HintName.L, Equals, "tidb_smj") 1993 c.Assert(hints[1].Tables[0].L, Equals, "t3") 1994 c.Assert(hints[1].Tables[1].L, Equals, "t4") 1995 1996 c.Assert(len(selectStmt.TableHints), Equals, 2) 1997 1998 stmt, _, err = parser.Parse("select /*+ TIDB_INLJ(t1, T2) tidb_inlj(t3, t4) */ c1, c2 from t1, t2 where t1.c1 = t2.c1", "", "") 1999 c.Assert(err, IsNil) 2000 selectStmt = stmt[0].(*ast.SelectStmt) 2001 2002 hints = selectStmt.TableHints 2003 c.Assert(len(hints), Equals, 2) 2004 c.Assert(hints[0].HintName.L, Equals, "tidb_inlj") 2005 c.Assert(len(hints[0].Tables), Equals, 2) 2006 c.Assert(hints[0].Tables[0].L, Equals, "t1") 2007 c.Assert(hints[0].Tables[1].L, Equals, "t2") 2008 2009 c.Assert(hints[1].HintName.L, Equals, "tidb_inlj") 2010 c.Assert(hints[1].Tables[0].L, Equals, "t3") 2011 c.Assert(hints[1].Tables[1].L, Equals, "t4") 2012 2013 stmt, _, err = parser.Parse("select /*+ TIDB_HJ(t1, T2) tidb_hj(t3, t4) */ c1, c2 from t1, t2 where t1.c1 = t2.c1", "", "") 2014 c.Assert(err, IsNil) 2015 selectStmt = stmt[0].(*ast.SelectStmt) 2016 2017 hints = selectStmt.TableHints 2018 c.Assert(len(hints), Equals, 2) 2019 c.Assert(hints[0].HintName.L, Equals, "tidb_hj") 2020 c.Assert(len(hints[0].Tables), Equals, 2) 2021 c.Assert(hints[0].Tables[0].L, Equals, "t1") 2022 c.Assert(hints[0].Tables[1].L, Equals, "t2") 2023 2024 c.Assert(hints[1].HintName.L, Equals, "tidb_hj") 2025 c.Assert(hints[1].Tables[0].L, Equals, "t3") 2026 c.Assert(hints[1].Tables[1].L, Equals, "t4") 2027 2028 stmt, _, err = parser.Parse("SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM t1 INNER JOIN t2 where t1.c1 = t2.c1", "", "") 2029 c.Assert(err, IsNil) 2030 selectStmt = stmt[0].(*ast.SelectStmt) 2031 hints = selectStmt.TableHints 2032 c.Assert(len(hints), Equals, 1) 2033 c.Assert(hints[0].HintName.L, Equals, "max_execution_time") 2034 c.Assert(hints[0].MaxExecutionTime, Equals, uint64(1000)) 2035 } 2036 2037 func (s *testParserSuite) TestType(c *C) { 2038 table := []testCase{ 2039 // for time fsp 2040 {"CREATE TABLE t( c1 TIME(2), c2 DATETIME(2), c3 TIMESTAMP(2) );", true, "CREATE TABLE `t` (`c1` TIME(2),`c2` DATETIME(2),`c3` TIMESTAMP(2))"}, 2041 2042 // for hexadecimal 2043 {"select x'0a', X'11', 0x11", true, "SELECT x'0a',x'11',x'11'"}, 2044 {"select x'13181C76734725455A'", true, "SELECT x'13181c76734725455a'"}, 2045 {"select x'0xaa'", false, ""}, 2046 {"select 0X11", false, ""}, 2047 {"select 0x4920616D2061206C6F6E672068657820737472696E67", true, "SELECT x'4920616d2061206c6f6e672068657820737472696e67'"}, 2048 2049 // for bit 2050 {"select 0b01, 0b0, b'11', B'11'", true, "SELECT b'1',b'0',b'11',b'11'"}, 2051 // 0B01 and 0b21 are identifiers, the following two statement could parse. 2052 // {"select 0B01", false, ""}, 2053 // {"select 0b21", false, ""}, 2054 2055 // for enum and set type 2056 {"create table t (c1 enum('a', 'b'), c2 set('a', 'b'))", true, "CREATE TABLE `t` (`c1` ENUM('a','b'),`c2` SET('a','b'))"}, 2057 {"create table t (c1 enum)", false, ""}, 2058 {"create table t (c1 set)", false, ""}, 2059 2060 // for blob and text field length 2061 {"create table t (c1 blob(1024), c2 text(1024))", true, "CREATE TABLE `t` (`c1` BLOB(1024),`c2` TEXT(1024))"}, 2062 2063 // for year 2064 {"create table t (y year(4), y1 year)", true, "CREATE TABLE `t` (`y` YEAR(4),`y1` YEAR)"}, 2065 {"create table t (y year(4) unsigned zerofill zerofill, y1 year signed unsigned zerofill)", true, "CREATE TABLE `t` (`y` YEAR(4),`y1` YEAR)"}, 2066 2067 // for national 2068 {"create table t (c1 national char(2), c2 national varchar(2))", true, "CREATE TABLE `t` (`c1` CHAR(2),`c2` VARCHAR(2))"}, 2069 2070 // for json type 2071 {`create table t (a JSON);`, true, "CREATE TABLE `t` (`a` JSON)"}, 2072 } 2073 s.RunTest(c, table) 2074 } 2075 2076 func (s *testParserSuite) TestPrivilege(c *C) { 2077 table := []testCase{ 2078 // for create user 2079 {`CREATE USER 'test'`, true, "CREATE USER `test`@`%`"}, 2080 {`CREATE USER test`, true, "CREATE USER `test`@`%`"}, 2081 {"CREATE USER `test`", true, "CREATE USER `test`@`%`"}, 2082 {"CREATE USER test-user", false, ""}, 2083 {"CREATE USER test.user", false, ""}, 2084 {"CREATE USER 'test-user'", true, "CREATE USER `test-user`@`%`"}, 2085 {"CREATE USER `test-user`", true, "CREATE USER `test-user`@`%`"}, 2086 {"CREATE USER test.user", false, ""}, 2087 {"CREATE USER 'test.user'", true, "CREATE USER `test.user`@`%`"}, 2088 {"CREATE USER `test.user`", true, "CREATE USER `test.user`@`%`"}, 2089 {"CREATE USER uesr1@localhost", true, "CREATE USER `uesr1`@`localhost`"}, 2090 {"CREATE USER `uesr1`@localhost", true, "CREATE USER `uesr1`@`localhost`"}, 2091 {"CREATE USER uesr1@`localhost`", true, "CREATE USER `uesr1`@`localhost`"}, 2092 {"CREATE USER `uesr1`@`localhost`", true, "CREATE USER `uesr1`@`localhost`"}, 2093 {"CREATE USER 'uesr1'@localhost", true, "CREATE USER `uesr1`@`localhost`"}, 2094 {"CREATE USER uesr1@'localhost'", true, "CREATE USER `uesr1`@`localhost`"}, 2095 {"CREATE USER 'uesr1'@'localhost'", true, "CREATE USER `uesr1`@`localhost`"}, 2096 {"CREATE USER 'uesr1'@`localhost`", true, "CREATE USER `uesr1`@`localhost`"}, 2097 {"CREATE USER `uesr1`@'localhost'", true, "CREATE USER `uesr1`@`localhost`"}, 2098 {"CREATE ROLE `test-role`, `role1`@'localhost'", true, "CREATE ROLE `test-role`@`%`, `role1`@`localhost`"}, 2099 {"CREATE ROLE `test-role`", true, "CREATE ROLE `test-role`@`%`"}, 2100 {"CREATE ROLE role1", true, "CREATE ROLE `role1`@`%`"}, 2101 {"CREATE ROLE `role1`@'localhost'", true, "CREATE ROLE `role1`@`localhost`"}, 2102 {"create user 'bug19354014user'@'%' identified WITH mysql_native_password", true, "CREATE USER `bug19354014user`@`%`"}, 2103 {"create user 'bug19354014user'@'%' identified WITH mysql_native_password by 'new-password'", true, "CREATE USER `bug19354014user`@`%` IDENTIFIED BY 'new-password'"}, 2104 {"create user 'bug19354014user'@'%' identified WITH mysql_native_password as 'hashstring'", true, "CREATE USER `bug19354014user`@`%` IDENTIFIED BY PASSWORD 'hashstring'"}, 2105 {`CREATE USER IF NOT EXISTS 'root'@'localhost' IDENTIFIED BY 'new-password'`, true, "CREATE USER IF NOT EXISTS `root`@`localhost` IDENTIFIED BY 'new-password'"}, 2106 {`CREATE USER 'root'@'localhost' IDENTIFIED BY 'new-password'`, true, "CREATE USER `root`@`localhost` IDENTIFIED BY 'new-password'"}, 2107 {`CREATE USER 'root'@'localhost' IDENTIFIED BY PASSWORD 'hashstring'`, true, "CREATE USER `root`@`localhost` IDENTIFIED BY PASSWORD 'hashstring'"}, 2108 {`CREATE USER 'root'@'localhost' IDENTIFIED BY 'new-password', 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD 'hashstring'`, true, "CREATE USER `root`@`localhost` IDENTIFIED BY 'new-password', `root`@`127.0.0.1` IDENTIFIED BY PASSWORD 'hashstring'"}, 2109 {`ALTER USER IF EXISTS 'root'@'localhost' IDENTIFIED BY 'new-password'`, true, "ALTER USER IF EXISTS `root`@`localhost` IDENTIFIED BY 'new-password'"}, 2110 {`ALTER USER 'root'@'localhost' IDENTIFIED BY 'new-password'`, true, "ALTER USER `root`@`localhost` IDENTIFIED BY 'new-password'"}, 2111 {`ALTER USER 'root'@'localhost' IDENTIFIED BY PASSWORD 'hashstring'`, true, "ALTER USER `root`@`localhost` IDENTIFIED BY PASSWORD 'hashstring'"}, 2112 {`ALTER USER 'root'@'localhost' IDENTIFIED BY 'new-password', 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD 'hashstring'`, true, "ALTER USER `root`@`localhost` IDENTIFIED BY 'new-password', `root`@`127.0.0.1` IDENTIFIED BY PASSWORD 'hashstring'"}, 2113 {`ALTER USER USER() IDENTIFIED BY 'new-password'`, true, "ALTER USER USER() IDENTIFIED BY 'new-password'"}, 2114 {`ALTER USER IF EXISTS USER() IDENTIFIED BY 'new-password'`, true, "ALTER USER IF EXISTS USER() IDENTIFIED BY 'new-password'"}, 2115 {`DROP USER 'root'@'localhost', 'root1'@'localhost'`, true, "DROP USER `root`@`localhost`, `root1`@`localhost`"}, 2116 {`DROP USER IF EXISTS 'root'@'localhost'`, true, "DROP USER IF EXISTS `root`@`localhost`"}, 2117 {`DROP ROLE 'role'@'localhost', 'role1'@'localhost'`, true, ""}, 2118 {`DROP ROLE 'administrator', 'developer';`, true, ""}, 2119 {`DROP ROLE IF EXISTS 'role'@'localhost'`, true, ""}, 2120 2121 // for grant statement 2122 {"GRANT ALL ON db1.* TO 'jeffrey'@'localhost';", true, "GRANT ALL ON `db1`.* TO `jeffrey`@`localhost`"}, 2123 {"GRANT ALL ON TABLE db1.* TO 'jeffrey'@'localhost';", true, "GRANT ALL ON TABLE `db1`.* TO `jeffrey`@`localhost`"}, 2124 {"GRANT ALL ON db1.* TO 'jeffrey'@'localhost' WITH GRANT OPTION;", true, "GRANT ALL ON `db1`.* TO `jeffrey`@`localhost` WITH GRANT OPTION"}, 2125 {"GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';", true, "GRANT SELECT ON `db2`.`invoice` TO `jeffrey`@`localhost`"}, 2126 {"GRANT ALL ON *.* TO 'someuser'@'somehost';", true, "GRANT ALL ON *.* TO `someuser`@`somehost`"}, 2127 {"GRANT SELECT, INSERT ON *.* TO 'someuser'@'somehost';", true, "GRANT SELECT, INSERT ON *.* TO `someuser`@`somehost`"}, 2128 {"GRANT ALL ON mydb.* TO 'someuser'@'somehost';", true, "GRANT ALL ON `mydb`.* TO `someuser`@`somehost`"}, 2129 {"GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';", true, "GRANT SELECT, INSERT ON `mydb`.* TO `someuser`@`somehost`"}, 2130 {"GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost';", true, "GRANT ALL ON `mydb`.`mytbl` TO `someuser`@`somehost`"}, 2131 {"GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';", true, "GRANT SELECT, INSERT ON `mydb`.`mytbl` TO `someuser`@`somehost`"}, 2132 {"GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';", true, "GRANT SELECT (`col1`), INSERT (`col1`,`col2`) ON `mydb`.`mytbl` TO `someuser`@`somehost`"}, 2133 {"grant all privileges on zabbix.* to 'zabbix'@'localhost' identified by 'password';", true, "GRANT ALL ON `zabbix`.* TO `zabbix`@`localhost` IDENTIFIED BY 'password'"}, 2134 {"GRANT SELECT ON test.* to 'test'", true, "GRANT SELECT ON `test`.* TO `test`@`%`"}, // For issue 2654. 2135 {"grant PROCESS,usage, REPLICATION SLAVE, REPLICATION CLIENT on *.* to 'xxxxxxxxxx'@'%' identified by password 'xxxxxxxxxxxxxxxxxxxxxxxxxxxx'", true, "GRANT PROCESS /* UNSUPPORTED TYPE */ /* UNSUPPORTED TYPE */ /* UNSUPPORTED TYPE */ ON *.* TO `xxxxxxxxxx`@`%` IDENTIFIED BY PASSWORD 'xxxxxxxxxxxxxxxxxxxxxxxxxxxx'"}, // For issue 4865 2136 {"/* rds internal mark */ GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, RELOAD, PROCESS, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER on *.* to 'root2'@'%' identified by password '*sdsadsdsadssadsadsadsadsada' with grant option", true, "GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES /* UNSUPPORTED TYPE */, PROCESS, INDEX, ALTER /* UNSUPPORTED TYPE */ /* UNSUPPORTED TYPE */, EXECUTE /* UNSUPPORTED TYPE */ /* UNSUPPORTED TYPE */, CREATE VIEW, SHOW VIEW /* UNSUPPORTED TYPE */ /* UNSUPPORTED TYPE */, CREATE USER /* UNSUPPORTED TYPE */, TRIGGER ON *.* TO `root2`@`%` IDENTIFIED BY PASSWORD '*sdsadsdsadssadsadsadsadsada' WITH GRANT OPTION"}, 2137 {"GRANT 'role1', 'role2' TO 'user1'@'localhost', 'user2'@'localhost';", true, ""}, 2138 {"GRANT 'u1' TO 'u1';", true, ""}, 2139 {"GRANT 'app_developer' TO 'dev1'@'localhost';", true, ""}, 2140 2141 // for revoke statement 2142 {"REVOKE ALL ON db1.* FROM 'jeffrey'@'localhost';", true, "REVOKE ALL ON `db1`.* FROM `jeffrey`@`localhost`"}, 2143 {"REVOKE SELECT ON db2.invoice FROM 'jeffrey'@'localhost';", true, "REVOKE SELECT ON `db2`.`invoice` FROM `jeffrey`@`localhost`"}, 2144 {"REVOKE ALL ON *.* FROM 'someuser'@'somehost';", true, "REVOKE ALL ON *.* FROM `someuser`@`somehost`"}, 2145 {"REVOKE SELECT, INSERT ON *.* FROM 'someuser'@'somehost';", true, "REVOKE SELECT, INSERT ON *.* FROM `someuser`@`somehost`"}, 2146 {"REVOKE ALL ON mydb.* FROM 'someuser'@'somehost';", true, "REVOKE ALL ON `mydb`.* FROM `someuser`@`somehost`"}, 2147 {"REVOKE SELECT, INSERT ON mydb.* FROM 'someuser'@'somehost';", true, "REVOKE SELECT, INSERT ON `mydb`.* FROM `someuser`@`somehost`"}, 2148 {"REVOKE ALL ON mydb.mytbl FROM 'someuser'@'somehost';", true, "REVOKE ALL ON `mydb`.`mytbl` FROM `someuser`@`somehost`"}, 2149 {"REVOKE SELECT, INSERT ON mydb.mytbl FROM 'someuser'@'somehost';", true, "REVOKE SELECT, INSERT ON `mydb`.`mytbl` FROM `someuser`@`somehost`"}, 2150 {"REVOKE SELECT (col1), INSERT (col1,col2) ON mydb.mytbl FROM 'someuser'@'somehost';", true, "REVOKE SELECT (`col1`), INSERT (`col1`,`col2`) ON `mydb`.`mytbl` FROM `someuser`@`somehost`"}, 2151 {"REVOKE all privileges on zabbix.* FROM 'zabbix'@'localhost' identified by 'password';", true, "REVOKE ALL ON `zabbix`.* FROM `zabbix`@`localhost` IDENTIFIED BY 'password'"}, 2152 {"REVOKE 'role1', 'role2' FROM 'user1'@'localhost', 'user2'@'localhost';", true, ""}, 2153 } 2154 s.RunTest(c, table) 2155 } 2156 2157 func (s *testParserSuite) TestComment(c *C) { 2158 table := []testCase{ 2159 {"create table t (c int comment 'comment')", true, "CREATE TABLE `t` (`c` INT COMMENT 'comment')"}, 2160 {"create table t (c int) comment = 'comment'", true, "CREATE TABLE `t` (`c` INT) COMMENT = 'comment'"}, 2161 {"create table t (c int) comment 'comment'", true, "CREATE TABLE `t` (`c` INT) COMMENT = 'comment'"}, 2162 {"create table t (c int) comment comment", false, ""}, 2163 {"create table t (comment text)", true, "CREATE TABLE `t` (`comment` TEXT)"}, 2164 {"START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */", true, "START TRANSACTION"}, 2165 // for comment in query 2166 {"/*comment*/ /*comment*/ select c /* this is a comment */ from t;", true, "SELECT `c` FROM `t`"}, 2167 // for unclosed comment 2168 {"delete from t where a = 7 or 1=1/*' and b = 'p'", false, ""}, 2169 } 2170 s.RunTest(c, table) 2171 } 2172 2173 func (s *testParserSuite) TestCommentErrMsg(c *C) { 2174 table := []testErrMsgCase{ 2175 {"delete from t where a = 7 or 1=1/*' and b = 'p'", false, errors.New("near '/*' and b = 'p'' at line 1")}, 2176 {"delete from t where a = 7 or\n 1=1/*' and b = 'p'", false, errors.New("near '/*' and b = 'p'' at line 2")}, 2177 {"select 1/*", false, errors.New("near '/*' at line 1")}, 2178 {"select 1/* comment */", false, nil}, 2179 } 2180 s.RunErrMsgTest(c, table) 2181 } 2182 2183 type subqueryChecker struct { 2184 text string 2185 c *C 2186 } 2187 2188 // Enter implements ast.Visitor interface. 2189 func (sc *subqueryChecker) Enter(inNode ast.Node) (outNode ast.Node, skipChildren bool) { 2190 if expr, ok := inNode.(*ast.SubqueryExpr); ok { 2191 sc.c.Assert(expr.Query.Text(), Equals, sc.text) 2192 return inNode, true 2193 } 2194 return inNode, false 2195 } 2196 2197 // Leave implements ast.Visitor interface. 2198 func (sc *subqueryChecker) Leave(inNode ast.Node) (node ast.Node, ok bool) { 2199 return inNode, true 2200 } 2201 2202 func (s *testParserSuite) TestSubquery(c *C) { 2203 table := []testCase{ 2204 // for compare subquery 2205 {"SELECT 1 > (select 1)", true, "SELECT 1>(SELECT 1)"}, 2206 {"SELECT 1 > ANY (select 1)", true, "SELECT 1>ANY (SELECT 1)"}, 2207 {"SELECT 1 > ALL (select 1)", true, "SELECT 1>ALL (SELECT 1)"}, 2208 {"SELECT 1 > SOME (select 1)", true, "SELECT 1>ANY (SELECT 1)"}, 2209 2210 // for exists subquery 2211 {"SELECT EXISTS select 1", false, ""}, 2212 {"SELECT EXISTS (select 1)", true, "SELECT EXISTS (SELECT 1)"}, 2213 {"SELECT + EXISTS (select 1)", true, "SELECT +EXISTS (SELECT 1)"}, 2214 {"SELECT - EXISTS (select 1)", true, "SELECT -EXISTS (SELECT 1)"}, 2215 {"SELECT NOT EXISTS (select 1)", true, "SELECT NOT EXISTS (SELECT 1)"}, 2216 {"SELECT + NOT EXISTS (select 1)", false, ""}, 2217 {"SELECT - NOT EXISTS (select 1)", false, ""}, 2218 } 2219 s.RunTest(c, table) 2220 2221 tests := []struct { 2222 input string 2223 text string 2224 }{ 2225 {"SELECT 1 > (select 1)", "select 1"}, 2226 {"SELECT 1 > (select 1 union select 2)", "select 1 union select 2"}, 2227 } 2228 parser := New() 2229 for _, t := range tests { 2230 stmt, err := parser.ParseOneStmt(t.input, "", "") 2231 c.Assert(err, IsNil) 2232 stmt.Accept(&subqueryChecker{ 2233 text: t.text, 2234 c: c, 2235 }) 2236 } 2237 } 2238 func (s *testParserSuite) TestUnion(c *C) { 2239 table := []testCase{ 2240 {"select c1 from t1 union select c2 from t2", true, "SELECT `c1` FROM `t1` UNION SELECT `c2` FROM `t2`"}, 2241 {"select c1 from t1 union (select c2 from t2)", true, "SELECT `c1` FROM `t1` UNION (SELECT `c2` FROM `t2`)"}, 2242 {"select c1 from t1 union (select c2 from t2) order by c1", true, "SELECT `c1` FROM `t1` UNION (SELECT `c2` FROM `t2`) ORDER BY `c1`"}, 2243 {"select c1 from t1 union select c2 from t2 order by c2", true, "SELECT `c1` FROM `t1` UNION SELECT `c2` FROM `t2` ORDER BY `c2`"}, 2244 {"select c1 from t1 union (select c2 from t2) limit 1", true, "SELECT `c1` FROM `t1` UNION (SELECT `c2` FROM `t2`) LIMIT 1"}, 2245 {"select c1 from t1 union (select c2 from t2) limit 1, 1", true, "SELECT `c1` FROM `t1` UNION (SELECT `c2` FROM `t2`) LIMIT 1,1"}, 2246 {"select c1 from t1 union (select c2 from t2) order by c1 limit 1", true, "SELECT `c1` FROM `t1` UNION (SELECT `c2` FROM `t2`) ORDER BY `c1` LIMIT 1"}, 2247 {"(select c1 from t1) union distinct select c2 from t2", true, "(SELECT `c1` FROM `t1`) UNION SELECT `c2` FROM `t2`"}, 2248 {"(select c1 from t1) union distinctrow select c2 from t2", true, "(SELECT `c1` FROM `t1`) UNION SELECT `c2` FROM `t2`"}, 2249 {"(select c1 from t1) union all select c2 from t2", true, "(SELECT `c1` FROM `t1`) UNION ALL SELECT `c2` FROM `t2`"}, 2250 {"(select c1 from t1) union distinct all select c2 from t2", false, ""}, 2251 {"(select c1 from t1) union distinctrow all select c2 from t2", false, ""}, 2252 {"(select c1 from t1) union (select c2 from t2) order by c1 union select c3 from t3", false, ""}, 2253 {"(select c1 from t1) union (select c2 from t2) limit 1 union select c3 from t3", false, ""}, 2254 {"(select c1 from t1) union select c2 from t2 union (select c3 from t3) order by c1 limit 1", true, "(SELECT `c1` FROM `t1`) UNION SELECT `c2` FROM `t2` UNION (SELECT `c3` FROM `t3`) ORDER BY `c1` LIMIT 1"}, 2255 {"select (select 1 union select 1) as a", true, "SELECT (SELECT 1 UNION SELECT 1) AS `a`"}, 2256 {"select * from (select 1 union select 2) as a", true, "SELECT * FROM (SELECT 1 UNION SELECT 2) AS `a`"}, 2257 {"insert into t select c1 from t1 union select c2 from t2", true, "INSERT INTO `t` SELECT `c1` FROM `t1` UNION SELECT `c2` FROM `t2`"}, 2258 {"insert into t (c) select c1 from t1 union select c2 from t2", true, "INSERT INTO `t` (`c`) SELECT `c1` FROM `t1` UNION SELECT `c2` FROM `t2`"}, 2259 {"select 2 as a from dual union select 1 as b from dual order by a", true, "SELECT 2 AS `a` UNION SELECT 1 AS `b` ORDER BY `a`"}, 2260 } 2261 s.RunTest(c, table) 2262 } 2263 2264 func (s *testParserSuite) TestUnionOrderBy(c *C) { 2265 parser := New() 2266 parser.EnableWindowFunc(s.enableWindowFunc) 2267 2268 tests := []struct { 2269 src string 2270 hasOrderBy []bool 2271 }{ 2272 {"select 2 as a from dual union select 1 as b from dual order by a", []bool{false, false, true}}, 2273 {"select 2 as a from dual union (select 1 as b from dual order by a)", []bool{false, true, false}}, 2274 {"(select 2 as a from dual order by a) union select 1 as b from dual order by a", []bool{true, false, true}}, 2275 {"select 1 a, 2 b from dual order by a", []bool{true}}, 2276 {"select 1 a, 2 b from dual", []bool{false}}, 2277 } 2278 2279 for _, t := range tests { 2280 stmt, _, err := parser.Parse(t.src, "", "") 2281 c.Assert(err, IsNil) 2282 us, ok := stmt[0].(*ast.UnionStmt) 2283 if ok { 2284 var i int 2285 for _, s := range us.SelectList.Selects { 2286 c.Assert(s.OrderBy != nil, Equals, t.hasOrderBy[i]) 2287 i++ 2288 } 2289 c.Assert(us.OrderBy != nil, Equals, t.hasOrderBy[i]) 2290 } 2291 ss, ok := stmt[0].(*ast.SelectStmt) 2292 if ok { 2293 c.Assert(ss.OrderBy != nil, Equals, t.hasOrderBy[0]) 2294 } 2295 } 2296 } 2297 2298 func (s *testParserSuite) TestLikeEscape(c *C) { 2299 table := []testCase{ 2300 // for like escape 2301 {`select "abc_" like "abc\\_" escape ''`, true, "SELECT 'abc_' LIKE 'abc\\_'"}, 2302 {`select "abc_" like "abc\\_" escape '\\'`, true, "SELECT 'abc_' LIKE 'abc\\_'"}, 2303 {`select "abc_" like "abc\\_" escape '||'`, false, ""}, 2304 {`select "abc" like "escape" escape '+'`, true, "SELECT 'abc' LIKE 'escape' ESCAPE '+'"}, 2305 {"select '''_' like '''_' escape ''''", true, "SELECT '''_' LIKE '''_' ESCAPE ''''"}, 2306 } 2307 2308 s.RunTest(c, table) 2309 } 2310 2311 func (s *testParserSuite) TestMysqlDump(c *C) { 2312 // Statements used by mysqldump. 2313 table := []testCase{ 2314 {`UNLOCK TABLES;`, true, ""}, 2315 {`LOCK TABLES t1 READ;`, true, ""}, 2316 {`show table status like 't'`, true, "SHOW TABLE STATUS LIKE 't'"}, 2317 {`LOCK TABLES t2 WRITE`, true, ""}, 2318 2319 // for unlock table and lock table 2320 {`UNLOCK TABLE;`, true, ""}, 2321 {`LOCK TABLE t1 READ;`, true, ""}, 2322 {`show table status like 't'`, true, "SHOW TABLE STATUS LIKE 't'"}, 2323 {`LOCK TABLE t2 WRITE`, true, ""}, 2324 {`LOCK TABLE t1 WRITE, t3 READ`, true, ""}, 2325 } 2326 s.RunTest(c, table) 2327 } 2328 2329 func (s *testParserSuite) TestIndexHint(c *C) { 2330 table := []testCase{ 2331 {`select * from t use index (primary)`, true, "SELECT * FROM `t` USE INDEX (`primary`)"}, 2332 {"select * from t use index (`primary`)", true, "SELECT * FROM `t` USE INDEX (`primary`)"}, 2333 {`select * from t use index ();`, true, "SELECT * FROM `t` USE INDEX ()"}, 2334 {`select * from t use index (idx);`, true, "SELECT * FROM `t` USE INDEX (`idx`)"}, 2335 {`select * from t use index (idx1, idx2);`, true, "SELECT * FROM `t` USE INDEX (`idx1`, `idx2`)"}, 2336 {`select * from t ignore key (idx1)`, true, "SELECT * FROM `t` IGNORE INDEX (`idx1`)"}, 2337 {`select * from t force index for join (idx1)`, true, "SELECT * FROM `t` FORCE INDEX FOR JOIN (`idx1`)"}, 2338 {`select * from t use index for order by (idx1)`, true, "SELECT * FROM `t` USE INDEX FOR ORDER BY (`idx1`)"}, 2339 {`select * from t force index for group by (idx1)`, true, "SELECT * FROM `t` FORCE INDEX FOR GROUP BY (`idx1`)"}, 2340 {`select * from t use index for group by (idx1) use index for order by (idx2), t2`, true, "SELECT * FROM (`t` USE INDEX FOR GROUP BY (`idx1`) USE INDEX FOR ORDER BY (`idx2`)) JOIN `t2`"}, 2341 } 2342 s.RunTest(c, table) 2343 } 2344 2345 func (s *testParserSuite) TestPriority(c *C) { 2346 table := []testCase{ 2347 {`select high_priority * from t`, true, "SELECT HIGH_PRIORITY * FROM `t`"}, 2348 {`select low_priority * from t`, true, "SELECT LOW_PRIORITY * FROM `t`"}, 2349 {`select delayed * from t`, true, "SELECT DELAYED * FROM `t`"}, 2350 {`insert high_priority into t values (1)`, true, "INSERT HIGH_PRIORITY INTO `t` VALUES (1)"}, 2351 {`insert LOW_PRIORITY into t values (1)`, true, "INSERT LOW_PRIORITY INTO `t` VALUES (1)"}, 2352 {`insert delayed into t values (1)`, true, "INSERT DELAYED INTO `t` VALUES (1)"}, 2353 {`update low_priority t set a = 2`, true, "UPDATE LOW_PRIORITY `t` SET `a`=2"}, 2354 {`update high_priority t set a = 2`, true, "UPDATE HIGH_PRIORITY `t` SET `a`=2"}, 2355 {`update delayed t set a = 2`, true, "UPDATE DELAYED `t` SET `a`=2"}, 2356 {`delete low_priority from t where a = 2`, true, "DELETE LOW_PRIORITY FROM `t` WHERE `a`=2"}, 2357 {`delete high_priority from t where a = 2`, true, "DELETE HIGH_PRIORITY FROM `t` WHERE `a`=2"}, 2358 {`delete delayed from t where a = 2`, true, "DELETE DELAYED FROM `t` WHERE `a`=2"}, 2359 {`replace high_priority into t values (1)`, true, "REPLACE HIGH_PRIORITY INTO `t` VALUES (1)"}, 2360 {`replace LOW_PRIORITY into t values (1)`, true, "REPLACE LOW_PRIORITY INTO `t` VALUES (1)"}, 2361 {`replace delayed into t values (1)`, true, "REPLACE DELAYED INTO `t` VALUES (1)"}, 2362 } 2363 s.RunTest(c, table) 2364 2365 parser := New() 2366 stmt, _, err := parser.Parse("select HIGH_PRIORITY * from t", "", "") 2367 c.Assert(err, IsNil) 2368 sel := stmt[0].(*ast.SelectStmt) 2369 c.Assert(sel.SelectStmtOpts.Priority, Equals, mysql.HighPriority) 2370 } 2371 2372 func (s *testParserSuite) TestSQLNoCache(c *C) { 2373 table := []testCase{ 2374 {`select SQL_NO_CACHE * from t`, false, ""}, 2375 {`select SQL_CACHE * from t`, true, "SELECT * FROM `t`"}, 2376 {`select * from t`, true, "SELECT * FROM `t`"}, 2377 } 2378 2379 parser := New() 2380 for _, tt := range table { 2381 stmt, _, err := parser.Parse(tt.src, "", "") 2382 c.Assert(err, IsNil) 2383 2384 sel := stmt[0].(*ast.SelectStmt) 2385 c.Assert(sel.SelectStmtOpts.SQLCache, Equals, tt.ok) 2386 } 2387 } 2388 2389 func (s *testParserSuite) TestEscape(c *C) { 2390 table := []testCase{ 2391 {`select """;`, false, ""}, 2392 {`select """";`, true, "SELECT '\"'"}, 2393 {`select "汉字";`, true, "SELECT '汉字'"}, 2394 {`select 'abc"def';`, true, "SELECT 'abc\"def'"}, 2395 {`select 'a\r\n';`, true, "SELECT 'a\r\n'"}, 2396 {`select "\a\r\n"`, true, "SELECT 'a\r\n'"}, 2397 {`select "\xFF"`, true, "SELECT 'xFF'"}, 2398 } 2399 s.RunTest(c, table) 2400 } 2401 2402 func (s *testParserSuite) TestInsertStatementMemoryAllocation(c *C) { 2403 sql := "insert t values (1)" + strings.Repeat(",(1)", 1000) 2404 var oldStats, newStats runtime.MemStats 2405 runtime.ReadMemStats(&oldStats) 2406 _, err := New().ParseOneStmt(sql, "", "") 2407 c.Assert(err, IsNil) 2408 runtime.ReadMemStats(&newStats) 2409 c.Assert(int(newStats.TotalAlloc-oldStats.TotalAlloc), Less, 1024*500) 2410 } 2411 2412 func (s *testParserSuite) TestExplain(c *C) { 2413 table := []testCase{ 2414 {"explain select c1 from t1", true, "EXPLAIN FORMAT = 'row' SELECT `c1` FROM `t1`"}, 2415 {"explain delete t1, t2 from t1 inner join t2 inner join t3 where t1.id=t2.id and t2.id=t3.id;", true, "EXPLAIN FORMAT = 'row' DELETE `t1`,`t2` FROM (`t1` JOIN `t2`) JOIN `t3` WHERE `t1`.`id`=`t2`.`id` AND `t2`.`id`=`t3`.`id`"}, 2416 {"explain insert into t values (1), (2), (3)", true, "EXPLAIN FORMAT = 'row' INSERT INTO `t` VALUES (1),(2),(3)"}, 2417 {"explain replace into foo values (1 || 2)", true, "EXPLAIN FORMAT = 'row' REPLACE INTO `foo` VALUES (1 OR 2)"}, 2418 {"explain update t set id = id + 1 order by id desc;", true, "EXPLAIN FORMAT = 'row' UPDATE `t` SET `id`=`id`+1 ORDER BY `id` DESC"}, 2419 {"explain select c1 from t1 union (select c2 from t2) limit 1, 1", true, "EXPLAIN FORMAT = 'row' SELECT `c1` FROM `t1` UNION (SELECT `c2` FROM `t2`) LIMIT 1,1"}, 2420 {`explain format = "row" select c1 from t1 union (select c2 from t2) limit 1, 1`, true, "EXPLAIN FORMAT = 'row' SELECT `c1` FROM `t1` UNION (SELECT `c2` FROM `t2`) LIMIT 1,1"}, 2421 {"DESC SCHE.TABL", true, "DESC `SCHE`.`TABL`"}, 2422 {"DESC SCHE.TABL COLUM", true, "DESC `SCHE`.`TABL` `COLUM`"}, 2423 {"DESCRIBE SCHE.TABL COLUM", true, "DESC `SCHE`.`TABL` `COLUM`"}, 2424 {"EXPLAIN ANALYZE SELECT 1", true, "EXPLAIN ANALYZE SELECT 1"}, 2425 {"EXPLAIN FORMAT = 'dot' SELECT 1", true, "EXPLAIN FORMAT = 'dot' SELECT 1"}, 2426 {"EXPLAIN FORMAT = 'row' SELECT 1", true, "EXPLAIN FORMAT = 'row' SELECT 1"}, 2427 {"EXPLAIN FORMAT = 'ROW' SELECT 1", true, "EXPLAIN FORMAT = 'ROW' SELECT 1"}, 2428 {"EXPLAIN SELECT 1", true, "EXPLAIN FORMAT = 'row' SELECT 1"}, 2429 } 2430 s.RunTest(c, table) 2431 } 2432 2433 func (s *testParserSuite) TestPrepare(c *C) { 2434 table := []testCase{ 2435 {"PREPARE pname FROM 'SELECT ?'", true, "PREPARE `pname` FROM 'SELECT ?'"}, 2436 {"PREPARE pname FROM @test", true, "PREPARE `pname` FROM @`test`"}, 2437 {"PREPARE `` FROM @test", true, "PREPARE `` FROM @`test`"}, 2438 } 2439 s.RunTest(c, table) 2440 } 2441 2442 func (s *testParserSuite) TestSavePoint(c *C) { 2443 table := []testCase{ 2444 {"SAVEPOINT id1", true, "SAVEPOINT id1"}, 2445 {"ROLLBACK TO SAVEPOINT id1", true, "ROLLBACK TO SAVEPOINT id1"}, 2446 {"RELEASE SAVEPOINT id1", true, "RELEASE SAVEPOINT id1"}, 2447 {"RELEASE SAVEPOINT id1", true, "RELEASE SAVEPOINT id1"}, 2448 } 2449 s.RunTest(c, table) 2450 } 2451 2452 func (s *testParserSuite) TestDeallocate(c *C) { 2453 table := []testCase{ 2454 {"DEALLOCATE PREPARE test", true, "DEALLOCATE PREPARE `test`"}, 2455 {"DEALLOCATE PREPARE ``", true, "DEALLOCATE PREPARE ``"}, 2456 } 2457 s.RunTest(c, table) 2458 } 2459 2460 func (s *testParserSuite) TestExecute(c *C) { 2461 table := []testCase{ 2462 {"EXECUTE test", true, "EXECUTE `test`"}, 2463 {"EXECUTE test USING @var1,@var2", true, "EXECUTE `test` USING @`var1`,@`var2`"}, 2464 {"EXECUTE `` USING @var1,@var2", true, "EXECUTE `` USING @`var1`,@`var2`"}, 2465 } 2466 s.RunTest(c, table) 2467 } 2468 2469 func (s *testParserSuite) TestTrace(c *C) { 2470 table := []testCase{ 2471 {"trace select c1 from t1", true, "TRACE SELECT `c1` FROM `t1`"}, 2472 {"trace delete t1, t2 from t1 inner join t2 inner join t3 where t1.id=t2.id and t2.id=t3.id;", true, "TRACE DELETE `t1`,`t2` FROM (`t1` JOIN `t2`) JOIN `t3` WHERE `t1`.`id`=`t2`.`id` AND `t2`.`id`=`t3`.`id`"}, 2473 {"trace insert into t values (1), (2), (3)", true, "TRACE INSERT INTO `t` VALUES (1),(2),(3)"}, 2474 {"trace replace into foo values (1 || 2)", true, "TRACE REPLACE INTO `foo` VALUES (1 OR 2)"}, 2475 {"trace update t set id = id + 1 order by id desc;", true, "TRACE UPDATE `t` SET `id`=`id`+1 ORDER BY `id` DESC"}, 2476 {"trace select c1 from t1 union (select c2 from t2) limit 1, 1", true, "TRACE SELECT `c1` FROM `t1` UNION (SELECT `c2` FROM `t2`) LIMIT 1,1"}, 2477 {"trace format = 'row' select c1 from t1 union (select c2 from t2) limit 1, 1", true, "TRACE FORMAT = 'row' SELECT `c1` FROM `t1` UNION (SELECT `c2` FROM `t2`) LIMIT 1,1"}, 2478 {"trace format = 'json' update t set id = id + 1 order by id desc;", true, "TRACE UPDATE `t` SET `id`=`id`+1 ORDER BY `id` DESC"}, 2479 } 2480 s.RunTest(c, table) 2481 } 2482 2483 func (s *testParserSuite) TestBinding(c *C) { 2484 table := []testCase{ 2485 {"create global binding for select * from t using select * from t use index(a)", true, "CREATE GLOBAL BINDING FOR SELECT * FROM `t` USING SELECT * FROM `t` USE INDEX (`a`)"}, 2486 {"create session binding for select * from t using select * from t use index(a)", true, "CREATE SESSION BINDING FOR SELECT * FROM `t` USING SELECT * FROM `t` USE INDEX (`a`)"}, 2487 {"create global binding for select * from t using select * from t use index(a)", true, "CREATE GLOBAL BINDING FOR SELECT * FROM `t` USING SELECT * FROM `t` USE INDEX (`a`)"}, 2488 {"create session binding for select * from t using select * from t use index(a)", true, "CREATE SESSION BINDING FOR SELECT * FROM `t` USING SELECT * FROM `t` USE INDEX (`a`)"}, 2489 {"show global bindings", true, "SHOW GLOBAL BINDINGS"}, 2490 {"show session bindings", true, "SHOW SESSION BINDINGS"}, 2491 } 2492 s.RunTest(c, table) 2493 2494 p := New() 2495 sms, _, err := p.Parse("create global binding for select * from t using select * from t use index(a)", "", "") 2496 c.Assert(err, IsNil) 2497 v, ok := sms[0].(*ast.CreateBindingStmt) 2498 c.Assert(ok, IsTrue) 2499 c.Assert(v.OriginSel.Text(), Equals, "select * from t") 2500 c.Assert(v.HintedSel.Text(), Equals, "select * from t use index(a)") 2501 c.Assert(v.GlobalScope, IsTrue) 2502 } 2503 2504 func (s *testParserSuite) TestView(c *C) { 2505 table := []testCase{ 2506 {"create view v as select * from t", true, "CREATE ALGORITHM = UNDEFINED DEFINER = CURRENT_USER SQL SECURITY DEFINER VIEW `v` AS SELECT * FROM `t`"}, 2507 {"create or replace view v as select * from t", true, "CREATE OR REPLACE ALGORITHM = UNDEFINED DEFINER = CURRENT_USER SQL SECURITY DEFINER VIEW `v` AS SELECT * FROM `t`"}, 2508 {"create or replace algorithm = undefined view v as select * from t", true, "CREATE OR REPLACE ALGORITHM = UNDEFINED DEFINER = CURRENT_USER SQL SECURITY DEFINER VIEW `v` AS SELECT * FROM `t`"}, 2509 {"create or replace algorithm = merge view v as select * from t", true, "CREATE OR REPLACE ALGORITHM = MERGE DEFINER = CURRENT_USER SQL SECURITY DEFINER VIEW `v` AS SELECT * FROM `t`"}, 2510 {"create or replace algorithm = temptable view v as select * from t", true, "CREATE OR REPLACE ALGORITHM = TEMPTABLE DEFINER = CURRENT_USER SQL SECURITY DEFINER VIEW `v` AS SELECT * FROM `t`"}, 2511 {"create or replace algorithm = merge definer = 'root' view v as select * from t", true, "CREATE OR REPLACE ALGORITHM = MERGE DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW `v` AS SELECT * FROM `t`"}, 2512 {"create or replace algorithm = merge definer = 'root' sql security definer view v as select * from t", true, "CREATE OR REPLACE ALGORITHM = MERGE DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW `v` AS SELECT * FROM `t`"}, 2513 {"create or replace algorithm = merge definer = 'root' sql security invoker view v as select * from t", true, "CREATE OR REPLACE ALGORITHM = MERGE DEFINER = `root`@`%` SQL SECURITY INVOKER VIEW `v` AS SELECT * FROM `t`"}, 2514 {"create or replace algorithm = merge definer = 'root' sql security invoker view v(a,b) as select * from t", true, "CREATE OR REPLACE ALGORITHM = MERGE DEFINER = `root`@`%` SQL SECURITY INVOKER VIEW `v` (`a`,`b`) AS SELECT * FROM `t`"}, 2515 {"create or replace algorithm = merge definer = 'root' sql security invoker view v(a,b) as select * from t with local check option", true, "CREATE OR REPLACE ALGORITHM = MERGE DEFINER = `root`@`%` SQL SECURITY INVOKER VIEW `v` (`a`,`b`) AS SELECT * FROM `t` WITH LOCAL CHECK OPTION"}, 2516 {"create or replace algorithm = merge definer = 'root' sql security invoker view v(a,b) as select * from t with cascaded check option", true, "CREATE OR REPLACE ALGORITHM = MERGE DEFINER = `root`@`%` SQL SECURITY INVOKER VIEW `v` (`a`,`b`) AS SELECT * FROM `t`"}, 2517 {"create or replace algorithm = merge definer = current_user view v as select * from t", true, "CREATE OR REPLACE ALGORITHM = MERGE DEFINER = CURRENT_USER SQL SECURITY DEFINER VIEW `v` AS SELECT * FROM `t`"}, 2518 } 2519 s.RunTest(c, table) 2520 2521 // Test case for the text of the select statement in create view statement. 2522 p := New() 2523 sms, _, err := p.Parse("create view v as select * from t", "", "") 2524 c.Assert(err, IsNil) 2525 v, ok := sms[0].(*ast.CreateViewStmt) 2526 c.Assert(ok, IsTrue) 2527 c.Assert(v.Algorithm, Equals, model.AlgorithmUndefined) 2528 c.Assert(v.Select.Text(), Equals, "select * from t") 2529 c.Assert(v.Security, Equals, model.SecurityDefiner) 2530 c.Assert(v.CheckOption, Equals, model.CheckOptionCascaded) 2531 2532 src := `CREATE OR REPLACE ALGORITHM = UNDEFINED DEFINER = root@localhost 2533 SQL SECURITY DEFINER 2534 VIEW V(a,b,c) AS select c,d,e from t 2535 WITH CASCADED CHECK OPTION;` 2536 2537 var st ast.StmtNode 2538 st, err = p.ParseOneStmt(src, "", "") 2539 c.Assert(err, IsNil) 2540 v, ok = st.(*ast.CreateViewStmt) 2541 c.Assert(ok, IsTrue) 2542 c.Assert(v.OrReplace, IsTrue) 2543 c.Assert(v.Algorithm, Equals, model.AlgorithmUndefined) 2544 c.Assert(v.Definer.Username, Equals, "root") 2545 c.Assert(v.Definer.Hostname, Equals, "localhost") 2546 c.Assert(v.Cols[0], Equals, model.NewCIStr("a")) 2547 c.Assert(v.Cols[1], Equals, model.NewCIStr("b")) 2548 c.Assert(v.Cols[2], Equals, model.NewCIStr("c")) 2549 c.Assert(v.Select.Text(), Equals, "select c,d,e from t") 2550 c.Assert(v.Security, Equals, model.SecurityDefiner) 2551 c.Assert(v.CheckOption, Equals, model.CheckOptionCascaded) 2552 } 2553 2554 func (s *testParserSuite) TestTimestampDiffUnit(c *C) { 2555 // Test case for timestampdiff unit. 2556 // TimeUnit should be unified to upper case. 2557 parser := New() 2558 stmt, _, err := parser.Parse("SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01'), TIMESTAMPDIFF(month,'2003-02-01','2003-05-01');", "", "") 2559 c.Assert(err, IsNil) 2560 ss := stmt[0].(*ast.SelectStmt) 2561 fields := ss.Fields.Fields 2562 c.Assert(len(fields), Equals, 2) 2563 expr := fields[0].Expr 2564 f, ok := expr.(*ast.FuncCallExpr) 2565 c.Assert(ok, IsTrue) 2566 c.Assert(f.Args[0].(ast.ValueExpr).GetDatumString(), Equals, "MONTH") 2567 2568 expr = fields[1].Expr 2569 f, ok = expr.(*ast.FuncCallExpr) 2570 c.Assert(ok, IsTrue) 2571 c.Assert(f.Args[0].(ast.ValueExpr).GetDatumString(), Equals, "MONTH") 2572 2573 // Test Illegal TimeUnit for TimestampDiff 2574 table := []testCase{ 2575 {"SELECT TIMESTAMPDIFF(SECOND_MICROSECOND,'2003-02-01','2003-05-01')", false, ""}, 2576 {"SELECT TIMESTAMPDIFF(MINUTE_MICROSECOND,'2003-02-01','2003-05-01')", false, ""}, 2577 {"SELECT TIMESTAMPDIFF(MINUTE_SECOND,'2003-02-01','2003-05-01')", false, ""}, 2578 {"SELECT TIMESTAMPDIFF(HOUR_MICROSECOND,'2003-02-01','2003-05-01')", false, ""}, 2579 {"SELECT TIMESTAMPDIFF(HOUR_SECOND,'2003-02-01','2003-05-01')", false, ""}, 2580 {"SELECT TIMESTAMPDIFF(HOUR_MINUTE,'2003-02-01','2003-05-01')", false, ""}, 2581 {"SELECT TIMESTAMPDIFF(DAY_MICROSECOND,'2003-02-01','2003-05-01')", false, ""}, 2582 {"SELECT TIMESTAMPDIFF(DAY_SECOND,'2003-02-01','2003-05-01')", false, ""}, 2583 {"SELECT TIMESTAMPDIFF(DAY_MINUTE,'2003-02-01','2003-05-01')", false, ""}, 2584 {"SELECT TIMESTAMPDIFF(DAY_HOUR,'2003-02-01','2003-05-01')", false, ""}, 2585 {"SELECT TIMESTAMPDIFF(YEAR_MONTH,'2003-02-01','2003-05-01')", false, ""}, 2586 } 2587 s.RunTest(c, table) 2588 } 2589 2590 func (s *testParserSuite) TestSessionManage(c *C) { 2591 table := []testCase{ 2592 // Kill statement. 2593 // See https://dev.mysql.com/doc/refman/5.7/en/kill.html 2594 {"kill 23123", true, "KILL 23123"}, 2595 {"kill connection 23123", true, "KILL 23123"}, 2596 {"kill query 23123", true, "KILL QUERY 23123"}, 2597 {"kill tidb 23123", true, "KILL TIDB 23123"}, 2598 {"kill tidb connection 23123", true, "KILL TIDB 23123"}, 2599 {"kill tidb query 23123", true, "KILL TIDB QUERY 23123"}, 2600 {"show processlist", true, "SHOW PROCESSLIST"}, 2601 {"show full processlist", true, "SHOW FULL PROCESSLIST"}, 2602 } 2603 s.RunTest(c, table) 2604 } 2605 2606 func (s *testParserSuite) TestSQLModeANSIQuotes(c *C) { 2607 parser := New() 2608 parser.SetSQLMode(mysql.ModeANSIQuotes) 2609 tests := []string{ 2610 `CREATE TABLE "table" ("id" int)`, 2611 `select * from t "tt"`, 2612 } 2613 for _, test := range tests { 2614 _, _, err := parser.Parse(test, "", "") 2615 c.Assert(err, IsNil) 2616 } 2617 } 2618 2619 func (s *testParserSuite) TestDDLStatements(c *C) { 2620 parser := New() 2621 // Tests that whatever the charset it is define, we always assign utf8 charset and utf8_bin collate. 2622 createTableStr := `CREATE TABLE t ( 2623 a varchar(64) binary, 2624 b char(10) charset utf8 collate utf8_general_ci, 2625 c text charset latin1) ENGINE=innoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin` 2626 stmts, _, err := parser.Parse(createTableStr, "", "") 2627 c.Assert(err, IsNil) 2628 stmt := stmts[0].(*ast.CreateTableStmt) 2629 c.Assert(mysql.HasBinaryFlag(stmt.Cols[0].Tp.Flag), IsTrue) 2630 for _, colDef := range stmt.Cols[1:] { 2631 c.Assert(mysql.HasBinaryFlag(colDef.Tp.Flag), IsFalse) 2632 } 2633 for _, tblOpt := range stmt.Options { 2634 switch tblOpt.Tp { 2635 case ast.TableOptionCharset: 2636 c.Assert(tblOpt.StrValue, Equals, "utf8") 2637 case ast.TableOptionCollate: 2638 c.Assert(tblOpt.StrValue, Equals, "utf8_bin") 2639 } 2640 } 2641 createTableStr = `CREATE TABLE t ( 2642 a varbinary(64), 2643 b binary(10), 2644 c blob)` 2645 stmts, _, err = parser.Parse(createTableStr, "", "") 2646 c.Assert(err, IsNil) 2647 stmt = stmts[0].(*ast.CreateTableStmt) 2648 for _, colDef := range stmt.Cols { 2649 c.Assert(colDef.Tp.Charset, Equals, mysql.CharsetBin) 2650 c.Assert(colDef.Tp.Collate, Equals, mysql.CollationBin) 2651 c.Assert(mysql.HasBinaryFlag(colDef.Tp.Flag), IsTrue) 2652 } 2653 } 2654 2655 func (s *testParserSuite) TestAnalyze(c *C) { 2656 table := []testCase{ 2657 {"analyze table t1", true, "ANALYZE TABLE `t1`"}, 2658 {"analyze table t,t1", true, "ANALYZE TABLE `t`,`t1`"}, 2659 {"analyze table t1 index", true, "ANALYZE TABLE `t1` INDEX"}, 2660 {"analyze table t1 index a", true, "ANALYZE TABLE `t1` INDEX `a`"}, 2661 {"analyze table t1 index a,b", true, "ANALYZE TABLE `t1` INDEX `a`,`b`"}, 2662 {"analyze table t with 4 buckets", true, "ANALYZE TABLE `t` WITH 4 BUCKETS"}, 2663 {"analyze table t index a with 4 buckets", true, "ANALYZE TABLE `t` INDEX `a` WITH 4 BUCKETS"}, 2664 {"analyze table t partition a", true, "ANALYZE TABLE `t` PARTITION `a`"}, 2665 {"analyze table t partition a with 4 buckets", true, "ANALYZE TABLE `t` PARTITION `a` WITH 4 BUCKETS"}, 2666 {"analyze table t partition a index b", true, "ANALYZE TABLE `t` PARTITION `a` INDEX `b`"}, 2667 {"analyze table t partition a index b with 4 buckets", true, "ANALYZE TABLE `t` PARTITION `a` INDEX `b` WITH 4 BUCKETS"}, 2668 } 2669 s.RunTest(c, table) 2670 } 2671 2672 func (s *testParserSuite) TestGeneratedColumn(c *C) { 2673 tests := []struct { 2674 input string 2675 ok bool 2676 expr string 2677 }{ 2678 {"create table t (c int, d int generated always as (c + 1) virtual)", true, "c + 1"}, 2679 {"create table t (c int, d int as ( c + 1 ) virtual)", true, "c + 1"}, 2680 {"create table t (c int, d int as (1 + 1) stored)", true, "1 + 1"}, 2681 } 2682 parser := New() 2683 for _, tt := range tests { 2684 stmtNodes, _, err := parser.Parse(tt.input, "", "") 2685 if tt.ok { 2686 c.Assert(err, IsNil) 2687 stmtNode := stmtNodes[0] 2688 for _, col := range stmtNode.(*ast.CreateTableStmt).Cols { 2689 for _, opt := range col.Options { 2690 if opt.Tp == ast.ColumnOptionGenerated { 2691 c.Assert(opt.Expr.Text(), Equals, tt.expr) 2692 } 2693 } 2694 } 2695 } else { 2696 c.Assert(err, NotNil) 2697 } 2698 } 2699 2700 } 2701 2702 func (s *testParserSuite) TestSetTransaction(c *C) { 2703 // Set transaction is equivalent to setting the global or session value of tx_isolation. 2704 // For example: 2705 // SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED 2706 // SET SESSION tx_isolation='READ-COMMITTED' 2707 tests := []struct { 2708 input string 2709 isGlobal bool 2710 value string 2711 }{ 2712 { 2713 "SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED", 2714 false, "READ-COMMITTED", 2715 }, 2716 { 2717 "SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ", 2718 true, "REPEATABLE-READ", 2719 }, 2720 } 2721 parser := New() 2722 for _, t := range tests { 2723 stmt1, err := parser.ParseOneStmt(t.input, "", "") 2724 c.Assert(err, IsNil) 2725 setStmt := stmt1.(*ast.SetStmt) 2726 vars := setStmt.Variables[0] 2727 c.Assert(vars.Name, Equals, "tx_isolation") 2728 c.Assert(vars.IsGlobal, Equals, t.isGlobal) 2729 c.Assert(vars.IsSystem, Equals, true) 2730 c.Assert(vars.Value.(ast.ValueExpr).GetValue(), Equals, t.value) 2731 } 2732 } 2733 2734 func (s *testParserSuite) TestSideEffect(c *C) { 2735 // This test cover a bug that parse an error SQL doesn't leave the parser in a 2736 // clean state, cause the following SQL parse fail. 2737 parser := New() 2738 _, err := parser.ParseOneStmt("create table t /*!50100 'abc', 'abc' */;", "", "") 2739 c.Assert(err, NotNil) 2740 2741 _, err = parser.ParseOneStmt("show tables;", "", "") 2742 c.Assert(err, IsNil) 2743 } 2744 2745 func (s *testParserSuite) TestTablePartition(c *C) { 2746 table := []testCase{ 2747 {"ALTER TABLE t1 TRUNCATE PARTITION p0", true, "ALTER TABLE `t1` TRUNCATE PARTITION `p0`"}, 2748 {"ALTER TABLE t1 ADD PARTITION (PARTITION `p5` VALUES LESS THAN (2010) COMMENT 'APSTART \\' APEND')", true, "ALTER TABLE `t1` ADD PARTITION (PARTITION `p5` VALUES LESS THAN (2010) COMMENT = 'APSTART '' APEND')"}, 2749 {"ALTER TABLE t1 ADD PARTITION (PARTITION `p5` VALUES LESS THAN (2010) COMMENT = 'xxx')", true, "ALTER TABLE `t1` ADD PARTITION (PARTITION `p5` VALUES LESS THAN (2010) COMMENT = 'xxx')"}, 2750 {`CREATE TABLE t1 (a int not null,b int not null,c int not null,primary key(a,b)) 2751 partition by range (a) 2752 partitions 3 2753 (partition x1 values less than (5), 2754 partition x2 values less than (10), 2755 partition x3 values less than maxvalue);`, true, "CREATE TABLE `t1` (`a` INT NOT NULL,`b` INT NOT NULL,`c` INT NOT NULL,PRIMARY KEY(`a`, `b`)) PARTITION BY RANGE (`a`) (PARTITION `x1` VALUES LESS THAN (5),PARTITION `x2` VALUES LESS THAN (10),PARTITION `x3` VALUES LESS THAN (MAXVALUE))"}, 2756 {"CREATE TABLE t1 (a int not null) partition by range (a) (partition x1 values less than (5) tablespace ts1)", true, "CREATE TABLE `t1` (`a` INT NOT NULL) PARTITION BY RANGE (`a`) (PARTITION `x1` VALUES LESS THAN (5))"}, 2757 {`create table t (a int) partition by range (a) 2758 (PARTITION p0 VALUES LESS THAN (63340531200) ENGINE = MyISAM, 2759 PARTITION p1 VALUES LESS THAN (63342604800) ENGINE MyISAM)`, true, "CREATE TABLE `t` (`a` INT) PARTITION BY RANGE (`a`) (PARTITION `p0` VALUES LESS THAN (63340531200),PARTITION `p1` VALUES LESS THAN (63342604800))"}, 2760 {`create table t (a int) partition by range (a) 2761 (PARTITION p0 VALUES LESS THAN (63340531200) ENGINE = MyISAM COMMENT 'xxx', 2762 PARTITION p1 VALUES LESS THAN (63342604800) ENGINE = MyISAM)`, true, "CREATE TABLE `t` (`a` INT) PARTITION BY RANGE (`a`) (PARTITION `p0` VALUES LESS THAN (63340531200) COMMENT = 'xxx',PARTITION `p1` VALUES LESS THAN (63342604800))"}, 2763 {`create table t1 (a int) partition by range (a) 2764 (PARTITION p0 VALUES LESS THAN (63340531200) COMMENT 'xxx' ENGINE = MyISAM , 2765 PARTITION p1 VALUES LESS THAN (63342604800) ENGINE = MyISAM)`, true, "CREATE TABLE `t1` (`a` INT) PARTITION BY RANGE (`a`) (PARTITION `p0` VALUES LESS THAN (63340531200) COMMENT = 'xxx',PARTITION `p1` VALUES LESS THAN (63342604800))"}, 2766 {`create table t (id int) 2767 partition by range (id) 2768 subpartition by key (id) subpartitions 2 2769 (partition p0 values less than (42))`, true, "CREATE TABLE `t` (`id` INT) PARTITION BY RANGE (`id`) (PARTITION `p0` VALUES LESS THAN (42))"}, 2770 {`create table t (id int) 2771 partition by range (id) 2772 subpartition by hash (id) 2773 (partition p0 values less than (42))`, true, "CREATE TABLE `t` (`id` INT) PARTITION BY RANGE (`id`) (PARTITION `p0` VALUES LESS THAN (42))"}, 2774 } 2775 s.RunTest(c, table) 2776 2777 // Check comment content. 2778 parser := New() 2779 stmt, err := parser.ParseOneStmt("create table t (id int) partition by range (id) (partition p0 values less than (10) comment 'check')", "", "") 2780 c.Assert(err, IsNil) 2781 createTable := stmt.(*ast.CreateTableStmt) 2782 c.Assert(createTable.Partition.Definitions[0].Comment, Equals, "check") 2783 } 2784 2785 func (s *testParserSuite) TestTablePartitionNameList(c *C) { 2786 table := []testCase{ 2787 {`select * from t partition (p0,p1)`, true, ""}, 2788 } 2789 2790 parser := New() 2791 for _, tt := range table { 2792 stmt, _, err := parser.Parse(tt.src, "", "") 2793 c.Assert(err, IsNil) 2794 2795 sel := stmt[0].(*ast.SelectStmt) 2796 source, ok := sel.From.TableRefs.Left.(*ast.TableSource) 2797 c.Assert(ok, IsTrue) 2798 tableName, ok := source.Source.(*ast.TableName) 2799 c.Assert(ok, IsTrue) 2800 c.Assert(len(tableName.PartitionNames), Equals, 2) 2801 c.Assert(tableName.PartitionNames[0], Equals, model.CIStr{"p0", "p0"}) 2802 c.Assert(tableName.PartitionNames[1], Equals, model.CIStr{"p1", "p1"}) 2803 } 2804 } 2805 2806 func (s *testParserSuite) TestNotExistsSubquery(c *C) { 2807 table := []testCase{ 2808 {`select * from t1 where not exists (select * from t2 where t1.a = t2.a)`, true, ""}, 2809 } 2810 2811 parser := New() 2812 for _, tt := range table { 2813 stmt, _, err := parser.Parse(tt.src, "", "") 2814 c.Assert(err, IsNil) 2815 2816 sel := stmt[0].(*ast.SelectStmt) 2817 exists, ok := sel.Where.(*ast.ExistsSubqueryExpr) 2818 c.Assert(ok, IsTrue) 2819 c.Assert(exists.Not, Equals, tt.ok) 2820 } 2821 } 2822 2823 func (s *testParserSuite) TestWindowFunctionIdentifier(c *C) { 2824 var table []testCase 2825 s.enableWindowFunc = true 2826 for key := range windowFuncTokenMap { 2827 table = append(table, testCase{fmt.Sprintf("select 1 %s", key), false, fmt.Sprintf("SELECT 1 AS `%s`", key)}) 2828 } 2829 s.RunTest(c, table) 2830 2831 s.enableWindowFunc = false 2832 for i := range table { 2833 table[i].ok = true 2834 } 2835 s.RunTest(c, table) 2836 } 2837 2838 func (s *testParserSuite) TestWindowFunctions(c *C) { 2839 table := []testCase{ 2840 // For window function descriptions. 2841 // See https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html 2842 {`SELECT CUME_DIST() OVER w FROM t;`, true, "SELECT CUME_DIST() OVER (`w`) FROM `t`"}, 2843 {`SELECT DENSE_RANK() OVER w FROM t;`, true, "SELECT DENSE_RANK() OVER (`w`) FROM `t`"}, 2844 {`SELECT FIRST_VALUE(val) OVER w FROM t;`, true, "SELECT FIRST_VALUE(`val`) OVER (`w`) FROM `t`"}, 2845 {`SELECT FIRST_VALUE(val) RESPECT NULLS OVER w FROM t;`, true, "SELECT FIRST_VALUE(`val`) OVER (`w`) FROM `t`"}, 2846 {`SELECT FIRST_VALUE(val) IGNORE NULLS OVER w FROM t;`, true, "SELECT FIRST_VALUE(`val`) IGNORE NULLS OVER (`w`) FROM `t`"}, 2847 {`SELECT LAG(val) OVER w FROM t;`, true, "SELECT LAG(`val`) OVER (`w`) FROM `t`"}, 2848 {`SELECT LAG(val, 1) OVER w FROM t;`, true, "SELECT LAG(`val`, 1) OVER (`w`) FROM `t`"}, 2849 {`SELECT LAG(val, 1, def) OVER w FROM t;`, true, "SELECT LAG(`val`, 1, `def`) OVER (`w`) FROM `t`"}, 2850 {`SELECT LAST_VALUE(val) OVER w FROM t;`, true, "SELECT LAST_VALUE(`val`) OVER (`w`) FROM `t`"}, 2851 {`SELECT LEAD(val) OVER w FROM t;`, true, "SELECT LEAD(`val`) OVER (`w`) FROM `t`"}, 2852 {`SELECT LEAD(val, 1) OVER w FROM t;`, true, "SELECT LEAD(`val`, 1) OVER (`w`) FROM `t`"}, 2853 {`SELECT LEAD(val, 1, def) OVER w FROM t;`, true, "SELECT LEAD(`val`, 1, `def`) OVER (`w`) FROM `t`"}, 2854 {`SELECT NTH_VALUE(val, 233) OVER w FROM t;`, true, "SELECT NTH_VALUE(`val`, 233) OVER (`w`) FROM `t`"}, 2855 {`SELECT NTH_VALUE(val, 233) FROM FIRST OVER w FROM t;`, true, "SELECT NTH_VALUE(`val`, 233) OVER (`w`) FROM `t`"}, 2856 {`SELECT NTH_VALUE(val, 233) FROM LAST OVER w FROM t;`, true, "SELECT NTH_VALUE(`val`, 233) FROM LAST OVER (`w`) FROM `t`"}, 2857 {`SELECT NTH_VALUE(val, 233) FROM LAST IGNORE NULLS OVER w FROM t;`, true, "SELECT NTH_VALUE(`val`, 233) FROM LAST IGNORE NULLS OVER (`w`) FROM `t`"}, 2858 {`SELECT NTH_VALUE(val) OVER w FROM t;`, false, ""}, 2859 {`SELECT NTILE(233) OVER w FROM t;`, true, "SELECT NTILE(233) OVER (`w`) FROM `t`"}, 2860 {`SELECT PERCENT_RANK() OVER w FROM t;`, true, "SELECT PERCENT_RANK() OVER (`w`) FROM `t`"}, 2861 {`SELECT RANK() OVER w FROM t;`, true, "SELECT RANK() OVER (`w`) FROM `t`"}, 2862 {`SELECT ROW_NUMBER() OVER w FROM t;`, true, "SELECT ROW_NUMBER() OVER (`w`) FROM `t`"}, 2863 {`SELECT n, LAG(n, 1, 0) OVER w, LEAD(n, 1, 0) OVER w, n + LAG(n, 1, 0) OVER w FROM fib;`, true, "SELECT `n`,LAG(`n`, 1, 0) OVER (`w`),LEAD(`n`, 1, 0) OVER (`w`),`n`+LAG(`n`, 1, 0) OVER (`w`) FROM `fib`"}, 2864 2865 // For window function concepts and syntax. 2866 // See https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html 2867 {`SELECT SUM(profit) OVER(PARTITION BY country) AS country_profit FROM sales;`, true, "SELECT SUM(`profit`) OVER (PARTITION BY `country`) AS `country_profit` FROM `sales`"}, 2868 {`SELECT SUM(profit) OVER() AS country_profit FROM sales;`, true, "SELECT SUM(`profit`) OVER () AS `country_profit` FROM `sales`"}, 2869 {`SELECT AVG(profit) OVER() AS country_profit FROM sales;`, true, "SELECT AVG(`profit`) OVER () AS `country_profit` FROM `sales`"}, 2870 {`SELECT BIT_XOR(profit) OVER() AS country_profit FROM sales;`, true, "SELECT BIT_XOR(`profit`) OVER () AS `country_profit` FROM `sales`"}, 2871 {`SELECT COUNT(profit) OVER() AS country_profit FROM sales;`, true, "SELECT COUNT(`profit`) OVER () AS `country_profit` FROM `sales`"}, 2872 {`SELECT COUNT(ALL profit) OVER() AS country_profit FROM sales;`, true, "SELECT COUNT(`profit`) OVER () AS `country_profit` FROM `sales`"}, 2873 {`SELECT COUNT(*) OVER() AS country_profit FROM sales;`, true, "SELECT COUNT(1) OVER () AS `country_profit` FROM `sales`"}, 2874 {`SELECT MAX(profit) OVER() AS country_profit FROM sales;`, true, "SELECT MAX(`profit`) OVER () AS `country_profit` FROM `sales`"}, 2875 {`SELECT MIN(profit) OVER() AS country_profit FROM sales;`, true, "SELECT MIN(`profit`) OVER () AS `country_profit` FROM `sales`"}, 2876 {`SELECT SUM(profit) OVER() AS country_profit FROM sales;`, true, "SELECT SUM(`profit`) OVER () AS `country_profit` FROM `sales`"}, 2877 {`SELECT ROW_NUMBER() OVER(PARTITION BY country) AS row_num1 FROM sales;`, true, "SELECT ROW_NUMBER() OVER (PARTITION BY `country`) AS `row_num1` FROM `sales`"}, 2878 {`SELECT ROW_NUMBER() OVER(PARTITION BY country, d ORDER BY year, product) AS row_num2 FROM sales;`, true, "SELECT ROW_NUMBER() OVER (PARTITION BY `country`, `d` ORDER BY `year`,`product`) AS `row_num2` FROM `sales`"}, 2879 2880 // For window function frame specification. 2881 // See https://dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html 2882 {`SELECT SUM(val) OVER (PARTITION BY subject ORDER BY time ROWS UNBOUNDED PRECEDING) FROM t;`, true, "SELECT SUM(`val`) OVER (PARTITION BY `subject` ORDER BY `time` ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM `t`"}, 2883 {`SELECT AVG(val) OVER (PARTITION BY subject ORDER BY time ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM t;`, true, "SELECT AVG(`val`) OVER (PARTITION BY `subject` ORDER BY `time` ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM `t`"}, 2884 {`SELECT AVG(val) OVER (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM t;`, true, "SELECT AVG(`val`) OVER (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM `t`"}, 2885 {`SELECT AVG(val) OVER (ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING) FROM t;`, true, "SELECT AVG(`val`) OVER (ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING) FROM `t`"}, 2886 {`SELECT AVG(val) OVER (RANGE BETWEEN INTERVAL 5 DAY PRECEDING AND INTERVAL '2:30' MINUTE_SECOND FOLLOWING) FROM t;`, true, "SELECT AVG(`val`) OVER (RANGE BETWEEN INTERVAL 5 DAY PRECEDING AND INTERVAL '2:30' MINUTE_SECOND FOLLOWING) FROM `t`"}, 2887 {`SELECT AVG(val) OVER (RANGE BETWEEN CURRENT ROW AND CURRENT ROW) FROM t;`, true, "SELECT AVG(`val`) OVER (RANGE BETWEEN CURRENT ROW AND CURRENT ROW) FROM `t`"}, 2888 {`SELECT AVG(val) OVER (RANGE CURRENT ROW) FROM t;`, true, "SELECT AVG(`val`) OVER (RANGE BETWEEN CURRENT ROW AND CURRENT ROW) FROM `t`"}, 2889 2890 // For named windows. 2891 // See https://dev.mysql.com/doc/refman/8.0/en/window-functions-named-windows.html 2892 {`SELECT RANK() OVER w FROM t WINDOW w AS (ORDER BY val);`, true, "SELECT RANK() OVER (`w`) FROM `t` WINDOW `w` AS (ORDER BY `val`)"}, 2893 {`SELECT RANK() OVER w FROM t WINDOW w AS ();`, true, "SELECT RANK() OVER (`w`) FROM `t` WINDOW `w` AS ()"}, 2894 {`SELECT FIRST_VALUE(year) OVER (w ORDER BY year ASC) AS first FROM sales WINDOW w AS (PARTITION BY country);`, true, "SELECT FIRST_VALUE(`year`) OVER (`w` ORDER BY `year`) AS `first` FROM `sales` WINDOW `w` AS (PARTITION BY `country`)"}, 2895 {`SELECT RANK() OVER w1 FROM t WINDOW w1 AS (w2), w2 AS (), w3 AS (w1);`, true, "SELECT RANK() OVER (`w1`) FROM `t` WINDOW `w1` AS (`w2`),`w2` AS (),`w3` AS (`w1`)"}, 2896 {`SELECT RANK() OVER w1 FROM t WINDOW w1 AS (w2), w2 AS (w3), w3 AS (w1);`, true, "SELECT RANK() OVER (`w1`) FROM `t` WINDOW `w1` AS (`w2`),`w2` AS (`w3`),`w3` AS (`w1`)"}, 2897 2898 // For tidb_parse_tso 2899 {`select tidb_parse_tso(1)`, true, "SELECT TIDB_PARSE_TSO(1)"}, 2900 {`select from_unixtime(404411537129996288)`, true, "SELECT FROM_UNIXTIME(404411537129996288)"}, 2901 {`select from_unixtime(404411537129996288.22)`, true, "SELECT FROM_UNIXTIME(404411537129996288.22)"}, 2902 } 2903 s.enableWindowFunc = true 2904 s.RunTest(c, table) 2905 } 2906 2907 type windowFrameBoundChecker struct { 2908 fb *ast.FrameBound 2909 exprRc int 2910 timeUnitRc int 2911 c *C 2912 } 2913 2914 // Enter implements ast.Visitor interface. 2915 func (wfc *windowFrameBoundChecker) Enter(inNode ast.Node) (outNode ast.Node, skipChildren bool) { 2916 if _, ok := inNode.(*ast.FrameBound); ok { 2917 wfc.fb = inNode.(*ast.FrameBound) 2918 if wfc.fb.Unit != nil { 2919 _, ok := wfc.fb.Expr.(ast.ValueExpr) 2920 wfc.c.Assert(ok, IsFalse) 2921 } 2922 } 2923 return inNode, false 2924 } 2925 2926 // Leave implements ast.Visitor interface. 2927 func (wfc *windowFrameBoundChecker) Leave(inNode ast.Node) (node ast.Node, ok bool) { 2928 if _, ok := inNode.(*ast.FrameBound); ok { 2929 wfc.fb = nil 2930 } 2931 if wfc.fb != nil { 2932 if inNode == wfc.fb.Expr { 2933 wfc.exprRc++ 2934 } else if inNode == wfc.fb.Unit { 2935 wfc.timeUnitRc++ 2936 } 2937 } 2938 return inNode, true 2939 } 2940 2941 // For issue #51 2942 // See https://github.com/XiaoMi/Gaea/parser/pull/51 for details 2943 func (s *testParserSuite) TestVisitFrameBound(c *C) { 2944 parser := New() 2945 parser.EnableWindowFunc(true) 2946 table := []struct { 2947 s string 2948 exprRc int 2949 timeUnitRc int 2950 }{ 2951 {`SELECT AVG(val) OVER (RANGE INTERVAL 1+3 MINUTE_SECOND PRECEDING) FROM t;`, 1, 1}, 2952 {`SELECT AVG(val) OVER (RANGE 5 PRECEDING) FROM t;`, 1, 0}, 2953 {`SELECT AVG(val) OVER () FROM t;`, 0, 0}, 2954 } 2955 for _, t := range table { 2956 stmt, err := parser.ParseOneStmt(t.s, "", "") 2957 c.Assert(err, IsNil) 2958 checker := windowFrameBoundChecker{c: c} 2959 stmt.Accept(&checker) 2960 c.Assert(checker.exprRc, Equals, t.exprRc) 2961 c.Assert(checker.timeUnitRc, Equals, t.timeUnitRc) 2962 } 2963 2964 } 2965 2966 func (s *testParserSuite) TestFieldText(c *C) { 2967 parser := New() 2968 stmts, _, err := parser.Parse("select a from t", "", "") 2969 c.Assert(err, IsNil) 2970 tmp := stmts[0].(*ast.SelectStmt) 2971 c.Assert(tmp.Fields.Fields[0].Text(), Equals, "a") 2972 2973 sqls := []string{ 2974 "trace select a from t", 2975 "trace format = 'row' select a from t", 2976 "trace format = 'json' select a from t", 2977 } 2978 for _, sql := range sqls { 2979 stmts, _, err = parser.Parse(sql, "", "") 2980 c.Assert(err, IsNil) 2981 traceStmt := stmts[0].(*ast.TraceStmt) 2982 c.Assert(traceStmt.Text(), Equals, sql) 2983 c.Assert(traceStmt.Stmt.Text(), Equals, "select a from t") 2984 } 2985 } 2986 2987 // See https://github.com/XiaoMi/Gaea/parser/issue/94 2988 func (s *testParserSuite) TestQuotedSystemVariables(c *C) { 2989 parser := New() 2990 2991 st, err := parser.ParseOneStmt( 2992 "select @@Sql_Mode, @@`SQL_MODE`, @@session.`sql_mode`, @@global.`s ql``mode`, @@session.'sql\\nmode', @@local.\"sql\\\"mode\";", 2993 "", 2994 "", 2995 ) 2996 c.Assert(err, IsNil) 2997 ss := st.(*ast.SelectStmt) 2998 expected := []*ast.VariableExpr{ 2999 { 3000 Name: "sql_mode", 3001 IsGlobal: false, 3002 IsSystem: true, 3003 ExplicitScope: false, 3004 }, 3005 { 3006 Name: "sql_mode", 3007 IsGlobal: false, 3008 IsSystem: true, 3009 ExplicitScope: false, 3010 }, 3011 { 3012 Name: "sql_mode", 3013 IsGlobal: false, 3014 IsSystem: true, 3015 ExplicitScope: true, 3016 }, 3017 { 3018 Name: "s ql`mode", 3019 IsGlobal: true, 3020 IsSystem: true, 3021 ExplicitScope: true, 3022 }, 3023 { 3024 Name: "sql\nmode", 3025 IsGlobal: false, 3026 IsSystem: true, 3027 ExplicitScope: true, 3028 }, 3029 { 3030 Name: `sql"mode`, 3031 IsGlobal: false, 3032 IsSystem: true, 3033 ExplicitScope: true, 3034 }, 3035 } 3036 3037 c.Assert(len(ss.Fields.Fields), Equals, len(expected)) 3038 for i, field := range ss.Fields.Fields { 3039 ve := field.Expr.(*ast.VariableExpr) 3040 cmt := Commentf("field %d, ve = %v", i, ve) 3041 c.Assert(ve.Name, Equals, expected[i].Name, cmt) 3042 c.Assert(ve.IsGlobal, Equals, expected[i].IsGlobal, cmt) 3043 c.Assert(ve.IsSystem, Equals, expected[i].IsSystem, cmt) 3044 c.Assert(ve.ExplicitScope, Equals, expected[i].ExplicitScope, cmt) 3045 } 3046 } 3047 3048 // See https://github.com/XiaoMi/Gaea/parser/issue/95 3049 func (s *testParserSuite) TestQuotedVariableColumnName(c *C) { 3050 parser := New() 3051 3052 st, err := parser.ParseOneStmt( 3053 "select @abc, @`abc`, @'aBc', @\"AbC\", @6, @`6`, @'6', @\"6\", @@sql_mode, @@`sql_mode`, @;", 3054 "", 3055 "", 3056 ) 3057 c.Assert(err, IsNil) 3058 ss := st.(*ast.SelectStmt) 3059 expected := []string{ 3060 "@abc", 3061 "@`abc`", 3062 "@'aBc'", 3063 `@"AbC"`, 3064 "@6", 3065 "@`6`", 3066 "@'6'", 3067 `@"6"`, 3068 "@@sql_mode", 3069 "@@`sql_mode`", 3070 "@", 3071 } 3072 3073 c.Assert(len(ss.Fields.Fields), Equals, len(expected)) 3074 for i, field := range ss.Fields.Fields { 3075 c.Assert(field.Text(), Equals, expected[i]) 3076 } 3077 } 3078 3079 // CleanNodeText set the text of node and all child node empty. 3080 // For test only. 3081 func CleanNodeText(node ast.Node) { 3082 var cleaner nodeTextCleaner 3083 node.Accept(&cleaner) 3084 } 3085 3086 // nodeTextCleaner clean the text of a node and it's child node. 3087 // For test only. 3088 type nodeTextCleaner struct { 3089 } 3090 3091 // Enter implements Visitor interface. 3092 func (checker *nodeTextCleaner) Enter(in ast.Node) (out ast.Node, skipChildren bool) { 3093 in.SetText("") 3094 switch node := in.(type) { 3095 case *ast.CreateTableStmt: 3096 for _, opt := range node.Options { 3097 switch opt.Tp { 3098 case ast.TableOptionCharset: 3099 opt.StrValue = strings.ToUpper(opt.StrValue) 3100 case ast.TableOptionCollate: 3101 opt.StrValue = strings.ToUpper(opt.StrValue) 3102 } 3103 } 3104 for _, col := range node.Cols { 3105 col.Tp.Charset = strings.ToUpper(col.Tp.Charset) 3106 col.Tp.Collate = strings.ToUpper(col.Tp.Collate) 3107 3108 for i, option := range col.Options { 3109 if option.Tp == 0 && option.Expr == nil && option.Stored == false && option.Refer == nil { 3110 col.Options = append(col.Options[:i], col.Options[i+1:]...) 3111 } 3112 } 3113 } 3114 if node.Partition != nil && node.Partition.Expr != nil { 3115 var tmpCleaner nodeTextCleaner 3116 node.Partition.Expr.Accept(&tmpCleaner) 3117 } 3118 case *ast.DeleteStmt: 3119 for _, tableHint := range node.TableHints { 3120 tableHint.HintName.O = "" 3121 } 3122 case *ast.UpdateStmt: 3123 for _, tableHint := range node.TableHints { 3124 tableHint.HintName.O = "" 3125 } 3126 case *ast.Constraint: 3127 if node.Option != nil { 3128 if node.Option.KeyBlockSize == 0x0 && node.Option.Tp == 0 && node.Option.Comment == "" { 3129 node.Option = nil 3130 } 3131 } 3132 case *ast.FuncCallExpr: 3133 node.FnName.O = strings.ToLower(node.FnName.O) 3134 case *ast.AggregateFuncExpr: 3135 node.F = strings.ToLower(node.F) 3136 case *ast.SelectField: 3137 node.Offset = 0 3138 case *driver.ValueExpr: 3139 if node.Kind() == types.KindMysqlDecimal { 3140 node.GetMysqlDecimal().FromString(node.GetMysqlDecimal().ToString()) 3141 } 3142 case *ast.GrantStmt: 3143 var privs []*ast.PrivElem 3144 for _, v := range node.Privs { 3145 if v.Priv != 0 { 3146 privs = append(privs, v) 3147 } 3148 } 3149 node.Privs = privs 3150 case *ast.AlterTableStmt: 3151 var specs []*ast.AlterTableSpec 3152 for _, v := range node.Specs { 3153 if v.Tp != 0 && !(v.Tp == ast.AlterTableOption && len(v.Options) == 0) { 3154 specs = append(specs, v) 3155 } 3156 } 3157 node.Specs = specs 3158 } 3159 return in, false 3160 } 3161 3162 // Leave implements Visitor interface. 3163 func (checker *nodeTextCleaner) Leave(in ast.Node) (out ast.Node, ok bool) { 3164 return in, true 3165 }