Module com.darkcollective.relix.plan

Package com.darkcollective.relix.plan.internal

Enum Class Dialect

java.lang.Object

java.lang.Enum<Dialect>

com.darkcollective.relix.plan.internal.Dialect

All Implemented Interfaces:

Serializable, Comparable<Dialect>, Constable

public enum Dialect
extends Enum<Dialect>

A SQL dialect — the database-specific surface syntax used when the planner pushes work down as SQL. It abstracts the two things that vary across the databases relix targets:

• Identifier quoting — quote(String) wraps a column or alias in the dialect's quote characters ("x" for PostgreSQL, `x` for MySQL). The generic dialect leaves a plain identifier bare, relying on the database folding unquoted identifiers case-insensitively, and delimits any other name with standard SQL's "x", since no backend reads order-lines bare. table(String) does the same for each part of a possibly schema-qualified table name.
• Row limiting — limit(long, long) renders the LIMIT/OFFSET clause.
• String literals — stringLiteral(String) writes a value as literal text. Doubling the quote is standard SQL and not sufficient: MySQL reads a backslash as an escape, so the two backends need different rules for the same value.

The GENERIC dialect emits the exact SQL the planner produced before dialects existed for every name that is a plain identifier (unquoted identifiers, LIMIT n [OFFSET m]), so it is a safe default for H2, PostgreSQL and MySQL. The POSTGRES, MYSQL, DUCKDB, SQLITE and SQLSERVER dialects add identifier quoting, which is correct when the relix schema's column names match the database's stored case (always true for introspected conn.table references).

Every per-backend question here is an exhaustive switch (this), so a constant added to this enum does not compile until it has answered each one. That is the point rather than a style: an answer inherited by majority is indistinguishable from an answer nobody considered, and the two differ exactly when the new backend is the odd one out — which is the case a default is least able to get right. The two methods that do not switch are the two whose answer is not the backend's to give: quote(java.lang.String) reads the quoting each constant declares, and boolAnd(java.lang.String) renders SQL-92 that no backend lacks.

Every constant but SQLSERVER and DB2 renders LIMIT n [OFFSET m]; Db2 renders the standard's FETCH FIRST, which it accepts with or without an ordering. SQL Server's row-limiting is OFFSET … FETCH, which needs more than an arm of limit(long, long): that form is legal only after an ORDER BY, so the pushdown planner also declines a limit fold over an unordered sub-tree — limitNeedsOrderBy().

Nested Class Summary

Nested classes/interfaces inherited from class java.lang.Enum

Enum.EnumDesc<E extends Enum<E>>

Enum Constant Summary

Enum Constants

Enum Constant	Description
DB2	IBM Db2 for Linux, UNIX and Windows, 11.5 and later.
DUCKDB	DuckDB: double-quoted identifiers, and SQL deliberately shaped like PostgreSQL's — NULLS LAST, date_trunc, LATERAL, window functions.
GENERIC	Unquoted identifiers and LIMIT n [OFFSET m] — the default.
MYSQL	MySQL / MariaDB: back-tick-quoted identifiers.
POSTGRES	PostgreSQL: double-quoted identifiers.
SQLITE	SQLite: double-quoted identifiers and a binary default collation, so it compares and orders strings as the engine does.
SQLSERVER	Microsoft SQL Server: bracket-quoted identifiers, and the first backend here whose differences are structural rather than spellings.

Method Summary

