DeltaTable Merge throws in merging if there are uppercase in Schema.

[mohitbansal-gep]

Environment

Delta-rs version: deltalake==0.12.0

Binding:

Environment:

• Cloud provider:
• OS:
• Other: python

---

Bug

What happened:
If deltatable schema has attribute with upper case in them, then the merge throws Generic deltatable error stating field not found
What you expected to happen:
The Deltatable merge operation should have been performed.
How to reproduce it:
Sample code:-

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

df = pl.DataFrame(
    {
        "Sales_order_id": ["1000", "1001", "1002", "1003"],
        "product": ["bike", "scooter", "car", "motorcycle"],
        "order_date": [
            datetime(2023, 1, 1),
            datetime(2023, 1, 5),
            datetime(2023, 1, 10),
            datetime(2023, 2, 1),
        ],
        "sales_price": [120.25, 2400, 32000, 9000],
        "paid_by_customer": [True, False, False, True],
    }
)
print(df)
df.write_delta("sales_orders_old", mode="append")

new_data = pl.DataFrame(
    {
        "Sales_order_id": ["1002", "1004"],
        "product": ["car", "car"],
        "order_date": [datetime(2023, 1, 10), datetime(2023, 2, 5)],
        "sales_price": [30000.0, 40000.0],
        "paid_by_customer": [True, True],
    }
)

from polars.io.delta import _convert_pa_schema_to_delta

dt = DeltaTable("sales_orders_old")
source = new_data.to_arrow()
delta_schema = _convert_pa_schema_to_delta(source.schema)
source = source.cast(delta_schema)

(
    dt.merge(
        source=source,
        predicate="s.Sales_order_id = t.Sales_order_id",
        source_alias="s",
        target_alias="t",
    )
    .when_matched_update_all()
    .when_not_matched_insert_all()
    .execute()
)

This will throw:- DeltaError: Generic DeltaTable error: Schema error: No field named s.sales_order_id.
More details:

Referenced from this blog post https://delta.io/blog/2023-10-22-delta-rs-python-v0.12.0/

[mohitbansal-gep]

@rtyler i can work on this issue if required, But i need some guidance on how to load the project and initial setup.

[ion-elgreco]

@mohitbansal-gep if you want to contribute, you can follow the guide here: https://github.com/delta-io/delta-rs/blob/main/python/CONTRIBUTING.md

[Blajda]

@mohitbansal-gep I you wrap those column with double quotes it should resolve the issue.
e.g predicate="\"s.Sales_order_id\" = \"t.Sales_order_id\""

[mohitbansal-gep]

@Blajda Tried above but same issue.

DeltaError: Generic DeltaTable error: Schema error: No field named "s.Sales_order_id".

[mohitbansal-gep]

@Blajda any more info?

[ArthurSteijn]

Same here. If you change the predicate of the merge, you will get the same error on a different Column.

@ion-elgreco what would be a fair direction for a fix here? Should the merge be able to handle table schemas with upper casing? (that definitely has my preference)

Schema:
Schema([Field(CustomerID, PrimitiveType("long"), nullable=True), Field(NameStyle, PrimitiveType("long"), nullable=True), Field(Title, PrimitiveType("string"), nullable=True), Field(FirstName, PrimitiveType("string"), nullable=True), Field(MiddleName, PrimitiveType("string"), nullable=True), Field(LastName, PrimitiveType("string"), nullable=True), Field(Suffix, PrimitiveType("string"), nullable=True), Field(CompanyName, PrimitiveType("string"), nullable=True), Field(SalesPerson, PrimitiveType("string"), nullable=True), Field(EmailAddress, PrimitiveType("string"), nullable=True), Field(Phone, PrimitiveType("string"), nullable=True), Field(PasswordHash, PrimitiveType("string"), nullable=True), Field(PasswordSalt, PrimitiveType("string"), nullable=True)])
Merge:

source = source_df.to_arrow()
delta_schema = _convert_pa_schema_to_delta(source.schema)
source = source.cast(delta_schema)
(
    dt.merge(
        source=source,
        predicate='s."CustomerID" = t."CustomerID"',
        source_alias="s",
        target_alias="t",
    )
    .when_matched_update_all()
    .when_not_matched_insert_all()
    .execute()
)

Error:
DeltaError: Generic DeltaTable error: Schema error: No field named s.passwordsalt. Valid fields are s."CustomerID", s."NameStyle", s."Title", s."FirstName", s."MiddleName", s."LastName", s."Suffix", s."CompanyName", s."SalesPerson", s."EmailAddress", s."Phone", s."PasswordHash", s."PasswordSalt", s.__delta_rs_source, t."CustomerID", t."NameStyle", t."Title", t."FirstName", t."MiddleName", t."LastName", t."Suffix", t."CompanyName", t."SalesPerson", t."EmailAddress", t."Phone", t."PasswordHash", t."PasswordSalt", t.__delta_rs_target.

[ion-elgreco]

@ArthurSteijn
Let me see if I can work on a fix for the update_all, insert_all.

Also fyi, _convert_pa_schema_to_delta behavour will change in next Polars version since it won't return normal arrow types now, but large. I am planning on porting the old behaviour into deltalake directly so we can use it for multiple use case (like pandas conversion and for the merge).

[Blajda]

@ion-elgreco I have a potential fix at the rust level for this which should not require any python related changes. Will upload the branch later today and add new test to confirm.

[ion-elgreco]

@ion-elgreco I have a potential fix at the rust level for this which should not require any python related changes. Will upload the branch later today and add new test to confirm.

Ah nice, even better!

[mohitbansal-gep]

@Blajda any updates?

[Blajda]

I think a fix for this will come after the refactor to logical plans are merged. This should come soonish just waiting on a new datafusion release.

[ion-elgreco]

@Blajda I checked against the latest main, however the issue still persist with logical plans.