Implementation & Specification Comparison

partiql-lang-kotlin compared to the PartiQL Specification

This document outlines the ways in which the partiql-lang-kotlin reference implementation conforms and diverges from the PartiQL specification. Also included are some features that are part of the Kotlin implementation but are not yet formally defined in the PartiQL spec.

Features defined in spec

Spec section	Feature	Implemented in partiql-lang-kotlin	Notes
3.3	Syntax improvement SELECT optionally written as last clause	No
4	Tuple path navigation	Yes	t.a; tuple t and attribute a (identifier)
4	Array navigation	Yes	a[i]; array a, i is an expression that evaluates to an integer
4	Tuple navigation with array notation	Yes	a[s]; shorthand for a.s where a is a tuple, s is either a string literal or expression CAST to a string
4	Composition of navigations	Yes	r.no[1]
4	Tuple navigation in tuples w/ duplicate attributes	Yes
4.1	Tuple path evaluation on wrongly typed data	Partially^1	Permissive mode behavior returns MISSING as defined in the spec. "type checking mode" doesn't exist in the Kotlin implementation
4.2	Array navigation evaluation on wrongly typed data	Partially^1	[1, 2, 3][1.0] evalutes to MISSING in permissive; errors in LEGACY
4.3	Wildcard steps	Yes
4.3	Path expressions with wildcards	Yes
5.1	Ranging over bags and arrays	Yes	AS and AT support; along with single FROM item
5.1.1	Position variable on bags	Partially^1	Permissive mode behavior returns MISSING as defined in the spec. LEGACY mode should error but doesn't. Sample query: SELECT * FROM <<1, 2, 3>> AS v AT p
5.1.1	Iteration over a scalar value	Partially^1	Permissive mode behavior returns MISSING as defined in the spec. LEGACY mode should error but doesn't. Sample query: SELECT * FROM 1 AS v AT p or SELECT * FROM [{'a':0, 'b':0}, {'a':1, 'b': 1}][0].a AS x
5.1.1	Iteration over a tuple value	Partially^1	Permissive mode behavior returns MISSING as defined in the spec. LEGACY mode should error but doesn't. Sample query: SELECT * FROM {'a': 1} AS v AT p
5.1.1	Iteration over an absent value	Partially^1	Permissive mode behavior returns MISSING as defined in the spec. LEGACY mode should error but doesn't. Sample query: SELECT * FROM [{'a':0, 'b':0}, {'a':1, 'b': 1}][0].c AS x
5.2	Ranging over attribute-value pairs (unpivoting tuples)	Yes
5.2.1	Mistyping cases (UNPIVOT)	Partially^1	Permissive mode has the correct equivalent query for non-tuples. LEGACY mode doesn't error
5.3	Combining multiple FROM items with Comma, CROSS JOIN, or JOIN	Yes
5.4	Combining multiple FROM Items with LEFT JOIN	Yes
5.5	Combining multiple FROM items with FULL JOIN	Parser only	Supports parsing of FULL JOIN (and RIGHT JOIN) but is not yet implemented in the evaluator
5.6	Expanding JOIN and LEFT JOIN with ON	Partially	Implementation requires ON clause since v0.2.0 https://github.com/partiql/partiql-lang-kotlin/releases/tag/v0.2.0-alpha
5.7	SQL's LATERAL	No
6.1	SELECT VALUE core clause	Yes
6.1.1	Tuple constructors	Yes
6.1.1	Tuple constructors -- treatment of mistyped attribute names	Partially^1	Permissive mode has correct behavior. LEGACY mode follows type-checking mode and gives an error
6.1.1	Tuple constructors -- treatment of duplicate attribute names	Yes
6.1.2	Array constructors	Yes
6.1.3	Bag constructors	Yes
6.1.4	Treatment of MISSING in SELECT VALUE -- when constructing tuples	Yes	Note: example in the spec (example 18) needs to alias the FROM source. Should be: SELECT VALUE {'a':v.a, 'b':v.b} FROM [{'a':1, 'b':1}, {'a':2}] AS v
6.1.4	Treatment of MISSING in SELECT VALUE -- when constructing arrays	Yes	Note: example in the spec (example 19) needs to alias the FROM source. Should be: SELECT VALUE [v.a, v.b] FROM [{'a':1, 'b':1}, {'a':2}] AS v
6.1.4	Treatment of MISSING in SELECT VALUE -- when constructing bags	Yes	Note: examples in the spec (examples 20 and 21) need to alias the FROM source.
6.2	Pivoting a Collection into a Variable-Width Tuple	Yes
6.3.1	Select Without *	Yes
6.3.2	SQL's *	Yes	.* properly extends to bindings that aren't tuples.
6.3.2	SQL's * -- TupleUnion function	No	The TUPLEUNION function is not part of implementation.
6.4	Examples with combinations of multiple features	Yes
7.1	Function inputs with wrong types (permissive mode)	Yes
7.1,1	Equality	Yes
8	WHERE clause	Yes
9.1	Coercion of a SELECT subquery into a scalar	No	Coercion into a scalar not implemented. Related issue: partiql-lang-kotlin#209.
9.2	Coercion of a SELECT subquery into an array	No
10	Scoping rules	Yes
11.1	PartiQL GROUP BY core: Grouping into a Group Variable	Yes
11.1.1	Equivalence function used by grouping; grouping of NULL and MISSING	Yes
11.1.2	The GROUP ALL variant	No
11.2.1	Grouping attributes and direct use of grouping expressions	Yes	Kotlin implementation supports using grouping expression in other clauses
11.2.2	SQL's implicit use of the group variable in SQL aggregate functions	Partially	Kotlin implementation allows for implicit use of group variable but rewrite does not occur as specified (e.g. rewriting query without GROUP BY to GROUP ALL GROUP AS g)
11.2.3	Designation of SQL aggregate functions	Partially	Kotlin implementation offers the standard aggregate functions (COUNT, SUM, AVG, etc.) but no the COLL_* functions (partiql-lang-kotlin#222). It's not required for an implementation to provide both.
11.2.4	Aliases from SELECT clause	No
11.3	Windowing cases simplified by the PartiQL grouping	No	Windowing functions not supported yet in PartiQL. See partiql-lang-kotlin#603 for a related issue
12.1	PartiQL ORDER BY syntax	Partially	ORDER BY syntax is impleneted other than the PRESERVE modifier
12.2	The PartiQL order-by less-than function	Yes
12.3	Dependency of ORDER BY semantics on the presence of set operators	Not defined in spec
12.4	SQL compatability ORDER BY clauses	No	CURRENT variable not supported. Condition in which CURRENT could be omitted is not supported (requires bag operators like UNION).
12.5	Use of SELECT variables in ORDER BY for SQL compatability	No	SELECT aliases are not yet supported. See partiql-lang-kotlin#571.
12.6	Coercion of literals for SQL compatability	No
13	UNION/INTERSECT/EXCEPT clauses	Partially	RFC0007-rfc-bag-operators defines. Only partial support currently. See partiql-lang-kotlin#184.
14	PIVOT clause semantics	Yes
15	Structural types and type-related query syntax and semantics (WIP)	Not defined in spec

Caveats:

• ^1 partiql-lang-kotlin does not support the "type checking mode" defined in section 4.1 of the spec, which errors on operations with wrongly-typed data. It does have a “LEGACY” mode which offers stricter type-checking support than “PERMISSIVE” mode but does not follow all of the type-checking behavior. See this comment in CompileOptions.kt for more information on LEGACY mode.

Features defined in partiql-lang-kotlin

Feature	Notes
LET clause	- Mentioned in section 3.3 (pg 11). Proposed spec change PR: https://github.com/partiql/partiql-spec/pull/21. LET semantics defined in partiql-spec#15: https://github.com/partiql/partiql-spec/issues/15
DML (INSERT, SET, REMOVE, DELETE)	Parsed only. Defined informally; no mention in spec
DDL (CREATE TABLE, DROP TABLE, CREATE INDEX, DROP INDEX)	Parsed only. Defined informally; no mention in spec
EXEC	Stored procedure calls