This is an automated email from the ASF dual-hosted git repository.
rusackas 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 74924ae73a4 fix(db_engine_specs): use CAST(DATE({col}) AS DATETIME) in
MySQL HOUR time grain (#38617)
74924ae73a4 is described below
commit 74924ae73a41a8beb3b0f161bb8bd8ae136f6882
Author: Nitish Agarwal <[email protected]>
AuthorDate: Tue Jul 28 01:22:25 2026 +0530
fix(db_engine_specs): use CAST(DATE({col}) AS DATETIME) in MySQL HOUR time
grain (#38617)
---
superset/db_engine_specs/mysql.py | 9 +--
tests/unit_tests/db_engine_specs/test_mysql.py | 88 +++++++++++++++++++++-
tests/unit_tests/db_engine_specs/test_starrocks.py | 25 +++++-
3 files changed, 113 insertions(+), 9 deletions(-)
diff --git a/superset/db_engine_specs/mysql.py
b/superset/db_engine_specs/mysql.py
index 91f13bd55b7..39d1b550cce 100644
--- a/superset/db_engine_specs/mysql.py
+++ b/superset/db_engine_specs/mysql.py
@@ -250,12 +250,9 @@ class MySQLEngineSpec(BasicParametersMixin,
BaseEngineSpec):
_time_grain_expressions = {
None: "{col}",
- TimeGrain.SECOND: "DATE_ADD(DATE({col}), "
- "INTERVAL (HOUR({col})*60*60 + MINUTE({col})*60"
- " + SECOND({col})) SECOND)",
- TimeGrain.MINUTE: "DATE_ADD(DATE({col}), "
- "INTERVAL (HOUR({col})*60 + MINUTE({col})) MINUTE)",
- TimeGrain.HOUR: "DATE_ADD(DATE({col}), INTERVAL HOUR({col}) HOUR)",
+ TimeGrain.SECOND: "DATE_FORMAT({col}, '%Y-%m-%d %H:%i:%s')",
+ TimeGrain.MINUTE: "DATE_FORMAT({col}, '%Y-%m-%d %H:%i:00')",
+ TimeGrain.HOUR: "DATE_FORMAT({col}, '%Y-%m-%d %H:00:00')",
TimeGrain.DAY: "DATE({col})",
TimeGrain.WEEK: "DATE(DATE_SUB({col}, INTERVAL DAYOFWEEK({col}) - 1
DAY))",
TimeGrain.MONTH: "DATE(DATE_SUB({col}, INTERVAL DAYOFMONTH({col}) - 1
DAY))",
diff --git a/tests/unit_tests/db_engine_specs/test_mysql.py
b/tests/unit_tests/db_engine_specs/test_mysql.py
index 64de9d479af..2ea2137dfce 100644
--- a/tests/unit_tests/db_engine_specs/test_mysql.py
+++ b/tests/unit_tests/db_engine_specs/test_mysql.py
@@ -23,7 +23,7 @@ from typing import Any, Optional
from unittest.mock import Mock, patch
import pytest
-from sqlalchemy import types
+from sqlalchemy import column, types
from sqlalchemy.dialects.mysql import (
BIT,
DECIMAL,
@@ -38,6 +38,8 @@ from sqlalchemy.dialects.mysql import (
)
from sqlalchemy.engine.url import make_url, URL # noqa: F401
+from superset.constants import TimeGrain
+from superset.db_engine_specs.base import TimestampExpression
from superset.utils.core import GenericDataType
from tests.unit_tests.db_engine_specs.utils import (
assert_column_spec,
@@ -304,3 +306,87 @@ def test_get_datatype_pymysql_fallback():
finally:
# Restore original state
MySQLEngineSpec.type_code_map = original_type_code_map
+
+
[email protected](
+ ("grain", "expected_expression"),
+ [
+ (None, "my_col"),
+ (
+ TimeGrain.SECOND,
+ "DATE_FORMAT(my_col, '%Y-%m-%d %H:%i:%s')",
+ ),
+ (
+ TimeGrain.MINUTE,
+ "DATE_FORMAT(my_col, '%Y-%m-%d %H:%i:00')",
+ ),
+ (
+ TimeGrain.HOUR,
+ "DATE_FORMAT(my_col, '%Y-%m-%d %H:00:00')",
+ ),
+ (TimeGrain.DAY, "DATE(my_col)"),
+ (
+ TimeGrain.WEEK,
+ "DATE(DATE_SUB(my_col, INTERVAL DAYOFWEEK(my_col) - 1 DAY))",
+ ),
+ (
+ TimeGrain.MONTH,
+ "DATE(DATE_SUB(my_col, INTERVAL DAYOFMONTH(my_col) - 1 DAY))",
+ ),
+ (
+ TimeGrain.QUARTER,
+ "MAKEDATE(YEAR(my_col), 1) "
+ "+ INTERVAL QUARTER(my_col) QUARTER - INTERVAL 1 QUARTER",
+ ),
+ (
+ TimeGrain.YEAR,
+ "DATE(DATE_SUB(my_col, INTERVAL DAYOFYEAR(my_col) - 1 DAY))",
+ ),
+ (
+ TimeGrain.WEEK_STARTING_MONDAY,
+ "DATE(DATE_SUB(my_col, "
+ "INTERVAL DAYOFWEEK(DATE_SUB(my_col, "
+ "INTERVAL 1 DAY)) - 1 DAY))",
+ ),
+ ],
+)
+def test_time_grain_expressions(
+ grain: Optional[TimeGrain], expected_expression: str
+) -> None:
+ """
+ Test that MySQL time grain expression templates produce the expected SQL.
+ Guards against the bare DATE() call being dropped by SQLGlot sanitization
+ or SQLAlchemy proxying for the SECOND/MINUTE/HOUR grains, which used to
+ truncate to a bare `DATE({col})`.
+ """
+ from superset.db_engine_specs.mysql import MySQLEngineSpec
+
+ actual = MySQLEngineSpec._time_grain_expressions[grain].replace("{col}",
"my_col")
+ assert actual == expected_expression
+
+
+def test_compile_timegrain_expression_preserves_date_truncation() -> None:
+ """
+ Test that compile_timegrain_expression preserves the full DATE_FORMAT
+ truncation in the MySQL HOUR time grain expression, including when the
+ expression is proxied through a subquery (series-limit path).
+
+ Regression test for: ECharts HOUR grain generates invalid SQL (DATE()
+ dropped by sanitization/proxying).
+ """
+ from sqlalchemy import select
+
+ from superset.db_engine_specs.mysql import MySQLEngineSpec
+
+ col = column("my_col")
+ template = MySQLEngineSpec._time_grain_expressions[TimeGrain.HOUR]
+ expr = TimestampExpression(template, col)
+ expected = "DATE_FORMAT(my_col, '%Y-%m-%d %H:00:00')"
+
+ compiled = str(expr)
+ assert compiled == expected, f"DATE_FORMAT truncation was dropped. Got:
{compiled}"
+
+ proxied = str(select(select(expr.label("bucket")).subquery().c.bucket))
+ assert expected in proxied, (
+ f"DATE_FORMAT truncation was dropped in proxied expression. Got:
{proxied}"
+ )
diff --git a/tests/unit_tests/db_engine_specs/test_starrocks.py
b/tests/unit_tests/db_engine_specs/test_starrocks.py
index 42779062ef1..5590c9d70d7 100644
--- a/tests/unit_tests/db_engine_specs/test_starrocks.py
+++ b/tests/unit_tests/db_engine_specs/test_starrocks.py
@@ -22,6 +22,7 @@ from pytest_mock import MockerFixture
from sqlalchemy import JSON, types
from sqlalchemy.engine.url import make_url
+from superset.constants import TimeGrain
from superset.db_engine_specs.starrocks import (
ARRAY,
BITMAP,
@@ -231,8 +232,8 @@ def test_get_catalog_names(mocker: MockerFixture) -> None:
# StarRocks returns rows with keys: ['Catalog', 'Type', 'Comment']
mock_row_1 = mocker.MagicMock()
mock_row_1.keys.return_value = ["Catalog", "Type", "Comment"]
- mock_row_1.__getitem__ = (
- lambda self, key: "default_catalog" if key == "Catalog" else None
+ mock_row_1.__getitem__ = lambda self, key: (
+ "default_catalog" if key == "Catalog" else None
)
mock_row_2 = mocker.MagicMock()
@@ -364,3 +365,23 @@ def
test_get_prequeries_with_email_prefix_from_user_email_when_effective_user_di
assert StarRocksEngineSpec.get_prequeries(database) == [
'EXECUTE AS "alice.doe" WITH NO REVERT;'
]
+
+
+def test_time_grain_expressions_inherit_mysql() -> None:
+ """
+ Test that StarRocksEngineSpec inherits MySQL time grain expressions and
+ that the HOUR grain renders to the correct DATE_FORMAT truncation SQL.
+
+ StarRocks does not override _time_grain_expressions, so it reuses MySQL's
+ templates, including the HOUR grain. This asserts the rendered HOUR output
+ (not object identity) to guard against the HOUR-grain truncation
regression.
+
+ Regression test for: ECharts HOUR grain generated invalid, over-truncated
+ SQL when the grain expression was normalized or proxied (#36798).
+ """
+ from superset.db_engine_specs.starrocks import StarRocksEngineSpec
+
+ actual =
StarRocksEngineSpec._time_grain_expressions[TimeGrain.HOUR].replace(
+ "{col}", "my_col"
+ )
+ assert actual == "DATE_FORMAT(my_col, '%Y-%m-%d %H:00:00')"
