[
https://issues.apache.org/jira/browse/HIVE-29816?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Raghav Aggarwal updated HIVE-29816:
-----------------------------------
Description:
When executing a MERGE INTO statement with a WHEN MATCHED THEN DELETE clause on
an Iceberg table configured with *write.delete.mode=copy-on-write* ({*}and
default write.merge.mode=merge-on-read{*}), the target rows matching the
condition are incorrectly duplicated instead of being deleted.
Attaching sql to repro and spark vs hive comparison screenshot
Simple steps to repro:
{code:java}
CREATE TABLE ice_cow_merge_delete_only (id INT, data STRING)
STORED BY ICEBERG
TBLPROPERTIES ('format-version'='3', 'write.delete.mode'='copy-on-write');
INSERT INTO ice_cow_merge_delete_only VALUES (1, 'apple'), (2, 'banana'), (3,
'cherry');
MERGE INTO ice_cow_merge_delete_only t
USING (SELECT 2 as id, 'banana' as data) s
ON t.id = s.id
WHEN MATCHED THEN DELETE;
SELECT * FROM ice_cow_merge_delete_only;
{code}
Wrong Output:
{code:java}
+-------------------------------+---------------------------------+
| ice_cow_merge_delete_only.id | ice_cow_merge_delete_only.data |
+-------------------------------+---------------------------------+
| 1 | apple |
| 2 | banana |
| 3 | cherry |
| 2 | banana |
+-------------------------------+---------------------------------+ {code}
Expected Output:
{code:java}
1 apple
3 cherry {code}
was:
When executing a MERGE INTO statement with a WHEN MATCHED THEN DELETE clause on
an Iceberg table configured with *write.delete.mode=copy-on-write* ({*}and
default write.merge.mode=merge-on-read{*}), the target rows matching the
condition are incorrectly duplicated instead of being deleted.
Attaching sql to repro and spark vs hive comparison screenshot
> Iceberg: MERGE INTO with WHEN MATCHED THEN DELETE incorrectly duplicates rows
> under Copy-On-Write delete mode
> -------------------------------------------------------------------------------------------------------------
>
> Key: HIVE-29816
> URL: https://issues.apache.org/jira/browse/HIVE-29816
> Project: Hive
> Issue Type: Bug
> Reporter: Raghav Aggarwal
> Assignee: Raghav Aggarwal
> Priority: Major
> Attachments: hive.png, spark.png
>
>
> When executing a MERGE INTO statement with a WHEN MATCHED THEN DELETE clause
> on an Iceberg table configured with *write.delete.mode=copy-on-write* ({*}and
> default write.merge.mode=merge-on-read{*}), the target rows matching the
> condition are incorrectly duplicated instead of being deleted.
> Attaching sql to repro and spark vs hive comparison screenshot
> Simple steps to repro:
> {code:java}
> CREATE TABLE ice_cow_merge_delete_only (id INT, data STRING)
> STORED BY ICEBERG
> TBLPROPERTIES ('format-version'='3', 'write.delete.mode'='copy-on-write');
> INSERT INTO ice_cow_merge_delete_only VALUES (1, 'apple'), (2, 'banana'), (3,
> 'cherry');
> MERGE INTO ice_cow_merge_delete_only t
> USING (SELECT 2 as id, 'banana' as data) s
> ON t.id = s.id
> WHEN MATCHED THEN DELETE;
> SELECT * FROM ice_cow_merge_delete_only;
> {code}
> Wrong Output:
> {code:java}
> +-------------------------------+---------------------------------+
> | ice_cow_merge_delete_only.id | ice_cow_merge_delete_only.data |
> +-------------------------------+---------------------------------+
> | 1 | apple |
> | 2 | banana |
> | 3 | cherry |
> | 2 | banana |
> +-------------------------------+---------------------------------+ {code}
> Expected Output:
> {code:java}
> 1 apple
> 3 cherry {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