Modifier and Type	Method	Description
Optional<String>	boolAnd(String predicateSql)	Returns the SQL boolean-aggregate expression for universal quantification (∀) over the given rendered predicate, or empty when this dialect has no supported spelling and the operator must run in-engine instead.
String	booleanLiteral(boolean value)	Writes a boolean value as a SQL literal.
boolean	comparesStringsExactly()	Whether this backend compares two strings the way the engine does — exactly, character for character.
static boolean	comparesStringsExactly(ConnectionDeclaration connection)	Whether connection's backend compares strings as the engine does: its declared collation when it has one, else its dialect's default.
Optional<String>	dateLiteral(LocalDate value)	Renders a DATE value as a SQL literal, or empty where this dialect has no literal that means what the engine means: GENERIC a quoted ISO string (relies on implicit cast), POSTGRES/MYSQL/DUCKDB a typed DATE '…' literal.
Optional<String>	durationLiteral(Duration value)	Renders a DURATION value as a SQL INTERVAL literal, or empty where this dialect has no spelling for one.
String	exactStringComparison(String expression)	Wraps a rendered string expression so this backend compares it exactly, or returns it unchanged where the backend already does.
String	exactStringOrder(String expression)	Wraps a rendered string expression so this backend orders it by code point, or returns it unchanged where the backend already does.
Optional<String>	like(String subject, String pattern, Optional<String> literal, boolean negated)	Renders subject LIKE pattern (or NOT LIKE) so that it matches what the engine's LIKE matches, or empty where this dialect cannot say it.
String	limit(long count, long offset)	Renders the row-limit clause: LIMIT count, plus OFFSET offset when offset is positive.
boolean	limitNeedsOrderBy()	Whether this dialect's row-limiting clause is legal only after an ORDER BY, so that a limit over an unordered sub-tree must not fold.
Optional<String>	nearestRowJoin(String left, String columns, String source, String orderBy, String alias, boolean inner)	Renders the FROM clause of an AS-OF join folded as a correlated nearest-row lookup — for each left row, the one right row a sub-select filters by the match condition and orders by the match column — or empty where this dialect has no such construct (supportsLateralAsOf()).
static Dialect	of(ConnectionDeclaration connection)	Resolves the dialect for a connection: its declared dialect, if any, otherwise inferred from the JDBC URL, otherwise GENERIC.
List<String>	orderByTerms(String expr, boolean descending)	Renders one ORDER BY key so the database places NULLs where the engine does — last, in both directions.
boolean	ordersStringsExactly()	Whether this backend puts two strings in the order the engine puts them — by code point, which is the order a binary collation gives and the order UTF-8 bytes already sort in.
static boolean	ordersStringsExactly(ConnectionDeclaration connection)	Whether connection's backend orders strings as the engine does: its declared collation when it has one, else its dialect's default.
Optional<String>	pinSessionToUtcSql()	The statement that pins a session's time zone to UTC, or empty for a backend this has not been confirmed against.
PushdownTarget	pushdownTarget()	Names this dialect to a function library, so a function can supply its own spelling here without the planner knowing anything about it.
String	quote(String identifier)	Quotes an identifier for this dialect, doubling any embedded quote character.
String	stringLiteral(String value)	Writes value as a SQL string literal for this dialect.
boolean	supportsLateralAsOf()	Returns whether this dialect's target database reliably supports a LATERAL derived table in a join — the construct used to push an AS-OF join down as a correlated "nearest row" lookup (LEFT JOIN LATERAL (SELECT … ORDER BY … LIMIT 1) ON TRUE).
boolean	supportsWindowFunctions()	Returns whether this dialect's target database reliably supports SQL window functions (OVER (PARTITION BY … ORDER BY … ROWS …)).
String	table(String table)	Writes a table name for this dialect, quoting each dot-separated part with quote(java.lang.String): public.orders becomes "public"."orders" on PostgreSQL.
Optional<String>	timeLiteral(LocalTime value)	Renders a TIME value as a SQL literal, or empty where this dialect declines it (see dateLiteral(java.time.LocalDate)).
Optional<String>	timestampLiteral(Instant value)	Renders a TIMESTAMP (an absolute instant) as a SQL literal.
static Dialect	valueOf(String name)	Returns the enum constant of this class with the specified name.
static Dialect[]	values()	Returns an array containing the constants of this enum class, in the order they are declared.
String	windowOverClause(List<String> partitionCols, List<String> orderByExprs, WindowFrame frame)	Renders the OVER (…) clause for a SQL window function, given already- rendered partition and sort expressions.

Methods inherited from class java.lang.Enum

clone, compareTo, describeConstable, equals, finalize, getDeclaringClass, hashCode, name, ordinal, toString, valueOf

Methods inherited from class java.lang.Object

getClass, notify, notifyAll, wait, wait, wait

Enum Constant Details

GENERIC

public static final Dialect GENERIC

Unquoted identifiers and LIMIT n [OFFSET m] — the default. A name that is not a plain identifier is double-quoted, as standard SQL delimits one.

POSTGRES

public static final Dialect POSTGRES

PostgreSQL: double-quoted identifiers.

MYSQL

public static final Dialect MYSQL

MySQL / MariaDB: back-tick-quoted identifiers.

DUCKDB

public static final Dialect DUCKDB

DuckDB: double-quoted identifiers, and SQL deliberately shaped like PostgreSQL's — NULLS LAST, date_trunc, LATERAL, window functions. Where it differs from PostgreSQL it differs in the engine's favour: its default collation is binary, so it compares and orders strings exactly as the engine does.

SQLITE

public static final Dialect SQLITE

