pingcap/parser

A MySQL Compatible SQL Parser

This repository has been moved to https://github.com/pingcap/tidb/tree/master/pkg/parser.

parser's Issues

TiDB does not recognize "@@`some_quoted_string`"

Issue discovered in #87. In MySQL 8.0:

mysql> select @@`sql_mode`, @@session.`sql_mode`, @@global.`sql_mode`\G;
*************************** 1. row ***************************
        @@`sql_mode`: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
@@session.`sql_mode`: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
 @@global.`sql_mode`: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

Meanwhile in TiDB (latest master and v2.1.1):

mysql> select @@`sql_mode`, @@session.`sql_mode`, @@global.`sql_mode`\G;
ERROR 1193 (HY000): Unknown system variable ''
ERROR: 
No query specified

---

Note that @@ does not respect ANSI_QUOTES so @@"sql_mode" will fail on MySQL. Not sure if we want to replicate this error behavior.

Alter table restore incorrect

sql:
alter table t1 add c1 int comment 'c1',
add c2 decimal (4,1) not null comment 'c2',
add c3 decimal (4,1) not null default 0.0 comment 'c3';

restore:
ALTER TABLE t1 ADD COLUMN c1 INT COMMENT 'c1', ADD COLUMN c2 DECIMAL(4,1) NOT NULL, COMMENT 'c2', ADD COLUMN c3 DECIMAL(4,1) NOT NULL, DEFAULT 0.0, COMMENT 'c3'

expected:
ALTER TABLE t1 ADD COLUMN c1 INT COMMENT 'c1', ADD COLUMN c2 DECIMAL(4,1) NOT NULL COMMENT 'c2', ADD COLUMN c3 DECIMAL(4,1) NOT NULL DEFAULT 0.0 COMMENT 'c3'

The column type `DECIMAL(20,0)` is restored incorrectly as `DECIMAL`

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

package main

import (
	"fmt"
	"github.com/pingcap/parser"
	"github.com/pingcap/parser/format"
	_ "github.com/pingcap/tidb/types/parser_driver"
	"strings"
)

func main() {
	p := parser.New()
	nodes, _ := p.ParseOneStmt(`
		create table precise_types (
			c DECIMAL(20,0) NOT NULL,
			d DOUBLE(20,0) NOT NULL
		);
	`, "", "")

	var s strings.Builder
	ctx := format.NewRestoreCtx(format.DefaultRestoreFlags, &s)
	nodes.Restore(ctx)

	fmt.Println(s.String())
}

2. What did you expect to see?

Prints

CREATE TABLE `precise_types` (`c` DECIMAL(20) NOT NULL,`d` DOUBLE(20,0) NOT NULL)

3. What did you see instead?

The precision is just directly omitted.

CREATE TABLE `precise_types` (`c` DECIMAL NOT NULL,`d` DOUBLE NOT NULL)

4. What version of TiDB SQL Parser are you using?

master (366172d)

Add integration test for CI

The CI should check that the parse could work with TiDB.
It should trigger something like:

mkdir tmp
cd tmp
echo 'module hello' > go.mod
// Get the TiDB, maybe latest master or a specified commit
go get -u github.com/pingcap/tidb@master
// Tell TiDB to use the parser from the PR branch
go mod edit -replace github.com/pingcap/parser=github.com/exialin/parser@restore-join 
// Run all tests, with the specified TiDB and parser version
go test github.com/pingcap/tidb/...

A problem is that TiDB use gofail and it's not go test-able, this can be skip using '-check.ignore'

tidb parser cann't parse tbl_name.* in delete multiple-table syntax

When I implement Restore for delete statement, I found tidb parser cann't parse tbl_name.* in delete multiple-table syntax.
for example:
delete t1.*, t2 from t1, t2
tidb parser will produce error
value *errors.withStack = line 1 column 11 near "*,t2 from t1, t2" ("line 1 column 11 near \"*,t2 from t1, t2\" ")

Support ON UPDATE ddl statements

Bug Report

1. What did you do?

Looking at the ddl tests in the testDDLSuite, I was under the impression from TestDDLOnUpdateRestore that parsing sql statements with "ON UPDATE" were supported, however, in attempting to parse the following statement I encountered an error.

Code snippet:

 p := parser.New()
 testSQL := "create table test(id int key, FOREIGN KEY (id) REFERENCES test2(id) ON UPDATE CASCADE);"
 if _, _, err := p.Parse(testSQL, "", ""); err != nil {
     logger.Error("failed to parse", zap.Error(err))
 } 

2. What did you expect to see?

I expected this statement to be able to be parsed without an issue.

3. What did you see instead?

I encountered the error "error":"line 1 column 77 near \"UPDATE CASCADE);\"

Looking closer at the current tests in TestDDLOnUpdateRestore

func (ts *testDDLSuite) TestDDLOnUpdateRestore(c *C) {
    testCases := []NodeRestoreTestCase{
        {"ON UPDATE RESTRICT", "ON UPDATE RESTRICT"},
        {"on update CASCADE", "ON UPDATE CASCADE"},
        {"on update SET NULL", "ON UPDATE SET NULL"},
        {"on update no action", "ON UPDATE NO ACTION"},
    }
    extractNodeFunc := func(node Node) Node {
        return node.(*CreateTableStmt).Constraints[1].Refer.OnUpdate
    }
    RunNodeRestoreTest(c, testCases, "CREATE TABLE child ( id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id)  ON DELETE CASCADE %s )", extractNodeFunc)
}

All ON UPDATE test cases are inserted after an ON DELETE CASCADE clause. When I removed, the ON DELETE and instead used

RunNodeRestoreTest(c, testCases, "CREATE TABLE child ( id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) %s )", extractNodeFunc)

The tests for TestDDLOnUpdateRestore start to fail

----------------------------------------------------------------------                                                                                                                   
FAIL: ddl_test.go:91: testDDLSuite.TestDDLOnUpdateRestore                                                                                                                                
                                                                                                                                                                                         
ddl_test.go:101:                                                                                                                                                                         
    RunNodeRestoreTest(c, testCases, "CREATE TABLE child ( id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) %s )", extractNodeFunc)       
util_test.go:113:                                                                                                                                                                        
    c.Assert(err, IsNil, comment)                                                                                                                                                        
... value *errors.withStack = line 1 column 126 near "UPDATE RESTRICT )"  ("line 1 column 126 near \"UPDATE RESTRICT )\" ")                                                              
... source ast_test.NodeRestoreTestCase{sourceSQL:"ON UPDATE RESTRICT", expectSQL:"ON UPDATE RESTRICT"}                                                                                  

Are DDL statements like create table test(id int key, FOREIGN KEY (id) REFERENCES test2(id) ON UPDATE CASCADE); not supported?

4. What version of TiDB SQL Parser are you using?

My go.mod file looks like this:

 github.com/pingcap/parser v0.0.0-20190312024907-3f6280b08c8b
 github.com/pingcap/tidb v0.0.0-20181120082053-821af9e9f65b9901bcac1661f28c41985697b511

test case not pass

func TestSetQuery2(t *testing.T) {
   sql := "SET @master_heartbeat_period=100"
   sqlParser := parser.New()
   stmt, err := sqlParser.ParseOneStmt(sql, "", "")
   assert.Nil(t, err)
   assert.IsType(t, &ast.SetStmt{}, stmt)

   //sql := "SET @master_binlog_checksum= @@global.binlog_checksum"
   //sqlParser := parser.New()
   //stmt, err := sqlParser.ParseOneStmt(sql, "", "")
   //assert.Nil(t, err)
   //assert.IsType(t, stmt, &ast.SetStmt{})
}

go test -v -test.run TestSetQuery2

this case not pass,but the comment code will pass.

Invalid character inside an optimizer hint comment will hang the parser

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

Run this SQL:

DELETE /*+ 😅 */;

2. What did you expect to see?

A syntax error immediately returned.

3. What did you see instead?

TiDB stuck in an infinite loop while parsing.

(Note: same effect if you replace 😅 by the characters \x00 or \x01)

4. What version of TiDB SQL Parser are you using?

master (4628ac3)

