Pure SQL can create varied types in a single column, preventing output via Parquet

[philrz]

The following pure SQL generates a query result that can't currently be output as Parquet:

$ super -f parquet -o foo.parquet -c "
SELECT k, sum(v) FILTER (WHERE v > 1) AS s
FROM (VALUES (1,1),(2,2),(2,3)) AS t(k,v)
GROUP BY k ORDER BY k"
parquetio: encountered multiple types (consider 'blend'): {k:int64,s:null} and {k:int64,s:int64}

The advice of adding on a piped call to blend does indeed make it work. However, legacy SQL users that are accustomed to pure SQL always presenting output under a single schema might be thrown by this.

Details

At the time this issue is being opened, super is at commit 23a29a2.

As documented, in SuperSQL data is treated as a sequence of arbitrarily-typed values, and a relation is the special case where every value is a record of one common type. Therefore, not being able to fit query results into a single schema (such as needed to for output in a format like Parquet) is quite common. This would be particularly true when a query makes use of SuperSQL's pipe operators that are well-suited for working with dynamic data. However, users that come to SuperSQL after heavy prior exposure to legacy SQL may be thrown that the output of a "pure SQL" query like the one above does not automatically conform to a single schema.

For now, the error message's guidance toward using blend is indeed appropriate, and seeing the query's output shows why: The s values all take on a union type that includes the null type.

$ super -version &&
  super -c "
SELECT k, sum(v) FILTER (WHERE v > 1) AS s
FROM (VALUES (1,1),(2,2),(2,3)) AS t(k,v)
GROUP BY k ORDER BY k
| blend"

Version: v0.3.0-340-g23a29a22f

{k:1,s:null::(int64|null)}
{k:2,s:5::(int64|null)}

As @mccanne has explained it, the original repro can be explained by the fact that SuperSQL allows non-relational tables, in the sense that the type can vary within a single column.

That said, to at least address the Parquet case, a couple possible improvements have been discussed within the Dev team:

1. Rather than relying on the user manually adding a call to blend and re-executing the query, have special handling in the Parquet writer. Since the query engine wouldn't know the non-null type of s up front, this would require dynamic buffering similar to what blend does, but once the writer buffers enough rows to discover that type, it could emit s as an optional int64 column and store the empty-group value as a null within it. If multiple non-null types are encountered, it would still have to fail the way it does currently.

2. In a general, non-Parquet sense, wiring in the runtime could be added at some point such that aggregate result types could be derived from static input types when they're available.