BigQuery - FULL OUTER JOIN with USING causes casting error
I believe the way BQ is compiling your CTEs, the CAST(a_value as DATE)
in your final WHERE
clause is trying to be evaluated as if it were right after your self join and is causing a conversion/casting error before the other filters are applied.
I find it helpful to do as much casting as early as possible:
WITH
RawData AS (...),
Joined AS (
SELECT
IFNULL(a.type, b.type) AS type,
SAFE_CAST(a.value AS DATE) AS a_date, -- Handle casting ASAP
SAFE_CAST(b.value AS DATE) AS b_date
FROM RawData AS a
FULL OUTER JOIN RawData AS b USING (type)
),
Filtered AS (
SELECT * FROM Joined WHERE type = 'date'
)
SELECT * FROM Filtered
WHERE a_date BETWEEN '2020-12-01' AND '2020-12-31'
AND b_date BETWEEN '2020-12-01' AND '2020-12-31'
Even better would be to filter your table before you do a join so it is joining fewer rows!
WITH
RawData AS (...),
Filtered AS (
SELECT
*, SAFE_CAST(value AS DATE) AS date_value -- Handle casting ASAP
FROM RawData
WHERE type = 'date' -- Reduce Size Early On!
AND SAFE_CAST(value as DATE) BETWEEN '2020-12-01' AND '2020-12-31' -- Only do it 1 time here since it is the same range!
),
Joined AS (
SELECT
COALESCE(a.type, b.type) AS type,
a.date_value AS a_date,
b.date_value AS b_date
FROM Filtered AS a
FULL OUTER JOIN Filtered AS b USING (type)
)
SELECT * FROM Joined