`append` twice with null columns leads to runtime Google BigQuery SQL error `UNION ALL has incompatible types`

[Fanaen]

What happened?

• Doing two append in Google BigQuery with null columns.
• prqlc outputs a query that leads to runtime failure UNION ALL has incompatible types

PRQL input

prql target:sql.bigquery

from beers
select { name, price_per_l, alcohol_degree = null, brewing_date = null }
sort { +price_per_l }
take 2
append (
  from beers
  select { name, price_per_l = null, alcohol_degree, brewing_date = null }
  sort { -alcohol_degree }
  take 2
  append (
    from beers
    select { name, price_per_l = null, alcohol_degree = null, brewing_date }
    sort { -brewing_date }
    take 2
  )
)

SQL output

(
  SELECT
    name,
    price_per_l,
    NULL AS alcohol_degree,
    NULL AS brewing_date
  FROM
    beers
  ORDER BY
    price_per_l
  LIMIT
    2
)
UNION
ALL (
  SELECT
    name,
    NULL AS price_per_l,
    alcohol_degree,
    NULL AS brewing_date
  FROM
    beers
  ORDER BY
    alcohol_degree DESC
  LIMIT
    2
)
UNION
ALL (
  SELECT
    name,
    NULL AS price_per_l,
    NULL AS alcohol_degree,
    brewing_date
  FROM
    beers
  ORDER BY
    brewing_date DESC
  LIMIT
    2
)

Expected SQL output

WITH cte0 as (
  SELECT
    name,
    price_per_l
  FROM
    beers
  ORDER BY
    price_per_l
  LIMIT
    2
), cte1 as (
  SELECT
    name,
    alcohol_degree,
  FROM
    beers
  ORDER BY
    alcohol_degree DESC
  LIMIT
    2
), cte2 as (
  SELECT
    name,
    NULL AS price_per_l,
    NULL AS alcohol_degree,
    brewing_date
  FROM
    beers
  ORDER BY
    brewing_date DESC
  LIMIT
    2
)
(
  SELECT
    name,
    price_per_l,
    NULL AS alcohol_degree,
    NULL AS brewing_date
  FROM
    cte0
)
UNION
ALL (
  SELECT
    name,
    NULL AS price_per_l,
    alcohol_degree,
    NULL AS brewing_date
  FROM
    cte1
)
UNION
ALL (
  SELECT
    name,
    NULL AS price_per_l,
    NULL AS alcohol_degree,
    brewing_date
  FROM
    cte2
)

MVCE confirmation

• Minimal example
• New issue

Anything else?

Related to #5341.
Unfortunately, Google BigQuery is even more picky, and needs both subqueries and CTEs to succesfully infer the types here.