columns not in aggregate function and `group by`

[YuxiLing]

What happened?

PRQL compiler version:0.9.2

I found some related discussions in #2057. But some databases (such as Postgres, DuckDB, and Clickhouse) do not support a select clause to include a column that is not used in an aggregate function without a group by clause.

Error message from duckdb: Binder Error: column c0 must appear in the GROUP BY clause or be used in an aggregate function. Either add it to the GROUP BY list, or use "ANY_VALUE(c0)" if the exact value of "c0" is not important.

PRQL input

prql target:sql.duckdb
from tb1
sort {c0}
aggregate {new_col = min c1}

SQL output

WITH table_0 AS (
  SELECT
    MIN(c1) AS new_col,
    c0
  FROM
    tb1
)
SELECT
  new_col
FROM
  table_0

Expected SQL output

WITH table_0 AS (
  SELECT
    MIN(c1) AS new_col
  FROM
    tb1
)
SELECT
  new_col
FROM
  table_0

MVCE confirmation

• Minimal example
• New issue

Anything else?

No response

[max-sixty]

Great bug!

aggregate should reset the sort — which it does — but it still retains the c0 column, which isn't needed any longer...

[max-sixty]

some databases (such as Postgres, DuckDB, and Clickhouse) do not support a select clause to include a column that is not used in an aggregate function without a group by clause.

Yes, I think this is v reasonable behavior...