feat: support MERGE

[BohuTANG]

Summary

With MERGE, we can merge a source table into a target table by conditionally insert, update, or delete rows in batch, this is the best way to change data capture (CDC) big data.

MERGE INTO <target_table> USING <source> ON <join_expr> { matchedClause | notMatchedClause } [ ... ]

matchedClause ::=
  WHEN MATCHED [ AND <case_predicate> ] THEN { UPDATE SET <col_name> = <expr> [ , <col_name2> = <expr2> ... ] | DELETE } [ ... ]

notMatchedClause ::=
   WHEN NOT MATCHED [ AND <case_predicate> ] THEN INSERT [ ( <col_name> [ , ... ] ) ] VALUES ( <expr> [ , ... ] )

Example

Target Table

CREATE TABLE account
 (
     id           INT,
     name         VARCHAR,
     last_updated DATETIME
 );

INSERT INTO account VALUES     (1, 'alice', '2023-01-01');
INSERT INTO account VALUES     (2, 'bob',  '2023-01-01');
INSERT INTO account VALUES     (3, 'charlie',  '2023-01-02'); 

Stage Table

CREATE TABLE account_stage
  (
     id           INT,
     NAME         VARCHAR,
     last_updated DATETIME,
     operation    VARCHAR
  );

INSERT INTO account_stage VALUES     (1, 'alice', '2023-02-22', 'U');
INSERT INTO account_stage VALUES     (1, 'alice', '2023-02-23', 'D');
INSERT INTO account_stage VALUES     (2, 'bob', '2023-02-21', 'U');
INSERT INTO account_stage VALUES     (2, 'bob', '2023-02-23', 'U');
INSERT INTO account_stage VALUES     (4, 'dell',  '2022-02-23',  'I'); 

Merge Stage to Target

MERGE INTO account
using (SELECT id, NAME, last_updated,operation FROM   account_stage) AS cdc
ON account.id = cdc.id

WHEN matched AND cdc.operation='U' THEN
  UPDATE SET NAME = cdc.NAME, last_updated = cdc.last_updated
WHEN matched AND cdc.operation='D' THEN
  DELETE
WHEN NOT matched AND cdc.operation='I' THEN
  INSERT VALUES(cdc.id, cdc.NAME, cdc.last_updated) 

References:
[1] PostgreSQL: https://www.postgresql.org/docs/current/sql-merge.html
[2] Snowflake: https://docs.snowflake.com/en/sql-reference/sql/merge

[BohuTANG]

@leiysky @andylokandy : SQL parser and Planner need your help and comments
@dantengsky : storage needs your help

Thanks.

[flaneur2020]

there's a JOIN in the MERGE statement, does it need an integration with CBO?

(in most of the time the right hand side is not very big, however MERGE also support the merging of two full tables totally, it can be viewed as a materializing the joined result back to the original table in a batch manner.

[BohuTANG]

there's a JOIN in the MERGE statement, does it need an integration with CBO?

(in most of the time the right hand side is not very big, however MERGE also support the merging of two full tables totally, it can be viewed as a materializing the joined result back to the original table in a batch manner.

Merge is a special logic plan(DML), it's not a join query as normal, the USING .. ON is a constraint not a really JOIN, no CBO here.

[JackTan25]

/assign me

[BohuTANG]

Done