feat: parse MySQL 8.4 SHOW statements for variables, status, grants, logs and other server metadata - #2916

Purpose

Parse the MySQL 8.4 SHOW statements that still came back as UnsupportedStatement (or as a bare ShowStatement name), following the server grammar (sql_yacc.yy, show_*_stmt):

• SHOW [GLOBAL | SESSION | LOCAL] VARIABLES and STATUS, SHOW [FULL] PROCESSLIST,
• SHOW DATABASES, CHARACTER SET, COLLATION, [FULL] TRIGGERS, EVENTS, OPEN TABLES, PROCEDURE/FUNCTION STATUS and CODE,
• SHOW GRANTS [FOR account [USING role, …]], SHOW WARNINGS / ERRORS with LIMIT or COUNT(*), SHOW PROFILE,
• SHOW ENGINE name {STATUS | MUTEX | LOGS}, ENGINES, PLUGINS, PRIVILEGES, PROFILES,
• SHOW BINARY LOG STATUS, BINARY LOGS, BINLOG EVENTS, RELAYLOG EVENTS, REPLICA STATUS, REPLICAS,
• the full SHOW [EXTENDED] {INDEX | INDEXES | KEYS} {FROM | IN} t [{FROM | IN} db] [WHERE expr].

SHOW GLOBAL VARIABLES LIKE 'innodb%';
SHOW STATUS WHERE Variable_name LIKE 'Com%' AND Value > 0;
SHOW FULL PROCESSLIST;
SHOW GRANTS FOR 'app'@'%' USING 'reader', 'writer'@'%';
SHOW WARNINGS LIMIT 5 OFFSET 2;
SHOW COUNT(*) ERRORS;
SHOW ENGINE INNODB STATUS;
SHOW BINLOG EVENTS IN 'binlog.000001' FROM 4 LIMIT 2, 3;
SHOW RELAYLOG EVENTS LIMIT 10 FOR CHANNEL 'c1';
SHOW REPLICA STATUS FOR CHANNEL 'c1';
SHOW EXTENDED INDEX FROM t FROM db WHERE Key_name = 'PRIMARY';
SHOW FULL TRIGGERS FROM db LIKE 't%';
SHOW PROCEDURE STATUS WHERE Db = 'shop';
SHOW PROFILE CPU, BLOCK IO FOR QUERY 2 LIMIT 10;
SHOW CHARACTER SET LIKE 'utf8%';

Current design (As-is)

• Show() types SHOW TABLES, SHOW INDEX FROM t, and the SHOW [FULL] COLUMNS, SHOW TABLE STATUS and SHOW CREATE forms of Parse MySQL SHOW FULL COLUMNS, SHOW TABLE STATUS and SHOW CREATE statements #2824. Every other SHOW goes to captureRest(): one word becomes ShowStatement("DATABASES"), more words an UnsupportedStatement. So SHOW GLOBAL VARIABLES LIKE 'a%', SHOW GRANTS FOR 'u'@'h', SHOW WARNINGS LIMIT 5 or SHOW ENGINE INNODB STATUS carry no structure.
• ShowIndexStatement holds a table name string and reads INDEX FROM name only: SHOW INDEX FROM t FROM db, SHOW KEYS IN t or SHOW EXTENDED INDEX FROM t WHERE … fail or are unsupported, and TablesNamesFinder throws for it.
• The {FROM | IN} db clause and the LIKE | WHERE filter are written out separately in ShowTables(), ShowColumns() and ShowTableStatus(), with four fields per class.
• The typed forms of Parse MySQL SHOW FULL COLUMNS, SHOW TABLE STATUS and SHOW CREATE statements #2824 are chosen by a lookahead over the whole statement, and that lookahead sees only the first condition of a WHERE expression (the rest is read by prattExpressionRest()), so SHOW COLUMNS FROM t WHERE a = 1 AND b = 2 becomes an UnsupportedStatement.
• The LIKE pattern is any simple expression in every dialect, although MySQL takes a string literal only (SHOW TABLES LIKE a is a syntax error there).

Target design (To-be)

Shared clauses

