[
https://issues.apache.org/jira/browse/HIVE-30147?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Work on HIVE-30147 started by Ryu Kobayashi.
--------------------------------------------
> Align Iceberg copy-on-write MERGE semantics with native MERGE
> -------------------------------------------------------------
>
> Key: HIVE-30147
> URL: https://issues.apache.org/jira/browse/HIVE-30147
> Project: Hive
> Issue Type: Bug
> Reporter: Ryu Kobayashi
> Assignee: Ryu Kobayashi
> Priority: Major
>
> h2. Summary
> The Iceberg copy-on-write MERGE rewrite does not fully preserve the semantics
> of the native MERGE implementation.
> The affected areas include NULL-key handling, source-only row detection, SQL
> three-valued predicate evaluation, and `WHEN MATCHED` clause ordering. These
> issues should be corrected so that copy-on-write MERGE behaves consistently
> with native MERGE.
> h2. Root cause
> The current implementation in `CopyOnWriteMergeRewriter` rewrites MERGE
> statements into a source CTE and multiple `UNION ALL` branches.
> Several parts of the rewrite rely on derived predicates and string-level
> predicate composition:
> - `MergeSemanticAnalyzer.OnClauseAnalyzer` derives source-only predicates
> from NULL checks on target join columns.
> - `CopyOnWriteMergeRewriter` reuses those predicates for the INSERT and
> carry-over branches.
> - Target/source presence is not represented by an explicit join-side marker
> such as the target file path.
> - Multiple predicates are concatenated with `AND` and `OR`, which can change
> their effective precedence when the original expressions contain OR operators.
> - Later `WHEN MATCHED` clauses are gated with `NOT(previousPredicate)`,
> which does not implement SQL `IS NOT TRUE` semantics when the previous
> predicate evaluates to `UNKNOWN`.
> h2. Repeated source evaluation and duplicated execution work
> The current CoW rewrite also evaluates the same source relation and match
> predicate multiple times. The marker CTE contains separate representative-row
> and grouped-count scans.
> This can produce inconsistent results for non-deterministic or stateful
> predicates such as `rand()`, because the scans may observe different row
> sets. It also adds a second full scan, aggregation, join, and shuffle to CoW
> DML, and can duplicate the work for MERGE source relations.
> The follow-up design should evaluate the source relation and match
> disposition once through a shared execution stream, while preserving native
> MERGE semantics and avoiding duplicated window and aggregation pipelines.
> h2. Reproduction steps
> # NULL-key target row is lost or causes a spurious INSERT
> Create an Iceberg v2 copy-on-write table and insert a NULL-key row together
> with a matched row:
> {code:java}
> CREATE TABLE target (id int, value string)
> STORED BY ICEBERG
> TBLPROPERTIES ('format-version'='2',
> 'write.merge.mode'='copy-on-write');INSERT INTO target VALUES (1, 'one'),
> (NULL, 'null-key');CREATE TABLE source (id int, value string);
> INSERT INTO source VALUES (1, 'updated');
> MERGE INTO target t
> USING source s
> ON t.id = s.id
> WHEN MATCHED THEN UPDATE SET value = s.value
> WHEN NOT MATCHED THEN INSERT VALUES (s.id, s.value);The existing (NULL,
> 'null-key') row must be preserved, and no synthetic NULL row may be
> inserted.{code}
> The existing (NULL, 'null-key') row must be preserved, and no synthetic NULL
> row may be inserted.
> # UNKNOWN matched predicate does not fall through
> Use a target row whose predicate column is NULL:
> {code:java}
> Use a target row whose predicate column is NULL:INSERT INTO target VALUES (2,
> NULL);
> INSERT INTO source VALUES (2, 'source');
> MERGE INTO target t
> USING source s
> ON t.id = s.id
> WHEN MATCHED AND t.value = 'delete' THEN DELETE
> WHEN MATCHED THEN UPDATE SET value = 'updated';{code}
> The row (2, NULL) must be updated to (2, 'updated'). It must not be lost
> because the first predicate evaluates to UNKNOWN.
> # Missing predicate grouping affects unmatched target rows
> Create a target with matched and unmatched rows:
> {code:java}
> CREATE TABLE target_grouping (id int, value string)
> STORED BY ICEBERG
> TBLPROPERTIES ('format-version'='2',
> 'write.merge.mode'='copy-on-write');INSERT INTO target_grouping VALUES
> (1, 'delete'), (2, 'keep'), (3, 'keep');CREATE TABLE source_grouping (id
> int);
> INSERT INTO source_grouping VALUES (1);
> MERGE INTO target_grouping t
> USING source_grouping s
> ON t.id = s.id
> WHEN MATCHED AND t.value = 'delete' OR t.value = 'keep' THEN DELETE; {code}
> Only (1, 'delete') should be removed. The unmatched rows (2, 'keep') and (3,
> 'keep') must remain.
> h2. Confirmed / observed behavior
> The following behaviors have been observed or identified:
> * NULL-key target rows can be misclassified as source-only rows when a data
> file is rewritten.
> * Target-only rows with NULL join keys can produce unintended INSERT rows.
> * A matched predicate that evaluates to UNKNOWN can prevent a later `WHEN
> MATCHED` clause from executing.
> * Unparenthesized combinations of `ON` predicates and clause predicates can
> affect unrelated target rows.
> * Repeated evaluation of the same source relation or predicate can produce
> inconsistent results for non-deterministic expressions.
> * Repeated source evaluation can create additional scan, join, aggregation,
> and shuffle stages.
> h2. Impact / exposure
> These issues affect the correctness and compatibility of Iceberg
> copy-on-write MERGE statements.
> Potential effects include:
> - Silent loss of legitimate target rows.
> - Unintended insertion of NULL or source-derived rows.
> - Different results from native MERGE for predicates involving NULL or OR
> expressions.
> - Incorrect first-match behavior for multiple `WHEN MATCHED` clauses.
> - Inconsistent results for non-deterministic or stateful predicates.
> - Increased scan, join, aggregation, and shuffle work.
> - Behavior that varies depending on CBO configuration.
> This issue is separate from HIVE-30064, which focuses on affected-row
> reporting for Iceberg copy-on-write DML.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
