GitHub user dosubot[bot] added a comment to the discussion: Error: ORA-00979: not a GROUP BY expression - Superset with OracleDB
<!-- Greeting --> Hi @alislman21! I'm [Dosu](https://go.dosu.dev/dosubot) and I’m helping the superset team. <!-- Answer --> This is a known issue when using Superset with Oracle: Oracle does not support positional references like `GROUP BY 1`, but Superset (via SQLGlot) sometimes generates them, leading to the ORA-00979 error. This happens especially when the "Formatted" option is enabled in SQL Lab or chart queries, which rewrites queries with positional GROUP BY clauses that Oracle rejects [see discussion](https://github.com/apache/superset/issues/35414). There is currently no configuration or patch in Superset 6.0 to force explicit column names in GROUP BY for Oracle. The only reliable workaround is to keep the "Formatted" option set to False, so Superset does not rewrite the query with positional GROUP BY [details](https://github.com/apache/superset/issues/35414). Alternatively, you can manually edit your queries to use explicit column names in the GROUP BY clause. Some users have reported that upgrading the `sqlglot` dependency to version 27.21.0 or later resolves the issue, as recent versions of sqlglot improved GROUP BY handling for Oracle. You can try adding `sqlglot==27.29.0` (or newer) to your `requirements-local.txt` and reinstalling dependencies [reference](https://github.com/apache/superset/issues/35414). There is no built-in Superset config or Oracle engine spec override for this; a permanent fix would require a dependency upgrade or code change. For more background, see the related issues and discussions: - [Issue 35414](https://github.com/apache/superset/issues/35414) - [Issue 37044](https://github.com/apache/superset/issues/37044) - [Discussion 31919](https://github.com/apache/superset/discussions/31919) <!-- 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/4ddbe46b-4b17-418d-845f-9cdbc90a135b?feedback_type=great_response) | [Irrelevant](https://app.dosu.dev/response-feedback/4ddbe46b-4b17-418d-845f-9cdbc90a135b?feedback_type=irrelevant_answer) | [Incorrect](https://app.dosu.dev/response-feedback/4ddbe46b-4b17-418d-845f-9cdbc90a135b?feedback_type=incorrect_sources) | [Verbose](https://app.dosu.dev/response-feedback/4ddbe46b-4b17-418d-845f-9cdbc90a135b?feedback_type=too_verbose) | [Hallucination](https://app.dosu.dev/response-feedback/4ddbe46b-4b17-418d-845f-9cdbc90a135b?feedback_type=hallucination) | [Report 🐛](https://app.dosu.dev/response-feedback/4ddbe46b-4b17-418d-845f-9cdbc90a135b?feedback_type=bug_report) | [Other](https://app.dosu.dev/response-feedback/4ddbe46b-4b17-418d-845f-9cdbc90a135b?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%20issue%20when%20using%20Superset%20with%20Oracle%3A%20Oracle%20does%20not%20support%20positional%20references%20like%20%60GROUP%20BY%201%60%2C%20but%20Superset%20%28via%20SQLGlot%29%20sometimes%20generates%20them%2C%20leading%20to%20the%20ORA-00979%20error.%20This%20happens%20especially%20when%20the%20%22Formatted%22%20option%20is%20enabled%20in%20SQL%20Lab%20or%20chart%20queries%2C%20which%20rewrites%20queries%20with%20positional%20GROUP%20BY%20clauses%20that%20Oracle%20rejects%C2%A0%5Bsee%20discussion%5D%28https%3A//github.com/apache/superset/issues/35414%29.%0A%0AThere%20is%20currently%20no%20configuration%20or%20patch%20in%20Superset%206.0%20to%20force%20explicit%20column%20names%20in%20GROUP%20BY%20for%20Oracle.%20The%20onl y%20reliable%20workaround%20is%20to%20keep%20the%20%22Formatted%22%20option%20set%20to%20False%2C%20so%20Superset%20does%20not%20rewrite%20the%20query%20with%20positional%20GROUP%20BY%C2%A0%5Bdetails%5D%28https%3A//github.com/apache/superset/issues/35414%29.%20Alternatively%2C%20you%20can%20manually%20edit%20your%20queries%20to%20use%20explicit%20column%20names%20in%20the%20GROUP%20BY%20clause.%0A%0ASome%20users%20have%20reported%20that%20upgrading%20the%20%60sqlglot%60%20dependency%20to%20version%2027.21.0%20or%20later%20resolves%20the%20issue%2C%20as%20recent%20versions%20of%20sqlglot%20improved%20GROUP%20BY%20handling%20for%20Oracle.%20You%20can%20try%20adding%20%60sqlglot%3D%3D27.29.0%60%20%28or%20newer%29%20to%20your%20%60requirements-local.txt%60%20and%20reinstalling%20dependencies%C2%A0%5Breference%5D%28https%3A//github.com/apache/superset/issues/35414%29.%0A%0AThere%20is%20no%20built-in%20Superset%20config%20or%20Oracle%20engine%20spec%20override%20for%20this%3B%20a%20perman ent%20fix%20would%20require%20a%20dependency%20upgrade%20or%20code%20change.%20For%20more%20background%2C%20see%20the%20related%20issues%20and%20discussions%3A%0A-%20%5BIssue%2035414%5D%28https%3A//github.com/apache/superset/issues/35414%29%0A-%20%5BIssue%2037044%5D%28https%3A//github.com/apache/superset/issues/37044%29%0A-%20%5BDiscussion%2031919%5D%28https%3A//github.com/apache/superset/discussions/31919%29) [](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/discussions/37431) GitHub link: https://github.com/apache/superset/discussions/37431#discussioncomment-15599006 ---- This is an automatically sent email for [email protected]. To unsubscribe, please send an email to: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
