Pivot with IN clause doesn't work for a boolean column

[DanCory]

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

[DanCory]

I wondered if it was all uses of IN with boolean, but
select * from cities where (Country='NL') in (true)
works fine

[orzom411]

You could also employ your IN as a values list, e.g.:

pivot cities on (Country='NL')::bool in (values( 1::bool ),(0::bool)) using avg(Population) group by name;

Results:

name|true|false
Amsterdam|1076.0|
Seattle||636.6666666666666
New York City||8320.666666666666

This also works, it assumes the result:

pivot cities on (Country='NL') in (values( 1::bool ),(0::bool)) using avg(Population) group by name;

Rule of thumb I like to use when reading error messages is the hint, list must contain columns... oh, yah, an array isn't column(s), hence "values" addresses the issue.

Hope this helped.

[DanCory]

Thanks for the suggestion. This works too:
pivot cities on (Country='NL') in (values( true),(false)) using avg(Population) group by name;

[DanCory]

Still repros in 0.9.0