Querying View of a View Crashes

[pmh722]

What happens?

DuckDB crashes with "Segmentation fault: 11" when I try to query a view of a view

To Reproduce

# This is all fine
con = duckdb.connect(database='test.db')
con.execute("CREATE TABLE IF NOT EXISTS test (val INTEGER);")
con.execute("INSERT INTO test(val) VALUES (1), (2), (3)")
con.execute("CREATE OR REPLACE VIEW foo AS (SELECT * FROM test)")
con.execute("CREATE OR REPLACE VIEW foo AS (SELECT * FROM foo)")

# This crashes
con.execute("SELECT * FROM foo")

Environment (please complete the following information):

The reproduction above crashes on Google Colab

[Alex-Monahan]

It looks like that second view has the same name as the prior view (both are foo). It shouldn't segfault for sure, but that should get it working for you in the short term!

[pmh722]

It looks like that second view has the same name as the prior view (both are foo). It shouldn't segfault for sure, but that should get it working for you in the short term!

Thanks for the prompt reply! And thanks for DuckDB, I really do like it.

Yes I have it working with multiple view names. But I like the ability to incrementally build up a view, a few columns at a time, referencing previous columns by name. That experience mimics the favorable developer ergonomics of a Pandas DF. Having to name each table foo1, foo2, foo3 etc, incrementing as I add each column, is tedious and error prone.

[Alex-Monahan]

Hmm, I don't know of any SQL database that supports that exact kind of incremental view building with the exact same name. CTE's (Common Table Expressions) are another way to build incrementally (but each must be named differently).

One more approach that will be slightly slower in performance, but maybe more convenient, is to execute each query and retrieve the results as the same Pandas DF, and have the next query point at that same DF. For example:

my_df = duckdb_conn.execute("SELECT * from test").fetchdf()
my_df = duckdb_conn.execute("SELECT * from my_df").fetchdf()
my_df = duckdb_conn.execute("SELECT * from my_df").fetchdf()

Also, have you had a look at our Python Relational API?

[hannes]

I can confirm this segfaults

[pmh722]

Hmm, I don't know of any SQL database that supports that exact kind of incremental view building with the exact same name.

Indeed. But DuckDB is forging a brave new path, bringing the power of databases to the data scientist workflow! There is a reason pandas has become hugely popular for data wrangling, and its api is entirely centered on incrementally building up a DF, one column at a time.

Actually the best solution may be to let us reference column aliases from within the same SELECT clause that defines them. That would be a super powerful feature.

Saying all this as someone who is cheering for the DuckDB project to succeed, and take market share from DataFrames, but who has extensively used DataFrames and understands why they have a more appealing API than conventional SQL…

[Alex-Monahan]

Thanks for being on team DuckDB!

Referring to previous aliases in the Select clause was requested previously in #1547. We agree that would be a great feature! We are open to a PR if you would like to give it a try!