[
https://issues.apache.org/jira/browse/HIVE-30147?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ryu Kobayashi updated HIVE-30147:
---------------------------------
Description:
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.
was:
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, and the cardinality-validation branch independently
evaluates the MERGE source relation.
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/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 cases require regression coverage and correction:
# A target row whose join key is NULL can be mistaken for a source-only row
when another row in the same data file is matched and the file is rewritten.
# A target-only row with NULL join keys can satisfy the derived source-only
predicate and produce an unintended INSERT.
# 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
cause source-only rows or unrelated target rows to enter the replacement-marker
path.
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.
- Inconsistent behavior depending on CBO configuration.
This issue is separate from HIVE-30064's affected-row reporting scope.
HIVE-30064 tracks logical affected-row counts for CoW DML; this issue tracks
the broader correctness and semantic alignment of CoW MERGE rewriting.
The implementation should preserve native MERGE semantics and include
regression coverage for the affected cases.
> 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
(v8.20.10#820010)
