dosubot[bot] commented on issue #36676: URL: https://github.com/apache/superset/issues/36676#issuecomment-3661245647
<!-- Answer --> Superset's filter value queries for native filters (used in chart creation and dashboards) do not automatically apply time filters or partition filters, even if you have set a default datetime column or enabled `DEFAULT_TIME_FILTER`. The backend API that retrieves distinct values for filter controls simply selects from the table without including any time or partition predicates, unless you explicitly configure them in the dataset's `fetch_values_predicate` field. This is why BigQuery rejects these queries when a required partition filter is missing, and why you see no values in the filter box UI. This behavior is by design in the current codebase and matches your observations ([source](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/models/helpers.py#L1288-L2117), [source](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/datasource/api.py#L39-L125)). The only way to ensure a partition filter is always present in these queries is to add a Jinja template in the dataset's `fetch_values_predicate` that enforces a filter on your partition column (for example, always filter to the last N days, or require a time range parameter). This will apply to all filter value queries for that dataset, including those triggered by the filter box UI. On dashboards, making a filter dependent on a time filter works because the UI passes the time filter to the backend, so the generated query includes the partition filter. However, in chart creation or when the filter is not dependent, this does not happen automatically. There are no recent changes or configuration options that alter this behavior, and no indication that default time filters or chart-level filters are applied to filter value queries by default. If you want to avoid full table scans and BigQuery errors, you must enforce the partition filter via the dataset configuration ([source](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/models/helpers.py#L1288-L2117)). If you need help crafting a `fetch_values_predicate` template for your use case, let me know! <!-- Dosu Comment Footer --> *To reply, just mention [@dosu](https://go.dosu.dev/dosubot).* --- <sup>How did I do? [Good](https://app.dosu.dev/response-feedback/deaa46fa-4995-4c4f-bc99-10e015c72773?feedback_type=great_response) | [Irrelevant](https://app.dosu.dev/response-feedback/deaa46fa-4995-4c4f-bc99-10e015c72773?feedback_type=irrelevant_answer) | [Incorrect](https://app.dosu.dev/response-feedback/deaa46fa-4995-4c4f-bc99-10e015c72773?feedback_type=incorrect_sources) | [Verbose](https://app.dosu.dev/response-feedback/deaa46fa-4995-4c4f-bc99-10e015c72773?feedback_type=too_verbose) | [Hallucination](https://app.dosu.dev/response-feedback/deaa46fa-4995-4c4f-bc99-10e015c72773?feedback_type=hallucination) | [Report 🐛](https://app.dosu.dev/response-feedback/deaa46fa-4995-4c4f-bc99-10e015c72773?feedback_type=bug_report) | [Other](https://app.dosu.dev/response-feedback/deaa46fa-4995-4c4f-bc99-10e015c72773?feedback_type=other)</sup> [](https://app.dosu.dev/a28d3c7e-a9d3-459e-9fb6-3a6f9ff4f357/ask?utm_source=github) [](https://cursor.com/link/prompt?text=Superset%27s%20filter%20value%20queries%20for%20native%20filters%20%28used%20in%20chart%20creation%20and%20dashboards%29%20do%20not%20automatically%20apply%20time%20filters%20or%20partition%20filters%2C%20even%20if%20you%20have%20set%20a%20default%20datetime%20column%20or%20enabled%20%60DEFAULT_TIME_FILTER%60.%20The%20backend%20API%20that%20retrieves%20distinct%20values%20for%20filter%20controls%20simply%20selects%20from%20the%20table%20without%20including%20any%20time%20or%20partition%20predicates%2C%20unless%20you%20explicitly%20configure%20them%20in%20the%20dataset%27s%20%60fetch_values_predicate%60%20field.%20This%20is%20why%20BigQuery%20rejects%20these%20queries%20when%20a%20required%20partition%20filter%20is%20missing%2C%20and%20why%20you%20s ee%20no%20values%20in%20the%20filter%20box%20UI.%20This%20behavior%20is%20by%20design%20in%20the%20current%20codebase%20and%20matches%20your%20observations%26nbsp%3B%28%5Bsource%5D%28https%3A//github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/models/helpers.py%23L1288-L2117%29%2C%20%5Bsource%5D%28https%3A//github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/datasource/api.py%23L39-L125%29%29.%0A%0AThe%20only%20way%20to%20ensure%20a%20partition%20filter%20is%20always%20present%20in%20these%20queries%20is%20to%20add%20a%20Jinja%20template%20in%20the%20dataset%27s%20%60fetch_values_predicate%60%20that%20enforces%20a%20filter%20on%20your%20partition%20column%20%28for%20example%2C%20always%20filter%20to%20the%20last%20N%20days%2C%20or%20require%20a%20time%20range%20parameter%29.%20This%20will%20apply%20to%20all%20filter%20value%20queries%20for%20that%20dataset%2C%20including%20those%20triggered%20by%20the%20filter%20box%20UI.% 0A%0AOn%20dashboards%2C%20making%20a%20filter%20dependent%20on%20a%20time%20filter%20works%20because%20the%20UI%20passes%20the%20time%20filter%20to%20the%20backend%2C%20so%20the%20generated%20query%20includes%20the%20partition%20filter.%20However%2C%20in%20chart%20creation%20or%20when%20the%20filter%20is%20not%20dependent%2C%20this%20does%20not%20happen%20automatically.%0A%0AThere%20are%20no%20recent%20changes%20or%20configuration%20options%20that%20alter%20this%20behavior%2C%20and%20no%20indication%20that%20default%20time%20filters%20or%20chart-level%20filters%20are%20applied%20to%20filter%20value%20queries%20by%20default.%20If%20you%20want%20to%20avoid%20full%20table%20scans%20and%20BigQuery%20errors%2C%20you%20must%20enforce%20the%20partition%20filter%20via%20the%20dataset%20configuration%26nbsp%3B%28%5Bsource%5D%28https%3A//github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/models/helpers.py%23L1288-L2117%29%29.%0A%0AIf%20you%20need%20help%20crafti ng%20a%20%60fetch_values_predicate%60%20template%20for%20your%20use%20case%2C%20let%20me%20know%21) [](https://go.dosu.dev/discord-bot) [](https://twitter.com/intent/tweet?text=%40dosu_ai%20helped%20me%20solve%20this%20issue!&url=https%3A//github.com/apache/superset/issues/36676) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
