Skip to content

iceberg_scan: WHERE length(col) = <literal> returns no rows (regression in 1.4.2) #377

Closed as duplicate
Closed as duplicate
iceberg_scan: WHERE length(col) = <literal> returns no rows (regression in 1.4.2)#377
Labels
reproduced

Description

@lasse-schier
lasse-schier
opened on Mar 12, 2026

What happens?

Filtering on a string column with WHERE length(col) = <integer> via iceberg_scan() returns 0 rows starting in DuckDB 1.4.2. The same query returns the correct results in DuckDB 1.4.1.

The bug does not affect read_parquet() on the same underlying files. It also does not affect equivalent predicates written as WHERE length(col) > N-1 AND length(col) < N+1 or WHERE length(col) IN (N) — both of those return the correct rows via iceberg_scan() in all versions.

To Reproduce

import duckdb
import pyarrow as pa
import pyiceberg
from pyiceberg.catalog.sql import SqlCatalog
from pyiceberg.schema import Schema
from pyiceberg.types import NestedField, StringType

print(f"DuckDB version:    {duckdb.__version__}")
print(f"PyIceberg version: {pyiceberg.__version__}")
print(f"PyArrow version:   {pa.__version__}")

WAREHOUSE_PATH = "/tmp/iceberg_debug"
NAMESPACE = "ns"
TABLE_NAME = "items"

catalog = SqlCatalog(
    "local",
    uri="sqlite:///:memory:",
    warehouse=f"file://{WAREHOUSE_PATH}",
)
catalog.create_namespace_if_not_exists(NAMESPACE)

schema = Schema(NestedField(1, "item_id", StringType()))
full_table_name = f"{NAMESPACE}.{TABLE_NAME}"
if catalog.table_exists(full_table_name):
    catalog.drop_table(full_table_name)
iceberg_table = catalog.create_table(full_table_name, schema=schema)

# Two rows with length=36 (should match), one with 37 and one with 35 (should not match)
iceberg_table.append(pa.table({"item_id": [
    "10000000-0000-0000-0000-000000000001",   # length=36 ← should match
    "20000000-0000-0000-0000-000000000002",   # length=36 ← should match
    "30000000-0000-0000-0000-0000000000033",  # length=37 ← should NOT match
    "40000000-0000-0000-0000-00000000004",    # length=35 ← should NOT match
]}))

con = duckdb.connect()
con.execute("INSTALL iceberg; LOAD iceberg; SET unsafe_enable_version_guessing = true;")
SCAN = f"iceberg_scan('{iceberg_table.location()}')"

print("\n-- Query 1 (broken): equality predicate on length()")
print(con.execute(f"SELECT item_id, length(item_id) AS len FROM {SCAN} WHERE length(item_id) = 36").pl())

print("\n-- Query 2 (workaround): range predicate")
print(con.execute(f"SELECT item_id, length(item_id) AS len FROM {SCAN} WHERE length(item_id) > 35 AND length(item_id) < 37").pl())

print("\n-- Query 3 (workaround): IN predicate")
print(con.execute(f"SELECT item_id, length(item_id) AS len FROM {SCAN} WHERE length(item_id) IN (36)").pl())

parquet_glob = f"{WAREHOUSE_PATH}/{NAMESPACE}/{TABLE_NAME}/data/*.parquet"
print("\n-- Query 4 (workaround): read_parquet with same equality predicate")
print(con.execute(f"SELECT item_id, length(item_id) AS len FROM read_parquet('{parquet_glob}') WHERE length(item_id) = 36").pl())

catalog.purge_table(full_table_name)
catalog.drop_namespace(NAMESPACE)

Output for DuckDB 1.4.1:

-- Query 1 (broken): equality predicate on length()
shape: (2, 2)
┌──────────────────────────────────────┬─────┐
│ item_id                              ┆ len │
│ ---                                  ┆ --- │
│ str                                  ┆ i64 │
╞══════════════════════════════════════╪═════╡
│ 10000000-0000-0000-0000-000000000001 ┆ 36  │
│ 20000000-0000-0000-0000-000000000002 ┆ 36  │
└──────────────────────────────────────┴─────┘