SQLite: double-quoted identifiers and a binary default collation, so it compares and orders strings as the engine does. What sets it apart is what it lacks — date and time types, EXTRACT, LATERAL, a case-sensitive LIKE — and each of those is declined or respelled here rather than inherited from GENERIC, which is what a SQLite connection resolved to before it had a constant of its own.

SQLSERVER

public static final Dialect SQLSERVER

Microsoft SQL Server: bracket-quoted identifiers, and the first backend here whose differences are structural rather than spellings. It has no LIMIT — its OFFSET … FETCH is legal only after an ORDER BY, so a limit folds only over a sort (limitNeedsOrderBy()) — no NULLS LAST, no boolean values, no LATERAL (its APPLY is the same thing spelled otherwise), no session time zone, and a case-insensitive default collation.

DB2

public static final Dialect DB2

IBM Db2 for Linux, UNIX and Windows, 11.5 and later. Unlike every other named dialect it leaves a plain identifier bare: Db2 folds an unquoted name to upper case, so a table created as orders is stored as ORDERS and the quoted "orders" would name a different, absent one. A name that is not a plain identifier is double-quoted exactly as written, as the generic dialect does.

A Db2 database created in UTF-8, the default, compares strings with the IDENTITY collation — by their bytes, which is code-point order — so it answers both string questions as the engine does. A database created with a locale collation does not, and says so with collation: database.

Method Details

values

public static Dialect[] values()

Returns an array containing the constants of this enum class, in the order they are declared.

Returns:

an array containing the constants of this enum class, in the order they are declared

valueOf

public static Dialect valueOf(String name)

Returns the enum constant of this class with the specified name. The string must match exactly an identifier used to declare an enum constant in this class. (Extraneous whitespace characters are not permitted.)

Parameters:

name - the name of the enum constant to be returned.

Returns:

the enum constant with the specified name

Throws:

IllegalArgumentException - if this enum class has no constant with the specified name

NullPointerException - if the argument is null

quote

public String quote(String identifier)

Quotes an identifier for this dialect, doubling any embedded quote character. The generic dialect returns a plain identifier ([A-Za-z_][A-Za-z0-9_]*) unchanged and double-quotes anything else.

Generic leaves a plain identifier bare because it does not know how the backend folds case, and a bare name lets the backend decide. A name like order-lines is different: no backend reads it bare, and a table or column with that name can only have been created delimited, which also preserved its case, so the delimited form written exactly as named is the one that finds it.

One of the two methods here that does not switch on the dialect, and it needs no arm for the same reason a switch would give one: the quoting is given by constructor arguments, so a new constant states its own answer where it is declared. Doubling the closing character is the rule every quoting backend follows, bracket-quoting included.

Parameters:

identifier - the column or alias name; must not be null

Returns:

the identifier as this dialect writes it

table

public String table(String table)

Writes a table name for this dialect, quoting each dot-separated part with quote(java.lang.String): public.orders becomes "public"."orders" on PostgreSQL. A dot therefore always separates a schema from a table.

A FROM clause, pushed or not, writes its table through this, so that the engine's own scan of a table and a query pushed to the same table name the same thing.

Parameters:

table - the table name as declared, possibly schema-qualified; must not be null

Returns:

the table name as this dialect writes it

stringLiteral

public String stringLiteral(String value)

Writes value as a SQL string literal for this dialect.

Doubling the embedded quote is standard SQL and is the whole of the rule for PostgreSQL and the generic dialect, both of which read a backslash as the character it is (standard_conforming_strings).

MySQL and MariaDB also read a backslash as an escape, unless the session sets NO_BACKSLASH_ESCAPES, which is not the default. Doubling only the quote therefore lets a value end its own literal: x\ followed by ' OR 1=1 -- renders as 'x\'' OR 1=1 -- ', where the value's backslash escapes the quote that was doubled to contain the apostrophe, the literal closes at the second quote instead of the first, and everything after it is parsed as SQL. Escaping the backslash too is what closes that.

The two rules are not interchangeable in either direction: doubling a backslash for PostgreSQL would put a second one into the value, and not doubling it for MySQL is the hole above. That is what makes this a per-backend question rather than one answer with an exception, and why it is an exhaustive switch here rather than a private helper in the renderer — where it was, and where a new backend could inherit an answer nobody had considered for it.

This is the escaping rule, not a substitute for one. A literal is inlined into the statement text rather than bound as a parameter, so the rule has to be right for the backend the text is going to.

Parameters:

value - the string the literal denotes; must not be null

Returns:

the literal, quotes included; never null

limit

public String limit(long count,
 long offset)

Renders the row-limit clause: LIMIT count, plus OFFSET offset when offset is positive.

