Improve parser compatibility

[zier-one]

As MySQL evolves constantly, there are always new SQL syntaxes that TiDB doesn’t support. To support a new SQL function in TiDB is a careful decision, and may take a long time. For better compatibility and user experience, it’s our intention that TiDB can parse and ignore the unsupported functions while only executing the supported part of SQL. Therefore, we need to parse as many SQL syntaxes as possible in the TiDB Parser to make it behave more closely to the parser in MySQL.

To find out the differences between MySQL and TiDB parser, we generate SQL cases randomly based on the yacc file of MySQL. These SQL cases are later executed respectively in TiDB parser and MySQL server to screen out the incompatible ones that cause different behaviors between TiDB parser and MySQL.

The incompatible test cases will be grouped by behaviors - those with similar behavior form a subtask, with an issue created correspondingly. As contributors, you can learn about the subtask based on the comments below the issue and claim accordingly. After finishing one or more subtasks, submit them individually or collectively as pull requests (PRs).

See: 中文教程

Sub tasks list:

• Remove CONSTRAINT for KEY | INDEX | FULLTEXT Remove CONSTRAINT syntax for KEY | INDEX | FULLTEXT parser#535 @wty4427300
• SET=DEFAULT Fix SET=DEFAULT syntax parser#549 @hey-kong parser: fix SET=DEFAULT syntax parser#551
• SQL_TSI_YEAR Fix SQL_TSI_YEAR column type syntax parser#537 @hey-kong parser: fix SQL_TSI_YEAR column type syntax  parser#540
• FIXED Fix FIXED syntax parser#555 @hey-kong parser: fix FIXED syntax parser#556
• *TEXT BYTE | ASCII | UNICODE Fix *TEXT BYTE | ASCII | UNICODE syntax parser#529 @hey-kong parser: fix *TEXT BYTE | ASCII | UNICODE syntax parser#536
• CREATE FULLTEXT...WITH PARSER Fix CREATE FULLTEXT...WITH PARSER syntax parser#507 @yunnian Parser: support with parser ident parser#524
• CHARSET DEFAULT Fix CHARSET DEFAULT syntax parser#498 @sim41 fix alter table convert to charset default syntax parser#566
• STORAGE ENGINE Fix STORAGE ENGINE syntax parser#500 @yangwenmai Parser: support storage engine and VALUE IN MAXVALUE parser#542
• CREATE INDEX .. TYPE Fix CREATE INDEX .. TYPE syntax parser#479 @yufeiminds parser: fix CREATE INDEX .. TYPE syntax parser#533
• ORDER BY Fix ORDER BY syntax parser#499 @zuoRambo parser: fix alter_stmt order by syntax parser#526
• DISCARD/IMPORT TABLESPACE Fix DISCARD/IMPORT TABLESPACE syntax parser#501 @fzhedu parser: Fix DISCARD/IMPORT TABLESPACE syntax parser#512
• CONSTRAINT Fix CONSTRAINT syntax parser#413 @ichn-hu fix CONSTRAINT syntax parser#548
• SQL_TSI_* Fix SQL_TSI_* syntax parser#480 @ichn-hu parser: support SQL_TSI_* parser#495
• COLUMN_FORMAT Fix COLUMN_FORMAT syntax parser#476 @doggeral Support COLUMN_FORMAT syntax. parser#482
• LOCK clause error message Fix LOCK clause error message parser#508 @lauhg parser: fix LOCK clause error message parser#516
• VARCHARACTER Fix VARCHARACTER syntax parser#509 @lauhg parser: fix VARCHARACTER, VARYING syntax parser#515
• VARYING Fix VARYING syntax parser#503 @lauhg parser: fix VARCHARACTER, VARYING syntax parser#515
• CHARACTER VARYING Fix CHARACTER VARY syntax parser#410 @lauhg parser: fix CHARACTER VARYING syntax parser#465
• STATS_AUTO_RECALC Fix STATS_AUTO_RECALC syntax parser#472 @lauhg parser: fix STATS_AUTO_RECALC syntax parser#483
• LONG Fix LONG syntax parser#470 @lauhg parser: fix LONG syntax parser#473
• ALTER TABLE ... ALTER INDEX Fix ALTER TABLE ... ALTER INDEX syntax parser#463 @tangwz add support for ALTER TABLE ... ALTER INDEX syntax parser#519
• ALTER TABLE ... RENAME COLUMN Fix ALTER TABLE ... RENAME COLUMN syntax parser#462 @tangwz add support for ALTER TABLE ... RENAME COLUMN syntax parser#504
• ALTER TABLE ... RENAME Fix ALTER TABLE ... RENAME syntax parser#461 @tangwz add support for ALTER TABLE ... RENAME syntax parser#489
• MATCH Fix MATCH syntax  parser#426 @qiukun attempt to fix MATCH syntax parser#466
• REPAIR PARTITION Fix REPAIR PARTITION syntax parser#439 @huaouo add support for [TRUNCATE|DISCARD|IMPORT|OPTIMIZE|REPAIR] PARTITION parser#453
• OPTIMIZE PARTITION Fix OPTIMIZE PARTITION syntax parser#437 @huaouo add support for [TRUNCATE|DISCARD|IMPORT|OPTIMIZE|REPAIR] PARTITION parser#453
• IMPORT PARTITION/TABLESPACE Fix IMPORT PARTITION/TABLESPACE syntax parser#436 @huaouo add support for [TRUNCATE|DISCARD|IMPORT|OPTIMIZE|REPAIR] PARTITION parser#453
• DISCARD PARTITION/TABLESPACE Fix DISCARD PARTITION/TABLESPACE syntax parser#434 @huaouo add support for [TRUNCATE|DISCARD|IMPORT|OPTIMIZE|REPAIR] PARTITION parser#453
• TRUNCATE PARTITION ALL Fix TRUNCATE PARTITION ALL syntax parser#432 @huaouo add support for [TRUNCATE|DISCARD|IMPORT|OPTIMIZE|REPAIR] PARTITION parser#453
• INSERT_METHOD Fix INSERT_METHOD syntax parser#415 @TennyZhuang add support for INSERT_METHOD parser#452
• DROP INDEX ... ALGORITHM Fix DROP INDEX ... ALGORITHM syntax parser#460 @zhenghaoz Support DROP INDEX ... [ALGORITHM | LOCK] syntax parser#484
• CREATE INDEX .. VISIBLE/INVISIBLE Fix CREATE INDEX .. VISIBLE/INVISIBLE syntax parser#478 @zhenghaoz Fix CREATE INDEX .. VISIBLE/INVISIBLE syntax parser#518
• ALGORITHM Fix ALGORITHM syntax parser#429 @zhenghaoz add support for CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX parser#454
• CREATE SPATIAL INDEX Fix CREATE SPATIAL INDEX syntax parser#431 @zhenghaoz add support for CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX parser#454
• DROP INDEX ... LOCK Fix DROP INDEX ... LOCK syntax parser#459 @zhenghaoz Support DROP INDEX ... [ALGORITHM | LOCK] syntax parser#484
• CREATE FULLTEXT INDEX Fix CREATE FULLTEXT INDEX syntax parser#430 @zhenghaoz add support for CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX parser#454
• CREATE DATABASE … ENCRYPTION Fix CREATE DATABASE … ENCRYPTION syntax parser#417 @zhenghaoz Fix CREATE DATABASE … ENCRYPTION syntax parser#450
• UNION Fix UNION syntax parser#502 @spongedu parser: support table option 'UNION' parser#523
• ENCRYPTION Fix ENCRYPTION syntax parser#475 @spongedu parser: support table option: 'ENCRYPTION' parser#520
• FLOAT4/FLOAT8 Fix FLOAT4/FLOAT8 syntax parser#474 @spongedu parser: support float4 and float8 parser#490
• SECONDARY_LOAD/SECONDARY_UNLOAD Fix SECONDARY_LOAD/SECONDARY_UNLOAD syntax parser#444 @spongedu parser: support secondary_load and secondary_unload parser#496
• CHECK Fix CHECK syntax parser#497 @spongedu parser: support ALTER TABLE ALTER CHECK, ALTER TABLE DROP CHECK syntax parser#505
• REBUILD PARTITION Fix REBUILD PARTITION syntax parser#448 @spongedu Support ALTER TABLE REBUILD PARTITION syntax parser#469
• FIELDS Fix FIELDS syntax parser#464 @spongedu parser: Support keyword FIELDS in ALTER TABLE PARTITION BY syntax parser#471
• REORGANIZE PARTITION Fix REORGANIZE PARTITION syntax parser#419 @spongedu parser: support REORGANIZE PARTITION syntax parser#494
• SERIAL Fix SERIAL syntax parser#467 @spongedu Support keyword SERIAL parser#481
• CHECK PARTITION Fix CHECK PARTITION syntax parser#435 @spongedu parser: Fix CHECK PARTITION syntax parser#457
• NO_WRITE_TO_BINLOG Fix NO_WRITE_TO_BINLOG syntax parser#445 @spongedu parser: Fix NO_WRITE_TO_BINLOG option for ALTER TABLE Partition Operations syntax parser#456
• TABLE_CHECKSUM Fix TABLE_CHECKSUM syntax parser#468 @leiysky fix: add support for TABLE_CHECKSUM parser#488
• EXCHANGE PARTITION Fix EXCHANGE PARTITION syntax parser#412 @leiysky add support for EXCHANGE PARTITION parser#449
• PASSWORD EXPIRE Fix PASSWORD EXPIRE syntax parser#409 @leiysky add support for PASSWORD EXPIRE parser#442
• TEMPORARY TABLE Fix TEMPORARY syntax parser#408 @leiysky add support for CREATE TEMPORARY TABLE parser#438
• RTREE Fix RTREE syntax parser#406 @leiysky add support for RTREE index parser#433
• SECONDARY_ENGINE Fix SECONDARY_ENGINE syntax parser#405 @leiysky add support for SECONDARY_ENGINE parser#418
• STATS_SAMPLE_PAGES Fix STATS_SAMPLE_PAGES syntax parser#404 @leiysky add support for STATS_SAMPLE_PAGES parser#414
• STORAGE Fix STORAGE syntax parser#403 @leiysky add support for STORAGE table option parser#407
• WITH/WITHOUT VALIDATION Fix WITH/WITHOUT VALIDATION syntax parser#420 @imtsuki parser: fix WITH/WITHOUT VALIDATION syntax (#420) parser#427
• REMOVE PARTITIONING Fix remove partitioning syntax parser#402 @tangenta parser: fix remove partitioning syntax parser#396