FROM Clause

The formal semantics of a FROM clause describe the collection of binding tuples \$B_"FROM"^"out"\$ that is output by the FROM clause. The semantics specify three cases and essentially extend the tuple calculus that underlies the SQL semantics.

The semantics specify what is the core semantics of a clause with a single item (from and unpivot). The term “semantics of the item \$f\$” is synonymous to the term “semantics of a clause with the single item \$f\$”. In either case, we refer to the specification of the collection of binding tuples \$B_"FROM"^"out"\$ that results from the evaluation of “\$f\$”.
Then the semantics specify how multiple items combine, according to the core semantics, using the join and outerjoin operations (see joins).
Finally, the semantics specify the syntactic sugar structures that are overlaid over the core semantics. Their primary purpose is SQL compatibility.

Ranging Over Bags and Arrays

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:

FROM a AS v AT p

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 >>\$.

Example 1.

Consider the following \$p_0\$:

-- database environment
{
  "someOrderedTable": [
    { 'a': 0, 'b': 0 },
    { 'a': 1, 'b': 1 }
  ]
}

then the following FROM clause:

FROM someOrderedTable AS x AT y

outputs the bag of binding tuples:

\$B_"FROM"^"out" =\$ \$<<<<\$
\$" " << x: {'a':0, 'b':0}, y:0 >> \$
\$" " << x: {'a':1, 'b':1}, y:1 >> \$
\$>>>>\$

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:

FROM a AS v

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 >> \$.

Example 2.

Consider again the database of and then the FROM clause

FROM someOrderedTable AS x

this clause outputs:

\$B_"FROM"^"out" =\$ \$<<<<\$
\$" " << x: {'a':0, 'b':0} >> \$
\$" " << x: {'a':1, 'b':1} >> \$
\$>>>>\$

Mistyping Cases

In the following cases the expression in the FROM clause item has the wrong type. Under the type checking option, all of these cases raise an error and the query fails. Under the permissive option, the cases proceed as follows

Position variable on bags

Consider the clause:

FROM b AS v AT p

and assume that \$b\$ is a bag \$<< e_0, ..., e_{n-1} >> \$. The output is a bag with binding tuples \$<< v: e_i, p: "MISSING" >>\$. The value MISSING for the variable \$p\$ indicates that the order of elements in the bag was meaningless.

Iteration over a scalar value

Consider the query:

FROM s AS v AT p

or the query:

FROM s AS v

where \$s\$ is a scalar value. Then \$s\$ coerces into the bag \$<< s >>\$, i.e., the bag that has a single element, the \$s\$. The rest of the semantics is identical to what happens when the lhs of the FROM item is a bag.

Example 3.

Consider again the database of and the FROM clause:

FROM someOrderedTable[0].a AS x

The expression someOrderedTable[0].a evaluates to 0 and, consequently, the FROM clause outputs a single binding tuple:

\$B_"FROM"^"out" =\$ \$<<\$ \$ << x: 0 >> \$ \$ >> \$

Iteration over a tuple value

Consider the query:

FROM t AS v AT p

or the query:

FROM t AS v

where \$t\$ is a tuple. Then \$t\$ coerces into the bag \$<< t >>\$

Iteration over an absent value

Consider the query

FROM a AS v AT p

or the query

FROM a AS v

whereas \$a\$ evaluates into an absent value (i.e., either MISSING or NULL). In either case the absent value \$a\$ coerces into the bag \$<< a >>\$. Then the semantics follow the normal case.

Example 4.

Consider again the database of and the clause

FROM someOrderedTable[0].c AS x

The expression someOrderedTable[0].c evaluates to MISSING and, consequently, the FROM clause outputs the binding tuple:

\$B_"FROM"^"out" = <<<<\ << x: "MISSING" >>\ >>>> \$

Ranging over Attribute-Value Pairs

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

FROM UNPIVOT t AS v AT a

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 >>\ >>>>\$

Example 5.

Consider the environment \$p_0\$:

{
  "justATuple" : {'amzn': 840.05, 'tdc': 31.06}
}

The clause:

FROM UNPIVOT justATuple AS price AT symbol

outputs:

\$B_"FROM"^"out" =\$ \$<<<<\$
\$" " << price: 840.05, symbol: 'amzn' >> \$
\$" " << price: 31.06, symbol: 'tdc' >> \$
\$>>>>\$

Mistyping Cases

In the following cases the expression in the FROM UNPIVOT clause item has the “wrong” type, i.e., it is not a tuple. Under the type checking option, all of these cases raise an error and the query fails. Under the permissive option, the cases proceed as follows:

FROM UNPIVOT x AS v AT n

whereas \$x\$ is not a tuple and is not MISSING, is equivalent to:

FROM UNPIVOT {'_1': x} AS v AT n

Effectively, a tuple is generated for the non-tuple value. When \$x\$ is MISSING then the above is equivalent to:

FROM UNPIVOT {} AS v AT n

remember that a tuple cannot contain MISSING. So the present case is equivalent to the empty tuple case.