Every constant renders that form, which H2, PostgreSQL and MySQL all accept. It is written as a switch rather than as one return because the clause is not universal — see the class documentation on OFFSET … FETCH — and a constant that cannot spell LIMIT should not be able to inherit it.

SQL Server has no LIMIT: it renders OFFSET m ROWS FETCH NEXT n ROWS ONLY, which it accepts only after an ORDER BY — see limitNeedsOrderBy(), which is what stops the planner asking for one without.

Parameters:

count - the maximum number of rows

offset - the number of leading rows to skip (0 = none)

Returns:

the SQL clause, without a leading space

limitNeedsOrderBy

public boolean limitNeedsOrderBy()

Whether this dialect's row-limiting clause is legal only after an ORDER BY, so that a limit over an unordered sub-tree must not fold.

OFFSET … FETCH is the standard's row limiting and requires an ordering on SQL Server, as it does on Oracle. The ORDER BY (SELECT NULL) idiom would make it legal, and would also make it the one fold here whose rows are chosen by the database's plan rather than by anything the query said — so the limit runs in the engine instead, over the rows the rest of the statement returns.

Returns:

true when a limit folds only onto a statement that has an ORDER BY

orderByTerms

public List<String> orderByTerms(String expr,
 boolean descending)

Renders one ORDER BY key so the database places NULLs where the engine does — last, in both directions.

This is not a preference either: an in-engine τ and a pushed one are the same operator, so a query that sorts differently depending on whether the planner folded it is wrong however the rows come out. SQL's own default is not one answer to copy — H2 and MySQL put NULLs first on ASC, Postgres puts them last — so the placement is always stated explicitly rather than inherited.

Postgres gets the standard NULLS LAST. Everything else gets the portable form, an extra leading key on the NULL-ness itself: false sorts before true, so ascending that expression puts the present values first whichever way the real key runs. MySQL has no NULLS LAST syntax at all, and GENERIC is an unidentified backend that may be MySQL-shaped, so neither can be given it. SQLite has had NULLS LAST only since 3.30, and the driver — which is the database, for an embedded backend — is the user's to choose, so it gets the portable form too.

SQL Server has neither NULLS LAST nor a boolean value to sort on, so the portable form is itself a syntax error there. Its leading key is the NULL test spelled as a number: CASE WHEN e IS NULL THEN 1 ELSE 0 END.

Parameters:

expr - the rendered key expression (already quoted)

descending - whether the key itself sorts descending

Returns:

the ORDER BY terms for this key, in order; never empty

like

public Optional<String> like(String subject,
 String pattern,
 Optional<String> literal,
 boolean negated)

Renders subject LIKE pattern (or NOT LIKE) so that it matches what the engine's LIKE matches, or empty where this dialect cannot say it.

The engine's LIKE is exact: % and _ are its only wildcards, and every other character — case included — matches only itself. That is standard SQL, and most backends need nothing more than the subject rendered in comparison position, which the caller has already done.

SQLITE's LIKE is case-insensitive for ASCII letters, and no collation changes that: 'a' LIKE 'A' is true there whatever the column says. Its GLOB is the case-sensitive matcher, with * and ? for wildcards, so a literal pattern is translated into one — its own *, ? and [ bracketed so that they match themselves. A pattern that is not a literal cannot be translated here and declines.

SQLSERVER's LIKE honours the collation, which the caller has already made exact, but also reads [ as opening a character class, so a literal pattern has each one written [[] and a computed one declines.

Parameters:

subject - the rendered expression being matched; must not be null

pattern - the rendered pattern, used where the dialect reads LIKE as the engine does; must not be null

literal - the pattern's text when it is a string literal, else empty

negated - whether this is NOT LIKE

Returns:

the predicate, parenthesised; or empty when this dialect declines it

booleanLiteral

public String booleanLiteral(boolean value)

Writes a boolean value as a SQL literal.

TRUE and FALSE everywhere but SQL Server, which has no boolean type — its BIT holds 1 and 0, and TRUE is read as a column name.

Parameters:

value - the value

Returns:

the literal

boolAnd

public Optional<String> boolAnd(String predicateSql)

Returns the SQL boolean-aggregate expression for universal quantification (∀) over the given rendered predicate, or empty when this dialect has no supported spelling and the operator must run in-engine instead.

The returned expression is placed in a HAVING clause immediately after GROUP BY. Every dialect renders the same strict form, COUNT(*) = COUNT(CASE WHEN P THEN 1 END): a row whose predicate is UNKNOWN disqualifies its group.

