WITH Clause with UNION could not be saved as dataset

[Scope0910]

A clear and concise description of what the bug is.

How to reproduce the bug

1. Create a database Connection of type oracle (first qithout enable DML operations)
2. Open SQL Lab and run following sql

WITH SET1 AS (SELECT SYSDATE FROM DUAL UNION Select SYSDATE from DUAL), SET2 AS (SELECT * FROM SET1)
SELECT * FROM SET2;

3. an error occured, that the statement is not allowed
4. go to datasource settings and enable DML-Feature
5. run SQL again - it works
6. if i want so save the dataset an error occured: value = f(*args, **kwargs) File "/app/superset/views/base_api.py", line 93, in wraps return f(self, *args, **kwargs) File "/app/superset/datasets/api.py", line 315, in post new_model = CreateDatasetCommand(item).run() File "/app/superset/datasets/commands/create.py", line 50, in run dataset.fetch_metadata(commit=False) File "/app/superset/connectors/sqla/models.py", line 1247, in fetch_metadata new_columns = self.external_metadata() File "/app/superset/connectors/sqla/models.py", line 715, in external_metadata return get_virtual_table_metadata(dataset=self) File "/app/superset/connectors/sqla/utils.py", line 116, in get_virtual_table_metadata raise SupersetSecurityException( superset.exceptions.SupersetSecurityException: Only SELECT statements are allowed

Expected results

Union-Statements within SQl WITH Clause should work properly. In best case if the DML feature would be disabled.

Actual results

Following Exception will raise: value = f(*args, **kwargs) File "/app/superset/views/base_api.py", line 93, in wraps return f(self, *args, **kwargs) File "/app/superset/datasets/api.py", line 315, in post new_model = CreateDatasetCommand(item).run() File "/app/superset/datasets/commands/create.py", line 50, in run dataset.fetch_metadata(commit=False) File "/app/superset/connectors/sqla/models.py", line 1247, in fetch_metadata new_columns = self.external_metadata() File "/app/superset/connectors/sqla/models.py", line 715, in external_metadata return get_virtual_table_metadata(dataset=self) File "/app/superset/connectors/sqla/utils.py", line 116, in get_virtual_table_metadata raise SupersetSecurityException( superset.exceptions.SupersetSecurityException: Only SELECT statements are allowed

Screenshots

see attachment

Environment

(please complete the following information):

• browser type and version:
• superset version: superset 3.0.0
• python version: Python 3.9.18
• node.js version: v18.13.0
• any feature flags active: no

[rusackas]

Do you know if this is happening with other DBs, or if it's strictly an Oracle phenomenon? We don't have any oracle folks on the committer roster, so we could use help with an investigation/fix if you're open to it.

[rusackas]

Sadly, this is at risk of being closed as stale if nobody has interest in either reproducing or fixing the issue.

[rahmedrbx]

Can confirm that we are hitting a similar issue with a different DB (trino in our case)

[aleksei-perepelov]

@rahmedrbx Spent 2 hours trying to solve it. In my case enclosing CTEs with brackets helped, i.e.

with "a" as (),
"b" as (),

etc. There a lot of issues that seem to be related to some one problem: https://github.com/apache/superset/issues/22704

p.s. none of the things that have to do with TALISMAN or CSRF tokens helped.

[gulldan]

same with clickhouse

[rusackas]

This issue has gone silent for quite some time, and seems to be a duplicate of the linked issue above, if I'm not mistaken. If anyone can say this is a distinct issue, or wants to tackle it, that'd be great, otherwise it'll be closed as inactive and/or duplicate.

[Makarov-AA]

Same here, superset version 3.0.0, postgresql 16.8 I don't know why this issue is considered duplicate of https://github.com/apache/superset/issues/22704, can't see relation. I use UNION in SQL Lab in request - everything is fine. As long as I use it in WITH AS statement - it breaks.

SELECT * FROM a UNION SELECT * FROM b; -- works fine.

WITH c AS (
	SELECT * FROM a UNION SELECT * FROM b
)
SELECT * FROM c;

breaks immediately with an error:

DB engine Error
Only SELECT statements are allowed against this database.

This may be triggered by:
Issue 1022 - Database does not allow data manipulation.

If I enable DML in dataset settings, everything works, but this is not an option. Since UNION is not supported I have to come up with some weird ideas like OUTER JOIN ON FALSE to replace UNION

psql executes both requests just fine with read only user