Datatype mess when UNION ALL

@segahm Are you doing the casting on the top-level projection in the union all? Better yet—are you casting uniformly and consistently on all projections in the union all? You may also have to cast any top-level null values.

To elaborate on this. In BigQuery’s legacy SQL, column order did not matter:

So, you could UNION 2 tables as follows:

SELECT

  x,

  y

FROM

  (SELECT 1 AS x, "foo" AS y),

  (SELECT "bar" AS y, 2 AS x);

It was a great feature that is missing from many dialects. Cautionary note: In BigQuery’s new Standard SQL, the above will no longer work:

SELECT

  x,

  y

FROM

  (SELECT 1 AS x, "foo" AS y UNION ALL

  SELECT "bar" AS y, 2 AS x);

Instead, you have to match columns as in Redshift:

SELECT

  x,

  y

FROM

  (SELECT 1 AS x, "foo" AS y UNION ALL

  SELECT 2 AS x, "bar" AS y);