That is not a dialect quirk but the reading ∀ has everywhere else — it is a conjunction across the group's rows, and TRUE ∧ UNKNOWN is UNKNOWN, which keeps nothing. Postgres used to render bool_and(P) here, which is lenient: like every aggregate it skips NULL inputs, so a group whose only interesting row is UNKNOWN comes back TRUE and is kept — the same query answering differently on Postgres than in-engine or on any other backend. The shorter spelling is not worth a third answer to the same question.

No dialect declines, and this is therefore the one answering method here that does not switch on the dialect: the form is the standard's rather than the backend's, so there is nothing for a new constant to decide. The Optional return survives because declining stays expressible if a backend ever needs to.

That the strict form is the one every backend gets is also what makes it available everywhere: COUNT, CASE and a comparison in HAVING are SQL-92, so there is no dialect this has to be withheld from. MySQL was withheld from it while the Postgres arm still said bool_and — an aggregate MySQL genuinely lacks — and stayed withheld after the arm changed, because no test rendered a MySQL ∀ and no MySQL ran one.

Parameters:

predicateSql - the predicate rendered as a SQL expression; must not be null

Returns:

the aggregate expression for HAVING; never empty

dateLiteral

public Optional<String> dateLiteral(LocalDate value)

Renders a DATE value as a SQL literal, or empty where this dialect has no literal that means what the engine means: GENERIC a quoted ISO string (relies on implicit cast), POSTGRES/MYSQL/DUCKDB a typed DATE '…' literal.

SQLITE declines all four temporal literals. SQLite has no date or time types: a "date" column holds TEXT, a REAL or an INTEGER by the application's convention, and a comparison against it is a comparison of text or of numbers. A quoted ISO string agrees with the engine only while every stored value is written in exactly that form, which nothing the planner can reach reports — so a temporal predicate runs in the engine over the values the connector read, where it means what it says whatever the convention.

Parameters:

value - the date; must not be null

Returns:

the literal, or empty when this dialect declines it

timeLiteral

public Optional<String> timeLiteral(LocalTime value)

Renders a TIME value as a SQL literal, or empty where this dialect declines it (see dateLiteral(java.time.LocalDate)).

Parameters:

value - the time; must not be null

Returns:

the literal, or empty when this dialect declines it

timestampLiteral

public Optional<String> timestampLiteral(Instant value)

Renders a TIMESTAMP (an absolute instant) as a SQL literal. A relix TIMESTAMP is UTC: GENERIC emits the UTC wall-clock as a quoted string (matching how a zone-less SQL TIMESTAMP column is read); POSTGRES emits an offset-aware TIMESTAMP WITH TIME ZONE '…Z'; MYSQL emits a TIMESTAMP '…' of the UTC wall-clock.

DUCKDB takes PostgreSQL's form. Against a zone-less TIMESTAMP column DuckDB converts the literal to the session's wall clock, which relix pins to UTC (pinSessionToUtcSql()), so it compares the instant the engine reads the column as; against a TIMESTAMPTZ column it compares instants directly. SQLITE declines (see dateLiteral(java.time.LocalDate)).

SQLSERVER gets a DATETIME2 of the UTC wall clock: a DATETIME2 column holds the wall clock the engine reads as UTC, and against a DATETIMEOFFSET column the literal is converted at offset +00:00, so it names the same instant either way — and the column, not the literal, keeps its type, so an index on it still serves the comparison.

Parameters:

value - the instant; must not be null

Returns:

the literal, or empty when this dialect declines it

durationLiteral

public Optional<String> durationLiteral(Duration value)

Renders a DURATION value as a SQL INTERVAL literal, or empty where this dialect has no spelling for one.

PostgreSQL accepts an ISO-8601 interval string directly, so a duration renders as it prints: INTERVAL 'PT30M'. MYSQL and GENERIC decline, because their INTERVAL syntax names a unit (INTERVAL 30 MINUTE) and a duration does not carry one — ninety minutes is as truly 90 MINUTE as 1.5 HOUR, and picking for the user is how a literal starts meaning something the engine did not say.

DuckDB rejects the ISO-8601 form (INTERVAL 'PT30M' is a conversion error there) but builds an interval from an exact count of microseconds, which names no calendar unit and so picks none for the user: to_microseconds(n). A duration finer than a microsecond has no such count and declines.

Alone among the four temporal literals this returns an Optional, because declining is a real answer for it: no fold is a slower plan, where a wrong literal is a wrong result.

Parameters:

value - the duration; must not be null

Returns:

the INTERVAL literal, or empty when this dialect has no spelling

