Comments (21)
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
- superset/models/helpers.py
- UPDATING.md
- 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.
from superset.
Let's see if our AI overlords can help at all...
from superset.
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..
from superset.
@mistercrunch
@villebro
@michael-s-molina
@betodealmeida
Can you help me, I have not been able to solve it for several weeks
from superset.
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?
from superset.
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"
.
from superset.
@michael-s-molina
but 5 months ago version didn't have this error, I got this error when upgrading to new version.
from superset.
@michael-s-molina GROUP BY 1 with GROUP BY "CODE".
I could not find this part, can you tell me where to replace it
from superset.
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
from superset.
@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 :
from superset.
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?
from superset.
@mujohiddin Can you also share what was the previous version where this was working?
from superset.
- I don't remember the exact version, it was about 6 months ago,
It works successfully with postgre, clickhouse, oracle - The last new superset I installed recently was 1 month before the project
in this case there is a problem with oracle
from superset.
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.
from superset.
how can i detect the old superset version, 0.0.dev is the same in the interface
@mistercrunch
@villebro
@michael-s-molina
@betodealmeida
@john-bodley
from superset.
I have also faced this issue with Oracle,
any updates @rusackas , @michael-s-molina , @mistercrunch ?
Any workaround applied ? @mujohiddin
from superset.
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
from superset.
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
from superset.
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
from superset.
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 (?)
from superset.
That's the question why superset is doing this?
from superset.
Related Issues (20)
- superset 4.0.1 uses werkzeug 3.0.1 with CVE => should update to 3.0.3 or newer HOT 2
- superset 4.0.1 uses sqlparse 0.4.4 with two high CVE findings => update to 0.5.0 HOT 2
- Doris made an error in creating a chart using the GROUP_CONCAT syntax HOT 2
- Content not from webpack is served from 'D:\tech_release\superset-fork\superset\static\assets' directory HOT 4
- 🐛 RLS form not showing all datasets available HOT 1
- requests.exceptions.HTTPError: 422 Client Error: UNPROCESSABLE ENTITY for url: HOT 1
- ROW_LIMIT not working HOT 5
- Superset throws access is denied error when viewing dashboard even after signing in. HOT 2
- {{ current_user_email() }} doesn't work HOT 3
- [SIP-136] split alert and report Menu and Access rights HOT 2
- (empty post) HOT 1
- Broken ssh connections: Could not establish session to SSH gateway HOT 1
- BUG) Superset get wrong value of TIMESTAMP WITH TIME ZONE type field on Trino client HOT 2
- Can't get ALERTS & REPORTS working. Error: Failed taking a screenshot Message: 'geckodriver' executable needs to be in PATH. HOT 4
- Superset throws unexpected error while loading filters in a dashboard. HOT 1
- Can't export dashboards with CLI if 'admin' user is not created HOT 1
- Option for file could be the same for superset export-dashboards / superset import-dashboards
- superset fab list-users return username, email, role but not firstname, lastname, active HOT 6
- Can't import dashbaords with CLI HOT 4
- 🐛 Problems creating rows and columns in the superset. HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from superset.