-- Query 2 (workaround): range predicate
┌─────────────────────────────────┬─────┐
│ item_id                         ┆ len │
│ ---                             ┆ --- │
│ str                             ┆ i64 │
╞═════════════════════════════════╪═════╡
│ 10000000-0000-0000-0000-000000… ┆ 36  │
│ 20000000-0000-0000-0000-000000… ┆ 36  │
└─────────────────────────────────┴─────┘

-- Query 3 (workaround): IN predicate
shape: (2, 2)
┌─────────────────────────────────┬─────┐
│ item_id                         ┆ len │
│ ---                             ┆ --- │
│ str                             ┆ i64 │
╞═════════════════════════════════╪═════╡
│ 10000000-0000-0000-0000-000000… ┆ 36  │
│ 20000000-0000-0000-0000-000000… ┆ 36  │
└─────────────────────────────────┴─────┘

-- Query 4 (workaround): read_parquet with same equality predicate
shape: (2, 2)
┌─────────────────────────────────┬─────┐
│ item_id                         ┆ len │
│ ---                             ┆ --- │
│ str                             ┆ i64 │
╞═════════════════════════════════╪═════╡
│ 10000000-0000-0000-0000-000000… ┆ 36  │
│ 20000000-0000-0000-0000-000000… ┆ 36  │
└─────────────────────────────────┴─────┘

Output for DuckDB 1.4.2+ (Tested for each version up to and including 1.5.0):

-- Query 1 (broken): equality predicate on length()
shape: (0, 2)
┌─────────┬─────┐
│ item_id ┆ len │
│ ---     ┆ --- │
│ str     ┆ i64 │
╞═════════╪═════╡
└─────────┴─────┘

-- Query 2 (workaround): range predicate
shape: (2, 2)
┌─────────────────────────────────┬─────┐
│ item_id                         ┆ len │
│ ---                             ┆ --- │
│ str                             ┆ i64 │
╞═════════════════════════════════╪═════╡
│ 10000000-0000-0000-0000-000000… ┆ 36  │
│ 20000000-0000-0000-0000-000000… ┆ 36  │
└─────────────────────────────────┴─────┘

-- Query 3 (workaround): IN predicate
shape: (2, 2)
┌─────────────────────────────────┬─────┐
│ item_id                         ┆ len │
│ ---                             ┆ --- │
│ str                             ┆ i64 │
╞═════════════════════════════════╪═════╡
│ 10000000-0000-0000-0000-000000… ┆ 36  │
│ 20000000-0000-0000-0000-000000… ┆ 36  │
└─────────────────────────────────┴─────┘

-- Query 4 (workaround): read_parquet with same equality predicate
shape: (2, 2)
┌─────────────────────────────────┬─────┐
│ item_id                         ┆ len │
│ ---                             ┆ --- │
│ str                             ┆ i64 │
╞═════════════════════════════════╪═════╡
│ 10000000-0000-0000-0000-000000… ┆ 36  │
│ 20000000-0000-0000-0000-000000… ┆ 36  │
└─────────────────────────────────┴─────┘

OS:

Ubuntu 24.04 x86_64

DuckDB Package Version:

1.4.2

Python Version:

3.11

Full Name:

Lasse Schier Christiansen

Affiliation:

TV2 Danmark

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

I have tested with a stable release

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

Activity

  1. lasse-schier
    added
    needs triage
    on Mar 12, 2026
  2. evertlammerts
    added
    reproduced
    and removed
    needs triage
    on Mar 19, 2026

  3. evertlammerts commented on Mar 19, 2026

    @evertlammerts
    evertlammerts
    on Mar 19, 2026
    Member

    This is probably a bug in the iceberg extension, not in Python.

    @szarnyasg can you move this to the iceberg extension repo?

    Iceberg table creation script

    Save in repro_377_setup.py, then run with uv run --script repro_377_setup.py

    # /// script
