Feature Request: Filter Pushdown for IN operator on Parquet Files

[MPizzotti]

Why do you want this feature?

I want this feature, for the following scenario:

Imagine that we have a parquet file with dimensions and facts columns:
ex:
dimension columns: store_code, store_region, store_nation .....
facts columns: net_sales_current_year, net_sales_previous_year....

now, we want to create a pretty UI where the user can run some small BI analysis work on top of the mentioned parquet file.

for simple queries the filter pushdown on the parquet file works fantastically.

but for a complex scenario (like this query):

SELECT * FROM {parquet} WHERE store_code IN ('k1', 'k3', 'k999')

the query is unable to pushdown the filter, NOT ONLY THE "store_code" FILTER, but the entire filtering condition,

with this problem, the query intead of taking 2 seconds is taking almost 4 minutes:

EXPLAIN ANALYZE WITHOUT IN:

│┌───────────────────────────────────┐│
││    Query Profiling Information    ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
explain analyze {redacted_query}
     FROM
			{redacted_parquet}
	WHERE
       storegroup = 7 AND dategroup = 2047 -- these are hive_partitioned (file filter pushdown)
	   AND store_code = 'k1'
 )
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││         Total Time: 1.24s         ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
┌───────────────────────────┐
│      RESULT_COLLECTOR     │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│             0             │
│          (0.00s)          │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│      EXPLAIN_ANALYZE      │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│             0             │
│          (0.00s)          │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│         PROJECTION        │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│        {redacted}         │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│       READ_PARQUET        │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│        {redacted}         │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│ Filters: store_grouping=k1│
│ AND store_grouping IS│
│          NOT NULL         │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│    File Filters: (CAST    │
│(storegroup AS INTEGER...  │
│(dategroup AS INTEGER)...  │
│             )             │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│         EC: 77060         │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│            898            │
│          (1.76s)          │
└───────────────────────────┘                             

SAME QUERY, WHERE I USE 'IN' OPERATOR FOR GETTING 2 STORE CODES:

┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││    Query Profiling Information    ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
explain analyze SELECT *
     FROM (
     SELECT {redacted_select}
     FROM
        {redacted_parquet 
		 WHERE
         storegroup = 7 AND dategroup = 2047
		 AND store_grouping IN ('K1, 'K2')
 )
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││        Total Time: 251.70s        ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
┌───────────────────────────┐
│      RESULT_COLLECTOR     │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│             0             │
│          (0.00s)          │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│      EXPLAIN_ANALYZE      │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│             0             │
│          (0.00s)          │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│         PROJECTION        │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│        {redacted}         │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│           FILTER          │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│((store_grouping = 'K1'│
│ ) OR ((store_grouping = 'K│
│2') AND (CAST(stor...  │
│ AS INTEGER) = 7) AND (CAST│
│  (dategroup AS INTEGER) = │
│           2047)))         │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│        EC: 4623636        │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│            2192           │
│          (0.33s)          │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│       READ_PARQUET        │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│        {redacted}         │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│        EC: 23118180       │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│          18226396         │
│         (2010.30s)        │
└───────────────────────────┘                             

looking at the analyze query, duckdb is loading the entire dataset, MOREOVER, is not even running the file filter pushdown, on the 2 columns used inside the hive partitioning