Bigquery apply subquery to partition time

Question

I have two queries which work correct separately but together there is an error:

WITH minimum_time AS 
(
    SELECT DATE (min(_PARTITIONTIME)) AS minimums
    FROM `Day`
    WHERE DATE (_PARTITIONTIME) = "2020-11-20"
)
SELECT *
FROM `Day`
WHERE DATE (_PARTITIONTIME) > (SELECT minimums
                               FROM minimum_time)

and I get this error:

Cannot query over table 'Day' without a filter over column(s) '_PARTITION_LOAD_TIME', '_PARTITIONDATE', '_PARTITIONTIME' that can be used for partition elimination

I do not quite understand why this is happening, first query returns a date.

Yun Zhang Yun Zhang · Accepted Answer · 2020-11-27T19:27:39

You were getting the error because:

the table has the option set: require_partition_filter=true, a query on the table should fail if no partition filter is specified.
There is limitation on using subquery as the partition filter, the limitation is documented here.

In general, partition pruning will reduce query cost when the filters can be evaluated at the outset of the query without requiring any subquery evaluations or data scans.

The workaround is using BigQuery Scripting to pre-determine the partition filter, like:

DECLARE minimums DATE DEFAULT ((SELECT minimums FROM `Day` WHERE ...));
SELECT *
FROM `Day`
WHERE DATE (_PARTITIONTIME) > minimums; -- minimums is a constant to the second query

Bigquery apply subquery to partition time

1 Answers