dosubot[bot] commented on issue #37114: URL: https://github.com/apache/superset/issues/37114#issuecomment-3748014884
<!-- Answer --> This is a known bug in Superset 5.0.0: enabling async queries with custom SQL metrics containing newlines (\r\n vs \n) or Jinja templates causes cache key mismatches between the web UI and Celery worker, resulting in HTTP 422 errors and failed data loads. The root cause is that cache key generation does not normalize SQL expressions or template rendering, so the cache key for writing and reading can differ if the SQL formatting or template output changes ([details](https://github.com/apache/superset/issues/35991), [details](https://github.com/apache/superset/issues/35226)). A fix was merged after 5.0.0 in [PR #35419](https://github.com/apache/superset/pull/35419), which sanitizes SQL clauses before cache key generation and ensures consistent hashing regardless of newlines or whitespace. To resolve this, you’ll need to upgrade to a version that includes this PR. If you can’t upgrade, workarounds include disabling async queries (`GLOBAL_ASYNC_QUERIES = False`), avoiding Jinja templates in chart filters, or using CASE WHEN statements or virtual datasets for dynamic SQL logic. Using `url_param('param', add_to_cache_keys=False)` in Jinja templates is another workaround, but it breaks filtering ([see workaround discussion](https://github.com/apache/superset/issues/35226)). About the default `ORDER BY <metrics></metrics>`: Superset’s chart queries often auto-generate an ORDER BY clause based on selected metrics to ensure deterministic results and proper sorting, especially for time series and aggregated charts. This is standard behavior in the query builder. Let me know if you need more details on upgrading or implementing a workaround. <!-- Dosu Comment Footer --> *To reply, just mention [@dosu](https://go.dosu.dev/dosubot).* --- <sup>How did I do? [Good](https://app.dosu.dev/response-feedback/df6bfcca-598c-4f2f-bdc8-582043f90d96?feedback_type=great_response) | [Irrelevant](https://app.dosu.dev/response-feedback/df6bfcca-598c-4f2f-bdc8-582043f90d96?feedback_type=irrelevant_answer) | [Incorrect](https://app.dosu.dev/response-feedback/df6bfcca-598c-4f2f-bdc8-582043f90d96?feedback_type=incorrect_sources) | [Verbose](https://app.dosu.dev/response-feedback/df6bfcca-598c-4f2f-bdc8-582043f90d96?feedback_type=too_verbose) | [Hallucination](https://app.dosu.dev/response-feedback/df6bfcca-598c-4f2f-bdc8-582043f90d96?feedback_type=hallucination) | [Report 🐛](https://app.dosu.dev/response-feedback/df6bfcca-598c-4f2f-bdc8-582043f90d96?feedback_type=bug_report) | [Other](https://app.dosu.dev/response-feedback/df6bfcca-598c-4f2f-bdc8-582043f90d96?feedback_type=other)</sup> [](https://app.dosu.dev/a28d3c7e-a9d3-459e-9fb6-3a6f9ff4f357/ask?utm_source=github) [](https://cursor.com/link/prompt?text=This%20is%20a%20known%20bug%20in%20Superset%205.0.0%3A%20enabling%20async%20queries%20with%20custom%20SQL%20metrics%20containing%20newlines%20%28%5Cr%5Cn%20vs%20%5Cn%29%20or%20Jinja%20templates%20causes%20cache%20key%20mismatches%20between%20the%20web%20UI%20and%20Celery%20worker%2C%20resulting%20in%20HTTP%20422%20errors%20and%20failed%20data%20loads.%20The%20root%20cause%20is%20that%20cache%20key%20generation%20does%20not%20normalize%20SQL%20expressions%20or%20template%20rendering%2C%20so%20the%20cache%20key%20for%20writing%20and%20reading%20can%20differ%20if%20the%20SQL%20formatting%20or%20template%20output%20changes%20%28%5Bdetails%5D%28https%3A//github.com/apache/superset/issues/35991%29%2C%20%5Bdetails%5D%28https%3A//github.com/apache/superset /issues/35226%29%29.%0A%0AA%20fix%20was%20merged%20after%205.0.0%20in%20%5BPR%20%2335419%5D%28https%3A//github.com/apache/superset/pull/35419%29%2C%20which%20sanitizes%20SQL%20clauses%20before%20cache%20key%20generation%20and%20ensures%20consistent%20hashing%20regardless%20of%20newlines%20or%20whitespace.%20To%20resolve%20this%2C%20you%E2%80%99ll%20need%20to%20upgrade%20to%20a%20version%20that%20includes%20this%20PR.%0A%0AIf%20you%20can%E2%80%99t%20upgrade%2C%20workarounds%20include%20disabling%20async%20queries%20%28%60GLOBAL_ASYNC_QUERIES%20%3D%20False%60%29%2C%20avoiding%20Jinja%20templates%20in%20chart%20filters%2C%20or%20using%20CASE%20WHEN%20statements%20or%20virtual%20datasets%20for%20dynamic%20SQL%20logic.%20Using%20%60url_param%28%27param%27%2C%20add_to_cache_keys%3DFalse%29%60%20in%20Jinja%20templates%20is%20another%20workaround%2C%20but%20it%20breaks%20filtering%20%28%5Bsee%20workaround%20discussion%5D%28https%3A//github.com/apache/superset/issues/35226%29%29.%0A%0AAbout% 20the%20default%20%60ORDER%20BY%20%3Cmetrics%3E%3C/metrics%3E%60%3A%20Superset%E2%80%99s%20chart%20queries%20often%20auto-generate%20an%20ORDER%20BY%20clause%20based%20on%20selected%20metrics%20to%20ensure%20deterministic%20results%20and%20proper%20sorting%2C%20especially%20for%20time%20series%20and%20aggregated%20charts.%20This%20is%20standard%20behavior%20in%20the%20query%20builder.%0A%0ALet%20me%20know%20if%20you%20need%20more%20details%20on%20upgrading%20or%20implementing%20a%20workaround.) [](https://go.dosu.dev/discord-bot) [](https://twitter.com/intent/tweet?text=%40dosu_ai%20helped%20me%20solve%20this%20issue!&url=https%3A//github.com/apache/superset/issues/37114) -- 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]
