Bug description

In Superset, the "Drill to details by" feature does not apply filters correctly when generating the SQL query. If used without any filters, all raw data is displayed correctly. However, when filters are applied and the feature is used, the generated SQL query does not include the filters, resulting in the display of raw records instead of the filtered records in the drill down.

How to reproduce the bug

  1. Create a Virtual Dataset: sql SalesDataset: SELECT region, customer_name, sales_usd FROM public.sales WHERE 1 = 1 {% if filter_values('company')|length %} AND company IN {{ filter_values('company')|where_in }} {% endif %}

  2. Create a Virtual Dataset for Filter: sql CompanyFilterDataSet: SELECT DISTINCT company AS company FROM public.sales

  3. Create a Pivot Table Chart:

    • DataSet: SalesDataset
    • Rows: region
    • Column: customer_name
    • Metric: count(sales_usd)

  4. Create a Dashboard Filter:

    • **Source: CompanyFilterDataSet
    • **Column: company

  5. **Test without Applying Filter:

  6. **Use the "Drill to details by all" feature.
  7. **The drill down shows the correct number of records.
  8. Apply Filter and Test Again:
  9. **Apply a filter using the CompanyFilterDataSet.
  10. **Notice that the values in the chart change according to the filter.
  11. **Use the "Drill to details by all" feature.
  12. **The drill down shows the original raw records (without the filter) instead of the filtered records.

Expected Behavior

The generated SQL query for the drill down should include the applied filters, and the drill down should display the filtered records.

Screenshots/recordings

ScreenRecording2024-05-17at5 36 03PM-ezgif com-video-to-gif-converter

Superset version

4.0.1

Python version

3.11

Node version

18 or greater

Browser

Chrome

Additional context

When creating a dashboard, I performed the following steps:

  • Created a dataset SalesDataset with a filter condition for company.
  • Created another dataset CompanyFilterDataSet for filtering companies.
  • Built a pivot table chart using SalesDataset, with region as rows, customer_name as columns, and count(sales_usd) as the metric.
  • Added a dashboard filter using CompanyFilterDataSet on the company column.
  • Initially, without applying any filters, the drill down feature shows the correct number of records. However, when a filter is applied, although the chart updates correctly, the drill down shows the original unfiltered records.

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.

Comment From: dosubot[bot]

The issue you're experiencing with the "Drill to details by" feature not applying filters correctly in Apache Superset version 4.0.1 is recognized and has been addressed in a subsequent update. Specifically, a fix related to enabling the DrillBy feature in charts without filters (dimensions) might also resolve issues related to filter application in drill-down SQL query generation. This fix is documented in the pull request #27941.

The "Drill to details by" feature's behavior, including how it handles filters in SQL query generation, is influenced by several factors, including chart configurations, user permissions, and the specific version of Superset you're using. Known limitations in version 4.0.1, such as database support, aggregations, filters requirement, and chart type support, could also contribute to the problem.

To address this issue, ensure you're running a version of Superset that includes the fix mentioned above. If the problem persists even after updating, it may be worth reviewing the specific configurations and settings related to the "Drill to details by" feature in your Superset installation, including chart type support and filter handling.

If you need further assistance or have additional questions, feel free to ask.

#### Details **Sources** - [CHANGELOG/4.0.1.md](https://github.com/apache/superset/blob/master/CHANGELOG/4.0.1.md) - [superset-frontend/src/components/Chart/DrillDetail/DrillDetailMenuItems.tsx](https://github.com/apache/superset/blob/master/superset-frontend/src/components/Chart/DrillDetail/DrillDetailMenuItems.tsx) - [superset/common/query_actions.py](https://github.com/apache/superset/blob/master/superset/common/query_actions.py) - [superset-frontend/src/components/Chart/ChartContextMenu/ChartContextMenu.tsx](https://github.com/apache/superset/blob/master/superset-frontend/src/components/Chart/ChartContextMenu/ChartContextMenu.tsx) - [superset/models/helpers.py](https://github.com/apache/superset/blob/master/superset/models/helpers.py) - [superset-frontend/packages/superset-ui-core/src/query/processFilters.ts](https://github.com/apache/superset/blob/master/superset-frontend/packages/superset-ui-core/src/query/processFilters.ts) - [superset-frontend/packages/superset-ui-core/test/query/processFilters.test.ts](https://github.com/apache/superset/blob/master/superset-frontend/packages/superset-ui-core/test/query/processFilters.test.ts) - [superset-frontend/cypress-base/cypress/e2e/dashboard/drilltodetail.test.ts](https://github.com/apache/superset/blob/master/superset-frontend/cypress-base/cypress/e2e/dashboard/drilltodetail.test.ts) - [superset-frontend/src/components/Chart/DrillDetail/DrillDetailMenuItems.test.tsx](https://github.com/apache/superset/blob/master/superset-frontend/src/components/Chart/DrillDetail/DrillDetailMenuItems.test.tsx) - [superset-frontend/cypress-base/cypress/e2e/dashboard/drillby.test.ts](https://github.com/apache/superset/blob/master/superset-frontend/cypress-base/cypress/e2e/dashboard/drillby.test.ts)
**About Dosu** This response is meant to be useful and save you time. It is **not meant to be a precise solution**, but rather a starting point for your own research. Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

