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("")