Tme filter applied twice in virtual dataset even with get_time_filter(remove_filter=True)

[nikitasavelyev425]

Bug description

Not duplicate of #31365!

1. Create virtual dataset that applies time filter in inner query using get_time_filter macro
   Example query:

{% set time_filter = get_time_filter("dttm", remove_filter=True, target_type='DATE') %}
{% set time_filter2 = get_time_filter("dttm", remove_filter=True, target_type='DATE') %}
{% set from_expr = time_filter.from_expr %}
{% set to_expr = time_filter.to_expr %}
WITH filtered_trans AS(
    SELECT
        etm.amount / etm.exchange_rate AS amount_usdt,
        etm.amount / 1000000 AS amount_fiat,
        etm.final_status_timestamp::DATE "uct"
    FROM public.external_transaction_model etm
    WHERE etm.status = 'accept'
        AND etm.exchange_rate != 0
        {% if from_expr %}AND etm.final_status_timestamp::DATE >= {{ from_expr }}{% endif %}
        {% if to_expr %}AND etm.final_status_timestamp::DATE < {{ to_expr }}{% endif %}
        {% if get_filters('geo_fltr')|length > 0 %}AND etm.currency_id IN{{get_filters('geo_fltr', remove_filter=True)[0].get('val')|where_in}}{% endif %}
        {% if get_filters('direction_fltr')|length > 0 %}AND etm.direction = '{{get_filters('direction_fltr', remove_filter=True)[0].get('val')}}'{% endif %}
),
filtered_trust AS(
    SELECT
        ubcm.create_timestamp::DATE AS uct,
        SUM(ubcm.trust_balance) / 1000000 AS trust_balance
    FROM public.external_transaction_model etm
    JOIN public.user_balance_change_model ubcm
        on etm.id = transaction_id
        AND ubcm.user_id = '5f34d784-cf37-4cc2-830b-793d84580f84'
    WHERE etm.status = 'accept'
        AND etm.exchange_rate != 0
        {% if from_expr %}AND ubcm.create_timestamp::DATE >= {{ from_expr }}{% endif %}
        {% if to_expr %}AND   ubcm.create_timestamp::DATE < {{ to_expr }}{% endif %}
        {% if get_filters('geo_fltr')|length > 0 %}AND etm.currency_id IN{{get_filters('geo_fltr', remove_filter=True)[0].get('val')|where_in}}{% endif %}
        {% if get_filters('direction_fltr')|length > 0 %}AND etm.direction = '{{get_filters('direction_fltr', remove_filter=True)[0].get('val')}}'{% endif %}

    GROUP BY ubcm.create_timestamp::DATE
),
aggregated AS(
    SELECT
        trans.uct                                                                                         AS day,
        SUM(trans.amount_usdt)                                                                            AS "общий объем",
        SUM(CASE WHEN trans.amount_fiat >= 1000 AND trans.amount_fiat < 2001  THEN trans.amount_usdt END) AS "1000-2000",
        SUM(CASE WHEN trans.amount_fiat >= 2001 AND trans.amount_fiat < 3001  THEN trans.amount_usdt END) AS "2001-3000",
        SUM(CASE WHEN trans.amount_fiat >= 3001 AND trans.amount_fiat < 4001  THEN trans.amount_usdt END) AS "3001-4000",
        SUM(CASE WHEN trans.amount_fiat >= 4001 AND trans.amount_fiat < 5001  THEN trans.amount_usdt END) AS "4001-5000",
        SUM(CASE WHEN trans.amount_fiat >= 5001 AND trans.amount_fiat < 6001  THEN trans.amount_usdt END) AS "5001-6000",
        SUM(CASE WHEN trans.amount_fiat >= 6001 AND trans.amount_fiat < 7001  THEN trans.amount_usdt END) AS "6001-7000",
        SUM(CASE WHEN trans.amount_fiat >= 7001 AND trans.amount_fiat < 8001  THEN trans.amount_usdt END) AS "7001-8000",
        SUM(CASE WHEN trans.amount_fiat >= 8001                               THEN trans.amount_usdt END) AS "8001-inf",
        SUM(CASE WHEN trans.amount_fiat >= 1001 AND trans.amount_fiat < 20001 THEN trans.amount_usdt END) AS "1001-20000",
        SUM(CASE WHEN trans.amount_fiat >= 20001                              THEN trans.amount_usdt END) AS "20001-inf"
    FROM filtered_trans trans
    GROUP BY trans.uct
    ORDER BY trans.uct
)
SELECT
    day,
    "общий объем",
    "1000-2000",
    "2001-3000",
    "3001-4000",
    "4001-5000",
    "5001-6000",
    "6001-7000",
    "7001-8000",
    "8001-inf",
    "1001-20000",
    "20001-inf",
    trust.trust_balance AS "прибыль",
    CASE
        WHEN "общий объем" = 0
            THEN 0
        ELSE round(trust.trust_balance / "общий объем" * 100, 2)
    END AS "маржинальность",
    '' AS direction_fltr
