CTE reference misinterpreted as recursive CTE · Issue #9187 · duckdb/duckdb

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement . We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

What happens?

I'm migrating a system from Postgres to Duck, the following simplified query works in Postgres but fails in DuckDB. For some reason it's interpreting that as a recursive CTE.

CREATE TABLE people (
    name text
WITH people AS (SELECT * FROM people),
people_cte AS (SELECT * FROM people)
SELECT * FROM people_cte

Error

java.sql.SQLException: Binder Error: Circular reference to CTE \"people\", use WITH RECURSIVE to use recursive CTEs;
To Reproduce
Just run the above query.
Apple Silicon - ARM
DuckDB Version:
0.8.1
DuckDB Client:
Full Name:
Ricardo Mayerhofer
Affiliation:
Hopara
Have you tried this on the latest main branch?
I have tested with a main build
Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?
 Yes, I have
          @ricardoekm Thanks for reporting!
When reproducing via CLI, I get a more detailed error message with two possible suggestions:
D WITH people AS (SELECT * FROM people),
> people_cte AS (SELECT * FROM people)
> SELECT * FROM people_cte
Error: Binder Error: Circular reference to CTE "people", There are two possible solutions.
1. use WITH RECURSIVE to use recursive CTEs.
2. If you want to use the TABLE name "people" the same as the CTE name, please explicitly add "SCHEMA" before table name. You can try "main.people" (main is the duckdb default schema)
That said, let me cc @kryonix on this issue who may have more to say.
          Thanks @szarnyasg! An additional input, the following query works fine. The problem seem to be when adding more CTEs to the mix.
WITH people AS (SELECT * FROM people)
SELECT * FROM people
          This is a binder issue. The CTE name people takes precedence over the table people, instead of using the table as a fallback like PostgreSQL would do. As already mentioned, main.people fixes this for version 0.9. But maybe we can do a better job handling such cases during the binding phase. I will have a look at it.