sadpandajoe commented on code in PR #43870: URL: https://github.com/apache/superset/pull/43870#discussion_r4339385288
########## tests/unit_tests/models/partition_mirroring_test.py: ########## @@ -0,0 +1,3204 @@ +# Licensed to the Apache Software Foundation (ASF) under one +# or more contributor license agreements. See the NOTICE file +# distributed with this work for additional information +# regarding copyright ownership. The ASF licenses this file +# to you under the Apache License, Version 2.0 (the +# "License"); you may not use this file except in compliance +# with the License. You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, +# software distributed under the License is distributed on an +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +# KIND, either express or implied. See the License for the +# specific language governing permissions and limitations +# under the License. +""" +Partition filter mirroring inside ``ExploreMixin.get_sqla_query``. + +The probe that resolves ``T(v)`` against the engine is stubbed throughout; what +these tests pin down is *which* predicates get mirrored and with what values, +which is where the correctness argument lives. +""" + +from __future__ import annotations + +from datetime import datetime +from typing import Any +from unittest.mock import MagicMock, patch + +import pandas as pd +import pytest +import sqlalchemy as sa +from flask import Flask + +from superset.connectors.sqla.models import SqlaTable, SqlMetric, TableColumn +from superset.connectors.sqla.partition_mapping import RawProbeValue +from superset.db_engine_specs.clickhouse import ClickHouseEngineSpec +from superset.db_engine_specs.presto import PrestoEngineSpec +from superset.db_engine_specs.sqlite import SqliteEngineSpec +from superset.models.core import Database +from superset.superset_typing import QueryObjectDict +from superset.utils import json +from superset.utils.core import FilterOperator + +PROBE = "superset.connectors.sqla.partition_mapping.evaluate_transform" + + [email protected](autouse=True) +def enable_partition_filter_mapping(app: Flask) -> Any: + app.config["DEFAULT_FEATURE_FLAGS"]["PARTITION_FILTER_MAPPING"] = True + yield + del app.config["DEFAULT_FEATURE_FLAGS"]["PARTITION_FILTER_MAPPING"] + + +def _table( + *, + transform: str = "unix_timestamp(:value)", + monotonic: bool = True, + partition_column: str | None = "dt_epoch", + mapped_column: str = "event_time", + partition_mapped_column: str | None = None, + main_dttm_col: str | None = "event_time", +) -> SqlaTable: + database = Database(database_name="test_db", sqlalchemy_uri="sqlite://") + columns = [ + TableColumn(column_name="event_time", is_dttm=True, type="TIMESTAMP"), + TableColumn(column_name="other_time", is_dttm=True, type="TIMESTAMP"), + TableColumn(column_name="dt_epoch", type="BIGINT"), + TableColumn(column_name="country", type="VARCHAR"), + TableColumn(column_name="region_key", type="VARCHAR"), + ] + table = SqlaTable( + table_name="web_events", + database=database, + schema=None, + main_dttm_col=main_dttm_col, + columns=columns, + metrics=[SqlMetric(metric_name="hits", expression="COUNT(*)")], + ) + table.partition_column = partition_column + table.partition_mapped_column = partition_mapped_column + for column in columns: + if column.column_name == mapped_column: + column.partition_value_transform = transform + column.partition_transform_is_monotonic = monotonic + return table + + +def _mapped_date_table() -> SqlaTable: + """ + A dataset whose mapped column is a ``DATE``. + + That is the type whose literal an engine resolves to a bare day, which is + what the resolution reasoning is about; `_rendering_dates_like_presto` is + what makes this table's spec answer that way. + """ + table = _table(mapped_column="event_date", main_dttm_col="event_date") + table.columns.append( + TableColumn(column_name="event_date", is_dttm=True, type="DATE") + ) + table.columns[-1].partition_value_transform = "unix_timestamp(:value)" + table.columns[-1].partition_transform_is_monotonic = True + return table + + +def _rendering_dates_like_presto(table: SqlaTable) -> Any: + """ + Make the dataset's engine spec render a bound the way Presto does. + + `Database.db_engine_spec` is a derived property, so the spec cannot be + swapped wholesale; delegating `convert_dttm` keeps the test tied to the real + spec rather than to a hand-copied format string. Presto's DATE branch is + `DATE '{dttm.date().isoformat()}'`, which Trino, BigQuery and some two dozen + others share. + """ + return patch.object( + table.database.db_engine_spec, + "convert_dttm", + classmethod( + lambda cls, target_type, dttm, db_extra=None: PrestoEngineSpec.convert_dttm( + target_type, dttm, db_extra=db_extra + ) + ), + ) + + +def _query(table: SqlaTable, **kwargs: Any) -> str: + defaults: dict[str, Any] = { + "columns": ["country"], + "metrics": [], + "orderby": [], + "extras": {}, + "filter": [], + "granularity": None, + "is_timeseries": False, + } + defaults.update(kwargs) + result = table.get_sqla_query(**defaults) + return str( + result.sqla_query.compile(compile_kwargs={"literal_binds": True}) + ).replace("\n", " ") + + +# --------------------------------------------------------------------------- +# The safe operators +# --------------------------------------------------------------------------- + + +def test_equality_filter_mirrors_onto_the_partition_column(app: Flask) -> None: + table = _table( + transform="lower(:value)", + monotonic=False, + mapped_column="country", + partition_mapped_column="country", + partition_column="region_key", + ) + + with app.app_context(): + with patch(PROBE, return_value=["us"]): + sql = _query( + table, + filter=[ + {"col": "country", "op": FilterOperator.EQUALS.value, "val": "US"} + ], + ) + + assert "region_key = 'us'" in sql + assert "country = 'US'" in sql + + +def _with_date_partition_column(table: SqlaTable) -> SqlaTable: + """Re-point the mapping's partition column at a ``DATE`` column.""" + table.columns.append( + TableColumn(column_name="part_date", is_dttm=True, type="DATE") + ) + table.partition_column = "part_date" + return table + + +def test_ordinary_text_does_not_mirror_onto_a_temporal_partition_column( + app: Flask, +) -> None: + """ + `lower(:value)` on a `VARCHAR` mapped column answers with text, and a + temporal partition column admits text -- a day key is legitimately text. + But it admitted *any* text: `country = 'US'` probed to `'us'` and emitted + `part_date = 'us'`, which PostgreSQL refuses as a date. By then the mirror + is in the statement, so a chart that worked before the mapping returned an + error rather than losing its pruning. The preview called it valid. + """ + table = _with_date_partition_column( + _table( + transform="lower(:value)", + monotonic=False, + mapped_column="country", + partition_mapped_column="country", + ) + ) + + with app.app_context(): + with patch(PROBE, return_value=["us"]): + sql = _query( + table, + filter=[ + {"col": "country", "op": FilterOperator.EQUALS.value, "val": "US"} + ], + ) + + assert "part_date" not in sql + assert "country = 'US'" in sql + + +def test_a_text_column_s_bound_is_not_rounded_as_though_it_were_temporal( + app: Flask, +) -> None: + """ + SQLite's `convert_dttm` renders for `types.String` as well as the date + types, so a VARCHAR mapped column read as second-resolution -- and a filter + value that merely *looks* like an instant had its bound rounded as though + the engine had truncated it. + + On a VARCHAR `country` mapped onto VARCHAR `region_key` by `:value`, + `country < '2026-07-06T10:08:11.500000'` was mirrored as + `region_key <= '2026-07-06 10:08:12.000000'`. A space sorts before `T`, so a + row holding `2026-07-06T09:00:00` -- which the filter keeps -- fell outside + the mirror and was dropped. + + `_column_literal_resolution` already guarded on `is_dttm` and said in its + docstring that the per-instant callers never met this case. They do: the + value parses as an instant while the column is still text. + """ + table = _table( + transform=":value", + monotonic=True, + mapped_column="country", + partition_mapped_column="country", + partition_column="region_key", + ) + + with app.app_context(): + with patch(PROBE, return_value=["2026-07-06T10:08:11.500000"]) as probe: + _query( + table, + filter=[ + { + "col": "country", + "op": FilterOperator.LESS_THAN.value, + "val": "2026-07-06T10:08:11.500000", + } + ], + ) + + # Handed through untouched: no rounding, and no seconds-shaped rewrite. + assert probe.call_args.args[-1] == ["2026-07-06T10:08:11.500000"] + + +def test_a_day_key_still_mirrors_onto_a_temporal_partition_column( + app: Flask, +) -> None: + """ + The narrowing above must not cost the case the text admission exists for: + `to_char(:value, 'YYYYMMDD')` answers with a day key, which PostgreSQL + reads as a date and so declares in `temporal_literal_formats`. + + Its sibling `test_a_day_key_still_mirrors_onto_a_text_partition_column` + covers the same transform onto a *text* key, which is the other axis -- + that one is about `GenericDataType.STRING` and never reached this gate. + """ + table = _with_date_partition_column( + _table( + transform="to_char(:value, 'YYYYMMDD')", + monotonic=False, + mapped_column="country", + partition_mapped_column="country", + ) + ) + + with app.app_context(): + with patch.object(SqliteEngineSpec, "temporal_literal_formats", ("%Y%m%d",)): + with patch(PROBE, return_value=["20260101"]): + sql = _query( + table, + filter=[ + { + "col": "country", + "op": FilterOperator.EQUALS.value, + "val": "US", + } + ], + ) + + assert "part_date = '20260101'" in sql + + +def test_a_day_key_declines_where_the_engine_has_not_claimed_it( + app: Flask, +) -> None: + """ + BigQuery coerces a STRING literal to `DATE` only in the canonical + `YYYY-MM-DD` form and Trino wants an explicit cast, so `part_date = + '20260101'` fails the whole chart there rather than merely losing its + pruning. An engine that has not declared the format declines instead. + """ + table = _with_date_partition_column( + _table( + transform="to_char(:value, 'YYYYMMDD')", + monotonic=False, + mapped_column="country", + partition_mapped_column="country", + ) + ) + + assert SqliteEngineSpec.temporal_literal_formats == () + + with app.app_context(): + with patch(PROBE, return_value=["20260101"]): + sql = _query( + table, + filter=[ + {"col": "country", "op": FilterOperator.EQUALS.value, "val": "US"} + ], + ) + + assert "part_date" not in sql + + +def test_in_filter_mirrors_element_wise(app: Flask) -> None: + table = _table( + transform="lower(:value)", + monotonic=False, + mapped_column="country", + partition_mapped_column="country", + partition_column="region_key", + ) + + with app.app_context(): + with patch(PROBE, return_value=["us", "ca"]): + sql = _query( + table, + filter=[ + { + "col": "country", + "op": FilterOperator.IN.value, + "val": ["US", "CA"], + } + ], + ) + + assert "region_key IN ('us', 'ca')" in sql + + +def test_time_range_mirrors_both_bounds(app: Flask) -> None: + """ + The Explore time range is the most important operator in the feature, and it + is a range operator -- so it only mirrors on a declared-monotonic transform. + """ + table = _table() + + with app.app_context(): + with patch(PROBE, return_value=[1767225600, 1769904000]): + sql = _query( + table, + granularity="event_time", + from_dttm=datetime(2026, 1, 1), + to_dttm=datetime(2026, 2, 1), + ) + + assert "dt_epoch >= 1767225600" in sql + assert "dt_epoch <= 1769904000" in sql + + +def test_temporal_range_filter_mirrors_both_bounds(app: Flask) -> None: + table = _table() + + with app.app_context(): + with patch(PROBE, return_value=[1767225600, 1769904000]): + sql = _query( + table, + filter=[ + { + "col": "event_time", + "op": FilterOperator.TEMPORAL_RANGE.value, + "val": "2026-01-01 : 2026-02-01", + } + ], + ) + + assert "dt_epoch >= 1767225600" in sql + assert "dt_epoch <= 1769904000" in sql + + [email protected]( + "operator,expected", + [ + (FilterOperator.GREATER_THAN, "dt_epoch >= 1767225600"), + (FilterOperator.GREATER_THAN_OR_EQUALS, "dt_epoch >= 1767225600"), + (FilterOperator.LESS_THAN, "dt_epoch <= 1767225600"), + (FilterOperator.LESS_THAN_OR_EQUALS, "dt_epoch <= 1767225600"), + ], +) +def test_strict_range_operators_mirror_non_strictly( + app: Flask, operator: FilterOperator, expected: str +) -> None: + """ + The direction is preserved; the strictness is not. "Preserves ordering" + means non-decreasing, so `col < v` only implies `T(col) <= T(v)` -- a + strict mirror would drop the rows a bucketing transform puts at the + boundary. The non-strict mirror reads one extra partition, and the original + filter is still there to exclude whatever it admits. + """ + table = _table() + + with app.app_context(): + with patch(PROBE, return_value=[1767225600]): + sql = _query( + table, + filter=[ + { + "col": "event_time", + "op": operator.value, + "val": "2026-01-01 00:00:00", + } + ], + ) + + assert expected in sql + + +def test_a_bucketing_transform_keeps_the_boundary_bucket(app: Flask) -> None: + """ + A day key is monotonic but not *strictly* so: every instant in a day + maps to one value, so an upper bound at `2026-07-06 12:00` and a row at + `2026-07-06 06:00` share the bucket `20260706`. Mirroring that bound + strictly prunes the bucket the row is in and drops it from the chart -- + silently, with the pruning glyph still promising only a speed-up. + """ + table = _table( + transform="to_char(:value, 'YYYYMMDD')", + partition_column="region_key", + ) + + with app.app_context(): + with patch(PROBE, return_value=["20260705", "20260706"]): + sql = _query( + table, + granularity="event_time", + from_dttm=datetime(2026, 7, 5), + to_dttm=datetime(2026, 7, 6, 12, 0), + ) + + assert "region_key >= '20260705'" in sql + assert "region_key <= '20260706'" in sql + + +def test_an_intraday_strict_pair_does_not_mirror_to_an_empty_range( + app: Flask, +) -> None: + """ + The zero-instead-of-nine case. `event_time > '2026-07-05 06:00'` plus + `event_time < '2026-07-06 00:00'` is an ordinary pair of ad-hoc filters, and + under a day key the bounds land in adjacent buckets. Mirrored strictly it + reads `day_key > '20260705' AND day_key < '20260706'`, which is empty by + construction whatever the table holds -- so the chart returns nothing while + SQL Lab returns the real rows. + """ + table = _table( + transform="to_char(:value, 'YYYYMMDD')", + partition_column="region_key", + ) + + with app.app_context(): + with patch(PROBE, return_value=["20260705", "20260706"]): + sql = _query( + table, + filter=[ + { + "col": "event_time", + "op": FilterOperator.GREATER_THAN.value, + "val": "2026-07-05 06:00:00", + }, + { + "col": "event_time", + "op": FilterOperator.LESS_THAN.value, + "val": "2026-07-06 00:00:00", + }, + ], + ) + + assert "region_key >= '20260705'" in sql + assert "region_key <= '20260706'" in sql + + +def test_an_integer_bucket_key_is_mirrored_non_strictly_too(app: Flask) -> None: + """ + The same loss was measured on a day *number* as well as a day string, so it + is a property of any many-to-one transform rather than of text keys or of + the formatting function used to build them. + """ + table = _table(transform="cast(unix_timestamp(:value) / 86400 as bigint)") + + with app.app_context(): + with patch(PROBE, return_value=[20638, 20639]): + sql = _query( + table, + granularity="event_time", + from_dttm=datetime(2026, 7, 5), + to_dttm=datetime(2026, 7, 6, 12, 0), + ) + + assert "dt_epoch >= 20638" in sql + assert "dt_epoch <= 20639" in sql + + +# --------------------------------------------------------------------------- +# The unsafe operators — nothing is emitted +# --------------------------------------------------------------------------- + + [email protected]( + "flt", + [ + {"col": "country", "op": FilterOperator.NOT_EQUALS.value, "val": "US"}, + {"col": "country", "op": FilterOperator.NOT_IN.value, "val": ["US"]}, + {"col": "country", "op": FilterOperator.LIKE.value, "val": "U%"}, + {"col": "country", "op": FilterOperator.ILIKE.value, "val": "U%"}, + {"col": "country", "op": FilterOperator.NOT_LIKE.value, "val": "U%"}, + {"col": "country", "op": FilterOperator.IS_NULL.value}, + {"col": "country", "op": FilterOperator.IS_NOT_NULL.value}, + ], +) +def test_unsafe_operators_emit_nothing(app: Flask, flt: dict[str, Any]) -> None: + """ + ``T`` need not be injective, so ``country != 'US'`` does not imply + ``region_key != 'us'`` -- mirroring it would drop rows whose ``country`` is + already lowercase, rows the original filter keeps. + """ + table = _table( + transform="lower(:value)", + monotonic=False, + mapped_column="country", + partition_mapped_column="country", + partition_column="region_key", + ) + + with app.app_context(): + with patch(PROBE, side_effect=AssertionError("probe must not run")): + sql = _query(table, filter=[flt]) + + assert "region_key" not in sql + + +def test_ranges_do_not_mirror_when_the_transform_is_not_order_preserving( + app: Flask, +) -> None: + """ + ``hour(:value)`` is a perfectly reasonable partition transform on a + ``TIMESTAMP`` column and it is not monotonic, so a time range must not + mirror through it. + """ + table = _table(transform="hour(:value)", monotonic=False) + + with app.app_context(): + with patch(PROBE, side_effect=AssertionError("probe must not run")): + sql = _query( + table, + granularity="event_time", + from_dttm=datetime(2026, 1, 1), + to_dttm=datetime(2026, 2, 1), + ) + + assert "dt_epoch" not in sql + + +def test_equality_still_mirrors_when_the_transform_is_not_order_preserving( + app: Flask, +) -> None: + table = _table(transform="hour(:value)", monotonic=False) + + with app.app_context(): + with patch(PROBE, return_value=[13]): + sql = _query( + table, + filter=[ + { + "col": "event_time", + "op": FilterOperator.EQUALS.value, + "val": "2026-01-01 13:00:00", + } + ], + ) + + assert "dt_epoch = 13" in sql + + +# --------------------------------------------------------------------------- +# Bail-outs and edge cases +# --------------------------------------------------------------------------- + + +def test_nothing_mirrors_when_the_feature_flag_is_off(app: Flask) -> None: + table = _table() + app.config["DEFAULT_FEATURE_FLAGS"]["PARTITION_FILTER_MAPPING"] = False + + with app.app_context(): + with patch(PROBE, side_effect=AssertionError("probe must not run")): + sql = _query( + table, + granularity="event_time", + from_dttm=datetime(2026, 1, 1), + to_dttm=datetime(2026, 2, 1), + ) + + assert "dt_epoch" not in sql + + +def test_an_open_ended_range_mirrors_only_the_bound_it_has(app: Flask) -> None: + """``from_dttm``/``to_dttm`` are ``None`` for open-ended ranges.""" + table = _table() + + with app.app_context(): + with patch(PROBE, return_value=[1767225600]) as probe: + sql = _query( + table, + granularity="event_time", + from_dttm=datetime(2026, 1, 1), + to_dttm=None, + ) + + assert probe.call_args.args[-1] == [datetime(2026, 1, 1)] + assert "dt_epoch >= 1767225600" in sql + assert "dt_epoch <" not in sql + + +def test_no_filter_range_mirrors_nothing(app: Flask) -> None: + table = _table() + + with app.app_context(): + with patch(PROBE, side_effect=AssertionError("probe must not run")): + sql = _query( + table, + granularity="event_time", + from_dttm=None, + to_dttm=None, + ) + + assert "dt_epoch" not in sql + + +def test_the_same_bound_is_not_mirrored_twice(app: Flask) -> None: + """ + A ``granularity`` time filter *and* a ``TEMPORAL_RANGE`` ad-hoc filter on the + same column is a routine Explore configuration. Emitting the predicate twice + is harmless SQL but makes "View query" surprising. + """ + table = _table() + + with app.app_context(): + with patch(PROBE, return_value=[1767225600, 1769904000]): + sql = _query( + table, + granularity="event_time", + from_dttm=datetime(2026, 1, 1), + to_dttm=datetime(2026, 2, 1), + filter=[ + { + "col": "event_time", + "op": FilterOperator.TEMPORAL_RANGE.value, + "val": "2026-01-01 : 2026-02-01", + } + ], + ) + + assert sql.count("dt_epoch >= 1767225600") == 1 + assert sql.count("dt_epoch <= 1769904000") == 1 + + +def test_always_filter_main_dttm_mirrors_the_main_column_too(app: Flask) -> None: + """ + With ``always_filter_main_dttm`` the query also filters ``main_dttm_col``, + which is a *different* column from the one the chart grouped by. That filter + is the one the mapping tracks, so it has to mirror. + """ + table = _table() + table.always_filter_main_dttm = True + + with app.app_context(): + with patch(PROBE, return_value=[1767225600, 1769904000]): + sql = _query( + table, + granularity="other_time", + from_dttm=datetime(2026, 1, 1), + to_dttm=datetime(2026, 2, 1), + ) + + assert "dt_epoch >= 1767225600" in sql + assert "dt_epoch <= 1769904000" in sql + + +def test_a_failing_probe_leaves_the_query_correct_and_unpruned(app: Flask) -> None: + table = _table() + + with app.app_context(): + with patch(PROBE, return_value=None): + sql = _query( + table, + granularity="event_time", + from_dttm=datetime(2026, 1, 1), + to_dttm=datetime(2026, 2, 1), + ) + + assert "dt_epoch" not in sql + assert "event_time" in sql + + +def test_a_probe_result_the_partition_column_cannot_hold_emits_nothing( + app: Flask, +) -> None: + """ + `cast(:value as text) || 'x'` evaluates perfectly and answers with a string, + and `dt_epoch` is a BIGINT. The predicate used to go out as + `dt_epoch >= '2026-01-01 00:00:00x'`, which Postgres refuses -- and by then + the mirror is in the statement, so the chart returned a 400 rather than + losing its pruning. + """ + table = _table(transform="cast(:value as text) || 'x'") + + with app.app_context(): + probed = ["2026-01-01 00:00:00x", "2026-02-01 00:00:00x"] + with patch(PROBE, return_value=probed): + sql = _query( + table, + granularity="event_time", + from_dttm=datetime(2026, 1, 1), + to_dttm=datetime(2026, 2, 1), + ) + + assert "dt_epoch" not in sql + assert "event_time" in sql + + +def test_a_numeric_probe_result_still_mirrors_onto_a_numeric_key( + app: Flask, +) -> None: + """The other side of the gate: the ordinary case is untouched.""" + table = _table() + + with app.app_context(): + with patch(PROBE, return_value=[1767225600, 1769904000]): + sql = _query( + table, + granularity="event_time", + from_dttm=datetime(2026, 1, 1), + to_dttm=datetime(2026, 2, 1), + ) + + assert "dt_epoch >= 1767225600" in sql + + +def test_a_day_key_still_mirrors_onto_a_text_partition_column(app: Flask) -> None: + """ + A `to_char(:value, 'YYYYMMDD')`-style transform answers with text, and a + text partition column holds text. Refusing that would break the commonest + bucketing mapping there is. + """ + table = _table( + transform="to_char(:value, 'YYYYMMDD')", partition_column="region_key" + ) + + with app.app_context(): + with patch(PROBE, return_value=["20260101", "20260201"]): + sql = _query( + table, + granularity="event_time", + from_dttm=datetime(2026, 1, 1), + to_dttm=datetime(2026, 2, 1), + ) + + assert "region_key >= '20260101'" in sql + + +def test_an_inverted_transform_emits_nothing(app: Flask) -> None: + """ + ``T(lower) <= T(upper)`` is a nearly-free runtime backstop for the + monotonicity *declaration*: it catches inverted transforms, and catches + ``hour()`` on any range spanning a day boundary. Necessary, not sufficient. + """ + table = _table() + + with app.app_context(): + with patch(PROBE, return_value=[1769904000, 1767225600]): + sql = _query( + table, + granularity="event_time", + from_dttm=datetime(2026, 1, 1), + to_dttm=datetime(2026, 2, 1), + ) + + assert "dt_epoch" not in sql + + +def test_incomparable_probe_results_emit_nothing(app: Flask) -> None: + """Probe results come back as pandas scalars; not every pair compares.""" + table = _table() + + with app.app_context(): + with patch(PROBE, return_value=[object(), object()]): + sql = _query( + table, + granularity="event_time", + from_dttm=datetime(2026, 1, 1), + to_dttm=datetime(2026, 2, 1), + ) + + assert "dt_epoch" not in sql + + +def test_self_mapping_is_skipped_defensively(app: Flask) -> None: + """Save-time validation rejects this, but older rows can carry it.""" + table = _table(partition_column="event_time") + + with app.app_context(): + with patch(PROBE, side_effect=AssertionError("probe must not run")): + sql = _query( + table, + granularity="event_time", + from_dttm=datetime(2026, 1, 1), + to_dttm=datetime(2026, 2, 1), + ) + + assert sql.count("event_time >=") == 1 + + +def test_a_filter_on_an_unmapped_column_mirrors_nothing(app: Flask) -> None: + table = _table() + + with app.app_context(): + with patch(PROBE, side_effect=AssertionError("probe must not run")): + sql = _query( + table, + filter=[ + {"col": "country", "op": FilterOperator.EQUALS.value, "val": "US"} + ], + ) + + assert "dt_epoch" not in sql + + +def test_the_probe_receives_timezone_adjusted_bounds(app: Flask) -> None: + """ + ``get_time_filter`` shifts the bounds by the dataset's timezone before + building the clause. Probing the *raw* bounds would produce epoch bounds + describing a different instant than the timestamp bounds they mirror -- + wrong by exactly the offset, silently. + + The bounds reach the probe as `datetime` objects here because the mapped + column declares no ``python_date_format``, so that *is* its stored + representation. See the sibling tests for a column where it is not. + """ + table = _table() + table.extra = '{"timezone": "Europe/Berlin"}' + + with app.app_context(): + with patch(PROBE, return_value=[1, 2]) as probe: + _query( + table, + granularity="event_time", + from_dttm=datetime(2026, 1, 9), + to_dttm=datetime(2026, 1, 10), + ) + + # Berlin is UTC+1 in January, so local midnight is 23:00 the day before. + assert probe.call_args.args[-1] == [ + datetime(2026, 1, 8, 23, 0), + datetime(2026, 1, 9, 23, 0), + ] + + +def test_the_probe_receives_hour_offset_adjusted_bounds(app: Flask) -> None: + """The legacy ``offset`` field shifts bounds too, and must reach the probe.""" + table = _table() + table.offset = 5 + + with app.app_context(): + with patch(PROBE, return_value=[1, 2]) as probe: + _query( + table, + granularity="event_time", + from_dttm=datetime(2026, 1, 9, 12), + to_dttm=datetime(2026, 1, 10, 12), + ) + + assert probe.call_args.args[-1] == [ + datetime(2026, 1, 9, 7), + datetime(2026, 1, 10, 7), + ] + + +def test_the_probe_receives_an_epoch_column_s_own_representation( + app: Flask, +) -> None: + """ + The real predicate compares the column against `dttm_sql_literal`, which + for a column declaring an epoch ``python_date_format`` renders an integer. + So on such a column the transform is a function of an integer -- probing it + with a `datetime` evaluates something unrelated to the partition keys, or + raises, and a raise costs the dataset its pruning silently. + """ + table = _table(mapped_column="event_epoch", main_dttm_col="event_epoch") + table.columns.append( + TableColumn( + column_name="event_epoch", + is_dttm=True, + type="BIGINT", + python_date_format="epoch_s", + ) + ) + table.columns[-1].partition_value_transform = "to_date(:value)" + table.columns[-1].partition_transform_is_monotonic = True + + with app.app_context(): + with patch(PROBE, return_value=[1, 2]) as probe: + _query( + table, + granularity="event_epoch", + from_dttm=datetime(2026, 1, 1), + to_dttm=datetime(2026, 2, 1), + ) + + assert probe.call_args.args[-1] == [1767225600, 1769904000] + + +def test_the_probe_honours_a_per_column_date_format_from_db_extra( + app: Flask, +) -> None: + """ + `dttm_sql_literal` falls back to ``python_date_format_by_column_name`` in + the database's extra, so the probe has to read the same fallback -- on a + column where that fallback is actually reached. `event_epoch` is a BIGINT, + so SQLite's `convert_dttm` answers nothing and the format decides the + literal. + + Its sibling below is the same configuration on a column where the engine + *does* answer, which is the case this test used to assert by accident. + """ + table = _table(mapped_column="event_epoch", main_dttm_col="event_epoch") + table.columns.append( + TableColumn(column_name="event_epoch", is_dttm=True, type="BIGINT") + ) + table.columns[-1].partition_value_transform = "to_date(:value)" + table.columns[-1].partition_transform_is_monotonic = True + table.database.extra = json.dumps( + {"python_date_format_by_column_name": {"event_epoch": "%Y%m%d"}} + ) + + with app.app_context(): + with patch(PROBE, return_value=[1, 2]) as probe: + _query( + table, + granularity="event_epoch", + from_dttm=datetime(2026, 1, 1), + to_dttm=datetime(2026, 2, 1), + ) + + assert probe.call_args.args[-1] == ["20260101", "20260201"] + + +def test_a_date_format_the_engine_literal_preempts_is_not_probed( + app: Flask, +) -> None: + """ + `dttm_sql_literal` asks `convert_dttm` first and reaches the format only if + the engine answers nothing. `event_time` is a TIMESTAMP, so SQLite answers + `'2026-01-01 00:00:00'` and the `%Y%m%d` format never applies to the real + predicate -- but the probe applied it anyway. + + The mirror therefore described a different bound from the filter it stands + in for: `event_time >= '2026-01-01 00:00:00'` became + `dt_epoch >= '20260101'`, and a January 15 row keyed `2026-01-15` sorts + below that string, so the chart dropped a row the filter keeps. + + The `datetime` goes to the probe unchanged, which is what already happens + for every temporal column carrying no format -- `_engine_literal_resolution` + then widens the bound for whatever the engine's own literal throws away. + """ + table = _table() + table.database.extra = json.dumps( + {"python_date_format_by_column_name": {"event_time": "%Y%m%d"}} + ) + + with app.app_context(): + with patch(PROBE, return_value=[1, 2]) as probe: + _query( + table, + granularity="event_time", + from_dttm=datetime(2026, 1, 1), + to_dttm=datetime(2026, 2, 1), + ) + + assert probe.call_args.args[-1] == [datetime(2026, 1, 1), datetime(2026, 2, 1)] + + +def test_the_probe_rounds_outward_when_the_engine_drops_subseconds( + app: Flask, +) -> None: + """ + SQLite renders a timestamp with ``timespec="seconds"``, so the real lower + bound is floored while the probe was handed the full precision. That makes + the mirror *narrower* than the predicate it stands in for, and narrower + means it drops rows the filter keeps -- a row at ``.250000`` matches + ``event_time >= '2026-01-01 00:00:00'`` but not a mirror derived from + ``.500000``. + + Rounding outward is never narrower. What it moves is the *value* the probe + is handed, which is what the assertion below pins -- every mirrored upper + bound is inclusive either way, see `mirror_operator`. + """ + table = _table() + + with app.app_context(): + with patch(PROBE, return_value=[1, 2]) as probe: + sql = _query( + table, + granularity="event_time", + from_dttm=datetime(2026, 1, 1, 0, 0, 0, 500000), + to_dttm=datetime(2026, 2, 1, 0, 0, 0, 500000), + ) + + assert probe.call_args.args[-1] == [ + datetime(2026, 1, 1, 0, 0, 0), + datetime(2026, 2, 1, 0, 0, 1), + ] + assert "dt_epoch <= 2" in sql + + +def test_the_probe_rounds_outward_on_a_date_column_too(app: Flask) -> None: + """ + The truncation test cannot be "does this render the same as the bound + truncated to the second". SQLite renders a DATE column at exactly midnight + as a bare date, so truncating a `.500000` bound crosses a *formatting* + boundary as well as a precision one: the two texts differ for a reason that + has nothing to do with precision, and the truncation that is really + happening goes unnoticed. Two probes that both carry a sub-second part see + it. + """ + table = _table(mapped_column="event_date", main_dttm_col="event_date") + table.columns.append( + TableColumn(column_name="event_date", is_dttm=True, type="DATE") + ) + table.columns[-1].partition_value_transform = "unix_timestamp(:value)" + table.columns[-1].partition_transform_is_monotonic = True + + with app.app_context(): + with patch(PROBE, return_value=[1, 2]) as probe: + _query( + table, + granularity="event_date", + from_dttm=datetime(2026, 1, 1, 0, 0, 0, 500000), + to_dttm=datetime(2026, 2, 1, 0, 0, 0, 500000), + ) + + assert probe.call_args.args[-1] == [ + datetime(2026, 1, 1, 0, 0, 0), + datetime(2026, 2, 1, 0, 0, 1), + ] + + +def test_the_probe_widens_to_the_day_when_the_engine_drops_the_time( + app: Flask, +) -> None: + """ + Presto, Trino, BigQuery and some two dozen other specs render a DATE bound + as `dttm.date().isoformat()`, so a 10:00 lower bound reaches the engine as + `DATE '2026-01-01'` while the probe was handed 10:00. A Jan-1 row then has + `dt_epoch = T(DATE '2026-01-01')`, which satisfies the real predicate and + *fails* the mirror -- the whole first day is dropped. + + Rounding to the second cannot fix this: there is no sub-second part to + floor, and flooring one would leave 10:00 exactly where it was. + """ + table = _table(mapped_column="event_date", main_dttm_col="event_date") + table.columns.append( + TableColumn(column_name="event_date", is_dttm=True, type="DATE") + ) + table.columns[-1].partition_value_transform = "unix_timestamp(:value)" + table.columns[-1].partition_transform_is_monotonic = True + + with app.app_context(): + with _rendering_dates_like_presto(table): + with patch(PROBE, return_value=[1, 2]) as probe: + sql = _query( + table, + granularity="event_date", + from_dttm=datetime(2026, 1, 1, 10, 0), + to_dttm=datetime(2026, 1, 2, 10, 0), + ) + + assert probe.call_args.args[-1] == [ + datetime(2026, 1, 1, 0, 0), + datetime(2026, 1, 3, 0, 0), + ] + assert "dt_epoch <= 2" in sql + + +def test_the_probe_widens_to_the_day_on_a_date_column_not_flagged_temporal( + app: Flask, +) -> None: + """ + `get_time_filter` renders a bound through `convert_dttm` for a DATE column + whether or not the owner ticked "Is temporal", so an unflagged DATE column + still compares `DATE '2026-01-01'` for a 10:00 bound. Keying the resolution + check on `is_dttm` alone handed the probe 10:00 there, and the mirror + dropped the first day. + """ + table = _table(mapped_column="event_date", main_dttm_col="event_date") + table.columns.append( + TableColumn(column_name="event_date", is_dttm=False, type="DATE") + ) + table.columns[-1].partition_value_transform = "unix_timestamp(:value)" + table.columns[-1].partition_transform_is_monotonic = True + + with app.app_context(): + with _rendering_dates_like_presto(table): + with patch(PROBE, return_value=[1, 2]) as probe: + _query( + table, + granularity="event_date", + from_dttm=datetime(2026, 1, 1, 10, 0), + to_dttm=datetime(2026, 1, 2, 10, 0), + ) + + assert probe.call_args.args[-1] == [ + datetime(2026, 1, 1, 0, 0), + datetime(2026, 1, 3, 0, 0), + ] + + +def test_a_day_resolution_bound_already_at_midnight_is_left_alone( + app: Flask, +) -> None: + """ + Widening is outward-only, so a bound that already sits on the day boundary + needs nothing done to it -- and the upper bound must not be pushed out a + whole extra day for no reason. + """ + table = _table(mapped_column="event_date", main_dttm_col="event_date") + table.columns.append( + TableColumn(column_name="event_date", is_dttm=True, type="DATE") + ) + table.columns[-1].partition_value_transform = "unix_timestamp(:value)" + table.columns[-1].partition_transform_is_monotonic = True + + with app.app_context(): + with _rendering_dates_like_presto(table): + with patch(PROBE, return_value=[1, 2]) as probe: + _query( + table, + granularity="event_date", + from_dttm=datetime(2026, 1, 1), + to_dttm=datetime(2026, 2, 1), + ) + + assert probe.call_args.args[-1] == [ + datetime(2026, 1, 1, 0, 0), + datetime(2026, 2, 1, 0, 0), + ] + + +def test_an_equality_is_not_mirrored_when_the_engine_drops_the_time( + app: Flask, +) -> None: + """ + An ungrained temporal equality -- what drill-to-detail builds -- compares + `event_date = DATE '2026-01-01'` on such an engine, so a mirror probed with + 10:00 asks for a key no row holds. These entries are AND-ed onto the query, + so unlike a range there is nowhere to widen to: the only safe answer is to + emit no mirror and lose the pruning. + """ + table = _table(mapped_column="event_date", main_dttm_col="event_date") + table.columns.append( + TableColumn(column_name="event_date", is_dttm=True, type="DATE") + ) + table.columns[-1].partition_value_transform = "unix_timestamp(:value)" + table.columns[-1].partition_transform_is_monotonic = True + + with app.app_context(): + with _rendering_dates_like_presto(table): + with patch(PROBE, return_value=[1]) as probe: + sql = _query( + table, + filter=[ + { + "col": "event_date", + "op": FilterOperator.EQUALS.value, + "val": datetime(2026, 1, 1, 10, 0), + } + ], + ) + + probe.assert_not_called() + assert "dt_epoch" not in sql + + +def test_an_equality_at_midnight_still_mirrors_on_a_date_column( + app: Flask, +) -> None: + """ + The guard above is about *lost* precision, not about DATE columns: at + midnight the engine's literal and the probe's value agree, so the mirror is + exact and the pruning is kept. + """ + table = _table(mapped_column="event_date", main_dttm_col="event_date") + table.columns.append( + TableColumn(column_name="event_date", is_dttm=True, type="DATE") + ) + table.columns[-1].partition_value_transform = "unix_timestamp(:value)" + table.columns[-1].partition_transform_is_monotonic = True + + with app.app_context(): + with _rendering_dates_like_presto(table): + with patch(PROBE, return_value=[1767225600]): + sql = _query( + table, + filter=[ + { + "col": "event_date", + "op": FilterOperator.EQUALS.value, + "val": datetime(2026, 1, 1), + } + ], + ) + + assert "dt_epoch = 1767225600" in sql + + +def test_the_probe_keeps_full_precision_on_an_engine_that_does_not_truncate( + app: Flask, +) -> None: + """ + The widening is detected, not enumerated per engine: an engine whose + literal carries the sub-second part needs no rounding, and rounding anyway + would scan partitions for no reason. + """ + table = _table() + + with app.app_context(): + with patch.object( + table.database.db_engine_spec, + "convert_dttm", + classmethod(lambda cls, target_type, dttm, db_extra=None: repr(dttm)), + ): + with patch(PROBE, return_value=[1, 2]) as probe: + _query( + table, + granularity="event_time", + from_dttm=datetime(2026, 1, 1, 0, 0, 0, 500000), + to_dttm=datetime(2026, 2, 1, 0, 0, 0, 500000), + ) + + assert probe.call_args.args[-1] == [ + datetime(2026, 1, 1, 0, 0, 0, 500000), + datetime(2026, 2, 1, 0, 0, 0, 500000), + ] + + +# --------------------------------------------------------------------------- +# The value the engine actually compares +# --------------------------------------------------------------------------- + + +def test_a_string_equality_carrying_a_time_does_not_mirror_on_a_date_column( + app: Flask, +) -> None: + """ + A simple filter arrives as the text the chart author typed, not as a + `datetime`: `filter_values_handler` rewrites only an all-digit value, which + it reads as epoch milliseconds. On a DATE column the engine compares that + text on its date part alone, so the filter keeps the whole of 2026-01-01 + while a mirror probed at 10:00 asks for a key no row holds. + + The `datetime` form of this has been covered since the day resolution was + detected at all -- see + `test_an_equality_is_not_mirrored_when_the_engine_drops_the_time`. The + string form is the one a user can actually produce, and it was the one that + dropped rows. + """ + table = _mapped_date_table() + + with app.app_context(): + with _rendering_dates_like_presto(table): + with patch(PROBE, return_value=[1]) as probe: + sql = _query( + table, + filter=[ + { + "col": "event_date", + "op": FilterOperator.EQUALS.value, + "val": "2026-01-01 10:00:00", + } + ], + ) + + probe.assert_not_called() + assert "dt_epoch" not in sql + assert "event_date = '2026-01-01 10:00:00'" in sql + + [email protected]( + "values", + [ + ["2026-01-01 10:00:00", "2026-01-02 09:00:00"], + ["2026-01-01", "2026-01-02 09:00:00"], + ], + ids=["every-member-coarse", "one-member-coarse"], +) +def test_a_string_in_list_declines_entire_when_any_member_carries_a_time( + app: Flask, values: list[str] +) -> None: + """ + An `IN` list is mirrored as one predicate, so a member the engine compares + more coarsely than the mirror can be built from is the whole list's + problem. Mirroring only the members that do work would be *narrower* than + the filter -- the same narrowing the `None`-member guard refuses -- so the + list declines entire. + """ + table = _mapped_date_table() + + with app.app_context(): + with _rendering_dates_like_presto(table): + with patch(PROBE, return_value=[1, 2]) as probe: + sql = _query( + table, + filter=[ + { + "col": "event_date", + "op": FilterOperator.IN.value, + "val": values, + } + ], + ) + + probe.assert_not_called() + assert "dt_epoch" not in sql + + +def test_a_string_in_list_of_dates_mirrors_element_wise(app: Flask) -> None: + """ + The guard above is about a *time of day*, not about `IN` on a DATE column: + every member at the start of its day is compared in full, so the list + mirrors and the pruning is kept. + """ + table = _mapped_date_table() + + with app.app_context(): + with _rendering_dates_like_presto(table): + with patch(PROBE, return_value=[1767225600, 1767312000]) as probe: + sql = _query( + table, + filter=[ + { + "col": "event_date", + "op": FilterOperator.IN.value, + "val": ["2026-01-01", "2026-01-02"], + } + ], + ) + + assert probe.call_args.args[-1] == ["2026-01-01", "2026-01-02"] + assert "dt_epoch IN (1767225600, 1767312000)" in sql + + +def test_a_string_lower_bound_rounds_outward_on_a_date_column(app: Flask) -> None: + """ + A bound has somewhere to widen to where an equality does not. The engine + compares `event_date >= DATE '2026-01-05'`, so every row on 2026-01-05 + satisfies the filter and the mirror has to admit that whole day -- which is + what moving the bound back to midnight does. + """ + table = _mapped_date_table() + + with app.app_context(): + with _rendering_dates_like_presto(table): + with patch(PROBE, return_value=[1767571200]) as probe: + sql = _query( + table, + filter=[ + { + "col": "event_date", + "op": FilterOperator.GREATER_THAN_OR_EQUALS.value, + "val": "2026-01-05 10:00:00", + } + ], + ) + + assert probe.call_args.args[-1] == [datetime(2026, 1, 5, 0, 0)] + assert "dt_epoch >= 1767571200" in sql + + +def test_a_string_upper_bound_rounds_outward_on_a_date_column(app: Flask) -> None: + """ + The upper bound moves the other way, and by a whole day: the engine compares + `event_date < DATE '2026-01-20'`, and the mirror must not exclude the + partition the boundary rows live in. + """ + table = _mapped_date_table() + + with app.app_context(): + with _rendering_dates_like_presto(table): + with patch(PROBE, return_value=[1768953600]) as probe: + sql = _query( + table, + filter=[ + { + "col": "event_date", + "op": FilterOperator.LESS_THAN.value, + "val": "2026-01-20 10:00:00", + } + ], + ) + + assert probe.call_args.args[-1] == [datetime(2026, 1, 21, 0, 0)] + assert "dt_epoch <= 1768953600" in sql + + +def test_a_date_only_string_equality_still_mirrors_unchanged(app: Flask) -> None: + """ + A value the engine compares in full is left exactly as it arrived, down to + the type the probe binds: reading the string back into an instant would + change the bind type and the probe's cache key on a query that was already + correct, for no gain at all. + """ + table = _mapped_date_table() + + with app.app_context(): + with _rendering_dates_like_presto(table): + with patch(PROBE, return_value=[1767225600]) as probe: + sql = _query( + table, + filter=[ + { + "col": "event_date", + "op": FilterOperator.EQUALS.value, + "val": "2026-01-01", + } + ], + ) + + assert probe.call_args.args[-1] == ["2026-01-01"] + assert "dt_epoch = 1767225600" in sql + + +def test_a_datetime_lower_bound_rounds_outward_instead_of_declining( + app: Flask, +) -> None: + """ + A `datetime` bound used to reach the same guard an equality does and + decline, which left a string and the `datetime` denoting the same instant + mirroring differently -- the asymmetry that let the string case above go + unnoticed. A bound widens whichever shape it arrived in. + """ + table = _mapped_date_table() + + with app.app_context(): + with _rendering_dates_like_presto(table): + with patch(PROBE, return_value=[1767571200]) as probe: + sql = _query( + table, + filter=[ + { + "col": "event_date", + "op": FilterOperator.GREATER_THAN_OR_EQUALS.value, + "val": datetime(2026, 1, 5, 10, 0), + } + ], + ) + + assert probe.call_args.args[-1] == [datetime(2026, 1, 5, 0, 0)] + assert "dt_epoch >= 1767571200" in sql + + +def test_a_string_filter_value_is_untouched_on_a_full_precision_engine( + app: Flask, +) -> None: + """ + The widening is detected, not enumerated per engine. An engine whose + literal carries the whole value needs none of this, and substituting an + instant for the text would move the probe off the cache entry every + already-correct query shares. + """ + table = _table() + + with app.app_context(): + with patch.object( + table.database.db_engine_spec, + "convert_dttm", + classmethod(lambda cls, target_type, dttm, db_extra=None: repr(dttm)), + ): + with patch(PROBE, return_value=[1]) as probe: + _query( + table, + filter=[ + { + "col": "event_time", + "op": FilterOperator.GREATER_THAN_OR_EQUALS.value, + "val": "2026-01-05 10:00:00", + } + ], + ) + + assert probe.call_args.args[-1] == ["2026-01-05 10:00:00"] + + +def test_a_string_in_list_is_untouched_on_a_full_precision_engine( + app: Flask, +) -> None: + """The `IN` counterpart: the new element-wise routing disturbs nothing.""" + table = _table() + + with app.app_context(): + with patch.object( + table.database.db_engine_spec, + "convert_dttm", + classmethod(lambda cls, target_type, dttm, db_extra=None: repr(dttm)), + ): + with patch(PROBE, return_value=[1, 2]) as probe: + sql = _query( + table, + filter=[ + { + "col": "event_time", + "op": FilterOperator.IN.value, + "val": ["2026-01-01 10:00:00", "2026-01-02 09:00:00"], + } + ], + ) + + assert probe.call_args.args[-1] == [ + "2026-01-01 10:00:00", + "2026-01-02 09:00:00", + ] + assert "dt_epoch IN (1, 2)" in sql + + [email protected]( + "operator, value", + [ + (FilterOperator.EQUALS.value, "07/06/2026 10:08:11"), + (FilterOperator.EQUALS.value, "2026/07/06 10:08:11"), + (FilterOperator.EQUALS.value, "July 6 2026 10:08:11"), + (FilterOperator.GREATER_THAN_OR_EQUALS.value, "07/05/2026 10:00:00"), + (FilterOperator.LESS_THAN.value, "07/20/2026"), + # The date-only form, which mirrored correctly by luck -- it denotes + # midnight, so probing the whole instant and probing the date agreed. + # It declines with the rest, because telling July 6 from June 7 is the + # guess that started this. + (FilterOperator.EQUALS.value, "06/07/2026"), + ], + ids=[ + "us-equality", + "ymd-slashes", + "month-name", + "lower-bound", + "upper-bound", + "date-only", + ], +) +def test_an_unreadable_string_does_not_mirror_on_a_date_column( + app: Flask, operator: str, value: str +) -> None: + """ + Only ISO 8601 is read, because it is the one format whose meaning is not a + guess: ``'06/07/2026'`` as July 6 or as June 7 is a coin flip, and a mirror + derived from the wrong one is the silent narrowing all of this is here to + prevent. + + Passing such a value through to the probe was the same narrowing by another + door. Postgres casts ``'07/06/2026 10:08:11'`` to a DATE and keeps the whole + of July 6, while the probe evaluated the transform at 10:08:11 and asked for + ``day_epoch = 1783332491`` -- a key no row holds. The chart returned 0 rows + where the filter matched 13, with no error and the pruning glyph still + showing. + + A bound declines too, where a readable one widens: rounding outward needs an + instant to round, and there is none. + """ + table = _mapped_date_table() + + with app.app_context(): + with _rendering_dates_like_presto(table): + with patch(PROBE, return_value=[1]) as probe: + sql = _query( + table, + filter=[{"col": "event_date", "op": operator, "val": value}], + ) + + probe.assert_not_called() + assert "dt_epoch" not in sql + # The filter the author wrote is still there, so the query is correct and + # merely unpruned -- the one failure this feature is allowed. + assert f"'{value}'" in sql + + +def test_an_unreadable_member_declines_an_in_list_on_a_date_column( + app: Flask, +) -> None: + """ + One member the server cannot place is the whole list's problem, exactly as + one carrying a time of day is: dropping just that member mirrors a narrower + list than the filter and loses the rows it named. + """ + table = _mapped_date_table() + + with app.app_context(): + with _rendering_dates_like_presto(table): + with patch(PROBE, return_value=[1, 2]) as probe: + sql = _query( + table, + filter=[ + { + "col": "event_date", + "op": FilterOperator.IN.value, + "val": ["2026-07-06", "07/07/2026 09:00:00"], + } + ], + ) + + probe.assert_not_called() + assert "dt_epoch" not in sql + + +def test_an_unreadable_string_is_left_alone_on_a_full_precision_engine( + app: Flask, +) -> None: + """ + The decline belongs to the coarseness, not to the format. On a TIMESTAMP + column the engine compares the whole string the chart author typed, so there + is nothing for the server to resolve and the probe is handed the text as it + arrived -- which is what keeps the same non-ISO filter working on a + timestamp mapped column while it declines on a DATE one. + """ + table = _table() + + with app.app_context(): + with patch.object( + table.database.db_engine_spec, + "convert_dttm", + classmethod(lambda cls, target_type, dttm, db_extra=None: repr(dttm)), + ): + with patch(PROBE, return_value=[1783332491]) as probe: + sql = _query( + table, + filter=[ + { + "col": "event_time", + "op": FilterOperator.EQUALS.value, + "val": "07/06/2026 10:08:11", + } + ], + ) + + assert probe.call_args.args[-1] == ["07/06/2026 10:08:11"] + assert "dt_epoch = 1783332491" in sql + + +def _on_postgres(table: SqlaTable) -> SqlaTable: + """ + Re-point the dataset at Postgres, whose driver over-escapes a backslash. + + SQLAlchemy only learns whether the server honours a backslash escape when + the dialect connects, which the probe's dialect never does, so it renders + ``a\\b`` as ``'a\\\\b'`` and a server with the standard setting reads four + characters. A real backend rather than a patched helper, so the test is + tied to what the installed driver actually emits. + """ + table.database.sqlalchemy_uri = "postgresql://user@host/db" + return table + + +def test_a_value_the_engine_cannot_read_back_declines_rather_than_mirroring( + app: Flask, +) -> None: + """ + The probe evaluates the transform at a literal, so a value the driver and + the engine spell differently would be probed as something nobody filtered + on -- and the mirror would ask for a partition key holding none of the rows + the filter keeps. Declining costs the query its pruning and nothing else. + """ + table = _on_postgres( + _table( + transform="lower(:value)", + monotonic=False, + mapped_column="country", + partition_mapped_column="country", + partition_column="region_key", + ) + ) + + with app.app_context(): + with patch(PROBE, return_value=["a\\b"]) as probe: + sql = _query( + table, + filter=[ + { + "col": "country", + "op": FilterOperator.EQUALS.value, + "val": "a\\b", + } + ], + ) + + probe.assert_not_called() + assert "region_key" not in sql + # The filter the author wrote is untouched, so the query is correct and + # merely unpruned. + assert "country = 'a" in sql + + +def test_a_member_the_dialect_cannot_write_faithfully_declines_the_whole_list( + app: Flask, +) -> None: + """ + One member is the whole list's problem, exactly as one carrying a UTC + offset is: mirroring the rest would be narrower than the filter and would + lose the rows it named. + """ + table = _on_postgres( + _table( + transform="lower(:value)", + monotonic=False, + mapped_column="country", + partition_mapped_column="country", + partition_column="region_key", + ) + ) + + with app.app_context(): + with patch(PROBE, return_value=["us", "a\\b"]) as probe: + sql = _query( + table, + filter=[ + { + "col": "country", + "op": FilterOperator.IN.value, + "val": ["US", "a\\b"], + } + ], + ) + + probe.assert_not_called() + assert "region_key" not in sql + + +def test_a_quote_bearing_value_still_mirrors_where_the_engine_reads_it_back( + app: Flask, +) -> None: + """ + And the gate is not a blanket refusal of the character. On SQLite the + driver's doubling is the engine's own escape, so the probe asks about the + value the filter compares and the mirror stands. + """ + table = _table( + transform="lower(:value)", + monotonic=False, + mapped_column="country", + partition_mapped_column="country", + partition_column="region_key", + ) + + with app.app_context(): + with patch(PROBE, return_value=["o'brien"]) as probe: + sql = _query( + table, + filter=[ + { + "col": "country", + "op": FilterOperator.EQUALS.value, + "val": "O'Brien", + } + ], + ) + + probe.assert_called_once() + assert "region_key = 'o''brien'" in sql + + +def test_an_offset_bearing_bound_declines_rather_than_narrowing(app: Flask) -> None: + """ + Rounding an offset-bearing bound outward would first have to place it in the + frame the partition column was written in, and guessing that frame is how a + mirror ends up a whole offset narrower than the filter. Declining costs the + query its pruning and nothing else. + """ + table = _mapped_date_table() + + with app.app_context(): + with _rendering_dates_like_presto(table): + with patch(PROBE, return_value=[1]) as probe: + sql = _query( + table, + filter=[ + { + "col": "event_date", + "op": FilterOperator.GREATER_THAN_OR_EQUALS.value, + "val": "2026-01-05T10:00:00+02:00", + } + ], + ) + + probe.assert_not_called() + assert "dt_epoch" not in sql + + [email protected]( + "operator", + [FilterOperator.EQUALS, FilterOperator.GREATER_THAN_OR_EQUALS], +) +def test_an_offset_bearing_string_declines_on_a_full_resolution_column( + app: Flask, operator: FilterOperator +) -> None: + """ + An engine that compares the whole instant still does not help a transform + that drops the offset. On a TIMESTAMPTZ column with + `extract(epoch from CAST(:value AS TIMESTAMP))`, a row at + `2026-01-01 00:00:00Z` matches `event_time = '2026-01-01T01:00:00+01:00'`, + but the cast discards the `+01:00` and the probe asks for the key an hour + later -- so the mirror would exclude the row. Declined like any other + offset-bearing value. + """ + table = _table() + + with app.app_context(): + with patch(PROBE, return_value=[1767229200]) as probe: + sql = _query( + table, + filter=[ + { + "col": "event_time", + "op": operator.value, + "val": "2026-01-01T01:00:00+01:00", + } + ], + ) + + probe.assert_not_called() + assert "dt_epoch" not in sql + + [email protected]( + "native_type, filter_value, probed", + [ + # The engine compares `n = '1.0'` numerically and keeps the row holding + # 1, so the probe has to see the 1 the row holds, not the 1.0 typed. + ("INTEGER", "1.0", [1]), + ("INTEGER", 1.0, [1]), + # A non-integral value matches no integer row; nothing to protect. + ("INTEGER", 1.5, [1.5]), + # A REAL column is left alone: PostgreSQL renders the column's own + # value as `'1'` but the literal `1.0` as `'1.0'`, so coercing would + # mirror onto a key no matching row holds. + ("REAL", 1, [1]), + # A DECIMAL's text depends on a scale the spec does not expose. + ("DECIMAL", 1, [1]), + ], +) +def test_a_numeric_equality_is_probed_in_the_column_s_number_type( + app: Flask, native_type: str, filter_value: Any, probed: list[Any] +) -> None: + """ + A transform such as `CAST(:value AS TEXT)` answers differently for `1` and + `1.0`, so probing at the number the filter happened to carry -- rather than + the one the column stores -- mirrors onto a partition key no matching row + holds. + """ + table = _table(partition_column="region_key", mapped_column="n") + table.columns.append(TableColumn(column_name="n", type=native_type)) + table.partition_mapped_column = "n" + table.columns[-1].partition_value_transform = "CAST(:value AS TEXT)" + table.columns[-1].partition_transform_is_monotonic = False + + with app.app_context(): + with patch(PROBE, return_value=["1"]) as probe: + _query( + table, + filter=[ + { + "col": "n", + "op": FilterOperator.EQUALS.value, + "val": filter_value, + } + ], + ) + + assert probe.call_args.args[-1] == probed + assert [type(value) for value in probe.call_args.args[-1]] == [ + type(value) for value in probed + ] + + +def test_a_non_temporal_string_equality_is_unaffected(app: Flask) -> None: + """ + Every string value is now offered to an ISO parse, so the text mapping most + of this file exercises has to come out the other side unchanged: `'US'` does + not parse, and a VARCHAR column's `convert_dttm` answers nothing either way. + """ + table = _mapped_country_table() + + with app.app_context(): + with patch(PROBE, return_value=["us"]) as probe: + sql = _query( + table, + filter=[ + {"col": "country", "op": FilterOperator.EQUALS.value, "val": "US"} + ], + ) + + assert probe.call_args.args[-1] == ["US"] + assert "region_key = 'us'" in sql + + +def test_a_rounded_string_bound_dedupes_against_the_time_filter(app: Flask) -> None: + """ + Rounding a string bound outward lands it on the same instant the chart's own + time filter already contributed, so the two collapse into one request -- + which is what the dedupe in `_build_partition_mirror_predicates` promises + and what a reader of "View query" expects to see. + """ + table = _mapped_date_table() + + with app.app_context(): + with _rendering_dates_like_presto(table): + with patch(PROBE, return_value=[1767571200, 1768953600]): + sql = _query( + table, + granularity="event_date", + from_dttm=datetime(2026, 1, 5), + to_dttm=datetime(2026, 1, 20), + filter=[ + { + "col": "event_date", + "op": FilterOperator.GREATER_THAN_OR_EQUALS.value, + "val": "2026-01-05 10:00:00", + } + ], + ) + + assert sql.count("dt_epoch >=") == 1 + + +def test_one_probe_round_trip_per_query(app: Flask) -> None: + """Mirror requests are collected and resolved together, not one at a time.""" + table = _table() + + with app.app_context(): + with patch(PROBE, return_value=[1767225600, 1769904000]) as probe: + _query( + table, + granularity="event_time", + from_dttm=datetime(2026, 1, 1), + to_dttm=datetime(2026, 2, 1), + filter=[ + { + "col": "event_time", + "op": FilterOperator.TEMPORAL_RANGE.value, + "val": "2026-01-01 : 2026-02-01", + } + ], + ) + + probe.assert_called_once() + + +def test_mirrored_predicates_reach_the_series_limit_subquery(app: Flask) -> None: + """ + The mirrored predicate is appended to ``where_clause_and``, which the + series-limit subquery reuses -- so pruning applies there too. + """ + table = _table() + + with app.app_context(): + with patch(PROBE, return_value=[1767225600, 1769904000]): + sql = _query( + table, + columns=["country"], + metrics=["hits"], + granularity="event_time", + is_timeseries=True, + from_dttm=datetime(2026, 1, 1), + to_dttm=datetime(2026, 2, 1), + timeseries_limit=5, + timeseries_limit_metric="hits", + ) + + assert sql.count("dt_epoch >= 1767225600") >= 2 + + +def _split_series_limit(sql: str) -> tuple[str, str]: + """The ranking subquery and the outer query, as separate text.""" + _, marker, rest = sql.partition("JOIN (SELECT") + assert marker, "expected a series-limit subquery" + subquery, _, outer = rest.partition(") AS series_limit") + return subquery, outer + + +def test_the_series_limit_subquery_mirrors_the_inner_time_window( + app: Flask, +) -> None: + """ + The ranking subquery filters the *inner* window, not the outer one. On a + time comparison `processing_time_offsets` shifts `from_dttm`/`to_dttm` to + the comparison period and leaves `inner_*` on the original, so an + outer-window mirror in the subquery demands partition keys from a period + the subquery's own time filter excludes: the ranking comes back empty and + the join drops the comparison series entirely. + """ + table = _table() + epochs = { + datetime(2025, 1, 1): 1735689600, + datetime(2025, 2, 1): 1738368000, + datetime(2026, 1, 1): 1767225600, + datetime(2026, 2, 1): 1769904000, + } + + with app.app_context(): + with patch( + PROBE, side_effect=lambda *args, **kwargs: [epochs[v] for v in args[-1]] + ): + sql = _query( + table, + columns=["country"], + metrics=["hits"], + granularity="event_time", + is_timeseries=True, + from_dttm=datetime(2025, 1, 1), + to_dttm=datetime(2025, 2, 1), + inner_from_dttm=datetime(2026, 1, 1), + inner_to_dttm=datetime(2026, 2, 1), + timeseries_limit=5, + timeseries_limit_metric="hits", + ) + + subquery, outer = _split_series_limit(sql) + + # The subquery asks for the window its own time filter describes. + assert "dt_epoch >= 1767225600" in subquery + assert "dt_epoch >= 1735689600" not in subquery + + # The outer query still asks for the window it was given. + assert "dt_epoch >= 1735689600" in outer + assert "dt_epoch >= 1767225600" not in outer + + +def test_the_series_limit_prequery_mirrors_the_inner_window(app: Flask) -> None: + """ + The other series-limit branch is already right, for a different reason: it + re-enters `query()` with `inner_from_dttm or from_dttm` as its own + `from_dttm`, so the mirrors are recomputed for the inner window rather than + inherited. Pinned so a future refactor cannot quietly break the half that + works. + """ + table = _table() + captured: list[dict[str, Any]] = [] + + def _capture(query_obj: dict[str, Any]) -> Any: + captured.append(query_obj) + return MagicMock(df=pd.DataFrame({"country": []})) + + with app.app_context(): + with patch.object(table.database.db_engine_spec, "allows_joins", False): + with patch(PROBE, return_value=[1767225600, 1769904000]): + with patch.object(type(table), "query", side_effect=_capture): + _query( + table, + columns=["country"], + metrics=["hits"], + granularity="event_time", + is_timeseries=True, + from_dttm=datetime(2025, 1, 1), + to_dttm=datetime(2025, 2, 1), + inner_from_dttm=datetime(2026, 1, 1), + inner_to_dttm=datetime(2026, 2, 1), + timeseries_limit=5, + timeseries_limit_metric="hits", + ) + + assert captured, "expected a prequery" + assert captured[0]["from_dttm"] == datetime(2026, 1, 1) + assert captured[0]["to_dttm"] == datetime(2026, 2, 1) + + +def test_one_probe_round_trip_when_both_windows_match(app: Flask) -> None: + """ + The inner sink is collected unconditionally, but building its predicates a + second time would cost a second probe round trip. Every query that is not a + time comparison asks for the same thing twice, so the result is reused. + """ + table = _table() + + with app.app_context(): + with patch(PROBE, return_value=[1767225600, 1769904000]) as probe: + _query( + table, + columns=["country"], + metrics=["hits"], + granularity="event_time", + is_timeseries=True, + from_dttm=datetime(2026, 1, 1), + to_dttm=datetime(2026, 2, 1), + timeseries_limit=5, + timeseries_limit_metric="hits", + ) + + probe.assert_called_once() + + +def test_the_series_limit_subquery_keeps_row_level_security(app: Flask) -> None: + """ + The subquery takes a snapshot of `where_clause_and` from before the mirrors + are appended. That snapshot has to be taken *after* row level security and + the extras `where` go on, or swapping the mirrors would quietly drop them + from the ranking subquery -- which for row level security would be a + security bug, not a pruning one. + """ + table = _table() + + with app.app_context(): + with patch.object( + type(table), + "get_sqla_row_level_filters", + return_value=[sa.text("country = 'US'")], + ): + with patch(PROBE, return_value=[1767225600, 1769904000]): + sql = _query( + table, + columns=["country"], + metrics=["hits"], + granularity="event_time", + is_timeseries=True, + from_dttm=datetime(2026, 1, 1), + to_dttm=datetime(2026, 2, 1), + timeseries_limit=5, + timeseries_limit_metric="hits", + extras={"where": "region_key <> 'x'"}, + ) + + subquery, _ = _split_series_limit(sql) + assert "country = 'US'" in subquery + assert "region_key <> 'x'" in subquery + + +def _mapped_country_table() -> SqlaTable: + """A string-column mapping: ``country`` onto ``region_key``.""" + return _table( + transform="lower(:value)", + monotonic=False, + mapped_column="country", + partition_mapped_column="country", + partition_column="region_key", + ) + + +def test_string_equality_does_not_mirror_on_a_case_insensitive_engine( + app: Flask, +) -> None: + """ + `col = v` mirrors onto `partition_col = T(v)` on the strength of `col = v` + implying `T(col) = T(v)` -- true of value equality, false of SQL equality + under any comparison that is not byte-exact. With a case-insensitive + collation a stored `country` of `'us'` satisfies a filter for `'US'` while + the mirror `region_key = 'us'` is derived from `'US'` and excludes the row, + so the chart loses it with nothing to indicate why. + """ + table = _mapped_country_table() + + with app.app_context(): + with patch.object( + table.database.db_engine_spec, "binary_string_comparison", False + ): + with patch(PROBE, return_value=["us"]) as probe: + sql = _query( + table, + filter=[ + { + "col": "country", + "op": FilterOperator.EQUALS.value, + "val": "US", + } + ], + ) + + assert "region_key" not in sql + assert "country = 'US'" in sql + probe.assert_not_called() + + +def test_string_equality_still_mirrors_on_a_byte_exact_engine(app: Flask) -> None: + """ + The gate must not cost the feature its second canonical mapping on the + engines it exists for, all of which compare text byte-exactly. + """ + table = _mapped_country_table() + + with app.app_context(): + with patch(PROBE, return_value=["us"]): + sql = _query( + table, + filter=[ + {"col": "country", "op": FilterOperator.EQUALS.value, "val": "US"} + ], + ) + + assert "region_key = 'us'" in sql + + +def test_a_numeric_mapped_column_is_unaffected_by_the_collation_gate( + app: Flask, +) -> None: + """Numeric comparison is exact on every engine.""" + table = _table( + transform="CAST(:value AS STRING)", + monotonic=False, + mapped_column="dt_epoch", + partition_mapped_column="dt_epoch", + partition_column="region_key", + ) + + with app.app_context(): + with patch.object( + table.database.db_engine_spec, "binary_string_comparison", False + ): + with patch(PROBE, return_value=["17"]): + sql = _query( + table, + filter=[ + { + "col": "dt_epoch", + "op": FilterOperator.EQUALS.value, + "val": 17, + } + ], + ) + + assert "region_key = '17'" in sql + + +def test_the_summary_withdraws_the_operators_the_query_path_withdraws( + app: Flask, +) -> None: + """ + The glyph reads `mirrorable_operators` off this summary, so the gate has to + reach it or Explore would mark a filter chip as pruned while the generated + SQL carries no partition predicate -- which is the whole class of bug the + indicator work has been closing. + """ + table = _mapped_country_table() + + with app.app_context(): + with patch.object( + table.database.db_engine_spec, "binary_string_comparison", False + ): + summary = table.partition_filter_mapping_summary + + assert summary is not None + assert summary["mirrorable_operators"] == [] + + unguarded = table.partition_filter_mapping_summary + + assert unguarded is not None + assert unguarded["mirrorable_operators"] == ["==", "IN"] + + +def test_extra_cache_keys_include_the_mapping(app: Flask) -> None: + """ + The mapping changes the SQL a cached chart result came from, so it has to + participate in the chart-data cache key or a mapping fix leaves stale pruned + results behind. + """ + table = _table() + + with app.app_context(): + keys = table.get_extra_cache_keys({}) + + assert any("dt_epoch" in str(key) for key in keys) Review Comment: This only asserts that some key mentions `dt_epoch`, so dropping `mapping.value_transform` and `mapping.is_monotonic` from the `partition_filter_mapping` tuple in `get_extra_cache_keys` leaves it green. If the monotonic declaration flips without the key changing, a cached chart keeps the old pruned-vs-unpruned SQL result. Could this build the same table twice, flipping only `partition_transform_is_monotonic` (and separately only the transform text), and assert the key changes each time while an unchanged table stays stable? -- 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]