comparesStringsExactly

public boolean comparesStringsExactly()

Whether this backend compares two strings the way the engine does — exactly, character for character.

It is not a question about SQL but about the column's collation, and a collation may say that two values differing in case or in accent are the same value. MySQL's default is utf8mb4_0900_ai_ci, which says exactly that, so a folded =, IN, LIKE, GROUP BY, DISTINCT, ORDER BY or MIN/MAX over a string answers a different question there than the same operator answers in the engine — the query returning different rows depending on where the planner put the work.

POSTGRES and GENERIC answer true, and for Postgres that answer was checked against a server rather than reasoned about: a deterministic collation falls back to comparing bytes when it is asked whether two strings are equal, and Postgres's defaults are deterministic, so 'Gold' = 'gold' is false there as it is here. DUCKDB's default collation is binary, so it answers true for the plainer reason. SQLSERVER's default, SQL_Latin1_General_CP1_CI_AS, is case-insensitive, so it answers false as MySQL does. DB2 answers true — a UTF-8 database's default IDENTITY collation compares bytes — with SQL Server's one caveat: it compares padded, so 'a' = 'a ' holds there too, and no collation of its own changes that.

This is a claim about equality alone, and the separation is the whole reason there are two methods: the same Postgres that compares equal strings exactly does not order them as the engine does, because ordering is what a locale collation is for. See ordersStringsExactly(), which is the question <, ORDER BY and MIN/MAX ask.

The answer is a default, not a fact, because the truth is per column and the planner has no channel to a column's collation. A connection whose string columns really are binary says so with collation: exact, and a Postgres deployment that wants the conservative reading says collation: database — see comparesStringsExactly(ConnectionDeclaration).

Of every question on this enum this is the one that must never be inherited. A wrong true does not cost a fold, it folds a comparison the backend answers differently — so the query returns different rows depending on where the planner put the work, which is a wrong result rather than a slow one.

Returns:

true when a string equality test may be handed to this backend

ordersStringsExactly

public boolean ordersStringsExactly()

Whether this backend puts two strings in the order the engine puts them — by code point, which is the order a binary collation gives and the order UTF-8 bytes already sort in.

It is a different question from comparesStringsExactly(), and Postgres is the backend that separates them. A deterministic collation decides equality by bytes, so = is exact there; it decides order by the locale, and a locale sorts the way a dictionary does — case-insensitively at the primary level, punctuation weighed differently, 'Straße' filed beside 'Strasse'. Under en_US.UTF-8, which is what a default install initialises with, 'Alan' < 'a😀b' < 'grace'; by code point the emoji sorts after both. So a τ on a string key, a < between two strings and a MIN over one all answer differently there than here.

MySQL answers false for the reason it answers false to the equality question — its default collation is neither exact nor code-point ordered — so on that backend the two questions have never disagreed, which is why one boolean was enough until a Postgres was run.

DuckDB answers true to both questions: its default collation compares UTF-8 bytes, which is code-point order. That was run rather than read — over the agreement fixture's U+FF5E/emoji pair, the one that tells code-point order from UTF-16 order. SQLite's BINARY collation is memcmp over UTF-8, and answers the same, measured over the same pair; so does a UTF-8 Db2 database under its default IDENTITY collation.

A false answer does not decline the fold: it renders the ordering under an explicit exact collation, exactStringOrder(java.lang.String).

Returns:

true when a string ordering may be handed to this backend as written

exactStringComparison

public String exactStringComparison(String expression)

Wraps a rendered string expression so this backend compares it exactly, or returns it unchanged where the backend already does.

MySQL's answer is CONVERT(e USING utf8mb4) COLLATE utf8mb4_0900_bin, and both halves are load-bearing. The COLLATE is what replaces the column's own case- and accent-insensitive collation with an exact one. The CONVERT is what makes that legal for any column: a collation belongs to a character set, so COLLATE utf8mb4_0900_bin applied to a latin1 column is not a wrong answer but a rejected query, and transcoding first removes the question. Both were checked against a server rather than reasoned about, accented values included.

It costs the column's index for this comparison, and that is the right trade by a wide margin: the alternative is not an indexed lookup but no fold at all, so the comparison is between a scan inside the database and every row of the table crossing the wire to be scanned here.

This is the wrapping for equality — =, IN, LIKE, GROUP BY, DISTINCT, a join condition. Ordering is wrapped separately by exactStringOrder(java.lang.String), and separately because the two are not the same set of backends: Postgres needs the second and not the first.

