shalom-t opened a new issue, #36292:
URL: https://github.com/apache/superset/issues/36292
### Bug description
Issue:
When using Superset with a Snowflake data source, applying a Custom SQL
filter (e.g. LOAN_ID IN ('...')) on a chart using aggregation leads to a data
error or no results. Using the same filter with the “Simple” filter option
works correctly.
Steps to Reproduce:
1. Use Snowflake as the database backend.
2. Have a table with a column LOAN_ID (string or varchar) and a numeric
metric (e.g. PRINCIPALBALANCE).
3. Create a chart (e.g. Table visualization) in “aggregate” mode, grouping
by LOAN_ID.
4. Apply a filter on LOAN_ID using the “Custom SQL” filter type, with
condition like:
```
LOAN_ID IN ('YAPN927')
```
5. Run the query.
Expected Behavior:
Filter should behave exactly like the built-in “Simple” filter — return rows
where LOAN_ID = 'YAPN927'.
Actual Behavior:
The chart returns a data error. The generated query payload contains the
filter under extras.where, which seems incompatible with aggregation mode +
Snowflake dialect.
Additional Context / Notes:
- This works if you use the “Simple” filter UI for LOAN_ID.
- Snowflake requires proper quoting for identifiers (e.g. "LOAN_ID"), which
Superset does correctly when using simple filters, but fails when using
custom-SQL filters in this context.
- I attempted repetition with different quoting (e.g. "LOAN_ID", CAST(...),
etc.), but error persists.
- Superset version: 6.0.0
- Database: Snowflake
- This appears to be a generic issue combining: Snowflake dialect,
custom-SQL filters, aggregation charts, and Superset’s query builder logic.
Suggested Fix / Hypothesis:
- Ensure that when building SQL for custom filters + aggregation +
Snowflake, Superset quotes identifiers properly (e.g. "LOAN_ID").
- Avoid placing custom filters into extras.where / HAVING for aggregated
queries; instead inject them into WHERE clause before grouping.
Relevant Payload / Example:
Valid payload when run with "Simple" filter:
```
{
"datasource": {
"id": 8,
"type": "table"
},
"force": false,
"queries": [
{
"filters": [
{
"col": "CLOSEDDATE",
"op": "TEMPORAL_RANGE",
"val": "No filter"
},
{
"col": "LAST_REPAYMENT_DATE",
"op": "TEMPORAL_RANGE",
"val": "Current year"
},
{
"col": "LOAN_ID",
"op": "IN",
"val": [
"YAPN927"
]
}
],
"extras": {
"time_grain_sqla": "P1D",
"having": "",
"where": ""
},
"applied_time_extras": {},
"columns": [
{
"timeGrain": "P1D",
"columnType": "BASE_AXIS",
"sqlExpression": "LAST_REPAYMENT_DATE",
"label": "LAST_REPAYMENT_DATE",
"expressionType": "SQL"
},
"LOAN_ID"
],
"metrics": [
{
"aggregate": "SUM",
"column": {
"advanced_data_type": null,
"certification_details": null,
"certified_by": null,
"column_name": "PRINCIPALBALANCE",
"description": null,
"expression": null,
"filterable": true,
"groupby": true,
"id": 119,
"is_certified": false,
"is_dttm": false,
"python_date_format": null,
"type": null,
"type_generic": null,
"uuid": "9501808a-b14f-4a97-b1d7-4ef513241374",
"verbose_name": null,
"warning_markdown": null
},
"datasourceWarning": false,
"expressionType": "SIMPLE",
"hasCustomLabel": false,
"label": "SUM(PRINCIPALBALANCE)",
"optionName": "metric_0b47pprdc74q_2d7hxhmvo2",
"sqlExpression": null
}
],
"orderby": [
[
{
"aggregate": "SUM",
"column": {
"advanced_data_type": null,
"certification_details": null,
"certified_by": null,
"column_name": "PRINCIPALBALANCE",
"description": null,
"expression": null,
"filterable": true,
"groupby": true,
"id": 119,
"is_certified": false,
"is_dttm": false,
"python_date_format": null,
"type": null,
"type_generic": null,
"uuid": "9501808a-b14f-4a97-b1d7-4ef513241374",
"verbose_name": null,
"warning_markdown": null
},
"datasourceWarning": false,
"expressionType": "SIMPLE",
"hasCustomLabel": false,
"label": "SUM(PRINCIPALBALANCE)",
"optionName": "metric_0b47pprdc74q_2d7hxhmvo2",
"sqlExpression": null
},
false
]
],
"annotation_layers": [],
"row_limit": 10000,
"series_limit": 0,
"group_others_when_limit_reached": false,
"order_desc": true,
"url_params": {
"form_data_key": "0tlamyc6LXk",
"slice_id": "5"
},
"custom_params": {},
"custom_form_data": {},
"post_processing": [],
"time_offsets": []
}
],
"form_data": {
"datasource": "8__table",
"viz_type": "table",
"slice_id": 5,
"url_params": {
"form_data_key": "0tlamyc6LXk",
"slice_id": "5"
},
"query_mode": "aggregate",
"groupby": [
"LAST_REPAYMENT_DATE",
"LOAN_ID"
],
"time_grain_sqla": "P1D",
"temporal_columns_lookup": {
"CLOSEDDATE": true,
"CREATIONDATE": true,
"APPROVEDDATE": true,
"LASTMODIFIEDDATE": true,
"LAST_EXPECTED_REPAYMENT": true,
"LOAN_REPAID_DATE": true,
"LAST_REPAYMENT_DATE": true,
"DISBURSEMENTDATE": true,
"_LAST_REPAYMENT_UPDATE_DATE": true
},
"metrics": [
{
"aggregate": "SUM",
"column": {
"advanced_data_type": null,
"certification_details": null,
"certified_by": null,
"column_name": "PRINCIPALBALANCE",
"description": null,
"expression": null,
"filterable": true,
"groupby": true,
"id": 119,
"is_certified": false,
"is_dttm": false,
"python_date_format": null,
"type": null,
"type_generic": null,
"uuid": "9501808a-b14f-4a97-b1d7-4ef513241374",
"verbose_name": null,
"warning_markdown": null
},
"datasourceWarning": false,
"expressionType": "SIMPLE",
"hasCustomLabel": false,
"label": "SUM(PRINCIPALBALANCE)",
"optionName": "metric_0b47pprdc74q_2d7hxhmvo2",
"sqlExpression": null
}
],
"all_columns": [],
"percent_metrics": [],
"adhoc_filters": [
{
"expressionType": "SIMPLE",
"subject": "CLOSEDDATE",
"operator": "TEMPORAL_RANGE",
"comparator": "No filter",
"clause": "WHERE",
"sqlExpression": null,
"isExtra": false,
"isNew": false,
"datasourceWarning": false,
"filterOptionName": "filter_mkc3y3o9def_i9fe2leyva"
},
{
"expressionType": "SIMPLE",
"subject": "LAST_REPAYMENT_DATE",
"operator": "TEMPORAL_RANGE",
"comparator": "Current year",
"clause": "WHERE",
"sqlExpression": null,
"isExtra": false,
"isNew": false,
"datasourceWarning": false,
"filterOptionName": "filter_rf8ruxla9s8_j1s0kqe341q"
},
{
"expressionType": "SIMPLE",
"subject": "LOAN_ID",
"operator": "IN",
"operatorId": "IN",
"comparator": [
"YAPN927"
],
"clause": "WHERE",
"sqlExpression": null,
"isExtra": false,
"isNew": false,
"datasourceWarning": false,
"filterOptionName": "filter_8scjx5i79k3_jbr5a9lhxv"
}
],
"order_by_cols": [],
"order_desc": true,
"server_page_length": 10,
"row_limit": 10000,
"percent_metric_calculation": "row_limit",
"table_timestamp_format": "smart_date",
"allow_render_html": true,
"show_cell_bars": true,
"color_pn": true,
"comparison_color_scheme": "Green",
"comparison_type": "values",
"extra_form_data": {},
"force": false,
"result_format": "json",
"result_type": "full"
},
"result_format": "json",
"result_type": "full"
}
```
Invalid payload when using "Custom SQL":
```
{
"datasource": {
"id": 8,
"type": "table"
},
"force": false,
"queries": [
{
"filters": [
{
"col": "CLOSEDDATE",
"op": "TEMPORAL_RANGE",
"val": "No filter"
},
{
"col": "LAST_REPAYMENT_DATE",
"op": "TEMPORAL_RANGE",
"val": "Current year"
}
],
"extras": {
"having": "",
"where": "(LOAN_ID In ('YAPN927'))"
},
"applied_time_extras": {},
"columns": [
"LOAN_ID"
],
"metrics": [
{
"aggregate": "SUM",
"column": {
"advanced_data_type": null,
"certification_details": null,
"certified_by": null,
"column_name": "PRINCIPALBALANCE",
"description": null,
"expression": null,
"filterable": true,
"groupby": true,
"id": 119,
"is_certified": false,
"is_dttm": false,
"python_date_format": null,
"type": null,
"type_generic": null,
"uuid": "9501808a-b14f-4a97-b1d7-4ef513241374",
"verbose_name": null,
"warning_markdown": null
},
"datasourceWarning": false,
"expressionType": "SIMPLE",
"hasCustomLabel": false,
"label": "SUM(PRINCIPALBALANCE)",
"optionName": "metric_0b47pprdc74q_2d7hxhmvo2",
"sqlExpression": null
}
],
"orderby": [
[
{
"aggregate": "SUM",
"column": {
"advanced_data_type": null,
"certification_details": null,
"certified_by": null,
"column_name": "PRINCIPALBALANCE",
"description": null,
"expression": null,
"filterable": true,
"groupby": true,
"id": 119,
"is_certified": false,
"is_dttm": false,
"python_date_format": null,
"type": null,
"type_generic": null,
"uuid": "9501808a-b14f-4a97-b1d7-4ef513241374",
"verbose_name": null,
"warning_markdown": null
},
"datasourceWarning": false,
"expressionType": "SIMPLE",
"hasCustomLabel": false,
"label": "SUM(PRINCIPALBALANCE)",
"optionName": "metric_0b47pprdc74q_2d7hxhmvo2",
"sqlExpression": null
},
false
]
],
"annotation_layers": [],
"row_limit": 10000,
"series_limit": 0,
"group_others_when_limit_reached": false,
"order_desc": true,
"url_params": {
"slice_id": "5"
},
"custom_params": {},
"custom_form_data": {},
"post_processing": [],
"time_offsets": []
}
],
"form_data": {
"datasource": "8__table",
"viz_type": "table",
"slice_id": 5,
"url_params": {
"slice_id": "5"
},
"query_mode": "aggregate",
"groupby": [
"LOAN_ID"
],
"temporal_columns_lookup": {
"CLOSEDDATE": true,
"CREATIONDATE": true,
"APPROVEDDATE": true,
"LASTMODIFIEDDATE": true,
"LAST_EXPECTED_REPAYMENT": true,
"LOAN_REPAID_DATE": true,
"LAST_REPAYMENT_DATE": true,
"DISBURSEMENTDATE": true,
"_LAST_REPAYMENT_UPDATE_DATE": true
},
"metrics": [
{
"aggregate": "SUM",
"column": {
"advanced_data_type": null,
"certification_details": null,
"certified_by": null,
"column_name": "PRINCIPALBALANCE",
"description": null,
"expression": null,
"filterable": true,
"groupby": true,
"id": 119,
"is_certified": false,
"is_dttm": false,
"python_date_format": null,
"type": null,
"type_generic": null,
"uuid": "9501808a-b14f-4a97-b1d7-4ef513241374",
"verbose_name": null,
"warning_markdown": null
},
"datasourceWarning": false,
"expressionType": "SIMPLE",
"hasCustomLabel": false,
"label": "SUM(PRINCIPALBALANCE)",
"optionName": "metric_0b47pprdc74q_2d7hxhmvo2",
"sqlExpression": null
}
],
"all_columns": [],
"percent_metrics": [],
"adhoc_filters": [
{
"expressionType": "SIMPLE",
"subject": "CLOSEDDATE",
"operator": "TEMPORAL_RANGE",
"comparator": "No filter",
"clause": "WHERE",
"sqlExpression": null,
"isExtra": false,
"isNew": false,
"datasourceWarning": false,
"filterOptionName": "filter_mkc3y3o9def_i9fe2leyva"
},
{
"expressionType": "SIMPLE",
"subject": "LAST_REPAYMENT_DATE",
"operator": "TEMPORAL_RANGE",
"comparator": "Current year",
"clause": "WHERE",
"sqlExpression": null,
"isExtra": false,
"isNew": false,
"datasourceWarning": false,
"filterOptionName": "filter_rf8ruxla9s8_j1s0kqe341q"
},
{
"expressionType": "SQL",
"sqlExpression": "LOAN_ID In ('YAPN927')",
"clause": "WHERE",
"subject": null,
"operator": null,
"comparator": null,
"isExtra": false,
"isNew": false,
"datasourceWarning": false,
"filterOptionName": "filter_fai50u42ycu_3v0yosy8u15"
}
],
"order_by_cols": [],
"row_limit": 10000,
"percent_metric_calculation": "row_limit",
"table_timestamp_format": "smart_date",
"allow_render_html": true,
"show_cell_bars": true,
"color_pn": true,
"comparison_color_scheme": "Green",
"comparison_type": "values",
"extra_form_data": {},
"force": false,
"result_format": "json",
"result_type": "full"
},
"result_format": "json",
"result_type": "full"
}
```
Why this matters:
This bug prevents using custom filters reliably on Snowflake when
aggregation is involved — significantly reducing Superset’s usability with
Snowflake for filtered/aggregated dashboards.
### Screenshots/recordings
"Simple" filter:
<img width="1917" height="989" alt="Image"
src="https://github.com/user-attachments/assets/027efcd3-ecf2-4a6b-bf1d-321cb153defe";
/>
"Custom SQL" filter
<img width="1915" height="981" alt="Image"
src="https://github.com/user-attachments/assets/f650403e-58b3-4520-a9f0-340e86101e0a";
/>
### Superset version
master / latest-dev
### Python version
3.9
### Node version
16
### Browser
Chrome
### Additional context
_No response_
### Checklist
- [x] I have searched Superset docs and Slack and didn't find a solution to
my problem.
- [x] I have searched the GitHub issue tracker and didn't find a similar bug
report.
- [x] I have checked Superset's logs for errors and if I found a relevant
Python stacktrace, I included it here as text in the "additional context"
section.
--
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]
