PIVOT USING aggregate does not respect FILTER

[smilingthax]

What happens?

PIVOT allows arbitrary aggregate expressions in theUSING clause, but neither warns/errors, nor respects all given parts, esp. FILTER is ignored silently.

To Reproduce

Consider:

SELECT name, count(value) FILTER (value = 3) FROM (VALUES ('x', 2), ('y', 3), ('y', 4)) t(name,value) GROUP BY 1;

┌──────┬─────────────────────────────────────────────┐
│ name ┆ count("value") FILTER (WHERE ("value" = 3)) │
╞══════╪═════════════════════════════════════════════╡
│ x    ┆                                           0 │
│ y    ┆                                           1 │
└──────┴─────────────────────────────────────────────┘

Now, using PIVOT:

PIVOT (VALUES ('x', 2), ('y', 3), ('y', 4)) t(name, value) ON name USING count(value) FILTER (value = 3);

┌───┬───┐
│ x ┆ y │
╞═══╪═══╡
│ 1 ┆ 2 │
└───┴───┘

This would be correct without the FILTER clause, but with it, one would expect 0 | 1.

Unfortunately, duckdb does not warn about this (or forbid the syntax), neither in the cli, nor in the PIVOT documentation.

Not only the Simplified PIVOTSyntax is affected, but also the SQL Standard Syntax:

FROM (VALUES ('x', 2), ('y', 3), ('y', 4)) t(name, value) PIVOT (count(value) FILTER (value = 3) FOR name IN ('x', 'y'));

OS:

e.g. wasm

DuckDB Version:

v0.10.2, @duckdb/duckdb-wasm@1.28.1-dev181.0

DuckDB Client:

http://shell.duckdb.org

Full Name:

Tobias Hoffmann

Affiliation:

cueconcept

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a nightly build

Did you include all relevant data sets for reproducing the issue?

Yes

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• Yes, I have