GeezerVison

Purpose

Several PostgreSQL 18 SELECT forms fail to parse with Dialect.POSTGRESQL:

SELECT * INTO TEMP t2 FROM t;
SELECT * INTO UNLOGGED TABLE t2 FROM t;
SELECT * FROM a JOIN b USING (id) AS j;
SELECT ts AT LOCAL FROM t;
SELECT s IS NOT NFC NORMALIZED FROM t;
SELECT xmlelement(name foo, xmlattributes(a AS x, b)) FROM t;

This change reads them, following gram.y (into_clause/OptTempTableName, join_using_alias, a_expr AT LOCAL, IS [NOT] unicode_normal_form NORMALIZED, xml_attributes) and the manual: SELECT INTO, SELECT, AT LOCAL, IS NORMALIZED and xmlelement.

Current design (As-is)

• PlainSelect keeps the INTO target in two unrelated fields: intoTables for SELECT … INTO t after the select list, and intoTempTable for Informix's … INTO TEMP t at the end of the query. Each has its own rendering in toString() and in SelectDeParser. There is no place for a persistence, so INTO TEMP t FROM …, INTO UNLOGGED t and INTO TABLE t are rejected.
• Join renders USING (…) once in toString() and once in SelectDeParser, and has no alias for the USING columns.
• TimezoneExpression holds a list of zones, but the zone operand reads a following AT TIME ZONE itself, so a AT TIME ZONE 'UTC' AT TIME ZONE 'CET' is stored as a AT TIME ZONE ('UTC' AT TIME ZONE 'CET'). AT LOCAL is rejected.
• IS [NOT] [form] NORMALIZED is rejected.
• xmlattributes(a AS x) is rejected, because XMLATTRIBUTES is read as a plain function call.
• GROUP BY ALL | DISTINCT already parses with Dialect.POSTGRESQL.

Target design (To-be)

• SELECT INTO: the new SelectIntoClause value object is the only storage of the clause in PlainSelect (getIntoClause()/setIntoClause()/withIntoClause()).
  • It holds the tables, the Persistence (TEMP, TEMPORARY, LOCAL_TEMP, LOCAL_TEMPORARY, GLOBAL_TEMP, GLOBAL_TEMPORARY, UNLOGGED, or null), the optional TABLE keyword and the Position (BEFORE_FROM or TRAILING).
  • getIntoTables()/setIntoTables()/addIntoTables() and getIntoTempTable()/setIntoTempTable() remain and are views of the clause. toString() and SelectDeParser render it through one appendTo.
  • The persistence and TABLE keywords are read with Dialect.POSTGRESQL and without a dialect. As on the server, a persistence word that no table name follows is the table itself: SELECT * INTO temp FROM t creates the table temp.
  • With Dialect.POSTGRESQL, an empty select list may precede INTO (SELECT INTO t2 FROM t), and forms the server rejects are rejected: several targets (INTO a, b), INTO TABLE FROM t and a trailing INTO TEMP t. Other dialects keep these readings.
  • A SELECT has one INTO clause: SELECT a INTO x FROM t INTO TEMP y, which parsed before, is rejected.
  • Feature.selectInto is added to the PostgreSQL validation capabilities.
