DATA BRANCH PICK

Between tables with compatible explicit primary keys, cherry-pick selected rows from a source table into a destination table without merging the whole diff. The tables may share data-branch lineage or be independent with no LCA.

Description

DATA BRANCH PICK copies a subset of rows between two tables with compatible explicit primary keys. They may participate in the same data-branch lineage or be independent tables with no common ancestor. The statement reuses the diff machinery behind DATA BRANCH DIFF / DATA BRANCH MERGE, narrows the scope to the supplied keys or snapshot window, and writes INSERT/DELETE changes to the destination table. A hidden fake key on the source produces DATA BRANCH PICK requires a table with a primary key, while other primary-key mismatches are rejected by schema compatibility validation.

The statement is useful for selective synchronization — for example, promoting a single new row, back-porting one fix to another branch, or propagating only the changes that happened between two snapshots.

Syntax

DATA BRANCH PICK source_table [{ SNAPSHOT = 'snapshot_name' }]
    INTO destination_table
    [BETWEEN SNAPSHOT snapshot_from AND snapshot_to]
    [KEYS ( key_list | subquery )]
    [WHEN CONFLICT conflict_option]

At least one of KEYS (...) or BETWEEN SNAPSHOT ... AND ... is required.

KEYS clause

key_list   ::= expr [, expr ...]
             | ( col1_val, col2_val [, ...] ) [, ( ... ) ...]
subquery   ::= SELECT ... FROM ...

Conflict options

conflict_option:
    FAIL                            -- Default. Fail on conflict.
  | SKIP                            -- Keep destination row, drop the picked change.
  | ACCEPT                          -- Overwrite destination with source row.

Arguments

Parameter

Description

source_table

Table the rows are picked from. May carry a {SNAPSHOT = 'snap'} source-time option.

destination_table

Existing table that receives the picked rows. Its primary key and mapped columns must be compatible with the source; exact schema identity is not required. A snapshot option on the destination is rejected.

KEYS (expr, ...)

Literal list of primary-key values to pick. For a single-column primary key, each entry is a scalar; for a composite primary key, each entry must be a tuple (c1, c2, ...) with one element per PK column.

KEYS (SELECT ...)

Subquery whose result columns define the primary-key values. For a composite primary key, the subquery must project the PK columns in order. NULL values are rejected.

BETWEEN SNAPSHOT a AND b

Restrict picked rows to the half-open interval (a, b]. The source image is read at upper snapshot b. Snapshot names may be bare identifiers or string literals. Cannot be combined with a source-table SNAPSHOT option.

WHEN CONFLICT FAIL

Return an error when a picked key already exists in the destination with a different value. This is the default when the clause is omitted.

WHEN CONFLICT SKIP

Keep the destination row unchanged; drop both the DELETE and INSERT for the conflicting key.

WHEN CONFLICT ACCEPT

Apply the source row over the destination row (delete old, insert source value).

Usage Notes

  • Compatible explicit primary keys required. Both source and destination must have compatible explicit primary keys; otherwise, DATA BRANCH PICK is rejected. A hidden fake key on the source produces DATA BRANCH PICK requires a table with a primary key, while other primary-key mismatches are rejected by schema compatibility validation.

  • Destination snapshot is not supported. Specifying {SNAPSHOT = ...} on destination_table raises destination snapshot option is not supported for DATA BRANCH PICK.

  • Transaction restrictions. PICK rejects both an explicit BEGIN transaction and a session with autocommit=0. The reported error is DATA BRANCH MERGE/PICK is not supported in transactions.

  • Mutual exclusion of source snapshot and BETWEEN. Using source_table{SNAPSHOT = ...} together with BETWEEN SNAPSHOT a AND b raises BETWEEN SNAPSHOT and source table snapshot option cannot be used together.

  • At least one scope clause is required. Omitting both KEYS and BETWEEN SNAPSHOT raises DATA BRANCH PICK requires a KEYS or BETWEEN SNAPSHOT clause.

  • Conflict default is FAIL. Without WHEN CONFLICT, the first conflicting key aborts the statement and leaves the destination unchanged for the remaining picked keys.

  • Non-existent keys are no-ops. If a listed key is not present in the source (or not in the source change set in BETWEEN SNAPSHOT mode), the row is silently skipped.

  • KEYS subquery rules. The subquery must return the same number of columns as the primary key (one for single-column PK, N for N-column composite PK). Each row is coerced into the PK type; unsupported types raise KEYS subquery column type X is not supported for primary-key coercion, and NULL values raise KEYS subquery returned NULL, but DATA BRANCH PICK primary keys cannot be NULL.

  • Composite primary key. For a composite PK, literal keys must be tuples with exactly as many elements as PK columns; a mismatch raises KEYS tuple has N elements but composite primary key has M columns.

  • Privileges. The caller needs SELECT on the source, SELECT on the destination, and the destination write privileges (INSERT, UPDATE, and DELETE). Tables read by a KEYS subquery also require SELECT.

  • Schema compatibility. Exact source/destination schema identity is not required. Historical schema mapping and target-only columns can work when an explicit primary key is present, but mapped column types, attributes, nullability, generated-column definitions, and primary-key definitions must be compatible. Incompatible mappings are rejected before changes are applied.

Examples

DROP DATABASE IF EXISTS data_branch_pick_demo;
CREATE DATABASE data_branch_pick_demo;
USE data_branch_pick_demo;

