[Ur] Subqueries.

[Adam Chlipala]

Alexei Golovko wrote:
> Can anyone show me sql subquery usage example? I don't understand which syntax I should use.
> I am trying:
>
> $ cat sql.ur
> table t : { X : int, Y : int }
> table s : { X : int, Y : int, Z : int }
> val test = dml (INSERT INTO t (X, Y) VALUES
>      ( (SELECT s.X FROM s WHERE s.Z = 1),
>      (SELECT s.Y FROM s WHERE s.Z = 1) )
> )
>    

The problem has to do with an aspect of the Ur/Web SQL encoding that 
isn't a literal transcription of standard SQL rules.  In particular, 
Ur/Web query results distinguish between columns pulled out of tables 
and columns computed from arbitrary expressions.  Your above subqueries 
generate output using the first kind of column, while Ur/Web expects the 
second kind of column.  A subquery should return exactly one column per 
row, where that column is an "arbitrary computed" column with any name.

It turns out you can fix the example by putting the selected item in 
parens, e.g.:
     SELECT (s.X) FROM ...
which "demotes" that output column to "arbitrary computed" status, 
forgetting that it comes from a table, and giving it name [1] by default.

The Ur type system doesn't provide an obvious way to type the subquery 
operator where it would work with either kind of output column.