This is an automated email from the ASF dual-hosted git repository.
aminghadersohi 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 7a2913e2dbe fix(sqlite): write midnight as a bare date for DATE
columns in time filters (#44805)
7a2913e2dbe is described below
commit 7a2913e2dbe892805f67138696ee5561d67ab7c5
Author: Daniel Alyoshin <[email protected]>
AuthorDate: Sun Oct 4 19:51:11 2026 -0400
fix(sqlite): write midnight as a bare date for DATE columns in time filters
(#44805)
Co-authored-by: Amin Ghadersohi
<[email protected]>
---
UPDATING.md | 12 +++
superset/db_engine_specs/d1.py | 20 -----
superset/db_engine_specs/gsheets.py | 23 +-----
superset/db_engine_specs/shillelagh.py | 20 ++++-
superset/db_engine_specs/sqlite.py | 16 +++-
tests/unit_tests/db_engine_specs/test_d1.py | 48 +-----------
.../unit_tests/db_engine_specs/test_shillelagh.py | 13 ++++
tests/unit_tests/db_engine_specs/test_sqlite.py | 83 ++++++++++++++++++++
tests/unit_tests/extensions/test_sqlalchemy.py | 89 ++++++++++++++++++++++
9 files changed, 231 insertions(+), 93 deletions(-)
diff --git a/UPDATING.md b/UPDATING.md
index 9f2e5bd0cd7..50ed73e9f8c 100644
--- a/UPDATING.md
+++ b/UPDATING.md
@@ -44,6 +44,18 @@ assists people when migrating to a new version.
loader rejects semantic bundles;
use the chart, dashboard or assets importer instead.
+### SQLite time filters on `DATE` columns
+
+On SQLite, Shillelagh and the Superset meta database, a time filter on a `DATE`
+column writes its bounds as a date, such as `'2026-09-20'`, and no longer uses
+the column's **Datetime format** (`python_date_format`) or the database's
+`python_date_format_by_column_name`. This fixes ranges that started and ended
+one day late on `DATE` columns holding `YYYY-MM-DD` text. A `DATE` column that
+holds values in another format, such as `20260920`, `09/20/2026` or epoch
+seconds, now matches no rows, even with a Datetime format set. Columns declared
+as `INTEGER` are not affected. On Shillelagh and the meta database, a bound
+with a time of day is cut to its date.
+
### Apache Doris connection form and `DBS_AVAILABLE_DENYLIST`
`DBS_AVAILABLE_DENYLIST` is matched against an engine spec's `default_driver`.
diff --git a/superset/db_engine_specs/d1.py b/superset/db_engine_specs/d1.py
index 056d7942d7b..45bb47f7fd4 100644
--- a/superset/db_engine_specs/d1.py
+++ b/superset/db_engine_specs/d1.py
@@ -18,12 +18,10 @@
from __future__ import annotations
import re
-from datetime import datetime, time
from re import Pattern
from typing import Any, TYPE_CHECKING
from flask_babel import lazy_gettext as _
-from sqlalchemy import types
from sqlalchemy.engine.url import URL
from superset.db_engine_specs.base import DatabaseCategory
@@ -131,24 +129,6 @@ class CloudflareD1EngineSpec(SqliteEngineSpec):
raise ValueError("Forbidden query parameter(s): {'base_url'}")
super().validate_database_uri(sqlalchemy_uri)
- @classmethod
- def convert_dttm(
- cls, target_type: str, dttm: datetime, db_extra: dict[str, Any] | None
= None
- ) -> str | None:
- """
- Write midnight as a bare date for DATE columns.
-
- D1 keeps a DATE as text such as ``2026-09-20`` and compares it as
text, so
- ``'2026-09-20 00:00:00'`` sorts after the day it starts and a time
filter
- on a DATE column would be one day off. Any other time keeps its time
part,
- which sorts between two days, as a comparison of dates should.
- """
- if isinstance(cls.get_sqla_column_type(target_type), types.Date):
- if dttm.time() == time.min:
- return f"'{dttm.date().isoformat()}'"
- return f"""'{dttm.isoformat(sep=" ", timespec="seconds")}'"""
- return super().convert_dttm(target_type, dttm, db_extra=db_extra)
-
@classmethod
def _extract_error_message(cls, ex: Exception) -> str:
"""
diff --git a/superset/db_engine_specs/gsheets.py
b/superset/db_engine_specs/gsheets.py
index 2eb210b46ce..ec335b74afe 100644
--- a/superset/db_engine_specs/gsheets.py
+++ b/superset/db_engine_specs/gsheets.py
@@ -35,7 +35,7 @@ from marshmallow.exceptions import ValidationError
from requests import Session
from shillelagh.adapters.api.gsheets.lib import SCOPES
from shillelagh.exceptions import UnauthenticatedError
-from sqlalchemy import text, types
+from sqlalchemy import text
from sqlalchemy.engine import create_engine
from sqlalchemy.engine.reflection import Inspector
from sqlalchemy.engine.url import URL
@@ -214,27 +214,6 @@ class GSheetsEngineSpec(ShillelaghEngineSpec):
oauth2_token_request_uri = "https://oauth2.googleapis.com/token"; # noqa:
S105
oauth2_exception = (UnauthenticatedError, OAuth2TokenRefreshError)
- @classmethod
- def convert_dttm(
- cls, target_type: str, dttm: datetime, db_extra: dict[str, Any] | None
= None
- ) -> str | None:
- """
- Convert a datetime to a SQL literal understood by shillelagh's GSheets
- adapter.
-
- ``SqliteEngineSpec.convert_dttm`` (inherited via
``ShillelaghEngineSpec``)
- has no case for ``types.Date`` and returns ``None``, which makes
Superset
- fall back to a literal that still carries a time-of-day component. The
- GSheets adapter's virtual table layer parses that literal with
- ``datetime.date.fromisoformat``, which rejects the trailing time and
- silently drops the filter value, producing an invalid query against the
- Google Sheets API. A bare ``YYYY-MM-DD`` literal is required instead.
- """
- sqla_type = cls.get_sqla_column_type(target_type)
- if isinstance(sqla_type, types.Date):
- return f"'{dttm.date().isoformat()}'"
- return super().convert_dttm(target_type, dttm, db_extra=db_extra)
-
@classmethod
def get_oauth2_authorization_uri(
cls,
diff --git a/superset/db_engine_specs/shillelagh.py
b/superset/db_engine_specs/shillelagh.py
index d0fbc79cec0..6eacae77b38 100644
--- a/superset/db_engine_specs/shillelagh.py
+++ b/superset/db_engine_specs/shillelagh.py
@@ -16,9 +16,10 @@
# under the License.
from __future__ import annotations
-from typing import TYPE_CHECKING
+from datetime import datetime
+from typing import Any, TYPE_CHECKING
-from sqlalchemy import event
+from sqlalchemy import event, types
from superset.db_engine_specs.base import DatabaseCategory
from superset.db_engine_specs.sqlite import SqliteEngineSpec
@@ -59,6 +60,21 @@ class ShillelaghEngineSpec(SqliteEngineSpec):
),
}
+ @classmethod
+ def convert_dttm(
+ cls, target_type: str, dttm: datetime, db_extra: dict[str, Any] | None
= None
+ ) -> str | None:
+ """
+ Write a bare date for DATE columns.
+
+ Shillelagh reads a date filter with ``date.fromisoformat``, which
rejects a
+ time part and drops the filter without an error, so every row would
come
+ back. A time other than midnight is cut to its date.
+ """
+ if isinstance(cls.get_sqla_column_type(target_type), types.Date):
+ return f"'{dttm.date().isoformat()}'"
+ return super().convert_dttm(target_type, dttm, db_extra=db_extra)
+
@classmethod
def get_function_names(
cls,
diff --git a/superset/db_engine_specs/sqlite.py
b/superset/db_engine_specs/sqlite.py
index 3955c079556..18a3a01390f 100644
--- a/superset/db_engine_specs/sqlite.py
+++ b/superset/db_engine_specs/sqlite.py
@@ -18,7 +18,7 @@
from __future__ import annotations
import re
-from datetime import datetime
+from datetime import datetime, time
from re import Pattern
from typing import Any, TYPE_CHECKING
@@ -164,8 +164,20 @@ class SqliteEngineSpec(BaseEngineSpec):
def convert_dttm(
cls, target_type: str, dttm: datetime, db_extra: dict[str, Any] | None
= None
) -> str | None:
+ """
+ Write midnight as a bare date for DATE columns.
+
+ SQLite has no date type, so a DATE column usually holds text such as
+ ``2026-09-20``. ``'2026-09-20 00:00:00'`` sorts after that text, so a
time
+ filter on a DATE column would be one day off. Any other time keeps its
time
+ part, which sorts between two days, as a comparison of dates should.
Values
+ that look like numbers, such as ``20260920`` or epoch seconds, are
stored as
+ numbers, and SQLite sorts every number before any text.
+ """
sqla_type = cls.get_sqla_column_type(target_type)
- if isinstance(sqla_type, (types.String, types.DateTime)):
+ if isinstance(sqla_type, types.Date) and dttm.time() == time.min:
+ return f"'{dttm.date().isoformat()}'"
+ if isinstance(sqla_type, (types.String, types.Date, types.DateTime)):
return f"""'{dttm.isoformat(sep=" ", timespec="seconds")}'"""
return None
diff --git a/tests/unit_tests/db_engine_specs/test_d1.py
b/tests/unit_tests/db_engine_specs/test_d1.py
index 220fb84e498..4c54e900366 100644
--- a/tests/unit_tests/db_engine_specs/test_d1.py
+++ b/tests/unit_tests/db_engine_specs/test_d1.py
@@ -21,7 +21,7 @@ from datetime import datetime
import pytest
from pytest_mock import MockerFixture
-from sqlalchemy import create_engine, text, types
+from sqlalchemy import types
from sqlalchemy.engine.url import make_url
from superset.errors import SupersetErrorType
@@ -289,49 +289,3 @@ def test_convert_dttm(
from superset.db_engine_specs.d1 import CloudflareD1EngineSpec as spec #
noqa: N813
assert_convert_dttm(spec, target_type, expected_result, dttm)
-
-
[email protected](
- "start,end,expected",
- [
- (
- datetime(2026, 9, 20),
- datetime(2026, 9, 22),
- ["2026-09-20", "2026-09-21"],
- ),
- (
- datetime(2026, 9, 20, 12),
- datetime(2026, 9, 22, 12),
- ["2026-09-21", "2026-09-22"],
- ),
- ],
-)
-def test_time_filter_on_date_column(
- start: datetime,
- end: datetime,
- expected: list[str],
-) -> None:
- """
- Test that a time filter on a DATE column stored as text returns the right
days.
-
- D1 is SQLite, so the comparison runs on an in-memory SQLite database. Each
day
- counts as its midnight, as it would in a comparison of dates.
- """
- from superset.db_engine_specs.d1 import CloudflareD1EngineSpec as spec #
noqa: N813
-
- since = spec.convert_dttm("DATE", start)
- until = spec.convert_dttm("DATE", end)
- sql = f"SELECT day FROM t WHERE day >= {since} AND day < {until} ORDER BY
day" # noqa: S608
-
- engine = create_engine("sqlite://")
- with engine.connect() as connection:
- connection.execute(text("CREATE TABLE t (day DATE)"))
- connection.execute(
- text(
- "INSERT INTO t VALUES "
- "('2026-09-19'), ('2026-09-20'), ('2026-09-21'),
('2026-09-22')"
- )
- )
- rows = connection.execute(text(sql)).fetchall()
-
- assert [row[0] for row in rows] == expected
diff --git a/tests/unit_tests/db_engine_specs/test_shillelagh.py
b/tests/unit_tests/db_engine_specs/test_shillelagh.py
index e66014f5c24..8a7def2ed06 100644
--- a/tests/unit_tests/db_engine_specs/test_shillelagh.py
+++ b/tests/unit_tests/db_engine_specs/test_shillelagh.py
@@ -17,6 +17,7 @@
import sqlite3
from contextlib import closing
+from datetime import datetime
import pytest
from sqlalchemy import create_engine, text
@@ -98,3 +99,15 @@ def test_non_apsw_backend_is_left_alone() -> None:
with engine.connect() as connection:
assert connection.execute(text("SELECT 1")).scalar() == 1
+
+
[email protected]("spec", [ShillelaghEngineSpec, SupersetEngineSpec])
+def test_convert_dttm_date(spec: type[ShillelaghEngineSpec]) -> None:
+ """
+ A DATE column gets a bare date, which shillelagh can read as a date.
+ """
+ assert spec.convert_dttm("DATE", datetime(2026, 9, 20)) == "'2026-09-20'"
+ assert spec.convert_dttm("DATE", datetime(2026, 9, 20, 12)) ==
"'2026-09-20'"
+ assert (
+ spec.convert_dttm("DATETIME", datetime(2026, 9, 20)) == "'2026-09-20
00:00:00'"
+ )
diff --git a/tests/unit_tests/db_engine_specs/test_sqlite.py
b/tests/unit_tests/db_engine_specs/test_sqlite.py
index 0efb6fdcbc8..9c92f9aa672 100644
--- a/tests/unit_tests/db_engine_specs/test_sqlite.py
+++ b/tests/unit_tests/db_engine_specs/test_sqlite.py
@@ -33,6 +33,7 @@ from tests.unit_tests.fixtures.common import dttm # noqa:
F401
("Text", "'2019-01-02 03:04:05'"),
("DateTime", "'2019-01-02 03:04:05'"),
("TimeStamp", "'2019-01-02 03:04:05'"),
+ ("Date", "'2019-01-02 03:04:05'"),
("Other", None),
],
)
@@ -46,6 +47,88 @@ def test_convert_dttm(
assert_convert_dttm(spec, target_type, expected_result, dttm)
[email protected](
+ "target_type,expected_result",
+ [
+ ("Date", "'2019-01-02'"),
+ ("Text", "'2019-01-02 00:00:00'"),
+ ("DateTime", "'2019-01-02 00:00:00'"),
+ ("TimeStamp", "'2019-01-02 00:00:00'"),
+ ("Other", None),
+ ],
+)
+def test_convert_dttm_midnight(
+ target_type: str,
+ expected_result: Optional[str],
+) -> None:
+ """
+ Test that midnight is written as a bare date for DATE columns only.
+ """
+ from superset.db_engine_specs.sqlite import SqliteEngineSpec as spec #
noqa: N813
+
+ assert_convert_dttm(spec, target_type, expected_result, datetime(2019, 1,
2))
+
+
[email protected]("sqlalchemy_uri", ["sqlite://",
"d1://account:token@db"])
[email protected]("time_grain", [None, TimeGrain.DAY])
[email protected](
+ "start,end,expected",
+ [
+ (
+ datetime(2026, 9, 20),
+ datetime(2026, 9, 22),
+ ["2026-09-20", "2026-09-21"],
+ ),
+ (
+ datetime(2026, 9, 20, 12),
+ datetime(2026, 9, 22, 12),
+ ["2026-09-21", "2026-09-22"],
+ ),
+ ],
+)
+def test_time_filter_on_date_column(
+ app_context: None,
+ sqlalchemy_uri: str,
+ time_grain: Optional[str],
+ start: datetime,
+ end: datetime,
+ expected: list[str],
+) -> None:
+ """
+ Test that a time filter on a DATE column stored as text returns the right
days.
+
+ The filter comes from ``get_time_filter``, as in a chart query. D1 is
SQLite,
+ so both run on an in-memory SQLite database. Each day counts as its
midnight,
+ as it would in a comparison of dates.
+ """
+ from superset.connectors.sqla.models import SqlaTable, TableColumn
+ from superset.models.core import Database
+
+ column = TableColumn(column_name="day", type="DATE", is_dttm=True)
+ table = SqlaTable(
+ table_name="t",
+ columns=[column],
+ database=Database(database_name="db", sqlalchemy_uri=sqlalchemy_uri),
+ )
+ time_filter = table.get_time_filter(column, start, end,
time_grain=time_grain)
+ assert time_filter is not None
+
+ engine = create_engine("sqlite://")
+ where = time_filter.compile(engine, compile_kwargs={"literal_binds": True})
+ sql = f"SELECT day FROM t WHERE {where} ORDER BY day" # noqa: S608
+ with engine.connect() as connection:
+ connection.execute(text("CREATE TABLE t (day DATE)"))
+ connection.execute(
+ text(
+ "INSERT INTO t VALUES "
+ "('2026-09-19'), ('2026-09-20'), ('2026-09-21'),
('2026-09-22')"
+ )
+ )
+ rows = connection.execute(text(sql)).fetchall()
+
+ assert [row[0] for row in rows] == expected
+
+
@pytest.mark.parametrize(
"dttm,grain,expected",
[
diff --git a/tests/unit_tests/extensions/test_sqlalchemy.py
b/tests/unit_tests/extensions/test_sqlalchemy.py
index 998534a015b..fdb41775c06 100644
--- a/tests/unit_tests/extensions/test_sqlalchemy.py
+++ b/tests/unit_tests/extensions/test_sqlalchemy.py
@@ -19,6 +19,7 @@
import os
import re
from collections.abc import Iterator
+from datetime import date, datetime
from typing import TYPE_CHECKING
import pytest
@@ -796,3 +797,91 @@ def test_allowed_dbs(mocker: MockerFixture, app_context:
None, table1: None) ->
(Background on this error at: https://sqlalche.me/e/XX/f405)
""".strip()
)
+
+
[email protected]
+def table_dates(session: Session, database1: "Database") -> Iterator[None]:
+ """
+ A table with a DATE column holding 2026-09-19 to 2026-09-22.
+ """
+ with database1.get_sqla_engine() as engine:
+ with engine.begin() as conn:
+ conn.execute(text("CREATE TABLE table_dates (day DATE)"))
+ conn.execute(
+ text(
+ "INSERT INTO table_dates (day) VALUES "
+ "('2026-09-19'), ('2026-09-20'), ('2026-09-21'),
('2026-09-22')"
+ )
+ )
+ db.session.commit()
+
+ yield
+
+ with engine.begin() as conn:
+ conn.execute(text("DROP TABLE table_dates"))
+ db.session.commit()
+
+
+@with_feature_flags(ENABLE_SUPERSET_META_DB=True)
[email protected](
+ "start,end",
+ [
+ (datetime(2026, 9, 20), datetime(2026, 9, 22)),
+ (datetime(2026, 9, 20, 12), datetime(2026, 9, 22, 12)),
+ ],
+)
+def test_time_filter_on_date_column(
+ mocker: MockerFixture,
+ app_context: None,
+ table_dates: None,
+ start: datetime,
+ end: datetime,
+) -> None:
+ """
+ Test that a time filter on a DATE column is applied.
+
+ Shillelagh reads a date filter with ``date.fromisoformat``, so a bound
with a
+ time part would be dropped and every row would come back. The filter comes
+ from ``get_time_filter``, as in a chart query.
+ """
+ from superset.connectors.sqla.models import SqlaTable, TableColumn
+ from superset.models.core import Database
+
+ # Mock the security_manager.raise_for_access to allow access
+ mocker.patch(
+ "superset.extensions.metadb.security_manager.raise_for_access",
+ return_value=None,
+ )
+
+ # Mock Flask g.user for security checks
+ # In Python 3.8+, we can't directly patch flask.g
+ # Instead, we need to ensure g.user exists in the context
+ from flask import g
+
+ g.user = mocker.MagicMock()
+ g.user.is_anonymous = False
+
+ try:
+ engine = create_engine("superset://")
+ except Exception as e:
+ # Skip test if superset:// dialect can't be loaded (common in Docker)
+ pytest.skip(f"Superset dialect not available: {e}")
+
+ column = TableColumn(column_name="day", type="DATE", is_dttm=True)
+ table = SqlaTable(
+ table_name="database1.table_dates",
+ columns=[column],
+ database=Database(database_name="meta", sqlalchemy_uri="superset://"),
+ )
+ time_filter = table.get_time_filter(column, start, end)
+ assert time_filter is not None
+ where = time_filter.compile(engine, compile_kwargs={"literal_binds": True})
+
+ with engine.connect() as conn:
+ results = conn.execute(
+ text(
+ 'SELECT day FROM "database1.table_dates" ' # noqa: S608
+ f"WHERE {where} ORDER BY day"
+ )
+ )
+ assert list(results) == [(date(2026, 9, 20),), (date(2026, 9, 21),)]
