[BUG] JSQLParser 4.7 : MySQL 8 : Cannot parse functional indices in table creation DDL

[ls-denis-iakovlev]

Failing SQL Feature:

Parsing CREATE TABLE DDL query failed when there is a KEY parameter that uses functions like LOWER.

The parsing fails with this error:

net.sf.jsqlparser.JSQLParserException: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "(" "("
    at line 6, column 19.

Was expecting one of:

    "ACTION"
    "ACTIVE"
    "ADD"
    "ADVANCE"
    "ADVISE"
    "AGAINST"
    "ALGORITHM"
    "ALTER"
    "ANALYZE"
    "APPLY"
    "ARCHIVE"
    "ARRAY"
    "ASC"
    "AT"
    "AUTHORIZATION"
    "AUTO"
    "BEGIN"
    "BERNOULLI"
    "BINARY"
    "BIT"
    "BLOCK"
    "BROWSE"
    "BUFFERS"
    "BY"
    "BYTE"
    "BYTES"
    "CACHE"
    "CALL"
    "CASCADE"
    "CASE"
    "CAST"
    "CHANGE"
    "CHANGES"
    "CHAR"
    "CHARACTER"
    "CHECKPOINT"
    "CLOSE"
    "COLLATE"
    "COLUMN"
    "COLUMNS"
    "COMMENT"
    "COMMIT"
    "CONFLICT"
    "CONNECT"
    "CONVERT"
    "COSTS"
    "CREATE"
    "CS"
    "CYCLE"
    "DATABASE"
    "DDL"
    "DECLARE"
    "DEFAULT"
    "DEFERRABLE"
    "DELAYED"
    "DELETE"
    "DESC"
    "DESCRIBE"
    "DISABLE"
    "DISCONNECT"
    "DIV"
    "DML"
    "DO"
    "DOMAIN"
    "DROP"
    "DUMP"
    "DUPLICATE"
    "ELEMENTS"
    "EMIT"
    "ENABLE"
    "END"
    "ESCAPE"
    "EXCLUDE"
    "EXEC"
    "EXECUTE"
    "EXPLAIN"
    "EXPLICIT"
    "EXTENDED"
    "EXTRACT"
    "FALSE"
    "FILTER"
    "FIRST"
    "FLUSH"
    "FN"
    "FOLLOWING"
    "FORMAT"
    "FULLTEXT"
    "FUNCTION"
    "GLOBAL"
    "GRANT"
    "GROUP"
    "GUARD"
    "HISTORY"
    "HOPPING"
    "IGNORE"
    "INCLUDE"
    "INCREMENT"
    "INDEX"
    "INSERT"
    "INTERLEAVE"
    "INTERPRET"
    "INTERVAL"
    "ISNULL"
    "JSON"
    "KEEP"
    "KEY"
    "KEYS"
    "LAST"
    "LEADING"
    "LINK"
    "LOCAL"
    "LOCKED"
    "LOG"
    "MATCH"
    "MATCHED"
    "MATERIALIZED"
    "MAXVALUE"
    "MEMBER"
    "MERGE"
    "MINVALUE"
    "MODIFY"
    "MOVEMENT"
    "NEXT"
    "NO"
    "NOCACHE"
    "NOKEEP"
    "NOLOCK"
    "NOMAXVALUE"
    "NOMINVALUE"
    "NOORDER"
    "NOTHING"
    "NOTNULL"
    "NOVALIDATE"
    "NOWAIT"
    "NULLS"
    "OF"
    "OFF"
    "ON"
    "OPEN"
    "OVER"
    "OVERLAPS"
    "PARALLEL"
    "PARENT"
    "PARTITION"
    "PATH"
    "PERCENT"
    "PLACING"
    "PRECEDING"
    "PRECISION"
    "PRIMARY"
    "PRIOR"
    "PURGE"
    "QUALIFY"
    "QUERY"
    "QUICK"
    "QUIESCE"
    "RANGE"
    "RAW"
    "READ"
    "RECYCLEBIN"
    "REFERENCES"
    "REFRESH"
    "REGISTER"
    "RENAME"
    "REPEATABLE"
    "REPLACE"
    "RESET"
    "RESTART"
    "RESTRICT"
    "RESTRICTED"
    "RESUMABLE"
    "RESUME"
    "RLIKE"
    "ROLLBACK"
    "ROOT"
    "ROW"
    "ROWS"
    "RR"
    "RS"
    "SAVEPOINT"
    "SCHEMA"
    "SEED"
    "SEPARATOR"
    "SEQUENCE"
    "SESSION"
    "SETS"
    "SHOW"
    "SHUTDOWN"
    "SIBLINGS"
    "SIGNED"
    "SIMILAR"
    "SIZE"
    "SKIP"
    "START"
    "STORED"
    "STRING"
    "SUSPEND"
    "SWITCH"
    "SYNONYM"
    "SYSTEM"
    "TABLE"
    "TABLES"
    "TABLESPACE"
    "TEMP"
    "TEMPORARY"
    "THEN"
    "TIMEOUT"
    "TIMESTAMPTZ"
    "TO"
    "TOP"
    "TRIGGER"
    "TRUE"
    "TRUNCATE"
    "TUMBLING"
    "TYPE"
    "UNLOGGED"
    "UNQIESCE"
    "UNSIGNED"
    "UPDATE"
    "UPSERT"
    "UR"
    "USER"
    "VALIDATE"
    "VALUE"
    "VALUES"
    "VERBOSE"
    "VIEW"
    "WAIT"
    "WITHIN"
    "WITHOUT"
    "WORK"
    "XML"
    "XMLAGG"
    "XMLDATA"
    "XMLSCHEMA"
    "XMLTEXT"
    "XSINIL"
    "YAML"
    "YES"
    "ZONE"
    <K_DATETIMELITERAL>
    <K_DATE_LITERAL>
    <K_ISOLATION>
    <K_STRING_FUNCTION_NAME>
    <K_TIME_KEY_EXPR>
    <S_IDENTIFIER>
    <S_QUOTED_IDENTIFIER>

SQL Example:

CREATE TABLE TEST_TABLE
(
    ID   bigint NOT NULL,
    NAME VARCHAR(255),
    PRIMARY KEY (`ID`),
    KEY MY_INDEX ((LOWER(NAME)))
) ENGINE = InnoDB;

Software Information:

• JSqlParser version: 4.7
• Database: MySQL 8

Tips:

Please write in English and avoid Screenshots (as we can't copy and paste content from it).
MySQL 8 added a feature for functional indices: https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-functional-key-parts

[manticore-projects]

Greetings,

your example works fine without the extra-brackets:

CREATE TABLE test_table (
    id           BIGINT          NOT NULL
    , name       VARCHAR (255)
    , PRIMARY KEY ( `id` )
    , KEY my_index (LOWER (NAME))
) ENGINE = INNODB
;

You can test it online here.

[ls-denis-iakovlev]

Good day @manticore-projects, thanks for quick response!
I'm sorry, this is my bad, I tried to simplify an example but probably lost that in the original issue I had an index on 2 columns:

CREATE TABLE test_table (
    id           BIGINT          NOT NULL
    , name       VARCHAR (255)
    , description VARCHAR (255)
    , PRIMARY KEY ( `id` )
    , KEY my_index (description, (LOWER (NAME)))
) ENGINE = INNODB
;

Here is a link

This query successfully executes on MySQL 8

[manticore-projects]

Not supported yet in the Grammar and its not trivial to amend.
The current implementation expects strictly column names.