Jinja dataset macro doesn't work for Oracle databases

[kron82]

Bug description

The error is that the Jinja macro "Dataset" is replaced by a subquery using an alias with the keyword "as" in the "from" clause. In SQL syntax, the keyword "as" is optional in the "from" clause and Oracle DB does not support it at all. This leads to the inability to use the "Dataset" macro with Oracle DB.
Steps to reproduce:

1. Create a SQL query and set Oracle DB as the source database in SQLLab.
2. Set the SQL text "select * from dual"
3. Save this query as a dataset with the name "DUAL"
4. Find the dataset_id of the created dataset (in URL for example)
5. Create and execute a new query with the text "select * from {{dataset(X)}}", where X should be replaced with the dataset_id from step 4.

Screenshots/recordings

No response

Superset version

master / latest-dev

Python version

Not applicable

Node version

Not applicable

Browser

Not applicable

Additional context

1. Set feature flag ENABLE_TEMPLATE_PROCESSING to True
2. Use any Oracle Database for queries.
   log.txt

Checklist

• I have searched Superset docs and Slack and didn't find a solution to my problem.
• 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.

[dosubot]

Hello @kron82! I'm here to help you with bugs, questions, and becoming a contributor. Let's tackle this issue together!

The issue you're encountering with the Jinja 'Dataset' macro in Oracle databases is due to Oracle's SQL syntax not supporting the use of the "AS" keyword for aliasing in the "FROM" clause. This is a known limitation with Oracle's SQL syntax.

To work around this issue, you can modify the SQL generated by the Jinja macro to remove the "AS" keyword when using Oracle. This would involve customizing the SQL generation logic for Oracle databases to ensure compatibility. Unfortunately, I don't have specific details on how to implement this workaround directly in Superset, but you might consider creating a custom template processor or modifying the existing logic to handle this case specifically for Oracle.

If you have access to the Superset codebase, you could explore the dataset_macro function and adjust the SQL string it generates to omit the "AS" keyword when the target database is Oracle. This would require some familiarity with Python and the Superset codebase.

To continue talking to Dosu, mention @dosu.

[rusackas]

Is this still an issue, or did you figure something out? We simply don't have any oracle users that are committers around here, so we'd welcome some extra troubleshooting/fixing if you can. Otherwise this will be closed as stale/not-planned before too long.