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 <[email protected]> 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 <[email protected]> --- 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 + + [email protected]( + "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
