Polars Lazy Filter not working with latest Polars (1.34.0)

[paultiq]

What happens?

A Polars test is failing against the most recent version of Polars (1.34.0). tests\fast\arrow\test_polars.py::TestPolars::test_polars_lazy_pushdown_numeric[HUGEINT]

I boiled this down to an MRE below.

I'm assuming this is related to pola-rs/polars#24346, but don't see a reason... and didn't see anything obviously wrong in polars_io.py.

Notes:

• Fails when the data type is either Decimal(38,0) or HUGEINT.
• Passes when lazy=False
• Fails on Polars 1.34.0, but passes on 1.33.1

To Reproduce

import duckdb
import polars as pl

print(f"Polars version: {pl.__version__}")
print(f"duckdb version: {duckdb.__version__}")

con = duckdb.connect()
rel = con.sql("select a::HUGEINT as a FROM range(10) as t(a)")

lazy_df = rel.pl(lazy=True)
print(f"Schema: {lazy_df.collect_schema()}")
result1 = lazy_df.filter(pl.col("a") == 1).collect()
assert len(result1) == 1, f"Expected 1 row, got {len(result1)}"

OS:

Linux

DuckDB Package Version:

1.5.0_

Python Version:

3.13

Full Name:

Paul T

Affiliation:

Iqmo

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a source build

Did you include all relevant data sets for reproducing the issue?

Yes

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration to reproduce the issue?

• Yes, I have

[evertlammerts]

Yup, generating a polars df with a hugeint has been failing because of this since yesterday. I've glanced at it for a little while this afternoon but didn't have time to dig deeper yet. It's a bit hacky.

We create the df by first creating an arrow schema based on the duckdb schema, creating an empty arrow table based on that schema, then converting that table to a polars dataframe, extract its schema, and use that to create the actual dataframe. In other words: duckdb types -> arrow types -> polars types. I see that arrow creates a 128bit decimal for a hugeint (d:38,0 -> 38 precision, scale 0), and it looks like polars now (?) turns that into something that has rounding errors, which means that pl.col("a") == 1 fails.

I'll try to get this fixed on Monday.

[paultiq]

I narrowed it down a little more:

1.34.0 returns this to polars_io.py::_pl_tree_to_sql():

Schema: Schema({'a': Decimal(precision=38, scale=0)})
{'BinaryExpr': {'left': {'Column': 'a'}, 'op': 'Eq', 'right': {'Literal': {'Scalar': {'Decimal': [1, 38, 0]}}}}}

1.33.1 returns:

Schema: Schema({'a': Decimal(precision=38, scale=0)})
tree {'BinaryExpr': {'left': {'Column': 'a'}, 'op': 'Eq', 'right': {'Literal': {'Scalar': {'Decimal': [1, 0]}}}}}

_pl_tree_to_sql converts decimal_values assuming that the 1st item is the scale... not the precision:

decimal_value = Decimal(decimal_value[0]) / Decimal(10 ** decimal_value[1])

Notes:

• I added a print(tree) at top of _pl_tree_to_sql.
• I haven't found root cause here... it might be feat: Use fixed-scale Decimals pola-rs/polars#24542, still unsure.
• I'm also not clear why/when this Decimal path exists.... it seems both fragile, and it's not used for other precisions/scales, afaict.

[paultiq]

Here's a better repro without duckdb:

import polars as pl
import json
from decimal import Decimal

print(f"Polars version: {pl.__version__}")
df = pl.DataFrame({"a": [1, 2, 3]}).with_columns(
    pl.col("a").cast(pl.Decimal(precision=38, scale=0))
)

print(f"Schema: {df.schema}")
expr = pl.col("a") == pl.lit(Decimal("1")).cast(pl.Decimal(precision=38, scale=0))
serialized = expr.meta.serialize(format="json")
tree = json.loads(serialized)
print(json.dumps(tree, indent=2))

1.34.0

Polars version: 1.34.0
Schema: Schema({'a': Decimal(precision=38, scale=0)})

