Filter required partition field n unnest array of date-CodePudding

I need to interrogate my table partitioned by a required transactionDate field.

I want to filter my data only on multiple dates to avoid recovering a lot of data but I have this error : Query error: Cannot query over table t without a filter over column(s) 'transactionDate' that can be used for partition elimination

My code :

DECLARE dates ARRAY < DATE >;

SET
dates = (
    SELECT
        ARRAY_AGG(DISTINCT(DATE(transactionDate)))
    FROM
        t
    WHERE
        transactionDate >= "1900-01-01"
        AND analyticsUpdateDate BETWEEN "2022-05-01"
        AND "2022-05-10"
);

SELECT * FROM t WHERE transactionDate IN UNNEST(dates)

How can I manage this error while not querying the whole table?

Thanks for your help.

CodePudding user response：

When dates is empty, transactionDate IN UNNEST(dates) condition will be false and your query will return an error:

Cannot query over table 'testset.t' without a filter over column(s) 'transactionDate' that can be used for partition elimination

DECLARE dates ARRAY <DATE>;

-- Sample table
CREATE SCHEMA IF NOT EXISTS testset;
CREATE OR REPLACE TABLE testset.t PARTITION BY (transactionDate) OPTIONS (require_partition_filter = true) AS
SELECT n, p AS transactionDate, p AS analyticsUpdateDate
  FROM UNNEST(GENERATE_ARRAY(0, 9)) n WITH OFFSET 
 CROSS JOIN UNNEST(GENERATE_DATE_ARRAY('2022-04-01', '2022-04-10')) p WITH OFFSET USING(OFFSET)
;

-- Your Query will retrun an error because `dates` is empty.
SET dates = (
    SELECT
        ARRAY_AGG(DISTINCT(DATE(transactionDate)))
    FROM
        testset.t
    WHERE
        transactionDate >= "1900-01-01"
        AND analyticsUpdateDate BETWEEN "2022-05-01"
        AND "2022-05-10"
);

SELECT * FROM testset.t WHERE transactionDate IN UNNEST(dates);

Simple workaround for this is to add IS NOT NULL condition on the partition column like below.

SELECT * FROM testset.t 
 WHERE transactionDate IS NOT NULL AND transactionDate IN UNNEST(dates);