• JOIN USING alias: Join.getUsingAlias() holds the alias of a JOIN b USING (id) AS j. AS is required, the join must be a qualified join and the USING columns plain names, as in gram.y. Join.appendUsingTo is shared by toString() and the deparser.
• AT LOCAL: TimezoneExpression.isAtLocal(). AT TIME ZONE and AT LOCAL now chain from left to right: one TimezoneExpression holds a run of zones and the AT LOCAL that ends it, and a conversion after AT LOCAL wraps that expression. A cast may follow (ts AT LOCAL::date). toString() and ExpressionDeParser share appendTo.
• IS NORMALIZED: the new IsNormalizedExpression with isNot() and getNormalForm() (NFC, NFD, NFKC, NFKD, or null when omitted). It chains with the other postfix IS tests (s IS NORMALIZED IS TRUE). ExpressionVisitor gets default methods, so existing visitors compile unchanged; the deparser, TablesNamesFinder, the validator and ExpressionVisitorAdapter visit the operand.
• XMLATTRIBUTES: inside XMLELEMENT, directly after the element name, xmlattributes(value [AS name], …) is a Function over AliasedExpression arguments, the model XMLFOREST uses. It is not read anywhere else, as on the server. With Dialect.POSTGRESQL the NAME keyword and an identifier as the element name are required; other dialects also read XMLELEMENT("Emp", XMLATTRIBUTES(e.id AS "id")).
• GROUP BY ALL | DISTINCT: no change; tests cover each gram.y item after the quantifier (expressions, (), ROLLUP, CUBE, GROUPING SETS).
• Grammar: no new tokens or reserved words. NFC, NFD, NFKC, NFKD, NORMALIZED and XMLATTRIBUTES are matched as contextual keywords; AT, LOCAL, TEMP, UNLOGGED stay usable as names and aliases.
• Compatibility:
  • SQL output of statements that parsed before is unchanged.
  • PlainSelect holds one INTO clause: setIntoTempTable(table) replaces a clause set through setIntoTables(…), and the other way round setIntoTables(…) replaces a trailing clause.
  • a AT TIME ZONE x AT TIME ZONE y now gives one TimezoneExpression with two zones instead of a nested one in the zone operand. Its zone list spans the text from the first AT.
  • AliasedExpression arguments of XMLFOREST now carry an AST node.

Not covered: a reserved word as the element or attribute name (xmlelement(name table, …), AS select), GROUPING SETS as an item of a longer GROUP BY list or nested in another GROUPING SETS, and operators directly after IS NORMALIZED (s IS NORMALIZED = true), which the other IS tests do not accept either.

Validation

• 208 new tests in PostgreSqlSelectFormsTest:
  • 113 statements of postgresql/select-forms.sql: parse, toString, StatementDeParser and reparse with Dialect.POSTGRESQL and without a dialect, and PostgreSQL validation; the file is also walked by ASTNodeCoverageTest;
  • AST fields and node ranges of each form, the legacy INTO accessors, visitors, TablesNamesFinder, statement features and Feature.selectInto;
  • 67 rejected forms, the readings other dialects keep (SELECT a, b INTO x, y, INTO #tmp, Informix INTO TEMP … WITH NO LOG, Oracle XMLELEMENT), statement boundaries in a script, and the GROUP BY quantifier forms.
• Java 17 Gradle spotlessApply cleanTest check: 13,545 tests, 13,520 passed, 25 skipped, zero failures/errors. The grammar choice conflict check passes.
• Compared with master (e27929f76) on the MySQL 8.4 and PostgreSQL 18 DDL/DCL and non-DDL statement corpora:
  • Fixed: SELECT * INTO TEMP t2 FROM t, SELECT * FROM a JOIN b USING (id) AS j, SELECT ts AT LOCAL FROM t, SELECT s IS NFC NORMALIZED FROM t and SELECT xmlelement(name foo, xmlattributes(a AS x, b)) FROM t, each with Dialect.POSTGRESQL and without a dialect.
  • 0 regressions; no other case changed status, statement class or output.
• PostgreSQL 18.6 accepts the syntax of 147 statements that this branch parses, 107 of which master rejects; some stop only at execution. It reports a syntax error for 69 statements that this branch rejects, including the 67 rejected forms of the tests. Twenty statements differ, none of them changed by this branch:
  • Ten that PostgreSQL rejects parse here as on master: INTO inside a sub-select or a UNION branch (gram.y accepts it and the analyzer reports it), USING after NATURAL JOIN or CROSS JOIN, and xmlelement(name), xmlelement(name 'foo'), xmlelement(name foo, s ORDER BY a), xmlattributes(a) and xmlattributes(*), which are read as plain function calls.
  • Ten that PostgreSQL accepts fail here as on master: the forms listed under "Not covered", ts AT LOCAL COLLATE "C", s IS NORMALIZED::text and s IS NULL = true.

AI assistance: prepared with Claude Code.