# requires-python = ">=3.11"
# dependencies = [
#     "pyiceberg[sql-sqlite]",
#     "pyarrow",
# ]
# ///
import pyarrow as pa
from pyiceberg.catalog.sql import SqlCatalog
from pyiceberg.schema import Schema
from pyiceberg.types import NestedField, StringType

WAREHOUSE_PATH = "/tmp/repro_385_pypi/iceberg_debug"

catalog = SqlCatalog("local", uri="sqlite:///:memory:", warehouse=f"file://{WAREHOUSE_PATH}")
catalog.create_namespace_if_not_exists("ns")

schema = Schema(NestedField(1, "item_id", StringType()))
if catalog.table_exists("ns.items"):
    catalog.drop_table("ns.items")
table = catalog.create_table("ns.items", schema=schema)

table.append(pa.table({"item_id": [
    "10000000-0000-0000-0000-000000000001",   # length=36 — should match
    "20000000-0000-0000-0000-000000000002",   # length=36 — should match
    "30000000-0000-0000-0000-0000000000033",  # length=37 — should NOT match
    "40000000-0000-0000-0000-00000000004",    # length=35 — should NOT match
]}))

print(f"Iceberg table created at: {table.location()}")
    SQL script 
    -- Reproducer for https://github.com/duckdb/duckdb-python/issues/377
--
-- Prerequisites:
--   1. Create the iceberg table (requires pyiceberg):
--      uv run --script repro_377_setup.py
--   2. Run this script:
--      duckdb < repro_377_cli.sql

INSTALL iceberg;
LOAD iceberg;
SET unsafe_enable_version_guessing = true;

-- BUG: returns 0 rows (should return 2)
SELECT 'Query 1 - BROKEN: WHERE length(item_id) = 36' AS test;
SELECT item_id, length(item_id) AS len
FROM iceberg_scan('file:///tmp/repro_385_pypi/iceberg_debug/ns/items')
WHERE length(item_id) = 36;

-- Workaround 1: range predicate (returns 2 rows correctly)
SELECT 'Query 2 - OK: WHERE length(item_id) > 35 AND length(item_id) < 37' AS test;
SELECT item_id, length(item_id) AS len
FROM iceberg_scan('file:///tmp/repro_385_pypi/iceberg_debug/ns/items')
WHERE length(item_id) > 35 AND length(item_id) < 37;

-- Workaround 2: IN predicate (returns 2 rows correctly)
SELECT 'Query 3 - OK: WHERE length(item_id) IN (36)' AS test;
SELECT item_id, length(item_id) AS len
FROM iceberg_scan('file:///tmp/repro_385_pypi/iceberg_debug/ns/items')
WHERE length(item_id) IN (36);

-- Control: read_parquet on same underlying data (returns 2 rows correctly)
SELECT 'Query 4 - OK: read_parquet with same equality predicate' AS test;
SELECT item_id, length(item_id) AS len
FROM read_parquet('/tmp/repro_385_pypi/iceberg_debug/ns/items/data/*.parquet')
WHERE length(item_id) = 36;

  4. evertlammerts commented on Mar 19, 2026

    @evertlammerts
    evertlammerts
    on Mar 19, 2026
    Member

    Ah, looks like you opened this in two places, @lasse-schier. Please cross-link them next time!

    Duplicate: duckdb/duckdb-iceberg#791

  5. evertlammerts
    closed this as duplicateon Mar 19, 2026

  6. lasse-schier commented on Mar 19, 2026

    @lasse-schier
    lasse-schier
    on Mar 19, 2026
    Author

    Ah, looks like you opened this in two places, @lasse-schier. Please cross-link them next time!

    Duplicate: duckdb/duckdb-iceberg#791

    Ahh sorry I will make sure to do this next time!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    reproduced

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions