View or Table Creation Runtime Error

[shauryauppal]

What happens?

Facing runtime error: "RuntimeError: Parser Error: Expected a single SELECT statement"

It is clearly visible in the screenshot below that there is only one SELECT statement in the query then why is this error showing up. I am on duckdb 0.3.0

To Reproduce

Create a Table view T1 then apply some operation on T1 then create a table view of processed_T1 to get the resultant T2 view.
T2 view over-processed T1 view will fail.

Environment (please complete the following information)

• OS: Mac
• DuckDB Version: 0.3.0
• DuckDB Client: Python

[shauryauppal]

Same Error Message when I try to CREATE TABLE or CREATE VIEW

[Mytherin]

con.query creates a Relation node from a select statement (equivalent to a subquery node), it’s not intended for running arbitrary SQL statements. The error message there should be clearer. You are probably looking for con.execute.

[shauryauppal]

" SELECT * from product_view_tbl where session_event_ct!=-1" Query as such is working but CREATE TABLE over that query is giving Runtimeerror.

[shauryauppal]

con.execute vs con.query was really helpful.

I had one more question check the below screenshot. Here the first method fails why? Ideally, it should work.

@Mytherin

[Mytherin]

CTEs in views are not supported, and do not appear to be supported in most systems (e.g. this also doesn't appear to work in Postgres, MySQL or SQLite). Feel free to open a feature request for this.

[github-actions]

This issue is stale because it has been open 90 days with no activity. Remove stale label or comment or this will be closed in 30 days.

[github-actions]

This issue was closed because it has been stale for 30 days with no activity.