You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@superset.apache.org by "Scope0910 (via GitHub)" <gi...@apache.org> on 2023/10/16 13:31:52 UTC
[I] WITH Clause with UNION is not supported [superset]
Scope0910 opened a new issue, #25659:
URL: https://github.com/apache/superset/issues/25659
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
`<img width="1613" alt="Screenshot 2023-10-16 152915" src="https://github.com/apache/superset/assets/74256450/b1468d27-4e60-4ba8-bc84-a0797f6750a4"> `
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
--
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: notifications-unsubscribe@superset.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] WITH Clause with UNION could not be saved as dataset [superset]
Posted by "mason-gordon (via GitHub)" <gi...@apache.org>.
mason-gordon commented on issue #25659:
URL: https://github.com/apache/superset/issues/25659#issuecomment-4091268306
Using Superset 4.1.1, I ran into a different stack trace that resulted in the same error: Only `SELECT` statements are allowed.
```python
superset_app | Traceback (most recent call last):
superset_app | File "/usr/local/lib/python3.10/site-packages/flask_appbuilder/api/__init__.py", line 111, in wraps
superset_app | return f(self, *args, **kwargs)
superset_app | File "/app/superset/views/base_api.py", line 119, in wraps
superset_app | duration, response = time_function(f, self, *args, **kwargs)
superset_app | File "/app/superset/utils/core.py", line 1364, in time_function
superset_app | response = func(*args, **kwargs)
superset_app | File "/app/superset/utils/log.py", line 303, in wrapper
superset_app | value = f(*args, **kwargs)
superset_app | File "/app/superset/views/base_api.py", line 91, in wraps
superset_app | return f(self, *args, **kwargs)
superset_app | File "/app/superset/datasets/api.py", line 337, in post
superset_app | new_model = CreateDatasetCommand(item).run()
superset_app | File "/app/superset/utils/decorators.py", line 266, in wrapped
superset_app | return on_error(ex)
superset_app | File "/app/superset/utils/decorators.py", line 236, in on_error
superset_app | raise ex
superset_app | File "/app/superset/utils/decorators.py", line 259, in wrapped
superset_app | result = func(*args, **kwargs)
superset_app | File "/app/superset/commands/dataset/create.py", line 51, in run
superset_app | dataset.fetch_metadata()
superset_app | File "/app/superset/connectors/sqla/models.py", line 1814, in fetch_metadata
superset_app | new_columns = self.external_metadata()
superset_app | File "/app/superset/connectors/sqla/models.py", line 1377, in external_metadata
superset_app | return get_virtual_table_metadata(dataset=self)
superset_app | File "/app/superset/connectors/sqla/utils.py", line 110, in get_virtual_table_metadata
superset_app | raise SupersetSecurityException(
superset_app | superset.exceptions.SupersetSecurityException: Only `SELECT` statements are allowed
```
My query did not have a union, only CTEs. This suggestion from @rahmedrbx solved the problem.
> [@rahmedrbx](https://github.com/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: [#22704](https://github.com/apache/superset/issues/22704)
>
> p.s. none of the things that have to do with TALISMAN or CSRF tokens helped.
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] WITH Clause with UNION could not be saved as dataset [superset]
Posted by "aleksei-perepelov (via GitHub)" <gi...@apache.org>.
aleksei-perepelov commented on issue #25659:
URL: https://github.com/apache/superset/issues/25659#issuecomment-2463372279
@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
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] WITH Clause with UNION could not be saved as dataset [superset]
Posted by "rusackas (via GitHub)" <gi...@apache.org>.
rusackas commented on issue #25659:
URL: https://github.com/apache/superset/issues/25659#issuecomment-4043438466
This may overlap with #22704 (SQL parsing/validation errors with complex queries), but the most recent comment suggests it may be distinct — the issue is specifically with `WITH ... AS` containing `UNION` failing at dataset save time, not at query execution time.
Before investing time in a fix, it would help to:
1. Confirm whether this still occurs on Superset 5.x (note: 3.0.0 is no longer supported)
2. Clarify whether the failure is during SQL parsing/validation (same root cause as #22704) or in a different code path
If you can reproduce on 5.x, please comment with the exact error message from the server logs.
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] WITH Clause with UNION could not be saved as dataset [superset]
Posted by "rusackas (via GitHub)" <gi...@apache.org>.
rusackas commented on issue #25659:
URL: https://github.com/apache/superset/issues/25659#issuecomment-2259388742
Sadly, this is at risk of being closed as stale if nobody has interest in either reproducing or fixing the issue.
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] WITH Clause with UNION could not be saved as dataset [superset]
Posted by "rusackas (via GitHub)" <gi...@apache.org>.
rusackas commented on issue #25659:
URL: https://github.com/apache/superset/issues/25659#issuecomment-1990096270
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.
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] WITH Clause with UNION could not be saved as dataset [superset]
Posted by "rahmedrbx (via GitHub)" <gi...@apache.org>.
rahmedrbx commented on issue #25659:
URL: https://github.com/apache/superset/issues/25659#issuecomment-2408268501
Can confirm that we are hitting a similar issue with a different DB (trino in our case)
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] WITH Clause with UNION could not be saved as dataset [superset]
Posted by "gulldan (via GitHub)" <gi...@apache.org>.
gulldan commented on issue #25659:
URL: https://github.com/apache/superset/issues/25659#issuecomment-2590078204
same with clickhouse
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] WITH Clause with UNION could not be saved as dataset [superset]
Posted by "Makarov-AA (via GitHub)" <gi...@apache.org>.
Makarov-AA commented on issue #25659:
URL: https://github.com/apache/superset/issues/25659#issuecomment-3248343017
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
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] WITH Clause with UNION could not be saved as dataset [superset]
Posted by "rusackas (via GitHub)" <gi...@apache.org>.
rusackas closed issue #25659: WITH Clause with UNION could not be saved as dataset
URL: https://github.com/apache/superset/issues/25659
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org