FROM aggregated K
LEFT JOIN filtered_trust trust
    ON trust.uct = K.day

2. Create simple RAW_RECORDS chart using new dataset

3. Add new chart to dashboard with time filter and see the resulting query
Time filter applies twice

SELECT day AS day, "общий объем" AS "общий объем", "1000-2000" AS "1000-2000", "2001-3000" AS "2001-3000", "3001-4000" AS "3001-4000", "4001-5000" AS "4001-5000", "5001-6000" AS "5001-6000", "6001-7000" AS "6001-7000", "7001-8000" AS "7001-8000", "8001-inf" AS "8001-inf", "1001-20000" AS "1001-20000", "20001-inf" AS "20001-inf", "прибыль" AS "прибыль", "маржинальность" AS "маржинальность" 
FROM (



WITH filtered_trans AS(
    SELECT
        etm.amount / etm.exchange_rate AS amount_usdt,
        etm.amount / 1000000 AS amount_fiat,
        etm.final_status_timestamp::DATE "uct"
    FROM public.external_transaction_model etm
    WHERE etm.status = 'accept'
        AND etm.exchange_rate != 0
        AND etm.final_status_timestamp::DATE >= TO_DATE('2025-07-28', 'YYYY-MM-DD')
        AND etm.final_status_timestamp::DATE < TO_DATE('2025-08-28', 'YYYY-MM-DD')
        AND etm.currency_id IN('RUB')
        AND etm.direction = 'outbound'
),
filtered_trust AS(
    SELECT
        ubcm.create_timestamp::DATE AS uct,
        SUM(ubcm.trust_balance) / 1000000 AS trust_balance
    FROM public.external_transaction_model etm
    JOIN public.user_balance_change_model ubcm
        on etm.id = transaction_id
        AND ubcm.user_id = '5f34d784-cf37-4cc2-830b-793d84580f84'
    WHERE etm.status = 'accept'
        AND etm.exchange_rate != 0
        AND ubcm.create_timestamp::DATE >= TO_DATE('2025-07-28', 'YYYY-MM-DD')
        AND   ubcm.create_timestamp::DATE < TO_DATE('2025-08-28', 'YYYY-MM-DD')
        AND etm.currency_id IN('RUB')
        AND etm.direction = 'outbound'

    GROUP BY ubcm.create_timestamp::DATE
),
aggregated AS(
    SELECT
        trans.uct                                                                                         AS day,
        SUM(trans.amount_usdt)                                                                            AS "общий объем",
        SUM(CASE WHEN trans.amount_fiat >= 1000 AND trans.amount_fiat < 2001  THEN trans.amount_usdt END) AS "1000-2000",
        SUM(CASE WHEN trans.amount_fiat >= 2001 AND trans.amount_fiat < 3001  THEN trans.amount_usdt END) AS "2001-3000",
        SUM(CASE WHEN trans.amount_fiat >= 3001 AND trans.amount_fiat < 4001  THEN trans.amount_usdt END) AS "3001-4000",
        SUM(CASE WHEN trans.amount_fiat >= 4001 AND trans.amount_fiat < 5001  THEN trans.amount_usdt END) AS "4001-5000",
        SUM(CASE WHEN trans.amount_fiat >= 5001 AND trans.amount_fiat < 6001  THEN trans.amount_usdt END) AS "5001-6000",
        SUM(CASE WHEN trans.amount_fiat >= 6001 AND trans.amount_fiat < 7001  THEN trans.amount_usdt END) AS "6001-7000",
        SUM(CASE WHEN trans.amount_fiat >= 7001 AND trans.amount_fiat < 8001  THEN trans.amount_usdt END) AS "7001-8000",
        SUM(CASE WHEN trans.amount_fiat >= 8001                               THEN trans.amount_usdt END) AS "8001-inf",
        SUM(CASE WHEN trans.amount_fiat >= 1001 AND trans.amount_fiat < 20001 THEN trans.amount_usdt END) AS "1001-20000",
        SUM(CASE WHEN trans.amount_fiat >= 20001                              THEN trans.amount_usdt END) AS "20001-inf"
    FROM filtered_trans trans
    GROUP BY trans.uct
    ORDER BY trans.uct
)
SELECT
    day,
    "общий объем",
    "1000-2000",
    "2001-3000",
    "3001-4000",
    "4001-5000",
    "5001-6000",
    "6001-7000",
    "7001-8000",
    "8001-inf",
    "1001-20000",
    "20001-inf",
    trust.trust_balance AS "прибыль",
    CASE
        WHEN "общий объем" = 0
            THEN 0
        ELSE round(trust.trust_balance / "общий объем" * 100, 2)
    END AS "маржинальность",
    '' AS direction_fltr
FROM aggregated K
LEFT JOIN filtered_trust trust
    ON trust.uct = K.day
) AS virtual_table 
WHERE day >= TO_DATE('2025-07-28', 'YYYY-MM-DD') AND day < TO_DATE('2025-08-28', 'YYYY-MM-DD') 
 LIMIT 1000;

