bug(geospatial): casting of literals preventing filter push down to parquet reader

[ncclementi]

When reading parquet like in this example:

url = "s3://overturemaps-us-west-2/release/2024-06-13-beta.1/theme=buildings/type=*/*"
t2 = con.read_parquet(url, table_name="buildings",
                 **{"filename": True, 
                   "hive_partitioning": 1}).select(
                    _.id,
                    _.height,
                    _.geometry,
                    _.bbox,
                    primary_names=_.names.primary).filter(
                    _.primary_names.notnull(), 
                    _.bbox.xmin > -84.36,
                    _.bbox.xmax < -82.42,
                    _.bbox.ymin > 41.71,
                    _.bbox.ymax < 43.33).limit(10)

We get a phyiscal plan that shows that the optimization of pushing down the filter to the parquet reader is not happening:

>>> print(con.raw_sql(f"EXPLAIN {ibis.to_sql(t)}").fetchone()[1])
┌───────────────────────────┐
│         PROJECTION        │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│             id            │
│           height          │
│          geometry         │
│            bbox           │
│       primary_names       │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│         PROJECTION        │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│             id            │
│           height          │
│          geometry         │
│            bbox           │
│       primary_names       │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│           LIMIT           │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│           FILTER          │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│  ((struct_extract(names,  │
│'primary') IS NOT NULL...  │
│(CAST(struct_extract(bbox, │
│'ymax') AS DOUBLE) < 43.33)│
│  AND (CAST(struct_extract │
│(bbox, 'ymin') AS DOUB...  │
│       .71) AND (CAST      │
│(struct_extract(bbox, ...  │
│    < -82.42) AND (CAST    │
│(struct_extract(bbox, ...  │
│     DOUBLE) > -84.36))    │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│       EC: 484889332       │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│       READ_PARQUET        │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│             id            │
│          geometry         │
│            bbox           │
│           names           │
│           height          │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│       EC: 2424446660      │
└───────────────────────────┘  

However, if we do pass the values as literals explicitly, it will perform the optimization as expected.

url = "s3://overturemaps-us-west-2/release/2024-06-13-beta.1/theme=buildings/type=*/*"
t = con.read_parquet(url, table_name="buildings",
                 **{"filename": True, 
                   "hive_partitioning": 1}).select(
                    _.id,
                    _.height,
                    _.geometry,
                    _.bbox,
                    primary_names=_.names.primary).filter(
                    _.primary_names.notnull(), 
                    _.bbox.xmin > ibis.literal(-84.36, "float32"),
                    _.bbox.xmax < ibis.literal(-82.42, "float32"),
                    _.bbox.ymin > ibis.literal(41.71, "float32"),
                    _.bbox.ymax < ibis.literal(43.33, "float32")).limit(10)

>>> print(con.raw_sql(f"EXPLAIN {ibis.to_sql(t)}").fetchone()[1])
┌───────────────────────────┐
│         PROJECTION        │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│             id            │
│           height          │
│          geometry         │
│            bbox           │
│       primary_names       │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│         PROJECTION        │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│             id            │
│           height          │
│          geometry         │
│            bbox           │
│       primary_names       │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│           LIMIT           │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│           FILTER          │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│  (struct_extract(names,   │
│  'primary') IS NOT NULL)  │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│        EC: 96977866       │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│       READ_PARQUET        │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│             id            │
│          geometry         │
│            bbox           │
│           names           │
│           height          │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│  Filters: bbox.ymax<43.33 │
│ AND bbox.ymax IS NOT ...  │
│  bbox.ymin>41.71 AND bbox │
│ .ymin IS NOT NULL AND bbox│
│.xmax<-82.42 AND bbox....  │
│ NOT NULL AND bbox.xmin>-84│
│  .36 AND bbox.xmin IS NOT │
│            NULL           │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│       EC: 484889332       │
└───────────────────────────┘  

Notice that if we run the duckdb raw sql and check the physical plan, we get:

>>> sql = """SELECT
      id,
      names.primary as primary_name,
      height,
      geometry
    FROM read_parquet('s3://overturemaps-us-west-2/release/2024-06-13-beta.1/theme=buildings/type=*/*', filename=true, hive_partitioning=1)
    WHERE primary_name IS NOT NULL
    AND bbox.xmin > -84.36
    AND bbox.xmax < -82.42
    AND bbox.ymin > 41.71
    AND bbox.ymax < 43.33;
"""
>>> print(con.raw_sql(f"EXPLAIN {sql}").fetchone()[1])
┌───────────────────────────┐
│         PROJECTION        │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│             id            │
│        primary_name       │
│           height          │
│          geometry         │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│           FILTER          │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│  (struct_extract(names,   │
│  'primary') IS NOT NULL)  │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│        EC: 96977866       │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│       READ_PARQUET        │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│           names           │
│             id            │
│           height          │
│          geometry         │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│ Filters: bbox.xmin>-84.36 │
│  AND bbox.xmin IS NOT NULL│
│ AND bbox.xmax<-82.42 ...  │
│ .xmax IS NOT NULL AND bbox│
│.ymin>41.71 AND bbox.y...  │
│  NOT NULL AND bbox.ymax<43│
│  .33 AND bbox.ymax IS NOT │
│            NULL           │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│       EC: 484889332       │
└───────────────────────────┘