BigQuery standard SQL table wildcards with date columns

Question

I'm getting an error when using a date column in the BigQuery web UI, with a query in standard SQL, when using table wildcards.

This works:

#standardSQL
SELECT 
  sale_item,
  date_of_sale
FROM `my-project.past_sales.sales_20170601`
limit 100

This does not:

#standardSQL
SELECT 
  sale_item,
  date_of_sale
FROM `my-project.past_sales.sales_*`
WHERE _TABLE_SUFFIX BETWEEN "20170530" AND "20170601"
limit 100

The column date_of_sale is type DATE, while sale_item is type STRING. The error is: Error: Invalid adaptation for field: date_of_sale (message Msg_0_CLOUD_QUERY_TABLE). Cannot change from TYPE_STRING to int32

Elliott Brossard Elliott Brossard · Accepted Answer · 2017-06-08T22:17:01

This means that date_of_sale is a DATE in one table and a STRING in another. You can find the culprit by taking a union instead:

#standardSQL
SELECT * FROM `my-project.past_sales.sales_20170530` UNION ALL
SELECT * FROM `my-project.past_sales.sales_20170531` UNION ALL
SELECT * FROM `my-project.past_sales.sales_20170601`;

You don't actually need to run the query; the validator in the UI will indicate where the mismatched column type is.

To fix the error, you can either regenerate the data for the problematic day, applying a cast, or you can run a query with a union instead where you add an explicit cast. For example, supposing that the table for 20170531 has the column as a STRING, you could do:

#standardSQL
SELECT 
  sale_item,
  date_of_sale
FROM (
  SELECT *
  FROM `my-project.past_sales.sales_*`
  WHERE _TABLE_SUFFIX BETWEEN "20170530" AND "20170601" AND
    _TABLE_SUFFIX != 20170531
) UNION ALL
SELECT * REPLACE(CAST(date_of_sale AS DATE) AS date_of_sale)
FROM `my-project.past_sales.sales_20170531`
LIMIT 100;

BigQuery standard SQL table wildcards with date columns

1 Answers