You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@superset.apache.org by jl...@apache.org on 2026/08/20 21:55:15 UTC
(superset) branch master updated: fix(charts): align grain-less time comparisons safely (#43315)
This is an automated email from the ASF dual-hosted git repository.
sadpandajoe pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/superset.git
The following commit(s) were added to refs/heads/master by this push:
new 7441ce90aea fix(charts): align grain-less time comparisons safely (#43315)
7441ce90aea is described below
commit 7441ce90aea6285c6d753ed31d0d29967f7cb1c3
Author: Joe Li <jo...@preset.io>
AuthorDate: Thu Aug 20 14:55:01 2026 -0700
fix(charts): align grain-less time comparisons safely (#43315)
---
superset/models/helpers.py | 320 +++++++++++++++++++---
superset/utils/date_parser.py | 24 +-
tests/integration_tests/query_context_tests.py | 52 ++++
tests/unit_tests/common/test_time_shifts.py | 354 ++++++++++++++++++++++++-
tests/unit_tests/utils/date_parser_tests.py | 5 +
5 files changed, 697 insertions(+), 58 deletions(-)
diff --git a/superset/models/helpers.py b/superset/models/helpers.py
index ebfc43e6b1b..1344e7876d1 100644
--- a/superset/models/helpers.py
+++ b/superset/models/helpers.py
@@ -131,6 +131,7 @@ from superset.utils.core import (
DTTM_ALIAS,
FilterOperator,
GenericDataType,
+ get_base_axis_columns,
get_base_axis_labels,
get_column_name,
get_column_names,
@@ -210,6 +211,189 @@ def _as_wall_clock(series: pd.Series) -> pd.Series:
return series
+class _TemporalColumnMetadata(NamedTuple):
+ """Temporal metadata resolved from a physical dataset column."""
+
+ is_temporal: bool = False
+ python_date_format: str | None = None
+
+
+def _get_temporal_physical_column_metadata(
+ datasource: Any, column_name: str | None
+) -> _TemporalColumnMetadata:
+ """Resolve temporal metadata using the physical column's precedence rules."""
+ if not column_name or not hasattr(datasource, "get_column"):
+ return _TemporalColumnMetadata()
+ column = datasource.get_column(column_name.strip())
+ if not column:
+ return _TemporalColumnMetadata()
+ if isinstance(column, dict):
+ declared_is_dttm = column.get("is_dttm")
+ is_temporal = (
+ bool(declared_is_dttm)
+ if declared_is_dttm is not None
+ else column.get("type_generic") == GenericDataType.TEMPORAL
+ )
+ python_date_format = column.get("python_date_format")
+ else:
+ is_temporal_property = getattr(column, "is_temporal", None)
+ if is_temporal_property is not None:
+ is_temporal = bool(is_temporal_property)
+ else:
+ declared_is_dttm = getattr(column, "is_dttm", None)
+ is_temporal = (
+ bool(declared_is_dttm)
+ if declared_is_dttm is not None
+ else getattr(column, "type_generic", None) == GenericDataType.TEMPORAL
+ )
+ python_date_format = getattr(column, "python_date_format", None)
+ return _TemporalColumnMetadata(
+ is_temporal=is_temporal,
+ python_date_format=(str(python_date_format) if python_date_format else None),
+ )
+
+
+def _temporal_axis_parse_error(column_name: str) -> QueryObjectValidationError:
+ """Build the user-facing error for an unparseable temporal join axis."""
+ return QueryObjectValidationError(
+ _(
+ "Unable to align time comparison because temporal axis "
+ "'%(column)s' contains values that cannot be parsed as datetimes. "
+ "Update the column's datetime format or choose a valid temporal "
+ "column.",
+ column=column_name,
+ )
+ )
+
+
+def _apply_temporal_join_format(
+ series: pd.Series,
+ column_name: str,
+ datetime_format: str | None,
+) -> pd.Series:
+ """Apply a dataset column's declared datetime format to working values."""
+ if not datetime_format:
+ return series
+ working_df = pd.DataFrame({column_name: series.copy()})
+ normalize_dttm_col(
+ working_df,
+ (
+ DateColumn(
+ col_label=column_name,
+ timestamp_format=datetime_format,
+ ),
+ ),
+ )
+ return working_df[column_name]
+
+
+def _parse_temporal_join_values(series: pd.Series, column_name: str) -> pd.Series:
+ """Parse working temporal values and wrap pandas parser-policy errors."""
+ if pd.api.types.is_datetime64_any_dtype(series):
+ return series
+ try:
+ return pd.to_datetime(series, errors="coerce", format="mixed")
+ except (TypeError, ValueError) as ex:
+ raise _temporal_axis_parse_error(column_name) from ex
+
+
+def _retry_temporal_join_values_at_wider_resolution(
+ series: pd.Series,
+ column_name: str,
+ datetime_format: str | None,
+) -> pd.Series:
+ """Retry valid values outside pandas' nanosecond datetime range."""
+ resolution = "ms" if datetime_format == "epoch_ms" else "s"
+ try:
+ if datetime_format and datetime_format not in {"epoch_s", "epoch_ms"}:
+ parsed_values = [
+ datetime.strptime(str(value), datetime_format)
+ if pd.notna(value)
+ else None
+ for value in series
+ ]
+ else:
+ parsed_values = series
+ converted = pd.Series(
+ pd.array(parsed_values, dtype=f"datetime64[{resolution}]"),
+ index=series.index,
+ name=series.name,
+ )
+ except (OverflowError, TypeError, ValueError) as ex:
+ raise _temporal_axis_parse_error(column_name) from ex
+ if (series.notna() & converted.isna()).any():
+ raise _temporal_axis_parse_error(column_name)
+ return converted
+
+
+def _coerce_temporal_join_series(
+ series: pd.Series,
+ column_name: str,
+ datetime_format: str | None = None,
+) -> pd.Series:
+ """Parse a temporal join series losslessly and normalize it to wall clocks."""
+ if series.isna().all():
+ return series
+
+ converted = _apply_temporal_join_format(series, column_name, datetime_format)
+ converted = _parse_temporal_join_values(converted, column_name)
+ if (series.notna() & converted.isna()).any():
+ converted = _retry_temporal_join_values_at_wider_resolution(
+ series, column_name, datetime_format
+ )
+
+ if isinstance(converted.dtype, pd.DatetimeTZDtype):
+ return converted.dt.tz_localize(None)
+ if pd.api.types.is_datetime64_any_dtype(converted):
+ return converted
+
+ def as_wall_clock(value: Any) -> pd.Timestamp:
+ if pd.isna(value):
+ return pd.NaT
+ timestamp = pd.Timestamp(value)
+ return timestamp.tz_localize(None) if timestamp.tzinfo else timestamp
+
+ return converted.map(as_wall_clock)
+
+
+def _has_multiple_utc_offsets(series: pd.Series) -> bool:
+ """Return whether every value is timezone-aware and offsets differ."""
+ utc_offsets: set[timedelta | None] = set()
+ for value in series.dropna():
+ try:
+ timestamp = pd.Timestamp(value)
+ except (TypeError, ValueError):
+ return False
+ if timestamp.tzinfo is None:
+ return False
+ utc_offsets.add(timestamp.utcoffset())
+ return len(utc_offsets) > 1
+
+
+def _shift_grainless_temporal_source(
+ source: pd.Series,
+ offset: str,
+ delta: DateOffset | None,
+) -> pd.Series:
+ """Shift a temporal join source after validating free-form anchors."""
+ if delta is None and not is_constant_human_timedelta(offset):
+ raise QueryObjectValidationError(
+ _("Time Grain must be specified when using Time Comparison.")
+ )
+ if source.isna().all():
+ return source.map(lambda value: pd.NaT)
+ if delta is not None:
+ return source + delta
+
+ def shift(value: pd.Timestamp) -> pd.Timestamp:
+ if pd.isna(value):
+ return value
+ truncated = value.floor("s").to_pydatetime()
+ return value + (get_past_or_future(offset, truncated) - truncated)
+
+ return source.map(shift)
+
+
class CachedTimeOffset(TypedDict):
"""Result type for time offset processing"""
@@ -2571,6 +2755,16 @@ class ExploreMixin: # pylint: disable=too-many-public-methods
offset_dfs[offset] = offset_metrics_df
if offset_dfs:
+ x_axis_columns = get_base_axis_columns(query_object.columns)
+ x_axis_expression = (
+ x_axis_columns[0].get("sqlExpression")
+ if x_axis_columns and isinstance(x_axis_columns[0], dict)
+ else None
+ )
+ x_axis_metadata = _get_temporal_physical_column_metadata(
+ self,
+ x_axis_expression if isinstance(x_axis_expression, str) else None,
+ )
df = self.join_offset_dfs(
df,
offset_dfs,
@@ -2578,6 +2772,8 @@ class ExploreMixin: # pylint: disable=too-many-public-methods
join_keys,
full_range=getattr(query_object, "time_compare_full_range", False),
x_axis_label=get_x_axis_label(query_object.columns),
+ x_axis_is_temporal=x_axis_metadata.is_temporal,
+ x_axis_datetime_format=x_axis_metadata.python_date_format,
)
return CachedTimeOffset(df=df, queries=queries, cache_keys=cache_keys)
@@ -2752,6 +2948,8 @@ class ExploreMixin: # pylint: disable=too-many-public-methods
is_date_range_offset: bool,
join_column_producer: Any,
x_axis_label: str | None = None,
+ x_axis_is_temporal: bool = False,
+ x_axis_datetime_format: str | None = None,
) -> tuple[pd.DataFrame, list[str]]:
"""Determine appropriate join keys and modify DataFrames if needed."""
if time_grain and not is_date_range_offset:
@@ -2781,7 +2979,13 @@ class ExploreMixin: # pylint: disable=too-many-public-methods
else:
return self._align_offset_without_time_grain(
- df, offset_df, offset, join_keys, x_axis_label
+ df,
+ offset_df,
+ offset,
+ join_keys,
+ x_axis_label,
+ x_axis_is_temporal,
+ x_axis_datetime_format,
)
def _align_offset_without_time_grain(
@@ -2791,6 +2995,8 @@ class ExploreMixin: # pylint: disable=too-many-public-methods
offset: str,
join_keys: list[str],
x_axis_label: str | None = None,
+ x_axis_is_temporal: bool = False,
+ x_axis_datetime_format: str | None = None,
) -> tuple[pd.DataFrame, list[str]]:
"""
Determine join keys for a relative offset when no time grain is set.
@@ -2810,9 +3016,15 @@ class ExploreMixin: # pylint: disable=too-many-public-methods
-- so the rows it returns carry the source wall clock, and matching on
it is what aligns the two series. Re-localizing the result would only
reintroduce the DST edge cases that wall-clock arithmetic sidesteps:
- shifting onto a skipped or repeated local hour raises out of pandas.
- Both sides are normalized identically, so the two readings of a
- repeated hour still align with each other.
+ shifting onto a skipped or repeated local hour raises out of pandas. A
+ single reading of a repeated hour aligns by wall clock. Distinct raw
+ values that normalize to the same working key are rejected because
+ they would fan out the merge; the error identifies a daylight-saving
+ fold when their UTC offsets differ.
+
+ A string-backed x-axis is parsed only when its physical dataset column
+ declares temporal metadata. Its configured datetime format is applied
+ to working join values without changing the displayed columns.
Month, quarter, and year offsets shift via ``DateOffset``, which clamps
to a valid calendar day (e.g. Mar 29, 30, and 31 all shift back one
@@ -2830,14 +3042,46 @@ class ExploreMixin: # pylint: disable=too-many-public-methods
for key in candidate_keys
if key in df.columns
and key in offset_df.columns
- and pd.api.types.is_datetime64_any_dtype(df[key])
+ and (
+ pd.api.types.is_datetime64_any_dtype(df[key])
+ or (key == x_axis_label and x_axis_is_temporal)
+ )
),
None,
)
if not temporal_join_key:
return offset_df, join_keys
- source = _as_wall_clock(df[temporal_join_key])
+ source_values = df[temporal_join_key]
+ offset_values = offset_df[temporal_join_key]
+ raw_offset_values = offset_values.copy()
+ remaining_keys = [key for key in join_keys if key != temporal_join_key]
+ raw_duplicate_mask = offset_df.duplicated(
+ subset=[temporal_join_key, *remaining_keys], keep=False
+ )
+ if x_axis_is_temporal and (
+ not pd.api.types.is_datetime64_any_dtype(source_values)
+ or not pd.api.types.is_datetime64_any_dtype(offset_values)
+ ):
+ if x_axis_datetime_format is None and any(
+ pd.api.types.is_numeric_dtype(values) and values.notna().any()
+ for values in (source_values, offset_values)
+ ):
+ # A numeric temporal axis without a declared format cannot be
+ # interpreted, so retain the pre-alignment raw-key join.
+ return offset_df, join_keys
+ source_values = _coerce_temporal_join_series(
+ source_values,
+ temporal_join_key,
+ x_axis_datetime_format,
+ )
+ offset_values = _coerce_temporal_join_series(
+ offset_values,
+ temporal_join_key,
+ x_axis_datetime_format,
+ )
+
+ source = _as_wall_clock(source_values)
try:
delta: DateOffset | None = DateOffset(**normalize_time_delta(offset))
@@ -2845,44 +3089,36 @@ class ExploreMixin: # pylint: disable=too-many-public-methods
delta = None
column_name = OFFSET_JOIN_COLUMN_SUFFIX + offset
- if delta is not None:
- # DateOffset addition is vectorized over the datetime column; NaT
- # rows shift to NaT (they join to the offset series' NaT rows).
- shifted = source + delta
- else:
- # Free-form offsets (e.g. "one year ago") don't match the
- # normalize_time_delta grammar; shift with the same parser that
- # shifted the offset query's time range. parsedatetime resolves
- # second resolution only, so compute each row's delta from a
- # truncated copy and apply it to the original value, preserving
- # sub-second precision.
- if not is_constant_human_timedelta(offset):
- # Anchors such as "yesterday" resolve every source time within
- # a day onto one timestamp rather than shifting each by a
- # fixed amount, so they cannot align two series row by row:
- # distinct timestamps would collapse onto a single join key.
- # A time grain gives the join a truncated column to match on
- # instead of a shifted one.
- raise QueryObjectValidationError(
- _("Time Grain must be specified when using Time Comparison.")
- )
-
- def shift(value: pd.Timestamp) -> pd.Timestamp:
- if pd.isna(value):
- return value
- truncated = value.floor("s").to_pydatetime()
- return value + (get_past_or_future(offset, truncated) - truncated)
-
- shifted = source.map(shift)
+ shifted = _shift_grainless_temporal_source(source, offset, delta)
# Join on string values so that mismatched key dtypes (e.g. an empty
# offset series materializes its join keys as NaN floats) cannot break
# the merge.
df[column_name] = shifted.map(str)
- offset_df[column_name] = _as_wall_clock(offset_df[temporal_join_key]).map(str)
+ offset_df[column_name] = _as_wall_clock(offset_values).map(str)
- remaining_keys = [key for key in join_keys if key != temporal_join_key]
- return offset_df, [column_name, *remaining_keys]
+ actual_join_keys = [column_name, *remaining_keys]
+ normalized_duplicate_mask = offset_df.duplicated(
+ subset=actual_join_keys, keep=False
+ )
+ introduced_duplicate_mask = normalized_duplicate_mask & ~raw_duplicate_mask
+ if introduced_duplicate_mask.any():
+ if _has_multiple_utc_offsets(raw_offset_values[normalized_duplicate_mask]):
+ message = _(
+ "Unable to align time comparison because the offset series "
+ "contains an ambiguous daylight-saving fold for the same "
+ "dimensions and local time. Add a Time Grain or filter the "
+ "source to one UTC offset."
+ )
+ else:
+ message = _(
+ "Unable to align time comparison because the temporal axis "
+ "contains distinct values that normalize to the same instant "
+ "for the same dimensions. Standardize the source values or "
+ "add a Time Grain."
+ )
+ raise QueryObjectValidationError(message)
+ return offset_df, actual_join_keys
def _perform_join(
self,
@@ -2928,6 +3164,8 @@ class ExploreMixin: # pylint: disable=too-many-public-methods
join_keys: list[str],
full_range: bool = False,
x_axis_label: str | None = None,
+ x_axis_is_temporal: bool = False,
+ x_axis_datetime_format: str | None = None,
) -> pd.DataFrame:
"""
Join offset DataFrames with the main DataFrame.
@@ -2942,6 +3180,10 @@ class ExploreMixin: # pylint: disable=too-many-public-methods
the current day is still in progress) are preserved.
:param x_axis_label: The query's temporal x-axis label, used to pick the
temporal join key when no time grain is set.
+ :param x_axis_is_temporal: Whether physical-column metadata declares the
+ x-axis temporal.
+ :param x_axis_datetime_format: The physical x-axis column's configured
+ Python datetime format.
"""
join_column_producer = app.config["TIME_GRAIN_JOIN_COLUMN_PRODUCERS"].get(
time_grain
@@ -2964,6 +3206,8 @@ class ExploreMixin: # pylint: disable=too-many-public-methods
is_date_range_offset,
join_column_producer,
x_axis_label,
+ x_axis_is_temporal,
+ x_axis_datetime_format,
)
# The full-range option is only meaningful for relative offsets aligned
diff --git a/superset/utils/date_parser.py b/superset/utils/date_parser.py
index aa51aa630db..ccbf07da8fd 100644
--- a/superset/utils/date_parser.py
+++ b/superset/utils/date_parser.py
@@ -61,14 +61,16 @@ logging.getLogger("parsedatetime").setLevel(logging.WARNING)
logger = logging.getLogger(__name__)
# Source times used by ``is_constant_human_timedelta`` to tell a delta from an
-# anchor. They share a date -- mid-month and mid-year, away from any month or
-# year boundary a shift could clamp against -- and differ only in the hour, so
-# that the sole thing the comparison can detect is sensitivity to time of day.
-# Neither hour is parsedatetime's 09:00 default, so an anchor cannot coincide
-# with a probe and masquerade as a zero shift.
-_SHIFT_PROBE_TIMES: tuple[datetime, datetime] = (
+# anchor. The first two share a date and differ only in the hour, detecting
+# sensitivity to time of day. The third changes the date and weekday, detecting
+# weekday and month-name anchors. All are mid-month and mid-year, away from any
+# boundary a relative shift could clamp against. Neither hour is parsedatetime's
+# 09:00 default, so an anchor cannot coincide with a probe and masquerade as a
+# zero shift.
+_SHIFT_PROBE_TIMES: tuple[datetime, ...] = (
datetime(2024, 6, 15, 3, 0, 0),
datetime(2024, 6, 15, 21, 0, 0),
+ datetime(2024, 6, 18, 3, 0, 0),
)
# Mapping of ordinal words to their numeric values for date expressions
@@ -179,11 +181,11 @@ def is_constant_human_timedelta(human_readable: str | None) -> bool:
timestamp (parsedatetime defaults to 09:00) no matter where the source
time sits within the day, so it shifts each row by a different amount.
- Probing two source times within the same day separates the two. A delta
- shifts both probes equally; an anchor maps both onto one timestamp, which
- -- the probes being distinct -- necessarily yields differing shifts. Both
- probes share a date, so calendar irregularities such as leap years and
- month lengths apply to them identically and cannot skew the comparison.
+ Probing source times across hours and dates separates the two. A delta
+ shifts every probe equally; an anchor depends on at least the source hour,
+ weekday, or month and therefore yields differing shifts. The probes avoid
+ calendar boundaries so leap years and month lengths cannot skew the
+ comparison.
"""
if not is_parseable_human_timedelta(human_readable):
return False
diff --git a/tests/integration_tests/query_context_tests.py b/tests/integration_tests/query_context_tests.py
index 3488cbacf64..00ab1234355 100644
--- a/tests/integration_tests/query_context_tests.py
+++ b/tests/integration_tests/query_context_tests.py
@@ -1313,6 +1313,58 @@ def test_time_grain_and_time_offset_with_base_axis(app_context, physical_dataset
)
+@only_sqlite
+@pytest.mark.parametrize("sql_expression", ["col6", " col6 "])
+def test_time_offset_without_grain_aligns_direct_custom_sql_temporal_axis(
+ app_context, physical_dataset, sql_expression
+):
+ """A direct Custom SQL reference uses its physical column's temporal type."""
+ column_on_axis: AdhocColumn = {
+ "label": "custom_col6",
+ "sqlExpression": sql_expression,
+ "columnType": "BASE_AXIS",
+ "isColumnReference": True,
+ }
+ qc = QueryContextFactory().create(
+ datasource={
+ "type": physical_dataset.type,
+ "id": physical_dataset.id,
+ },
+ queries=[
+ {
+ "columns": [column_on_axis],
+ "metrics": [
+ {
+ "label": "SUM(col1)",
+ "expressionType": "SQL",
+ "sqlExpression": "SUM(col1)",
+ }
+ ],
+ "time_offsets": ["32 days ago"],
+ "filters": [
+ {
+ "col": "col6",
+ "op": "TEMPORAL_RANGE",
+ "val": "2002-02-04 : 2002-04-13",
+ }
+ ],
+ }
+ ],
+ result_type=ChartDataResultType.FULL,
+ force=True,
+ )
+
+ df = qc.get_df_payload(qc.queries[0])["df"]
+
+ assert df["custom_col6"].tolist() == [
+ "2002-02-04 00:00:00",
+ "2002-03-07 00:00:00",
+ "2002-04-12 00:00:00",
+ ]
+ assert df["SUM(col1)"].tolist() == [1, 2, 3]
+ assert df["SUM(col1)__32 days ago"].tolist()[0] == 0
+
+
@only_sqlite
def test_time_grain_and_time_offset_on_legacy_query(app_context, physical_dataset):
qc = QueryContextFactory().create(
diff --git a/tests/unit_tests/common/test_time_shifts.py b/tests/unit_tests/common/test_time_shifts.py
index 908ce4233b2..99ed32994da 100644
--- a/tests/unit_tests/common/test_time_shifts.py
+++ b/tests/unit_tests/common/test_time_shifts.py
@@ -21,10 +21,14 @@ from pytest import fixture, mark, raises # noqa: PT013
from superset.common.chart_data import ChartDataResultFormat, ChartDataResultType
from superset.common.query_context import QueryContext
from superset.common.query_context_processor import QueryContextProcessor
-from superset.connectors.sqla.models import BaseDatasource
+from superset.connectors.sqla.models import BaseDatasource, TableColumn
from superset.constants import TimeGrain
from superset.exceptions import QueryObjectValidationError
-from superset.models.helpers import ExploreMixin
+from superset.models.helpers import (
+ _get_temporal_physical_column_metadata,
+ ExploreMixin,
+)
+from superset.utils.core import GenericDataType
# Create processor and bind ExploreMixin methods to datasource
processor = QueryContextProcessor(
@@ -371,6 +375,222 @@ def test_join_offset_dfs_no_time_grain_aligns_relative_offset() -> None:
assert_frame_equal(expected, result)
+def test_join_offset_dfs_no_time_grain_aligns_temporal_string_axis() -> None:
+ """A physical temporal x-axis is aligned even when its values are strings."""
+ df = DataFrame({"displayed_ds": ["2021-01-01T12:00:00"], "D": [1]})
+ offset_df = DataFrame({"displayed_ds": ["2020-01-01T12:00:00"], "B": [5]})
+
+ result = query_context_processor.join_offset_dfs(
+ df,
+ {"1 year ago": offset_df},
+ time_grain=None,
+ join_keys=["displayed_ds"],
+ x_axis_label="displayed_ds",
+ x_axis_is_temporal=True,
+ )
+
+ assert result["displayed_ds"].tolist() == ["2021-01-01T12:00:00"]
+ assert result["B"].tolist() == [5]
+
+
+def test_join_offset_dfs_rejects_unparseable_temporal_string_axis() -> None:
+ """Invalid values on a declared temporal x-axis fail instead of self-joining."""
+ df = DataFrame({"ds": ["not-a-date"], "D": [1]})
+ offset_df = DataFrame({"ds": ["not-a-date"], "B": [5]})
+ with raises(
+ QueryObjectValidationError,
+ match="contains values that cannot be parsed as datetimes",
+ ):
+ query_context_processor.join_offset_dfs(
+ df,
+ {"1 year ago": offset_df},
+ time_grain=None,
+ join_keys=["ds"],
+ x_axis_label="ds",
+ x_axis_is_temporal=True,
+ )
+
+
+def test_join_offset_dfs_no_time_grain_preserves_categorical_string_axis() -> None:
+ """Categorical x-axes retain the raw-key join used without a time grain."""
+ df = DataFrame({"category": ["alpha"], "D": [1]})
+ offset_df = DataFrame({"category": ["alpha"], "B": [5]})
+ result = query_context_processor.join_offset_dfs(
+ df,
+ {"1 year ago": offset_df},
+ time_grain=None,
+ join_keys=["category"],
+ x_axis_label="category",
+ )
+
+ assert result["B"].tolist() == [5]
+
+
+def test_join_offset_dfs_no_time_grain_aligns_mixed_offset_temporal_strings() -> None:
+ """Mixed UTC offsets are compared by their parsed local wall clocks."""
+ df = DataFrame(
+ {
+ "ds": [
+ "2021-03-20T12:00:00-04:00",
+ "2021-12-01T12:00:00-05:00",
+ ],
+ "D": [1, 2],
+ }
+ )
+ offset_df = DataFrame(
+ {
+ "ds": [
+ "2020-03-20T12:00:00-04:00",
+ "2020-12-01T12:00:00-05:00",
+ ],
+ "B": [5, 6],
+ }
+ )
+ result = query_context_processor.join_offset_dfs(
+ df,
+ {"1 year ago": offset_df},
+ time_grain=None,
+ join_keys=["ds"],
+ x_axis_label="ds",
+ x_axis_is_temporal=True,
+ )
+
+ assert result["B"].tolist() == [5, 6]
+
+
+def test_temporal_physical_column_honors_explicit_false(monkeypatch) -> None:
+ """Explicit non-temporal metadata takes precedence over inferred type."""
+ columns = [
+ {
+ "is_dttm": False,
+ "type_generic": GenericDataType.TEMPORAL,
+ },
+ TableColumn(column_name="ds", type="TIMESTAMP", is_dttm=False),
+ ]
+
+ for column in columns:
+ monkeypatch.setattr(
+ query_context_processor,
+ "get_column",
+ lambda column_name, column=column: column,
+ )
+ metadata = _get_temporal_physical_column_metadata(query_context_processor, "ds")
+ assert not metadata.is_temporal
+
+
+def test_temporal_physical_column_strips_expression(monkeypatch) -> None:
+ """Metadata lookup normalizes whitespace like SQL column resolution."""
+ looked_up: list[str] = []
+
+ def get_column(column_name: str) -> dict[str, bool | str]:
+ looked_up.append(column_name)
+ return {"is_dttm": True, "python_date_format": "epoch_s"}
+
+ monkeypatch.setattr(query_context_processor, "get_column", get_column)
+
+ metadata = _get_temporal_physical_column_metadata(query_context_processor, " ds ")
+
+ assert metadata.is_temporal
+ assert metadata.python_date_format == "epoch_s"
+ assert looked_up == ["ds"]
+
+
+def test_join_offset_dfs_no_time_grain_aligns_epoch_seconds_axis() -> None:
+ """A declared epoch-seconds temporal axis uses its metadata format."""
+ df = DataFrame({"ds": [1012780800], "D": [1]})
+ offset_df = DataFrame({"ds": [981244800], "B": [5]})
+
+ result = query_context_processor.join_offset_dfs(
+ df,
+ {"1 year ago": offset_df},
+ time_grain=None,
+ join_keys=["ds"],
+ x_axis_label="ds",
+ x_axis_is_temporal=True,
+ x_axis_datetime_format="epoch_s",
+ )
+
+ assert result["B"].tolist() == [5]
+
+
+def test_join_offset_dfs_no_time_grain_aligns_out_of_bounds_dates() -> None:
+ """Valid dates outside nanosecond bounds align at second resolution."""
+ df = DataFrame({"ds": ["2002-01-01", "9999-12-31"], "D": [1, 2]})
+ offset_df = DataFrame({"ds": ["2001-01-01", "9998-12-31"], "B": [5, 6]})
+
+ result = query_context_processor.join_offset_dfs(
+ df,
+ {"1 year ago": offset_df},
+ time_grain=None,
+ join_keys=["ds"],
+ x_axis_label="ds",
+ x_axis_is_temporal=True,
+ )
+
+ assert result["B"].tolist() == [5, 6]
+
+
+def test_join_offset_dfs_no_time_grain_out_of_bounds_respects_format() -> None:
+ """A wider-resolution retry preserves the declared strftime format."""
+ df = DataFrame({"ds": ["03/04/2022", "31/12/9999"], "D": [1, 2]})
+ offset_df = DataFrame({"ds": ["03/03/2022", "30/11/9999"], "B": [5, 6]})
+
+ result = query_context_processor.join_offset_dfs(
+ df,
+ {"1 month ago": offset_df},
+ time_grain=None,
+ join_keys=["ds"],
+ x_axis_label="ds",
+ x_axis_is_temporal=True,
+ x_axis_datetime_format="%d/%m/%Y",
+ )
+
+ assert result["B"].tolist() == [5, 6]
+
+
+def test_join_offset_dfs_numeric_temporal_without_format_uses_raw_key() -> None:
+ """An uninterpretable numeric temporal axis retains raw-key behavior."""
+ df = DataFrame({"ds": [1012780800], "D": [1]})
+ offset_df = DataFrame({"ds": [1012780800], "B": [5]})
+
+ result = query_context_processor.join_offset_dfs(
+ df,
+ {"1 year ago": offset_df},
+ time_grain=None,
+ join_keys=["ds"],
+ x_axis_label="ds",
+ x_axis_is_temporal=True,
+ )
+
+ assert result["B"].tolist() == [5]
+
+
+def test_join_offset_dfs_no_time_grain_wraps_datetime_parser_value_error(
+ monkeypatch,
+) -> None:
+ """A pandas parser-policy change remains a user-facing validation error."""
+ df = DataFrame({"ds": ["2021-01-01"], "D": [1]})
+ offset_df = DataFrame({"ds": ["2020-01-01"], "B": [5]})
+
+ def fail_to_parse(*args, **kwargs):
+ raise ValueError("mixed time zones require utc=True")
+
+ monkeypatch.setattr("superset.models.helpers.pd.to_datetime", fail_to_parse)
+
+ with raises(
+ QueryObjectValidationError,
+ match="contains values that cannot be parsed as datetimes",
+ ):
+ query_context_processor.join_offset_dfs(
+ df,
+ {"1 year ago": offset_df},
+ time_grain=None,
+ join_keys=["ds"],
+ x_axis_label="ds",
+ x_axis_is_temporal=True,
+ )
+
+
def test_join_offset_dfs_no_time_grain_unmatched_timestamps_yield_nulls() -> None:
"""
Without a time grain, offset timestamps that have no exact shifted
@@ -521,7 +741,7 @@ def test_join_offset_dfs_no_time_grain_uninterpretable_offset_subsecond() -> Non
)
-@mark.parametrize("offset", ["yesterday", "last month"])
+@mark.parametrize("offset", ["yesterday", "last month", "friday", "june"])
def test_join_offset_dfs_no_time_grain_anchor_offset(offset: str) -> None:
"""
Phrases that parsedatetime resolves to a fixed point rather than a shift
@@ -579,12 +799,7 @@ def test_join_offset_dfs_no_time_grain_dst_nonexistent_hour() -> None:
def test_join_offset_dfs_no_time_grain_dst_ambiguous_hour() -> None:
- """
- A shift landing on a local hour that DST repeats aligns on the wall clock
- the offset query returned. 01:30 occurs twice on 2021-11-07 in US/Eastern;
- shifting the tz-aware timestamp directly raised AmbiguousTimeError out of
- pandas rather than picking either reading.
- """
+ """One reading of a repeated local hour still aligns by wall clock."""
df = DataFrame({"ds": [Timestamp("2021-12-07 01:30", tz="US/Eastern")], "D": [1]})
offset_df = DataFrame(
{
@@ -602,6 +817,127 @@ def test_join_offset_dfs_no_time_grain_dst_ambiguous_hour() -> None:
assert result["B"].tolist() == [5]
+def test_join_offset_dfs_no_time_grain_rejects_both_dst_fold_readings() -> None:
+ """
+ When the offset query returns both readings of a repeated local hour,
+ dropping their UTC offsets would give both rows the same merge key and
+ expand the main series. Reject the ambiguous alignment instead.
+ """
+ df = DataFrame({"ds": [Timestamp("2021-12-07 01:30", tz="US/Eastern")], "D": [1]})
+ offset_df = DataFrame(
+ {
+ "ds": [
+ Timestamp("2021-11-07 01:30").tz_localize("US/Eastern", ambiguous=True),
+ Timestamp("2021-11-07 01:30").tz_localize(
+ "US/Eastern", ambiguous=False
+ ),
+ ],
+ "B": [5, 6],
+ }
+ )
+
+ with raises(
+ QueryObjectValidationError,
+ match="ambiguous daylight-saving fold",
+ ):
+ query_context_processor.join_offset_dfs(
+ df, {"1 month ago": offset_df}, time_grain=None, join_keys=["ds"]
+ )
+
+
+def test_join_offset_dfs_no_time_grain_rejects_naive_normalization_collision() -> None:
+ """Distinct naive values that normalize alike cannot expand the result."""
+ df = DataFrame({"ds": ["2021-02-01"], "D": [1]})
+ offset_df = DataFrame(
+ {
+ "ds": ["2021-01-01", "2021-01-01 00:00:00"],
+ "B": [5, 6],
+ }
+ )
+
+ with raises(
+ QueryObjectValidationError,
+ match="normalize to the same instant",
+ ):
+ query_context_processor.join_offset_dfs(
+ df,
+ {"1 month ago": offset_df},
+ time_grain=None,
+ join_keys=["ds"],
+ x_axis_label="ds",
+ x_axis_is_temporal=True,
+ )
+
+
+def test_join_offset_dfs_no_time_grain_rejects_dst_fold_with_raw_duplicate() -> None:
+ """A raw duplicate does not mask a DST fold in the same normalized group."""
+ df = DataFrame({"ds": [Timestamp("2021-12-07 01:30", tz="US/Eastern")], "D": [1]})
+ first_fold = Timestamp("2021-11-07 01:30").tz_localize("US/Eastern", ambiguous=True)
+ second_fold = Timestamp("2021-11-07 01:30").tz_localize(
+ "US/Eastern", ambiguous=False
+ )
+ offset_df = DataFrame(
+ {
+ "ds": [first_fold, first_fold, second_fold],
+ "B": [5, 6, 7],
+ }
+ )
+
+ with raises(
+ QueryObjectValidationError,
+ match="ambiguous daylight-saving fold",
+ ):
+ query_context_processor.join_offset_dfs(
+ df, {"1 month ago": offset_df}, time_grain=None, join_keys=["ds"]
+ )
+
+
+def test_join_offset_dfs_no_time_grain_preserves_raw_duplicate_offsets() -> None:
+ """Pre-existing naive duplicate keys are not diagnosed as a DST fold."""
+ df = DataFrame({"ds": [Timestamp("2021-02-01")], "D": [1]})
+ offset_df = DataFrame(
+ {
+ "ds": [Timestamp("2021-01-01"), Timestamp("2021-01-01")],
+ "B": [5, 6],
+ }
+ )
+
+ result = query_context_processor.join_offset_dfs(
+ df, {"1 month ago": offset_df}, time_grain=None, join_keys=["ds"]
+ )
+
+ assert result["B"].tolist() == [5, 6]
+
+
+def test_join_offset_dfs_no_time_grain_all_null_anchor_still_raises() -> None:
+ """An all-null temporal axis does not bypass anchor validation."""
+ df = DataFrame({"ds": Series([None], dtype="datetime64[ns]"), "D": [1]})
+ offset_df = DataFrame({"ds": [float("nan")], "B": [float("nan")]})
+
+ with raises(QueryObjectValidationError, match="Time Grain must be"):
+ query_context_processor.join_offset_dfs(
+ df, {"friday": offset_df}, time_grain=None, join_keys=["ds"]
+ )
+
+
+def test_join_offset_dfs_no_time_grain_allows_month_end_clamp_on_left() -> None:
+ """Multiple main dates may intentionally shift to one month-end key."""
+ df = DataFrame(
+ {
+ "ds": [Timestamp("2021-03-30"), Timestamp("2021-03-31")],
+ "D": [1, 2],
+ }
+ )
+ offset_df = DataFrame({"ds": [Timestamp("2021-02-28")], "B": [5]})
+
+ result = query_context_processor.join_offset_dfs(
+ df, {"1 month ago": offset_df}, time_grain=None, join_keys=["ds"]
+ )
+
+ assert len(result) == len(df)
+ assert result["B"].tolist() == [5, 5]
+
+
@mark.parametrize(
"offset",
[
diff --git a/tests/unit_tests/utils/date_parser_tests.py b/tests/unit_tests/utils/date_parser_tests.py
index a4a5a0b6c6c..c5522b496ee 100644
--- a/tests/unit_tests/utils/date_parser_tests.py
+++ b/tests/unit_tests/utils/date_parser_tests.py
@@ -613,6 +613,9 @@ def test_is_parseable_human_timedelta() -> None:
def test_is_constant_human_timedelta() -> None:
# phrases that shift every source time by the same amount
assert is_constant_human_timedelta("1 week ago")
+ assert is_constant_human_timedelta("1 month ago")
+ assert is_constant_human_timedelta("52 weeks ago")
+ assert is_constant_human_timedelta("1 year ago")
assert is_constant_human_timedelta("one year ago")
assert is_constant_human_timedelta("1 quarter ago")
assert is_constant_human_timedelta("2 days later")
@@ -623,6 +626,8 @@ def test_is_constant_human_timedelta() -> None:
assert not is_constant_human_timedelta("yesterday")
assert not is_constant_human_timedelta("last month")
assert not is_constant_human_timedelta("noon")
+ assert not is_constant_human_timedelta("friday")
+ assert not is_constant_human_timedelta("june")
# a phrase nothing can parse is not a delta either
assert not is_constant_human_timedelta("not a real offset")
assert not is_constant_human_timedelta("")