Bug description
We've been testing the 6.0.0rc1 release and have run into a problem. Our prior setup (Superset 5.0.0 and Pinot 1.0.0) operated as expected, but after the update, we are experiencing an error on all charts (see screenshot below). While investigating, we came across ticket https://github.com/apache/superset/issues/34700. Ironically, this ticket is supposed to correct the exact problem we are now facing. This bug did not affect us in version 5.0.0, but has been introduced with the 6.0.0rc1 release, despite the inclusion of the supposed fix.
Screenshots/recordings
Superset version
master / latest-dev
Python version
3.9
Node version
16
Browser
Chrome
Additional context
No response
Checklist
- [x] I have searched Superset docs and Slack and didn't find a solution to my problem.
- [ ] I have searched the GitHub issue tracker and didn't find a similar bug report.
- [ ] I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.
Comment From: sofiankhalfi-kosmos
Maybe the recent update of Pinot DB SQL dialect doesn't work with pinot 1.0.0 ?
Comment From: dosubot[bot]
Hi @sofiankhalfi-kosmos! I'm Dosu and I’m helping the superset team.
Yes, recent changes in Superset's SQL dialect handling could be causing incompatibility with Pinot 1.0.0. Superset generates Pinot queries using the MySQL dialect via the sqlglot library, and recent updates to sqlglot or Superset's dialect mapping may produce SQL syntax (like backticks or tabs) that Pinot 1.0.0 can't parse. The Pinot engine spec itself hasn't changed in a way that would break compatibility, but the underlying SQL generator likely has. This means the issue is probably due to how Superset (and sqlglot) now formats SQL for Pinot, not a direct change in Pinot-specific logic. If you need to restore compatibility, you may need to adjust the SQLGLOT_DIALECTS_EXTENSIONS config or patch the dialect mapping to avoid MySQL-specific syntax for Pinot 1.0.0. More details on the dialect mapping and recent changes can be found here and here.
To reply, just mention @dosu.
How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other 
Comment From: betodealmeida
@sofiankhalfi-kosmos what version of Pinot are you using?
Comment From: sofiankhalfi-kosmos
@betodealmeida Pinot 1.0.0
Comment From: sofiankhalfi-kosmos
The problem can be resolved by executing this command:
sed -i '/"pinot"/d' /app/superset/sql/parse.py
A more robust solution would be to allow the MySQL dialect to be configured through a parameter or an environment variable, thus avoiding the need for manual modification of the Python source file.
Comment From: betodealmeida
OK, so it sounds like we should not be using the MySQL dialect for Pinot, which is weird because the docs say:
Pinot provides a SQL interface for querying, which uses the Calcite SQL parser to parse queries and the MYSQL_ANSI dialect.
Comment From: sofiankhalfi-kosmos
Maybe there is a difference between the 1.0.0 and the latest version
Comment From: betodealmeida
This should fix it: https://github.com/apache/superset/pull/34895