-- Example 1: Pick specific rows from an independent source table.
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a));
INSERT INTO t1 VALUES (1,1),(3,3),(5,5);

CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
INSERT INTO t2 VALUES (1,1),(2,2),(4,4);

-- Pick only pk=2 from t2 into t1.
DATA BRANCH PICK t2 INTO t1 KEYS(2);
SELECT * FROM t1 ORDER BY a ASC;

-- Pick another key; absent keys are silently ignored.
DATA BRANCH PICK t2 INTO t1 KEYS(4);
DATA BRANCH PICK t2 INTO t1 KEYS(99);
SELECT * FROM t1 ORDER BY a ASC;

DROP TABLE t1;
DROP TABLE t2;

-- Example 2: Conflict handling with WHEN CONFLICT SKIP / ACCEPT.
CREATE TABLE t0 (a INT, b INT, PRIMARY KEY(a));
INSERT INTO t0 VALUES (1,1),(2,2);

DATA BRANCH CREATE TABLE t1 FROM t0;
INSERT INTO t1 VALUES (3,30);

DATA BRANCH CREATE TABLE t2 FROM t0;
INSERT INTO t2 VALUES (3,40);

-- Both branches inserted pk=3 with different values; the default FAIL mode
-- aborts the statement.
-- Expected-Success: false
DATA BRANCH PICK t2 INTO t1 KEYS(3);

-- SKIP keeps t1's (3,30).
DATA BRANCH PICK t2 INTO t1 KEYS(3) WHEN CONFLICT SKIP;
SELECT * FROM t1 ORDER BY a ASC;

-- ACCEPT overwrites with t2's (3,40).
DATA BRANCH PICK t2 INTO t1 KEYS(3) WHEN CONFLICT ACCEPT;
SELECT * FROM t1 ORDER BY a ASC;

DROP TABLE t0;
DROP TABLE t1;
DROP TABLE t2;

-- Example 3: Cherry-pick with a KEYS subquery.
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a));
INSERT INTO t1 VALUES (1,1);

CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
INSERT INTO t2 VALUES (1,1),(2,2),(3,3),(4,4),(5,5);

-- Pick only the even-keyed rows from t2 into t1.
DATA BRANCH PICK t2 INTO t1 KEYS(SELECT a FROM t2 WHERE a % 2 = 0);
SELECT * FROM t1 ORDER BY a ASC;

DROP TABLE t1;
DROP TABLE t2;

-- Example 4: Composite primary key with tuple keys.
CREATE TABLE t0 (id INT, name VARCHAR(20), val INT, PRIMARY KEY(id, name));
INSERT INTO t0 VALUES (1,'alice',10),(2,'bob',20);

DATA BRANCH CREATE TABLE t1 FROM t0;
DATA BRANCH CREATE TABLE t2 FROM t0;

INSERT INTO t2 VALUES (4,'dave',40),(5,'eve',50),(6,'frank',60);

-- Pick two composite keys out of three new rows.
DATA BRANCH PICK t2 INTO t1 KEYS((4,'dave'),(6,'frank'));
SELECT * FROM t1 ORDER BY id, name;

DROP TABLE t0;
DROP TABLE t1;
DROP TABLE t2;

-- Example 5: Pick changes that happened between two snapshots.
DROP SNAPSHOT IF EXISTS sp1;
DROP SNAPSHOT IF EXISTS sp2;

CREATE TABLE t0 (a INT, b INT, PRIMARY KEY(a));
INSERT INTO t0 VALUES (1,1),(2,2),(3,3);

DATA BRANCH CREATE TABLE t1 FROM t0;

CREATE SNAPSHOT sp1 FOR ACCOUNT sys;

INSERT INTO t1 VALUES (4,4),(5,5);

CREATE SNAPSHOT sp2 FOR ACCOUNT sys;

INSERT INTO t1 VALUES (6,6),(7,7);

-- Only rows changed between sp1 and sp2 are picked (pk=4,5).
-- Rows inserted after sp2 (pk=6,7) are not.
DATA BRANCH PICK t1 INTO t0 BETWEEN SNAPSHOT sp1 AND sp2;
SELECT * FROM t0 ORDER BY a ASC;

-- BETWEEN can be combined with KEYS for an intersection of time and key set.
DATA BRANCH PICK t1 INTO t0 BETWEEN SNAPSHOT sp1 AND sp2 KEYS(5);
SELECT * FROM t0 ORDER BY a ASC;

DROP SNAPSHOT sp1;
DROP SNAPSHOT sp2;
DROP TABLE t0;
DROP TABLE t1;

DROP DATABASE data_branch_pick_demo;

Notes

  1. DATA BRANCH PICK is statement-atomic even though it is not a whole-table snapshot copy. With WHEN CONFLICT FAIL (the default), any conflicting key aborts the statement and none of its earlier or later key changes remain. SKIP applies non-conflicting keys and preserves the destination rows for conflicting keys. ACCEPT applies the source-side result for conflicting keys.

  2. When both BETWEEN SNAPSHOT and KEYS are supplied, the picked set is the intersection of the snapshot-range change set and the key set.

  3. Picking a key that exists in the destination but not in the source is a no-op. Picking a key that was deleted on the source with the destination unchanged propagates the DELETE to the destination.

  4. For WHEN CONFLICT FAIL, the reported error identifies the conflicting source and destination operations for the primary key; INSERT/INSERT is one possible form.