bug(mysql): structure diff generates invalid SQL for functional indexes

[openai0229]

Chat2DB Edition

Chat2DB Community

Chat2DB Version

Current main at 0ce376e6dc47791bd9d244e8595bada318611c62

Deployment

Web

Operating System

macOS

Operating System Version

27.0 (26A5378n)

Database

MySQL

Database Version

8.0.36

Driver Name and Version

MySQL Connector/J 8.4.0. Chat2DB Community currently pins liquibase-core 4.30.0.

Affected Area

SQL execution or DDL

Minimal Reproduction

Create the same table in two MySQL schemas, with the functional index present only in the source schema:

CREATE TABLE source_db.sample_config (
    id BIGINT NOT NULL PRIMARY KEY,
    attributes JSON,
    KEY idx_region ((CAST(JSON_EXTRACT(attributes, '$.regionId') AS UNSIGNED)))
);

CREATE TABLE target_db.sample_config (
    id BIGINT NOT NULL PRIMARY KEY,
    attributes JSON
);

1. Add both schemas as Chat2DB Community data sources.
2. Open the structure comparison workflow.
3. Compare source_db to target_db and generate the synchronization SQL.

The Community code path is:

DbDiffController.diff -> DbDiffServiceImpl.diff -> DiffToChangeLog.print -> Liquibase.update -> SqlUtils.parse.

Expected Behavior

Structure comparison should return an executable MySQL functional-index statement. The charset-introduced literal must not contain backslashes, and the computed key part must keep MySQL's required expression parentheses:

CREATE INDEX idx_region ON target_db.sample_config(
    (cast(json_extract(`attributes`,_latin1'$.regionId') as unsigned))
);

Actual Behavior

Liquibase serializes the computed index expression with two independent syntax problems:

<column computed="true" name="cast(json_extract(`attributes`,_latin1\'$.regionId\') as unsigned)"/>

1. The charset-introduced JSON path literal retains backslashes before its quote delimiters.
2. The computed key part is not wrapped as a MySQL expression key part, so Liquibase generates ON table(cast(...)) instead of ON table((cast(...))).

Druid first reports an unclosed string while Chat2DB filters the generated SQL. If only the backslashes are removed, Druid can parse the statement, but MySQL still rejects it with error 1064 because the functional key part lacks the extra parentheses.

This is an expression index. Liquibase's computed="true" flag describes the computed index key part; the table does not contain a generated column.

Logs

com.alibaba.druid.sql.parser.ParserException: unclosed str, token LITERAL_INT

[Failed SQL: (1064) CREATE INDEX idx_region ON target_db.sample_config(
    cast(json_extract(`attributes`,_latin1\'$.regionId\') as unsigned)
)]

# Removing only the backslashes is still invalid on MySQL:
[Failed SQL: (1064) CREATE INDEX idx_region ON target_db.sample_config(
    cast(json_extract(`attributes`,_latin1'$.regionId') as unsigned)
)]

Screenshots or Additional Context

This was reproduced with a clean, task-scoped MySQL 8.0.36 container, Liquibase 4.30.0, and MySQL Connector/J 8.4.0. INFORMATION_SCHEMA.STATISTICS.EXPRESSION exposed both a scalar JSON functional index and a multi-valued CAST(... AS UNSIGNED ARRAY) index with escaped quote delimiters. Liquibase retained those values in the generated changelog.

After normalizing the quote delimiters and wrapping each computed key part, Liquibase generated the required double-parenthesized SQL. liquibase update applied both changesets successfully, and both indexes were present in INFORMATION_SCHEMA.STATISTICS on the target schema.

The underlying Liquibase behavior has also been reported upstream: liquibase/liquibase#7851

Chat2DB issue #2246 concerns creating and preserving these indexes in the table editor. This report is separate: it covers structure comparison failing after Liquibase has already represented the index as a computed key part.

A narrow Chat2DB-side compatibility workaround can repair only MySQL CreateIndexChange entries whose columns are marked computed before the changelog is serialized: normalize recognized charset-introduced escaped literals, then ensure the computed expression is parenthesized. Ordinary columns, raw SQL, non-MySQL databases, and already-parenthesized expressions should remain unchanged.

Impact

Blocks startup or a core workflow

Affected Scope

A specific configuration or query

Workaround

A partial workaround exists

The functional index can be temporarily omitted from structure comparison and recreated manually, but that makes the generated synchronization SQL incomplete.

Submission Checklist

• I reproduced this on the selected Chat2DB edition.
• I included a minimal sanitized reproduction.
• I searched existing issues for duplicates.

[github-actions]

Thanks for the detailed MySQL 8.4.8 reproduction. The report is complete and isolates the failure to structure comparison serializing the computed functional-index expression with escaped quotes, producing SQL that both Druid and MySQL reject.

The next maintainer step is to reproduce this against the reported main commit and assess a narrowly scoped MySQL workaround around computed CreateIndexChange expressions, with coverage confirming ordinary columns, non-MySQL databases, and already-valid expressions are unchanged.

Generated by AI Issue Assistant for #2250 · 10.5 AIC · ⌖ 0.777 AIC · ⊞ 17.2K · ◷