Description
What happened?
Adding a select in a joined pipeline after a few columns have been derived results in CTEs referecing invalid columns
PRQL input
from (s"SELECT a,b,c,d FROM my_table")
join side:left (
s"SELECT e,f,g FROM other_table"
derive {
my_concatenated_col = f"{col} {other_col}"
}
group {my_concatenated_col} (aggregate { my_sum = first this.`my_concatenated_col`})
sort {this.my_concatenated_col, my_sum}
derive {new_name = my_sum}
select {this.my_concatenated_col, this.new_name}
) (this.my_concatenated_col == that.my_concatenated_col)SQL output
WITH table_0 AS (
SELECT
a,
b,
c,
d
FROM
my_table
),
table_2 AS (
SELECT
e,
f,
g
FROM
other_table
),
table_3 AS (
SELECT
CONCAT(col, ' ', other_col) AS my_concatenated_col,
FIRST_VALUE(my_concatenated_col) AS _expr_0
FROM
table_2
GROUP BY
CONCAT(col, ' ', other_col)
),
table_4 AS (
SELECT
my_concatenated_col,
_expr_0 AS new_name,
_expr_0
FROM
table_3
),
table_1 AS (
SELECT
my_concatenated_col,
new_name,
CONCAT(col, ' ', other_col) AS my_concatenated_col,
FIRST_VALUE(my_concatenated_col) AS _expr_0
FROM
table_4
)
SELECT
table_0.*,
table_1.my_concatenated_col,
table_1.new_name
FROM
table_0
LEFT JOIN table_1 ON table_0.my_concatenated_col = table_1.my_concatenated_col
-- Generated by PRQL compiler version:0.13.4 (https://prql-lang.org)Expected SQL output
MVCE confirmation
- Minimal example
- New issue
Anything else?
table_1 should not exist at all: table_4 already does the selection work, and my_concatenated_col has already been defined.
The following changes result in valid SQL being generate:
- Removing the
derive {new_name = my_sum}line andselectingthis.my_sumbelow - Removing the
selectline altogether - Removing the
groupline