To continue the conversation, mention @dosu.

Comment From: aamir4813

Hi , This is not related to the #27941 (i've checked the problem statement of this fix)

Comment From: dosubot[bot]

To resolve the issue where the "Drill to details by" feature in Apache Superset version 4.0.1 ignores applied filters, ensure that filters are correctly passed and applied both in the frontend and backend.

Frontend Changes

  1. Pass Filters to DrillDetailMenuItems Component: Ensure the filters object contains the correct filter values and is passed to the DrillDetailMenuItems component.

tsx if (showDrillToDetail) { menuItems.push( <DrillDetailMenuItems chartId={id} formData={formData} filters={filters?.drillToDetail} isContextMenu contextMenuY={clientY} onSelection={onSelection} submenuIndex={showCrossFilters ? 2 : 1} showModal={drillModalIsOpen} setShowModal={setDrillModalIsOpen} {...(additionalConfig?.drillToDetail || {})} />, ); }

  1. Verify handleContextMenu Function: Ensure the handleContextMenu function constructs the drillToDetailFilters based on the selected filters.

tsx const handleContextMenu = useCallback( ( e: MouseEvent, colKey: (string | number | boolean)[] | undefined, rowKey: (string | number | boolean)[] | undefined, dataPoint: { [key: string]: string }, ) => { if (onContextMenu) { e.preventDefault(); e.stopPropagation(); const drillToDetailFilters: BinaryQueryObjectFilterClause[] = []; if (colKey && colKey.length > 1) { colKey.forEach((val, i) => { const col = cols[i]; const formatter = dateFormatters[col]; const formattedVal = formatter?.(val as number) || String(val); if (i > 0) { drillToDetailFilters.push({ col, op: '==', val, formattedVal, grain: formatter ? timeGrainSqla : undefined, }); } }); } if (rowKey) { rowKey.forEach((val, i) => { const col = rows[i]; const formatter = dateFormatters[col]; const formattedVal = formatter?.(val as number) || String(val); drillToDetailFilters.push({ col, op: '==', val, formattedVal, grain: formatter ? timeGrainSqla : undefined, }); }); } onContextMenu(e.clientX, e.clientY, { drillToDetail: drillToDetailFilters, crossFilter: getCrossFilterDataMask(dataPoint), drillBy: dataPoint && { filters: [ { col: Object.keys(dataPoint)[0], op: '==', val: Object.values(dataPoint)[0], }, ], groupbyFieldName: rowKey ? 'groupbyRows' : 'groupbyColumns', }, }); } }, [ cols, dateFormatters, getCrossFilterDataMask, onContextMenu, rows, timeGrainSqla, ], );

Backend Changes

  1. Apply Filters in _get_drill_detail Function: Ensure the _get_drill_detail function in superset/common/query_actions.py correctly applies the filters to the query object.

py def _get_drill_detail( query_context: QueryContext, query_obj: QueryObject, force_cached: bool = False ) -> dict[str, Any]: datasource = _get_datasource(query_context, query_obj) query_obj = copy.copy(query_obj) query_obj.is_timeseries = False query_obj.orderby = [] query_obj.metrics = None query_obj.post_processing = [] qry_obj_cols = [] for o in datasource.columns: if isinstance(o, dict): qry_obj_cols.append(o.get("column_name")) else: qry_obj_cols.append(o.column_name) query_obj.columns = qry_obj_cols return _get_full(query_context, query_obj, force_cached)

  1. Check QueryContext Object: Ensure the QueryContext object handles filters correctly when generating SQL queries for drill-down details.

python def _apply_filters(self, query_object: QueryObject) -> None: if query_object.time_range: for filter_object in query_object.filter: if filter_object["op"] == "TEMPORAL_RANGE": filter_object["val"] = query_object.time_range

  1. Verify buildQuery Function: Ensure the buildQuery function in superset-frontend/src/filters/components/Select/buildQuery.ts correctly adds extra filters based on user input.