SQL Server's is (e) COLLATE Latin1_General_100_BIN2. A binary collation makes 'Gold' = 'gold' false, and it is still not all the way to exact: SQL Server compares strings padded, as the standard's PAD SPACE says, so 'a' = 'a ' is true there under every collation it has. relix compares them as different values. That difference is recorded rather than worked round — no collation removes it, and declining every string comparison to avoid values that differ only in trailing spaces would cost every such query a full read.

Parameters:

expression - an already-rendered expression of string type; must not be null

Returns:

the expression, wrapped if this dialect needs it to compare exactly

exactStringOrder

public String exactStringOrder(String expression)

Wraps a rendered string expression so this backend orders it by code point, or returns it unchanged where the backend already does.

Postgres spells that (e) COLLATE "C" — the C collation compares the UTF-8 bytes, which for UTF-8 is code-point order — and it is applied in ordering position only: a <, an ORDER BY key, a MIN or MAX. Its equality is already exact, so wrapping an = there would cost the column's index and buy nothing.

MySQL reuses its comparison wrapping, which is already binary and therefore already code-point ordered. That the two coincide on one backend and not on the other is exactly why they are two methods.

SQL Server reuses its comparison wrapping too, and it orders by UTF-16 code unit rather than by code point — the same difference H2 has as the generic dialect, confined to a character outside the basic multilingual plane compared with one in U+E000..U+FFFF. A code-point order exists there only through a VARCHAR under a _UTF8 collation, which would change what a MIN returns and what a < against a literal compares.

Like the comparison wrapping, this costs the column's index for the term it wraps, and the alternative is not an indexed sort but no fold at all — every row crossing the wire to be sorted here.

Parameters:

expression - an already-rendered expression of string type; must not be null

Returns:

the expression, wrapped if this dialect needs it to order by code point

comparesStringsExactly

public static boolean comparesStringsExactly(ConnectionDeclaration connection)

Whether connection's backend compares strings as the engine does: its declared collation when it has one, else its dialect's default.

collation: exact is how a MySQL whose string columns are declared with a binary collation gets its string predicates folded again. It is the user's claim rather than the engine's, because a collation is a property of each column and nothing the planner can reach reports it — which is also why the safe answer is the default and the fast one is opted into.

Parameters:

connection - the connection declaration; must not be null

Returns:

whether a string comparison may be folded into this connection's SQL

ordersStringsExactly

public static boolean ordersStringsExactly(ConnectionDeclaration connection)

Whether connection's backend orders strings as the engine does: its declared collation when it has one, else its dialect's default.

The declaration answers both questions at once, and that is right rather than a shortcut: collation: exact is the claim that these columns are declared with a binary collation, and a binary collation is both exact and code-point ordered.

Parameters:

connection - the connection declaration; must not be null

Returns:

whether a string ordering may be folded into this connection's SQL as written

pinSessionToUtcSql

public Optional<String> pinSessionToUtcSql()

The statement that pins a session's time zone to UTC, or empty for a backend this has not been confirmed against.

Why a session needs pinning at all. A relix TIMESTAMP is an instant, and a zone-less SQL TIMESTAMP is read as UTC. A pushed date_trunc or EXTRACT runs on the server, over whatever wall clock the session presents — so unless that clock is UTC, the folded query and the in-engine one truncate different numbers and the same query answers differently depending on where the planner put the work.

Why it is not left to the connection string. On PostgreSQL it cannot be: the driver sends the session's TimeZone in its startup packet, taken from the client JVM's default zone, and a TimeZone named in the URL is silently ignored. So a Postgres deployment's answers moved with the time zone of the machine the engine happened to run on, and there was no property a user could set to stop it. That is the shape of bug this exists to remove.

GENERIC answers empty, and that is deliberate rather than an omission: an unidentified backend is one whose spelling of this is unknown, and a rejected statement would break the connection outright — a much worse failure than the wall clock it would have corrected.

Returns:

the SQL to run once on a new connection, or empty to leave the session alone

pushdownTarget

public PushdownTarget pushdownTarget()

Names this dialect to a function library, so a function can supply its own spelling here without the planner knowing anything about it.

A PushdownTarget is deliberately weaker than a Dialect: it is a family and a variant string, and it carries no quoting, no limit syntax and no knowledge of query planning. That is what lets a spelling live in a library that has never heard of this module — the engine keeps every structural decision and hands over arguments already rendered.

Returns:

the target a function's spelling is asked for

supportsWindowFunctions

public boolean supportsWindowFunctions()

Returns whether this dialect's target database reliably supports SQL window functions (OVER (PARTITION BY … ORDER BY … ROWS …)).

