"Drill to Details by" Feature Ignores Applied Filters in Superset 4.0.1 #28562

aamir4813 · 2024-05-17T12:17:37Z

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

Create a Virtual Dataset:

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 %}

Create a Virtual Dataset for Filter:

CompanyFilterDataSet:
SELECT DISTINCT company AS company
FROM public.sales

Create a Pivot Table Chart:
- DataSet: SalesDataset
- Rows: region
- Column: customer_name
- Metric: count(sales_usd)
Create a Dashboard Filter:
- **Source: CompanyFilterDataSet
- **Column: company
**Test without Applying Filter:
- **Use the "Drill to details by all" feature.
- **The drill down shows the correct number of records.
Apply Filter and Test Again:
- **Apply a filter using the CompanyFilterDataSet.
- **Notice that the values in the chart change according to the filter.
- **Use the "Drill to details by all" feature.
- **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

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

I have searched Superset docs and Slack and didn't find a solution to my problem.
I have searched the GitHub issue tracker and didn't find a similar bug report.
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.

The text was updated successfully, but these errors were encountered:

dosubot · 2024-05-17T13:14:32Z

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

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.

aamir4813 · 2024-05-21T07:49:41Z

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

dosubot · 2024-05-21T08:00:24Z

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

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

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 || {})}
      />,
    );
  }

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

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

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.

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)

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

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

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

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

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.

dosubot bot added the dashboard:drill-down Related to drill-down functionality of the Dashboard label May 17, 2024

aamir4813 changed the title ~~"Drill to Details by" Feature Ignores Applied Filters in Superset 4.0.0~~ "Drill to Details by" Feature Ignores Applied Filters in Superset 4.0.1 May 17, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

"Drill to Details by" Feature Ignores Applied Filters in Superset 4.0.1 #28562

"Drill to Details by" Feature Ignores Applied Filters in Superset 4.0.1 #28562

aamir4813 commented May 17, 2024 •

edited

dosubot bot commented May 17, 2024

Details

aamir4813 commented May 21, 2024

dosubot bot commented May 21, 2024

Details

"Drill to Details by" Feature Ignores Applied Filters in Superset 4.0.1 #28562

"Drill to Details by" Feature Ignores Applied Filters in Superset 4.0.1 #28562

Comments

aamir4813 commented May 17, 2024 • edited

Bug description

How to reproduce the bug

Expected Behavior

Screenshots/recordings

Superset version

Python version

Node version

Browser

Additional context

When creating a dashboard, I performed the following steps:

Checklist

dosubot bot commented May 17, 2024

Details

aamir4813 commented May 21, 2024

dosubot bot commented May 21, 2024

Frontend Changes

Backend Changes

Details

aamir4813 commented May 17, 2024 •

edited