Filtering by char column doesn't work correctly in Postgres_scanner

[osawyerr]

What happens?

When filtering by a char column the query doesn't return correct results if the filter is not pushed down to Postgres. For example in the customer table of TPC-H, the c_mktsegment is usually a char(10).

select count(*), c_mktsegment from customer where c_mktsegment = 'BUILDING' group by c_mktsegment;

doesn't return any results when the filter is not pushed down to Postgres. However when the filter is pushed down to Postgres, the query returns the correct result. I believe its because Postgres ignores the blank space padding of the column but duckdb does not.

Similarly, if the query is written explicitly with the blank spaces padding, and the filter isn't pushed down, then this also works OK.

select count(*), c_mktsegment from customer where c_mktsegment = 'BUILDING ' group by c_mktsegment;

To Reproduce

• Create the TPC-H customer table in postgres and populate it with data of any scale. Note that the c_mktsegment is a char(10)

create table tpch.customer
(
    c_custkey    integer        not null,
    c_name       varchar(25)    not null,
    c_address    varchar(40)    not null,
    c_nationkey  integer        not null,
    c_phone      char(15)       not null,
    c_acctbal    numeric(15, 2) not null,
    c_mktsegment char(10)       not null,
    c_comment    varchar(117)   not null
);

• Link duckdb to the postgres schema and don't enable filter pushdown

CALL postgres_attach('host=localhost port=28815 dbname=tpch', source_schema='tpch', filter_pushdown=false, overwrite=true);

• Run the query below, you'll see that no results are returned
  select count(*), c_mktsegment from customer where c_mktsegment = 'BUILDING' group by c_mktsegment;

OS:

MacOS

PostgreSQL Version:

15

DuckDB Version:

0.6.1

DuckDB Client:

CLI

Full Name:

Olo Sawyerr

Affiliation:

None. Hobby projects.

Have you tried this on the latest master branch?

• I agree

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

• I agree

[Mytherin]

Thanks for the report! This should be fixed now in #111.