[Feature Request]: REPLACE INTO Optimization - Unify Legacy/Modern Paths and Improve Performance

[XuPeng-SH]

Is there an existing issue for the same feature request?

• I have checked the existing issues.

Summary

REPLACE INTO in MatrixOne has two execution paths with significant issues in semantics, performance, and correctness.

Current Problems

Legacy Path Issues

1. Suspicious DELETE conditions: Legacy path constructs DELETE conditions via string concatenation. buildConjunction() can generate col = NULL conditions with questionable semantics.
2. Non-atomic execution: Legacy path executes two separate SQL statements (DELETE then INSERT) in scope.go, not a single operator with atomic semantics.

Modern Path Issues

1. Multiple table/index scans: Modern path scans the main table multiple times. Each unique index triggers additional DEDUP JOIN and old-rowid LEFT JOIN operations.
2. Concurrency concerns: LOCK_OP comes late in the pipeline. Potential race conditions under high concurrency need targeted stress testing.
3. Partition handling: PartitionNames exists in parser but is not consumed in REPLACE planning.

Missing Test Coverage

• NULL value handling
• High-concurrency scenarios
• Partition table cases

Performance Concerns

• Modern path overhead: Main table scanned at least 2x. Each unique index amplifies join and lock target complexity.
• Legacy path overhead: String construction, SQL re-parsing, and dual-execution costs.

Proposed Improvements

Short-term (High Value)

• Eliminate legacy fallback path, unify to operator-based execution.

Medium-term

• Create a dedicated REPLACE operator that reuses initInsertReplaceStmt / MULTI_UPDATE logic.
• Maintain delete+insert semantics, avoid direct ODKU substitution.

Long-term (Optimal)

• Push conflict detection + delete + insert down to storage/txn layer for true atomic replace.

Additional Context

Related: PK/UK/FK/CTE/vector-index test cases exist, but NULL/concurrency/partition tests are missing.

---

What type of PR is this?

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

[ck89119]

Legacy Path has been removed