DeltaTable.delete fails with DataFusion predicate: arrow_cast should have been simplified to cast

[geoHeil]

Describe the bug

When using deltalake.DeltaTable.delete(predicate=...) with a
predicate generated for the DataFusion dialect, Delta errors
with:

DeltaError: Generic DeltaTable error: Internal error: arrow_cast
should have been simplified to cast

Traceback (most recent call last):
  File "demo.py", line 64, in <module>
    main()
  File "demo.py", line 60, in main
    table.delete(predicate=predicate)
  File ".venv/lib/python3.10/site-packages/deltalake/table.py", line 1112, in delete
    metrics = self._table.delete(
_internal.DeltaError: Generic DeltaTable error: Internal error: arrow_cast should have been simplified to cast.
This issue was likely caused by a bug in DataFusion's code. Please help us to resolve this by filing a bug report in our issue tracker: https://github.com/apache/datafusion/issues

See the raw SQL which is causing the problem

SELECT
  *
FROM "t" AS "t0"
WHERE
  "t0"."created_at" = ARROW_CAST('2024-01-01 00:00:00+00:00', 'Timestamp(Microsecond, Some("UTC"))')
Predicate: "created_at" = ARROW_CAST('2024-01-01 00:00:00+00:00', 'Timestamp(Microsecond, Some("UTC"))')

To Reproduce

Minimal repro (Python):

verified for deltalake 1.3.2

from __future__ import annotations

from datetime import datetime, timedelta, timezone
from pathlib import Path
import shutil
import re

import polars as pl
import sqlglot
from deltalake import DeltaTable, write_deltalake

import ibis


def extract_where(sql: str, *, dialect: str | None) -> str:
    expr = sqlglot.parse_one(sql, read=dialect)
    where = expr.args.get("where")
    if where is None:
        raise RuntimeError("No WHERE clause found")
    # Strip table qualifiers for a single-table predicate
    for col in where.find_all(sqlglot.exp.Column):
        if col.table:
            col.set("table", None)
    predicate = where.sql(dialect=dialect) if dialect else where.sql()
    predicate = re.sub(r"^WHERE\s+", "", predicate, flags=re.IGNORECASE)
    return predicate


def main() -> None:
    root = Path("/tmp/delta_arrow_cast_datafusion").resolve()
    table_uri = str(root)
    shutil.rmtree(root, ignore_errors=True)

    base_time = datetime(2024, 1, 1, tzinfo=timezone.utc)
    newer_time = base_time + timedelta(minutes=5)

    df = pl.DataFrame(
        {
            "sample_uid": ["a", "a"],
            "value": [1, 2],
            "created_at": [base_time, newer_time],
        }
    )

    write_deltalake(table_uri, df, mode="overwrite")

    schema = ibis.schema({
        "sample_uid": "string",
        "value": "int64",
        "created_at": "timestamp('UTC')",
    })
    t = ibis.table(schema, name="t")
    filtered = t.filter(t.created_at == base_time)
    sql = ibis.to_sql(filtered, dialect="datafusion")
    predicate = extract_where(sql, dialect="datafusion")
    print("DataFusion SQL:", sql)
    print("Predicate:", predicate)

    table = DeltaTable(table_uri)
    table.delete(predicate=predicate)


if __name__ == "__main__":
    main()

Expected behavior

perform the deletion without parsing error

Additional context

Switching the dialect to postgres by changing from

predicate = extract_where(sql, dialect="datafusion")

to

predicate = extract_where(sql, dialect="postgres")

is a viable workaround for now - but feels wrong.

[danielgafni]

I think you should first:

1. Verify whether this is a DataFusion issue or a DeltaLake issue. The exception message provided here originates from DeltaLake. Im pretty sure the standalone DataFusion query engine has worked fine with these predicates in our separate unit tests. A full stack trace would clarify things.
2. If you think this is DataFusion issue, you should remove everything unnecessary from the example. Currently it involves SQL generation via Ibis and other code which makes it harder to prove this is a DataFusion issue. Can you just keep the problematic SQL predicate? I’m sure the example can be stripped to ~5 lines of code?

Perhaps the issue should be reported to delta-rs instead?

[geoHeil]

The full stacktrace has:

This issue was likely caused by a bug in DataFusion's code. Please help us to resolve this by filing a bug report in our issue tracker: https://github.com/apache/datafusion/issues

at the bottom this is why I created the issue here.

Shortened example:

from datetime import datetime, timezone
from pathlib import Path

import polars as pl
from deltalake import DeltaTable, write_deltalake

table_uri = str(Path("/tmp/delta_arrow_cast_datafusion").resolve())
base_time = datetime(2024, 1, 1, tzinfo=timezone.utc)

df = pl.DataFrame(
    {
        "sample_uid": ["a", "a"],
        "value": [1, 2],
        "created_at": [base_time, base_time],
    }
)
write_deltalake(table_uri, df, mode="overwrite")

predicate = "\"created_at\" = ARROW_CAST('2024-01-01 00:00:00+00:00', 'Timestamp(Microsecond, Some(\"UTC\"))')"
DeltaTable(table_uri).delete(predicate=predicate)

[danielgafni]

Oh I see, my bad, completely missed this part. Seems good then!

[kumarUjjawal]

Hi @geoHeil Thank you for the detailed report. I looked into it and I think this is actually working as designed. The arrow_cast function is a planning-time only construct, it's meant to be rewritten to a standard CAST during DataFusion's optimizer pass and never actually executed.

When you run a query through DataFusion normally, the simplifier converts:

arrow_cast('2024-01-01', 'Timestamp(Microsecond, Some("UTC"))')

into:

CAST('2024-01-01' AS Timestamp(Microsecond, Some("UTC")))

The issue is that deltalake is taking your predicate string and executing it directly without running it through DataFusion's optimizer/simplifier. That's why it hits the "should have been simplified" error.

The fix should be in deltalake, they'd need to run predicates through ExprSimplifier before execution, or handle ARROW_CAST syntax on their end.

Your workaround of using dialect="postgres" is the right approach for now since it generates standard SQL cast syntax that works directly.

cc @alamb

[alamb]

Looks like @geoHeil has filed an upstream ticket: delta-io/delta-rs#4093