Bug description
I faced this problem with superset oracle connection. There is a connection with oracle and there is data, but there is this error in the filter, this process happened after connecting to oracle postres, this problem is not there, because it is the superset settings or the queries are wrong?
and where does such an error 1 appear in the console?
How to reproduce the bug
I don't know
Screenshots/recordings
Superset version
3.1.2
Python version
3.9
Node version
16
Browser
Chrome
Additional context
No response
Checklist
- [ ] I have searched Superset docs and Slack and didn't find a solution to my problem.
- [X] 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: mujohiddin
@mistercrunch @villebro @michael-s-molina @betodealmeida
Can you help me, I have not been able to solve it for several weeks
Comment From: mujohiddin
I faced this problem with superset oracle connection. There is a connection with oracle and there is data, but there is this error in the filter, this process happened after connecting to oracle postres, this problem is not there, because it is the superset settings or the queries are wrong?
Comment From: michael-s-molina
Hi @mujohiddin. Oracle does not support GROUP BY 1. I believe you are using a virtual dataset? If that's the case, just replace GROUP BY 1 with GROUP BY "CODE".
Comment From: mujohiddin
@michael-s-molina but 5 months ago version didn't have this error, I got this error when upgrading to new version.
Comment From: mujohiddin
@michael-s-molina GROUP BY 1 with GROUP BY "CODE". I could not find this part, can you tell me where to replace it
Comment From: michael-s-molina
I could not find this part, can you tell me where to replace it
1 - Go to the filter configuration 2 - Check what's the dataset that's being used for the filter 3 - Go to the datasets list and check if that dataset is a virtual dataset 4 - If it is, you can edit its query
Comment From: mujohiddin
@michael-s-molina , I followed the steps what you suggested, but there problem is not in the dataset. The above query was generated by Superset itself on filter pane. I think issue is with SQLA dialect, because the source was change to Postgre, it works, since Postgre supports Group BY 1, but Oracle does not. Therefore I am guesing it is due to SQLA dialect on newer version of Superset.
Moreover, I cannot create Table, Pivot charts , they are failling with the same error :
Comment From: michael-s-molina
The above query was generated by Superset itself on filter pane. I think issue is with SQLA dialect, because the source was change to Postgre, it works, since Postgre supports Group BY 1, but Oracle does not.
@rusackas @betodealmeida Does any of you have an Oracle instance to test this? Or do you know who's the Oracle dialect maintainer?
Comment From: michael-s-molina
@mujohiddin Can you also share what was the previous version where this was working?
Comment From: mujohiddin
@michael-s-molina 1. I don't remember the exact version, it was about 6 months ago, It works successfully with postgre, clickhouse, oracle 2. The last new superset I installed recently was 1 month before the project in this case there is a problem with oracle
Comment From: rusackas
Sadly, I don't know of anyone with Oracle. It's still on the wish list in the rolodex, if anyone on this thread would be kind enough to sign up.
Comment From: mujohiddin
@mistercrunch @villebro @michael-s-molina @betodealmeida @john-bodley
Comment From: RavshanbekXB
I have also faced this issue with Oracle, any updates @rusackas , @michael-s-molina , @mistercrunch ? Any workaround applied ? @mujohiddin
Comment From: mujohiddin
i have old version working with oracle but i have problems in new version, do i need to add some parts from old version or some scprits? exactly in which python file or class is this generation process taking place. If you tell me that, I will send it to you, and we will compare the status of the two versions
@mistercrunch @villebro @michael-s-molina @betodealmeida @john-bodley @rusackas
Comment From: mujohiddin
I have also faced this issue with Oracle, any updates @rusackas , @michael-s-molina , @mistercrunch ? Any workaround applied ? @mujohiddin
No, the solution has not been found yet. I am waiting for a response from contributions
Comment From: rusackas
Let's see if our AI overlords can help at all...
@dosu-bot
Comment From: dosubot[bot]
To address the "ORA-00979: not a GROUP BY expression" error in Superset when using Oracle, it seems the issue stems from Oracle's lack of support for the "GROUP BY 1" shorthand syntax, which is not an issue with databases like PostgreSQL that support this syntax. The error arises because Oracle requires explicit column names in the GROUP BY clause instead of positional references.
A potential solution involves customizing the SQL compilation process in SQLAlchemy, which Superset uses to interact with databases. You can create a custom SQLAlchemy dialect for Oracle or modify the existing one to ensure that GROUP BY expressions are correctly handled. This involves overriding the visit_group_by method in a custom compiler class to ensure that column names are used in GROUP BY clauses instead of positional references.
Additionally, changes in newer versions of Superset, particularly in how SQL statements are generated for Oracle databases, could contribute to this issue. These changes might involve adjustments in time grain expressions, date-time conversion methods, and enforced column alias quoting, which could affect the generation of GROUP BY clauses.
A specific commit addressing a similar issue by modifying the SQL generation logic in Superset to comply with Oracle's requirements might also offer a clue. This commit ensures that columns used in the ORDER BY clause are included in the SELECT clause, as required by Oracle.
Implementing these solutions requires a good understanding of Python, SQLAlchemy, and Superset's architecture. If you're comfortable making these adjustments, you could potentially resolve the issue. Otherwise, it might be beneficial to reach out to the Superset community for further assistance or consider hiring a developer with the necessary expertise.
#### Details
**Sources** - [Group by filters broken in 1.5.0](https://github.com/apache/superset/issues/20000) - [superset/models/helpers.py](https://github.com/apache/superset/blob/master/superset/models/helpers.py) - [UPDATING.md](https://github.com/apache/superset/blob/master/UPDATING.md) - [superset/db_engine_specs/oracle.py](https://github.com/apache/superset/blob/master/superset/db_engine_specs/oracle.py)**About Dosu** This response is meant to be useful and save you time. It is **not meant to be a precise solution**, but rather a starting point for your own research. Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.
To continue the conversation, mention @dosu.
Comment From: villebro
Debugging this actually only requires having access to the Oracle SQLA dialect. I assume @mujohiddin you are using the default dialect included in SQLA? If so, I think we may be doing something wrong, as I'd be extremely surprised if SQLA has bug of this magnitude..
Comment From: vranjan25
This is an issue from superset filter, it's not using the column name in group by. DATABASE: Oracle SQLALCHEMY URI: oracle+cx_oracle://myUser:XXXXXXXXXX@dev
Comment From: mistercrunch
I'm not clear on what may trigger sqlalchemy to use ordinals (GROUP BY 1) while using its group_by() clause, for anything. In my experience it will always use aliases.
And why would it do it for Oracle, one of the databases that seems to not support this (?)
Comment From: vranjan25
That's the question why superset is doing this?
Comment From: mujohiddin
Is this problem still not fixed? it was working in old version ie below versions 3.0.0, 3.0.1, 3.0.2 new version is showing this problem. I know there have been a lot of changes since then, but it's important that later we still have this problem, we need to find it. @mistercrunch @villebro @michael-s-molina @betodealmeida @john-bodley @rusackas
Comment From: rusackas
it's important that later we still have this problem, we need to find it.
100% agreed, but I don't think a single one of the people you mentioned here has an Oracle DB to test against. If you do, would you be able or willing to do some digging here? It's times like this where we can really use the help of external contributions to the project. The comment from the Dosu bot above may be accurate in that a different or custom SQLAlchemy dialect may be required, but an Oracle DB would be needed to test any of that.
Comment From: fraannco
Hi, I have just installed this version of superset on my server, is there any way to install an older version that does not have this problem with oracle? I get the same message at the time of making the charts.
Comment From: mistercrunch
You can git checkout any version tag and build from there.
Comment From: mujohiddin
This was working in versions 3.0.1 and 3.0.2, so you should see changes in later versions
Comment From: mistercrunch
so you should see changes in later versions
Lots of changes since then, it'd be great if someone could bissect this one. @rusackas published this recently -> https://preset.io/blog/using-git-bisect-to-find-and-fix-bugs/
Comment From: mujohiddin
@Sumeet
this bug was not present in version 3.0.1 and 3.0.2, this problem should be fixed in the new version. this error is omitted
Comment From: mujohiddin
@sumeet-singh04
Comment From: wangyan1856
I found a word around for this: comment out this code in the superset/models/helpers.py file:
try.
sql = SQLStatement(sql, engine=self.db_engine_spec.engine).format()
except SupersetParseError.
logger.warning("Unable to parse SQL to format it, passing it as-is")
Here the sqlglot tool is used to parse the sql, the purpose is to make sure that there is only one statement, I guess it should be in order to avoid potential problems with custom SQL queries, but it shouldn't be necessary for the sql generated by superset itself
Comment From: MarcoFranchin96
Hello,
sorry, I also have this problem however, in the indicated file (superset/models/helpers.py) there is no ne portion of code as indicated by @wangyan1856 's comment . By any chance with the latest version has this problem disappeared or am I doing something wrong? Can I get more details for the resolution, even momentary?
Thank you
Comment From: rusackas
Hey all. This thread has been silent for upward of 6 months now. Is anyone still facing this in current versions (i.e. 4.1.2/5.0.0rc2)? If not, it may be closed as inactive at some point soon. As always, fix PRs are welcome if anyone is indeed facing this and can get to the bottom of it.
Comment From: rusackas
OK... so it seems we have a conundrum here:
* None of the core contributors have access to Oracle to test/fix
* None of the people facing the problem are opening fix PRs.
* We don't know if it's happening on the final 5.0 release or on current master after the SQLParse removal
* It's been inactive for a long time, and has a seemingly celebrated workaround on the thread.
I'll go ahead and close this as not-planned, but if anyone wants to dig in further or reproduce it in the as-of-now codebase, we can revisit/reopen as needed.
Comment From: betodealmeida
@rusackas I can test it, I have acess to an "Always Free Oracle Autonomous Database Serverless".
Comment From: betodealmeida
I tested with a 19c version of Oracle and it works as expected:
Note that we're generating the GROUP BY 1 query in the chart (this is a physical dataset):
Oracle 19c seems to work fine with that, and that was the older version I had available to test. I'll try to change our logic so we don't generate the GROUP BY 1, using the expression instead, so it works with older versions.