Lexeme conflicts across dialects: what is solved, what remains

[fudianchn]

AI disclosure: this discussion was prepared with AI coding agents, reviewed and revised line by line by me.

The one-lexeme-two-dialects class of problem keeps recurring (#677, #2466, #2502), and each resolution left a switch behind: allowSquareBracketQuotation (v3.0), allowBackslashEscapeCharacter (v4.6), allowHashLineComments (#2508), now reachable per dialect through the #2511 presets, a narrow slice of the dialect-aware direction @minleejae argued for in #2465. Following up on its "more deterministic parsing in ambiguous cases" benefit, here is an inventory of the remaining contested lexemes, every row probed on master 94c4508 first hand.

Already covered

lexeme	conflict	how it is resolved today
[..]	SQL Server quotation vs Postgres arrays	switch (v3.0, #677)
\	escape sequences in string literals	switch (v4.6)
#	MySQL line comments vs Postgres operator	switch, operator by default (#2507, #2508)
?	JDBC parameter vs Postgres jsonb operators vs ClickHouse ternary	contextual lookahead: js ? 'k' and `js ?
||	concat vs boolean OR	concat; SELECT a || b yields Concat on master
MINUS / EXCEPT	set operation wording	both accepted
`	MySQL identifier quotation	lexed unconditionally
q'[..]', E'..', B'..', X'..', @var, @@var	dialect literals and variables	all parse and round-trip

Open conflicts

1. "...": identifier vs string literal. ANSI, Postgres, Oracle and SQL Server (QUOTED_IDENTIFIER ON) read double quotes as identifiers; MySQL default mode and SQL Server with QUOTED_IDENTIFIER OFF read them as string literals. Master always produces the identifier reading: SELECT "not an identifier" yields a Column. A MySQL user silently gets the wrong AST node type. No switch exists today (checked Feature and its history). Candidate: an allowDoubleQuotedStrings-style switch, off by default, with the MySQL preset turning it on (MySQL itself flips back to identifiers under ANSI_QUOTES, so the preset row would take a decision).

2. $tag$...$tag$: tagged dollar quoting, [BUG] JSQLParser 5.1: PostgreSQL: fail to parse dollar-quoted string constants with tags #2233. Plain $$abc$$ parses (StringValue), a tag breaks the lexer: SELECT $tag$ it's $tag$ fails with a TokenMgrException. Same machinery family as the feat(parser): support MySQL # line comments behind allowHashLineComments #2508 flag work, a MORE-style state keyed on the opening tag, and it unlocks function bodies for the Postgres family.

3. 'a' 'b': adjacent string literals. The standard and Postgres concatenate adjacent literals only when the whitespace between them contains a newline (PostgreSQL docs); GoogleSQL and Spark/Databricks concatenate them across any whitespace (their literal chunking); MySQL and SQL Server read the second one as an alias; Oracle errors. Master takes the alias reading silently: SELECT 'a' 'b' yields the StringValue 'a' aliased 'b', expression contexts fail loudly. Lower impact, but the same never-silently-wrong-dialect class. (Corrected: the original row missed the newline qualifier and the GoogleSQL/Spark variant.)

Proposal

Continue the incremental pattern that just carried # through: one conflict, one switch off by default, one preset row, two-state tests, every behavior delta disclosed. The double-quote item looks like the highest-value next step; I am happy to prepare it, and the tagged dollar quoting after that (with due diligence to the 19 comments of prior art on #2233).

[manticore-projects]

Thank you.

Item 1) is interesting for BigQuery especially and I have had to apply ugly workarounds in JSQLTranspiler
Item 2) I had a prototype running for this and it is not an Open Conflict, but a new feature (it does not collide with any RDBMS or dialect). I will try to dig this up, the DBeaver folks also have an open issue on that.

I am not a big fan of item 3, but as always: if we can get it done somehow, I won't oppose it.

[manticore-projects]

Please let us always consider DataBricks, BigQuery, Snowflake and Redshift as well.

[fudianchn]

The four warehouses split evenly on ", each verified against the vendor docs:

dialect	"..." reads as
BigQuery, Databricks (Spark SQL)	string literal (identifiers are backticks)
Snowflake, Redshift	delimited identifier

With MySQL default mode on the string side and ANSI/Postgres/Oracle/SQL Server on the identifier side, item 1 has preset rows on both ends of the switch, so it is next for me. Also from the BigQuery lexical page: # is a single-line comment there too, a future BIGQUERY preset would carry allowHashLineComments as well.

Item 2 is yours then, noted as a feature rather than a conflict.

Item 3 gained shape while checking the warehouses: the standard and Postgres concatenate adjacent literals only across a newline, GoogleSQL and Spark/Databricks across any whitespace (their literal chunking), MySQL and SQL Server alias, Oracle errors. Three readings, so not a boolean switch: an enum-valued feature (off / newline-only / any whitespace), the shape Feature.dialect already has. The survey row is corrected accordingly, and I would sequence it after items 1 and 2.

Sources: BigQuery lexical, Spark literals, Snowflake identifiers, Redshift names, PostgreSQL lexical