[Oracle] - ORA-00904 - invalid identifier

[jhult]

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!

• 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.
• 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 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.

[junlincc]

Thanks for reporting! @eschutho could you verify and look into which DBs are being affected if possible? thanks! 🙏

[eschutho]

This revert commit should fix this issue: #15752