dosubot[bot] commented on issue #38882: URL: https://github.com/apache/superset/issues/38882#issuecomment-4135074604
<!-- Greeting --> Hi @robertolucchetti! I'm [Dosu](https://go.dosu.dev/dosubot) and Iām helping the superset team. <!-- Answer --> This is a known regression in Superset 6.x! The issue is tracked in [#36798](https://github.com/apache/superset/issues/36798), and a fix is proposed in [PR #38617](https://github.com/apache/superset/pull/38617) (not yet merged). **Root cause:** SQLGlot sanitization and SQLAlchemy's proxy mechanism strip the bare `DATE({col})` call during query construction, specifically affecting HOUR and sub-hour time grains. DAY/MONTH/YEAR grains are unaffected. **Workaround:** Add this to your `superset_config.py`: ```python def FLASK_APP_MUTATOR(app): from superset.db_engine_specs.mysql import MySQLEngineSpec orig = MySQLEngineSpec.get_time_grain_expressions @classmethod def patched(cls): d = dict(orig() or {}) d["PT1H"] = "DATE_ADD(CAST(DATE({col}) AS DATETIME), INTERVAL HOUR({col}) HOUR)" return d try: orig.cache_clear() except Exception: pass MySQLEngineSpec.get_time_grain_expressions = patched return app ``` The `CAST(DATE({col}) AS DATETIME)` wrapper prevents the renderer from stripping the truncation function. <!-- Dosu Comment Footer --> *To reply, just mention [@dosu](https://go.dosu.dev/dosubot?utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=brand-link).* --- <sup>How did I do? [Good](https://app.dosu.dev/response-feedback/f0dee3e4-41cf-4500-95d8-a18fa16641e8?feedback_type=great_response&utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=feedback-great_response) | [Irrelevant](https://app.dosu.dev/response-feedback/f0dee3e4-41cf-4500-95d8-a18fa16641e8?feedback_type=irrelevant_answer&utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=feedback-irrelevant_answer) | [Incorrect](https://app.dosu.dev/response-feedback/f0dee3e4-41cf-4500-95d8-a18fa16641e8?feedback_type=incorrect_sources&utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=feedback-incorrect_sources) | [Verbose](https://app.dosu.dev/response-feedback/f0dee3e4-41cf-4500-95d8-a18fa16641e8?feedback_type=too_verbose&utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=feedback-too_verbose) | [Hallucination](https://app.dosu.dev/response-feedback/f0dee3e4-41cf -4500-95d8-a18fa16641e8?feedback_type=hallucination&utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=feedback-hallucination) | [Report š](https://app.dosu.dev/response-feedback/f0dee3e4-41cf-4500-95d8-a18fa16641e8?feedback_type=bug_report&utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=feedback-bug_report) | [Other](https://app.dosu.dev/response-feedback/f0dee3e4-41cf-4500-95d8-a18fa16641e8?feedback_type=other&utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=feedback-other)</sup> [](https://app.dosu.dev/a28d3c7e-a9d3-459e-9fb6-3a6f9ff4f357/ask?utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=chat-badge) [](https://cursor.com/link/prompt?text=This%20is%20a%20known%20regression%20in%20Superset%206.x%21%20The%20issue%20is%20tracked%20in%20%5B%2336798%5D%28https%3A//github.com/apache/superset/issues/36798%29%2C%20and%20a%20fix%20is%20proposed%20in%20%5BPR%20%2338617%5D%28https%3A//github.com/apache/superset/pull/38617%29%20%28not%20yet%20merged%29.%0A%0A%2A%2ARoot%20cause%3A%2A%2A%20SQLGlot%20sanitization%20and%20SQLAlchemy%27s%20proxy%20mechanism%20strip%20the%20bare%20%60DATE%28%7Bcol%7D%29%60%20call%20during%20query%20construction%2C%20specifically%20affecting%20HOUR%20and%20sub-hour%20time%20grains.%20DAY/MONTH/YEAR%20grains%20are%20unaffected.%0A%0A%2A%2AWorkaround%3A%2A%2A%20Add%20this%20to%20your%20%60supers et_config.py%60%3A%0A%0A%60%60%60python%0Adef%20FLASK_APP_MUTATOR%28app%29%3A%0A%20%20%20%20from%20superset.db_engine_specs.mysql%20import%20MySQLEngineSpec%0A%0A%20%20%20%20orig%20%3D%20MySQLEngineSpec.get_time_grain_expressions%0A%0A%20%20%20%20%40classmethod%0A%20%20%20%20def%20patched%28cls%29%3A%0A%20%20%20%20%20%20%20%20d%20%3D%20dict%28orig%28%29%20or%20%7B%7D%29%0A%20%20%20%20%20%20%20%20d%5B%22PT1H%22%5D%20%3D%20%22DATE_ADD%28CAST%28DATE%28%7Bcol%7D%29%20AS%20DATETIME%29%2C%20INTERVAL%20HOUR%28%7Bcol%7D%29%20HOUR%29%22%0A%20%20%20%20%20%20%20%20return%20d%0A%0A%20%20%20%20try%3A%0A%20%20%20%20%20%20%20%20orig.cache_clear%28%29%0A%20%20%20%20except%20Exception%3A%0A%20%20%20%20%20%20%20%20pass%0A%0A%20%20%20%20MySQLEngineSpec.get_time_grain_expressions%20%3D%20patched%0A%20%20%20%20return%20app%0A%60%60%60%0A%0AThe%20%60CAST%28DATE%28%7Bcol%7D%29%20AS%20DATETIME%29%60%20wrapper%20prevents%20the%20renderer%20from%20stripping%20the%20truncation%20function.) [](https://go.dosu.dev/discord-bot?utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=join-discord) [](https://twitter.com/intent/tweet?text=%40dosu_ai%20helped%20me%20solve%20this%20issue!&url=https%3A//github.com/apache/superset/issues/38882) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
