You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@superset.apache.org by ru...@apache.org on 2026/07/26 06:31:12 UTC
(superset) 01/01: fix(db-engine-specs): handle bare-year python_date_format columns in time-grain expressions
This is an automated email from the ASF dual-hosted git repository.
rusackas pushed a commit to branch fix-year-pdf-time-grain
in repository https://gitbox.apache.org/repos/asf/superset.git
commit 73ca8fb72787372bd6e83f1ddc4729d456e381dd
Author: Claude Code <no...@anthropic.com>
AuthorDate: Sat Jul 25 23:30:38 2026 -0700
fix(db-engine-specs): handle bare-year python_date_format columns in time-grain expressions
get_timestamp_expr() only knew how to convert epoch_s/epoch_ms raw values
into a proper timestamp before applying a time-grain truncation function;
any other python_date_format (e.g. "%Y") fell through and passed the raw
value straight into the grain function. On SQLite that means
DATETIME(year, 'start of year') interprets a bare integer year as a Julian
day number rather than a calendar year, silently returning NULL for every
row. This exact pattern ships in Superset's own video_game_sales example
dataset (year: BIGINT, is_dttm=true, python_date_format: '%Y'), so any
time-grain-truncated chart against it (Nightingale Rose included) returns
no data.
Add a year_to_dttm() hook (mirroring epoch_to_dttm/epoch_ms_to_dttm) that
converts a bare year into a proper date before truncation, implemented for
SQLite via printf()-based date construction with an explicit NULL guard
(printf() otherwise treats a NULL argument as 0).
Co-Authored-By: Claude Sonnet 5 <no...@anthropic.com>
---
superset/db_engine_specs/base.py | 19 +++++++++++++++
superset/db_engine_specs/sqlite.py | 12 ++++++++++
tests/unit_tests/db_engine_specs/test_sqlite.py | 31 +++++++++++++++++++++++++
3 files changed, 62 insertions(+)
diff --git a/superset/db_engine_specs/base.py b/superset/db_engine_specs/base.py
index eee5635160a..1d1c89d00b6 100644
--- a/superset/db_engine_specs/base.py
+++ b/superset/db_engine_specs/base.py
@@ -1110,6 +1110,14 @@ class BaseEngineSpec: # pylint: disable=too-many-public-methods
time_expr = time_expr.replace("{col}", cls.epoch_to_dttm())
elif pdf == "epoch_ms":
time_expr = time_expr.replace("{col}", cls.epoch_ms_to_dttm())
+ elif pdf == "%Y":
+ # a bare four-digit year (e.g. the `year` column on the `video_game_sales`
+ # example dataset) has no native date type to lean on; without this the
+ # column value is passed straight into the grain function below, which
+ # every engine interprets as something other than a calendar year (SQLite
+ # reads a bare integer as a Julian day number, for instance), silently
+ # producing NULL for every row.
+ time_expr = time_expr.replace("{col}", cls.year_to_dttm())
return TimestampExpression(time_expr, col, type_=col.type)
@@ -1326,6 +1334,17 @@ class BaseEngineSpec: # pylint: disable=too-many-public-methods
"""
return cls.epoch_to_dttm().replace("{col}", "({col}/1000)")
+ @classmethod
+ def year_to_dttm(cls) -> str:
+ """
+ SQL expression that converts a bare four-digit year value to the January 1st
+ datetime of that year, for use in a query. The reference column should be
+ denoted as `{col}` in the return expression, e.g. "MAKE_DATE({col}, 1, 1)"
+
+ :return: SQL Expression
+ """
+ raise NotImplementedError()
+
@classmethod
def get_datatype(cls, type_code: Any) -> str | None:
"""
diff --git a/superset/db_engine_specs/sqlite.py b/superset/db_engine_specs/sqlite.py
index c704a120fee..0d196fc6bb4 100644
--- a/superset/db_engine_specs/sqlite.py
+++ b/superset/db_engine_specs/sqlite.py
@@ -126,6 +126,18 @@ class SqliteEngineSpec(BaseEngineSpec):
def epoch_to_dttm(cls) -> str:
return "datetime({col}, 'unixepoch')"
+ @classmethod
+ def year_to_dttm(cls) -> str:
+ # SQLite's date functions parse a 'YYYY-01-01' string just fine, but won't
+ # accept a bare integer/real year (it's read as a Julian day number instead).
+ # The CASE guard is needed because printf() treats a NULL argument as 0,
+ # which would otherwise turn a missing year into '0000-01-01' rather than
+ # propagating the NULL.
+ return (
+ "CASE WHEN {col} IS NULL THEN NULL "
+ "ELSE printf('%04d-01-01', CAST({col} AS INTEGER)) END"
+ )
+
@classmethod
def convert_dttm(
cls, target_type: str, dttm: datetime, db_extra: dict[str, Any] | None = None
diff --git a/tests/unit_tests/db_engine_specs/test_sqlite.py b/tests/unit_tests/db_engine_specs/test_sqlite.py
index 79c4f8fc5ca..6e47087befa 100644
--- a/tests/unit_tests/db_engine_specs/test_sqlite.py
+++ b/tests/unit_tests/db_engine_specs/test_sqlite.py
@@ -131,3 +131,34 @@ def test_time_grain_expressions(dttm: str, grain: str, expected: str) -> None:
with engine.connect() as connection:
result = connection.execute(text(sql)).scalar()
assert result == expected
+
+
+@pytest.mark.parametrize(
+ "year,expected",
+ [
+ (2013, "2013-01-01 00:00:00"),
+ (2013.0, "2013-01-01 00:00:00"),
+ (None, None),
+ ],
+)
+def test_year_pdf_time_grain(year: Optional[float], expected: Optional[str]) -> None:
+ """A bare four-digit year (e.g. the `year` column on the `video_game_sales`
+ example dataset) has no native date type; without `year_to_dttm` the raw
+ value is passed straight into the grain function, which SQLite reads as a
+ Julian day number rather than a calendar year, silently producing NULL."""
+ from sqlalchemy import column
+
+ from superset.db_engine_specs.sqlite import SqliteEngineSpec
+
+ engine = create_engine("sqlite://", future=True)
+ with engine.begin() as connection:
+ connection.execute(text("CREATE TABLE t (year REAL)"))
+ connection.execute(text("INSERT INTO t VALUES (:year)"), {"year": year})
+
+ expression = SqliteEngineSpec.get_timestamp_expr(
+ col=column("year"), pdf="%Y", time_grain=TimeGrain.YEAR
+ )
+ sql = f"SELECT {expression} FROM t" # noqa: S608
+ with engine.connect() as connection:
+ result = connection.execute(text(sql)).scalar()
+ assert result == expected