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
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?
[x] 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?
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
customer
table in postgres and populate it with data of any scale. Note that the c_mktsegment is a char(10)CALL postgres_attach('host=localhost port=28815 dbname=tpch', source_schema='tpch', filter_pushdown=false, overwrite=true);
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?Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?