GeezerVison

Purpose

Allow keywords that MySQL 8.4 and PostgreSQL 18 accept as unquoted column and table names. A sweep of MySQL's non-reserved words and PostgreSQL's unreserved and column-name keywords, each used as a column in CREATE TABLE, in ALTER TABLE ... ADD COLUMN and as a table name, found 38 statements that fail and 26 CREATE TABLE <keyword> (...) statements that silently become an UnsupportedStatement. Each server accepts the words of its own list, for example:

-- MySQL 8.4
CREATE TABLE t (current INT, only INT, returning INT, sql_no_cache INT);
ALTER TABLE t ADD COLUMN offset INT;
CREATE TABLE value (a INT);
-- PostgreSQL 18
CREATE TABLE t (prior int, unknown int, between int, xmlserialize int, p path);
ALTER TABLE t ADD COLUMN set int;

Current design (As-is)

• ABSENT, CURRENT, FORCE, IMPORT, PRIOR, SQL_NO_CACHE, UNBOUNDED, UNKNOWN and XMLSERIALIZE are reserved, although each one only starts a clause in a few places.
• BETWEEN, FULL, ONLY and RETURNING are reserved and are not in the list of reserved words that RelObjectName() accepts in name positions (like LEFT, ORDER, SET, VALUE).
• Those listed words are only names after a delimiter, after a clause keyword or before a safe follower. After TABLE or COLUMN, CREATE TABLE value (a INT) and ADD COLUMN offset INT fail; for CREATE TABLE the statement falls back to an UnsupportedStatement.
• A CREATE TABLE element that starts with EXCLUDE is always read as an exclusion constraint, so a column named exclude fails.
• ColDataType() does not accept the PATH token, so PostgreSQL's geometric type path fails.
• keywords.rst still lists EXISTS, EXTEND, SAMPLE and STATEMENT, which fix: allow STATEMENT as an unquoted identifier #2706, fix: allow exists as an unquoted identifier #2773 and fix: allow sample and extend as unquoted identifiers #2774 made non-reserved.

Target design (To-be)