• GENERIC (H2 2.x), POSTGRES and DUCKDB: true.
• SQLITE: true — window functions arrived in SQLite 3.25 (2018), and the corpus's window cases run against it in the gate.
• SQLSERVER: true — ROWS BETWEEN frames arrived in SQL Server 2012, which predates every release still in support.
• MYSQL: false — MySQL 5.x predates window-function support; since the declared dialect cannot confirm the server version, window operators fall back to in-engine execution for safety.

Returns:

true when the dialect can execute a pushed OVER clause

supportsLateralAsOf

public boolean supportsLateralAsOf()

Returns whether this dialect's target database reliably supports a LATERAL derived table in a join — the construct used to push an AS-OF join down as a correlated "nearest row" lookup (LEFT JOIN LATERAL (SELECT … ORDER BY … LIMIT 1) ON TRUE).

• POSTGRES: true — LATERAL has been supported since PostgreSQL 9.3 and is the canonical AS-OF spelling.
• DUCKDB: true — it spells LATERAL as PostgreSQL does, and the AS-OF corpus runs against it in the gate.
• GENERIC: false — the generic target is H2, whose 2.x releases have no LATERAL support, so AS-OF runs in-engine.
• SQLITE: false — SQLite has no LATERAL.
• SQLSERVER: true — it has no LATERAL either, but OUTER APPLY/CROSS APPLY is the same correlated join, and nearestRowJoin(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, boolean) spells it.
• DB2: true — it takes PostgreSQL's spelling unchanged.
• MYSQL: false — LATERAL arrived only in MySQL 8.0.14, and the declared dialect cannot confirm the server version, so AS-OF falls back to in-engine execution for safety (as window functions do).

Answered by whether nearestRowJoin(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, boolean) has a spelling, which is the exhaustive switch: there is one list of the backends that fold an AS-OF, not two.

Returns:

true when the dialect can execute a pushed nearest-row AS-OF

nearestRowJoin

public Optional<String> nearestRowJoin(String left,
 String columns,
 String source,
 String orderBy,
 String alias,
 boolean inner)

Renders the FROM clause of an AS-OF join folded as a correlated nearest-row lookup — for each left row, the one right row a sub-select filters by the match condition and orders by the match column — or empty where this dialect has no such construct (supportsLateralAsOf()).

PostgreSQL and DuckDB spell it LEFT JOIN LATERAL (… LIMIT 1) r ON TRUE, or JOIN LATERAL to drop an unmatched left row. SQL Server has the same construct under another name — OUTER APPLY (SELECT TOP 1 …) r, or CROSS APPLY — with no ON and no LIMIT; both of its halves were checked against a running server.

Parameters:

left - the left table and its alias, as they appear in FROM

columns - the sub-select's select list

source - the sub-select's FROM … WHERE …

orderBy - the sub-select's ordering, nearest row first

alias - the quoted alias the sub-select's columns are read through

inner - whether a left row with no match is dropped rather than NULL-padded

Returns:

the FROM clause, or empty when this dialect cannot fold the join

windowOverClause

public String windowOverClause(List<String> partitionCols,
 List<String> orderByExprs,
 WindowFrame frame)

Renders the OVER (…) clause for a SQL window function, given already- rendered partition and sort expressions.

The ANSI SQL:2003 syntax produced here is identical across GENERIC, POSTGRES, and MYSQL (8.0+): OVER (PARTITION BY k1, k2 ORDER BY col ASC ROWS BETWEEN n PRECEDING AND CURRENT ROW). Only call this method after verifying supportsWindowFunctions().

Parameters:

partitionCols - already-quoted partition-key column expressions; may be empty

orderByExprs - already-rendered col [ASC|DESC] expressions; must not be empty

frame - the row scope (WindowFrame.BoundedFrame, WindowFrame.CumulativeFrame, or WindowFrame.PartitionFrame); never null

Returns:

the complete OVER (…) string, starting with a space

of

public static Dialect of(ConnectionDeclaration connection)

Resolves the dialect for a connection: its declared dialect, if any, otherwise inferred from the JDBC URL, otherwise GENERIC.

Reads the two properties directly rather than through ConnectionDeclaration.config(), which requires a url and raises when there is none. A connection may legitimately have no URL — one whose coordinates are a live handle supplied by an embedder, or one that is not JDBC at all — and the right answer for it is GENERIC, not an exception. Getting a dialect wrong costs pushdown; raising here would cost the query.

Parameters:

connection - the connection declaration; must not be null

Returns:

the resolved dialect; never null