You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@superset.apache.org by GitBox <gi...@apache.org> on 2022/10/06 09:10:17 UTC
[GitHub] [superset] brian-bh opened a new issue, #21715: with recursive CTE works on SQL Lab, but not on making chart with it
brian-bh opened a new issue, #21715:
URL: https://github.com/apache/superset/issues/21715
A clear and concise description of what the bug is.
#### How to reproduce the bug
I am using databases on Amazon Redshift (and PostgreSQL just in case).
For the purpose of ordering data to date, I was using with recursive CTE in many queries.
The problem is: recursive CTE works well in SQL Lab, but not in making Charts.
### Expected results
Making chart also works well with recursive CTE.
### Actual results
Chart query gives me an error: "Error: Recursive CTE in subquery are not supported."
#### Screenshots
(This is a partial query from my company, so I am masking some not important values and schema name)
### Environment
- browser type and version: Chromium based browsers, spotted on Google Chrome
- superset version: `superset version`: 2.0.0
- on docker non-dev composed settings;
### Checklist
Make sure to follow these steps before submitting your issue - thank you!
- [V] I have checked the superset logs for python stacktraces and included it here as text if there are any.
- [V] I have reproduced the issue with at least the latest released version of superset.
- [V] I have checked the issue tracker for the same issue and I haven't found one similar.
### Additional context
Add any other context about the problem here.
--
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
[GitHub] [superset] brian-bh commented on issue #21715: with recursive CTE works on SQL Lab, but not on making chart with it
Posted by GitBox <gi...@apache.org>.
brian-bh commented on issue #21715:
URL: https://github.com/apache/superset/issues/21715#issuecomment-1274069787
@mayurnewase thanks for your reply.
I tried as you say so (modified VALUES to SELECT because it gives me an error that there's no function name VALUES);
and voila, it gives me same error that I reported in SQLLab.
and the error is not only in redshift but also in postgresql, and I think that's natural in psycopg2.
but the thing is, you said that this query
`WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 2 ) SELECT sum(n) FROM t;`
succeed in making charts, but I get the same error as I reported.
So, I think in making Charts, the whole query becomes subquery that named virtual_table, and that's the problem.
In this case, there's no option to make charts from queries that has recursive CTE and so?
--
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
[GitHub] [superset] brian-bh commented on issue #21715: with recursive CTE works on SQL Lab, but not on making chart with it
Posted by GitBox <gi...@apache.org>.
brian-bh commented on issue #21715:
URL: https://github.com/apache/superset/issues/21715#issuecomment-1274091258
yep, now that's an inevitable conclusion... thank you for looking onto this issue. I will close this thread.
--
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
[GitHub] [superset] mayurnewase commented on issue #21715: with recursive CTE works on SQL Lab, but not on making chart with it
Posted by GitBox <gi...@apache.org>.
mayurnewase commented on issue #21715:
URL: https://github.com/apache/superset/issues/21715#issuecomment-1274061703
Looks like redshift doesn't support recursive CTE in subquery, and this happens because chart exploration needs to wrap raw sql queries in a subquery and have control over it for columns and metrics.
I tested below with postgres works fine even on plotting chat
`WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n+1 FROM t WHERE n < 2
)
SELECT sum(n) FROM t;`
can you test this query in **sqllab**
`SELECT sum
FROM
(WITH RECURSIVE t(n) AS
(
VALUES (1)
UNION ALL SELECT n+1
FROM t
WHERE n < 2 ) SELECT sum(n)
FROM t) AS virtual_table
LIMIT 1000;`
--
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
[GitHub] [superset] mayurnewase commented on issue #21715: with recursive CTE works on SQL Lab, but not on making chart with it
Posted by GitBox <gi...@apache.org>.
mayurnewase commented on issue #21715:
URL: https://github.com/apache/superset/issues/21715#issuecomment-1274090240
yeah with redshift you could convert recursive CTE to subqury or create temporary table/view in db and query on that.
don't think we could do anything to fix from superset.
--
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
[GitHub] [superset] brian-bh closed issue #21715: with recursive CTE works on SQL Lab, but not on making chart with it
Posted by GitBox <gi...@apache.org>.
brian-bh closed issue #21715: with recursive CTE works on SQL Lab, but not on making chart with it
URL: https://github.com/apache/superset/issues/21715
--
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