Support CREATE USER extensions per MySQL 5.7

Via pingcap/tidb#7733 - this command does not currently work in TiDB:

mysql> CREATE USER 'msandbox'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
ERROR 1105 (HY000): line 1 column 129 near " NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK" (total length 173)

MySQL 5.7 produces this result from the command SHOW CREATE USER, so it is important to parse and the unsupported parts could probably be ignored for now.

How to get comment info in SelectStmt?

Question

After parsing a SELECT SQL successfully, how to get comment info in SelectStmt? like this:

/* this is a comment */ SELECT * from tbl;

FK with no default database gives unexpected error

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

drop database if exists fk_test;
create database if not exists fk_test;
create table test.t1 (c1 int not null primary key);
create table test.t2 (id int not null primary key, c1 int not null, constraint foreign key (c1) references t1(c1))

2. What did you expect to see?

In MySQL, the create table statement with the foreign key constraint does not cause an error. Presumably, MySQL assumes that the parent table is in the same database as the child table.

3. What did you see instead?

TiDB Server issues an error in this case.

$ cat fk_test.sql
drop database if exists fk_test;
create database if not exists fk_test;
create table fk_test.t1 (c1 int not null primary key);
create table fk_test.t2 (id int not null primary key, c1 int not null, constraint foreign key (c1) references t1(c1))
$ mysql --verbose -P 3306 --database= < fk_test.sql
--------------
drop database if exists fk_test
--------------

--------------
create database if not exists fk_test
--------------

--------------
create table fk_test.t1 (c1 int not null primary key)
--------------

--------------
create table fk_test.t2 (id int not null primary key, c1 int not null, constraint foreign key (c1) references t1(c1))
--------------

$ mysql --verbose -P 4000 --database= < fk_test.sql
--------------
drop database if exists fk_test
--------------

--------------
create database if not exists fk_test
--------------

--------------
create table fk_test.t1 (c1 int not null primary key)
--------------

--------------
create table fk_test.t2 (id int not null primary key, c1 int not null, constraint foreign key (c1) references t1(c1))
--------------

ERROR 1046 (3D000) at line 4: No database selected

