[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)

[minleejae]

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.

[manticore-projects]

Apologies for being blunt: I do not see much (personal) value in these specific syntax or parsing DDLs. It will always be a cat-mouse game and every RDBMS vendor seems to be eager to implement crazy stuff to lock-in customers.

But of course I won't oppose a PR when you get it working without blowing up anything else.

[minleejae]

Hi @manticore-projects, thanks for your feedback. I completely agree that DBMS vendors creating custom syntax instead of following ANSI SQL is a major issue.

However, my use case specifically requires parsing exact DDLs for various DBMSs (like MySQL and PostgreSQL). For this reason, I rely on JSqlParser—when I evaluated several libraries two years ago, it was the best option for DDL parsing.

Given JSqlParser's slogan ("One grammar covers all of them, and missing syntax gets added on demand"), I believe it should aim to be a superset of vendor-specific SQL dialects, including detailed DDLs. In fact, this is precisely why I recently proposed supporting explicit, DBMS-specific parsing.

What are your thoughts on this?

[manticore-projects]

Your PRs will be welcome and I happily will merge those. I just won't spend any resource on it myself since DDLs are out of my scope.

[manticore-projects]

Coincidentally #2491 just came, you may want to continue from there.