Schema merge issue Decimal type scale change while merge sql

[dattawalake]

Delta version 0.6.1
Spark 2.4.4

Merge sql fails , if source dataframe schema specifically dataype Decimal with scale change . Seems its not auto merging schema I am getting below exception -
Failed to merge decimal types with incompatible scale 0 and 2;

Workaround : I am applying schema diff changes before merge query explicitly, as per version 0.6.1 delta take care auto schema merge to target table.

Thanks

[tdas]

can you give more information on the query and data types of the relevant columns? We would like to understand this well.

[dattawalake]

Here is a scenario executed with delta core 0.6.1 version:

1.Day 1 run to n runs with version 0.5.0
1.1 Source (Input)
schema -> id : string (nullable = true)
amount: decimal(17,0) (nullable = true)
1.2 Target table(Delta table) created after merge (merge sql) with source
schema -> id : string (nullable = true)
amount: decimal(17,0) (nullable = true)

day1 to n run Result : successfully merge and created output for n runs for no schema differences on format level. Added/deleted column we handled with our code to sync schema.

2.Day n+1 run with updated jar version 0.6.1
2.1 Source (Input)
schema -> id : string (nullable = true)
amount: decimal(17,2) (nullable = true)
2.2 Target table(Delta table) Before merge
schema -> id : string (nullable = true)
amount: decimal(17,0) (nullable = true)
2.3 Trying to execute merge sql as per scala merge syntax based on key

Run result failed with error -> Failed to merge decimal types with incompatible scale 0 and 2;

I tried to sync input file schema to target (delta table) location with .mode("Append") and .option("mergeSchema", "true") before executing merge , but it did not help, failed with same exception.

In previous version 0.5.0 I handled schema merge this way, before merging, finding difference between source and target(delta table) schema and on any difference, writing blank file with input schema to target location.
Seems datatype format level differences not handled in mergeSchema. or i am missing something.

Workaround for above issue I followed is -> re writing target to temp location with cast to decimal (17,2) and copied to target (delta table) location.

Thanks

[tdas]

It's possible that since 0.5.0, we may have fixed a few corner cases regarding incorrect schema evolutions. @brkyvz does any change come to mind?

[chaudharirohit2810]

We are facing same issue in version 2.1.0, is there any solution for this?

Our Scenario:
We have decimal column with lower precision for eg. 8 and we want to increase the precision for eg. 13
We tried to do it using mergeSchema option
But it throws the error decimal(38,8) cannot be cast to decimal(38,13)

Also changing type using ALTER TABLE doesn't work

The only solution we can find is to overwrite the entire data using overwriteSchema option

[zsxwing]

Run result failed with error -> Failed to merge decimal types with incompatible scale 0 and 2;

For the original issue reported in this ticket, currently Delta doesn't support merging different decimal types. The technical challenge is parquet may store different decimal types in different formats ( https://github.com/apache/parquet-format/blob/master/LogicalTypes.md#decimal ) and Delta today doesn't have a mechanism to read data with different potential parquet decimal formats.

decimal(38,8) cannot be cast to decimal(38,13)

This is expected. Changing a value of decimal(38,8) to decimal(38,13) may lose information, hence we don't allow it.

[AnasKhchaf]

Hello chaudharirohit2810 dattawalake,

have you tried spark.sql.decimalOperations.allowPrecisionLoss=false configuration to not losse precision in in decimal datatype ?

Hope this will help !