bug: `CREATE VIEW` fails with "unexpected non-SELECT command in SubLink" for correlated subqueries and EXISTS

[oscarbritse]

Summary

When pg_ducklake is loaded, any CREATE VIEW statement whose query body
contains a correlated subquery in the SELECT list, an EXISTS
expression, or any other subquery-in-expression construct (ANY, ALL,
IN (SELECT ...)) crashes with:

ERROR: unexpected non-SELECT command in SubLink

The view is not created. The same query works fine as a plain SELECT.

Reproduction

-- Setup
CREATE TABLE t1 (id int PRIMARY KEY);
CREATE TABLE t2 (id int PRIMARY KEY, label text);

-- ✅ WORKS - plain SELECT with a correlated subquery is fine
SELECT t1.id, (SELECT t2.label FROM t2 WHERE t2.id = t1.id) AS label
FROM t1;

-- ✅ WORKS - plain SELECT with EXISTS is fine
SELECT t1.id FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.id = t1.id);

-- ❌ FAILS - CREATE VIEW with a correlated subquery in the SELECT list
-- ERROR: unexpected non-SELECT command in SubLink
CREATE VIEW test_correlated_subquery AS
SELECT t1.id,
       (SELECT t2.label FROM t2 WHERE t2.id = t1.id) AS label
FROM t1;

-- ❌ FAILS - CREATE VIEW with EXISTS in the WHERE clause
-- ERROR: unexpected non-SELECT command in SubLink
CREATE VIEW test_exists_view AS
SELECT t1.id
FROM t1
WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.id = t1.id);

Both subqueries contain a plain SELECT - there is nothing non-SELECT about them.

Root cause (hypothesis)

PostgreSQL represents subqueries embedded inside an expression (correlated
subqueries, EXISTS, ANY, ALL, IN (SELECT ...)) as SubLink nodes.
Each SubLink carries a subselect field that points to the already-parsed
Query struct for the inner SELECT.

pg_ducklake's DDL hook appears to handle SubLink but then checks the command
type of the contained query and raises an error when it is not what the code
expects. Because the contained query is always a SELECT (command type
CMD_SELECT), the check is incorrect and always triggers.

The error text "unexpected non-SELECT command in SubLink" suggests the code
is treating the subselect pointer as a raw parse tree node of a different
command type, possibly confusing it with a RawStmt or CreateStmt from the
outer DDL context.

Suggested fix

In the SubLink case of the DDL-hook walker:

1. Cast sublink->subselect to Query * (it is already analysed at this
   point in DDL processing).
2. Assert or check ((Query *) sublink->subselect)->commandType == CMD_SELECT
   • this is always true for a valid SubLink; remove or correct whatever
     check currently produces the error.
3. Recurse into the inner Query to discover any DuckLake table references
   inside it.

Workaround

There is no known workaround. Views that require correlated subqueries,
EXISTS, ANY, ALL, or IN (SELECT ...) cannot be created while
pg_ducklake is loaded, regardless of how the query is restructured.

The only multi-table pattern that currently succeeds is the implicit comma join
(FROM t1, t2 WHERE ...), but that pattern cannot express the filtering logic
that subqueries are used for.

Version: pg_ducklake 1.0.0, PostgreSQL 18.4

[qsliu2017]

should be fixed by #231