PostgreSQL CTEs auxiliary statements cannot reference each other

[lnhrdt]

SQLDelight Version

2.0.0

SQLDelight Dialect

PostgreSQL

Describe the Bug

The PostgreSQL docs on Common Table Expressions show that auxiliary statements can reference each other. Here's the example they provide:

WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
), top_regions AS (
    SELECT region
    FROM regional_sales
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

In this example the output of regional_sales is used in top_regions. However when building PostgreSQL queries in SQLDelight, this technique fails with a No table found with name regional_sales error.

It does work to reference the auxiliary statements in the main command (i.e. the SELECT region FROM top_regions bit in the example above), just not within other auxiliary statements.

Stacktrace

No response

[griffio]

@lnhrdt

With 2.0 - 🍔 I did try with the given example (as below) and does generate.

The generated queries can insert into orders and select totals.

Is there something I am missing from your case above?

CREATE TABLE orders(
region TEXT,
product TEXT,
amount INTEGER,
quantity INTEGER
);

insertOrders:
INSERT INTO orders (region, product, amount, quantity) VALUES (?, ?, ?, ?);

selectRegionalSales:
WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
), top_regions AS (
    SELECT region
    FROM regional_sales
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

[lnhrdt]

@griffio thanks for taking a look!

Perhaps the issue I'm having is more nuanced than just any references between any CTEs auxiliary statements. I made a repo demonstrating my issue for you.

Here's an example of the type of query that I'm unable to write.

In the repo pizzaCreate is commented out so that the example compiles.

I also included a main function that uses the example query. In the commit I swapped pizzaCreate for pizzaCreateWorkaround to demonstrate the non-CTE workaround I'm using and so that it compiles.

[griffio]

🥼 I have reproduced a simplifed test case

The example is using a CTE to create 3 rows in 3 tables that share the same key, a gen_random_uuid is generated and the row is referenced by the other auxiliary expressions. (Cute trick 😺 )

Looks like it doesn't currently work as the CTE inserted_pizza is being referenced from an INSERT statement not a SELECT (that appears to work).

Also - in the example above the TIMEZONE function is not supported by SqlDelight PostgreSql dialect (that can be added).

Error is from sql-psi TableNameMixin

I will have to see if there is a solution 🔬

(8, 50): No table found with name inserted_pizza
(11, 32): No table found with name inserted_pizza
01 pizzaCreate:
02    WITH inserted_pizza AS (
03    INSERT INTO pizza(id, created_at, name)
04    VALUES (gen_random_uuid(), NOW(), :name)
05    RETURNING *
06    ), inserted_spec AS (
07     INSERT INTO pizzaSpecs(id, diameter_inches, weight_pounds)
08     SELECT id, :diameter_inches, :weight_pounds FROM inserted_pizza
                                                        ^^^^^^^^^^^^^^
09    ), inserted_sauce AS (
10        INSERT INTO pizzaSauce(id, name)
11        SELECT id, :sauce_name FROM inserted_pizza
                                      ^^^^^^^^^^^^^^
12    )
13    SELECT * FROM inserted_pizza

[lnhrdt]

Looks like it doesn't currently work as the CTE inserted_pizza is being referenced from an INSERT statement not a SELECT (that appears to work).

Aha! Good catch.

Also - in the example above the TIMEZONE function is not supported by SqlDelight PostgreSql dialect (that can be added).

Oh wow. It appears to compile with SQLDelight and I'm using that in production. Perhaps it's failing silently or something. I've been using that syntax to ensure UTC regardless of system timezone because other syntaxes (e.g. NOW() AT TIME ZONE 'UTC') don't compile with SQLDelight. I'll keep an eye out for updates.

Cute trick 😺

Thanks 💁🏻‍♂️

I will have to see if there is a solution 🔬

Thanks @griffio!