IssueTracker

Hello,

This issue report has been forwarded to the Cloud BigQuery Product team so that they may investigate it, but there is no ETA for a resolution today. Future updates regarding this issue will be provided here.

Hi,

To troubleshoot the issue further, I have created a private ticket to provide some information about the issue (for which you should have received a notification). Please provide requested information there. Don't put any personal information, including project identifiers in this public ticket.

Hello,

Thank you for your response.

Can you provide the dataset in order to reproduce this in my environment? It will be really helpful in investigating this issue.

Apologies, it looks like the above query does evaluate if at least 1 filter of the WHERE clause does not use RANGE_CONTAINS(). See the below query for a better example of the behavior:

CREATE OR REPLACE TABLE `test_project.test_dataset.test_table` (TEST_DATE DATE)
 PARTITION BY TEST_DATE
 OPTIONS(require_partition_filter = TRUE);

INSERT INTO `test_project.test_dataset.test_table`
SELECT * FROM UNNEST(GENERATE_DATE_ARRAY(DATE '2024-01-01', DATE '2024-04-01'));

-- This query appears to succeed because at least 1 filter compares require_partition_filter TEST_DATE to a DATE type.
-- Even though RANGE_END(RANGE(DATE '9998-12-31', DATE '9999-12-31')) uses a RANGE, RANGE_END returns a DATE type.
-- This query returns 31 rows, 1 row for each day within RANGE(DATE '2024-01-01', DATE'2024-02-01')
SELECT * FROM `test_project.test_dataset.test_table`
WHERE RANGE_CONTAINS(RANGE(DATE '2024-01-01', DATE'2024-02-01'),TEST_DATE)
  OR RANGE_END(RANGE(DATE '9998-12-31', DATE '9999-12-31')) = TEST_DATE
  OR RANGE_END(RANGE(DATE '9998-12-31', DATE '9999-12-31')) = TEST_DATE

-- This query appears to fail because there are no filters return a required_partition_filter TEST_DATE to a DATE type.
-- CAST(RANGE_END(RANGE(DATE '9998-12-31', NULL)) AS DATE) returns a NULL DATE type, which does not meet the required_partition_filter condition.
-- This query returns the error "Cannot query over table 'test_project.test_dataset.test_table' without a filter
-- over column(s) 'TEST_DATE' that can be used for partition elimination"
SELECT * FROM `test_project.test_dataset.test_table`
WHERE RANGE_CONTAINS(RANGE(DATE '2024-01-01', DATE'2024-02-01'),TEST_DATE)
  OR CAST(RANGE_END(RANGE(DATE '9998-12-31', NULL)) AS DATE) = TEST_DATE

Since RANGE_CONTAINS() does correctly evaluate if another condition compares the date partition column against a DATE type, my guess is that require_partition_filter's behavior looks for any condition filtering the partitioned column that returns a non-NULL DATE type. DATE RANGE_CONTAINS can return 2 different types: a RANGE if the arguments are (RANGE,RANGE) or DATE if the arguments are (RANGE, DATE). RANGE_CONTAINS should be whitelisted; DATE RANGES are currently not supported for PARTITIONING, so the only arguments available to a RANGE_CONTAINS() filter can be (RANGE, DATE)

Hello,

Thank you for reaching out to us with your request.

We have duly noted your feedback and will thoroughly validate it. While we cannot provide an estimated time of implementation or guarantee the fulfillment of the issue, please be assured that your input is highly valued. Your feedback enables us to enhance our products and services.

We appreciate your continued trust and support in improving our Google Cloud Platform products. In case you want to report a new issue, please do not hesitate to create a new issue on the Issue Tracker providing a detailed description of your issue.

Once again, we sincerely appreciate your valuable feedback; Thank you for your understanding and collaboration.