• ShowDbSelection ({FROM | IN} db) and ShowFilter (LIKE 'pattern' | WHERE expr, a Kind and one expression) are value types with one production each, used by SHOW TABLES, COLUMNS, TABLE STATUS, INDEX and the new statement. ShowTablesStatement, ShowColumnsStatement and ShowTableStatusStatement hold them as their single state; getSelectionMode()/getDbSelectionMode(), getDbName(), getLikeExpression(), getWhereCondition() and their setters remain and read and write that state. A statement holds one filter, so setWhereCondition replaces a LIKE filter (the classes used to keep both fields and render one or both).
• With Dialect.MYSQL and Dialect.MARIADB the LIKE pattern is a string literal ('…' or "…", a StringValue), as in the server; a statement with another pattern is left to the catch-all. Without a dialect it stays any simple expression. This is a behaviour change for SHOW TABLES/COLUMNS/TABLE STATUS … LIKE expr in those two dialects, for input MySQL rejects.
• The whole-statement lookahead accepts a typed form once it has entered the WHERE filter, so a condition with AND/OR is read by the typed statements (also those of Parse MySQL SHOW FULL COLUMNS, SHOW TABLE STATUS and SHOW CREATE statements #2824); text after the condition that the expression does not take is a parse error, as in MySQL.
• StatementDeParser prints these statements through appendTo(builder, printer), shared with toString(), so filter and limit expressions go through the ExpressionDeParser.

ShowIndexStatement

• Extended like ShowColumnsStatement in Parse MySQL SHOW FULL COLUMNS, SHOW TABLE STATUS and SHOW CREATE statements #2824: isExtended(), getKeyword() (INDEX, INDEXES, KEYS), getTableSelectionMode(), getTable() (a Table), getDbSelection() and getWhereCondition(). The String constructor, getTableName() and setTableName(String) remain and derive from the table. TablesNamesFinder and the validator report the table, qualified by the trailing FROM db.

MySqlShowStatement

• One statement for the other forms instead of a class per keyword. Kind names the statement and lists the Parts it allows; getKeyword() keeps the spelling (SCHEMAS, CHARSET, CHAR SET, STORAGE ENGINES).

  Kind	Parts
  VARIABLES, STATUS	Scope (GLOBAL, SESSION, LOCAL), filter
  PROCESSLIST	FULL
  DATABASES, CHARACTER_SET, COLLATION, PROCEDURE_STATUS, FUNCTION_STATUS	filter
  TRIGGERS (with FULL), EVENTS, OPEN_TABLES	database, filter
  PROCEDURE_CODE, FUNCTION_CODE	routine name (name or db.name)
  ENGINE	engine name (a name, a string or ALL), EngineInfo (STATUS, MUTEX, LOGS)
  GRANTS	account and USING roles (UserAccount, read by MySqlAccountName() like CREATE USER)
  WARNINGS, ERRORS	COUNT(*) or limit
  BINLOG_EVENTS, RELAYLOG_EVENTS	log name, position, limit; channel for RELAYLOG
  REPLICA_STATUS	channel
  PROFILE	ProfileType list, FOR QUERY n, limit
  ENGINES, PLUGINS, PRIVILEGES, PROFILES, BINARY_LOGS, BINARY_LOG_STATUS, REPLICAS	none

• Values follow the server grammar: a limit is LIMIT [offset,] n (Limit) or LIMIT n OFFSET offset (Limit and Offset, as in a query) with unsigned integers, names or ?; log names and channels are strings; a log position is an unsigned number; COUNT( has no space, as the MySQL lexer requires.

• Setting a part the kind does not allow throws IllegalArgumentException, and setKind drops such parts.

• Dialects:

  • Dialect.MYSQL and Dialect.MARIADB read every form, including the one-word statements (SHOW DATABASES is now a MySqlShowStatement there instead of a ShowStatement).
  • Without a dialect the forms are read too, but a single word stays a ShowStatement as before.
  • SHOW MASTER STATUS, SHOW MASTER LOGS, SHOW SLAVE STATUS and SHOW SLAVE HOSTS, which MySQL 8.4 removed, are read with Dialect.MARIADB and without a dialect, not with Dialect.MYSQL.
  • Other dialects are unchanged: PostgreSQL SHOW name, SHOW TIME ZONE, SHOW TRANSACTION ISOLATION LEVEL and SHOW SESSION AUTHORIZATION stay ShowStatement, and other forms stay UnsupportedStatement.

• A SHOW form that MySQL rejects (SHOW FULL EVENTS, SHOW WARNINGS LIKE 'a', SHOW BINLOG EVENTS FROM -1, SHOW REPLICA STATUS FOR CHANNEL c) keeps the catch-all result, UnsupportedStatement.

• No new token and no reserved word: VARIABLES, STATUS, PROCESSLIST, GRANTS, WARNINGS, BINLOG, PROFILE and the others stay identifiers, matched in the SHOW production only.

• Wired into StatementVisitor (default method), StatementVisitorAdapter, StatementDeParser, TablesNamesFinder (names no table), StatementFeatureVisitor (RETURNS_RESULT_SET) and StatementValidator with the new feature mySqlShow, enabled for MySQL and MariaDB. README, index.rst and usage.rst describe the statements.

Not included: SHOW PARSE_TREE (debug builds only; MySQL 8.4.11 reports a syntax error) and account names that MySqlAccountName() does not read yet (u@h.example.com without quotes).

Validation

• New tests: MySqlShowStatementTest (295 cases) and ShowClausesTest (11 cases), more cases in ShowIndexStatementTest, ShowIndexStatementValidatorTest and MySqlShowStatementValidatorTest, and entries in ASTNodeCoverageTest. They cover each form with Dialect.MYSQL, Dialect.MARIADB and without a dialect, toString, the deparser, reparse, AST fields and node spans, forms MySQL rejects, readings kept in other dialects, statement boundaries in scripts and IF blocks, visitors, TablesNamesFinder and validation features.

• ./gradlew spotlessApply cleanTest check jar on Java 17: 13,677 tests, 0 failures, 25 skipped; checkGrammarAmbiguity, Checkstyle, PMD, SpotBugs and JaCoCo pass.

• Compared with master (e27929f76) on the MySQL and PostgreSQL DDL/DCL and non-DDL statement corpora (parsed with the dialect and without): no regression and no changed result. The 26 MySQL SHOW statements of the non-DDL corpus that were UnsupportedStatement, a bare ShowStatement or a parse error (SHOW EXTENDED INDEX FROM t FROM db WHERE …) are now typed with Dialect.MYSQL (21 of them without a dialect, where one-word statements stay ShowStatement).

• MySQL 8.4.11: the 146 statements whose syntax the server accepts are all typed with Dialect.MYSQL (131 MySqlShowStatement, 12 ShowIndexStatement, 2 ShowColumnsStatement, 1 ShowTableStatusStatement); some stop only at execution, for example with an unknown account or log file. Of the 125 statements the server rejects, 121 keep the UnsupportedStatement of the catch-all and 4 are parse errors (text after a WHERE condition, a LIKE pattern that is not a string in SHOW TABLES). Two statements differ: SHOW ERRORS LIMIT ? OFFSET ? and SHOW BINLOG EVENTS LIMIT ? are typed, because limit_option includes the parameter marker, although the server takes ? only in a prepared statement.

AI assistance: prepared with Claude Code.