This page describes the SQL that JParq supports and how it behaves in detail. For an overview, see the README. For Parquet-specific behavior such as predicate pushdown, see Parquet files and Hugging Face datasets.
JParq follows the SQL standard (SQL:2016 and earlier) for read queries. Where it deviates, the deviation is a bug and is listed under known deviations and open issues.
- Supported statements and clauses
- Functions
- Name resolution
- Subqueries
- Grouping and aggregation
- CTEs, derived tables, LATERAL, and UNNEST
- Set operations
- Data types
- Literals and parameters
- JDBC result values
- Statement cancellation
- Errors and SQLStates
- Known deviations and open issues
*and qualified wildcards (table.*)- Column and table aliases, including computed expressions with aliases (
SELECT mpg * 2 AS double_mpg) - Quoted identifiers using "double quotes", as the SQL standard specifies
DISTINCTCASEexpressions; see CASE- Line (
--) and block (/* */) comments
Omitted FROM. Literal and computed expressions can omit FROM, including recursive CTE anchors. The implicit
input is one row; WHERE and row limits still apply.
- Column references and wildcards (
*andtable.*) are rejected whenFROMis omitted. - Correlated scalar and
EXISTSsubqueries can omitFROM. LATERALinputs that reference outer columns still require an explicit input, such asLATERAL (SELECT e.id * 2 AS d FROM (VALUES (1)) v(z)) x.
BETWEEN,IN,LIKE, andSIMILAR TOAND,OR, andNOT- Comparison operators
=,!=,<>,<,>,<=,>= IS NULLandIS NOT NULLEXISTS,IN, and quantified comparisons (ANY,SOME,ALL) over subqueries and arrays; see quantified comparisons
Expressions skipped by filtering or CASE branch selection are not evaluated.
Multiple sort keys with ASC/DESC and optional NULLS FIRST/NULLS LAST. A bare output name resolves
against the SELECT list first. Inside an expression, input columns take precedence, including when a SELECT
alias has the same name. In grouped queries, an input-column operand outside an aggregate must be
grouped; otherwise ORDER BY fails with SQLState 42803, even if an output alias shares that name.
As an extension, an otherwise unresolved name may refer to a deterministic SELECT
alias, including grouped aggregates (for example,
SELECT cyl, SUM(mpg) AS s FROM mtcars GROUP BY cyl ORDER BY s * 2).
Expression references to aliases containing window functions, RAND/RANDOM, or subqueries are rejected with
SQLState 42000; use a derived table or CTE to expose their computed values as input columns. See
column labels and ambiguity for how output names are resolved.
- Standard
OFFSET ... FETCH FIRST/NEXT ... ROWS ONLY LIMITandOFFSET, including the PostgreSQL shorthandLIMIT n OFFSET m- MySQL-style
LIMIT offset, countis rejected with SQLState0A000; useOFFSETandLIMIT.
Row counts accept non-negative integer literals and bound JDBC parameters up to 2147483647.
| Row count | Result |
|---|---|
Integral numeric parameters such as 2.0 and BigDecimal("2.00") |
Accepted |
Character parameters such as "2" or '2' |
Accepted |
| Invalid numeric strings | SQLState 22018 |
| Other non-numeric parameters | 2201X for OFFSET, 2201W for LIMIT/FETCH |
Values above 2147483647 |
22003 |
| Negative counts, and NULL, fractional, or non-finite numeric parameters | SQLDataException: 2201X for OFFSET, 2201W for LIMIT/FETCH |
Other expressions (bitwise NOT, arithmetic, subqueries, literal NULL) |
42000 |
INNER, LEFT, RIGHT, FULL, CROSS, and self joins, with ON or USING. ON conditions can reference only
columns from inputs joined so far.
JOIN ... USING (x)
- Unqualified
xandSELECT *expose one merged key. - Qualified references such as
a.xandb.x, includinga.*andb.*, keep their input values and return NULL when that input is absent from an outer-join row. - Later joins using
xmatch against the accumulated merged key. - Grouping by merged
xdoes not group the separate input keysa.xorb.x; select those only when they are also grouped or aggregated. SELECT x, a.x ... ORDER BY xhas an ambiguous output label; use distinct aliases or a positional reference.
Parenthesized join groups
- Nested join order is preserved, including outer-join NULL extension.
- Unaliased groups expose their inputs' qualifiers. A group alias exposes the group's output columns instead.
- For aliased inputs, groups expose only the aliases, not the real table names.
- Qualified wildcards such as
g.*currently require distinct output labels for any aliased join input, including groups, derived tables, and CTEs. Duplicate labels raise SQLState42000; give the input unique positional column aliases, such asg(x, y), to expand them. - See predicate pushdown for performance notes.
Subqueries are supported in the SELECT, FROM, WHERE, and HAVING clauses:
-- SELECT clause: returns a single value
SELECT first_name,
(SELECT department_name FROM departments
WHERE departments.department_id = employees.department_id) AS department_name
FROM employees;
-- FROM clause: a derived table
SELECT *
FROM (SELECT first_name, salary FROM employees WHERE salary > 5000) AS "high_salaried";
-- WHERE clause: filters rows
SELECT first_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id > 1500);
-- HAVING clause: filters groups
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);Derived tables can contain UNION, UNION ALL, INTERSECT, and EXCEPT (including ALL variants), nested set
operations, and LATERAL inputs. Inner ordering and row bounds apply before the consuming query. Table-free
SELECT and set-operation derived tables may omit a table alias in joins; reference their output columns by
unqualified name when unambiguous. Multiple VALUES inputs still require aliases.
See Subqueries for scoping and evaluation rules.
- Aggregates,
COUNT(*), andCASEexpressions inGROUP BYandSELECT HAVINGwith conditions, subqueries, and quantified comparisonsGROUPING SETS,ROLLUP, andCUBE
UNION, UNION ALL, INTERSECT, INTERSECT ALL, EXCEPT, EXCEPT ALL, and nested set operations. See
Set operations.
WITH and WITH RECURSIVE. See CTEs, derived tables, LATERAL, and UNNEST.
| Kind | Functions |
|---|---|
| Ranking | ROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE |
| Aggregate | SUM, AVG, MIN, MAX, COUNT |
| Navigation | LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE |
- Unsupported function names used with
OVERinSELECTlists raiseSQLSyntaxErrorExceptionwith SQLState42000, naming the function. Validation outsideSELECTlists is tracked in #350. WITHIN GROUPclauses inSELECTlists are unsupported, with or withoutOVER, and raise SQLState42000(#473).- Window functions in grouped queries are rejected during preparation with SQLState
42000; separate inner queries keep their own window function scope.
- Parquet tables, with optional schema qualification
- Derived tables,
LATERALderived tables,UNNEST(with or without a table wrapper), andVALUEStable constructors TABLESAMPLE ... REPEATABLE (...); see predicate pushdownINFORMATION_SCHEMA.TABLESandINFORMATION_SCHEMA.COLUMNS
| Category | Functions |
|---|---|
| Aggregate | COUNT, SUM, AVG, MIN, MAX, STRING_AGG |
| Numeric | ABS, CEIL/CEILING, FLOOR, ROUND, SQRT, TRUNCATE/TRUNC, MOD, POWER/POW, EXP, LOG, LOG10, RAND/RANDOM, SIGN, SIN, COS, TAN, COT, ASIN, ACOS, ATAN, ATAN2, DEGREES, RADIANS, PI |
| Conditional | CASE, COALESCE, NULLIF |
| Conversion | CAST, CONVERT |
| Date and time | CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP, EXTRACT, TIMESTAMPADD, TIMESTAMPDIFF |
| Constructors | ARRAY, JSON_OBJECT, JSON_ARRAY |
| JSON | JSON_VALUE, JSON_QUERY |
| String | See string functions |
Unknown or unsupported scalar functions and expression types raise SQLSyntaxErrorException with SQLState 42000
when evaluated, instead of returning their SQL text as a value. Function names are not checked against an allow
list during preparation, so the error can occur during execution, materialization, or a result set getter.
COALESCE(a, b, ...)returns the first non-null argument.NULLIF(a, b)returns NULL when its arguments compare equal, otherwise the first argument. This includes the case where the comparison is UNKNOWN because an argument is NULL.
CONVERT(... USING charset)performs character set conversion, as in the SQL standard.- Without
USING, cast-styleCONVERTperforms data type conversion (a common extension) and sharesCAST's declaration checks. Use target-first syntax for NULL inputs; see #465. - See Data types for cast rules per type.
Both accept all nine JDBC SQL_TSI_* interval constants, as native calls or {fn ...} escapes, with or without
FROM. SQL_TSI_FRAC_SECOND counts nanoseconds; the other units range from seconds through years.
|| concatenates strings and binary values, with NULL propagation.
| Function | Description | Example |
|---|---|---|
CHAR_LENGTH(s), CHARACTER_LENGTH(s) |
Number of characters | CHAR_LENGTH('hello') → 5 |
OCTET_LENGTH(s) |
Number of bytes (UTF-8 for text) | OCTET_LENGTH('Å') → 2 |
POSITION(sub IN s) |
1-based position of sub in s |
POSITION('l' IN 'hello') → 3 |
SUBSTRING(s FROM start [FOR length]) |
Substring | SUBSTRING('abcdef' FROM 2 FOR 3) → 'bcd' |
CONCAT(s1, s2, ...) |
Concatenates two or more strings | CONCAT('a', 'b', 'c') → 'abc' |
UPPER(s), LOWER(s) |
Case conversion | UPPER('sql') → 'SQL' |
TRIM([LEADING | TRAILING | BOTH] [chars] FROM s) |
Removes characters from the ends | TRIM(BOTH 'x' FROM 'xaxx') → 'a' |
LPAD(s, length [, fill]) |
Pads on the left (SQL:2008 optional) | LPAD('42', 5, '0') → '00042' |
RPAD(s, length [, fill]) |
Pads on the right (SQL:2008 optional) | RPAD('42', 5, '0') → '42000' |
OVERLAY(s PLACING r FROM start [FOR length]) |
Replaces part of s with r |
OVERLAY('abcdef' PLACING 'xyz' FROM 3 FOR 2) → 'abxyzef' |
REPLACE(s, search, replacement) |
Replaces all occurrences (SQL:2008) | REPLACE('banana', 'na', 'xy') → 'baxyxy' |
s COLLATE collation |
Applies a collation | 'abc' COLLATE "sv_SE" |
s SIMILAR TO pattern |
SQL regular expression match | 'cab' SIMILAR TO 'c(a|o)b' → TRUE |
REGEXP_LIKE(s, pattern [, flags]) |
Regular expression match; flag 'i' is case-insensitive |
REGEXP_LIKE('AbC', 'abc', 'i') → TRUE |
NORMALIZE(s [USING form]) |
Unicode normalization (SQL:2016) | NORMALIZE('é') → 'é' |
STRING_AGG(expr, separator) |
Aggregates values into one string | STRING_AGG(name, ', ') → 'Alice, Bob, Carol' |
UNICODE(s) |
Code point of the first character | UNICODE('A') → 65 |
CHAR(code) |
Character for a code point | CHAR(65) → 'A' |
More SIMILAR TO and REGEXP_LIKE examples:
'cat' SIMILAR TO '(cat|dog)' -- TRUE
'cab' SIMILAR TO 'c(a|e)b' -- FALSE
'abc' SIMILAR TO 'a%' -- TRUE
REGEXP_LIKE('abc123', '^[a-z]+[0-9]+$') -- TRUE
REGEXP_LIKE('cat', 'dog|cat') -- TRUESTRING_AGG ... WITHIN GROUP (ORDER BY ...) is unsupported and raises SQLState 42000
(#473).
LENGTH is an alias of the character-only CHAR_LENGTH. For binary operands, see
character and binary types.
Non-standard extensions:
| Function | Description | Example |
|---|---|---|
LEFT(s, n) |
Leftmost characters | LEFT('abcdef', 3) → 'abc' |
RIGHT(s, n) |
Rightmost characters | RIGHT('abcdef', 2) → 'ef' |
LTRIM(s) |
Trims leading spaces | LTRIM(' hi') → 'hi' |
RTRIM(s) |
Trims trailing spaces | RTRIM('hi ') → 'hi' |
ASCII(s) |
ASCII code of the first character | ASCII('A') → 65 |
LOCATE(sub, s [, start]) |
Position of sub, from an optional start |
LOCATE('c', 'abcabc', 3) → 3 |
REPEAT(s, n) |
Repeats a string | REPEAT('a', 4) → 'aaaa' |
SPACE(n) |
String of n spaces |
SPACE(5) → ' ' |
INSERT(s, start, length, r) |
Replaces length characters at start with r |
INSERT('abcdef', 3, 2, 'XYZ') → 'abXYZef' |
SOUNDEX(s) |
Soundex phonetic code | SOUNDEX('Robert') → 'R163' |
DIFFERENCE(s1, s2) |
Soundex similarity (0–4) | DIFFERENCE('Smith', 'Smyth') → 4 |
JSON_VALUE, JSON_QUERY, JSON_OBJECT, and JSON_ARRAY are string-returning functions standardized in
SQL:2016–2023.
Paths in JSON_VALUE and JSON_QUERY:
- Double-quoted member names are supported, including spaces, dots, brackets, and JSON string escapes such as
\",\\, and\u0061. For example,JSON_VALUE('{"a b":1}', '$."a b"')returns1. - All member steps require a leading dot: use
$.aor$."a";$a,$ a,$"a", and$.a[0]braise errors. Whitespace between steps does not replace the dot. - Leading and trailing whitespace and whitespace between path tokens are ignored. The parser uses ECMAScript
WhiteSpace and
LineTerminator characters:
Unicode space separators (including NBSP), tab, vertical tab,
form feed, LF, CR, U+2028, U+2029, and U+FEFF. Other control characters are not whitespace. For example,
$.a .b,$.a [1], and$. "a"work like their compact forms. Whitespace inside quoted names is preserved; unquoted names containing whitespace must be quoted ($."a b", not$.a b). $."a"matches the keya. To select a key containing literal quotes, escape them:$."\"a\"". (Behavior change: earlier versions matched a key containing the literal quotes.)- A dot must be followed by a member name; empty names must be quoted (
$.""). - Missing members return NULL for supported paths.
- Numeric array indexes are zero-based integers. An optional
+sign is accepted ([+1]selects the second element). Negative indexes such as[-1]always return NULL; they do not count from the end of the array. Subscripts ignore the same surrounding whitespace as other path tokens. - In the default lax behavior, array steps wrap non-array values as one-element arrays. For example,
$.a[*],$.a[0],$.a[last], and$.a[0 to 2]all select1from{"a":1}. - Array steps support
[*](all elements),[last](the final element), offsets such as[last-1], inclusive ranges such as[0 to 2]and[last-1 to last], and comma-separated selections such as[0,last]. Numeric literals of any length are accepted and parsed with saturating arithmetic; endpoints outside the array are clipped, and descending ranges have no matches. Whitespace is allowed between subscript tokens, including around the sign inlast - 1. - Subsequent steps apply to every selected item. Both functions return NULL if the complete path selects zero
or multiple items (the default SQL/JSON behavior without an array wrapper). A single selected JSON array
remains one item and is returned intact by
JSON_QUERY. - Intermediate selections are limited to 100,000 items per path step, including duplicates. From the first
nonterminal array step onward, item/range evaluations share one cumulative budget over the complete path,
including terminal steps and ranges that select nothing. The cumulative budget defaults to 100,000.
For example,
$[*][0].xon 100,000 elements exceeds the default work budget:[*]costs 1 and[0]costs 100,000. Set the JVM system property-Djparq.json.maxIntermediateItems=200000to change both limits to a positive integer. The property is read only at the first nonterminal array step and reused for the remainder of that evaluation. Paths with no intermediate array step do not consult the property. This process-wide budget covers JDBC and direct JSON function calls; it is not a connection property. Exceeding either limit raisesSQLExceptionwith SQLState54000; an invalid setting usesHY024. Both diagnostics include the property name. Each range's size is checked before its items are collected; nonterminal item/range combinations are checked against the remaining cumulative budget before iteration. A terminal array step needs at most two items to determine whether the result is a singleton and does not expand the rest of the selection. - Numeric subscript literals accept ASCII digits only. Leading-zero validation remains tracked in #496.
- Malformed quoted members and invalid subscripts raise
SQLExceptionwhen evaluated, even after a missing member. Invalid subscripts include the bracket contents in the diagnostic.
Constructors (JSON_OBJECT, JSON_ARRAY):
- Struct values are embedded as JSON objects, and SQL
ARRAYvalues (stored or constructed) as JSON arrays, recursively. NULL elements become JSONnull, and empty arrays become[]. - Strings inside arrays and maps remain JSON strings, even when their text looks like JSON. This includes strings
returned by JSON constructors inside an SQL
ARRAY:JSON_ARRAY(ARRAY[JSON_OBJECT(KEY 'a' VALUE 1)])returns[["{\"a\":1}"]]. - Direct constructor arguments keep the heuristic that embeds JSON-looking text as JSON.
- Temporal values become ISO-8601 JSON strings. TIME includes seconds; explicit SQL fractional precision
retains trailing zeros (for example,
TIME '01:02:03.120'becomes"01:02:03.120"). UTC-adjusted timestamps use UTC with aZsuffix, and local timestamps retain their wall-clock fields without a zone suffix, including literals, casts, arithmetic, subqueries, and materialized local columns. For example, a UTC-adjusted timestamp of 1,700,000,000,000 epoch milliseconds becomes"2023-11-14T22:13:20Z"in every JVM time zone. These rules also apply within arrays and structs. - Temporal combinations use their SQL result type consistently in JSON, JDBC, and materialization.
UTC-adjusted and local Parquet timestamp columns both expose JDBC
TIMESTAMP(without time zone). Combining them inCASE,COALESCE, ARRAY elements, or set operations retainsTIMESTAMP; JSON renders the common local fields without an offset. UTC source fields use their JDBC wall-clock view in such combinations, which reflects the JVM time zone. UTC-only results retain their UTC instant. An actualTIMESTAMP WITH TIME ZONEoperand produces a common offset-bearing result: local values acquire the SQL-session displacement, and UTC sources retain their instant. The same rule applies to local and offset-bearing TIME values in ARRAY constructors. Promotion precedes JSON serialization. - Raw local timestamp fields are serialized without normalizing nonexistent wall-clock times through the
host's daylight-saving rules. Local values already decoded through JDBC
Timestampby correlation, materialization,CAST, or arithmetic can still be shifted before reaching JSON; this is tracked in #446. - Struct fields use their declared Avro logical types, including numeric decimals and date/timestamp strings. This
includes maps nested within struct fields. Top-level map columns and maps inside stored arrays decode their values
with the declared Avro logical types as well (#313). Like top-level
columns and stored array elements, decoded binary values render as text unless
jparq.binaryprefers binary output, while struct fields expose raw values as base64; TIME values decode through JDBCTimewith millisecond precision, so sub-millisecond digits are truncated (#521). - Raw
BYTESfields within structs become base64 strings. Top-level columns and stored array elements pass through JDBC coercion first, soBYTESmay become UTF-8 text unlessjparq.binaryprefers binary values. For example, the bytes ofhellorender as"hello"in a column or stored array and as"aGVsbG8="in a struct field. - Non-finite floating-point values keep their string representations, such as
"NaN".
- Function names and unquoted identifiers are case-insensitive. Quoted identifiers and string literal contents keep their case.
- Quoted lowercase column references resolve to unquoted lowercase columns:
SELECT "id" + 0 FROM optimized WHERE "id" = 1. This also applies toGROUP BY, derived tables, CTEs,VALUESinputs, and computed join expressions with qualified duplicate column names. - Quoted mixed-case names require an exact case match.
- Unquoted references to explicitly quoted lowercase materialized aliases, such as
SELECT x FROM (SELECT 1 AS "x") t, are not supported yet (#338). - Lowercase CTE names can be declared or referenced with quotes, including in correlated subqueries:
WITH "c" AS (SELECT 1 AS x) SELECT x FROM c. - Equivalent CTE names such as
cand"c"in the sameWITHlist are rejected with SQLState42000. A nestedWITHshadows an outer declaration in either form, including in scalar subqueries and later CTE declarations in that nested scope. - Doubled quotes in quoted identifiers are decoded:
"a""b"has the labela"b. - Table name case sensitivity is controlled by the
caseSensitiveconnection option.
- Quoted column aliases keep their case and appear in JDBC column labels without the surrounding quotes. Derived tables and CTEs preserve these labels, including spaces.
- Unaliased expressions are labeled with their SQL text, including quoted operands such as
"id" + 1. Derived tables and CTEs preserve these labels too (for exampleid + 1). Internal Avro names do not restrict SQL output labels, and generated internal names are not SQL column aliases. - Ambiguous references to duplicate output names in projections, predicates, and
ORDER BYraise an exception with SQLState42702naming the column, even for empty inputs. Qualify join columns with a table alias to resolve them; duplicate outputs remain available by position throughSELECT *and positionalORDER BY. ORDER BYcan use a repeatedSELECTlabel when every occurrence denotes the same expression. Different expressions with the same label are ambiguous, including columns contributed by qualified wildcards.- Column equivalence uses the bound input field, so an unqualified reference and its qualified form can share a label.
- Unused duplicate output labels do not prevent ordering by another column.
- A table alias hides the original table or CTE name everywhere:
SELECT(includingalias.*),WHERE,GROUP BY,HAVING,ORDER BY,DISTINCT ON,WINDOW,QUALIFY,ONconditions, and correlated subqueries. AfterFROM employees e, writee.id. - Quoted table aliases such as
"My T"are supported, including at derived table and CTE boundaries. Quoted aliases containing dots remain single identifiers, such as"a.b".idafterFROM employees "a.b". - Schema prefixes are checked against unaliased inputs:
acme.employees.idis valid afterFROM acme.employees, whilenosuch.employees.idand schema prefixes on aliases are rejected. - Unknown or hidden relation qualifiers raise
SQLSyntaxErrorExceptionwith SQLState42000, even when the input is empty or predicates are pushed down. - Qualifiers in nested queries are validated during preparation against local and enclosing inputs, even when the outer query returns no rows or a scalar subquery result is never read.
- Column references are validated against the visible output names, even for empty inputs.
- Unknown names, and columns replaced by a column alias list, raise
SQLSyntaxErrorExceptionwith SQLState42000naming the missing column. Generated internal Avro names are not visible SQL columns. - Non-parameterized single-input queries are validated during preparation; joins are validated when their inputs are built during execution.
Column alias lists rename outputs by position:
SELECT x.a, x.b FROM (SELECT id, id + 1 FROM optimized) x(a, b); -- derived table
SELECT * FROM c AS x(a, b); -- CTE reference
SELECT * FROM (c) x(a, b); -- parenthesized CTE reference
SELECT a FROM employees e(a, b, c); -- Parquet table- Derived table lists cover every output, including computed columns and expanded wildcards, and also work in joins
and
LATERALinputs. - CTE reference lists work with wildcard output, grouping, recursive CTEs, joins, and nested queries.
- On a Parquet table,
employees e(a, b, c)exposesid, first_name, last_nameasa, b, cand hides the original names, in plain queries and joins. - The alias count must equal the output (or table) column count, including for empty results. Mismatches raise
SQLSyntaxErrorExceptionwith SQLState42000. - Alias lists on
information_schema.tablesandinformation_schema.columnsare rejected; wrap them in a derived table or CTE to rename their columns.
- Unqualified column names resolve in the subquery's inner scope first, then in the outer row or grouping context
if no inner input has a matching column. This also applies to correlations in
HAVING. - Virtual
information_schematables use their fixed schemas for name binding. - If an inner input's columns cannot be determined, unqualified names stay inner; use an explicit qualifier for outer references.
WITHdeclarations inside subqueries, including derived tables, have local scope and override physical tables or inherited CTEs with the same name.
- A scalar subquery must return exactly one column and at most one row. An empty one-column result is NULL.
- Violations raise
SQLSyntaxErrorExceptionwith SQLState42000in scalar expressions andHAVING, including empty results with multiple columns. - Failures in select-list scalar subqueries keep this classification when reported by
ResultSet.next()or getters. Evaluation can be deferred until iteration or value access, so a successfulexecuteQuery()does not guarantee that a scalar subquery is valid.
- Single-value
IN/NOT INandANY/SOME/ALLsubqueries require exactly one column, even for empty results. Extra columns raiseSQLSyntaxErrorExceptionwith SQLState42000inWHEREandHAVING. - Column counts are validated during planning, before predicate pushdown or empty grouping can skip evaluation.
- These predicates allow multiple rows.
EXISTSallows multiple columns. EXISTSandNOT EXISTStest row existence, including in correlated subqueries,HAVINGpredicates, and set operations.IN,EXISTS,ANY,SOME, andALLkeep set semantics and SQL three-valued logic insideCASEand other scalar expressions inSELECT,HAVING, andORDER BY.
ANY, SOME, and ALL work over subqueries and arrays, such as cyl = ANY(ARRAY[4, 6]), in WHERE and HAVING
(including aggregate values and correlated grouping columns).
SOMEis a synonym forANY.- Empty inputs yield FALSE for
ANY/SOMEand TRUE forALL. - NULL arrays, and undecided comparisons involving NULL elements, yield UNKNOWN.
- Candidates must be scalar; array, map, and struct candidates from arrays or subqueries raise an error.
- Array column values decode scalar element logical types, including dates, decimals, and timestamps, before comparison or correlation binding.
- Within one outer execution, parsed query shapes and validated source schemas are reused. Outer values are bound with their Java types and NULL values intact, and each evaluation reads fresh results.
- Simple physical-table subqueries also reuse their prepared plans; derived tables, joins, and CTEs rebuild row-dependent materializations.
- Eligible correlated predicates are rebuilt from the current bindings for predicate pushdown on every evaluation; the Java residual filter still checks the current outer values.
- Caches are local to the owning execution. Uncorrelated subqueries reuse their results.
- Direct correlated scalar subqueries used as aggregate arguments have a binding limitation (#307).
- Aggregate results can be operands of arithmetic and scalar functions in
SELECT,HAVING, andORDER BY, with or withoutGROUP BY. For example,SUM(cyl) * 2,COALESCE(MAX(cyl), 0), andROUND(AVG(mpg), 1)are evaluated once per group. This includes aggregates insideSUBSTRING(... FROM ... FOR ...),POSITION(... IN ...), andTRIM(... FROM ...). ORDER BYandHAVINGmay use aggregates that are not inSELECT.- Scalar expressions outside aggregates may use grouping expressions and constants, evaluated with each group's
keys, including NULL keys and keys suppressed by grouping sets. For example,
SELECT cyl, ABS(cyl) FROM mtcars GROUP BY cyl. - Grouping keys used by
HAVINGneed not appear inSELECT:SELECT cyl FROM mtcars GROUP BY cyl HAVING NULLIF(cyl, 4) IS NOT NULL. - Grouped
IS [NOT] DISTINCT FROMpredicates bind grouping columns, aggregates, and subqueries with their NULL semantics intact. - Grouped arithmetic follows the same numeric and temporal rules as row expressions.
- Ungrouped column references in
SELECT,HAVING, andORDER BYare rejected with SQLState42803, even for empty inputs. - Unknown functions in grouped
SELECTandHAVINGraiseSQLSyntaxErrorExceptionwith SQLState42000when evaluated. SUMoverREALorDOUBLEinputs reports JDBCDOUBLEmetadata and returnsDouble. A sum of finite values that is not finite raisesSQLDataExceptionwith SQLState22003.
- Grouped and global aggregate queries apply
DISTINCTto their projected result tuples, and applyOFFSET,LIMIT, andFETCH ... ROWS ONLYafter grouping,HAVING, and ordering. - Value-inferred decimal bounds and interval declarations use the full aggregate result after
DISTINCTand before row limits, so metadata is consistent across pages. After inference, the result set keeps only the requested rows.
HAVINGfilters groups before select-list scalar subqueries run. A subquery alias referenced byHAVINGis evaluated on demand and reused for that group's output.AND/ORoperands are evaluated left to right:ANDstops after FALSE, andORstops after TRUE. Skipped operands are not evaluated, so their errors are suppressed; reordering conditions can expose an error.- Quantified comparisons use SQL three-valued logic; see quantified comparisons.
- Scalar subqueries in grouped
SELECTlists can reference outer grouping columns, including grouping columns not in the output. Each subquery uses the current group's values; NULL grouping keys and keys suppressed by grouping sets bind as NULL. Inner names take precedence over outer names. - References to ungrouped outer columns are rejected with SQLState
42803inSELECT,HAVING, andORDER BY, before reading groups and even when the outer input is empty. - Grouped
ORDER BYsupports scalar subqueries and evaluates sort keys once per group. - Only surviving groups contribute to inferred scalar result types.
COUNTsubqueries keepBIGINTmetadata when no groups survive; other unknown scalar types depend on the returned values.
Aggregate FILTER (WHERE ...) clauses are not supported yet (#438).
They raise SQLSyntaxErrorException with SQLState 42000 during preparation, including when combined with OVER.
Workarounds: use a WHERE clause when all aggregates share one filter, or conditional arguments such as
COUNT(CASE WHEN cyl IN (4, 6) THEN 1 END).
HAVING accepts unqualified SELECT aliases:
SELECT SUM(mpg) AS mpg FROM mtcars m GROUP BY cyl HAVING mpg > 200 -- filters on the aggregate aliasA qualified name such as m.mpg refers to the input column and must satisfy the grouping rules, so HAVING m.mpg > 200 raises SQLState 42803. In standard SQL, repeat the aggregate: HAVING SUM(mpg) > 200.
GROUP BY accepts unqualified SELECT aliases:
SELECT d.department AS dep, COUNT(*)
FROM employee_department ed JOIN departments d ON ed.department = d.id
GROUP BY depUnlike in HAVING, GROUP BY names refer to input columns in standard SQL. When a name matches both an input
column and a select-list alias, the input column wins; the alias applies only when no input column has that
name.
- Column matching honors quoted names, including the quoted-lowercase fallback, so the rule also holds for derived tables and CTEs whose internal field names differ from their SQL names.
- Aliases of aggregate expressions cannot be grouping expressions.
- A name shared by more than one select-list alias with different expressions is ambiguous (SQLState
42702).
Recursive CTEs keep the anchor's numeric types, decimal precision, and scale; recursive schemas are not widened
across iterations. Recursive values that need fractional truncation or decimal rounding, or that exceed integer
ranges or decimal precision, fail with SQLException (SQLState 22003).
For example, an integer anchor cannot accept a recursive step of 0.5. A decimal literal infers its precision from
its value, so an anchor of 0.5 becomes DECIMAL(1,1) and cannot grow to 1.5. Use an explicit cast to reserve
precision and scale:
WITH RECURSIVE r(n) AS (
SELECT CAST(0.5 AS DECIMAL(10,1))
UNION ALL SELECT n + 1 FROM r WHERE n < 29.5
)
SELECT n FROM r ORDER BY n- Known column types are kept, including for empty results.
- Computed values with unknown types, including dates and window results, are typed from the returned values through
CTEs and non-
LATERALderived tables, including in joins. Inferred metadata therefore depends on which rows survive filtering: empty or all-NULL unknown-type columns useVARCHAR, and mixed types may also resolve toVARCHAR. - For inferred columns,
getObject()returns the matching Java type, such asjava.sql.Date,Long, orBigDecimal.
LATERALderived tables bind outer values with their Java types intact. For example,FROM arr a CROSS JOIN LATERAL (SELECT u.t FROM UNNEST(a.dates) AS u(t)) xexpands the current row's array.LATERALderived tables retain the decimal declaration of arithmetic and window expressions with known bounds, including when the initial NULL correlation probe produces no value. Unbounded-decimal results (division,ROUND,CEIL,FLOOR,TRUNC, andMOD) andARRAYexpressions still exposeVARCHAR(#475, #480).
- NULL and empty arrays produce no rows;
LEFT JOIN LATERALkeeps their outer rows with NULL inner columns. - Standalone
UNNEST(NULL)returns no rows and usesVARCHARmetadata because its element type is unknown. - When the argument is a direct outer column reference, declared date and nested-array element types are preserved, including metadata when the outer input is empty.
- Declared element types are also propagated through
ARRAYconstructors,COALESCE,NULLIF(using its first argument's type),CASE, arithmetic, and sign expressions, including for empty outer inputs. Other function wrappers can still lose their type (#424). - Arguments can contain scalar subqueries, such as
UNNEST(ARRAY[(SELECT COUNT(*) FROM mtcars WHERE cyl = 4)]) AS t(x). - Joined
UNNESTexpressions can reference preceding inputs, including through correlated scalar subqueries, with implicit or explicitLATERAL. Subqueries use the enclosing CTEs and current parameter bindings; unqualified outer names must resolve uniquely. - Column arguments honor quoted lowercase names such as
UNNEST("n"."a"), including references into parenthesized join groups. Lowercase relation aliases can be declared or referenced with or without quotes. - Quoted mixed-case aliases require an exact match. Quoted mixed-case physical Parquet columns still fail to bind (#479).
- References to equivalent relation qualifiers that identify conflicting inputs raise SQLState
42702in flat and grouped joins. Statement-wide rejection of equivalent correlation names is tracked in #482. - Independent joined
UNNESTarguments are evaluated once per execution and supportRIGHTandFULLjoins. ARIGHTorFULL JOIN UNNESTargument cannot reference its preceding join inputs. COUNT(*)and ordinality-only queries overUNNESTdo not convert interval elements to text.
| Error | SQLState |
|---|---|
| Scalar subquery returns several rows | 21000 |
| Invalid scalar subquery column count, or wrong alias count | 42000 |
| Unknown column | 42000 |
| Ambiguous column or conflicting equivalent qualifier | 42702 |
RIGHT/FULL JOIN UNNEST references a preceding input |
42000 |
- Trailing
LIMIT,OFFSET, andFETCHapply to the combined result, with or withoutORDER BY. Parenthesized branches keep their own row bounds. - Values are converted to the common type before set comparison. Metadata reports the common type and decimal bounds, including for empty results.
| Branches | Result |
|---|---|
INTEGER and BIGINT |
BIGINT |
REAL and REAL |
REAL (Float values) |
| Other approximate combinations | DOUBLE |
DECIMAL and exact numeric |
DECIMAL with the largest integer digit count and scale |
| Numeric and non-numeric | Incompatible |
Integer literals currently use BIGINT.
- At the top level, an untyped NULL branch adopts the other branch's type.
- Untyped NULL projections report
OTHERmetadata in plainSELECTs, grouped results, set operations, CTEs, and derived tables. Their internal schemas stay untyped through CTEs and derived tables (including empty results), so an enclosing set operation can adopt a numeric or non-numeric branch's type. - String literals (including national
N'...'),DATE/TIME/TIMESTAMPliterals, character casts,DATEcasts, andTIME/TIMESTAMP WITHOUT TIME ZONEcasts report their declared JDBC types even for empty or all-NULL results. - Declared LOB and binary casts stay typed for set compatibility, even for all-NULL or empty results; incompatible
numeric, character, binary, or datetime families raise SQLState
42000. These declaredCASTNULLs are distinct from untyped NULL projections. - Compatible
CHARandVARCHARbranches produceVARCHAR. - Compatible local and offset times or timestamps are promoted to the corresponding
WITH TIME ZONEtype using the session offset. This also applies toVALUES.
Set-operation branch declaration checks and cross-branch type compatibility run during execution, including for empty inputs (#464).
JDBC types for arithmetic are derived before rows are read, including through derived tables and CTEs.
| Operands | Result |
|---|---|
Whole numbers with +, -, *, %, unary sign |
BIGINT (Long); overflow raises 22003 |
REAL or DOUBLE |
DOUBLE (Double), using double arithmetic; finite operands whose result is not finite raise 22003 |
| Exact decimals | DECIMAL (BigDecimal); declared bounds reserve carry and product digits |
| Non-approximate division | MathContext.DECIMAL64, so 7 / 2 returns 3.5. Precision and scale are unknown until materialization infers them from the values, keeping value scales such as 3.5. |
| NULL operand | NULL |
| Division or remainder by zero | NULL |
- Integer literals that fit
INTEGERreturnInteger; larger literals useLongor exact decimals. Decimal literals such as1.5are exact. - Unary signs promote whole-number literals to
BIGINT, so-1and+1returnLong. - Whole-number grouping and
DISTINCTkeys compare equal across these Java representations. - JDBC precision is 10 for
INTEGERand 19 forBIGINT; literal-specific digit bounds are kept internally for decimal arithmetic. - Unary signs and binary arithmetic accept numeric text and trim surrounding whitespace, as numeric
CASTdoes. Invalid or non-finite text raisesSQLDataExceptionwith SQLState22018. Text outside theDOUBLErange raises22003when converted for floating-point arithmetic. Unsupported operand types for unary signs raise42000.
DECIMAL(p,s),NUMERIC(p,s), andNUMBER(p,s)round to the declared scale withHALF_UP, then reject precision overflow with SQLState22003.- Omitting the scale while specifying precision means scale zero.
- Bounds must satisfy
1 <= pand0 <= s <= p. Invalid declarations fail during preparation with SQLState42000, including for NULL inputs and empty results. - Casts without precision keep the decimal value and can report unknown precision until materialization infers it.
- Integer casts truncate fractions toward zero before checking the target range.
- Integer range overflow and floating-point overflow to infinity raise
SQLDataExceptionwith SQLState22003. Floating-point rounding and underflow to zero follow Java conversion behavior. - Non-finite text such as
'Infinity','-Infinity', and'NaN', and invalid numeric text, raiseSQLDataExceptionwith SQLState22018. FLOAT, includingFLOAT(p)for any precision such asFLOAT(53), is always single precision (JavaFloat, JDBCREAL). UseDOUBLE,DOUBLE PRECISION, orFLOAT8for double precision; for example, cast1e300toDOUBLE, notFLOAT(53).
Floating-point values use one ordering everywhere, with and without predicate pushdown, matching PostgreSQL and DuckDB:
-Infinity < every finite value < +Infinity < NaN, where NaN = NaN and -0.0 = 0.0.
Comparisons with NaN and infinities are therefore TRUE or FALSE rather than UNKNOWN, and ORDER BY, MIN, and
MAX place NaN last. Arithmetic whose operands are non-finite follows IEEE 754: for example, Infinity * 0 and
NaN + 1 return NaN. Arithmetic on finite operands that produces a non-finite result raises SQLDataException
with SQLState 22003, like BIGINT overflow and floating-point CAST overflow.
DISTINCT and GROUP BY currently keep -0.0 and 0.0 apart
(#477).
Numeric result branches built from literals, columns, numeric casts, arithmetic, aggregates, scalar subqueries,
COALESCE, NULLIF, IFNULL, and supported numeric scalar functions use a common type derived before rows are
read. Numeric scalar functions include mathematical functions and numeric character and datetime functions,
including EXTRACT (exact integers for fields other than SECOND, which has a decimal declaration).
ROW_NUMBER, RANK, DENSE_RANK, and NTILE return BIGINT; PERCENT_RANK and CUME_DIST return DOUBLE.
Navigation windows retain their operand's numeric declaration.
Untyped NULL branches do not constrain the result type; combining NULL with an unresolved branch leaves that
branch unresolved. Scalar subqueries use their independently scoped projection declarations, including
correlated, empty, and NULL results and projections over joined derived tables, VALUES, and LATERAL inputs.
Joined USING columns retain their merged declaration. VALUES declarations come from the row expressions and
are cached without evaluating their values. A declaration that cannot be resolved safely remains unknown rather
than using a physical table from a different scope. These declarations also apply to scalar-subquery arithmetic,
window arithmetic, and COALESCE outside CASE.
- Selected values use that type consistently for JDBC metadata and
getObject(), including grouped aggregate branches. - Decimal branches share a scale when their bounds are known; unbounded expressions keep value-specific scales.
- Decimal equality keys ignore scale for
DISTINCT,GROUP BY, and windowPARTITION BY. - Unselected branches are not evaluated.
Character casts
- Casting numeric, datetime, or interval values to a character type raises
SQLDataExceptionwith SQLState22001when the text does not fit the requested length. CHAR,CHARACTER, andNCHARwithout a length mean length 1.- Character-source casts keep their truncation behavior.
- The standard
NATIONAL CHARACTER,NATIONAL CHAR, andNATIONAL CHARACTER VARYINGspellings behave likeNCHAR/NVARCHAR.
Binary values
- Hex literals (
X'41',0x41) and JDBCsetBytes/setObject(ByteBuffer)parameters returnbyte[]. Hex digits must come in pairs;X''is an empty byte array.ByteBufferparameters use the remaining bytes without changing the buffer's position or limit. - Binary casts and cast-style
CONVERTreturnbyte[]and keepBINARY,VARBINARY, orBLOBmetadata through CTEs and derived tables, including all-NULL and empty results. - Character/binary casts use UTF-8. Malformed UTF-8 raises SQLState
22021instead of replacing bytes. BLOBlengths acceptK/M/G/T/Pmultipliers using overflow-checked long bounds. Plain LOB lengths aboveInteger.MAX_VALUEhit a parser restriction (#462).||concatenation preserves bytes.OCTET_LENGTHcounts binary bytes.- Binary arguments to
CHAR_LENGTH,LENGTH,UPPER,LOWER, andCONCAT, binary/non-binary comparisons, invalid lengths, and known incompatible binary cast sources (includingINTERVAL) raise42000during preparation in plainSELECTs. Preparation checks includeIN,BETWEEN, quantified comparisons, mixed binary/characterLIKE, simpleCASE, andNULLIF, even for NULL and empty inputs. Operands of unknown type are validated at run time. - Standard binary
SUBSTRING,POSITION,TRIM,OVERLAY, and binary-patternLIKEare not supported yet (#461). - Both
getBloboverloads return detached BLOBs, or null for SQL NULL.
Large objects
CHARACTER LARGE OBJECT,NCLOB,NATIONAL CHARACTER LARGE OBJECT, andBINARY LARGE OBJECTshare the corresponding LOB compatibility checks.CLOB/NCLOBuse interim character storage. Full character LOB metadata and LOB array elements are tracked in #452.
Implicit conversion
Implicit interval-to-text coercion is a type error (SQLSyntaxErrorException, SQLState 42000), including in
comparisons with VARCHAR values, concatenation, LIKE, and string functions. Use an explicit cast when text is
intended, such as CAST(end_ts - start_ts AS VARCHAR). NULL operands still produce NULL.
Datetime value functions (CURRENT_TIMESTAMP, CURRENT_TIME, CURRENT_DATE, LOCALTIMESTAMP, LOCALTIME)
- All share one snapshot per statement execution, including nested queries and lazy result reads. Each execution of a prepared statement takes a fresh snapshot.
- They keep their Java time types, fractional seconds, UTC offsets, and JDBC metadata through derived tables and
CTEs, including empty results.
MIN/MAXover these derived columns keep the temporal metadata.
Parsing errors
- Invalid
DATE,TIME, andTIMESTAMPformats raiseSQLDataExceptionwith SQLState22007. - Out-of-range calendar and clock fields raise
22008. - These classifications and their causes are preserved through getters,
WHERE/HAVINGfilters, and nested query evaluation. Errors can be deferred until iteration or value access.
TIME values
TIMEliterals, parameters, and stored Avrotime-millis/time-microscolumns keep milliseconds injava.sql.Time. Digits below one millisecond are truncated, including in comparisons, sorting, grouping,DISTINCT, aggregates, and set operations; values that differ only below a millisecond compare equal.TIMEconversions, includingCAST(timestamp AS TIME)and JDBCgetTime(), use the local date 1970-01-01, so equality, hashing, and ordering depend on clock time alone.- Interval addition and subtraction keep milliseconds and wrap at midnight.
- Stored
TIMEcolumns report JDBCTIMEmetadata and returnjava.sql.TimefromgetObject()andgetTime().CAST(t AS VARCHAR)returns10:00:00for a stored 10:00 clock value.java.sql.Time.toString()shows whole seconds even when the value has milliseconds. - Stored
TIMEvalues outside the half-open range 00:00:00 to 24:00:00 raiseSQLDataExceptionwith SQLState22008. CASTdefaults to six fractional seconds forTIMESTAMPand zero forTIME; an explicit precision overrides the default. Casts truncate excess fractional digits before arithmetic.
Parameters
JDBC date, time, and timestamp parameters, including their java.time equivalents, keep their JDBC types through
typed SQL literals. Timestamps keep nanoseconds. LocalDateTime timestamp parameters before year 1 are rejected
with SQLState 22008 because their era cannot round-trip through JDBC.
Date arithmetic
DATE - DATEreturns the signed number of days asLong(JDBCBIGINT), including through derived tables and CTEs:DATE '2024-03-01' - DATE '2024-02-28'returns2.- Day counts cannot be compared with or added to
INTERVALvalues (SQLState42000). Compare against numbers instead:d - DATE '2024-02-01' > 7. - Date and time offsets require an explicit
INTERVAL:DATE '2024-01-01' + INTERVAL '1 day'works, whileDATE '2024-01-01' + 1and other unsupported temporal arithmetic raiseSQLExceptionwith SQLState42000when evaluated. - Subtracting
TIMEfromDATEorTIMESTAMP, or the reverse, raisesSQLSyntaxErrorExceptionwith SQLState42000, including within predicates. - Differences between
TIMESTAMPvalues, betweenTIMEvalues, or between mixedDATE/TIMESTAMPoperands return intervals; see below. VALUESrejects datetime inputs with incompatible fields.
Literals
- A separate unit:
INTERVAL '1' DAY, forYEAR,MONTH,DAY,HOUR,MINUTE, andSECOND.SECONDaccepts fractional values. - A single string:
INTERVAL '1 day'. DAY TO SECOND:INTERVAL '1 02:03:04' DAY TO SECOND. Fractional seconds are kept, and a leading sign applies to the whole interval. The day field must contain digits after the optional sign.- Values may have an embedded sign or a unary sign (
-INTERVAL '1' DAY). - Qualifier precision such as
DAY(3)andSECOND(2,3)is accepted. Fractional-second precision is kept, but leading-field precision limits are not enforced yet (#474).
| Problem | SQLState |
|---|---|
Invalid interval text, including unknown unit words in the single-string form (such as lightyear), and decimal, exponent, or repeated-sign day fields |
22007 (SQLDataException) |
| Numeric values that cannot be represented, including digit-only day values outside the supported range | 22003 |
Unsupported qualifiers (YEAR TO MONTH, WEEK, QUARTER, MILLISECOND, MICROSECOND, including plurals, in both literal forms) and expression operands such as INTERVAL (1+1) DAY |
0A000 (SQLFeatureNotSupportedException); see #352 |
Values and JDBC
- Intervals are returned as immutable
se.alipsa.jparq.helper.TemporalIntervalobjects fromgetObject()andgetObject(column, TemporalInterval.class). Plain interval literals use the same representation. - Interval columns report JDBC
Types.OTHER, type nameINTERVAL, and classTemporalIntervalin metadata. getString()and explicit character casts use the result column's common interval declaration. Thequalifier()andfractionalPrecision()accessors describe that declaration.CASE,COALESCE,VALUES, arrays, and set operations combine the widest field range and the largest fractional-second precision of their inputs, even inputs that produce no rows. For example, combining1 DAYand36 HOURprints1 00and1 12, and all rows use the same fractional digits. A standaloneINTERVAL '90' MINUTEstill prints90. Positive values omit the optional plus sign. Nanoseconds are kept through materialization.- Parentheses,
CASE,COALESCE,NULLIF, aggregates, navigation window functions, and scalar subqueries keep interval metadata even for empty inputs or all-NULL results. Datetime operands in those expressions also keep their declared type and precision when used in subtraction. - Intervals keep their types through derived tables, CTEs,
VALUES,UNNEST,LATERALinputs, set operations, and scalar or predicate subqueries. Comparisons with intervals andEXTRACTwork after materialization. - Implicit conversion of intervals to text is an error; see implicit conversion.
Constructing arrays
- Non-NULL scalar and array elements must share a common type and nesting depth. NULL elements are kept.
- Numeric elements, including leaves across sibling nested arrays, are promoted to a common numeric type. Mixing
numbers with nested arrays, strings, or other incompatible types raises
SQLSyntaxErrorExceptionwith SQLState42000when evaluated. - Stored array columns and
ARRAYconstructors can be combined; nested reference arrays are normalized to lists, including inARRAYsubqueries. - Computed arrays keep declared element types even when every element is NULL, including fixed-length character and
varying binary declarations. Incompatible declared element types are rejected during planning with SQLState
42000, including NULL elements in predicates, grouping, ordering, join conditions, and skipped subqueries.
Temporal elements
- Elements must have the same datetime fields (
DATE,TIME, orTIMESTAMP), including across sibling nested arrays.DATE/TIMESTAMP,DATE/TIME, andTIMESTAMP/TIMEcombinations raise SQLState42000. CastDATEelements to combine them with timestamps:ARRAY[CAST(DATE '2020-01-01' AS TIMESTAMP), TIMESTAMP '2020-01-01 12:00:00']. - Compatible local and zoned timestamps or times are promoted to the zoned type. Promoted local values receive the SQL session time zone displacement (the JVM time zone's current offset), regardless of their own date, without adjusting local timestamp fields in daylight-saving gaps.
- Values are normalized to
java.sql.Date,java.sql.Time(millisecond precision),java.sql.Timestamp,OffsetTime, orOffsetDateTime, keeping timestamp fractions and explicit offsets.
Types through queries
- Physical array and struct columns keep their types through derived tables, CTEs, and set operations, with matching source schemas available even for empty results.
- Computed arrays with unknown element declarations, and arrays with differing set-branch schemas in non-
LATERALderived tables and CTEs, infer element types from the returned values, recursively. Empty or all-NULL elements use a nullableVARCHARelement schema.
Comparison and grouping
- Predicates compare arrays by content, element by element, treating stored and computed arrays alike. A NULL element sorts before non-NULL elements, and when one array is a prefix of the other, the shorter array sorts first.
DISTINCT,GROUP BY, set-operation keys, and windowPARTITION BYcompare struct fields and array elements by content, independently of Avro record names.
JDBC access
getObject()returns every array value, stored or computed, asObject[], including mixed set-operation results.getArray(index)andgetArray(label)return detached JDBCArrayobjects; SQL NULL returns null and setswasNull(). JDBC arrays expose element type metadata,Object[]contents, one-based slices, and INDEX/VALUE element result sets.free()releases the array; later reads raiseSQLException.CLOBandBLOBarray elements raiseSQLFeatureNotSupportedExceptionwith SQLState0A000(#452). Custom user-defined element mappings raiseSQLFeatureNotSupportedExceptionwhen requested.
- Map keys and values are checked recursively for a common type, including numeric and temporal promotion across
sibling maps. NULL values and empty maps do not constrain inferred types. Incompatible map contents raise SQLState
42000. - Map columns decode their values with the declared Avro value schema, like ARRAY elements: dates become JDBC date
values, decimals
BigDecimal, and timestamps JDBC timestamp values. NULL values and string keys are preserved, and nested containers decode recursively (#313).
getObject()returns struct columns as AvroGenericRecordvalues. Fields keep their raw Avro representations: strings can beUtf8, dates epoch-day integers, decimalsByteBuffer, and timestamps stored longs. The record's schema describes the field types; nested struct fields are not decoded recursively.- Struct elements in arrays must have matching field names and order, with compatible SQL field types resolved
recursively. Numeric fields are promoted to a common type. Nullability, Avro type names, and storage properties do
not distinguish SQL types. Incompatible struct schemas raise SQLState
42000. - Temporal fields keep the finer precision when units differ. Mixed nanosecond and coarser timestamp fields use
lossless internal datetime text to preserve the full date range and nanosecond fractions:
- UTC-only combinations keep their instants and decode as
Timestamp, preserving the standalone JVM-zone view, even during daylight-saving overlaps. - Local-only combinations keep wall-clock fields and decode as
LocalDateTimeorTimestamp. - Mixed UTC-adjusted and local storage retains JDBC
TIMESTAMPand the common wall-clock representation. A UTC-adjusted storage declaration does not introduce SQLWITH TIME ZONEsemantics. - Actual
TIMESTAMP WITH TIME ZONEfields promote compatible local fields toOffsetDateTimeusing the SQL-session displacement. UTC source fields retain their instants during that promotion. - Timestamp promotion overflow in physical encodings raises SQLState
22008.
- UTC-only combinations keep their instants and decode as
String literals
- Escape an apostrophe by doubling it:
'O''Brien'evaluates toO'Brien. JDBCsetStringvalues are escaped automatically. - Untagged dollar-quoted strings (
$$...$$) are supported and keep their contents literally, including apostrophes, identifier quotes, and comment markers:$$it's$$returnsit's, and$$a--b$$returnsa--b. - Tagged forms such as
$tag$...$tag$are not supported (#476). - JDBC parameter scanning does not yet recognize dollar quotes: parameter markers inside their contents, and real parameters after them, can be misidentified (#296).
Parameters
- Positional
?and named:paramNameparameters; see the JDBC support. - For binary and datetime parameters, see binary values and date and time.
Numeric getters (getByte, getShort, getInt, getLong, getFloat, getDouble)
- Convert numeric text by column index and label.
- Return zero for SQL NULL, with
wasNull()returning true. - For text, follow numeric
CASTrules: truncate integer fractions toward zero, reject invalid or non-finite text with SQLState22018, and reject range overflow with22003. - Native numeric values keep Java numeric conversion behavior, including NaN and infinity for floating-point getters.
BOOLEANvalues convert to 1 for TRUE and 0 for FALSE.
Typed getObject(column, Class)
- Supports numeric conversions,
BigDecimal,LocalDate,LocalTime,LocalDateTime,OffsetTime, andOffsetDateTime. TIMEconversions keep milliseconds;TIMESTAMPconversions keep nanoseconds.- Datetime targets accept datetime and character sources. Numeric and Boolean sources raise
22005. OffsetTimeapplies the JVM's current time zone offset before normalizing to UTC.OffsetDateTimeconverts the timestamp's instant to UTC. Existing offset values keep their offsets.- SQL NULL returns null and sets
wasNull().
Other values: see arrays, structs, intervals, and binary values.
Statement.cancel() works while executeQuery() is aggregating, sorting, loading joins, or running nested queries,
and after a result has been returned.
- It requests closure without waiting for an active physical read. A pushed-down record filter can make the current read span the rest of a shard; cancellation does not interrupt a decoder in use.
- The running
executeQuery()or in-flightnext()throwsSQLExceptionwith SQLState57014when it observes the cancellation, instead of returning a partial aggregate or reporting the end of results. This includes cancellation inside a correlated subquery evaluated during iteration. - Nested queries share the outer cancellation state and preserve the outer result returned by
Statement.getResultSet(), so cancellation between subquery calls also stops iteration.
- SQL that JSqlParser rejects raises
SQLSyntaxErrorExceptionwith SQLState42000. - Syntax and numeric errors keep their preparation and nested query context in messages and cause chains.
- Projection evaluation failures are reported as
SQLExceptionwith their original causes, including through result set getters. - Non-Boolean predicates, such as
CASE WHEN cylorNOT model, raiseSQLSyntaxErrorExceptionwith42000. - Expression diagnostics keep the SQL structure but replace internal correlated bindings with
<outer value>, including inside nested subqueries. - Many errors are raised only when a value is evaluated, which can be during
next()or a getter call.
| SQLState | Meaning in JParq |
|---|---|
0A000 |
Feature not supported (some interval qualifiers, LIMIT offset, count, LOB array elements) |
21000 |
Scalar subquery returned more than one row |
22001 |
Cast result does not fit the character length |
22003 |
Numeric value out of range or overflow |
22005 |
Incompatible source type for typed getObject |
22007 |
Invalid datetime or interval format |
22008 |
Datetime field out of range |
22018 |
Invalid character value for a numeric conversion |
22021 |
Malformed UTF-8 in a character/binary conversion |
2201W |
Invalid LIMIT/FETCH row count |
2201X |
Invalid OFFSET row count |
42000 |
Syntax error or invalid query (unknown names, type errors, unsupported constructs) |
42702 |
Ambiguous column reference |
42803 |
Ungrouped column reference |
57014 |
Statement cancelled |
These are known gaps relative to the SQL standard or expected behavior. Each is a bug to be fixed.
| Area | Issue |
|---|---|
Aggregate FILTER (WHERE ...) is not supported |
#438 |
| Unquoted references do not resolve quoted lowercase materialized aliases | #338 |
| Correlated scalar subqueries used directly as aggregate arguments lose outer bindings | #307 |
Window functions outside SELECT lists are not validated |
#350 |
Scalar function result types (and UNNEST type propagation through them) |
#424 |
Character metadata for concatenation and CASE |
#460 |
TIME predicates are not pushed down |
#359 |
Row value IN/ANY/ALL comparisons |
#388 |
| Some type checks run at execution instead of preparation | #464 |
Source-first CONVERT rejects NULL inputs and parameterized decimal targets |
#465 |
Standard binary SUBSTRING, POSITION, TRIM, OVERLAY, and binary LIKE |
#461 |
CLOB/BLOB support, including array elements |
#452 |
Plain LOB lengths above Integer.MAX_VALUE |
#462 |
JSON path [*], [last], and ranges |
#303 |
| JDBC parameter scanning inside dollar-quoted strings | #296 |
Ordered-set aggregates with WITHIN GROUP (PERCENTILE_CONT, LISTAGG, ...) |
#473 |
Multi-field interval qualifiers (YEAR TO MONTH, ...) and INTERVAL (expr) unit |
#352 |
| Interval leading-field precision is not enforced | #474 |
LATERAL derived-table unbounded-decimal results (division, ROUND, CEIL, FLOOR, TRUNC, MOD) and ARRAY decimal expressions expose VARCHAR |
#475, #480 |
| Tagged dollar-quoted strings fail with an unclassified error | #476 |
GROUP BY and DISTINCT treat -0.0 and 0.0 as different values |
#477 |
WHERE predicates are not pushed into parenthesized join groups (performance only) |
#478 |
| Quoted mixed-case physical Parquet columns fail to bind | #479 |
| Equivalent correlation names are not rejected throughout the FROM clause | #482 |
Recursive CTEs expose no numeric declaration, so they cannot combine in CASE and DISTINCT numeric branches |
#565 |