Screenshots/recordings

No response

Superset version

5.0.0

Python version

3.10

Node version

I don't know

Browser

Firefox

Additional context

Dataset with following query works fine, filter applies only in inner query

{% set time_filter = get_time_filter("dttm", remove_filter=True, target_type='DATE') %}
{% set from_expr = time_filter.from_expr %}
{% set to_expr = time_filter.to_expr %}
WITH filtered_data AS(
    SELECT
        etm.id,
        etm.create_timestamp,
        etm.final_status_timestamp,
        etm.amount,
        etm.status,
        etm.exchange_rate,
        EXISTS(
            SELECT
            FROM public.appeals a
            WHERE a.transaction_id = etm.id
        ) AS appeal
    FROM public.external_transaction_model etm
{% if get_filters('merch_fltr')|length > 0 %}
    JOIN public.user_model um_m
        ON um_m.id = etm.merchant_id
        AND um_m.role = 'merchant'
        AND um_m.name IN{{get_filters('merch_fltr', remove_filter=True)[0].get('val')|where_in}}
{% endif %}
{% if get_filters('team_fltr')|length > 0 %}
    JOIN public.user_model um_t
        ON um_t.id = etm.team_id
        AND um_t.role = 'team'
        AND um_t.name IN{{get_filters('team_fltr', remove_filter=True)[0].get('val')|where_in}}
{% endif %}
    WHERE TRUE
        {% if get_filters('direction_fltr')|length > 0 %}AND etm.direction = '{{get_filters('direction_fltr', remove_filter=True)[0].get('val')}}'{% endif %}
        {% if from_expr %}AND etm.final_status_timestamp::DATE >= {{ from_expr }}{% endif %}
        {% if to_expr %}AND etm.final_status_timestamp::DATE < {{ to_expr }}{% endif %}
        {% if get_filters('geo_fltr')|length > 0 %}AND etm.currency_id IN{{get_filters('geo_fltr', remove_filter=True)[0].get('val')|where_in}}{% endif %}
        {% if get_filters('type_fltr')|length > 0 %}AND etm.type IN{{get_filters('type_fltr', remove_filter=True)[0].get('val')|where_in}}{% endif %}
),
aggregated_data AS MATERIALIZED(
    SELECT
{% if get_filters('partition_by_hour', remove_filter=True)|length > 0 %}
        EXTRACT(HOUR FROM etm.final_status_timestamp)::TEXT AS day_or_hour,
{% else %}
        TO_CHAR(etm.final_status_timestamp::DATE, 'dd.mm.yyyy') AS day_or_hour,
{% endif %}
        COUNT(*) AS trans_count,
        COUNT(*) FILTER (WHERE status = 'accept') AS success_count,
        (SUM(etm.amount) FILTER (WHERE etm.status = 'accept')) / 1000000 AS sum_success_amount,
        (AVG(etm.amount) FILTER (WHERE etm.status = 'accept')) / 1000000 AS avg_success_amount,
        ROUND(EXTRACT(EPOCH FROM AVG(etm.final_status_timestamp - etm.create_timestamp) FILTER (WHERE etm.status = 'accept')))::INTEGER AS avg_success_time,
        ROUND(EXTRACT(EPOCH FROM AVG(etm.final_status_timestamp - etm.create_timestamp) FILTER (WHERE etm.status = 'close' )))::INTEGER AS avg_reject_time,
        COUNT(*) FILTER (WHERE appeal) AS appeal_count
    FROM filtered_data etm
{% if get_filters('partition_by_hour', remove_filter=True)|length > 0 %}
    GROUP BY EXTRACT(HOUR FROM etm.final_status_timestamp)
    ORDER BY EXTRACT(HOUR FROM etm.final_status_timestamp) DESC
{% else %}
    GROUP BY etm.final_status_timestamp::DATE
    ORDER BY etm.final_status_timestamp::DATE DESC
{% endif %}
),
aggregated_with_summary AS(
    SELECT
        'ИТОГО' AS day_or_hour,
        SUM(trans_count) AS trans_count,
        SUM(success_count) AS success_count,
        SUM(sum_success_amount) AS sum_success_amount,
        AVG(avg_success_amount) AS avg_success_amount,
        ROUND(AVG(avg_success_time))::INTEGER AS avg_success_time,
        ROUND(AVG(avg_reject_time))::INTEGER AS avg_reject_time,
        SUM(appeal_count) AS appeal_count
    FROM aggregated_data
    UNION ALL
    SELECT *
    FROM aggregated_data
)
SELECT
    day_or_hour,
    trans_count,
    success_count,
    ROUND(success_count::DECIMAL / trans_count * 100, 2) AS conversion,
    ROUND(sum_success_amount) AS sum_success_amount,
    ROUND(avg_success_amount) AS avg_success_amount,
    LPAD((avg_success_time / 3600)::TEXT, 2, '0')
        || ':' || LPAD((avg_success_time % 3600 / 60)::TEXT, 2, '0')
        || ':' || LPAD((avg_success_time  % 60)::TEXT, 2, '0') AS avg_success_time,
    LPAD((avg_reject_time / 3600)::TEXT, 2, '0')
        || ':' || LPAD((avg_reject_time % 3600 / 60)::TEXT, 2, '0')
        || ':' || LPAD((avg_reject_time  % 60)::TEXT, 2, '0') AS avg_reject_time,
    ROUND(appeal_count::DECIMAL / trans_count * 100, 2) AS appeal_percent
FROM aggregated_with_summary;

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.

[nikitasavelyev425]

Attaching both dashboards exports with edited database connection data
Statistics by days - filter applied correctly.zip
profit months - filter applied twice.zip

[dosubot]

Hi @nikitasavelyev425! I'm Dosu and I’m helping the superset team.

The intended behavior of get_time_filter(remove_filter=True) is to ensure the time filter is only applied inside your virtual dataset query and not again in the outer query, preventing double filtering and improving performance. This is confirmed in both the documentation and the codebase (docs, code).

However, there is a subtle propagation bug: if your virtual dataset uses nested macros or is composed using the dataset() macro, the "removal" state that tells Superset not to apply the filter again does not always propagate correctly. This can cause the time filter to be applied both in the inner and outer queries, as you observed. The issue is highly sensitive to how the dataset is structured—direct use of the macro works, but nesting or composition can break the removal logic (code details).

To avoid double filtering, try to avoid nested dataset composition or ensure that all filter removal logic is handled at the top level of your virtual dataset. Until this propagation issue is fixed in Superset, this is the most reliable workaround. If you need more technical details or want to track the relevant code, see the implementation in superset/jinja_context.py and superset/models/helpers.py.

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}