I encountered this issue when trying to load the "employees" sample database (https://github.com/datacharmer/test_db.git) into MySQL->DM->TiDB.

4. What version of TiDB SQL Parser are you using?

Release Version: v3.0.0-beta.1-77-g1e5f62096
Git Commit Hash: 1e5f620966370f3bc5c48d78c17bcd3a3234066f
Git Branch: master
UTC Build Time: 2019-04-08 11:09:58
GoVersion: go version go1.12.1 darwin/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false

github.com/pingcap/parser v0.0.0-20190408064140-cdceeb2c5476

Add issue and pull request template

As the title says. Please copy the template from tidb repo.

support the `FULLTEXT INDEX` syntax

package main

import (
	"fmt"

	"github.com/pingcap/parser"
	_ "github.com/pingcap/tidb/types/parser_driver"
)

func main() {
	sql := "CREATE FULLTEXT INDEX ft_ip ON tb (ip);"
	fmt.Println(parser.New().ParseOneStmt(sql, "", ""))
}

line 1 column 15 near " INDEX ft_ip ON tb (ip);" (total length 39)

CURRENT_TIMESTAMP FuncCallExpr should support args

like the question on so :https://stackoverflow.com/questions/23671222/mysql-default-value-for-timestamp3

mysql> create table tbl_so_q23671222_1( ts timestamp(3) default now() );
ERROR 1067 (42000): Invalid default value for 'ts'
mysql> create table tbl_so_q23671222_1( ts timestamp(3) default now(3) );
Query OK, 0 rows affected (0.59 sec)
mysql> create table tbl_so_q23671222_2( ts timestamp(3) default current_timestamp );
ERROR 1067 (42000): Invalid default value for 'ts'
mysql> create table tbl_so_q23671222_2( ts timestamp(3) default current_timestamp(3) );
Query OK, 0 rows affected (0.38 sec)

current_timestamp(3) should be parsed, otherwise it's not a valid ddl.

version: 5958b6f

example:

func TestDDL(t *testing.T) {
	s := `create table t(dt datetime(6) not null default current_timestamp(6) on update current_timestamp(6))`
	p := parser.New()
	stmt, err := p.ParseOneStmt(s, "", "")
	require.NoError(t, err)
	b := bytes.NewBufferString("")
	ctx := format.NewRestoreCtx(format.DefaultRestoreFlags, b)
	require.NoError(t, stmt.Restore(ctx))
	fmt.Println(b.String())
}

outputs

`CREATE TABLE `t` (`dt` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP())` 

which is invalid.

decouple Assignment and ColumnName

Feature Request

Currently, the Assignment holds the *ColumnName directly,

type Assignment struct {
	node
	// Column is the column name to be assigned.
	Column *ColumnName
	// Expr is the expression assigning to ColName.
	Expr ExprNode
}

Is it possible to hold the *ColumnNameExpr instead of *ColumnName? We use the UpdateStmt and want to decorate the column names in Assignment, but it is coupled with *ColumnName now.

ERROR 1105 (HY000): inconsistent extra index PRIMARY

CREATE TABLE sys_dept (
id int(11) NOT NULL,
name varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE sys_user (
plat varchar(255) NOT NULL,
id varchar(255) NOT NULL,
dept int(11) DEFAULT NULL,
PRIMARY KEY (plat,id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into sys_dept values(1, 'xian');
insert into sys_dept values(2, 'shanghai');

insert into sys_user values('p', 'mofy', 1);
insert into sys_user values('p', 'yangyy', 2);

delete from sys_user where dept in(select id from sys_dept where name = 'shanghai');

select count(*) from sys_user where plat= 'p' and id = 'yangyy';
count should return 0, but return 1 instead.

select * from sys_user where plat= 'p' and id = 'yangyy';
should return Empty set, but return ERROR 1105 (HY000): inconsistent extra index PRIMARY

field ExtendValue is not visited in VariableAssignment.Accept

Question

// VariableAssignment is a variable assignment struct.
type VariableAssignment struct {
	node
	Name     string
	Value    ExprNode
	IsGlobal bool
	IsSystem bool

	// ExtendValue is a way to store extended info.
	// VariableAssignment should be able to store information for SetCharset/SetPWD Stmt.
	// For SetCharsetStmt, Value is charset, ExtendValue is collation.
	// TODO: Use SetStmt to implement set password statement.
	ExtendValue ValueExpr
}

// Accept implements Node interface.
func (n *VariableAssignment) Accept(v Visitor) (Node, bool) {
	newNode, skipChildren := v.Enter(n)
	if skipChildren {
		return v.Leave(newNode)
	}
	n = newNode.(*VariableAssignment)
	node, ok := n.Value.Accept(v)
	if !ok {
		return n, false
	}
	n.Value = node.(ExprNode)
	return v.Leave(n)
}

the ExtendValue is not visited in Accept. If the SQL is SET NAMES 'utf8' COLLATE 'utf8_general_ci', the collation clause cannot be visited by a visitor. Is it a bug or a feature?

Syntax error in optimizer hints should not invalidate the entire hint

On MySQL 8, all optimizer hints parsed before a syntax error will be accepted:

$ echo 'SELECT SLEEP(1);' | mysql -u root
SLEEP(1)
0

$ echo 'SELECT /*+ max_execution_time(2) */ SLEEP(1);' | mysql -u root
SLEEP(1)
1

$ echo 'SELECT /*+ max_execution_time(2) (error) */ SLEEP(1);' | mysql -u root
SLEEP(1)
1

$ echo 'SELECT /*+ (error) max_execution_time(2) */ SLEEP(1);' | mysql -u root
SLEEP(1)
0

(returning 0 means hint is ignored, returning 1 means hint is used)

On TiDB Parser, however, once an error is encountered the entire list of hints are thrown out, different from MySQL's behavior.

(TiDB Parser version = 89ae120)

some test case failed

func main(){

    sql := "select * from wn where id = 2"

    pa := parser.New()

    //set charsetInfo and collation
    charsetInfo, collation := "utf8", "utf8_general_ci" 

    pa.SetSQLMode(mysql.ModeNone)

    stmtNodes, err := pa.Parse( sql, charsetInfo, collation)

    fmt.Println(stmtNodes[0], err)
}

It works when I use sql="select * from wn"

But it failed when I use sql="select * from wn where id = 2"

The golang version is 1.11.1

panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0x0 pc=0x5e3ef8]

goroutine 1 [running]:
github.com/pingcap/parser.yyParse(0x715e60, 0xc000154048, 0xc000154000, 0xc00010bf68)
	/root/wn/go/pkg/mod/github.com/pingcap/[email protected]/parser.go:8654 +0x16ac8
github.com/pingcap/parser.(*Parser).Parse(0xc000154000, 0x7fffc6f056eb, 0x1b, 0x6b7519, 0x4, 0x6bd81d, 0xf, 0x0, 0x0, 0xc00010bf10, ...)
	/root/wn/go/pkg/mod/github.com/pingcap/[email protected]/yy_parser.go:101 +0x15f
main.main()
	/root/wn/go/test2/hello.go:26 +0x2a9

parser crashes when given comments

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

parser.ParseOneStmt("/*!SET;SET;*/")

2. What did you expect to see?

Parsed the comment correctly or return error if comments are not supported.

3. What did you see instead?

Panic in lexer.go:

... Panic: runtime error: index out of range (PC=0x402F344)

/usr/local/Cellar/go/1.12.5/libexec/src/runtime/panic.go:522
  in gopanic
/usr/local/Cellar/go/1.12.5/libexec/src/runtime/panic.go:44
  in panicindex
lexer.go:117
  in Scanner.stmtText

4. What version of TiDB SQL Parser are you using?

8395edb

Parser example doesn't work

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

I am trying this out according to https://godoc.org/gopkg.in/lysu/parser.v1#example-package--ParseSQL

package main

import (
    "fmt"

    "github.com/pingcap/parser"
    _ "github.com/pingcap/tidb/types/parser_driver"
)

func main() {

    // 0. make sure import parser_driver implemented by TiDB(user also can implement own driver by self).
    // and add `import _ "github.com/pingcap/tidb/types/parser_driver"` in the head of file.

    // 1. Create a parser. The parser is NOT goroutine safe and should
    // not be shared among multiple goroutines. However, parser is also
    // heavy, so each goroutine should reuse its own local instance if
    // possible.
    p := parser.New()

    // 2. Parse a text SQL into AST([]ast.StmtNode).
    stmtNodes, _, err := p.Parse("select * from tbl where id = 1", "", "")

    // 3. Use AST to do cool things.
    fmt.Println(stmtNodes[0], err)

}

$ go build main.go
go: finding github.com/pingcap/tidb/types/parser_driver latest
go: finding github.com/pingcap/tidb/types latest
build command-line-arguments: cannot load github.com/pingcap/tidb/types/parser_driver: cannot find module providing package github.com/pingcap/tidb/types/parser_driver

2. What did you expect to see?

build success

3. What did you see instead?

Can't find package

4. What version of TiDB SQL Parser are you using?

Not sure, the latest?

FLUSH SLOW LOGS parse error

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

2. What did you expect to see?

3. What did you see instead?

4. What version of TiDB SQL Parser are you using?

Support ANALYZE TABLE .. UPDATE HISTOGRAM syntax

Feature Request

Is your feature request related to a problem? Please describe:

Relates to pingcap/tidb#10829

Describe the feature you'd like:

The parser should support MySQL's histogram syntax:

ANALYZE TABLE `t1` UPDATE HISTOGRAM ON `b` WITH 1024 BUCKETS;

Describe alternatives you've considered:

MySQL 8.0 compatibility

Teachability, Documentation, Adoption, Migration Strategy:

MySQL 8.0 compatibility (mysqldump in 8.0 will require this to be supported if information_schema.column_statistics is not empty).

query `(back quote) should report syntax error

Bug Report

sql:

ALTER TABLE `table1`ADD INDEX`idx_name_age` (`name` ASC, `age` ASC)

table1 ADD no space/ INDEX idx_name_age no space.

The correct sql should be:

ALTER TABLE `table1`       ADD   INDEX      `idx_name_age` (`name` ASC, `age` ASC)

Readme cannot make test succssful

Question

In README.md, it say:

In your TiDB repository, execute the replace instruction to make your parser changes take effect:
First, GO111MODULE=on go mod edit -replace github.com/pingcap/parser=github.com/your-repo/parser@your-branch
Then , make dev to run CI in TiDB.

But indeed, I got :

xiekeyi@ubuntu:~/gopath/src/github.com/pingcap/tidb$ GO111MODULE=on go mod edit -replace github.com/pingcap/parser=github.com/xiekeyi98/parser@master
xiekeyi@ubuntu:~/gopath/src/github.com/pingcap/tidb$ make dev
cat checklist.md
# Following the checklist saves the reviewers' time and gets your PR reviewed faster.

# Self Review
Have you reviewed every line of your changes by yourself?

# Test
Have you added enough test cases to cover the new feature or bug fix?
Also, add comments to describe your test cases.

# Naming
Do function names keep consistent with its behavior?
Is it easy to infer the function's behavior by its name?

# Comment
Is there any code that confuses the reviewer?
Add comments on them! You'll be asked to do so anyway.
Make sure there is no syntax or spelling error in your comments.
Some online syntax checking tools like Grammarly may be helpful.

# Refactor
Is there any way to refactor the code to make it more readable?
If the refactoring touches a lot of existing code, send another PR to do it.

# Single Purpose
Make sure the PR does only one thing and nothing else.

# Diff Size
Make sure the diff size is no more than 500, split it into small PRs if it is too large.
GO111MODULE=on go list -f '{{ join .Imports "\n" }}' github.com/pingcap/tidb/store/tikv | grep ^github.com/pingcap/parser$ || exit 0; exit 1
go: finding github.com/xiekeyi98/parser master
go: github.com/xiekeyi98/[email protected] used for two different module paths (github.com/pingcap/parser and github.com/xiekeyi98/parser)
GO111MODULE=on go build -o tools/bin/gofail github.com/pingcap/gofail
go: github.com/xiekeyi98/[email protected] used for two different module paths (github.com/pingcap/parser and github.com/xiekeyi98/parser)
Makefile:228: recipe for target 'tools/bin/gofail' failed
make: *** [tools/bin/gofail] Error 1

I think it is necessary to update README.md to explain how to solve this problem or update Makefile to avoid it .

Help to explain a little bit the meaning of the SubqueryExpr.

Hi TiDB team,
I'd like to work on the SubqueryExpr task but I'm confusing on the meaning of the SubqueryExpr. Can you help to add some comments or give some example to explain?

Thanks!

TiDB deduced wrong column name for variables with quoted content "@`foo`"

Discovered in #87. The following SQL:

set @abc = 'foobar';
select @abc, @`abc`, @'aBc', @"AbC";
set @6 = '?!';
select @6, @`6`, @'6', @"6";

produces the following in MySQL 8.0:

mysql> set @abc = 'foobar';
Query OK, 0 rows affected (0.00 sec)

mysql> select @abc, @`abc`, @'aBc', @"AbC";
+--------+--------+--------+--------+
| @abc   | @`abc` | @'aBc' | @"AbC" |
+--------+--------+--------+--------+
| foobar | foobar | foobar | foobar |
+--------+--------+--------+--------+
1 row in set (0.00 sec)

mysql> set @6 = '?!';
Query OK, 0 rows affected (0.00 sec)

mysql> select @6, @`6`, @'6', @"6";
+------+------+------+------+
| @6   | @`6` | @'6' | @"6" |
+------+------+------+------+
| ?!   | ?!   | ?!   | ?!   |
+------+------+------+------+
1 row in set (0.00 sec)

and the following in TiDB latest master (pingcap/tidb@a7907ed) and v2.1.1:

mysql> set @abc = 'foobar';
Query OK, 0 rows affected (0.00 sec)

mysql> select @abc, @`abc`, @'aBc', @"AbC";
+--------+--------+--------+--------+
| @abc   | `abc`  | 'aBc'  | "AbC"  |
+--------+--------+--------+--------+
| foobar | foobar | foobar | foobar |
+--------+--------+--------+--------+
1 row in set (0.00 sec)

mysql> set @6 = '?!';
Query OK, 0 rows affected (0.00 sec)

mysql> select @6, @`6`, @'6', @"6";
+------+------+------+------+
| @6   | `6`  | '6'  | "6"  |
+------+------+------+------+
| ?!   | ?!   | ?!   | ?!   |
+------+------+------+------+
1 row in set (0.00 sec)

Note the column headers in TiDB are missing the leading @.

cannot find module providing package github.com/pingcap/tidb/types/parser_driver

simple test program.

➜  gotest cat main.go
package main // import "tt"

import (
	"fmt"

	"github.com/pingcap/parser"

	_ "github.com/pingcap/tidb/types/parser_driver"
)

func main() {
	sql := "select 1"

	p := parser.New()

	node, err := p.ParseOneStmt(sql, "", "")
	if err != nil {
		fmt.Printf("parse one statement error: %s", sql)
		return
	}

}

go mod init

➜  gotest cat go.mod
module tt

require (
	github.com/golang/protobuf v1.2.0 // indirect
	github.com/pingcap/errors v0.11.0 // indirect
	github.com/pingcap/parser v0.0.0-20190120153311-05caf0a5ea61
	github.com/pingcap/tidb v2.0.11+incompatible // indirect
	github.com/pingcap/tipb v0.0.0-20190107072121-abbec73437b7 // indirect
	github.com/sirupsen/logrus v1.3.0 // indirect
	golang.org/x/text v0.3.0 // indirect
)

go build

➜  gotest go build
go: finding github.com/pingcap/parser latest
go: finding github.com/pingcap/tidb/types/parser_driver latest
go: finding github.com/pingcap/tidb/types latest
go: finding github.com/pingcap/tidb/util/hack latest
go: finding github.com/pingcap/tidb/util latest
go: finding github.com/pingcap/tipb/go-tipb latest
go: finding github.com/pingcap/tipb latest
go: finding golang.org/x/text/encoding/japanese latest
go: finding golang.org/x/text/encoding/unicode latest
go: finding golang.org/x/text/encoding/traditionalchinese latest
go: finding golang.org/x/text/encoding/korean latest
go: finding golang.org/x/text/encoding latest
go: finding golang.org/x/text/encoding/charmap latest
go: finding golang.org/x/text/encoding/simplifiedchinese latest
go: finding github.com/golang/protobuf/proto latest
main.go:8:2: unknown import path "github.com/pingcap/tidb/types/parser_driver": cannot find module providing package github.com/pingcap/tidb/types/parser_driver

why the go.mod in this repository named go.mod1?

Index Hints in query not handled correctly

Bug Report

1. What did you do?

Hello! I have a program that uses the pingcap/parser library. In the course of developing this program, I uncovered a bug in the SQL-printing logic for SELECT statements that have index hints in them. For simplicity, imagine my program simply parses a select statement, and then calls Restore on that parsed statement to generate a string representation of the query. In the case that I found, the restored query does not conform with MySQL syntax.

2. What did you expect to see?

Imagine the query:

SELECT t.id FROM test.table AS t USE INDEX (PRIMARY)

When I parse this into an *ast.SelectStmt, then call Restore to generate the string value of the query from the SelectStmt, I expect to see the same query, plus or minus formatting stuff.

3. What did you see instead?

Instead, I get the following:

SELECT t.id FROM test.table USE INDEX (PRIMARY) AS t

Note that the index hint is before the AS t section of the query. This is not valid syntax.

4. What version of TiDB SQL Parser are you using?

My go.mod file looks like this:

 github.com/pingcap/parser v0.0.0-20190312024907-3f6280b08c8b
 github.com/pingcap/tidb v0.0.0-20181120082053-821af9e9f65b9901bcac1661f28c41985697b511

incompatible with MySQL when calculate display length of numeric type

Problem

When parsing ddl, and when no Flen assigned, TiDB's parse result is not compatible with MySQL.

These are types after ddl executed by MySQL 5.7:

• tinyint(3) unsigned
• smallint(5) unsigned
• mediumint(8) unsigned
• int(10) unsigned

These are types after parsed by TiDB parser:

• tinyint(4) unsigned
• smallint(6) unsigned
• mediumint(9) unsigned
• int(11) unsigned

Cause

Because func FieldType.CompactStr() in file types/field_type.go doesn't take account the sign of numeric.

Fix

Refer souce of MySQL 5.7, take account the sign of numeric, when calculate default field length.

parser return different ast from the same sql

select AVG(test_score)
select AVG(`test_score`)

this two sql parsed to different ast by parser:

the ast stmt parsed from select AVG(test_score)

&ast.SelectStmt{dmlNode:ast.dmlNode{stmtNode:ast.stmtNode{node:ast.node{text:"select AVG(test_score)"}}}, resultSetNode:ast.resultSetNode{resultFields:[]*ast.ResultField(nil)}, SelectStmtOpts:(*ast.SelectStmtOpts)(0xc0000854d0), Distinct:false, From:(*ast.TableRefsClause)(nil), Where:ast.ExprNode(nil), Fields:(*ast.FieldList)(0xc000085500), GroupBy:(*ast.GroupByClause)(nil), Having:(*ast.HavingClause)(nil), WindowSpecs:[]ast.WindowSpec(nil), OrderBy:(*ast.OrderByClause)(nil), Limit:(*ast.Limit)(nil), LockTp:0, TableHints:[]*ast.TableOptimizerHint(nil), IsAfterUnionDistinct:false, IsInBraces:false}
&ast.FieldList{node:ast.node{text:""}, Fields:[]*ast.SelectField{(*ast.SelectField)(0xc0000925a0)}}
&ast.SelectField{node:ast.node{text:"AVG(test_score)"}, Offset:7, WildCard:(*ast.WildCardField)(nil), Expr:(*ast.AggregateFuncExpr)(0xc0000baa00), AsName:model.CIStr{O:"", L:""}, Auxiliary:false}
&ast.AggregateFuncExpr{funcNode:ast.funcNode{exprNode:ast.exprNode{node:ast.node{text:""}, Type:types.FieldType{Tp:0x0, Flag:0x0, Flen:0, Decimal:0, Charset:"", Collate:"", Elems:[]string(nil)}, flag:0x18}}, F:"AVG", Args:[]ast.ExprNode{(*ast.ColumnNameExpr)(0xc000166080)}, Distinct:false}
&ast.ColumnNameExpr{exprNode:ast.exprNode{node:ast.node{text:""}, Type:types.FieldType{Tp:0x0, Flag:0x0, Flen:0, Decimal:0, Charset:"", Collate:"", Elems:[]string(nil)}, flag:0x8}, Name:(*ast.ColumnName)(0xc000127810), Refer:(*ast.ResultField)(nil)}
&ast.ColumnName{node:ast.node{text:""}, Schema:model.CIStr{O:"", L:""}, Table:model.CIStr{O:"", L:""}, Name:model.CIStr{O:"test_score", L:"test_score"}}

the ast stmt parsed from "select AVG(`test_score`)":

&ast.SelectStmt{dmlNode:ast.dmlNode{stmtNode:ast.stmtNode{node:ast.node{text:"select `AVG`(`test_score`)"}}}, resultSetNode:ast.resultSetNode{resultFields:[]*ast.ResultField(nil)}, SelectStmtOpts:(*ast.SelectStmtOpts)(0xc000085560), Distinct:false, From:(*ast.TableRefsClause)(nil), Where:ast.ExprNode(nil), Fields:(*ast.FieldList)(0xc000085590), GroupBy:(*ast.GroupByClause)(nil), Having:(*ast.HavingClause)(nil), WindowSpecs:[]ast.WindowSpec(nil), OrderBy:(*ast.OrderByClause)(nil), Limit:(*ast.Limit)(nil), LockTp:0, TableHints:[]*ast.TableOptimizerHint(nil), IsAfterUnionDistinct:false, IsInBraces:false}
&ast.FieldList{node:ast.node{text:""}, Fields:[]*ast.SelectField{(*ast.SelectField)(0xc000092720)}}
&ast.SelectField{node:ast.node{text:"`AVG`(`test_score`)"}, Offset:7, WildCard:(*ast.WildCardField)(nil), Expr:(*ast.FuncCallExpr)(0xc0000ee790), AsName:model.CIStr{O:"", L:""}, Auxiliary:false}
&ast.FuncCallExpr{funcNode:ast.funcNode{exprNode:ast.exprNode{node:ast.node{text:""}, Type:types.FieldType{Tp:0x0, Flag:0x0, Flen:0, Decimal:0, Charset:"", Collate:"", Elems:[]string(nil)}, flag:0xc}}, FnName:model.CIStr{O:"AVG", L:"avg"}, Args:[]ast.ExprNode{(*ast.ColumnNameExpr)(0xc000166180)}}
&ast.ColumnNameExpr{exprNode:ast.exprNode{node:ast.node{text:""}, Type:types.FieldType{Tp:0x0, Flag:0x0, Flen:0, Decimal:0, Charset:"", Collate:"", Elems:[]string(nil)}, flag:0x8}, Name:(*ast.ColumnName)(0xc000127a40), Refer:(*ast.ResultField)(nil)}
&ast.ColumnName{node:ast.node{text:""}, Schema:model.CIStr{O:"", L:""}, Table:model.CIStr{O:"", L:""}, Name:model.CIStr{O:"test_score", L:"test_score"}}

one is AggregateFuncExpr another is FuncCallExpr

fail to build on macOS

There are several problems in the Makefile

$ rm -f parser.go; make 

make parser
bin/goyacc -o /dev/null parser.y
make[1]: bin/goyacc: No such file or directory
make[1]: *** [parser] Error 1
make: *** [parser.go] Error 2


make parser
bin/goyacc -o /dev/null parser.y
Parse table entries: 758041 of 1909026, x 16 bits == 1516082 bytes
bin/goyacc -o parser.go parser.y 2>&1 | egrep "(shift|reduce)/reduce" | awk '{print} END {if (NR > 0) {print "Find conflict in parser.y. Please check y.output for more information."; exit 1;}}'
rm -f y.output
sed: -e: No such file or directory
make[1]: *** [parser] Error 1
make: *** [parser.go] Error 2

Failed to parse WHERE / GROUP BY clauses in a SELECT statement without a FROM table

These SQLs:

select 1 where true;
select 1 group by 1;
select 1 having true;

All return one row with the value 1 on MySQL 8.0 (also tested on PostgreSQL 9.6 and SQLite 3.24 with the same behavior), but failed to parse on TiDB 2.1.1 and master:

mysql> select 1 where true;
ERROR 1105 (HY000): line 1 column 14 near " true" (total length 19)
mysql> select 1 group by 1;
ERROR 1105 (HY000): line 1 column 14 near " by 1" (total length 19)
mysql> select 1 having true;
ERROR 1105 (HY000): line 1 column 15 near " true" (total length 20)

---

Table-less queries which are correctly accepted by TiDB:

select 1 order by 1;
select 1 limit 1;

Support SHUTDOWN sql command

Support parser functionality for pingcap/tidb#5046

`TableName` rule should not derive to `Identifier '.' '*'`

In fix of #188, the following new rule was introduced:

parser/parser.y

Lines 4886 to 4898 in 690470b

	TableName:
	Identifier
	{
	$$ = &ast.TableName{Name:model.NewCIStr($1)}
	}
	| Identifier '.' Identifier
	{
	$$ = &ast.TableName{Schema:model.NewCIStr($1), Name:model.NewCIStr($3)}
	}
	| Identifier '.' '*'
	{
	$$ = &ast.TableName{Name:model.NewCIStr($1)}
	}

but this affects all uses of TableName and leads to some non-sense syntax being accepted, e.g.

ANALYZE TABLE a.*;
-- should throw syntax error, but accepted and treated as equivalent to ANALYZE TABLE a;

We should create a separate rule for DELETE statement instead, and revert the TableName change.

This repository needs a CI

1. Ensure parser.go is up-to-date with parser.y
2. Run go test.

Unfortunately the yyXLAT array generated by goyacc is unstable regarding case-insensitive keywords:

Potential diff after running `make parser`

@@ -1054,8 +1053,8 @@ var (
 		57812: 533, // AssignmentList (3x)
 		57825: 534, // ByItem (3x)
 		57842: 535, // ColumnPosition (3x)
-		57848: 536, // Constraint (3x)
-		57380: 537, // constraint (3x)
+		57380: 536, // constraint (3x)
+		57848: 537, // Constraint (3x)
 		57850: 538, // ConstraintKeywordOpt (3x)
 		57892: 539, // ExplainableStmt (3x)
 		57909: 540, // FloatOpt (3x)
@@ -1292,8 +1291,8 @@ var (
 		58064: 771, // ShowIndexKwd (1x)
 		58068: 772, // ShowTargetFilterable (1x)
 		58072: 773, // Start (1x)
-		58073: 774, // Starting (1x)
-		57501: 775, // starting (1x)
+		57501: 774, // starting (1x)
+		58073: 775, // Starting (1x)
 		58075: 776, // StatementList (1x)
 		57504: 777, // stored (1x)
 		58080: 778, // StringType (1x)
@@ -1891,8 +1890,8 @@ var (
 		"AssignmentList",
 		"ByItem",
 		"ColumnPosition",
-		"Constraint",
 		"constraint",
+		"Constraint",
 		"ConstraintKeywordOpt",
 		"ExplainableStmt",
 		"FloatOpt",
@@ -2129,8 +2128,8 @@ var (
 		"ShowIndexKwd",
 		"ShowTargetFilterable",
 		"Start",
-		"Starting",
 		"starting",
+		"Starting",
 		"StatementList",
 		"stored",
 		"StringType",
@@ -3304,7 +3303,7 @@ var (
 		{539, 1},
 		{776, 1},
 		{776, 3},
-		{536, 2},
+		{537, 2},
 		{662, 1},
 		{662, 1},
 		{662, 4},
@@ -3525,8 +3524,8 @@ var (
 		{710, 3},
 		{736, 0},
 		{736, 3},
-		{774, 0},
-		{774, 3},
+		{775, 0},
+		{775, 3},
 		{737, 0},
 		{737, 3},
 		{672, 2},
@@ -3979,7 +3978,7 @@ var (
 		// 355
 		{23, 23, 206: 23, 347: 23, 360: 23},
 		{38, 38, 206: 38, 347: 1720, 725: 1719},
-		{20, 20, 206: 20, 347: 20, 501: 20, 774: 1711, 1712},
+		{20, 20, 206: 20, 347: 20, 501: 20, 774: 1712, 1711},
 		{18, 18, 206: 18, 347: 18, 501: 1716, 737: 1715},
 		{362: 1713},
 		// 360
@@ -5599,7 +5598,7 @@ var (
 		// 1705
 		{1196, 1196},
 		{2: 1540, 1463, 1464, 1496, 7: 1474, 1545, 1489, 1542, 1508, 1514, 1543, 1541, 1544, 1555, 1547, 1548, 1551, 1583, 22: 1576, 1595, 1517, 1520, 1521, 1475, 1611, 1495, 1538, 1651, 1631, 1633, 1632, 1532, 1483, 1503, 1589, 1590, 1586, 1552, 1594, 1534, 1488, 1572, 1610, 1494, 1507, 1509, 1482, 1481, 1556, 1505, 1579, 1519, 1524, 1537, 1564, 1609, 1502, 1557, 1587, 1567, 1592, 1606, 1603, 1581, 1529, 1530, 1619, 1467, 1574, 1620, 1476, 1477, 1478, 1639, 1622, 1484, 1569, 1485, 1487, 1570, 1497, 1498, 1647, 1623, 1577, 1573, 1511, 1512, 1608, 1516, 1625, 1518, 1525, 1526, 1528, 1461, 1465, 1468, 1470, 1469, 1471, 1621, 1617, 1473, 1604, 1539, 1559, 1479, 1480, 1486, 1490, 1491, 1578, 1582, 1500, 1575, 1501, 1553, 1492, 1566, 1648, 1612, 1624, 1504, 1563, 1546, 1599, 1600, 1601, 1602, 1613, 1533, 1549, 1580, 1561, 1591, 1588, 1593, 1653, 1618, 1558, 1616, 1562, 1513, 1596, 1597, 1605, 1598, 1515, 1628, 1629, 1627, 1626, 1607, 1614, 1522, 1523, 1630, 1660, 1527, 1554, 1560, 1615, 1531, 1634, 1535, 1462, 1466, 1635, 1636, 1637, 1472, 1638, 1585, 1640, 1641, 1642, 1643, 1493, 1644, 1499, 1645, 1646, 1457, 1650, 1649, 1506, 1652, 1654, 1510, 1571, 1550, 1584, 1536, 1565, 1568, 1655, 1656, 1657, 1658, 1659, 1661, 348: 1662, 1459, 1460, 1458, 429: 3320},
-		{2: 1540, 1463, 1464, 1496, 7: 1474, 1545, 1489, 1542, 1508, 1514, 1543, 1541, 1544, 1555, 1547, 1548, 1551, 1583, 22: 1576, 1595, 1517, 1520, 1521, 1475, 1611, 1495, 1538, 1651, 1631, 1633, 1632, 1532, 1483, 1503, 1589, 1590, 1586, 1552, 1594, 1534, 1488, 1572, 1610, 1494, 1507, 1509, 1482, 1481, 1556, 1505, 1579, 1519, 1524, 1537, 1564, 1609, 1502, 1557, 1587, 1567, 1592, 1606, 1603, 1581, 1529, 1530, 1619, 1467, 1574, 1620, 1476, 1477, 1478, 1639, 1622, 1484, 1569, 1485, 1487, 1570, 1497, 1498, 1647, 1623, 1577, 1573, 1511, 1512, 1608, 1516, 1625, 1518, 1525, 1526, 1528, 1461, 1465, 1468, 1470, 1469, 1471, 1621, 1617, 1473, 1604, 1539, 1559, 1479, 1480, 1486, 1490, 1491, 1578, 1582, 1500, 1575, 1501, 1553, 1492, 1566, 1648, 1612, 1624, 1504, 1563, 1546, 1599, 1600, 1601, 1602, 1613, 1533, 1549, 1580, 1561, 1591, 1588, 1593, 1653, 1618, 1558, 1616, 1562, 1513, 1596, 1597, 1605, 1598, 1515, 1628, 1629, 1627, 1626, 1607, 1614, 1522, 1523, 1630, 1660, 1527, 1554, 1560, 1615, 1531, 1634, 1535, 1462, 1466, 1635, 1636, 1637, 1472, 1638, 1585, 1640, 1641, 1642, 1643, 1493, 1644, 1499, 1645, 1646, 1457, 1650, 1649, 1506, 1652, 1654, 1510, 1571, 1550, 1584, 1536, 1565, 1568, 1655, 1656, 1657, 1658, 1659, 1661, 245: 3063, 291: 1302, 341: 1302, 1302, 3067, 348: 1725, 1459, 1460, 1458, 359: 1302, 372: 1302, 1302, 433: 3062, 477: 3065, 536: 3066, 3061, 3064, 662: 3068, 781: 3069},
+		{2: 1540, 1463, 1464, 1496, 7: 1474, 1545, 1489, 1542, 1508, 1514, 1543, 1541, 1544, 1555, 1547, 1548, 1551, 1583, 22: 1576, 1595, 1517, 1520, 1521, 1475, 1611, 1495, 1538, 1651, 1631, 1633, 1632, 1532, 1483, 1503, 1589, 1590, 1586, 1552, 1594, 1534, 1488, 1572, 1610, 1494, 1507, 1509, 1482, 1481, 1556, 1505, 1579, 1519, 1524, 1537, 1564, 1609, 1502, 1557, 1587, 1567, 1592, 1606, 1603, 1581, 1529, 1530, 1619, 1467, 1574, 1620, 1476, 1477, 1478, 1639, 1622, 1484, 1569, 1485, 1487, 1570, 1497, 1498, 1647, 1623, 1577, 1573, 1511, 1512, 1608, 1516, 1625, 1518, 1525, 1526, 1528, 1461, 1465, 1468, 1470, 1469, 1471, 1621, 1617, 1473, 1604, 1539, 1559, 1479, 1480, 1486, 1490, 1491, 1578, 1582, 1500, 1575, 1501, 1553, 1492, 1566, 1648, 1612, 1624, 1504, 1563, 1546, 1599, 1600, 1601, 1602, 1613, 1533, 1549, 1580, 1561, 1591, 1588, 1593, 1653, 1618, 1558, 1616, 1562, 1513, 1596, 1597, 1605, 1598, 1515, 1628, 1629, 1627, 1626, 1607, 1614, 1522, 1523, 1630, 1660, 1527, 1554, 1560, 1615, 1531, 1634, 1535, 1462, 1466, 1635, 1636, 1637, 1472, 1638, 1585, 1640, 1641, 1642, 1643, 1493, 1644, 1499, 1645, 1646, 1457, 1650, 1649, 1506, 1652, 1654, 1510, 1571, 1550, 1584, 1536, 1565, 1568, 1655, 1656, 1657, 1658, 1659, 1661, 245: 3063, 291: 1302, 341: 1302, 1302, 3067, 348: 1725, 1459, 1460, 1458, 359: 1302, 372: 1302, 1302, 433: 3062, 477: 3065, 536: 3061, 3066, 3064, 662: 3068, 781: 3069},
 		{2: 1540, 1463, 1464, 1496, 7: 1474, 1545, 1489, 1542, 1508, 1514, 1543, 1541, 1544, 1555, 1547, 1548, 1551, 1583, 22: 1576, 1595, 1517, 1520, 1521, 1475, 1611, 1495, 1538, 1651, 1631, 1633, 1632, 1532, 1483, 1503, 1589, 1590, 1586, 1552, 1594, 1534, 1488, 1572, 1610, 1494, 1507, 1509, 1482, 1481, 1556, 1505, 1579, 1519, 1524, 1537, 1564, 1609, 1502, 1557, 1587, 1567, 1592, 1606, 1603, 1581, 1529, 1530, 1619, 1467, 1574, 1620, 1476, 1477, 1478, 1639, 1622, 1484, 1569, 1485, 1487, 1570, 1497, 1498, 1647, 1623, 1577, 1573, 1511, 1512, 1608, 1516, 1625, 1518, 1525, 1526, 1528, 1461, 1465, 1468, 1470, 1469, 1471, 1621, 1617, 1473, 1604, 1539, 1559, 1479, 1480, 1486, 1490, 1491, 1578, 1582, 1500, 1575, 1501, 1553, 1492, 1566, 1648, 1612, 1624, 1504, 1563, 1546, 1599, 1600, 1601, 1602, 1613, 1533, 1549, 1580, 1561, 1591, 1588, 1593, 1653, 1618, 1558, 1616, 1562, 1513, 1596, 1597, 1605, 1598, 1515, 1628, 1629, 1627, 1626, 1607, 1614, 1522, 1523, 1630, 1660, 1527, 1554, 1560, 1615, 1531, 1634, 1535, 1462, 1466, 1635, 1636, 1637, 1472, 1638, 1585, 1640, 1641, 1642, 1643, 1493, 1644, 1499, 1645, 1646, 1457, 1650, 1649, 1506, 1652, 1654, 1510, 1571, 1550, 1584, 1536, 1565, 1568, 1655, 1656, 1657, 1658, 1659, 1661, 291: 1301, 341: 1301, 1301, 348: 3319, 1459, 1460, 1458, 359: 1301, 372: 1301, 1301, 661: 3318},
 		{35: 3208, 50: 3205, 3204, 56: 3207, 61: 3192, 96: 3206, 101: 3182, 3176, 3175, 115: 3190, 136: 3184, 159: 3200, 234: 3191, 248: 3186, 290: 154, 374: 3177, 3173, 3167, 378: 3193, 381: 3174, 3196, 384: 3181, 3179, 3168, 3169, 3170, 3171, 3172, 3203, 3198, 3202, 3197, 3166, 3201, 3178, 3194, 3180, 3165, 3195, 3164, 3199, 3187, 685: 3163, 3188, 3160, 703: 3158, 716: 3161, 3162, 728: 3159, 742: 3183, 745: 3156, 778: 3157, 788: 3189, 792: 3155, 797: 3185},
 		// 1710
@@ -5611,7 +5610,7 @@ var (
 		// 1715
 		{6: 237, 21: 237},
 		{6: 3070, 21: 3071},
-		{2: 1540, 1463, 1464, 1496, 7: 1474, 1545, 1489, 1542, 1508, 1514, 1543, 1541, 1544, 1555, 1547, 1548, 1551, 1583, 22: 1576, 1595, 1517, 1520, 1521, 1475, 1611, 1495, 1538, 1651, 1631, 1633, 1632, 1532, 1483, 1503, 1589, 1590, 1586, 1552, 1594, 1534, 1488, 1572, 1610, 1494, 1507, 1509, 1482, 1481, 1556, 1505, 1579, 1519, 1524, 1537, 1564, 1609, 1502, 1557, 1587, 1567, 1592, 1606, 1603, 1581, 1529, 1530, 1619, 1467, 1574, 1620, 1476, 1477, 1478, 1639, 1622, 1484, 1569, 1485, 1487, 1570, 1497, 1498, 1647, 1623, 1577, 1573, 1511, 1512, 1608, 1516, 1625, 1518, 1525, 1526, 1528, 1461, 1465, 1468, 1470, 1469, 1471, 1621, 1617, 1473, 1604, 1539, 1559, 1479, 1480, 1486, 1490, 1491, 1578, 1582, 1500, 1575, 1501, 1553, 1492, 1566, 1648, 1612, 1624, 1504, 1563, 1546, 1599, 1600, 1601, 1602, 1613, 1533, 1549, 1580, 1561, 1591, 1588, 1593, 1653, 1618, 1558, 1616, 1562, 1513, 1596, 1597, 1605, 1598, 1515, 1628, 1629, 1627, 1626, 1607, 1614, 1522, 1523, 1630, 1660, 1527, 1554, 1560, 1615, 1531, 1634, 1535, 1462, 1466, 1635, 1636, 1637, 1472, 1638, 1585, 1640, 1641, 1642, 1643, 1493, 1644, 1499, 1645, 1646, 1457, 1650, 1649, 1506, 1652, 1654, 1510, 1571, 1550, 1584, 1536, 1565, 1568, 1655, 1656, 1657, 1658, 1659, 1661, 291: 1302, 341: 1302, 1302, 3067, 348: 1725, 1459, 1460, 1458, 359: 1302, 372: 1302, 1302, 433: 3062, 477: 3065, 536: 3066, 3061, 3064, 662: 3072},
+		{2: 1540, 1463, 1464, 1496, 7: 1474, 1545, 1489, 1542, 1508, 1514, 1543, 1541, 1544, 1555, 1547, 1548, 1551, 1583, 22: 1576, 1595, 1517, 1520, 1521, 1475, 1611, 1495, 1538, 1651, 1631, 1633, 1632, 1532, 1483, 1503, 1589, 1590, 1586, 1552, 1594, 1534, 1488, 1572, 1610, 1494, 1507, 1509, 1482, 1481, 1556, 1505, 1579, 1519, 1524, 1537, 1564, 1609, 1502, 1557, 1587, 1567, 1592, 1606, 1603, 1581, 1529, 1530, 1619, 1467, 1574, 1620, 1476, 1477, 1478, 1639, 1622, 1484, 1569, 1485, 1487, 1570, 1497, 1498, 1647, 1623, 1577, 1573, 1511, 1512, 1608, 1516, 1625, 1518, 1525, 1526, 1528, 1461, 1465, 1468, 1470, 1469, 1471, 1621, 1617, 1473, 1604, 1539, 1559, 1479, 1480, 1486, 1490, 1491, 1578, 1582, 1500, 1575, 1501, 1553, 1492, 1566, 1648, 1612, 1624, 1504, 1563, 1546, 1599, 1600, 1601, 1602, 1613, 1533, 1549, 1580, 1561, 1591, 1588, 1593, 1653, 1618, 1558, 1616, 1562, 1513, 1596, 1597, 1605, 1598, 1515, 1628, 1629, 1627, 1626, 1607, 1614, 1522, 1523, 1630, 1660, 1527, 1554, 1560, 1615, 1531, 1634, 1535, 1462, 1466, 1635, 1636, 1637, 1472, 1638, 1585, 1640, 1641, 1642, 1643, 1493, 1644, 1499, 1645, 1646, 1457, 1650, 1649, 1506, 1652, 1654, 1510, 1571, 1550, 1584, 1536, 1565, 1568, 1655, 1656, 1657, 1658, 1659, 1661, 291: 1302, 341: 1302, 1302, 3067, 348: 1725, 1459, 1460, 1458, 359: 1302, 372: 1302, 1302, 433: 3062, 477: 3065, 536: 3061, 3066, 3064, 662: 3072},
 		{234, 234, 234, 234, 7: 234, 234, 234, 234, 234, 13: 234, 234, 234, 234, 234, 234, 234, 234, 206: 234, 210: 234, 213: 234, 236: 234, 239: 234, 347: 234, 352: 234, 234, 234, 234, 234},
 		{6: 236, 21: 236},
 		// 1720
@@ -6195,7 +6194,7 @@ var (
 		{354: 3632},
 		{1348, 1348, 6: 1348},
 		{368: 3628},
-		{2: 1311, 1311, 1311, 1311, 7: 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 22: 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 291: 1302, 341: 1302, 1302, 354: 3618, 359: 1302, 372: 1302, 1302, 504: 3585, 3616, 536: 3617, 3061, 3064},
+		{2: 1311, 1311, 1311, 1311, 7: 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 22: 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 291: 1302, 341: 1302, 1302, 354: 3618, 359: 1302, 372: 1302, 1302, 504: 3585, 3616, 536: 3061, 3617, 3064},
 		// 2205
 		{2: 1311, 1311, 1311, 1311, 7: 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 22: 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 1311, 291: 2499, 341: 3605, 354: 3606, 359: 2500, 372: 3608, 475: 3607, 504: 3585, 3604},
 		{548: 3603},

so we can't simply compare parser.go with the output make parser until this is fixed.

Why there's no return in SelectStmt.Restore

Question

func (n *SelectStmt) Restore(ctx *RestoreCtx) error {
        ......
	if n.TableHints != nil && len(n.TableHints) != 0 {
		ctx.WritePlain("/*+ ")
		for i, tableHint := range n.TableHints {
			if err := tableHint.Restore(ctx); err != nil {
				errors.Annotatef(err, "An error occurred while restore SelectStmt.TableHints[%d]", i)
			}
		}
		ctx.WritePlain("*/ ")
	}
        ......
}

Why not return errors.Annotatef()? If error occurs, the error will disappear and cannot be found in return value.

Support SQL_BIG_RESULT, SQL_SMALL_RESULT, SQL_BUFFER_RESULT

Via pingcap/tidb#4735

These keywords are safe to parse but ignore in TiDB for compatibility.

Support CTEs (WITH statement)

Feature Request

any plan to support with clause from SQL:1999 and supported by MySQL 8.0

https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-syntax

Support more table options in the CREATE TABLE statement

TiDB doesn't support all the syntax in CREATE TABLE statement, here is a related issue pingcap/tidb#7904.

If we improve the syntax support of CREATE TABLE statement in TiDB, it will be helpful for TiDB to compatiable with MySQL.
I list the MySQL syntax of CREATE TABLE statement and add a [TODO] flag at the end of the part which TiDB hasn't supported yet.
It is a huge work, but it is still easy to finish parts of it.

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [IGNORE | REPLACE]
    [AS] query_expression

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

create_definition:
    col_name column_definition
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (key_part,...)
      [index_option] ...
  | {INDEX|KEY} [index_name] [index_type] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
      [index_name] [index_type] (key_part,...)
      [index_option] ...
  | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (key_part,...) [TODO: SPATIAL not supported]
      [index_option] ...
  | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (col_name,...) reference_definition
  | CHECK (expr)

column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}] [TODO]
      [STORAGE {DISK|MEMORY|DEFAULT}] [TODO]
      [reference_definition]
  | data_type [GENERATED ALWAYS] AS (expression)
      [VIRTUAL | STORED] [NOT NULL | NULL]
      [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']

data_type:
    (see Chapter 11, Data Types)

key_part:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name [TODO]
  | COMMENT 'string'

reference_definition:
    REFERENCES tbl_name (key_part,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE] [TODO]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]

reference_option: [TODO]
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

table_options:
    table_option [[,] table_option] ...

table_option:
    AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | COMPRESSION [=] {'ZLIB'|'LZ4'|'NONE'}
  | CONNECTION [=] 'connect_string'
  | {DATA|INDEX} DIRECTORY [=] 'absolute path to directory' [TODO]
  | DELAY_KEY_WRITE [=] {0 | 1}
  | ENCRYPTION [=] {'Y' | 'N'} [TODO]
  | ENGINE [=] engine_name
  | INSERT_METHOD [=] { NO | FIRST | LAST } [TODO]
  | KEY_BLOCK_SIZE [=] value
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
  | STATS_AUTO_RECALC [=] {DEFAULT|0|1} [TODO]
  | STATS_PERSISTENT [=] {DEFAULT|0|1}
  | STATS_SAMPLE_PAGES [=] value [TODO]
  | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}] [TODO]
  | UNION [=] (tbl_name[,tbl_name]...) [TODO]

partition_options:
    PARTITION BY
        { [LINEAR] HASH(expr) [TODO: LINEAR not supported]
        | [LINEAR] KEY [ALGORITHM={1|2}] (column_list) [TODO: LINEAR and ALGORITHM not supported]
        | RANGE{(expr) | COLUMNS(column_list)}
        | LIST{(expr) | COLUMNS(column_list)} } [TODO]
    [PARTITIONS num]
    [SUBPARTITION BY [TODO: not supported except RANGE partition]
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1|2}] (column_list) }
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]

partition_definition:
    PARTITION partition_name
        [VALUES
            {LESS THAN {(expr | value_list) | MAXVALUE}
            |
            IN (value_list)}] [TODO]
        [[STORAGE] ENGINE [=] engine_name] [TODO: STORAGE not supported]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] 'data_dir'] [TODO]
        [INDEX DIRECTORY [=] 'index_dir'] [TODO]
        [MAX_ROWS [=] max_number_of_rows] [TODO]
        [MIN_ROWS [=] min_number_of_rows] [TODO]
        [TABLESPACE [=] tablespace_name]
        [(subpartition_definition [, subpartition_definition] ...)]

subpartition_definition:
    SUBPARTITION logical_name
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]

query_expression:
    SELECT ...   (Some valid select or union statement)

• SPATIAL supports in create_definition.
• COLUMN_FORMAT supports in column_definition.
• STORAGE supports in column_definition.
• WITH PARSER supports in index_option.
• MATCH supports in reference_definition.
• reference_option supports.
• DIRECTORY supports in table_option.
• INSERT_METHOD supports in table_option.
• STATS_AUTO_RECALC supports in table_option.
• STATS_SAMPLE_PAGES supports in table_option.
• TABLESPACE supports in table_option.
• UNION supports in table_option.
• LINEAR and ALGORITHM supports in partition_options.
• LIST supports in partition_options.
• SUBPARTITION supports in partition_options.
• IN supports in partition_definition.
• [STORAGE] supports in partition_definition.
• DATA DIRECTORY supports in partition_definition.
• INDEX DIRECTORY supports in partition_definition.
• MAX_ROWS supports in partition_definition.
• MIN_ROWS supports in partition_definition.

Support SHOW CREATE USER sql command

Support parser functionality for pingcap/tidb#7733

Semantic version required

We've make the parser a standalone repository, moving the code out from TiDB. One of the main purpose is to make the parser more general one.

The parser should be general enough to work as a library, works in both TiDB 2.1 and TiDB and many other projects (maybe different module version).

To reach that goal, I think it's time to tag release version for the parser, and employ the sematic version.

Make parser error/warn message useful

Now parser's error message is useless,
e.g. execute those script:

select1 1;

pingcap/parser will got:

ERROR 1105 (HY000): line 1 column 7 near " 1" (total length 9)

for MySQL will be:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select1 1' at line 1

DROP TEMPORARY TABLE support

Feature Request

Is your feature request related to a problem? Please describe:
attempt to parse DDL statement like
DROP TEMPORARY TABLE IF EXISTS test``
yields a parse error

Describe the feature you'd like:
Ability to correctly parse both flavors:
DROP /*!40005 TEMPORARY */ TABLE IF EXISTS test `DROP TEMPORARY TABLE IF EXISTS `test
and have an indicator in AST model on whether TEMPORARY keyword was used

Support OPTIONALLY ENCLOSED BY for LOAD DATA INFILE

It looks like the keyword OPTIONALLY is optional, because LOAD DATA seems to understand how to parse csv data that does not have enclosed quotes (this test case works the same in MySQL and TIDB):

echo "a,b,c" > /tmp/tmp.csv
echo '"1",2,"3"' >> /tmp/tmp.csv

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
 id INT NOT NULL PRIMARY KEY,
 b INT,
 c varchar(10)
);
LOAD DATA LOCAL INFILE '/tmp/tmp.csv' INTO TABLE test.t1 FIELDS TERMINATED BY ',' ENCLOSED BY '\"' IGNORE 1 LINES;

mysql> SELECT * FROM t1;
+----+------+------+
| id | b    | c    |
+----+------+------+
|  1 |    2 | 3    |
+----+------+------+
1 row in set (0.00 sec)

However, the following does not work in TiDB:

LOAD DATA LOCAL INFILE '/tmp/tmp.csv' INTO TABLE test.t1 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' IGNORE 1 LINES;

Because of the above test-case, it looks like OPTIONALLY can be parsed but ignored unless someone discovers a test-case to the contrary.

invalid memory address or nil pointer dereference for some test case

test.go

package main

import (
        "fmt"

        "github.com/pingcap/parser"
)

func main() {
        sql := "select * from tb where a=1"
        fmt.Println(parser.New().ParseOneStmt(sql, "", ""))
}

go run test.go
panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0x0 pc=0x5d8990]

BUG: parsing UseStmt.DBName is not in line with expectations

In TiDB Parser, version: master in repo

stmt, _ := parser.ParseOneStmt("use abc`def", "", "")
fmt.Printf("%#v",stmt)

&ast.UseStmt{stmtNode:ast.stmtNode{node:ast.node{text:"use abc`def"}}, DBName:"abc"}

In MariaDB(MySQL Untested), version: 10.3.10-MariaDB

MariaDB [(none)]> use na`me
Database changed
MariaDB [na`me]> 

Maybe we don't support this way, but we should't get a part of name.

Support FLUSH LOGS, FLUSH HOSTS sql commands

Both are safe to be a no-op in TiDB.

MySQL compatibility: `show create database if not exists` syntax

See: https://dev.mysql.com/doc/refman/5.7/en/show-create-database.html

MySQL

MariaDB [(none)]> show create database b2;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| b2       | CREATE DATABASE `b2` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.002 sec)

MariaDB [(none)]> show create database if not exists b2;
+----------+-----------------------------------------------------------------------------------------+
| Database | Create Database                                                                         |
+----------+-----------------------------------------------------------------------------------------+
| b2       | CREATE DATABASE /*!32312 IF NOT EXISTS*/ `b2` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+-----------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

TiDB

MySQL [b1]> show create batabase b2;
ERROR 1105 (HY000): line 1 column 20 near " b2" (total length 23)
MySQL [b1]> show create database b2;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| b2       | CREATE DATABASE `b2` /* !40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.000 sec)

MySQL [b1]> show create database if not exists b2;
ERROR 1105 (HY000): line 1 column 23 near " not exists b2" (total length 37)

add .editorconfig back from tidb

https://github.com/pingcap/tidb/blob/master/.editorconfig