jhult opened a new issue #15640: URL: https://github.com/apache/superset/issues/15640
Trying to run a Dataset query against an Oracle database fails with `oracle error: ORA-00904: "cost_total": invalid identifier` ### Expected results The query should run. ### Actual results I receive the following error: ``` Oracle Error oracle error: ORA-00904: "cost_total": invalid identifier This may be triggered by: Issue 1002 - The database returned an unexpected error. ``` #### Screenshots  #### How to reproduce the bug 1. Build a Docker image with Oracle database driver support: ``` git clone -b oracle --single-branch https://github.com/jhult/superset.git cd superset docker build --tag superset-oracle -f Dockerfile-Oracle . ``` 2. Run `docker-compose -f docker-compose-non-dev.yml up` (this uses the image that was built in previous step (e.g. `x-superset-image: &superset-image superset-oracle`) 3. Connect to an Oracle database 4. Create a Dataset based on an Oracle database table 5. Edit the Dataset 6. Use `Aggregate` Query Mode and select one specific column (not "Select All") for the `Group By`. 7. Run the query and view the error: ### Environment (please complete the following information): - superset version: `superset version`: 0.999.0dev - python version: `python --version`: 3.7.9 - node.js version: `node -v`: 14.15.5 ### Checklist Make sure to follow these steps before submitting your issue - thank you! - [x] I have checked the superset logs for python stacktraces and included it here as text if there are any. - [ ] I have reproduced the issue with at least the latest released version of superset. - [x] I have checked the issue tracker for the same issue and I haven't found one similar. **This does not occur in 1.2.0-dev. However, this appears to be because the Pull Request didn't make it into that release.** ### Log ``` superset_app | Query SELECT "cost_total" superset_app | FROM superset_app | (SELECT "cost_total" AS "cost_total" superset_app | FROM cost.daily_cost_report superset_app | GROUP BY "cost_total") superset_app | WHERE ROWNUM <= 10000 on schema cost failed superset_app | Traceback (most recent call last): superset_app | File "/app/superset/connectors/sqla/models.py", line 1525, in query superset_app | df = self.database.get_df(sql, self.schema, mutator=assign_column_label) superset_app | File "/app/superset/models/core.py", line 411, in get_df superset_app | self.db_engine_spec.execute(cursor, sqls[-1]) superset_app | File "/app/superset/db_engine_specs/base.py", line 1089, in execute superset_app | raise cls.get_dbapi_mapped_exception(ex) superset_app | File "/app/superset/db_engine_specs/base.py", line 1087, in execute superset_app | cursor.execute(query) superset_app | cx_Oracle.DatabaseError: ORA-00904: "cost_total": invalid identifier superset_app | 2021-07-12 19:51:48,558:WARNING:superset.connectors.sqla.models:Query SELECT "cost_total" superset_app | FROM superset_app | (SELECT "cost_total" AS "cost_total" superset_app | FROM cost.daily_cost_report superset_app | GROUP BY "cost_total") superset_app | WHERE ROWNUM <= 10000 on schema cost failed superset_app | Traceback (most recent call last): superset_app | File "/app/superset/connectors/sqla/models.py", line 1525, in query superset_app | df = self.database.get_df(sql, self.schema, mutator=assign_column_label) superset_app | File "/app/superset/models/core.py", line 411, in get_df superset_app | self.db_engine_spec.execute(cursor, sqls[-1]) superset_app | File "/app/superset/db_engine_specs/base.py", line 1089, in execute superset_app | raise cls.get_dbapi_mapped_exception(ex) superset_app | File "/app/superset/db_engine_specs/base.py", line 1087, in execute superset_app | cursor.execute(query) superset_app | cx_Oracle.DatabaseError: ORA-00904: "cost_total": invalid identifier ``` ### Additional context I believe this was introduced via Pull Request #15465. I was able to rectify this by altering [this line of models.py](https://github.com/preset-io/superset/blob/master/superset/connectors/sqla/models.py#L932) as follows to exclude the implementation for #15465 for Oracle databases: ``` if ( db_engine_spec.force_column_alias_quotes and db_engine_spec.engine != "oracle" ): ``` I am not (currently) submitting a Pull Request as I'm not sure what other ramifications this "fix" may have. -- 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]
