Bigquery unit tests with partial inputs fail on uncoercable types

[z3z1ma]

The UNNEST([STRUCT(...), ...]) statement thats generated in BQ does not pass in any types. This causes all elements to be inferred as a number type, INT64 I think. The CAST that then happens in the SELECT statement fails on uncoercible types. Most obviously with DATE / TIMESTAMP. You can reproduce with this:

select cast(x as timestamp) from unnest([null]) as x

Action:

select cast(x as timestamp) from unnest([cast(null as timestamp)]) as x

Adding a cast to just the first index of the [...] array is sufficient for BQ to type it correctly. With or without the outer CAST which would then be a no-op. A real life query generated by the UT framework may look like this:

SELECT CAST(`id` AS STRING), CAST(`opp1` AS STRING), CAST(`sort_order` AS INT64), ... 
FROM UNNEST([STRUCT('line1' AS `id`, 'opp1' AS `opportunity_id`, NULL AS `sort_order`, NULL AS `pricebook_entry_id`, 'product1' AS `product2_id`, NULL AS `product_code`, NULL AS `name`, 1 AS `quantity`, NULL AS `discount`, NULL AS `subtotal`, NULL AS `total_price`, NULL AS `unit_price`, NULL AS `list_price`, NULL AS `service_date`, NULL AS `description`, NULL AS `created_date`, NULL AS `created_by_id`,  ...