Open
Description
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