ts const buildQuery: BuildQuery<PluginFilterSelectQueryFormData> = ( formData: PluginFilterSelectQueryFormData, options, ) => { const { search, coltypeMap } = options?.ownState || {}; const { sortAscending, sortMetric } = { ...DEFAULT_FORM_DATA, ...formData }; return buildQueryContext(formData, baseQueryObject => { const { columns = [], filters = [] } = baseQueryObject; const extraFilters: QueryObjectFilterClause[] = []; if (search) { columns.filter(isPhysicalColumn).forEach(column => { const label = getColumnLabel(column); if ( coltypeMap[label] === GenericDataType.String || (coltypeMap[label] === GenericDataType.Numeric && !Number.isNaN(Number(search))) ) { extraFilters.push({ col: column, op: 'ILIKE', val: `%${search}%`, }); } }); } const sortColumns = sortMetric ? [sortMetric] : columns; const query: QueryObject[] = [ { ...baseQueryObject, columns, metrics: sortMetric ? [sortMetric] : [], filters: filters.concat(extraFilters), orderby: sortMetric || sortAscending !== undefined ? sortColumns.map(column => [column, !!sortAscending]) : [], }, ]; return query; }); };

By ensuring that filters are correctly passed and applied at each step, you should be able to resolve the issue where the "Drill to details by" feature ignores the applied filters.

#### Details **Sources** - [Drill by function error with caching enabled](https://github.com/apache/superset/issues/26471) - [superset-frontend/src/components/Chart/ChartContextMenu/ChartContextMenu.tsx](https://github.com/apache/superset/blob/master/superset-frontend/src/components/Chart/ChartContextMenu/ChartContextMenu.tsx) - [superset-frontend/plugins/plugin-chart-pivot-table/src/PivotTableChart.tsx](https://github.com/apache/superset/blob/master/superset-frontend/plugins/plugin-chart-pivot-table/src/PivotTableChart.tsx) - [superset/common/query_actions.py](https://github.com/apache/superset/blob/master/superset/common/query_actions.py) - [superset-frontend/src/components/Chart/DrillDetail/DrillDetailMenuItems.tsx](https://github.com/apache/superset/blob/master/superset-frontend/src/components/Chart/DrillDetail/DrillDetailMenuItems.tsx) - [superset-frontend/src/components/Chart/DrillDetail/DrillDetailMenuItems.test.tsx](https://github.com/apache/superset/blob/master/superset-frontend/src/components/Chart/DrillDetail/DrillDetailMenuItems.test.tsx) - [superset/common/query_context_factory.py](https://github.com/apache/superset/blob/master/superset/common/query_context_factory.py) - [superset/common/query_context.py](https://github.com/apache/superset/blob/master/superset/common/query_context.py) - [superset-frontend/src/filters/components/Select/buildQuery.ts](https://github.com/apache/superset/blob/master/superset-frontend/src/filters/components/Select/buildQuery.ts) - [tests/integration_tests/query_context_tests.py](https://github.com/apache/superset/blob/master/tests/integration_tests/query_context_tests.py) - [superset-frontend/src/filters/components/TimeColumn/buildQuery.ts](https://github.com/apache/superset/blob/master/superset-frontend/src/filters/components/TimeColumn/buildQuery.ts) - [superset-frontend/cypress-base/cypress/e2e/dashboard/drilltodetail.test.ts](https://github.com/apache/superset/blob/master/superset-frontend/cypress-base/cypress/e2e/dashboard/drilltodetail.test.ts) - [superset-frontend/cypress-base/cypress/e2e/dashboard/drillby.test.ts](https://github.com/apache/superset/blob/master/superset-frontend/cypress-base/cypress/e2e/dashboard/drillby.test.ts) - [superset/common/query_context_processor.py](https://github.com/apache/superset/blob/master/superset/common/query_context_processor.py)
**About Dosu** This response is meant to be useful and save you time. It is **not meant to be a precise solution**, but rather a starting point for your own research. Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

To continue the conversation, mention @dosu.

Comment From: suregoodru

I have a similar problem. Are there any working solutions?

Comment From: jeffreytai

this issue exists in version 3.1.3 as well

Comment From: rusackas

I'm assuming this is still an issue in 4.1.2, but if anyone can confirm or deny this, it'd be appreciated.

Comment From: rusackas

5.0.0 is out... can anyone check into whether the issue still exists there? If we don't hear back, this issue is at risk of being closed as inactive.