[BUG] 5.3 / master : MySQL : valid index DDL fails to parse (prefix+direction key parts, CREATE/DROP INDEX options, CREATE FULLTEXT/SPATIAL, multi-valued indexes) #2490

Failing SQL Feature:

Several valid MySQL index DDL statements fail to parse. Every statement listed below was verified to execute successfully on MySQL 8.4.11 (official Docker image), and then parsed with CCJSqlParserUtil.parse() against both release 5.3 and master @ 4c6a4fb (2026-08-19). The failures group into 6 root causes.

For contrast, closely related forms that already parse fine on master are listed per group, which should help localize each gap.

1. Key part with both prefix length and direction: col(len) [ASC|DESC]

CREATE INDEX i03 ON t (c1(20) DESC);
CREATE INDEX i04 ON t (c1(20) ASC, c2(10) DESC);
CREATE UNIQUE INDEX i25 ON t (c1(10) DESC);
ALTER TABLE t ADD INDEX i05 (c1(20) DESC);
ALTER TABLE t ADD INDEX i33 (c1(20) ASC);

Error: Encountered unexpected token: "DESC" "DESC" (resp. "ASC").

Works on master: c1(20) (prefix only), c1 DESC (direction only, since #2387), (LOWER(c1)) DESC (expression key part + direction), and ADD UNIQUE INDEX ux (c1(10) DESC) (unique-key path). Only the combination of prefix length + direction on a named column fails.

2. CREATE INDEX with KEY_BLOCK_SIZE

CREATE INDEX i08 ON t (c1) KEY_BLOCK_SIZE=8;
CREATE INDEX i09 ON t (c1) KEY_BLOCK_SIZE 8;
CREATE INDEX i14 ON t (c1) USING BTREE KEY_BLOCK_SIZE=8 COMMENT 'combo' INVISIBLE;

Error: Encountered unexpected token: "KEY_BLOCK_SIZE".

Works on master: ALTER TABLE t ADD INDEX i (c1) KEY_BLOCK_SIZE = 8 — the ALTER path already supports it, only the CREATE INDEX statement rejects it.

3. CREATE INDEX with algorithm_option / lock_option

CREATE INDEX i10 ON t (c1) ALGORITHM=INPLACE LOCK=NONE;
CREATE INDEX i11 ON t (c1) ALGORITHM INPLACE LOCK NONE;
CREATE INDEX i12 ON t (c1) ALGORITHM=INPLACE;
CREATE INDEX i13 ON t (c1) LOCK=NONE;

Error: Encountered unexpected token: "ALGORITHM" (resp. "LOCK").

Works on master: ALTER TABLE t ADD INDEX i (c1), ALGORITHM=INPLACE, LOCK=NONE (ALTER path).

4. DROP INDEX with algorithm_option / lock_option

DROP INDEX i15 ON t ALGORITHM=INPLACE LOCK=NONE;
DROP INDEX i16 ON t ALGORITHM INPLACE;

Error: Encountered unexpected token: "ALGORITHM".

5. CREATE FULLTEXT INDEX / CREATE SPATIAL INDEX → UnsupportedStatement

CREATE FULLTEXT INDEX i17 ON t (body);
CREATE FULLTEXT INDEX i18 ON t (body) WITH PARSER ngram;
CREATE SPATIAL INDEX i19 ON t (g);

These do not throw, but fall back to UnsupportedStatement instead of producing a CreateIndex.

Works on master: ALTER TABLE t ADD FULLTEXT INDEX fx (body) WITH PARSER ngram and ALTER TABLE t ADD SPATIAL INDEX sx (g) parse fine (related: #2367 covered SPATIAL KEY inside CREATE TABLE).

6. Multi-valued (functional) index: CAST(... AS ... ARRAY)

CREATE INDEX i20 ON t ((CAST(data->'$.zips' AS UNSIGNED ARRAY)));
ALTER TABLE t ADD INDEX i31 ((CAST(data->'$.zips' AS UNSIGNED ARRAY)));

Error: parse exception at the ARRAY keyword inside the CAST target type.

Works on master: plain functional key parts, e.g. CREATE INDEX i ON t ((LOWER(c1))).

SQL Example:

Minimal repro:

// throws JSQLParserException on master @ 4c6a4fb
CCJSqlParserUtil.parse("CREATE INDEX i03 ON t (c1(20) DESC)");

MySQL-side validation used this table (all statements above succeed against it on MySQL 8.4.11):

CREATE TABLE t (
  id INT PRIMARY KEY,
  c1 VARCHAR(100),
  c2 VARCHAR(100),
  body TEXT,
  data JSON,
  g GEOMETRY NOT NULL SRID 4326
) ENGINE=InnoDB;

Software Information:

• JSqlParser: release 5.3 and master @ 4c6a4fb (2026-08-19)
• Database: MySQL 8.4.11 (official Docker image; every listed statement executed successfully — ENGINE_ATTRIBUTE was deliberately excluded because InnoDB rejects it at execution time)

Grammar references:

• CREATE INDEX: https://dev.mysql.com/doc/refman/8.4/en/create-index.html — key_part: {col_name [(length)] | (expr)} [ASC | DESC], index_option: {KEY_BLOCK_SIZE [=] value | index_type | WITH PARSER parser_name | COMMENT 'string' | {VISIBLE | INVISIBLE} | ...}, plus trailing algorithm_option / lock_option
• DROP INDEX: https://dev.mysql.com/doc/refman/8.4/en/drop-index.html — trailing algorithm_option / lock_option
• Multi-valued indexes: https://dev.mysql.com/doc/refman/8.4/en/create-index.html#create-index-multi-valued

Notes on likely causes (from reading JSqlParserCC.jjt on master):

• Group 1: IndexColumnWithParams() allows at most one optional CreateParameter() per key part, so (20) consumes it and the following DESC cannot be matched. (IndexColumnsWithParamsList() is shared by the CREATE and ALTER paths, so one fix covers both.)
• Groups 2–3: the CreateIndex() tail loop only repeats CreateParameter(), whose token set lacks K_KEY_BLOCK_SIZE / K_ALGORITHM / K_LOCK — while IndexWithComment() (used by the ALTER path) already has an explicit KEY_BLOCK_SIZE ["="] <S_LONG> branch that could be mirrored.
• Group 4: the Drop() trailing-args loop accepts only (S_IDENTIFIER | CASCADE | RESTRICT | ON Table)*.
• Group 5: CreateIndex()'s leading [CreateParameter()] can consume UNIQUE (making CREATE UNIQUE INDEX work) but not FULLTEXT / SPATIAL, so the CREATE dispatch falls through to the UnsupportedStatement fallback.

I'd be happy to follow up with a PR for some of these if the approach sounds agreeable.