Serialized expression tree:
{
  "BinaryExpr": {
    "left": {
      "Column": "a"
    },
    "op": "Eq",
    "right": {
      "Cast": {
        "expr": {
          "Literal": {
            "Scalar": {
              "Decimal": [
                1,
                38,
                0
              ]
            }
          }
        },
        "dtype": {
          "Literal": {
            "Decimal": [
              38,
              0
            ]
          }
        },
        "options": "Strict"
      }
    }
  }
}

1.33.1

Polars version: 1.33.1
Schema: Schema({'a': Decimal(precision=38, scale=0)})

Serialized expression tree:
{
  "BinaryExpr": {
    "left": {
      "Column": "a"
    },
    "op": "Eq",
    "right": {
      "Cast": {
        "expr": {
          "Literal": {
            "Scalar": {
              "Decimal": [
                1,
                0
              ]
            }
          }
        },
        "dtype": {
          "Literal": {
            "Decimal": [
              38,
              0
            ]
          }
        },
        "options": "Strict"
      }
    }
  }
}

[evertlammerts]

I think that's indeed the pr that made the change. Polars wants a scale to be set explicitly for decimals now. Interestingly, if you set the expression's scale to 20, you get:

{
  "expr": {
    "Literal": {
      "Scalar": {
        "Decimal": [
          1,
          38, // Still 38?
          0
        ]
      }
    }
  },
  "dtype": {
    "Literal": {
      "Decimal": [
        20, // This is correct
        0
      ]
    }
  },
  "options": "Strict"
}

[paultiq]

There's a lot of changes in that PR that might be related, but this stands out:
https://github.com/orlp/polars/blame/89356e5cb7076fd7c9f8998f908d83fbf5567707/py-polars/polars/datatypes/classes.py#L452-L453

Edit: I'm thinking that the precision is probably not passed down... so that before, it was None and thus excluded, and now it's forced to 38

[paultiq]

I reviewed #102 (I'm commenting here, since it's merged):

There's still an issue here with 1.34.0 with this test case even after the fix. Any precision other than 38 will throw an exception and pushdown fails.

The exception is swallowed by

duckdb-python/duckdb/polars_io.py

Lines 43 to 45 in c1ed4fa

	except Exception:
	# If the conversion fails, we return None
	return None

Notes:
* 1.33.1 doesn't throw an exception in the test below
** I tested with and without your commit.
*** I don't love that the exception is swallowed silently here. Perhaps a WARNING would be appropriate... although I'd much rather see logger.debug messages in the duckdb-python lib.

Repro:

Before running, add a print for the exception in _predicate_to_expression:

import duckdb
import polars as pl

print(f"Polars version: {pl.__version__}")
print(f"duckdb version: {duckdb.__version__}")

con = duckdb.connect()
rel = con.sql("select a::DECIMAL(37,0) as a FROM range(10) as t(a)")

lazy_df = rel.pl(lazy=True)
print(f"Schema: {lazy_df.collect_schema()}")

expr = pl.col("a") == 1
result1 = lazy_df.filter(expr).collect()
assert len(result1) == 1, f"Expected 1 row, got {len(result1)}"
print(result1)

Exception seen: Node type: Cast is not implemented. {'expr': {'Column': 'a'}, 'dtype': {'Literal': {'Decimal': [38, 0]}}, 'options': 'NonStrict'}

(edit) test.polars

test_polars does have some checks for valid_filter, but these tests don't verify that the push down actually happens. I think this test needs to be changed (or added) so that it actually verifies that the pushdown occurs... perhaps by inspecting the plan.

For example: valid_filter(pl.col("a") == 1) (from test_polars) is not the expression that'll be attempted to be pushed down.

The expression that's used is '[(col("a").cast(Decimal(38, 1))) == (1.0)]'

[evertlammerts]

Should be fixed now. Running your repro outputs:

Polars version: 1.35.2
duckdb version: 1.5.0.dev248
Schema: Schema({'a': Decimal(precision=37, scale=0)})
shape: (1, 1)
┌───────────────┐
│ a             │
│ ---           │
│ decimal[37,0] │
╞═══════════════╡
│ 1             │
└───────────────┘