[Feature Request]: INSERT ON DUPLICATE KEY UPDATE - Unify Paths and Fix Semantic Issues

[XuPeng-SH]

Is there an existing issue for the same feature request?

• I have checked the existing issues.

Summary

INSERT ON DUPLICATE KEY UPDATE (ODKU) in MatrixOne has multiple execution paths with semantic inconsistencies and performance concerns.

Current Architecture

Modern Path

• Entry: pkg/sql/plan/build.go:74-79 → bindInsert() in pkg/sql/plan/bind_insert.go
• Features: PK/UK dedup, LOCK_OP, MULTI_UPDATE

Legacy Path

• Entry: buildInsert() → pkg/sql/plan/build_constraint_util.go + pkg/sql/colexec/onduplicatekey/on_duplicate_key.go

Rewrite-to-Replace Path

• pkg/sql/plan/build_insert.go:702-745 rewrites some ODKU to REPLACE
• pkg/sql/compile/scope.go:888-915 executes as DELETE + INSERT (non-atomic)

Critical Concerns

1. Semantic Fragmentation

Different statement patterns may fall into different execution paths, leading to inconsistent behavior and performance.

2. Non-Atomic Rewrite

ODKU rewritten to REPLACE is not a single operator but two SQL statements, introducing:

• Extra execution overhead
• Additional write window vulnerability
• No transactional atomicity

3. Legacy Path Risks

build_constraint_util.go uses a large LEFT JOIN with multiple unique key conditions OR'd together. For tables with multiple unique keys, it adds AGG + any_value, which is prone to semantic drift.

Missing Coverage

• NULL value handling in ODKU
• Multi-unique-key scenarios
• Rewrite-to-REPLACE edge cases
• Concurrent modification during rewrite window

Proposed Improvements

Short-term

• Audit and document all ODKU execution paths
• Add test coverage for edge cases (NULL, multi-UK, partitions)

Medium-term

• Unify legacy and modern paths under operator-based execution
• Eliminate or properly handle rewrite-to-REPLACE path

Long-term

• Consider pushing conflict detection to storage/txn layer
• Ensure atomic semantics for all ODKU variants

Related Code Locations

• Modern path: pkg/sql/plan/bind_insert.go
• Legacy path: pkg/sql/plan/build_constraint_util.go, pkg/sql/colexec/onduplicatekey/on_duplicate_key.go
• Rewrite logic: pkg/sql/plan/build_insert.go:702-745
• Execution: pkg/sql/compile/scope.go:888-915

---

What type of PR is this?

• API-change
• BUG
• Improvement
• Documentation
• Feature
• Test and CI
• Code Refactoring