github.com/matrixorigin/matrixone@v0.7.0/pkg/sql/parsers/dialect/mysql/mysql_sql_test.go (about) 1 // Copyright 2021 Matrix Origin 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 // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 12 // See the License for the specific language governing permissions and 13 // limitations under the License. 14 15 package mysql 16 17 import ( 18 "context" 19 "testing" 20 21 "github.com/matrixorigin/matrixone/pkg/sql/parsers/dialect" 22 "github.com/matrixorigin/matrixone/pkg/sql/parsers/tree" 23 ) 24 25 var ( 26 debugSQL = struct { 27 input string 28 output string 29 }{ 30 input: "create table t1 (a int comment '\"123123\\'')", 31 output: "create table t1 (a int comment \"123123'')", 32 } 33 ) 34 35 // character set latin1 NOT NULL default 36 func TestDebug(t *testing.T) { 37 if debugSQL.output == "" { 38 debugSQL.output = debugSQL.input 39 } 40 ast, err := ParseOne(context.TODO(), debugSQL.input) 41 if err != nil { 42 t.Errorf("Parse(%q) err: %v", debugSQL.input, err) 43 return 44 } 45 out := tree.String(ast, dialect.MYSQL) 46 if debugSQL.output != out { 47 t.Errorf("Parsing failed. \nExpected/Got:\n%s\n%s", debugSQL.output, out) 48 } 49 } 50 51 var ( 52 validSQL = []struct { 53 input string 54 output string 55 }{{ 56 input: "create table t1 (a int comment '\"123123\\'')", 57 output: "create table t1 (a int comment \"123123'')", 58 }, { 59 input: "select * from t1 where a not ilike '%a'", 60 output: "select * from t1 where a not ilike %a", 61 }, { 62 input: "select * from t1 where a ilike '%a'", 63 output: "select * from t1 where a ilike %a", 64 }, { 65 input: "select * from result_scan(query_id)", 66 output: "select * from result_scan(query_id)", 67 }, { 68 input: "select * from meta_scan('query_id');", 69 output: "select * from meta_scan(query_id)", 70 }, { 71 input: "show variables like 'sql_mode'", 72 output: "show variables like sql_mode", 73 }, { 74 input: "show index from t1 from db", 75 output: "show index from db.t1", 76 }, { 77 input: "select * from (SELECT * FROM (SELECT 1, 2, 3)) AS t1", 78 output: "select * from (select * from (select 1, 2, 3)) as t1", 79 }, { 80 input: "SELECT count(*) AS low_stock FROM (\nSELECT s_w_id, s_i_id, s_quantity\nFROM bmsql_stock\nWHERE s_w_id = 1 AND s_quantity < 1000 AND s_i_id IN (\nSELECT ol_i_id\nFROM bmsql_district\nJOIN bmsql_order_line ON ol_w_id = d_w_id\nAND ol_d_id = d_id\nAND ol_o_id >= d_next_o_id - 20\nAND ol_o_id < d_next_o_id\nWHERE d_w_id = 1 AND d_id = 1\n)\n);", 81 output: "select count(*) as low_stock from (select s_w_id, s_i_id, s_quantity from bmsql_stock where s_w_id = 1 and s_quantity < 1000 and s_i_id in (select ol_i_id from bmsql_district inner join bmsql_order_line on ol_w_id = d_w_id and ol_d_id = d_id and ol_o_id >= d_next_o_id - 20 and ol_o_id < d_next_o_id where d_w_id = 1 and d_id = 1))", 82 }, { 83 input: "create account `abc@124` admin_name `abc@124` identified by '111'", 84 output: "create account abc@124 admin_name 'abc@124' identified by '111'", 85 }, { 86 input: "create account account ADMIN_NAME 'root' IDENTIFIED BY '123456';", 87 output: "create account account admin_name 'root' identified by '123456'", 88 }, { 89 input: "drop table if exists history", 90 }, { 91 input: "create user daisy@192.168.1.10 identified by '123456'", 92 }, { 93 input: "create table t0 (a float(255, 3))", 94 }, { 95 input: "SELECT id,name,view_type,attribute,attribute_filed,size,created_at,updated_at FROM view_warehouse limit 0,10", 96 output: "select id, name, view_type, attribute, attribute_filed, size, created_at, updated_at from view_warehouse limit 10 offset 0", 97 }, { 98 input: "select algo_alarm_record.* from algo_alarm_record inner join (SELECT id FROM algo_alarm_record use index(algo_alarm_record_algo_id_first_id_created_at_index) WHERE first_id = 0 AND created_at >= '2022-09-18 00:00:00' AND created_at <= '2022-10-18 00:00:00' and algo_id not in (9808,9809) order by id desc limit 0,10 ) e on e.id = algo_alarm_record.id order by algo_alarm_record.id desc;", 99 output: "select algo_alarm_record.* from algo_alarm_record inner join (select id from algo_alarm_record use index(algo_alarm_record_algo_id_first_id_created_at_index) where first_id = 0 and created_at >= 2022-09-18 00:00:00 and created_at <= 2022-10-18 00:00:00 and algo_id not in (9808, 9809) order by id desc limit 10 offset 0) as e on e.id = algo_alarm_record.id order by algo_alarm_record.id desc", 100 }, { 101 input: "select a from t1 use index(b)", 102 }, { 103 input: "SELECT id,cid,status,ip,stream FROM camera WHERE (cid_type = ?)", 104 output: "select id, cid, status, ip, stream from camera where (cid_type = ?)", 105 }, { 106 input: "CREATE \nVIEW `xab0100` AS (\n select `a`.`SYSUSERID` AS `sysuserid`,`a`.`USERID` AS `userid`,`a`.`USERNAME` AS `usernm`,`a`.`PWDHASH` AS `userpwd`,`a`.`USERTYPE` AS `usertype`,`a`.`EMPID` AS `empid`,`a`.`EMAIL` AS `email`,`a`.`TELO` AS `telo`,`a`.`TELH` AS `telh`,`a`.`MOBIL` AS `mobil`,(case `a`.`ACTIVED` when '1' then 'N' when '2' then 'Y' else 'Y' end) AS `useyn`,`a`.`ENABLEPWD` AS `enablepwd`,`a`.`ENABLEMMSG` AS `enablemmsg`,`a`.`FEECENTER` AS `feecenter`,left(concat(ifnull(`c`.`ORGID`,''),'|'),(char_length(concat(ifnull(`c`.`ORGID`,''),'|')) - 1)) AS `orgid`,left(concat(ifnull(`c`.`ORGNAME`,''),'|'),(char_length(concat(ifnull(`c`.`ORGNAME`,''),'|')) - 1)) AS `orgname`,ifnull(`a`.`ISPLANNER`,'') AS `isplanner`,ifnull(`a`.`ISWHEMPLOYEE`,'') AS `iswhemployee`,ifnull(`a`.`ISBUYER`,'') AS `isbuyer`,ifnull(`a`.`ISQCEMPLOYEE`,'') AS `isqceemployee`,ifnull(`a`.`ISSALEEMPLOYEE`,'') AS `issaleemployee`,`a`.`SEX` AS `sex`,ifnull(`c`.`ENTID`,'3') AS `ORGANIZATION_ID`,ifnull(`a`.`NOTICEUSER`,'') AS `NOTICEUSER` \n from ((`kaf_cpcuser` `a` left join `kaf_cpcorguser` `b` on((`a`.`SYSUSERID` = `b`.`SYSUSERID`))) left join `kaf_cpcorg` `c` on((`b`.`ORGID` = `c`.`ORGID`))) \n order by `a`.`SYSUSERID`,`a`.`USERID`,`a`.`USERNAME`,`a`.`USERPASS`,`a`.`USERTYPE`,`a`.`EMPID`,`a`.`EMAIL`,`a`.`TELO`,`a`.`TELH`,`a`.`MOBIL`,`a`.`ACTIVED`,`a`.`ENABLEPWD`,`a`.`ENABLEMMSG`,`a`.`FEECENTER`,`a`.`ISPLANNER`,`a`.`ISWHEMPLOYEE`,`a`.`ISBUYER`,`a`.`ISQCEMPLOYEE`,`a`.`ISSALEEMPLOYEE`,`a`.`SEX`,`c`.`ENTID`) ;\n", 107 output: "create view xab0100 as (select a.SYSUSERID as sysuserid, a.USERID as userid, a.USERNAME as usernm, a.PWDHASH as userpwd, a.USERTYPE as usertype, a.EMPID as empid, a.EMAIL as email, a.TELO as telo, a.TELH as telh, a.MOBIL as mobil, (case a.ACTIVED when 1 then N when 2 then Y else Y end) as useyn, a.ENABLEPWD as enablepwd, a.ENABLEMMSG as enablemmsg, a.FEECENTER as feecenter, left(concat(ifnull(c.ORGID, ), |), (char_length(concat(ifnull(c.ORGID, ), |)) - 1)) as orgid, left(concat(ifnull(c.ORGNAME, ), |), (char_length(concat(ifnull(c.ORGNAME, ), |)) - 1)) as orgname, ifnull(a.ISPLANNER, ) as isplanner, ifnull(a.ISWHEMPLOYEE, ) as iswhemployee, ifnull(a.ISBUYER, ) as isbuyer, ifnull(a.ISQCEMPLOYEE, ) as isqceemployee, ifnull(a.ISSALEEMPLOYEE, ) as issaleemployee, a.SEX as sex, ifnull(c.ENTID, 3) as ORGANIZATION_ID, ifnull(a.NOTICEUSER, ) as NOTICEUSER from kaf_cpcuser as a left join kaf_cpcorguser as b on ((a.SYSUSERID = b.SYSUSERID)) left join kaf_cpcorg as c on ((b.ORGID = c.ORGID)) order by a.SYSUSERID, a.USERID, a.USERNAME, a.USERPASS, a.USERTYPE, a.EMPID, a.EMAIL, a.TELO, a.TELH, a.MOBIL, a.ACTIVED, a.ENABLEPWD, a.ENABLEMMSG, a.FEECENTER, a.ISPLANNER, a.ISWHEMPLOYEE, a.ISBUYER, a.ISQCEMPLOYEE, a.ISSALEEMPLOYEE, a.SEX, c.ENTID)", 108 }, { 109 input: "ALTER \nVIEW `xab0100` AS (\n select `a`.`SYSUSERID` AS `sysuserid`,`a`.`USERID` AS `userid`,`a`.`USERNAME` AS `usernm`,`a`.`PWDHASH` AS `userpwd`,`a`.`USERTYPE` AS `usertype`,`a`.`EMPID` AS `empid`,`a`.`EMAIL` AS `email`,`a`.`TELO` AS `telo`,`a`.`TELH` AS `telh`,`a`.`MOBIL` AS `mobil`,(case `a`.`ACTIVED` when '1' then 'N' when '2' then 'Y' else 'Y' end) AS `useyn`,`a`.`ENABLEPWD` AS `enablepwd`,`a`.`ENABLEMMSG` AS `enablemmsg`,`a`.`FEECENTER` AS `feecenter`,left(concat(ifnull(`c`.`ORGID`,''),'|'),(char_length(concat(ifnull(`c`.`ORGID`,''),'|')) - 1)) AS `orgid`,left(concat(ifnull(`c`.`ORGNAME`,''),'|'),(char_length(concat(ifnull(`c`.`ORGNAME`,''),'|')) - 1)) AS `orgname`,ifnull(`a`.`ISPLANNER`,'') AS `isplanner`,ifnull(`a`.`ISWHEMPLOYEE`,'') AS `iswhemployee`,ifnull(`a`.`ISBUYER`,'') AS `isbuyer`,ifnull(`a`.`ISQCEMPLOYEE`,'') AS `isqceemployee`,ifnull(`a`.`ISSALEEMPLOYEE`,'') AS `issaleemployee`,`a`.`SEX` AS `sex`,ifnull(`c`.`ENTID`,'3') AS `ORGANIZATION_ID`,ifnull(`a`.`NOTICEUSER`,'') AS `NOTICEUSER` \n from ((`kaf_cpcuser` `a` left join `kaf_cpcorguser` `b` on((`a`.`SYSUSERID` = `b`.`SYSUSERID`))) left join `kaf_cpcorg` `c` on((`b`.`ORGID` = `c`.`ORGID`))) \n order by `a`.`SYSUSERID`,`a`.`USERID`,`a`.`USERNAME`,`a`.`USERPASS`,`a`.`USERTYPE`,`a`.`EMPID`,`a`.`EMAIL`,`a`.`TELO`,`a`.`TELH`,`a`.`MOBIL`,`a`.`ACTIVED`,`a`.`ENABLEPWD`,`a`.`ENABLEMMSG`,`a`.`FEECENTER`,`a`.`ISPLANNER`,`a`.`ISWHEMPLOYEE`,`a`.`ISBUYER`,`a`.`ISQCEMPLOYEE`,`a`.`ISSALEEMPLOYEE`,`a`.`SEX`,`c`.`ENTID`) ;\n", 110 output: "alter view xab0100 as (select a.SYSUSERID as sysuserid, a.USERID as userid, a.USERNAME as usernm, a.PWDHASH as userpwd, a.USERTYPE as usertype, a.EMPID as empid, a.EMAIL as email, a.TELO as telo, a.TELH as telh, a.MOBIL as mobil, (case a.ACTIVED when 1 then N when 2 then Y else Y end) as useyn, a.ENABLEPWD as enablepwd, a.ENABLEMMSG as enablemmsg, a.FEECENTER as feecenter, left(concat(ifnull(c.ORGID, ), |), (char_length(concat(ifnull(c.ORGID, ), |)) - 1)) as orgid, left(concat(ifnull(c.ORGNAME, ), |), (char_length(concat(ifnull(c.ORGNAME, ), |)) - 1)) as orgname, ifnull(a.ISPLANNER, ) as isplanner, ifnull(a.ISWHEMPLOYEE, ) as iswhemployee, ifnull(a.ISBUYER, ) as isbuyer, ifnull(a.ISQCEMPLOYEE, ) as isqceemployee, ifnull(a.ISSALEEMPLOYEE, ) as issaleemployee, a.SEX as sex, ifnull(c.ENTID, 3) as ORGANIZATION_ID, ifnull(a.NOTICEUSER, ) as NOTICEUSER from kaf_cpcuser as a left join kaf_cpcorguser as b on ((a.SYSUSERID = b.SYSUSERID)) left join kaf_cpcorg as c on ((b.ORGID = c.ORGID)) order by a.SYSUSERID, a.USERID, a.USERNAME, a.USERPASS, a.USERTYPE, a.EMPID, a.EMAIL, a.TELO, a.TELH, a.MOBIL, a.ACTIVED, a.ENABLEPWD, a.ENABLEMMSG, a.FEECENTER, a.ISPLANNER, a.ISWHEMPLOYEE, a.ISBUYER, a.ISQCEMPLOYEE, a.ISSALEEMPLOYEE, a.SEX, c.ENTID)", 111 }, { 112 input: "select time from t1 as value", 113 }, { 114 input: "alter database test set mysql_compatbility_mode = '{transaction_isolation: REPEATABLE-READ, lower_case_table_names: 0}'", 115 output: "alter database configuration for test as {transaction_isolation: REPEATABLE-READ, lower_case_table_names: 0} ", 116 }, { 117 input: "show profiles", 118 }, { 119 input: "show privileges", 120 }, { 121 input: "show events from db1", 122 }, { 123 input: "show collation", 124 }, { 125 input: "show plugins", 126 }, { 127 input: "show procedure status", 128 }, { 129 input: "show triggers from db1 where 1", 130 }, { 131 input: "show engines", 132 }, { 133 input: "show config", 134 }, { 135 input: "show grants", 136 }, { 137 input: "show grants for 'test'@'localhost'", 138 output: "show grants for test@localhost", 139 }, { 140 input: "show table status from t1", 141 }, { 142 input: "show table status from t1", 143 }, { 144 input: "grant connect on account * to role_r1", 145 }, { 146 input: "select password from t1", 147 }, { 148 input: "create table t1 (a datetime on update CURRENT_TIMESTAMP(1))", 149 output: "create table t1 (a datetime(26) on update current_timestamp(1))", 150 }, { 151 input: `create table table10 (a int primary key, b varchar(10)) checksum=0 COMMENT="asdf"`, 152 output: "create table table10 (a int primary key, b varchar(10)) checksum = 0 comment = asdf", 153 }, { 154 input: "commit work", 155 output: "commit", 156 }, { 157 input: "select * from tables", 158 }, { 159 input: "update t1 set a = default", 160 }, { 161 input: "truncate t1", 162 output: "truncate table t1", 163 }, { 164 input: "truncate table t1", 165 output: "truncate table t1", 166 }, { 167 input: "truncate db1.t1", 168 output: "truncate table db1.t1", 169 }, { 170 input: "truncate table db1.t1", 171 output: "truncate table db1.t1", 172 }, { 173 input: "explain select * from emp", 174 output: "explain select * from emp", 175 }, { 176 input: "explain verbose select * from emp", 177 output: "explain (verbose) select * from emp", 178 }, { 179 input: "explain analyze select * from emp", 180 output: "explain (analyze) select * from emp", 181 }, { 182 input: "explain analyze verbose select * from emp", 183 output: "explain (analyze,verbose) select * from emp", 184 }, { 185 input: "explain (analyze true,verbose false) select * from emp", 186 output: "explain (analyze true,verbose false) select * from emp", 187 }, { 188 input: "explain (analyze true,verbose false,format json) select * from emp", 189 output: "explain (analyze true,verbose false,format json) select * from emp", 190 }, { 191 input: "with t11 as (select * from t1) update t11 join t2 on t11.a = t2.b set t11.b = 1 where t2.a > 1", 192 output: "with t11 as (select * from t1) update t11 inner join t2 on t11.a = t2.b set t11.b = 1 where t2.a > 1", 193 }, { 194 input: "UPDATE items,(SELECT id FROM items WHERE id IN (SELECT id FROM items WHERE retail / wholesale >= 1.3 AND quantity < 100)) AS discounted SET items.retail = items.retail * 0.9 WHERE items.id = discounted.id", 195 output: "update items cross join (select id from items where id in (select id from items where retail / wholesale >= 1.3 and quantity < 100)) as discounted set items.retail = items.retail * 0.9 where items.id = discounted.id", 196 }, { 197 input: "with t2 as (select * from t1) DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a1.id=a2.id;", 198 output: "with t2 as (select * from t1) delete from a1, a2 using t1 as a1 inner join t2 as a2 where a1.id = a2.id", 199 }, { 200 input: "DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a1.id=a2.id;", 201 output: "delete from a1, a2 using t1 as a1 inner join t2 as a2 where a1.id = a2.id", 202 }, { 203 input: "DELETE a1, a2 FROM t1 AS a1 INNER JOIN t2 AS a2 WHERE a1.id=a2.id", 204 output: "delete from a1, a2 using t1 as a1 inner join t2 as a2 where a1.id = a2.id", 205 }, { 206 input: "DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id", 207 output: "delete from t1, t2 using t1 inner join t2 inner join t3 where t1.id = t2.id and t2.id = t3.id", 208 }, { 209 input: "DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id", 210 output: "delete from t1, t2 using t1 inner join t2 inner join t3 where t1.id = t2.id and t2.id = t3.id", 211 }, { 212 input: "select cast(false as varchar)", 213 }, { 214 input: "select cast(a as timestamp)", 215 output: "select cast(a as timestamp(26))", 216 }, { 217 input: "select cast(\"2022-01-30\" as varchar);", 218 output: "select cast(2022-01-30 as varchar)", 219 }, { 220 input: "select cast(b as timestamp) from t2", 221 output: "select cast(b as timestamp(26)) from t2", 222 }, { 223 input: "select cast(\"2022-01-01 01:23:34\" as varchar)", 224 output: "select cast(2022-01-01 01:23:34 as varchar)", 225 }, { 226 input: "select binary('Geeksforgeeks')", 227 output: "select cast(Geeksforgeeks as binary)", 228 }, { 229 input: "show schemas where 1", 230 output: "show databases where 1", 231 }, { 232 input: "select role from t1", 233 }, { 234 input: "select a || 'hello' || 'world' from t1;", 235 output: "select concat(concat(a, hello), world) from t1", 236 }, { 237 input: "select col || 'bar'", 238 output: "select concat(col, bar)", 239 }, { 240 input: "select 'foo' || 'bar'", 241 output: "select concat(foo, bar)", 242 }, { 243 input: "select 'a\\'b'", 244 output: "select a'b", 245 }, { 246 input: "select char_length('\\n\\t\\r\\b\\0\\_\\%\\\\');", 247 output: "select char_length(\\n\\t\\r\\b\\0\\_\\%\\\\)", 248 }, { 249 input: "select CAST('10 ' as unsigned);", 250 output: "select cast(10 as unsigned)", 251 }, { 252 input: "select CAST('10 ' as unsigned integer);", 253 output: "select cast(10 as integer unsigned)", 254 }, { 255 input: "SELECT ((+0) IN ((0b111111111111111111111111111111111111111111111111111),(rpad(1.0,2048,1)), (32767.1)));", 256 output: "select ((+0) in ((0b111111111111111111111111111111111111111111111111111), (rpad(1.0, 2048, 1)), (32767.1)))", 257 }, { 258 input: "select 0b111111111111111111111111111111111111111111111111111", 259 }, { 260 input: "select date,format,to_date(date, format) as to_date from t1;", 261 output: "select date, format, to_date(date, format) as to_date from t1", 262 }, { 263 input: "select date,format,concat_ws(',',to_date(date, format)) as con from t1;", 264 output: "select date, format, concat_ws(,, to_date(date, format)) as con from t1", 265 }, { 266 input: "select date,format,to_date(date, format) as to_date from t1;", 267 output: "select date, format, to_date(date, format) as to_date from t1", 268 }, { 269 input: "select date,format,concat_ws(\" \",to_date(date, format),'') as con from t1;", 270 output: "select date, format, concat_ws( , to_date(date, format), ) as con from t1", 271 }, { 272 input: "select schema()", 273 }, { 274 input: "select last_insert_id()", 275 }, { 276 input: "show char set where charset = 'utf8mb4'", 277 output: "show charset where charset = utf8mb4", 278 }, { 279 input: "show charset where charset = 'utf8mb4'", 280 output: "show charset where charset = utf8mb4", 281 }, { 282 input: "show character set where charset = 'utf8mb4'", 283 output: "show charset where charset = utf8mb4", 284 }, { 285 input: "show config where a > 1", 286 }, { 287 input: "set @@a = b", 288 output: "set a = b", 289 }, { 290 input: "set @a = b", 291 output: "set a = b", 292 }, { 293 input: "CREATE TABLE t1 (datetime datetime, timestamp timestamp, date date)", 294 output: "create table t1 (datetime datetime(26), timestamp timestamp(26), date date)", 295 }, { 296 input: "SET timestamp=DEFAULT;", 297 output: "set timestamp = default", 298 }, { 299 input: "SET timestamp=UNIX_TIMESTAMP('2011-07-31 10:00:00')", 300 output: "set timestamp = unix_timestamp(2011-07-31 10:00:00)", 301 }, { 302 input: "select ltrim(\"a\"),rtrim(\"a\"),trim(BOTH \"\" from \"a\"),trim(BOTH \" \" from \"a\");", 303 output: "select ltrim(a), rtrim(a), trim(both from a), trim(both from a)", 304 }, { 305 input: "select rpad('hello', -18446744073709551616, '1');", 306 output: "select rpad(hello, -18446744073709551616, 1)", 307 }, { 308 input: "select rpad('hello', -18446744073709551616, '1');", 309 output: "select rpad(hello, -18446744073709551616, 1)", 310 }, { 311 input: "SELECT CONCAT_WS(1471290948102948112341241204312904-23412412-4141, \"a\", \"b\")", 312 output: "select concat_ws(1471290948102948112341241204312904 - 23412412 - 4141, a, b)", 313 }, { 314 input: "SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );", 315 output: "select * from t1 where a = any (select 1 union (select 1 union select 1))", 316 }, { 317 input: "SELECT * FROM t1 WHERE a = ANY ( SELECT 1 except ( SELECT 1 except SELECT 1 ) );", 318 output: "select * from t1 where a = any (select 1 except (select 1 except select 1))", 319 }, { 320 input: "SELECT * FROM t1 WHERE a = ANY ( SELECT 1 intersect ( SELECT 1 intersect SELECT 1 ) );", 321 output: "select * from t1 where a = any (select 1 intersect (select 1 intersect select 1))", 322 }, { 323 input: "SELECT * FROM t1 WHERE a = ANY ( SELECT 1 minus ( SELECT 1 minus SELECT 1 ) );", 324 output: "select * from t1 where a = any (select 1 minus (select 1 minus select 1))", 325 }, { 326 input: "SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);", 327 output: "select * from t1 where (a, b) = any (select a, max(b) from t1 group by a)", 328 }, { 329 input: "select (1,2) != ALL (select * from t1);", 330 output: "select (1, 2) != all (select * from t1)", 331 }, { 332 input: "select s1, s1 = ANY (SELECT s1 FROM t2) from t1;", 333 output: "select s1, s1 = any (select s1 from t2) from t1", 334 }, { 335 input: "select * from t3 where a >= some (select b from t2);", 336 output: "select * from t3 where a >= some (select b from t2)", 337 }, { 338 input: "select 9999999999999999999;", 339 output: "select 9999999999999999999", 340 }, { 341 input: "select substring('hello', -18446744073709551616, -18446744073709551616);", 342 output: "select substring(hello, -18446744073709551616, -18446744073709551616)", 343 }, { 344 input: "select substring('hello', -18446744073709551616, 1);", 345 output: "select substring(hello, -18446744073709551616, 1)", 346 }, { 347 input: "select space(18446744073709551616);", 348 output: "select space(18446744073709551616)", 349 }, { 350 input: "select space(-18446744073709551616);", 351 output: "select space(-18446744073709551616)", 352 }, { 353 input: "select ltrim(\"a\"),rtrim(\"a\"),trim(BOTH \"\" from \"a\"),trim(BOTH \" \" from \"a\");", 354 output: "select ltrim(a), rtrim(a), trim(both from a), trim(both from a)", 355 }, { 356 input: "SELECT (rpad(1.0, 2048,1)) IS NOT FALSE;", 357 output: "select (rpad(1.0, 2048, 1)) is not false", 358 }, { 359 input: "SELECT 1 is unknown;", 360 output: "select 1 is unknown", 361 }, { 362 input: "SELECT false is not unknown;", 363 output: "select false is not unknown", 364 }, { 365 input: "SELECT 1 is true;", 366 output: "select 1 is true", 367 }, { 368 input: "SELECT false is not true;", 369 output: "select false is not true", 370 }, { 371 input: "SELECT 1 is false;", 372 output: "select 1 is false", 373 }, { 374 input: "SELECT false is not false;", 375 output: "select false is not false", 376 }, { 377 input: "SELECT FROM_UNIXTIME(99999999999999999999999999999999999999999999999999999999999999999);", 378 output: "select from_unixtime(99999999999999999999999999999999999999999999999999999999999999999)", 379 }, { 380 input: "SELECT FROM_UNIXTIME(2147483647) AS c1, FROM_UNIXTIME(2147483648) AS c2, FROM_UNIXTIME(2147483647.9999999) AS c3, FROM_UNIXTIME(32536771199) AS c4,FROM_UNIXTIME(32536771199.9999999) AS c5;", 381 output: "select from_unixtime(2147483647) as c1, from_unixtime(2147483648) as c2, from_unixtime(2147483647.9999999) as c3, from_unixtime(32536771199) as c4, from_unixtime(32536771199.9999999) as c5", 382 }, { 383 input: "select date_add(\"1997-12-31 23:59:59\",INTERVAL -100000 YEAR);", 384 output: "select date_add(1997-12-31 23:59:59, interval(-100000, year))", 385 }, { 386 input: "SELECT ADDDATE(DATE'2021-01-01', INTERVAL 1 DAY);", 387 output: "select adddate(date(2021-01-01), interval(1, day))", 388 }, { 389 input: "select '2007-01-01' + interval a day from t1;", 390 output: "select 2007-01-01 + interval(a, day) from t1", 391 }, { 392 input: "SELECT CAST(COALESCE(t0.c0, -1) AS UNSIGNED) IS TRUE FROM t0;", 393 output: "select cast(coalesce(t0.c0, -1) as unsigned) is true from t0", 394 }, { 395 input: "select Fld1, variance(Fld2) as q from t1 group by Fld1 having q is not null;", 396 output: "select fld1, variance(fld2) as q from t1 group by fld1 having q is not null", 397 }, { 398 input: "select variance(-99999999999999999.99999);", 399 output: "select variance(-99999999999999999.99999)", 400 }, { 401 input: "select Fld1, std(Fld2) from t1 group by Fld1 having variance(Fld2) is not null", 402 output: "select fld1, std(fld2) from t1 group by fld1 having variance(fld2) is not null", 403 }, { 404 input: "select a.f1 as a, a.f1 > b.f1 as gt, a.f1 < b.f1 as lt, a.f1<=>b.f1 as eq from t1 a, t1 b;", 405 output: "select a.f1 as a, a.f1 > b.f1 as gt, a.f1 < b.f1 as lt, a.f1 <=> b.f1 as eq from t1 as a cross join t1 as b", 406 }, { 407 input: "select var_samp(s) as '0.5', var_pop(s) as '0.25' from bug22555;", 408 output: "select var_samp(s) as 0.5, var_pop(s) as 0.25 from bug22555", 409 }, { 410 input: "select var_samp(s) as 'null', var_pop(s) as 'null' from bug22555;", 411 output: "select var_samp(s) as null, var_pop(s) as null from bug22555", 412 }, { 413 input: "select cast(variance(ff) as decimal(10, 3)) from t2", 414 }, { 415 input: "SELECT GROUP_CONCAT(DISTINCT 2) from t1", 416 output: "select group_concat(distinct 2, ,) from t1", 417 }, { 418 input: "SELECT GROUP_CONCAT(DISTINCT a order by a) from t1", 419 output: "select group_concat(distinct a, ,) from t1", 420 }, { 421 input: "select variance(2) from t1", 422 }, { 423 input: "select SQL_BIG_RESULT bit_and(col), bit_or(col) from t1 group by col;", 424 output: "select sql_big_result bit_and(col), bit_or(col) from t1 group by col", 425 }, { 426 input: "select sql_small_result t2.id, avg(rating + 0.0e0) from t2 group by t2.id", 427 }, { 428 input: "select sql_small_result t2.id, avg(rating) from t2 group by t2.id", 429 }, { 430 input: "select any_value(name), avg(value1), std(value1), variance(value1) from t1, t2 where t1.id = t2.id group by t1.id", 431 output: "select any_value(name), avg(value1), std(value1), variance(value1) from t1 cross join t2 where t1.id = t2.id group by t1.id", 432 }, { 433 input: "select id, avg(value1), std(value1), variance(value1) from t1 group by id", 434 }, { 435 input: "select i, count(*), std(s1 / s2) from bug22555 group by i order by i", 436 }, { 437 input: "select i, count(*), variance(s1 / s2) from bug22555 group by i order by i", 438 }, { 439 input: "select i, count(*), variance(s1 / s2) from bug22555 group by i order by i", 440 }, { 441 input: "select name, avg(value1), std(value1), variance(value1) from t1, t2 where t1.id = t2.id group by t1.id", 442 output: "select name, avg(value1), std(value1), variance(value1) from t1 cross join t2 where t1.id = t2.id group by t1.id", 443 }, { 444 input: "select sum(all a),count(all a),avg(all a),std(all a),variance(all a),bit_or(all a),bit_and(all a),min(all a),max(all a),min(all c),max(all c) from t", 445 output: "select sum(all a), count(all a), avg(all a), std(all a), variance(all a), bit_or(all a), bit_and(all a), min(all a), max(all a), min(all c), max(all c) from t", 446 }, { 447 input: "insert into t1 values (date_add(NULL, INTERVAL 1 DAY));", 448 output: "insert into t1 values (date_add(null, interval(1, day)))", 449 }, { 450 input: "replace into t1 values (date_add(NULL, INTERVAL 1 DAY));", 451 output: "replace into t1 values (date_add(null, interval(1, day)))", 452 }, { 453 input: "SELECT DATE_ADD('2022-02-28 23:59:59.9999', INTERVAL 1 SECOND) '1 second later';", 454 output: "select date_add(2022-02-28 23:59:59.9999, interval(1, second)) as 1 second later", 455 }, { 456 input: "SELECT sum(a) as 'hello' from t1;", 457 output: "select sum(a) as hello from t1", 458 }, { 459 input: "SELECT DATE_ADD(\"2017-06-15\", INTERVAL -10 MONTH);", 460 output: "select date_add(2017-06-15, interval(-10, month))", 461 }, { 462 input: "create table t1 (a varchar)", 463 output: "create table t1 (a varchar)", 464 }, { 465 input: "SELECT (CAST(0x7FFFFFFFFFFFFFFF AS char));", 466 output: "select (cast(0x7fffffffffffffff as char))", 467 }, { 468 input: "select cast(-19999999999999999999 as signed);", 469 output: "select cast(-19999999999999999999 as signed)", 470 }, { 471 input: "select cast(19999999999999999999 as signed);", 472 output: "select cast(19999999999999999999 as signed)", 473 }, { 474 input: "select date_sub(now(), interval 1 day) from t1;", 475 output: "select date_sub(now(), interval(1, day)) from t1", 476 }, { 477 input: "select date_sub(now(), interval '1' day) from t1;", 478 output: "select date_sub(now(), interval(1, day)) from t1", 479 }, { 480 input: "select date_add(now(), interval '1' day) from t1;", 481 output: "select date_add(now(), interval(1, day)) from t1", 482 }, { 483 input: "SELECT md.datname as `Database` FROM TT md", 484 output: "select md.datname as Database from tt as md", 485 }, { 486 input: "select * from t where a = `Hello`", 487 output: "select * from t where a = Hello", 488 }, { 489 input: "CREATE VIEW v AS SELECT * FROM t WHERE t.id = f(t.name);", 490 output: "create view v as select * from t where t.id = f(t.name)", 491 }, { 492 input: "ALTER VIEW v AS SELECT * FROM t WHERE t.id = f(t.name);", 493 output: "alter view v as select * from t where t.id = f(t.name)", 494 }, { 495 input: "CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;", 496 output: "create view v as select qty, price, qty * price as value from t", 497 }, { 498 input: "ALTER VIEW v AS SELECT qty, price, qty*price AS value FROM t;", 499 output: "alter view v as select qty, price, qty * price as value from t", 500 }, { 501 input: "create view v_today (today) as select current_day from t", 502 }, { 503 input: "alter view v_today (today) as select current_day from t", 504 }, { 505 input: "explain (analyze true,verbose false) select * from emp", 506 }, { 507 input: "select quarter from ontime limit 1", 508 }, { 509 input: "select month from ontime limit 1", 510 }, { 511 input: "with tw as (select * from t2), tf as (select * from t3) select * from tw where a > 1", 512 }, { 513 input: "with tw as (select * from t2) select * from tw where a > 1", 514 }, { 515 input: "create table t (a double(13)) // comment", 516 output: "create table t (a double(13))", 517 }, { 518 input: "create table t (a double(13)) -- comment", 519 output: "create table t (a double(13))", 520 }, { 521 input: "select a as promo_revenue from (select * from r) as c_orders(c_custkey, c_count)", 522 }, { 523 input: "select extract(year from l_shipdate) as l_year from t", 524 output: "select extract(year, l_shipdate) as l_year from t", 525 }, { 526 input: "select * from R join S on R.uid = S.uid where l_shipdate <= date '1998-12-01' - interval '112' day", 527 output: "select * from r inner join s on r.uid = s.uid where l_shipdate <= date(1998-12-01) - interval(112, day)", 528 }, { 529 input: "create table deci_table (a decimal(10, 5))", 530 }, { 531 input: "create table deci_table (a decimal(20, 5))", 532 }, { 533 input: "create table deci_table (a decimal)", 534 output: "create table deci_table (a decimal(34))", 535 }, { 536 input: "create table deci_table (a decimal(20))", 537 }, { 538 input: "select substr(name, 5) from t1", 539 }, { 540 input: "select substring(name, 5) from t1", 541 }, { 542 input: "select substr(name, 5, 3) from t1", 543 }, { 544 input: "select substring(name, 5, 3) from t1", 545 }, { 546 input: "select * from R join S on R.uid = S.uid", 547 output: "select * from r inner join s on r.uid = s.uid", 548 }, { 549 input: "create table t (a int, b char, key idx1 type zonemap (a, b))", 550 output: "create table t (a int, b char, index idx1 using zonemap (a, b))", 551 }, { 552 input: "create table t (a int, index idx1 using zonemap (a))", 553 }, { 554 input: "create table t (a int, index idx1 using bsi (a))", 555 }, { 556 input: "set @@sql_mode ='TRADITIONAL'", 557 output: "set sql_mode = TRADITIONAL", 558 }, { 559 input: "set @@session.sql_mode ='TRADITIONAL'", 560 output: "set sql_mode = TRADITIONAL", 561 }, { 562 input: "set session sql_mode ='TRADITIONAL'", 563 output: "set sql_mode = TRADITIONAL", 564 }, { 565 input: "select @session.tx_isolation", 566 output: "select @session.tx_isolation", 567 }, { 568 input: "select @@session.tx_isolation", 569 output: "select @@tx_isolation", 570 }, { 571 input: "/* mysql-connector-java-8.0.27 (Revision: e920b979015ae7117d60d72bcc8f077a839cd791) */SHOW VARIABLES;", 572 output: "show variables", 573 }, { 574 input: "create index idx1 using bsi on a (a) ", 575 }, { 576 input: "INSERT INTO pet VALUES row('Sunsweet05','Dsant05','otter','f',30.11,2), row('Sunsweet06','Dsant06','otter','m',30.11,3);", 577 output: "insert into pet values (Sunsweet05, Dsant05, otter, f, 30.11, 2), (Sunsweet06, Dsant06, otter, m, 30.11, 3)", 578 }, { 579 input: "INSERT INTO t1 SET f1 = -1.0e+30, f2 = 'exore', f3 = 123", 580 output: "insert into t1 (f1, f2, f3) values (-1.0e+30, exore, 123)", 581 }, { 582 input: "INSERT INTO t1 SET f1 = -1;", 583 output: "insert into t1 (f1) values (-1)", 584 }, { 585 input: "INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b), b=VALUES(a)+VALUES(c);", 586 output: "insert into t1 (a, b, c) values (1, 2, 3), (4, 5, 6) on duplicate key update c = values(a) + values(b), b = values(a) + values(c)", 587 }, { 588 input: "INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=2, b=3;", 589 output: "insert into t1 (a, b, c) values (1, 2, 3), (4, 5, 6) on duplicate key update c = 2, b = 3", 590 }, { 591 input: "INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=2/2, b=3;", 592 output: "insert into t1 (a, b, c) values (1, 2, 3), (4, 5, 6) on duplicate key update c = 2 / 2, b = 3", 593 }, { 594 input: "insert into t1 values (18446744073709551615), (0xFFFFFFFFFFFFFFFE), (18446744073709551613), (18446744073709551612)", 595 output: "insert into t1 values (18446744073709551615), (0xfffffffffffffffe), (18446744073709551613), (18446744073709551612)", 596 }, { 597 input: "REPLACE INTO pet VALUES row('Sunsweet05','Dsant05','otter','f',30.11,2), row('Sunsweet06','Dsant06','otter','m',30.11,3);", 598 output: "replace into pet values (Sunsweet05, Dsant05, otter, f, 30.11, 2), (Sunsweet06, Dsant06, otter, m, 30.11, 3)", 599 }, { 600 input: "REPLACE INTO t1 SET f1 = -1.0e+30, f2 = 'exore', f3 = 123", 601 output: "replace into t1 (f1, f2, f3) values (-1.0e+30, exore, 123)", 602 }, { 603 input: "REPLACE INTO t1 SET f1 = -1;", 604 output: "replace into t1 (f1) values (-1)", 605 }, { 606 input: "replace into t1 values (18446744073709551615), (0xFFFFFFFFFFFFFFFE), (18446744073709551613), (18446744073709551612)", 607 output: "replace into t1 values (18446744073709551615), (0xfffffffffffffffe), (18446744073709551613), (18446744073709551612)", 608 }, { 609 input: "create table t (a int) properties(\"host\" = \"127.0.0.1\", \"port\" = \"8239\", \"user\" = \"mysql_user\", \"password\" = \"mysql_passwd\")", 610 output: "create table t (a int) properties(host = 127.0.0.1, port = 8239, user = mysql_user, password = mysql_passwd)", 611 }, { 612 input: "create table t (a int) properties('a' = 'b')", 613 output: "create table t (a int) properties(a = b)", 614 }, { 615 input: "create table t (a int, b char, check (1 + 1) enforced)", 616 }, { 617 input: "create table t (a int, b char, foreign key sdf (a, b) references b(a asc, b desc))", 618 }, { 619 input: "create table t (a int, b char, constraint sdf foreign key (a, b) references b(a asc, b desc))", 620 output: "create table t (a int, b char, foreign key sdf (a, b) references b(a asc, b desc))", 621 }, { 622 input: "create table t (a int, b char, constraint sdf foreign key dddd (a, b) references b(a asc, b desc))", 623 output: "create table t (a int, b char, foreign key sdf (a, b) references b(a asc, b desc))", 624 }, { 625 input: "create table t (a int, b char, unique key idx (a, b))", 626 }, { 627 input: "create table t (a int, b char, index if not exists idx (a, b))", 628 }, { 629 input: "create table t (a int, b char, fulltext idx (a, b))", 630 }, { 631 input: "create table t (a int, b char, constraint p1 primary key idx using hash (a, b))", 632 output: "create table t (a int, b char, primary key p1 using none (a, b))", 633 }, { 634 input: "create table t (a int, b char, primary key idx (a, b))", 635 }, { 636 input: "create external table t (a int) infile 'data.txt'", 637 output: "create external table t (a int) infile 'data.txt'", 638 }, { 639 input: "create external table t (a int) infile {'filepath'='data.txt', 'compression'='none'}", 640 }, { 641 input: "create external table t (a int) infile {'filepath'='data.txt', 'compression'='auto'}", 642 }, { 643 input: "create external table t (a int) infile {'filepath'='data.txt', 'compression'='lz4'}", 644 }, { 645 input: "create external table t (a int) infile 'data.txt' FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY ''", 646 output: "create external table t (a int) infile 'data.txt' fields terminated by \t optionally enclosed by \u0000 lines", 647 }, { 648 input: "SET NAMES 'utf8mb4' COLLATE 'utf8mb4_general_ci'", 649 output: "set names = utf8mb4 utf8mb4_general_ci", 650 }, { 651 input: "insert into cms values (null, default)", 652 }, { 653 input: "replace into cms values (null, default)", 654 }, { 655 input: "create database `show`", 656 output: "create database show", 657 }, { 658 input: "create table table16 (1a20 int, 1e int)", 659 }, { 660 input: "insert into t2 values (-3, 2)", 661 }, { 662 input: "replace into t2 values (-3, 2)", 663 }, { 664 input: "select spID,userID,score from t1 where spID>(userID-1);", 665 output: "select spid, userid, score from t1 where spid > (userid - 1)", 666 }, { 667 input: "CREATE TABLE t2(product VARCHAR(32),country_id INTEGER NOT NULL,year INTEGER,profit INTEGER)", 668 output: "create table t2 (product varchar(32), country_id integer not null, year integer, profit integer)", 669 }, { 670 input: "insert into numtable values (255, 65535, 4294967295, 18446744073709551615)", 671 }, { 672 input: "replace into numtable values (255, 65535, 4294967295, 18446744073709551615)", 673 }, { 674 input: "create table numtable (a tinyint unsigned, b smallint unsigned, c int unsigned, d bigint unsigned)", 675 }, { 676 input: "SELECT userID as user, MAX(score) as max FROM t1 GROUP BY userID order by user", 677 output: "select userid as user, max(score) as max from t1 group by userid order by user", 678 }, { 679 input: "load data infile 'test/loadfile5' ignore INTO TABLE T.A FIELDS TERMINATED BY ',' (@,@,c,d,e,f)", 680 output: "load data infile test/loadfile5 ignore into table t.a fields terminated by , (, , c, d, e, f)", 681 }, { 682 input: "load data infile '/root/lineorder_flat_10.tbl' into table lineorder_flat FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '';", 683 output: "load data infile /root/lineorder_flat_10.tbl into table lineorder_flat fields terminated by \t optionally enclosed by \u0000 lines", 684 }, { 685 input: "load data local infile 'data' replace into table db.a (a, b, @vc, @vd) set a = @vc != 0, d = @vd != 1", 686 output: "load data local infile data replace into table db.a (a, b, @vc, @vd) set a = @vc != 0, d = @vd != 1", 687 }, { 688 input: "load data local infile 'data' replace into table db.a lines starting by '#' terminated by '\t' ignore 2 lines", 689 output: "load data local infile data replace into table db.a lines starting by # terminated by ignore 2 lines", 690 }, { 691 input: "load data local infile 'data' replace into table db.a lines starting by '#' terminated by '\t' ignore 2 rows", 692 output: "load data local infile data replace into table db.a lines starting by # terminated by ignore 2 lines", 693 }, { 694 input: "load data local infile 'data' replace into table db.a lines terminated by '\t' starting by '#' ignore 2 lines", 695 output: "load data local infile data replace into table db.a lines starting by # terminated by ignore 2 lines", 696 }, { 697 input: "load data local infile 'data' replace into table db.a lines terminated by '\t' starting by '#' ignore 2 rows", 698 output: "load data local infile data replace into table db.a lines starting by # terminated by ignore 2 lines", 699 }, { 700 input: "load data infile 'data.txt' into table db.a fields terminated by '\t' escaped by '\t'", 701 output: "load data infile data.txt into table db.a fields terminated by \t escaped by \t", 702 }, { 703 input: "load data infile 'data.txt' into table db.a fields terminated by '\t' enclosed by '\t' escaped by '\t'", 704 output: "load data infile data.txt into table db.a fields terminated by \t enclosed by \t escaped by \t", 705 }, { 706 input: "load data infile 'data.txt' into table db.a", 707 output: "load data infile data.txt into table db.a", 708 }, { 709 input: "load data infile {'filepath'='data.txt', 'compression'='auto'} into table db.a", 710 }, { 711 input: "load data infile {'filepath'='data.txt', 'compression'='none'} into table db.a", 712 }, { 713 input: "create external table t (a int) infile 'data.txt'", 714 output: "create external table t (a int) infile 'data.txt'", 715 }, { 716 input: "create external table t (a int) infile {'filepath'='data.txt', 'compression'='none'}", 717 }, { 718 input: "create external table t (a int) infile {'filepath'='data.txt', 'compression'='auto'}", 719 }, { 720 input: "create external table t (a int) infile {'filepath'='data.txt', 'compression'='lz4'}", 721 }, { 722 input: "create external table t (a int) infile 'data.txt' FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY ''", 723 output: "create external table t (a int) infile 'data.txt' fields terminated by \t optionally enclosed by \u0000 lines", 724 }, { 725 input: "create external table t (a int) URL s3option{'endpoint'='s3.us-west-2.amazonaws.com', 'access_key_id'='XXX', 'secret_access_key'='XXX', 'bucket'='test', 'filepath'='*.txt', 'region'='us-west-2'}", 726 output: "create external table t (a int) url s3option {'endpoint'='s3.us-west-2.amazonaws.com', 'access_key_id'='XXX', 'secret_access_key'='XXX', 'bucket'='test', 'filepath'='*.txt', 'region'='us-west-2'}", 727 }, { 728 input: "load data infile 'test/loadfile5' ignore INTO TABLE T.A FIELDS TERMINATED BY ',' (@,@,c,d,e,f)", 729 output: "load data infile test/loadfile5 ignore into table t.a fields terminated by , (, , c, d, e, f)", 730 }, { 731 input: "load data infile '/root/lineorder_flat_10.tbl' into table lineorder_flat FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '';", 732 output: "load data infile /root/lineorder_flat_10.tbl into table lineorder_flat fields terminated by \t optionally enclosed by \u0000 lines", 733 }, { 734 input: "load data infile {'filepath'='data.txt', 'compression'='auto'} into table db.a", 735 }, { 736 input: "load data infile {'filepath'='data.txt', 'compression'='none'} into table db.a", 737 }, { 738 input: "load data infile {'filepath'='data.txt', 'compression'='GZIP'} into table db.a", 739 }, { 740 input: "load data infile {'filepath'='data.txt', 'compression'='BZIP2'} into table db.a", 741 }, { 742 input: "load data infile {'filepath'='data.txt', 'compression'='FLATE'} into table db.a", 743 }, { 744 input: "load data infile {'filepath'='data.txt', 'compression'='LZW'} into table db.a", 745 }, { 746 input: "load data infile {'filepath'='data.txt', 'compression'='ZLIB'} into table db.a", 747 }, { 748 input: "load data infile {'filepath'='data.txt', 'compression'='LZ4'} into table db.a", 749 }, { 750 input: "LOAD DATA URL s3option{'endpoint'='s3.us-west-2.amazonaws.com', 'access_key_id'='XXX', 'secret_access_key'='XXX', 'bucket'='test', 'filepath'='*.txt', 'region'='us-west-2'} into table db.a", 751 output: "load data url s3option {'endpoint'='s3.us-west-2.amazonaws.com', 'access_key_id'='XXX', 'secret_access_key'='XXX', 'bucket'='test', 'filepath'='*.txt', 'region'='us-west-2'} into table db.a", 752 }, 753 { 754 input: `load data url s3option {'endpoint'='s3.us-west-2.amazonaws.com', 'access_key_id'='XXX', 'secret_access_key'='XXX', 'bucket'='test', 'filepath'='jsonline/jsonline_object.jl', 'region'='us-west-2', 'compression'='none', 'format'='jsonline', 'jsondata'='object'} into table t1`, 755 }, { 756 input: "load data infile {'filepath'='data.txt', 'compression'='GZIP'} into table db.a", 757 }, { 758 input: "load data infile {'filepath'='data.txt', 'compression'='BZIP2'} into table db.a", 759 }, { 760 input: "load data infile {'filepath'='data.txt', 'compression'='FLATE'} into table db.a", 761 }, { 762 input: "load data infile {'filepath'='data.txt', 'compression'='LZW'} into table db.a", 763 }, { 764 input: "load data infile {'filepath'='data.txt', 'compression'='ZLIB'} into table db.a", 765 }, { 766 input: "load data infile {'filepath'='data.txt', 'compression'='LZ4'} into table db.a", 767 }, { 768 input: "load data infile {'filepath'='data.txt', 'format'='jsonline', 'jsondata'='array'} into table db.a", 769 }, 770 { 771 input: "load data infile {'filepath'='data.txt', 'format'='jsonline', 'jsondata'='object'} into table db.a", 772 }, 773 { 774 input: "load data infile {'filepath'='data.txt', 'compression'='BZIP2', 'format'='jsonline', 'jsondata'='object'} into table db.a", 775 }, 776 { 777 input: "import data infile '/root/lineorder_flat_10.tbl' into table lineorder_flat FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '';", 778 output: "import data infile /root/lineorder_flat_10.tbl into table lineorder_flat fields terminated by \t optionally enclosed by \u0000 lines", 779 }, { 780 input: "show tables from test01 where tables_in_test01 like '%t2%'", 781 output: "show tables from test01 where tables_in_test01 like %t2%", 782 }, { 783 input: "select userID,MAX(score) max_score from t1 where userID <2 || userID > 3 group by userID order by max_score", 784 output: "select userid, max(score) as max_score from t1 where concat(userid < 2, userid > 3) group by userid order by max_score", 785 }, { 786 input: "select c1, -c2 from t2 order by -c1 desc", 787 }, { 788 input: "select * from t1 where spID>2 AND userID <2 || userID >=2 OR userID < 2 limit 3", 789 output: "select * from t1 where concat(spid > 2 and userid < 2, userid >= 2) or userid < 2 limit 3", 790 }, { 791 input: "select * from t10 where (b='ba' or b='cb') and (c='dc' or c='ed');", 792 output: "select * from t10 where (b = ba or b = cb) and (c = dc or c = ed)", 793 }, { 794 input: "select CAST(userID AS DOUBLE) cast_double, CAST(userID AS FLOAT(3)) cast_float , CAST(userID AS REAL) cast_real, CAST(userID AS SIGNED) cast_signed, CAST(userID AS UNSIGNED) cast_unsigned from t1 limit 2", 795 output: "select cast(userid as double) as cast_double, cast(userid as float(3)) as cast_float, cast(userid as real) as cast_real, cast(userid as signed) as cast_signed, cast(userid as unsigned) as cast_unsigned from t1 limit 2", 796 }, { 797 input: "select distinct name as name1 from t1", 798 }, { 799 input: "select userID, userID DIV 2 as user_dir, userID%2 as user_percent, userID MOD 2 as user_mod from t1", 800 output: "select userid, userid div 2 as user_dir, userid % 2 as user_percent, userid % 2 as user_mod from t1", 801 }, { 802 input: "select sum(score) as sum from t1 where spID=6 group by score order by sum desc", 803 output: "select sum(score) as sum from t1 where spid = 6 group by score order by sum desc", 804 }, { 805 input: "select userID,count(score) from t1 where userID>2 group by userID having count(score)>1", 806 output: "select userid, count(score) from t1 where userid > 2 group by userid having count(score) > 1", 807 }, { 808 input: "SELECT product, SUM(profit),AVG(profit) FROM t2 where product<>'TV' GROUP BY product order by product asc", 809 output: "select product, sum(profit), avg(profit) from t2 where product != TV group by product order by product asc", 810 }, { 811 input: "SELECT product, SUM(profit),AVG(profit) FROM t2 where product='Phone' GROUP BY product order by product asc", 812 output: "select product, sum(profit), avg(profit) from t2 where product = Phone group by product order by product asc", 813 }, { 814 input: "select sum(col_1d),count(col_1d),avg(col_1d),min(col_1d),max(col_1d) from tbl1 group by col_1e", 815 output: "select sum(col_1d), count(col_1d), avg(col_1d), min(col_1d), max(col_1d) from tbl1 group by col_1e", 816 }, { 817 input: "select u.a, (select t.a from sa.t, u) from u, (select t.a, u.a from sa.t, u where t.a = u.a) as t where (u.a, u.b, u.c) in (select t.a, u.a, t.b * u.b tubb from t)", 818 output: "select u.a, (select t.a from sa.t cross join u) from u cross join (select t.a, u.a from sa.t cross join u where t.a = u.a) as t where (u.a, u.b, u.c) in (select t.a, u.a, t.b * u.b as tubb from t)", 819 }, { 820 input: "select u.a, (select t.a from sa.t, u) from u", 821 output: "select u.a, (select t.a from sa.t cross join u) from u", 822 }, { 823 input: "select t.a, u.a, t.b * u.b from sa.t join u on t.c = u.c or t.d != u.d where t.a = u.a and t.b > u.b group by t.a, u.a, (t.a + u.b + v.b) having t.a = 11 and v.c > 1000 order by t.a desc, u.a asc, v.d asc, tubb limit 200 offset 100", 824 output: "select t.a, u.a, t.b * u.b from sa.t inner join u on t.c = u.c or t.d != u.d where t.a = u.a and t.b > u.b group by t.a, u.a, (t.a + u.b + v.b) having t.a = 11 and v.c > 1000 order by t.a desc, u.a asc, v.d asc, tubb limit 200 offset 100", 825 }, { 826 input: "select t.a, u.a, t.b * u.b from sa.t join u on t.c = u.c or t.d != u.d where t.a = u.a and t.b > u.b group by t.a, u.a, (t.a + u.b + v.b) having t.a = 11 and v.c > 1000", 827 output: "select t.a, u.a, t.b * u.b from sa.t inner join u on t.c = u.c or t.d != u.d where t.a = u.a and t.b > u.b group by t.a, u.a, (t.a + u.b + v.b) having t.a = 11 and v.c > 1000", 828 }, { 829 input: "select t.a, u.a, t.b * u.b from sa.t join u on t.c = u.c or t.d != u.d where t.a = u.a and t.b > u.b group by t.a, u.a, (t.a + u.b + v.b)", 830 output: "select t.a, u.a, t.b * u.b from sa.t inner join u on t.c = u.c or t.d != u.d where t.a = u.a and t.b > u.b group by t.a, u.a, (t.a + u.b + v.b)", 831 }, { 832 input: "SELECT t.a,u.a,t.b * u.b FROM sa.t join u on t.c = u.c or t.d != u.d where t.a = u.a and t.b > u.b", 833 output: "select t.a, u.a, t.b * u.b from sa.t inner join u on t.c = u.c or t.d != u.d where t.a = u.a and t.b > u.b", 834 }, { 835 input: "select avg(u.a), count(u.b), cast(u.c as char) from u", 836 }, { 837 input: "select avg(u.a), count(*) from u", 838 }, { 839 input: "select avg(u.a), count(u.b) from u", 840 }, { 841 input: "select sum(col_1d) from tbl1 where col_1d < 13 group by col_1e", 842 }, { 843 input: "select sum(col_1a),count(col_1b),avg(col_1c),min(col_1d),max(col_1d) from tbl1", 844 output: "select sum(col_1a), count(col_1b), avg(col_1c), min(col_1d), max(col_1d) from tbl1", 845 }, { 846 input: "insert into tbl1 values (0,1,5,11, \"a\")", 847 output: "insert into tbl1 values (0, 1, 5, 11, a)", 848 }, { 849 input: "replace into tbl1 values (0,1,5,11, \"a\")", 850 output: "replace into tbl1 values (0, 1, 5, 11, a)", 851 }, { 852 input: "create table tbl1 (col_1a tinyint, col_1b smallint, col_1c int, col_1d bigint, col_1e char(10) not null)", 853 }, { 854 input: "insert into numtable values (4, 1.234567891, 1.234567891)", 855 }, { 856 input: "insert into numtable values (3, 1.234567, 1.234567)", 857 }, { 858 input: "replace into numtable values (4, 1.234567891, 1.234567891)", 859 }, { 860 input: "replace into numtable values (3, 1.234567, 1.234567)", 861 }, { 862 input: "create table numtable (id int, fl float, dl double)", 863 }, { 864 input: "drop table if exists numtable", 865 }, { 866 input: "create table table17 (`index` int)", 867 output: "create table table17 (index int)", 868 }, { 869 input: "create table table19$ (a int)", 870 }, { 871 input: "create table `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` (aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa int);", 872 output: "create table aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa (aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa int)", 873 }, { 874 input: "create table table12 (`a ` int)", 875 output: "create table table12 (a int)", 876 }, { 877 input: "create table `table11 ` (a int)", 878 output: "create table table11 (a int)", 879 }, { 880 input: "create table table10 (a int primary key, b varchar(10)) checksum=0 COMMENT=\"asdf\"", 881 output: "create table table10 (a int primary key, b varchar(10)) checksum = 0 comment = asdf", 882 }, { 883 input: "create temporary table table05 ( a int, b char(10));", 884 output: "create temporary table table05 (a int, b char(10))", 885 }, { 886 input: "create table table15 (a varchar(5) default 'abcde')", 887 output: "create table table15 (a varchar(5) default abcde)", 888 }, { 889 input: "create table table01 (a TINYINT primary key, b SMALLINT SIGNED, c INT UNSIGNED, d BIGINT not null , e FLOAT unique,f DOUBLE, g CHAR(10), h VARCHAR(20))", 890 output: "create table table01 (a tinyint primary key, b smallint, c int unsigned, d bigint not null, e float unique, f double, g char(10), h varchar(20))", 891 }, { 892 input: "create database test04 CHARACTER SET=utf8 collate=utf8_general_ci ENCRYPTION='N'", 893 output: "create database test04 character set utf8 collate utf8_general_ci encryption N", 894 }, { 895 input: "create database test03 DEFAULT CHARACTER SET utf8 collate utf8_general_ci ENCRYPTION 'Y'", 896 output: "create database test03 default character set utf8 collate utf8_general_ci encryption Y", 897 }, { 898 input: "drop database if exists t01234567890123456789012345678901234567890123456789012345678901234567890123456789", 899 }, { 900 input: "select distinct a from t", 901 }, { 902 input: "select * from t where a like 'a%'", 903 output: "select * from t where a like a%", 904 }, { 905 input: "select sysdate(), curtime(22) from t", 906 }, { 907 input: "select sysdate(), curtime from t", 908 }, { 909 input: "select current_time(), current_timestamp, lacalTIMe(89), utc_time() from t", 910 output: "select current_time(), current_timestamp(), lacaltime(89), utc_time() from t", 911 }, { 912 input: "select current_user(), current_role(), current_date, utc_date from t", 913 output: "select current_user(), current_role(), current_date(), utc_date() from t", 914 }, { 915 input: "select ascii(a), collation(b), hour(c), microsecond(d) from t", 916 }, { 917 input: "select dayofmonth('2001-11-00'), month('2005-00-00') from t", 918 output: "select dayofmonth(2001-11-00), month(2005-00-00) from t", 919 }, { 920 input: "select sum(distinct s) from tbl where 1", 921 }, { 922 input: "select u.a, interval 1 second from t", 923 output: "select u.a, interval(1, second) from t", 924 }, { 925 input: "select u.a, (select t.a from sa.t, u) from t where (u.a, u.b, u.c) in (select * from t)", 926 output: "select u.a, (select t.a from sa.t cross join u) from t where (u.a, u.b, u.c) in (select * from t)", 927 }, { 928 input: "select u.a, (select t.a from sa.t, u) from t where (u.a, u.b, u.c)", 929 output: "select u.a, (select t.a from sa.t cross join u) from t where (u.a, u.b, u.c)", 930 }, { 931 input: "select u.a, (select t.a from sa.t, u) from u", 932 output: "select u.a, (select t.a from sa.t cross join u) from u", 933 }, { 934 input: "select t.a from sa.t, u", 935 output: "select t.a from sa.t cross join u", 936 }, { 937 input: "select t.a from sa.t", 938 }, { 939 input: "create table k1 (id int not null primary key,name varchar(20)) partition by key() partitions 2", 940 output: "create table k1 (id int not null primary key, name varchar(20)) partition by key algorithm = 2 partitions 2", 941 }, { 942 input: "create table k1 (id int not null,name varchar(20),unique key (id))partition by key() partitions 2", 943 output: "create table k1 (id int not null, name varchar(20), unique key (id)) partition by key algorithm = 2 partitions 2", 944 }, { 945 input: "create table a (a int) partition by key (a, b, db.t.c) (partition xx (subpartition s1, subpartition s3 max_rows = 1000 min_rows = 100))", 946 output: "create table a (a int) partition by key algorithm = 2 (a, b, db.t.c) (partition xx (subpartition s1, subpartition s3 max_rows = 1000 min_rows = 100))", 947 }, { 948 input: "create table a (a int) partition by key (a, b, db.t.c) (partition xx row_format = dynamic max_rows = 1000 min_rows = 100)", 949 output: "create table a (a int) partition by key algorithm = 2 (a, b, db.t.c) (partition xx row_format = dynamic max_rows = 1000 min_rows = 100)", 950 }, { 951 input: "create table a (a int) engine = 'innodb' row_format = dynamic comment = 'table A' compression = 'lz4' data directory = '/data' index directory = '/index' max_rows = 1000 min_rows = 100", 952 output: "create table a (a int) engine = innodb row_format = dynamic comment = table A compression = lz4 data directory = /data index directory = /index max_rows = 1000 min_rows = 100", 953 }, { 954 input: "create table a (a int) partition by linear key algorithm = 3221 (a, b, db.t.c) (partition xx values less than (1, 2, 323), partition yy)", 955 output: "create table a (a int) partition by linear key algorithm = 3221 (a, b, db.t.c) (partition xx values less than (1, 2, 323), partition yy)", 956 }, { 957 input: "create table a (a int) partition by linear key algorithm = 3221 (a, b, db.t.c) partitions 10 subpartition by key (a, b, db.t.c) subpartitions 10", 958 output: "create table a (a int) partition by linear key algorithm = 3221 (a, b, db.t.c) partitions 10 subpartition by key algorithm = 2 (a, b, db.t.c) subpartitions 10", 959 }, { 960 input: "create table a (a int) partition by linear key algorithm = 3221 (a, b, db.t.c) partitions 10", 961 }, { 962 input: "create table a (a int) partition by linear hash (1 + 1234 / 32)", 963 }, { 964 input: "create table a (a int) partition by linear key algorithm = 31 (a, b, db.t.c)", 965 }, { 966 input: "create table a (a int) partition by linear key (a, b, db.t.c)", 967 output: "create table a (a int) partition by linear key algorithm = 2 (a, b, db.t.c)", 968 }, { 969 input: "create table a (a int) partition by list columns (a, b, db.t.c)", 970 }, { 971 input: "create table a (a int) partition by list columns (a, b, db.t.c)", 972 }, { 973 input: "create table a (a int) partition by range columns (a, b, db.t.c)", 974 }, { 975 input: "create table a (a int) partition by range(1 + 21)", 976 }, { 977 input: "create table a (a int storage disk constraint cx check (b + c) enforced)", 978 }, { 979 input: "create table a (a int storage disk, b int references b(a asc, b desc) match full on delete cascade on update restrict)", 980 }, { 981 input: "create table a (a int storage disk, b int)", 982 }, { 983 input: "create table a (a int not null default 1 auto_increment unique primary key collate utf8_bin storage disk)", 984 }, 985 { 986 input: `CREATE TABLE tp1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY(col3) PARTITIONS 4`, 987 output: `create table tp1 (col1 int, col2 char(5), col3 date) partition by key algorithm = 2 (col3) partitions 4`, 988 }, 989 { 990 input: `CREATE TABLE tp2 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY(col3)`, 991 output: `create table tp2 (col1 int, col2 char(5), col3 date) partition by key algorithm = 2 (col3)`, 992 }, 993 { 994 input: `CREATE TABLE tp3 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR KEY(col3) PARTITIONS 5`, 995 output: `create table tp3 (col1 int, col2 char(5), col3 date) partition by linear key algorithm = 2 (col3) partitions 5`, 996 }, 997 { 998 input: `CREATE TABLE tp4 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY ALGORITHM = 1 (col3)`, 999 output: `create table tp4 (col1 int, col2 char(5), col3 date) partition by key algorithm = 1 (col3)`, 1000 }, 1001 { 1002 input: `CREATE TABLE tp5 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR KEY ALGORITHM = 1 (col3) PARTITIONS 5;`, 1003 output: `create table tp5 (col1 int, col2 char(5), col3 date) partition by linear key algorithm = 1 (col3) partitions 5`, 1004 }, 1005 { 1006 input: `CREATE TABLE tp6 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY(col1, col2) PARTITIONS 4`, 1007 output: `create table tp6 (col1 int, col2 char(5), col3 date) partition by key algorithm = 2 (col1, col2) partitions 4`, 1008 }, 1009 { 1010 input: `CREATE TABLE tp7 (col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, PRIMARY KEY (col1, col2)) PARTITION BY KEY(col1) PARTITIONS 4`, 1011 output: `create table tp7 (col1 int not null, col2 date not null, col3 int not null, col4 int not null, primary key (col1, col2)) partition by key algorithm = 2 (col1) partitions 4`, 1012 }, 1013 { 1014 input: `CREATE TABLE tp9 (col1 INT, col2 CHAR(5)) PARTITION BY HASH(col1) PARTITIONS 4`, 1015 output: `create table tp9 (col1 int, col2 char(5)) partition by hash (col1) partitions 4`, 1016 }, 1017 { 1018 input: `CREATE TABLE tp10 (col1 INT, col2 CHAR(5), col3 DATETIME) PARTITION BY HASH (YEAR(col3));`, 1019 output: `create table tp10 (col1 int, col2 char(5), col3 datetime(26)) partition by hash (year(col3))`, 1020 }, 1021 { 1022 input: `CREATE TABLE tp11 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR HASH( YEAR(col3)) PARTITIONS 6`, 1023 output: `create table tp11 (col1 int, col2 char(5), col3 date) partition by linear hash (year(col3)) partitions 6`, 1024 }, 1025 { 1026 input: `CREATE TABLE tp12 (col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, PRIMARY KEY (col1, col2)) PARTITION BY HASH(col1) PARTITIONS 4`, 1027 output: `create table tp12 (col1 int not null, col2 date not null, col3 int not null, col4 int not null, primary key (col1, col2)) partition by hash (col1) partitions 4`, 1028 }, 1029 { 1030 input: `CREATE TABLE tp13 ( 1031 id INT NOT NULL, 1032 fname VARCHAR(30), 1033 lname VARCHAR(30), 1034 hired DATE NOT NULL DEFAULT '1970-01-01', 1035 separated DATE NOT NULL DEFAULT '9999-12-31', 1036 job_code INT, 1037 store_id INT 1038 ) 1039 PARTITION BY RANGE ( YEAR(separated) ) ( 1040 PARTITION p0 VALUES LESS THAN (1991), 1041 PARTITION p1 VALUES LESS THAN (1996), 1042 PARTITION p2 VALUES LESS THAN (2001), 1043 PARTITION p3 VALUES LESS THAN MAXVALUE 1044 );`, 1045 output: `create table tp13 (id int not null, fname varchar(30), lname varchar(30), hired date not null default 1970-01-01, separated date not null default 9999-12-31, job_code int, store_id int) partition by range(year(separated)) (partition p0 values less than (1991), partition p1 values less than (1996), partition p2 values less than (2001), partition p3 values less than (MAXVALUE))`, 1046 }, 1047 { 1048 input: `CREATE TABLE tp14 ( 1049 a INT NOT NULL, 1050 b INT NOT NULL 1051 ) 1052 PARTITION BY RANGE COLUMNS(a,b) PARTITIONS 4 ( 1053 PARTITION p0 VALUES LESS THAN (10,5), 1054 PARTITION p1 VALUES LESS THAN (20,10), 1055 PARTITION p2 VALUES LESS THAN (50,20), 1056 PARTITION p3 VALUES LESS THAN (65,30) 1057 )`, 1058 output: `create table tp14 (a int not null, b int not null) partition by range columns (a, b) partitions 4 (partition p0 values less than (10, 5), partition p1 values less than (20, 10), partition p2 values less than (50, 20), partition p3 values less than (65, 30))`, 1059 }, 1060 { 1061 input: `CREATE TABLE tp15 ( 1062 id INT PRIMARY KEY, 1063 name VARCHAR(35), 1064 age INT unsigned 1065 ) 1066 PARTITION BY LIST (id) ( 1067 PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21), 1068 PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22), 1069 PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23), 1070 PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24) 1071 );`, 1072 output: `create table tp15 (id int primary key, name varchar(35), age int unsigned) partition by list(id) (partition r0 values in (1, 5, 9, 13, 17, 21), partition r1 values in (2, 6, 10, 14, 18, 22), partition r2 values in (3, 7, 11, 15, 19, 23), partition r3 values in (4, 8, 12, 16, 20, 24))`, 1073 }, 1074 { 1075 input: `CREATE TABLE tp16 ( 1076 a INT NULL, 1077 b INT NULL 1078 ) 1079 PARTITION BY LIST COLUMNS(a,b) ( 1080 PARTITION p0 VALUES IN( (0,0), (NULL,NULL) ), 1081 PARTITION p1 VALUES IN( (0,1), (0,2), (0,3), (1,1), (1,2) ), 1082 PARTITION p2 VALUES IN( (1,0), (2,0), (2,1), (3,0), (3,1) ), 1083 PARTITION p3 VALUES IN( (1,3), (2,2), (2,3), (3,2), (3,3) ) 1084 )`, 1085 output: `create table tp16 (a int null, b int null) partition by list columns (a, b) (partition p0 values in ((0, 0), (null, null)), partition p1 values in ((0, 1), (0, 2), (0, 3), (1, 1), (1, 2)), partition p2 values in ((1, 0), (2, 0), (2, 1), (3, 0), (3, 1)), partition p3 values in ((1, 3), (2, 2), (2, 3), (3, 2), (3, 3)))`, 1086 }, 1087 { 1088 input: `CREATE TABLE tp17 ( 1089 id INT NOT NULL PRIMARY KEY, 1090 fname VARCHAR(30), 1091 lname VARCHAR(30) 1092 ) 1093 PARTITION BY RANGE (id) ( 1094 PARTITION p0 VALUES LESS THAN (6), 1095 PARTITION p1 VALUES LESS THAN (11), 1096 PARTITION p2 VALUES LESS THAN (16), 1097 PARTITION p3 VALUES LESS THAN (21) 1098 );`, 1099 output: `create table tp17 (id int not null primary key, fname varchar(30), lname varchar(30)) partition by range(id) (partition p0 values less than (6), partition p1 values less than (11), partition p2 values less than (16), partition p3 values less than (21))`, 1100 }, 1101 { 1102 input: "grant all, all(a, b), create(a, b), select(a, b), super(a, b, c) on table db.a to u1, u2 with grant option", 1103 }, { 1104 input: "grant proxy on u1 to u2, u3, u4 with grant option", 1105 }, { 1106 input: "grant proxy on u1 to u2, u3, u4", 1107 }, 1108 { 1109 input: "grant r1, r2, r3 to u1, u1, u3", 1110 }, { 1111 input: "grant super(a, b, c) on procedure db.func to 'h3'", 1112 output: "grant super(a, b, c) on procedure db.func to h3", 1113 }, 1114 { 1115 input: "revoke all, all(a, b), create(a, b), select(a, b), super(a, b, c) on table db.A from u1, u2", 1116 output: "revoke all, all(a, b), create(a, b), select(a, b), super(a, b, c) on table db.a from u1, u2", 1117 }, { 1118 input: "revoke r1, r2, r3 from u1, u2, u3", 1119 }, { 1120 input: "revoke super(a, b, c) on procedure db.func from h3", 1121 }, { 1122 input: "revoke all on table db.A from u1, u2", 1123 output: "revoke all on table db.a from u1, u2", 1124 }, { 1125 input: "revoke all on table db.a from u1", 1126 }, { 1127 input: "set default role r1, r2, r3 to u1, u2, u3", 1128 }, { 1129 input: "set default role all to u1, u2, u3", 1130 }, { 1131 input: "set default role none to u1, u2, u3", 1132 }, { 1133 input: "set password = password('ppp')", 1134 output: "set password = ppp", 1135 }, { 1136 input: "set password for u1@h1 = password('ppp')", 1137 output: "set password for u1@h1 = ppp", 1138 }, { 1139 input: "set password for u1@h1 = 'ppp'", 1140 output: "set password for u1@h1 = ppp", 1141 }, { 1142 input: "set @a = 0, @b = 1", 1143 output: "set a = 0, b = 1", 1144 }, { 1145 input: "set a = 0, session b = 1, @@session.c = 1, global d = 1, @@global.e = 1", 1146 output: "set a = 0, b = 1, c = 1, global d = 1, global e = 1", 1147 }, { 1148 input: "set @@session.a = 1", 1149 output: "set a = 1", 1150 }, { 1151 input: "set @@global.a = 1", 1152 output: "set global a = 1", 1153 }, { 1154 input: "set global a = 1", 1155 }, { 1156 input: "set a = 1", 1157 }, { 1158 input: "rollback", 1159 }, { 1160 input: "rollback and chain no release", 1161 output: "rollback", 1162 }, { 1163 input: "commit and chain no release", 1164 output: "commit", 1165 }, { 1166 input: "commit", 1167 }, { 1168 input: "start transaction read only", 1169 }, { 1170 input: "start transaction read write", 1171 }, { 1172 input: "start transaction", 1173 }, { 1174 input: "use db1", 1175 }, { 1176 input: "use", 1177 }, { 1178 input: "update a as aa set a = 3, b = 4 where a != 0 order by b limit 1", 1179 }, { 1180 input: "update a as aa set a = 3, b = 4", 1181 }, { 1182 input: "explain insert into u (a, b, c, d) values (1, 2, 3, 4), (5, 6, 7, 8)", 1183 }, { 1184 input: "explain replace into u (a, b, c, d) values (1, 2, 3, 4), (5, 6, 7, 8)", 1185 }, { 1186 input: "explain delete from a where a != 0 order by b limit 1", 1187 }, { 1188 input: "explain select a from a union select b from b", 1189 }, { 1190 input: "explain select a from a intersect select b from b", 1191 }, { 1192 input: "explain select a from a except select b from b", 1193 }, { 1194 input: "explain select a from a minus select b from b", 1195 }, { 1196 input: "explain select a from a", 1197 }, { 1198 input: "explain (format text) select a from A", 1199 output: "explain (format text) select a from a", 1200 }, { 1201 input: "explain analyze select * from t", 1202 output: "explain (analyze) select * from t", 1203 }, { 1204 input: "explain format = 'tree' for connection 10", 1205 output: "explain format = tree for connection 10", 1206 }, { 1207 input: "explain db.a", 1208 output: "show columns from db.a", 1209 }, { 1210 input: "explain a", 1211 output: "show columns from a", 1212 }, { 1213 input: "show index from t where true", 1214 }, { 1215 input: "show databases like 'a%'", 1216 output: "show databases like a%", 1217 }, { 1218 input: "show global status where 1 + 21 > 21", 1219 }, { 1220 input: "show global variables", 1221 }, { 1222 input: "show warnings", 1223 }, { 1224 input: "show errors", 1225 }, { 1226 input: "show full processlist", 1227 }, { 1228 input: "show processlist", 1229 }, { 1230 input: "show full tables from db1 like 'a%' where a != 0", 1231 output: "show full tables from db1 like a% where a != 0", 1232 }, { 1233 input: "show open tables from db1 like 'a%' where a != 0", 1234 output: "show open tables from db1 like a% where a != 0", 1235 }, { 1236 input: "show tables from db1 like 'a%' where a != 0", 1237 output: "show tables from db1 like a% where a != 0", 1238 }, { 1239 input: "show databases like 'a%' where a != 0", 1240 output: "show databases like a% where a != 0", 1241 }, { 1242 input: "show databases", 1243 }, { 1244 input: "show extended full columns from t from db like 'a%'", 1245 output: "show extended full columns from t from db like a%", 1246 }, { 1247 input: "show extended full columns from t from db where a != 0", 1248 }, { 1249 input: "show columns from t from db where a != 0", 1250 }, { 1251 input: "show columns from t from db", 1252 }, { 1253 input: "show create database if not exists db", 1254 }, { 1255 input: "show create database db", 1256 }, { 1257 input: "show create table db.t1", 1258 }, { 1259 input: "show create table t1", 1260 }, { 1261 input: "drop user if exists u1, u2, u3", 1262 }, { 1263 input: "drop user u1", 1264 }, { 1265 input: "drop role r1", 1266 }, { 1267 input: "drop role if exists r1, r2, r3", 1268 }, { 1269 input: "drop index if exists idx1 on db.t", 1270 }, { 1271 input: "drop index idx1 on db.t", 1272 }, { 1273 input: "drop table if exists t1, t2, db.t", 1274 }, { 1275 input: "drop table db.t", 1276 }, { 1277 input: "drop table if exists t", 1278 }, { 1279 input: "drop database if exists t", 1280 }, { 1281 input: "drop database t", 1282 }, { 1283 input: "create role if not exists 'a', 'b'", 1284 output: "create role if not exists a, b", 1285 }, { 1286 input: "create role if not exists 'webapp'", 1287 output: "create role if not exists webapp", 1288 }, { 1289 input: "create role 'admin', 'developer'", 1290 output: "create role admin, developer", 1291 }, { 1292 input: "create index idx1 on a (a) KEY_BLOCK_SIZE 10 with parser x comment 'x' invisible", 1293 output: "create index idx1 on a (a) KEY_BLOCK_SIZE 10 with parser x comment x invisible", 1294 }, { 1295 input: "create index idx1 using btree on A (a) KEY_BLOCK_SIZE 10 with parser x comment 'x' invisible", 1296 output: "create index idx1 using btree on a (a) KEY_BLOCK_SIZE 10 with parser x comment x invisible", 1297 }, { 1298 input: "create index idx1 on a (a)", 1299 }, { 1300 input: "create unique index idx1 using btree on a (a, b(10), (a + b), (a - b)) visible", 1301 }, { 1302 input: "create database test_db default collate 'utf8mb4_general_ci' collate utf8mb4_general_ci", 1303 output: "create database test_db default collate utf8mb4_general_ci collate utf8mb4_general_ci", 1304 }, { 1305 input: "create database if not exists test_db character set geostd8", 1306 }, { 1307 input: "create database test_db default collate utf8mb4_general_ci", 1308 }, { 1309 input: "create database if not exists db", 1310 }, { 1311 input: "create database db", 1312 }, { 1313 input: "delete from a as aa", 1314 }, { 1315 input: "delete from t where a > 1 order by b limit 1 offset 2", 1316 }, { 1317 input: "delete from t where a = 1", 1318 }, { 1319 input: "insert into u partition(p1, p2) (a, b, c, d) values (1, 2, 3, 4), (5, 6, 1, 0)", 1320 }, { 1321 input: "insert into t values ('aa', 'bb', 'cc')", 1322 output: "insert into t values (aa, bb, cc)", 1323 }, { 1324 input: "insert into t() values (1, 2, 3)", 1325 output: "insert into t values (1, 2, 3)", 1326 }, { 1327 input: "insert into t (c1, c2, c3) values (1, 2, 3)", 1328 }, { 1329 input: "insert into t (c1, c2, c3) select c1, c2, c3 from t1", 1330 }, { 1331 input: "insert into t select c1, c2, c3 from t1", 1332 }, { 1333 input: "insert into t values (1, 3, 4)", 1334 }, { 1335 input: "replace into u partition(p1, p2) (a, b, c, d) values (1, 2, 3, 4), (5, 6, 1, 0)", 1336 }, { 1337 input: "replace into t values ('aa', 'bb', 'cc')", 1338 output: "replace into t values (aa, bb, cc)", 1339 }, { 1340 input: "replace into t() values (1, 2, 3)", 1341 output: "replace into t values (1, 2, 3)", 1342 }, { 1343 input: "replace into t (c1, c2, c3) values (1, 2, 3)", 1344 }, { 1345 input: "replace into t (c1, c2, c3) select c1, c2, c3 from t1", 1346 }, { 1347 input: "replace into t select c1, c2, c3 from t1", 1348 }, { 1349 input: "replace into t values (1, 3, 4)", 1350 }, { 1351 input: "create table t1 (`show` bool(0));", 1352 output: "create table t1 (show bool(0))", 1353 }, { 1354 input: "create table t1 (t bool(0));", 1355 output: "create table t1 (t bool(0))", 1356 }, { 1357 input: "create table t1 (t char(0))", 1358 }, { 1359 input: "create table t1 (t bool(20), b int, c char(20), d varchar(20))", 1360 }, { 1361 input: "create table t (a int(20) not null)", 1362 }, { 1363 input: "create table db.t (db.t.a int(20) null)", 1364 }, { 1365 input: "create table t (a float(20, 20) not null, b int(20) null, c int(30) null)", 1366 }, { 1367 input: "create table t1 (t time(3) null, dt datetime(6) null, ts timestamp(1) null)", 1368 output: "create table t1 (t time(26, 3) null, dt datetime(26, 6) null, ts timestamp(26, 1) null)", 1369 }, { 1370 input: "create table t1 (a int default 1 + 1 - 2 * 3 / 4 div 7 ^ 8 << 9 >> 10 % 11)", 1371 output: "create table t1 (a int default 1 + 1 - 2 * 3 / 4 div 7 ^ 8 << 9 >> 10 % 11)", 1372 }, { 1373 input: "create table t1 (t bool default -1 + +1)", 1374 }, { 1375 input: "create table t (id int unique key)", 1376 }, { 1377 input: "select * from t", 1378 }, { 1379 input: "select c1, c2, c3 from t1, t as t2 where t1.c1 = 1 group by c2 having c2 > 10", 1380 output: "select c1, c2, c3 from t1 cross join t as t2 where t1.c1 = 1 group by c2 having c2 > 10", 1381 }, { 1382 input: "select a from t order by a desc limit 1 offset 2", 1383 }, { 1384 input: "select a from t order by a desc limit 1, 2", 1385 output: "select a from t order by a desc limit 2 offset 1", 1386 }, { 1387 input: "select * from t union select c from t1", 1388 }, { 1389 input: "select * from t union all select c from t1", 1390 }, { 1391 input: "select * from t union distinct select c from t1", 1392 }, { 1393 input: "select * from t except select c from t1", 1394 }, { 1395 input: "select * from t except all select c from t1", 1396 }, { 1397 input: "select * from t except distinct select c from t1", 1398 }, { 1399 input: "select * from t intersect select c from t1", 1400 }, { 1401 input: "select * from t intersect all select c from t1", 1402 }, { 1403 input: "select * from t intersect distinct select c from t1", 1404 }, { 1405 input: "select * from t minus all select c from t1", 1406 }, { 1407 input: "select * from t minus distinct select c from t1", 1408 }, { 1409 input: "select * from t minus select c from t1", 1410 }, { 1411 input: "select * from (select a from t) as t1", 1412 }, { 1413 input: "select * from (select a from t) as t1 join t2 on 1", 1414 output: "select * from (select a from t) as t1 inner join t2 on 1", 1415 }, { 1416 input: "select * from (select a from t) as t1 inner join t2 using (a)", 1417 }, { 1418 input: "select * from (select a from t) as t1 cross join t2", 1419 }, { 1420 input: "select * from t1 join t2 using (a, b, c)", 1421 output: "select * from t1 inner join t2 using (a, b, c)", 1422 }, { 1423 input: "select * from t1 straight_join t2 on 1 + 213", 1424 }, { 1425 input: "select * from t1 straight_join t2 on col", 1426 }, { 1427 input: "select * from t1 right outer join t2 on 123", 1428 output: "select * from t1 right join t2 on 123", 1429 }, { 1430 input: "select * from t1 natural left join t2", 1431 }, { 1432 input: "select 1", 1433 }, { 1434 input: "select $ from t", 1435 }, { 1436 input: "analyze table part (a,b )", 1437 output: "analyze table part(a, b)", 1438 }, { 1439 input: "select $ from t into outfile '/Users/tmp/test'", 1440 output: "select $ from t into outfile /Users/tmp/test fields terminated by , enclosed by \" lines terminated by \n header true", 1441 }, { 1442 input: "select $ from t into outfile '/Users/tmp/test' FIELDS TERMINATED BY ','", 1443 output: "select $ from t into outfile /Users/tmp/test fields terminated by , enclosed by \" lines terminated by \n header true", 1444 }, { 1445 input: "select $ from t into outfile '/Users/tmp/test' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'", 1446 output: "select $ from t into outfile /Users/tmp/test fields terminated by , enclosed by \" lines terminated by \n header true", 1447 }, { 1448 input: "select $ from t into outfile '/Users/tmp/test' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' header 'TRUE'", 1449 output: "select $ from t into outfile /Users/tmp/test fields terminated by , enclosed by \" lines terminated by \n header true", 1450 }, { 1451 input: "select $ from t into outfile '/Users/tmp/test' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' header 'FALSE'", 1452 output: "select $ from t into outfile /Users/tmp/test fields terminated by , enclosed by \" lines terminated by \n header false", 1453 }, { 1454 input: "select $ from t into outfile '/Users/tmp/test' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' header 'FALSE' MAX_FILE_SIZE 100", 1455 output: "select $ from t into outfile /Users/tmp/test fields terminated by , enclosed by \" lines terminated by \n header false max_file_size 102400", 1456 }, { 1457 input: "select $ from t into outfile '/Users/tmp/test' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' header 'FALSE' MAX_FILE_SIZE 100 FORCE_QUOTE (a, b)", 1458 output: "select $ from t into outfile /Users/tmp/test fields terminated by , enclosed by \" lines terminated by \n header false max_file_size 102400 force_quote a, b", 1459 }, { 1460 input: "drop prepare stmt_name1", 1461 }, { 1462 input: "deallocate prepare stmt_name1", 1463 }, { 1464 input: "execute stmt_name1", 1465 }, { 1466 input: "execute stmt_name1 using @var_name,@@sys_name", 1467 }, { 1468 input: "prepare stmt_name1 from select * from t1", 1469 }, { 1470 input: "prepare stmt_name1 from 'select * from t1'", 1471 output: "prepare stmt_name1 from select * from t1", 1472 }, { 1473 input: "prepare stmt_name1 from select * from t1 where a > ? or abs(b) < ?", 1474 }, { 1475 input: "create account if not exists nihao admin_name 'admin' identified by '123' open comment 'new account'", 1476 }, { 1477 input: "create account if not exists nihao admin_name 'admin' identified by random password", 1478 }, { 1479 input: "create account if not exists nihao admin_name 'admin' identified with '123'", 1480 }, { 1481 input: "create account nihao admin_name 'admin' identified by '123' open comment 'new account'", 1482 }, { 1483 input: "create account nihao admin_name 'admin' identified by random password", 1484 }, { 1485 input: "create account nihao admin_name 'admin' identified with '123'", 1486 }, { 1487 input: "drop account if exists abc", 1488 }, { 1489 input: "alter account if exists nihao admin_name 'admin' identified by '123' open comment 'new account'", 1490 }, { 1491 input: "alter account if exists nihao admin_name 'admin' identified by random password", 1492 }, { 1493 input: "alter account if exists nihao admin_name 'admin' identified with '123'", 1494 }, { 1495 input: "alter account nihao admin_name 'admin' identified by '123' open comment 'new account'", 1496 }, { 1497 input: "alter account nihao admin_name 'admin' identified by random password", 1498 }, { 1499 input: "alter account nihao admin_name 'admin' identified with '123'", 1500 }, { 1501 input: "create user if not exists abc1 identified by '123', abc2 identified by '234', abc3 identified by '111' default role def_role " + 1502 "password expire " + 1503 "comment 'new comment'", 1504 }, { 1505 input: "create user if not exists abc1 identified by '123', abc2 identified by '234', abc3 identified by '111' default role de_role " + 1506 "lock " + 1507 "attribute 'new attribute'", 1508 }, { 1509 input: "create user if not exists abc1 identified by '123', abc2 identified by '234', abc3 identified by '111', " + 1510 "abc4 identified by random password, " + 1511 "abc5 identified with '345' " + 1512 "default role de_role " + 1513 "attribute 'new attribute'", 1514 }, { 1515 input: "create user if not exists abc1 identified by '111' " + 1516 "default role de_role " + 1517 "comment 'new comment'", 1518 }, { 1519 input: "create user if not exists abc1 identified by '111' " + 1520 "default role de_role", 1521 }, { 1522 input: "create user if not exists abc1 identified by '123' " + 1523 "default role de_role", 1524 }, { 1525 input: "create user if not exists abc1 identified by '123' " + 1526 "default role de_role", 1527 }, { 1528 input: "create user abc1 identified by '123' " + 1529 "default role de_role", 1530 }, { 1531 input: "create user abc1 identified by '111' " + 1532 "default role de_role", 1533 }, { 1534 input: "create user abc1 identified by 'a111'", 1535 }, { 1536 input: "drop user if exists abc1, abc2, abc3", 1537 }, { 1538 input: "drop user abc1, abc2, abc3", 1539 }, { 1540 input: "drop user abc1", 1541 }, { 1542 input: "alter user if exists abc1 identified by '123', abc2 identified by '234', abc3 identified by '123' " + 1543 "default role de_role " + 1544 "lock " + 1545 "comment 'new comment'", 1546 }, { 1547 input: "alter user if exists abc1 identified by '123', abc2 identified by '234', abc3 identified by '123' " + 1548 "default role de_role " + 1549 "unlock " + 1550 "comment 'new comment'", 1551 }, { 1552 input: "alter user if exists abc1 identified by '123', abc2 identified by '234', abc3 identified by '123' " + 1553 "default role de_role " + 1554 "password expire " + 1555 "attribute 'new attribute'", 1556 }, { 1557 input: "alter user if exists abc1 identified by '123', abc2 identified by '234', abc3 identified by '123' " + 1558 "attribute 'new attribute'", 1559 }, { 1560 input: "alter user if exists abc1 identified by '123', abc2 identified by '234', abc3 identified by '123'", 1561 }, { 1562 input: "alter user if exists abc1 identified by '123', abc2 identified with '234', abc3 identified with 'SSL'", 1563 }, { 1564 input: "alter user if exists abc1 identified by '123'", 1565 }, { 1566 input: "alter user if exists abc1 identified by '123'", 1567 }, { 1568 input: "alter user abc1 identified by '123'", 1569 }, { 1570 input: "create role if not exists role1, role2, role2", 1571 }, { 1572 input: "create role role1", 1573 }, { 1574 input: "drop role if exists role1, role2, role2", 1575 }, { 1576 input: "drop role if exists role1", 1577 }, { 1578 input: "drop role role1", 1579 }, { 1580 input: "grant all, all(a, b), create(a, b), select(a, b), super(a, b, c) on table db.a to u1, u2 with grant option", 1581 }, { 1582 input: "grant all, all(a, b) on table *.* to u1, u2 with grant option", 1583 }, { 1584 input: "grant all, all(a, b) on table db.a to u1, u2 with grant option", 1585 }, { 1586 input: "grant all, all(a, b) on table db.* to u1, u2 with grant option", 1587 }, { 1588 input: "grant all, all(a, b) on database * to u1, u2 with grant option", 1589 }, { 1590 input: "grant all, all(a, b) on table *.* to u1, u2 with grant option", 1591 }, { 1592 input: "grant all, all(a, b) on table db1.* to u1, u2 with grant option", 1593 }, { 1594 input: "grant all, all(a, b) on table db1.tb1 to u1, u2 with grant option", 1595 }, { 1596 input: "grant all, all(a, b) on table tb1 to u1, u2 with grant option", 1597 }, { 1598 input: "grant r1, r2 to u1, u2, r3 with grant option", 1599 }, { 1600 input: "grant r1, r2 to u1, u2, r3", 1601 }, { 1602 input: "grant r1, r2 to u1@h1, u2@h2, r3", 1603 }, { 1604 input: "revoke if exists all, all(a, b), create(a, b), select(a, b), super(a, b, c) on table db.A from u1, u2", 1605 output: "revoke if exists all, all(a, b), create(a, b), select(a, b), super(a, b, c) on table db.a from u1, u2", 1606 }, { 1607 input: "revoke if exists r1, r2, r3 from u1, u2, u3", 1608 }, { 1609 input: "revoke if exists super(a, b, c) on procedure db.func from h3", 1610 }, { 1611 input: "revoke if exists all on table db.A from u1, u2", 1612 output: "revoke if exists all on table db.a from u1, u2", 1613 }, { 1614 input: "revoke if exists all on table db.a from u1", 1615 }, { 1616 input: "use db1", 1617 }, { 1618 input: "set role r1", 1619 }, { 1620 input: "set secondary role all", 1621 }, { 1622 input: "set secondary role none", 1623 }, { 1624 input: `select json_extract('{"a":1,"b":2}', '$.b')`, 1625 output: `select json_extract({"a":1,"b":2}, $.b)`, 1626 }, { 1627 input: `select json_extract(a, '$.b') from t`, 1628 output: `select json_extract(a, $.b) from t`, 1629 }, { 1630 input: `create table t1 (a int, b uuid)`, 1631 }, { 1632 input: `create table t2 (a uuid primary key, b varchar(10))`, 1633 }, { 1634 input: `create table t3 (a int, b uuid, primary key idx (a, b))`, 1635 }, { 1636 input: `DO SLEEP(5)`, 1637 output: `do sleep(5)`, 1638 }, { 1639 input: `DECLARE a, b INT`, 1640 output: `declare a b int default null`, 1641 }, { 1642 input: `DECLARE a, b INT DEFAULT 1`, 1643 output: `declare a b int default 1`, 1644 }, { 1645 input: "grant truncate on table *.* to r1", 1646 }, { 1647 input: "grant reference on table *.* to r1", 1648 }, 1649 { 1650 input: `VALUES ROW(1,-2,3), ROW(5,7,9), ROW(4,6,8)`, 1651 output: `values row(1, -2, 3), row(5, 7, 9), row(4, 6, 8)`, 1652 }, { 1653 input: `VALUES ROW(5,7,9), ROW(1,2,3), ROW(9,10,11) ORDER BY column_1`, 1654 output: `values row(5, 7, 9), row(1, 2, 3), row(9, 10, 11) order by column_1`, 1655 }, 1656 { 1657 input: `VALUES ROW(5,7,9), ROW(1,2,3), ROW(9,10,11) ORDER BY column_1 LIMIT 2`, 1658 output: `values row(5, 7, 9), row(1, 2, 3), row(9, 10, 11) order by column_1 limit 2`, 1659 }, 1660 { 1661 input: `select * from unnest("a") as f`, 1662 output: `select * from unnest(a) as f`, 1663 }, 1664 { 1665 input: `select * from unnest("a", "b") as f`, 1666 output: `select * from unnest(a, b) as f`, 1667 }, 1668 { 1669 input: `select * from unnest("a", "b", true) as f`, 1670 output: `select * from unnest(a, b, true) as f`, 1671 }, 1672 { 1673 input: `select * from unnest("a")`, 1674 output: `select * from unnest(a)`, 1675 }, 1676 { 1677 input: `select * from unnest("a", "b")`, 1678 output: `select * from unnest(a, b)`, 1679 }, 1680 { 1681 input: `select * from unnest("a", "b", true)`, 1682 output: `select * from unnest(a, b, true)`, 1683 }, 1684 { 1685 input: `select * from unnest(t.a)`, 1686 output: `select * from unnest(t.a)`, 1687 }, 1688 { 1689 input: `select * from unnest(t.a, "$.b")`, 1690 output: `select * from unnest(t.a, $.b)`, 1691 }, 1692 { 1693 input: `select * from unnest(t.a, "$.b", true)`, 1694 output: `select * from unnest(t.a, $.b, true)`, 1695 }, 1696 { 1697 input: `select * from unnest(t.a) as f`, 1698 output: `select * from unnest(t.a) as f`, 1699 }, 1700 { 1701 input: `select * from unnest(t.a, "$.b") as f`, 1702 output: `select * from unnest(t.a, $.b) as f`, 1703 }, 1704 { 1705 input: `select * from unnest(t.a, "$.b", true) as f`, 1706 output: `select * from unnest(t.a, $.b, true) as f`, 1707 }, 1708 { 1709 input: `select * from generate_series('1', '10', '1')`, 1710 output: `select * from generate_series(1, 10, 1)`, 1711 }, 1712 { 1713 input: `select * from generate_series('1', '10', '1') g`, 1714 output: `select * from generate_series(1, 10, 1) as g`, 1715 }, 1716 { 1717 input: `select * from generate_series(1, 10, 1)`, 1718 output: `select * from generate_series(1, 10, 1)`, 1719 }, 1720 { 1721 input: `select * from generate_series(1, 10, 1) as g`, 1722 output: `select * from generate_series(1, 10, 1) as g`, 1723 }, 1724 { 1725 input: `create table t1 (a int low_cardinality, b int not null low_cardinality)`, 1726 output: `create table t1 (a int low_cardinality, b int not null low_cardinality)`, 1727 }, 1728 { 1729 input: `modump database t into 'a.sql'`, 1730 output: `modump database t into a.sql`, 1731 }, 1732 { 1733 input: `modump database t into 'a.sql' max_file_size 1`, 1734 output: `modump database t into a.sql max_file_size 1`, 1735 }, 1736 { 1737 input: `modump database t tables t1 into 'a.sql'`, 1738 output: `modump database t tables t1 into a.sql`, 1739 }, 1740 { 1741 input: `modump database t tables t1 into 'a.sql' max_file_size 1`, 1742 output: `modump database t tables t1 into a.sql max_file_size 1`, 1743 }, 1744 { 1745 input: `modump database t tables t1,t2 into 'a.sql'`, 1746 output: `modump database t tables t1, t2 into a.sql`, 1747 }, 1748 { 1749 input: `modump database t tables t1,t2 into 'a.sql' max_file_size 1`, 1750 output: `modump database t tables t1, t2 into a.sql max_file_size 1`, 1751 }, 1752 { 1753 input: `select mo_show_visible_bin('a',0) as m`, 1754 output: `select mo_show_visible_bin(a, 0) as m`, 1755 }, 1756 //https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html 1757 { 1758 input: `select avg(a) over () from t1`, 1759 }, 1760 { 1761 input: `select avg(a) over (partition by col1, col2) from t1`, 1762 }, 1763 { 1764 input: `select avg(a) over (partition by col1, col2 order by col3 desc) from t1`, 1765 }, 1766 //https://dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html 1767 { 1768 input: `select count(a) over (partition by col1, col2 order by col3 desc rows 1 preceding) from t1`, 1769 }, 1770 { 1771 input: `select sum(a) over (partition by col1, col2 order by col3 desc rows between 1 preceding and 20 following) from t1`, 1772 }, 1773 { 1774 input: `select count(a) over (partition by col1, col2 order by col3 desc range unbounded preceding) from t1`, 1775 }, 1776 { 1777 input: "alter account if exists abc", 1778 }, 1779 { 1780 input: "alter account if exists abc admin_name 'root' identified by '111' open comment 'str'", 1781 }, 1782 { 1783 input: "alter account if exists abc open comment 'str'", 1784 }, 1785 { 1786 input: "alter account if exists abc comment 'str'", 1787 }, 1788 { 1789 input: "alter account if exists abc open", 1790 }, 1791 { 1792 input: "alter account if exists abc admin_name 'root' identified by '111' open", 1793 }, 1794 { 1795 input: "alter account if exists abc admin_name 'root' identified by '111' comment 'str'", 1796 }, 1797 { 1798 input: `create cluster table a (a int)`, 1799 }, 1800 { 1801 input: `insert into a accounts(acc1, acc2) values (1, 2), (1, 2)`, 1802 }, 1803 { 1804 input: `insert into a accounts(acc1, acc2) select a, b from a`, 1805 }, 1806 { 1807 input: `insert into a (a, b) accounts(acc1, acc2) values (1, 2), (1, 2)`, 1808 }, 1809 { 1810 input: `insert into a () accounts(acc1, acc2) values (1, 2), (1, 2)`, 1811 output: `insert into a accounts(acc1, acc2) values (1, 2), (1, 2)`, 1812 }, 1813 { 1814 input: `insert into a (a, b) accounts(acc1, acc2) select a, b from a`, 1815 }, 1816 { 1817 input: `insert into a accounts(acc1, acc2) set a = b, b = b + 1`, 1818 output: `insert into a (a, b) accounts(acc1, acc2) values (b, b + 1)`, 1819 }, 1820 { 1821 input: "load data infile 'test/loadfile5' ignore INTO TABLE T.A accounts (a1, a2) FIELDS TERMINATED BY ',' (@,@,c,d,e,f)", 1822 output: "load data infile test/loadfile5 ignore into table t.a accounts(a1, a2) fields terminated by , (, , c, d, e, f)", 1823 }, 1824 { 1825 input: "load data infile 'data.txt' into table db.a accounts(a1, a2) fields terminated by '\t' escaped by '\t'", 1826 output: "load data infile data.txt into table db.a accounts(a1, a2) fields terminated by \t escaped by \t", 1827 }, 1828 { 1829 input: `create function helloworld () returns int language sql as 'select id from test_table limit 1'`, 1830 output: `create function helloworld () returns int language sql as 'select id from test_table limit 1'`, 1831 }, 1832 { 1833 input: `create function twosum (x int, y int) returns int language sql as 'select $1 + $2'`, 1834 output: `create function twosum (x int, y int) returns int language sql as 'select $1 + $2'`, 1835 }, 1836 { 1837 input: `create function charat (x int) returns char language sql as 'select $1'`, 1838 output: `create function charat (x int) returns char language sql as 'select $1'`, 1839 }, 1840 { 1841 input: `create function charat (x int default 15) returns char language sql as 'select $1'`, 1842 output: `create function charat (x int default 15) returns char language sql as 'select $1'`, 1843 }, 1844 { 1845 input: `create function t.increment (x float) returns float language sql as 'select $1 + 1'`, 1846 output: `create function t.increment (x float) returns float language sql as 'select $1 + 1'`, 1847 }, 1848 { 1849 input: `drop function helloworld ()`, 1850 output: `drop function helloworld ()`, 1851 }, 1852 { 1853 input: `drop function charat (int)`, 1854 output: `drop function charat (int)`, 1855 }, 1856 { 1857 input: `drop function twosum (int, int)`, 1858 output: `drop function twosum (int, int)`, 1859 }, 1860 { 1861 input: `drop function t.increment (float)`, 1862 output: `drop function t.increment (float)`, 1863 }, 1864 { 1865 input: `create extension python as strutil file 'stringutils.whl'`, 1866 output: `create extension python as strutil file stringutils.whl`, 1867 }, 1868 { 1869 input: `load strutil`, 1870 output: `load strutil`, 1871 }, 1872 { 1873 input: `select * from (values row(1, 2), row(3, 3)) as a`, 1874 }, 1875 { 1876 input: `select t1.* from (values row(1, 1), row(3, 3)) as a(c1, c2) inner join t1 on a.c1 = t1.b`, 1877 }, 1878 { 1879 input: "modump query_result '0adaxg' into '/Users/tmp/test'", 1880 output: "modump query_result 0adaxg into /Users/tmp/test fields terminated by , enclosed by \" lines terminated by \n header true", 1881 }, 1882 { 1883 input: `modump query_result "queryId" into '/Users/tmp/test' FIELDS TERMINATED BY ','`, 1884 output: "modump query_result queryId into /Users/tmp/test fields terminated by , enclosed by \" lines terminated by \n header true", 1885 }, 1886 { 1887 input: "modump query_result 'abcx' into '/Users/tmp/test' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'", 1888 output: "modump query_result abcx into /Users/tmp/test fields terminated by , enclosed by \" lines terminated by \n header true", 1889 }, 1890 { 1891 input: "modump query_result '098e32' into '/Users/tmp/test' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' header 'TRUE'", 1892 output: "modump query_result 098e32 into /Users/tmp/test fields terminated by , enclosed by \" lines terminated by \n header true", 1893 }, 1894 { 1895 input: "modump query_result '09eqr' into '/Users/tmp/test' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' header 'FALSE'", 1896 output: "modump query_result 09eqr into /Users/tmp/test fields terminated by , enclosed by \" lines terminated by \n header false", 1897 }, 1898 { 1899 input: "modump query_result 'd097i7' into '/Users/tmp/test' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' header 'FALSE' MAX_FILE_SIZE 100", 1900 output: "modump query_result d097i7 into /Users/tmp/test fields terminated by , enclosed by \" lines terminated by \n header false max_file_size 102400", 1901 }, 1902 { 1903 input: "modump query_result '09eqrteq' into '/Users/tmp/test' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' header 'FALSE' MAX_FILE_SIZE 100 FORCE_QUOTE (a, b)", 1904 output: "modump query_result 09eqrteq into /Users/tmp/test fields terminated by , enclosed by \" lines terminated by \n header false max_file_size 102400 force_quote a, b", 1905 }, 1906 { 1907 input: "show accounts", 1908 }, 1909 { 1910 input: "show accounts like '%dafgda_'", 1911 output: "show accounts like %dafgda_", 1912 }, 1913 { 1914 input: "create table test (`col` varchar(255) DEFAULT b'0')", 1915 output: "create table test (col varchar(255) default 0)", 1916 }, 1917 { 1918 input: "select trim(a)", 1919 output: "select trim(a)", 1920 }, 1921 { 1922 input: "select trim(a from a)", 1923 }, 1924 { 1925 input: "select trim(leading a from b)", 1926 }, 1927 { 1928 input: "select trim(trailing b from a)", 1929 }, 1930 { 1931 input: "select trim(both a from b) from t", 1932 }, 1933 { 1934 input: "LOCK TABLES t READ", 1935 output: "Lock Table t READ", 1936 }, 1937 { 1938 input: "LOCK TABLES t READ LOCAL", 1939 output: "Lock Table t READ LOCAL", 1940 }, 1941 { 1942 input: "LOCK TABLES t WRITE", 1943 output: "Lock Table t WRITE", 1944 }, 1945 { 1946 input: "LOCK TABLES t LOW_PRIORITY WRITE", 1947 output: "Lock Table t LOW_PRIORITY WRITE", 1948 }, 1949 { 1950 input: "LOCK TABLES t LOW_PRIORITY WRITE, t1 READ, t2 WRITE", 1951 output: "Lock Table t LOW_PRIORITY WRITE, t1 READ, t2 WRITE", 1952 }, 1953 { 1954 input: "UNLOCK TABLES", 1955 output: "UnLock Table", 1956 }, 1957 } 1958 ) 1959 1960 func TestValid(t *testing.T) { 1961 ctx := context.TODO() 1962 for _, tcase := range validSQL { 1963 if tcase.output == "" { 1964 tcase.output = tcase.input 1965 } 1966 ast, err := ParseOne(ctx, tcase.input) 1967 if err != nil { 1968 t.Errorf("Parse(%q) err: %v", tcase.input, err) 1969 continue 1970 } 1971 out := tree.String(ast, dialect.MYSQL) 1972 if tcase.output != out { 1973 t.Errorf("Parsing failed. \nExpected/Got:\n%s\n%s", tcase.output, out) 1974 } 1975 } 1976 } 1977 1978 var ( 1979 multiSQL = []struct { 1980 input string 1981 output string 1982 }{{ 1983 input: "use db1; select * from t;", 1984 output: "use db1; select * from t", 1985 }, { 1986 input: "use db1; select * from t", 1987 }, { 1988 input: "use db1; select * from t; use db2; select * from t2", 1989 }} 1990 ) 1991 1992 func TestMulti(t *testing.T) { 1993 ctx := context.TODO() 1994 for _, tcase := range multiSQL { 1995 if tcase.output == "" { 1996 tcase.output = tcase.input 1997 } 1998 asts, err := Parse(ctx, tcase.input) 1999 if err != nil { 2000 t.Errorf("Parse(%q) err: %v", tcase.input, err) 2001 continue 2002 } 2003 var res string 2004 prefix := "" 2005 for _, ast := range asts { 2006 res += prefix 2007 out := tree.String(ast, dialect.MYSQL) 2008 res += out 2009 prefix = "; " 2010 } 2011 if tcase.output != res { 2012 t.Errorf("Parsing failed. \nExpected/Got:\n%s\n%s", tcase.output, res) 2013 } 2014 } 2015 }