What happens?

You can use an IN clause to specify the values to make into columns. If you do this with a boolean expression, you get a strange error:
Error: Parser Error: PIVOT IN list must contain columns or lists of columns

This is a somewhat odd thing to want to do, but the IN clause does tell the database that columns should be created for both values. Otherwise, columns are created only for the values present.

To Reproduce

Run the following SQL:
-- Example data from documentation for PIVOT
CREATE TABLE Cities(Country VARCHAR, Name VARCHAR, Year INT, Population INT);
INSERT INTO Cities VALUES ('NL', 'Amsterdam', 2000, 1005);
INSERT INTO Cities VALUES ('NL', 'Amsterdam', 2010, 1065);
INSERT INTO Cities VALUES ('NL', 'Amsterdam', 2020, 1158);
INSERT INTO Cities VALUES ('US', 'Seattle', 2000, 564);
INSERT INTO Cities VALUES ('US', 'Seattle', 2010, 608);
INSERT INTO Cities VALUES ('US', 'Seattle', 2020, 738);
INSERT INTO Cities VALUES ('US', 'New York City', 2000, 8015);
INSERT INTO Cities VALUES ('US', 'New York City', 2010, 8175);
INSERT INTO Cities VALUES ('US', 'New York City', 2020, 8772);
-- PIVOT on string column with IN clause works
pivot cities on Country in ('NL','US') using avg(Population) group by name;
-- PIVOT on boolean expression without IN clause works
pivot cities on (Country='NL') using avg(Population) group by name;
-- PIVOT on boolean expression with IN clause does not work
pivot cities on (Country='NL') in (false, true) using avg(Population) group by name;

OS:

Windows amd64

DuckDB Version:

0.8.2 main

DuckDB Client:

CLI

Full Name:

Dan Cory

Affiliation:

self

Have you tried this on the latest master branch?

I have tested with a master build

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• Yes, I have