PartiQL Specification

A PartiQL query is either an SFW query (i.e. SELECT-FROM-WHERE…​, (lines 3-16) the grammar of Listing 3) or an expression query (also called simple expression in the rest, <<#figure:query:bnf> lines 17-30) such as a path expression ( <<#figure:query:bnf> lines 25-30) or a function invocation. Unlike SQL expressions, which are restricted to outputting scalar and null values, PartiQL expressions output arbitrary PartiQL values, and are fully composable within larger SFW queries and expressions. Indeed, PartiQL allows the top-level query to also be an expression query, not just a SFW query as in SQL.

An PartiQL (sub)query is evaluated within an environment, which provides variable bindings (as defined next).

Each PartiQL (sub-)query and PartiQL (sub-)expression \$q\$ is evaluated within the database environment \$p_0\$ created by the database names and the variables environment \$p\$ created by the defined query variables. The pair of these environments, \$(p_0, p)\$ is collectively called the bindings environment.

In either case, an environment is a binding tuple \$<<x_1:v_1,...,x_n:v_n>>\$, where each \$x_i\$ is a bind name (Listing 4 lines 1-6) that is unique and binds to the PartiQL value \$v_i\$. The two distinct environments may also be thought of as global (the database object names) and local (the lexically defined variables in a particular scope of the query).

Similarly, for a given \$q\$ at compile (i.e. planning) time, a database type environment, \$Gamma_0\$, and variables type environment, \$Gamma\$ are defined. The type environment is a binding tuple \$<<x_1:tau_1,...,x_n:tau_n>>\$ , where each \$x_i\$ is a name that is unique and binds to the PartiQL type \$tau_i\$. For schema-less values, \$tau\$ can be considered the union of any possible type (for which all operations are potentially applicable). This is discussed in more detail in Chapter 15.

Qualified names (Listing 4 line 3) only ever appear in the database environment. Lexically defined variable names (Listing 4 line 4) are always just simple identifiers (Listing 4 lines 5-6). For example, a relational database might define a compound name mydb.log, where mydb is the schema (and not actually a value) and log could be a table name within that schema. Note, that a qualified name is distinct from a quoted identifier that contains a dot. Thus, the qualified name mydb.log is distinct from the bind name "mydb.log".

Remark on relationship of binding tuples to PartiQL tuples A binding tuple is similar to a PartiQL tuple, if you think of the bind names as attribute names. The characterization “binding” pertains to its use in the semantics (e.g. an association of names to types) and the fact that qualified names are not reified in the PartiQL data model, and we have a representation. As we will see collections of binding tuples will be homogenous, i.e., they will all have the same “attribute” names. Also important, is that when we represent binding tuples we explicitly represent a variable with a MISING value, as opposed to omitting it because the lack of a variable name is distinct from a variable whose value is MISSING. For example, we write \$<<x : 1, y: "MISSING">>\$ instead of \$<<x : 1>>\$.

Evaluation in environment The notation \$(p_0,p) |-- q -> v\$ denotes that the PartiQL query \$q\$ evaluates to the value \$v\$ when evaluated within the database environment \$p_0\$ and the variables environment \$p\$, i.e. when every variable of \$q\$ is instantiated by its binding in \$p\$ and each database name is instantiated to its value in \$p_0\$. For example, consider the query x + y / 2, the database environment \$p_0 = <<x:5>>\$ and the variables environment \$p = <<y:3>>\$. Then \$(p_0,p) |-- "(x+y)/2" -> "5+3/2" -> 4\$.

The semantics of PartiQL are shorter than the semantics of SQL itself—despite being backwards compatible with SQL. The key reason is that the semantics of each clause of an SFW query in the PartiQL core can be understood in isolation from the other clauses. A clause is simply a function that inputs and outputs binding tuples. Thus the specifics of how the binding tuples of a query and of its subqueries are produced are a central part of the semantics. At a high level (which will be elaborated upon later) the construction of binding environments proceeds as follows:

Similar to SQL semantics, the clauses of an PartiQL SFW query are evaluated in the following order: WITH, FROM, LET, WHERE, GROUP BY, HAVING, LETTING (which is special to PartiQL), ORDER BY, LIMIT/OFFSET, and SELECT (or SELECT VALUE or PIVOT, which are both special to ion PartiQL). ^[2]

Using the example of Example 2, we illustrate how the clauses of an SFW query input and output binding tuples. In the Example 2, the FROM, WHERE, and SELECT clauses of the example query are displayed apart from each other so that the example can also illustrate the binding tuples that flow from the one clause to the next.

The query is evaluated within the bindings environment \$(p_0, p)\$ shown at the top of Example 2. Consequently, the FROM clause is evaluated in the same environment. Thereafter the FROM clause outputs the bag of binding tuples \$B_"FROM"^"out"\$, which has four binding tuples in the example. In each binding tuple of \$B_"FROM"^"out"\$ , each variable of the FROM clause is bound to a value. There are no restrictions that a variable binds to homogenous values across binding tuples. In the example, x binds to two values that are heterogeneous: some bindings of x bind to a number, while others to a string. It would also be possible that a variable binds to, say, a scalar in one binding, while the same variable binds to a complex value in another binding.

Each subsequent clause inputs a bag of binding tuples, evaluates the constituent expressions of the clause (which may themselves contain nested SFW queries), and outputs a bag of binding tuples that is in turn input by the next clause. For instance, the WHERE clause inputs the bag of binding tuples that have been output by the FROM clause (\$B_"FROM"^"out" = B_"WHERE"^"in"\$), and outputs the subset thereof that satisfies the condition expression of the WHERE clause. This subset is the \$B_"WHERE"^"out" = B_"SELECT"^"in"\$.

In particular, the WHERE’s condition is evaluated once for each input binding tuple \$b\$ in \$B_"WHERE"^"in"\$. In general, each evaluation is done within the bindings environment \$(p_0,p || b)\$ , i.e., the concatenation of the binding tuple \$p\$ (where \$p\$ is the binding environment of the SFW query) with the binding tuple \$b\$ that has the variables of the clause. In the particular example \$p || b\$ is simply \$b\$ since \$p=<<>>\$. The condition is evaluated once for each of the four input binding tuples of \$B_"WHERE"^"in"\$. The variables environment of the first evaluation is:

\$p = <<x:3, y: { 'a':1, 'b':2 } >>\$

The condition evaluates to for the first binding tuple of \$B_"WHERE"^"in"\$, since

\$(p_0,p) |-- x > y.b -> 3 > { 'a':1, 'b':3}.b -> true \$

Thus the first binding tuple of \$B_"WHERE"^"in"\$ is output from the WHERE and is input to SELECT.

The pattern of “input bag of binding tuples, evaluate constituent expressions, output bag of binding tuples” has a few exceptions: First, the ORDER BY clause inputs a bag of binding tuples and outputs an array of binding tuples. Second, a LIMIT/OFFSET clause need not evaluate its constituent expression for each input binding tuple. For example a LIMIT 10 clause that inputs an array with 100 binding tuples need not access binding tuples 11-100.

Finally, the SELECT clause is responsible for converting from binding tuples to collections of arbitrary PartiQL elements. The SELECT inputs a bag (or array, if ORDER BY is present) of binding tuples, and outputs the SFW query’s result, which is a bag (resp. array) with exactly one element for each input binding tuple. In the example, the SELECT expressions x and y.a are evaluated once for each of the input binding tuples of \$B_"SELECT"^"in"\$, which in this example happen to be just one binding tuple.

Finally, notice that the above discussion of SFW queries did not capture the set operators UNION, INTERSECT, and EXCEPT. As is the case with SQL semantics too, the coordination of with the set operators requires attention.

In summary, each clause of PartiQL is an operator that inputs/outputs binding tuples. As such, we can (and will) present the semantics of each clause separately from the semantics of the other clauses. This is not the case in SQL: Notably, in the presence of aggregation functions the SELECT, HAVING, and WHERE cannot be interpreted in isolation; they can only be interpreted along with the GROUP BY clause.

As in any programming language, the PartiQL semantics have to deal with issues of variable scope. For example, how are references to x resolved in the following query:

Since this is an SQL query and PartiQL is backwards compatible to SQL, it is easy to tell that the x in x.c resolves to the variable defined by the inner query’s FROM clause.

Technically, this scoping rule is captured by the following handling of binding tuples. The inner FROM clause is evaluated with a variables environment \$p = <<x:...>>\$; its x is the one defined by the outer FROM. Then the inner FROM clause outputs a binding \$b = <<x..>>\$; this x is defined by thinner FROM. Then the x.c is evaluated in the concatenation \$p||b\$ and because x appears in both \$p\$ and \$b\$, the concatenation keeps only the x of its right argument. Essentially by putting \$b\$ as the right argument of the concatenation, the semantics indicate that the variables of \$b\$ have precedence over synonymous variables in the left argument (which was the \$p\$).

Generally, given two binding tuples \$b\$ and \$b'\$, their concatenation is a binding tuple, denoted as \$b||b'\$, that has the variable bindings of both \$b\$ and \$b'\$. This creates the possibility that both \$b\$ and \$b'\$ have the same variable \$x\$. In this case, the concatenation \$b||b'\$ will have the \$b'.x\$ and its value; it will not have the \$b.x\$ and its value.

Note, the above does not resolve scoping issues resulting from conflicts between the database environment and the variables environment. We resolve these conflicts by explicit rules.

In the case of tuple paths, since PartiQL does not assume a schema, the semantics must also specify the return value when:

PartiQL can operate in a permissive mode or in a conventional type checking mode, where the query fails once typing errors (such as the above mentioned ones) happen. In the permissive mode, typing errors are typically neglected by using the semantics outlined next.

In all of the above cases PartiQL returns the special value MISSING. Recall, the MISSING is different from NULL. The distinction enables PartiQL to be able to distinguish between a tuple (JSON object) that lacked an attribute a and a tuple (JSON object) whose a attribute was NULL. This distinction, coupled with appropriate features on how result tuples are constructed (see SELECT clause in Chapter 6), enables PartiQL to easily preserve (when needed) the distinction between absent attribute and null-valued attribute.

For example, the expression ` 'not a tuple'.a ` and the expression {'a':1, 'b':2}.noSuchAttribute evaluate to MISSING.

The above semantics apply regardless of whether the tuple navigation is accomplished via the dot notation or via the array notation. For example, the expression {'a':1, 'b':2}['noSuchAttribute'] will also evaluate to MISSING.

In the type checking mode and in the absence of schema, PartiQL will fail when tuple path navigation is applied on wrongly typed data.

In the permissive mode, an array navigation evaluation \$a\[i]\$ will result into MISSING in each of the following cases:

For example, [1,2,3][1.0] evaluates to MISSING since 1.0 is not an integer - even though it is coercible to an integer.

In type checking mode, the query will fail in each one of the cases above.

The following additional path functionalities are explained by reduction to the basic tuple navigation and array navigation.

The expression \$e\[**]\$ reduces to (i.e., is equivalent to):

where \$v\$ is a fresh variable, i.e., a variable that does not already appear in the query. Similarly, when the expression \$e.**\$ is not a SELECT clause item of the form \$t.*\$, where \$t\$ is a variable, it reduces to:

where \$v\$ is a fresh variable. An expression \$t.**\$, where \$t\$ is a variable and the expression appears as a SELECT clause item, is interpreted according to the SELECT clause semantics (Section 6.3.2).

PartiQL also provides multi-step path expressions, called path collection expressions. Their semantics is a generalization of the semantics of a path expression with a single \$\[**]\$ or \$.**\$. Consider the path collection expression:

where \$e\$ is any expression; \$n>0\$; each wildcard step \$w_i\$ is either \$\[**]\$ or \$.**\$; each series of plain path steps \$p_i\$ is a sequence of zero or more tuple path navigations or array navigations (potentially mixed).

Then the path collection expression is equivalent to the SFW query

where each \$v_i\$ is a fresh variable and each \$u_i\$ is UNPIVOT if \$w_i\$ is a \$.**\$ and it is nothing if \$w_i\$ is a \$\[**]\$. Intuitively \$v_i\$ corresponds to the \$i\$-th star.

Next we define the semantics of a FROM clause that has a single FROM item and such item ranges over a bag or array. First consider the FROM clause:

Let us call \$v\$ to be the element variable and \$p\$ to be the position variable. In the normal case, \$a\$ is an array \$ \[ e_0, ..., e_{n-1} ] \$. The FROM clause outputs a bag of binding tuples. For each \$e_i\$, the bag has a binding tuple \$< < v: e_i, p:i > >\$.

As in SQL, the AS keyword is optional. The same applies to all cases below where AS appears. If there is no AT clause, then the binding tuples have only the element variable. In particular, consider:

Normally \$a\$ is a collection, i.e, an array \$ \[e_0,...,e_{n-1}] \$ or a bag \$ < < e_0,...,e_{n-1} > > \$. In either case, the FROM clause outputs a bag. For each \$e_i\$, the bag has a binding tuple \$ << v:e_i >> \$.

The UNPIVOT clause enables ranging over the attribute-value pairs of a tuple. The FROM clause

normally expects \$t\$ to be a tuple, with attribute/value pairs \$ a_1:v_1, ..., a_n,v_n \$. It does not matter whether the tuple is ordered or unordered. The FROM clause outputs the collection of binding tuples

\$B_"FROM"^"out" = < < <<v:v_1, a:a_1>> ... <<v:v_n, a:a_n>> > >\$

The FROM clause expressions:

have the same semantics. They combine the bag of bindings produced from the FROM item \$l\$ with the bag of binding tuples produced by the FROM item \$r\$, whereas the expression \$r\$ may utilize variables defined by \$l\$. Again, the term “the semantics of l CROSSJOIN r” is equivalent to the term “the semantics of FROM l CROSSJOIN r”. In both cases, the semantics specify a bag of binding tuples.

The FROM clause expression:

replicates SQL’s LEFT JOIN functionality and, in addition, it also works for the case where the lhs of \$r\$ uses variables defined from \$l\$.

Let’s assume that the variables defined by \$r\$ are \$v_1^r, ..., v_n^r\$. The result of evaluating l LEFT CROSS JOIN r in environments \$p_0\$ and \$p\$ is the bag of binding tuples produced by the following pseudocode, which also uses the \$"eval"\$ function (See Section 5.3).

The clause expression:

replicates SQL’s FULL JOIN functionality. It assumes that (alike SQL) the lhs of \$r\$ does not use variables defined from \$l\$. Thus, we do not discuss further.

In compliance to SQL, the and have an optional clause. The semantics of can be explained as syntactic sugar over the core PartiQL. They can also be explained by a simple extension of the semantics of Section 5.3, Section 5.4, and Section 5.5. The semantics of:

are the following modification of the pseudocode of Section 5.3.

The semantics of:

are the following modification of the pseudocode of Section 5.4. In essence, the outputs a tuple padded with whenever there is no binding of \$r\$ that satisfies the condition \$c\$.

SQL 2003 used the LATERAL keyword to correlate clause items. In the interest of compatibility with SQL, PartiQL also allows the use of the keyword LATERAL, though it does not do anything more than the comma itself would do. That is l, LATERAL r is equivalent l, r.

The SELECT VALUE clause inputs a bag of binding tuples or an array of binding tuples (from the other clauses of the SQL query) and outputs a bag or an array. For example, if the query only has SELECT VALUE, FROM, and WHERE clauses, then the bindings that are output by the WHERE clause are input by SELECT VALUE the clause. Unlike SQL, the output of a SELECT VALUE clause need not be a bag or array of tuples. It is a bag or array of any kind of PartiQL values. For example, it may be a bag of integers, or a bag of arrays, etc. Indeed, the values may be heterogeneous. For example, the output may even be a bag that has both integers and arrays.

The core PartiQL clause:

inputs a bag or an array (depending on the presence or non-presence of ORDER BY) of binding tuples and outputs respectively a bag or an array of values. Let \$p_0\$ and \$p\$ be the environments of the SFW query. For each input binding tuple \$b in B_"SELECT"^"in"\$, outputs a value \$v\$, where \$p_0, (p || b) |-- e -> v\$. Notice that PartiQL expressions \$e\$ (Listing 3 lines 17-30) will typically be tuple or array or bag constructors (lines 20-22), which enable the construction of respective results. In general \$e\$ can be any expression.

The PIVOT clause may appear in lieu of SELECT VALUE. The PIVOT clause outputs a tuple; in contrast, a SELECT VALUE outputs a collection (bag or array). The syntax is

where the other clauses, …​, are the usual FROM, WHERE, etc. The semantics are similar to SELECT VALUE. Let \$p_0\$ and \$p\$ be the environments of the SFW query. For each input binding tuple \$b in B_"PIVOT"^"in"\$ PIVOT, outputs an attribute name/value pair \$a,v\$, where the name \$a\$ is the result of \$e_a\$ and the value \$v\$ is the result of \$e_v\$. (Technically, \$p_0, (p || b) |-- e_a |-> a\$ and \$p_0, (p || b) |-- e_v |-> v\$.) Regardless of whether \$B_"PIVOT"^"in"\$ is a bag (i.e., the SFW query did not have an ) or an array (i.e., the SFW query had an ORDER BY), the output tuple is unordered. Schema may be applied extantly to obtain an ordered tuple.

Unlike SQL where typing issues can be detected during query compilation, the permissive option of PartiQL has to define semantics for the case where the inputs of a function are not compatible with the function/predicate arguments. Furthermore, PartiQL facilitates propagating missing input attributes to respective missing output attributes.

Alike SQL, all functions have input argument types that they conform to. For example, the function log expects numbers. All functions return MISSING when they input data whose types do not conform to the input argument types. Since no function (other than IS MISSING) has MISSING as an input argument type, it follows that all functions return MISSING when one of their inputs is MISSING.

In each of the following cases a SFW subquery coerces into a scalar

Essentially, a subquery that is coerced may appear in all clauses except the FROM. For example, it may be a SELECT subquery \$s\$ that appears as an item of a SELECT, SELECT VALUE, or PIVOT clause. Or it may be a subexpression of an expression that appears in SELECT, SELECT VALUE , or PIVOT clause. Or it may be a subexpression of the WHERE clause expression, as long as it is not the rhs of an IN. In any of these cases the result of the subquery \$s\$ is cast into a scalar.

Technically, the subquery \$s\$ (which uses SELECT) is rewritten into an equivalent subquery \$s'\$ that utilizes SELECT VALUE, by following the steps of Section 6.3. Then the result of \$s'\$ is cast into a scalar by applying the function \$"COLL_TO_SCALAR"(s')\$.

As is the common semantics of PartiQL in the permissive mode, when COLL_TO_SCALAR fails to cast the subquery into a scalar, it outputs MISSING. The inputs that are coerced into scalars are the ones that SQL prescribes: When the input is a collection consisting of a single tuple with a single attribute, the input is coerced into a scalar. All other inputs to COLL_TO_SCALAR lead to MISSING.

As in SQL, an implementation with static type checks will be able to detect and warn that, in certain cases, a coercion will always fail and produce MISSING.

An SELECT SFW subquery coerces into an array when it is the rhs (respectively, lhs) of a comparison operator whose other argument is an array. ^[4]

The reduction of a SELECT subquery to the PartiQL is exhibited by the following example.

database names Since the rules are easier to express when all database names are a single identifier, such as thedb or "the db" (as opposed to paths, such as somedb.sometable), we first specify the scoping rules under the assumption that all database names are a single identifier. We remove the assumption and generalize later.

In the absence of schema the following rules apply

Next, we generalize to also allow for the possibility of database names of the form identifier.identifier. …​. The following rules apply regarding the semantics of \$i_1.i_2. ... .i_n\$, where \$i_1, i_2, ..., i_n\$ are identifiers.

The GROUP BY clause (Listing 3 lines 7-9)

creates a group. Each \$e_i\$ is a grouping expression, each \$x_i\$ is a grouping variable ^[6] and \$g\$ is the group variable.

As in SQL, the bag of input binding tuples \$B_"GROUP"^"in"\$ is partitioned into the minimal number of equivalence groups \$B_1,...,B_n\$, such that any two binding tuples \$b, b' in B_"GROUP"^"in"\$ are in the same equivalence group if and only if every grouping expression \$e_i\$ evaluates to equivalent values \$v_i\$ (when evaluated on \$b\$) and \$ v_i' \$ (when evaluated on \$b'\$). More precisely, as in SQL, there is an equivalence function \$"eqg"\$, used by the GROUP BY to determine if two values \$v_i\$ and \$ v_i' \$ are equivalent for grouping purposes. The equivalence function \$"eqg"(v_i, v_i' )\$ returns only true or false; true meaning that the values are equivalent for grouping purposes. See Section 11.1.1 for specifics of eqg. If a grouping expression evaluates to MISSING, it is first coerced into NULL, thus bringing MISSING and NULL in the same group.

Unlike SQL, for each group \$B_j (1 <= j <= n)\$, the GROUP BY clause outputs a binding tuple \$b_j = (: x_1 : v_1,...,x_m : v_m, g : B_j:)\$ that has the full group \$B_j\$. Notice:

Notice, the output binding tuple provides the partitioned input binding tuples in the group variable \$g\$, which can be explicitly utilized in subsequent HAVING, ORDER BY, and SELECT clauses. Thus, an PartiQL query can perform complex computations on the groups, leading to results of any type (e.g. collections nested within collections). The explicit presence of groups in PartiQL, while more general than SQL, also leads to simpler semantics than those of SQL, since the GROUP BY clause semantics are independent of the presence of subsequent functions in HAVING, ORDER BY, and SELECT.

Notice, the aggregate functions COLL_AVG and COLL_COUNT (and for that matter, by convention, any function starting with COLL) can be thought of as general-purpose functions. Generally, they do not have to be fed by the result of a grouping operation - unlike SQL’s COUNT and AVG that are being fed exclusively from the results of grouping operations. (Furthermore, the SQL COUNT and AVG make use of SQL’s syntactic sugar, where there is no explicit use of group variable, as explained in Section 11.2.2.)

Similarly, it is fine to include in any clause an aggregate function fed by the result of a (sub)query.

The PartiQL approach provides two benefits: First, it leads to shorter, modular semantics. Second, it enables GROUP BY to address use cases that would otherwise need knowledge and non-trivial SQL programming of window functions. See Example 48.

The group-by and aggregation of PartiQL is backwards compatible to SQL.

Similar to SQL, the PartiQL ORDER BY clause syntax is:

(Listing 3), where the sequence of expr is a list of ordering expressions. In PartiQL a SFW query with ORDER BY outputs an array, whereas a SFW query without ORDER BY outputs a bag.

Alike SQL’s ORDER BY clause, the NULLS FIRST and NULLS LAST keywords indicate whether NULL and MISSING values are ordered before or after all other values. Notice that in PartiQL, the NULLS FIRST and NULLS LAST refer to both NULL and MISSING.

The ORDER BY clause sorts its input using the order-by less-than function \$<^o\$, which is able to compare values of different types (unlike SQL). In particular:

Coming up…​

For SQL-compatibility, PartiQL allows the CURRENT variable to be omitted from ordering expressions. Then when the CURRENT variable binds tuples, the ordering expressions can refer directly to the attributes of those tuples.

The complete scoping rules are as follows. When all of the following conditions are satisfied:

then the path expression as resolves to CURRENT.as.

The most common and useful way to have the 3rd condition be satisfied is when the UNION …​ ORDER BY is a top-level query and, thus, the variables environment \$p\$ is empty.

Recall from Chapter 3 that ORDER BY is evaluated before SELECT. For SQL-compatibility, given SELECT e AS a, PartiQL also supports the syntactic sugar of using a in lieu of e in the ORDER BY clause. Therefore, both SFW queries below are equivalent:

Notice that definition of < dismissed the SQL coercions. In SQL, given explicit literals in a query, coercions may happen.

This aspect of SQL compatibility is introduced by rewriting. Namely, given a query with incompatible types