• Non-reserved words, moved into NonReservedWord() (as in Make dialect-specific clause words non-reserved #2793). Each keeps its keyword use through these guards:
  • PRIOR is Oracle's operator only when an operand follows (PRIOR id, PRIOR (a), PRIOR 2), not in prior = 1, prior IS NULL or ORDER BY prior DESC.
  • SQL_NO_CACHE is a MySQL select modifier only before a select item.
  • FORCE INDEX|KEY (...) is an index hint, never an implicit alias.
  • UNBOUNDED PRECEDING|FOLLOWING, XMLSERIALIZE ( and ABSENT ON NULL keep their meaning.
  • current.x is a qualified column. CURRENT alone stays a TimeKeyExpression as before.
• Reserved words that are names in name positions: BETWEEN, FULL, ONLY and RETURNING are added to that list in RelObjectName(). They start clauses after an expression or a table, so they stay reserved:
  • FULL follows the LEFT/RIGHT rule. It is an implicit alias unless a join follows, so FROM t full works and FROM a FULL JOIN b is unchanged.
  • BETWEEN, ONLY and RETURNING are never implicit aliases (AS still works).
  • ONLY before a relation name after FROM, JOIN, UPDATE, INTO, USING or , is PostgreSQL's inheritance prefix. Forms that are still unsupported, such as DELETE FROM ONLY t or JOIN ONLY t, fail as before instead of naming a table only.
  • JSON_ARRAY(RETURNING JSON) and JSON_OBJECT(ABSENT ON NULL) keep their clauses.
• A word after TABLE or COLUMN is always a name. IF [NOT] EXISTS and ONLY are matched before the name, as before.
• EXCLUDE starts an exclusion constraint only before USING, ( or WHERE. PATH is accepted as a column type, but not as the type of a JSON_TABLE column, where it introduces the path.
• NonReservedWord() is split into two productions:
  • At master, the lookahead routine that JavaCC generates for the single choice of 425 words has 7,951 bytes of bytecode. 9 more words take it to 8,122 bytes, above the JVM's 8,000 byte limit for JIT compilation (-XX:HugeMethodLimit), so it would always run interpreted. Measured, that cost about 5% on the benchmark below.
  • With the split, the largest routine has 4,222 bytes. The token kinds stay between the MIN_NON_RESERVED_WORD/MAX_NON_RESERVED_WORD sentinels.
• Two optional guards became choices (PRIOR before a condition and the SQL_NO_CACHE modifier). JavaCC does not evaluate a semantic lookahead on [...] or (...)* while it scans an enclosing syntactic lookahead, and a choice keeps that guard.
• keywords.rst is regenerated with updateKeywords. 88 words are reserved (was 100) and 434 non-reserved (was 425).

Not changed

• ALTER TABLE t ADD offset INT without COLUMN still fails for the listed reserved words. Clause keywords, such as IF NOT EXISTS, also follow ADD.
• DROP COLUMN and RENAME COLUMN read the column with KeywordOrIdentifier(), which accepts only a short fixed list of words. That is left to the ALTER column work.
• After a comma, ONLY followed by an unquoted identifier is read as the relation prefix. So CREATE TABLE t (a INT, only serial) still fails, and (only serial) or (a INT, only INT) parse.

Validation

• KeywordsTest: 1,015 cases.
  • 72 are new. 66 of them cover 22 words in the default, MySQL and PostgreSQL dialects:
    • a column in CREATE TABLE and in ADD COLUMN;
    • a CREATE TABLE name;
    • select list, qualified column, alias, WHERE and ORDER BY;
    • FROM, UPDATE ... SET and an INSERT column list.
  • Each case is parsed, deparsed and reparsed, and the AST names are checked. 6 more tests check the AST of the clauses that use these words.
  • 18 cases come from the 9 new non-reserved words in the existing per-keyword tests.
• Java 17 offline Gradle spotlessApply check: 10,442 tests, 10,417 passed, 25 skipped, zero failures/errors. The grammar choice conflict check reports no warnings.
• About 260 statements around these words were parsed in the default, MySQL and PostgreSQL dialects, and most of them also in the Oracle dialect. They cover the keyword uses (joins, index hints, RETURNING, JSON functions, window frames, Oracle CONNECT BY, PostgreSQL ONLY, EXCLUDE constraints, IF [NOT] EXISTS) and the identifier uses. The statement type and the rendered SQL match master, except for the identifier uses that now parse.
• In the keyword sweep, all 1,482 distinct MySQL and 1,179 PostgreSQL statements now parse as CreateTable/Alter, with no UnsupportedStatement left. The path column type statements also parse. The other corpus statements are unchanged.
• Performance:
  • Benchmark: all 321 .sql test resources parsed in the calling thread, thread CPU time, 40 rounds per JVM, 10 alternating JVM pairs against master.
  • Median best round: 69.6 ms on master and 69.9 ms with this change. Median of the per-run medians: 90.3 ms and 89.8 ms. This change was faster in 4 of 10 pairs.
  • Lookahead work on the same corpus: token scans +1.3% and syntactic lookahead calls -2.4%.
• MySQL 8.4 and PostgreSQL 18.3: every corpus statement this branch parses ran as written on one server and as rendered by toString() on a second, identical server, in the same order. All 205 MySQL and 328 PostgreSQL statements give the same result (success, or the same error).

Integration validation

Applied #2806–#2819 together on master (bdf8ac31), with the stacked PRs on top of their bases (#2806 → #2807 → #2808, #2806 → #2809 → #2810 and #2811 → #2812).

• Each branch merges cleanly into master. Between the branches, conflicts are limited to adjacent additions (README/index rows, usage sections, imports, visitor and feature lists, one enum line and one alias guard).
• The integrated build passes Java 17 offline Gradle spotlessApply check: 11,494 tests, 11,469 passed, 25 skipped, zero failures/errors. The grammar choice conflict check passes.
• On a corpus of 635 MySQL 8.4 and PostgreSQL 18 DDL/DCL statements, each accepted by the server, the integrated build parses 265 of 271 MySQL statements (master: 206) and 363 of 364 PostgreSQL statements (master: 328). Every MySQL 8.4 non-reserved word and PostgreSQL 18 unreserved or column-name keyword parses as a column and table name.
• On paired MySQL 8.4 and PostgreSQL 18.3 servers, all 263 MySQL and 362 PostgreSQL statements rendered by the integrated build give the same results as the originals.