Broken use cases appliying native & cross filters on common columns

[fbgeobit]

Bug description

As commented on issue #30438 by @Mainer-g00t, proposed fix broken our usual use case. We are providing service to customers with setup described below.
To deal with high cardinality tables on our database, is not an option for us showing all dimensions we need, so:

1. we split data on as many datasets as dimensions we need to show.
2. we create datasets with a custom jinja snippet, same on all datasets we want to filter.
3. we configure cross-filters to work on desired charts.

Result: by implementing that, we are able to filter several charts minimizing high dimensionality derived issues, increasing queries speed.

We provide tow gifts showing that configuration working on:

• Superset v 4.0.2: works well
• Superset v4.0.1rc3: broken our approach

Please, consider our use case.

Screenshots/recordings

• Supserset v4.0.2
  

• Supserset v4.1.0rc3
  

Superset version

master / latest-dev

Python version

3.9

Node version

16

Browser

Not applicable

Additional context

Configuration file: superset/docker/pythonpath_dev/superset_config.py

# CUSTOM FEATURE FLAGS
FEATURE_FLAGS = {"ALERT_REPORTS": True,
"ENABLE_TEMPLATE_PROCESSING": True,
"ENABLE_TEMPLATE_REMOVE_FILTERS": True,
"DASHBOARD_NATIVE_FILTERS": True,
"DASHBOARD_CROSS_FILTERS": True,
"EMBEDDED_SUPERSET": True,
"DASHBOARD_RBAC": True,
"DRILL_TO_DETAIL": True,
"HORIZONTAL_FILTER_BAR": True,
"ALERTS_ATTACH_REPORTS": True,
"VERSIONED_EXPORT": True}

# CUSTOM FEATURES
SESSION_COOKIE_SAMESITE = 'Lax'
SESSION_COOKIE_SECURE = False
SESSION_COOKIE_HTTPONLY = False
GUEST_ROLE_NAME = "Admin"
WTF_CSRF_ENABLED = False
ENABLE_PROXY_FIX = False
TALISMAN_ENABLED = False
GUEST_TOKEN_JWT_EXP_SECONDS = 28880

Sample datasets

sample_sales_a_product_name

--{# Comment it out #}

  {% set from_dttm = '2022-01-01T00:00:00' %}
  {% set to_dttm   = '2022-01-04T04:00:00' %}

{%
  set filters = {
      'product_name'          : filter_values('product_namedistribution_country'),
      'distribution_country'  : filter_values('distribution_country')
    } 
%}



SELECT
  product_name,
  SUM(sales_amount) AS sales_amount
FROM 
  asd.sample_sales_data
WHERE
  -- from datetime
  {% if from_dttm is not none %}
      sale_date >= '{{ from_dttm }}'::DATE
  {% else %}
      AND sale_date >= (DATE_TRUNC('DAY', CURRENT_DATE ) - INTERVAL '2 DAYS')
  {% endif %}
    
    -- to datetime
  {% if to_dttm is not none %}
      AND sale_date < '{{ to_dttm }}'::DATE
  {% else %}
      AND sale_date < (DATE_TRUNC('DAY', CURRENT_DATE))
  {% endif %}
  
  {% for key, values in filters.items() if values %}
   AND {{ key }} IN ('{{ values | join("','") }}')
  {% endfor %}
GROUP BY 1

Sample datasets

sample_sales_d_disitribution_country

--{# Comment it out #}

  {% set from_dttm = '2022-01-01T00:00:00' %}
  {% set to_dttm   = '2022-01-04T04:00:00' %}

{%
  set filters = {
      'product_name'          : filter_values('product_namedistribution_country'),
      'distribution_country'  : filter_values('distribution_country')
    } 
%}



SELECT
  distribution_country,
  SUM(sales_amount) AS sales_amount
FROM 
  asd.sample_sales_data
WHERE
  -- from datetime
  {% if from_dttm is not none %}
      sale_date >= '{{ from_dttm }}'::DATE
  {% else %}
      AND sale_date >= (DATE_TRUNC('DAY', CURRENT_DATE ) - INTERVAL '2 DAYS')
  {% endif %}
    
    -- to datetime
  {% if to_dttm is not none %}
      AND sale_date < '{{ to_dttm }}'::DATE
  {% else %}
      AND sale_date < (DATE_TRUNC('DAY', CURRENT_DATE))
  {% endif %}
  
  {% for key, values in filters.items() if values %}
   AND {{ key }} IN ('{{ values | join("','") }}')
  {% endfor %}
GROUP BY 1

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.

[dosubot]

Hey @fbgeobit! I'm here to help you tackle any bugs, answer questions, and guide you on your journey to becoming a contributor. Let's get to the bottom of this together!

I found a similar closed issue related to native filter errors in Apache Superset, which might be relevant to your situation. You can check out the details in this issue ^[1].

Regarding the specific problem you're facing with cross-filters and Jinja snippets, it seems that the changes introduced in pull request #30438 have disrupted your use case, particularly when dealing with high cardinality datasets. Unfortunately, I couldn't find specific details about the changes in that pull request that might have caused this issue. It might be helpful to review the pull request's discussion and code changes to identify what might have affected your setup.

To continue talking to Dosu, mention @dosu.