DATA BRANCH DIFF

The DATA BRANCH DIFF statement is used to compare data differences between two tables.

Description

The DATA BRANCH DIFF statement is used to compare data differences between two tables. This feature is similar to Git’s diff command and can display insert, delete, and update operations between two data branches.

The system automatically identifies the Lowest Common Ancestor (LCA) between two tables and calculates lineage-aware differences when one exists. In that case, the difference results include:

  • INSERT: Rows that exist in the target table but not in the base table

  • DELETE: Rows that exist in the base table but not in the target table

  • UPDATE: Rows with the same primary key in both tables but different values in other columns

If the tables have no common ancestor, both sides are treated as independent images: a row that exists on only one side, or has a different value on each side, is emitted as an INSERT for the side that supplies that image. DELETE and UPDATE describe lineage-aware changes, not the no-LCA fallback.

Syntax

DATA BRANCH DIFF target_table [{ SNAPSHOT = 'snapshot_name' }]
    AGAINST base_table [{ SNAPSHOT = 'snapshot_name' }]
    [COLUMNS ( col1 [, col2 ...] )]
    [OUTPUT output_option]

COLUMNS projection

COLUMNS (col1, col2, ...) restricts the visible value columns returned per diff row to exactly the requested subset. Primary-key columns are still used internally for matching and final ordering, but are not added to the output unless explicitly listed. Column names are matched and deduplicated case-insensitively, preserving the first occurrence, so COLUMNS(name, NAME, name) emits one name column. COLUMNS cannot be combined with OUTPUT FILE.

Output Options

output_option:
    COUNT                           -- Return only the count of different rows
  | LIMIT number                    -- Limit the number of returned difference rows
  | FILE 'directory_path'           -- Export differences as SQL file
  | AS table_name                   -- Persist differences in a new ordinary table
  | SUMMARY                         -- Return aggregated INSERT / DELETE / UPDATE counts

Arguments

Parameter Description

Parameter

Description

target_table

Target table (the table to compare)

base_table

Base table (the table used as comparison baseline)

SNAPSHOT = 'snapshot_name'

Optional parameter to specify using data at a snapshot point for comparison

OUTPUT COUNT

Return only the count of differences

OUTPUT LIMIT number

Return at most number difference rows after final primary-key ordering. 0 is valid and returns no rows; a value greater than 9223372036854775807 is rejected with OUTPUT LIMIT is out of range.

OUTPUT FILE 'path'

Export differences as SQL file to specified directory, supports local path or Stage path (e.g., stage://stage_name/)

OUTPUT AS table_name

Create a persistent ordinary table containing the diff. The table contains __mo_diff_source, __mo_diff_flag, and the selected value columns. The destination must not exist, cannot use a snapshot option, and requires CREATE TABLE on its database.

OUTPUT SUMMARY

Return three rows (INSERTED, DELETED, UPDATED) with columns metric, target-table count, and base-table count instead of row images.

COLUMNS (col1, col2, ...)

Return exactly the listed value columns; primary-key columns are not implicitly included. Cannot be combined with OUTPUT FILE.

Output Column Description

Default output includes the following columns:

Column Name

Description

diff target against base

Shows the table names being compared

flag

Difference type: INSERT, DELETE, or UPDATE

Other columns

All visible columns of the table

OUTPUT AS

OUTPUT AS table_name materializes the diff as a persistent ordinary table. It does not create a data branch and is visible to other sessions after the statement commits. The output keeps the requested value-column order and types, and adds two metadata columns populated by the diff operation:

  • __mo_diff_source: the source table name for the row in the diff.

  • __mo_diff_flag: INSERT, DELETE, or UPDATE.

If a user column has one of these names, MatrixOne chooses a collision-safe suffixed name. COLUMNS (...) controls the visible value columns in the same way as a result-set diff; primary-key columns are not added unless explicitly requested. The destination may be qualified with a database name, must not already exist, and cannot be a snapshot. Normal CREATE TABLE privileges are checked. If schema validation or table creation fails, the partially created output table is removed atomically.

DROP DATABASE IF EXISTS diff_output_demo;
CREATE DATABASE diff_output_demo;
USE diff_output_demo;
CREATE TABLE base (id INT PRIMARY KEY, value VARCHAR(20));
INSERT INTO base VALUES (1, 'old');
CREATE TABLE target (id INT PRIMARY KEY, value VARCHAR(20));
INSERT INTO target VALUES (1, 'new'), (2, 'added');
DATA BRANCH DIFF target AGAINST base COLUMNS (value) OUTPUT AS diff_rows;
SHOW COLUMNS FROM diff_rows;
SELECT __mo_diff_flag, __mo_diff_source, value FROM diff_rows ORDER BY value;
DROP TABLE diff_rows;
DROP TABLE target;
DROP TABLE base;
DROP DATABASE diff_output_demo;

Usage Notes

LCA (Lowest Common Ancestor)

The system automatically detects the branch relationship between two tables:

  1. No LCA: Two tables have no common ancestor, directly compare all data

  2. Has LCA: Two tables have a common ancestor, calculate incremental differences based on ancestor

  3. Self as LCA: One table is the ancestor of the other

For no-LCA comparisons, rows unique to either side and differing images of the same key are labeled as that side’s INSERT. For lineage-aware comparisons, INSERT, DELETE, and UPDATE describe changes relative to the common ancestor.

Persisting and ordering output

  • OUTPUT AS materializes the result as a normal table. It fails if the destination already exists, if a destination snapshot is specified, or if the caller lacks CREATE TABLE on the destination database.

  • The persisted table exposes __mo_diff_source and __mo_diff_flag in place of the display-only source/flag headings.

  • OUTPUT AS participates in an explicit transaction: both the table and its materialized rows persist on COMMIT and disappear on ROLLBACK. An empty diff still creates an empty table on commit.

  • If source columns already use __mo_diff_source or __mo_diff_flag, the generated metadata columns are renamed with an available numeric suffix such as __mo_diff_source_1; the source columns keep their names. Snapshot comparisons materialize the schema visible at the selected historical endpoint, and an incompatible historical mapping fails before the destination table is created.

  • OUTPUT LIMIT N applies to the final result ordered by the table primary key; it does not expose internal storage order.

  • OUTPUT SUMMARY returns columns metric, <target_table>, and <base_table>, with rows INSERTED, DELETED, and UPDATED. It does not return row images.

The LCA resolution and collect-range computation work across branches of arbitrary DAG depth, not just direct parent-child relationships. Multi-fork trees with complex branching histories produce correct diffs across any pair of nodes (siblings, cousins, cross-subtree).

Supported Table Types

  • Tables with primary key (recommended)

  • Tables with composite primary key

  • Tables without primary key (using hidden fake primary key)

Examples

Example 1: Basic Difference Comparison

Compare two tables without a common ancestor:

-- Expected-Rows: 0
CREATE DATABASE test;
-- Expected-Rows: 0
USE test;

-- Expected-Rows: 0
CREATE TABLE test.t1 (a INT PRIMARY KEY, b VARCHAR(10));
-- Expected-Rows: 0
INSERT INTO test.t1 VALUES (1, '1'), (2, '2'), (3, '3');

-- Expected-Rows: 0
CREATE TABLE test.t2 (a INT PRIMARY KEY, b VARCHAR(10));
-- Expected-Rows: 0
INSERT INTO test.t2 VALUES (1, '1'), (2, '2'), (4, '4');

-- Expected-Rows: 2
DATA BRANCH DIFF test.t2 AGAINST test.t1;
+-------------------+--------+------+------+
| diff t2 against t1 | flag   | a    | b    |
+-------------------+--------+------+------+
| t1                | INSERT |    3 | 3    |
| t2                | INSERT |    4 | 4    |
+-------------------+--------+------+------+

-- Expected-Rows: 2
DATA BRANCH DIFF test.t1 AGAINST test.t2;
+-------------------+--------+------+------+
| diff t1 against t2 | flag   | a    | b    |
+-------------------+--------+------+------+
| t1                | INSERT |    3 | 3    |
| t2                | INSERT |    4 | 4    |
+-------------------+--------+------+------+

-- Expected-Rows: 0
DROP TABLE test.t1;
-- Expected-Rows: 0
DROP TABLE test.t2;

Example 2: Compare Branch Tables (With Common Ancestor)

-- Expected-Rows: 0
CREATE TABLE test.t0 (a INT PRIMARY KEY, b INT);
-- Expected-Rows: 0
INSERT INTO test.t0 VALUES (1, 1), (2, 2), (3, 3);

-- Expected-Rows: 0
DATA BRANCH CREATE TABLE test.t1 FROM test.t0;
-- Expected-Rows: 0
INSERT INTO test.t1 VALUES (4, 4);

-- Expected-Rows: 0
DATA BRANCH CREATE TABLE test.t2 FROM test.t0;
-- Expected-Rows: 0
INSERT INTO test.t2 VALUES (5, 5);

-- Expected-Rows: 2
DATA BRANCH DIFF test.t2 AGAINST test.t1;
+-------------------+--------+------+------+
| diff t2 against t1 | flag   | a    | b    |
+-------------------+--------+------+------+
| t1                | INSERT |    4 |    4 |
| t2                | INSERT |    5 |    5 |
+-------------------+--------+------+------+

-- Expected-Rows: 0
DROP TABLE test.t0;
-- Expected-Rows: 0
DROP TABLE test.t1;
-- Expected-Rows: 0
DROP TABLE test.t2;

Example 3: Compare Using Snapshots

-- Expected-Rows: 0
CREATE TABLE test.t1 (a INT PRIMARY KEY, b INT);
-- Expected-Rows: 0
INSERT INTO test.t1 VALUES (1, 1), (2, 2);

-- Expected-Rows: 0
CREATE SNAPSHOT sp1 FOR TABLE test t1;

-- Expected-Rows: 0
INSERT INTO test.t1 VALUES (3, 3);
-- Expected-Rows: 1
UPDATE test.t1 SET b = 10 WHERE a = 1;

-- Expected-Rows: 0
CREATE SNAPSHOT sp2 FOR TABLE test t1;

-- Expected-Rows: 2
DATA BRANCH DIFF test.t1{SNAPSHOT='sp2'} AGAINST test.t1{SNAPSHOT='sp1'};
+--------------------+--------+------+------+
| diff t1 against t1 | flag   | a    | b    |
+--------------------+--------+------+------+
| t1                 | UPDATE |    1 |   10 |
| t1                 | INSERT |    3 |    3 |
+--------------------+--------+------+------+

-- Expected-Rows: 0
DROP SNAPSHOT sp1;
-- Expected-Rows: 0
DROP SNAPSHOT sp2;
-- Expected-Rows: 0
DROP TABLE test.t1;

Note

When comparing two snapshots of the same table, the system compares the data images captured at the two snapshot points. Rows are returned in ascending primary-key order: the UPDATE for a=1 (with the new value b=10) appears first, followed by the newly inserted row a=3.

Example 4: Get Only Difference Count

-- Expected-Rows: 0
CREATE TABLE test.t1 (a INT PRIMARY KEY, b INT);
-- Expected-Rows: 0
INSERT INTO test.t1 SELECT result, result FROM generate_series(1, 1000) g;

-- Expected-Rows: 0
DATA BRANCH CREATE TABLE test.t2 FROM test.t1;
-- Expected-Rows: 0
INSERT INTO test.t2 SELECT result, result FROM generate_series(1001, 2000) g;
-- Expected-Rows: 100
DELETE FROM test.t2 WHERE a <= 100;

-- Expected-Rows: 1
DATA BRANCH DIFF test.t2 AGAINST test.t1 OUTPUT COUNT;
+----------+
| COUNT(*) |
+----------+
|     1100 |
+----------+

-- Expected-Rows: 0
DROP TABLE test.t1;
-- Expected-Rows: 0
DROP TABLE test.t2;

Example 5: Limit Returned Rows

-- Expected-Rows: 0
CREATE TABLE test.t1 (a INT PRIMARY KEY, b INT);
-- Expected-Rows: 0
INSERT INTO test.t1 SELECT result, result FROM generate_series(1, 100) g;

-- Expected-Rows: 0
DATA BRANCH CREATE TABLE test.t2 FROM test.t1;
-- Expected-Rows: 0
INSERT INTO test.t2 SELECT result, result FROM generate_series(101, 200) g;

-- Expected-Rows: 5
DATA BRANCH DIFF test.t2 AGAINST test.t1 OUTPUT LIMIT 5;
+--------------------+--------+------+------+
| diff t2 against t1 | flag   | a    | b    |
+--------------------+--------+------+------+
| t2                 | INSERT |  101 |  101 |
| t2                 | INSERT |  102 |  102 |
| t2                 | INSERT |  103 |  103 |
| t2                 | INSERT |  104 |  104 |
| t2                 | INSERT |  105 |  105 |
+--------------------+--------+------+------+

-- Expected-Rows: 0
DROP TABLE test.t1;
-- Expected-Rows: 0
DROP TABLE test.t2;

Note

OUTPUT LIMIT returns the first N rows after final primary-key ordering.

Example 6: Export Differences as SQL File

-- Expected-Rows: 0
CREATE TABLE test.t1 (a INT PRIMARY KEY, b INT);
-- Expected-Rows: 0
INSERT INTO test.t1 VALUES (1, 1), (2, 2);

-- Expected-Rows: 0
DATA BRANCH CREATE TABLE test.t2 FROM test.t1;
-- Expected-Rows: 0
INSERT INTO test.t2 VALUES (3, 3);
-- Expected-Rows: 1
UPDATE test.t2 SET b = 10 WHERE a = 1;
-- Expected-Rows: 1
DELETE FROM test.t2 WHERE a = 2;

-- Expected-Rows: 1
DATA BRANCH DIFF test.t2 AGAINST test.t1 OUTPUT FILE '/tmp/';
+------------------------------------------------------------------------------------------+-----------------------------------------------+
| FILE SAVED TO                                                                            | HINT                                          |
+------------------------------------------------------------------------------------------+-----------------------------------------------+
| /tmp/diff_t2_t1_<UTC-YYYYMMDD_HHMMSS>_<UUID>.sql                                         | DELETE FROM `test`.`t1`, INSERT INTO `test`.`t1` |
+------------------------------------------------------------------------------------------+-----------------------------------------------+

-- Expected-Rows: 0
DROP TABLE test.t1;
-- Expected-Rows: 0
DROP TABLE test.t2;

The shown path is a pattern: MatrixOne generates the UTC timestamp and UUID for every invocation. The v4.2 output is a .sql patch containing the required DELETE FROM ... and INSERT INTO ... statements.

Replaying Patch Files

Replay SQL file (incremental sync):

mysql -h <mo_host> -P <mo_port> -u <user> -p <db_name> < diff_t2_t1_<UTC-YYYYMMDD_HHMMSS>_<UUID>.sql

Example 6b: Export Differences to Stage (Object Storage)

Stage is a logical object in MatrixOne for connecting to external storage (such as S3, HDFS). You can output difference files directly to object storage for cross-cluster/cross-environment data synchronization.

-- Expected-Rows: 0
CREATE STAGE my_stage URL = 's3://my-bucket/diff-output/?region=us-east-1&access_key_id=xxx&secret_access_key=yyy';

DATA BRANCH DIFF test.t2 AGAINST test.t1 OUTPUT FILE 'stage://my_stage/';
+-------------------------------------------------+------------------------------------------+
| FILE SAVED TO                                   | HINT                                     |
+-------------------------------------------------+------------------------------------------+
| stage://my_stage/diff_t2_t1_<UTC-YYYYMMDD_HHMMSS>_<UUID>.sql | DELETE FROM `test`.`t1`, INSERT INTO `test`.`t1` |
+-------------------------------------------------+------------------------------------------+

SELECT load_file(CAST('stage://my_stage/diff_t2_t1_<UTC-YYYYMMDD_HHMMSS>_<UUID>.sql' AS DATALINK));

-- Expected-Rows: 0
DROP STAGE my_stage;

Advantages of using Stage:

  • Security: No need to expose AK/SK in every SQL statement; administrators configure once

  • Convenience: Encapsulate complex URL paths into simple object names

  • Cross-cluster sync: Source writes to object storage, target reads and executes directly

Example 7: Detect Update Operations

-- Expected-Rows: 0
CREATE TABLE test.t0 (a INT PRIMARY KEY, b INT, c INT);
-- Expected-Rows: 0
INSERT INTO test.t0 SELECT result, result, result FROM generate_series(1, 100) g;

-- Expected-Rows: 0
DATA BRANCH CREATE TABLE test.t1 FROM test.t0;
-- Expected-Rows: 3
UPDATE test.t1 SET c = c + 1 WHERE a IN (1, 50, 100);

-- Expected-Rows: 0
DATA BRANCH CREATE TABLE test.t2 FROM test.t0;
-- Expected-Rows: 3
UPDATE test.t2 SET c = c + 2 WHERE a IN (1, 25, 75);

-- Compare differences, will detect conflicting updates
-- Expected-Rows: 6
DATA BRANCH DIFF test.t2 AGAINST test.t1;
+--------------------+--------+------+------+------+
| diff t2 against t1 | flag   | a    | b    | c    |
+--------------------+--------+------+------+------+
| t2                 | UPDATE |    1 |    1 |    3 |
| t1                 | UPDATE |    1 |    1 |    2 |
| t2                 | UPDATE |   25 |   25 |   27 |
| t1                 | UPDATE |   50 |   50 |   51 |
| t2                 | UPDATE |   75 |   75 |   77 |
| t1                 | UPDATE |  100 |  100 |  101 |
+--------------------+--------+------+------+------+

-- Expected-Rows: 0
DROP TABLE test.t0;
-- Expected-Rows: 0
DROP TABLE test.t1;
-- Expected-Rows: 0
DROP TABLE test.t2;

Example 8: Difference Comparison for Composite Primary Key Tables

-- Expected-Rows: 0
CREATE TABLE test.orders (
    tenant_id INT,
    order_code VARCHAR(8),
    amount DECIMAL(12,2),
    PRIMARY KEY (tenant_id, order_code)
);

-- Expected-Rows: 0
INSERT INTO test.orders VALUES
    (100, 'A100', 120.50),
    (100, 'A101', 80.00),
    (101, 'B200', 305.75);

-- Expected-Rows: 0
DATA BRANCH CREATE TABLE test.orders_branch FROM test.orders;

-- Make modifications on branch
-- Expected-Rows: 1
UPDATE test.orders_branch SET amount = 130.50 WHERE tenant_id = 100 AND order_code = 'A100';
-- Expected-Rows: 1
DELETE FROM test.orders_branch WHERE tenant_id = 100 AND order_code = 'A101';
-- Expected-Rows: 0
INSERT INTO test.orders_branch VALUES (102, 'C300', 512.25);

-- Compare differences
-- Expected-Rows: 3
DATA BRANCH DIFF test.orders_branch AGAINST test.orders;
+-----------------------------------+--------+-----------+------------+--------+
| diff orders_branch against orders | flag   | tenant_id | order_code | amount |
+-----------------------------------+--------+-----------+------------+--------+
| orders_branch                     | UPDATE |       100 | A100       | 130.50 |
| orders_branch                     | DELETE |       100 | A101       |  80.00 |
| orders_branch                     | INSERT |       102 | C300       | 512.25 |
+-----------------------------------+--------+-----------+------------+--------+

-- Expected-Rows: 0
DROP TABLE test.orders;
-- Expected-Rows: 0
DROP TABLE test.orders_branch;
-- Expected-Rows: 0
DROP DATABASE test;

Example 9: COLUMNS projection

COLUMNS (col1, ..., colN) returns exactly the requested value columns. Primary-key columns remain part of internal matching and ordering, but appear only when explicitly requested.

DROP DATABASE IF EXISTS data_branch_diff_columns_demo;
CREATE DATABASE data_branch_diff_columns_demo;
USE data_branch_diff_columns_demo;

CREATE TABLE c1 (
    id INT PRIMARY KEY,
    name VARCHAR(30),
    balance DECIMAL(12,2),
    created_at TIMESTAMP,
    birthday DATE
);
INSERT INTO c1 VALUES
    (1, 'alice', 1000.50, '2024-01-01 10:00:00', '1990-03-15'),
    (2, 'bob',   2000.75, '2024-01-02 11:00:00', '1985-07-20'),
    (3, 'carol', 3000.00, '2024-01-03 12:00:00', '1992-11-08');

DATA BRANCH CREATE TABLE c1_br FROM c1;
UPDATE c1_br SET balance = 1500.50, name = 'alice_v2' WHERE id = 1;
DELETE FROM c1_br WHERE id = 2;
INSERT INTO c1_br VALUES (4, 'dave', 4000.00, '2024-02-01 09:00:00', '1988-12-25');

-- Full diff, all columns.
DATA BRANCH DIFF c1_br AGAINST c1;

-- Project only the "name" column: id is not included in the visible result.
DATA BRANCH DIFF c1_br AGAINST c1 COLUMNS (name);

-- Project two non-PK columns.
DATA BRANCH DIFF c1_br AGAINST c1 COLUMNS (name, balance);

-- Request the PK explicitly when it must appear in the result.
DATA BRANCH DIFF c1_br AGAINST c1 COLUMNS (id, balance);

-- Combine COLUMNS with OUTPUT LIMIT.
DATA BRANCH DIFF c1_br AGAINST c1 COLUMNS (balance) OUTPUT LIMIT 5;

DROP TABLE c1_br;
DROP TABLE c1;
DROP DATABASE data_branch_diff_columns_demo;

Example 10: Linear Chain Diff (Non-Adjacent Branches)

DATA BRANCH DIFF works across non-adjacent levels of a linear branch chain (e.g., t0 -> t1 -> t2 -> t3). When diffing t3 against t0, the LCA is resolved to t0 by walking through the intermediate clone edges. The chain shape exercises a different LCA code path than a flat fork. Wrap-around intermediates (t1, t2) may be invisible to the diff: only mutations on t0 and t3 that occurred after the relevant branch points are surfaced.

DROP DATABASE IF EXISTS test_chain_diff;
CREATE DATABASE test_chain_diff;
USE test_chain_diff;

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

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

-- Write on leaf t2 only
INSERT INTO t2 VALUES (4, 4), (5, 5);
UPDATE t2 SET b = b + 100 WHERE a = 1;
DELETE FROM t2 WHERE a = 2;

-- Non-adjacent diff: t2 against t0 (LCA = t0)
DATA BRANCH DIFF t2 AGAINST t0 OUTPUT SUMMARY;

-- Adjacent diff: t2 against t1 (LCA = t1)
DATA BRANCH DIFF t2 AGAINST t1 OUTPUT SUMMARY;

-- Reverse direction
DATA BRANCH DIFF t0 AGAINST t2 OUTPUT SUMMARY;

DROP TABLE t2;
DROP TABLE t1;
DROP TABLE t0;
DROP DATABASE test_chain_diff;

Example 11: Multi-Fork Tree Diff

DATA BRANCH DIFF works across complex multi-fork branch trees (arbitrary DAG depth). The LCA resolution handles sibling leaves, cousins, cross-subtree pairs, and pairs at different depths. Each diff pair exercises a structurally distinct cross-section of the tree. The following is abbreviated pseudocode, not a directly executable SQL script: nodes t3 through t18 are intentionally omitted. See test/distributed/cases/git4data/branch/diff/diff_13.sql in the MatrixOne source tree for the complete executable setup.

Tree shape (4 levels, 19 nodes):

                    t0  (root)
               /    |    \\
              t1    t2    t3        (level 2)
            /  \\    |    /  \\
          t4   t5  t6  t7   t8      (level 3)
         / \\  / \\  / \\ / \\  / \\
       t9 t10 t11 t12 t13 t14 t15 t16 t17 t18  (level 4, 10 leaves)

DROP DATABASE IF EXISTS test_tree_diff;
CREATE DATABASE test_tree_diff;
USE test_tree_diff;

CREATE TABLE t0(a INT PRIMARY KEY, b INT);
INSERT INTO t0 SELECT *, * FROM generate_series(1, 20) g;

-- Build tree top-down with IUD on each node
DATA BRANCH CREATE TABLE t1 FROM t0;
INSERT INTO t1 VALUES (101, 101);
UPDATE t1 SET b = 10000 WHERE a = 1;

DATA BRANCH CREATE TABLE t2 FROM t0;
INSERT INTO t2 VALUES (102, 102);
UPDATE t2 SET b = 20000 WHERE a = 2;

-- Pseudocode: create t3, t4-t8, and leaves t9-t18 as shown in diff_13.sql.

-- Leaf vs root: skip 3 edges
DATA BRANCH DIFF t9 AGAINST t0 OUTPUT SUMMARY;

-- Leaf vs sibling leaf: share grandparent
DATA BRANCH DIFF t9 AGAINST t10 OUTPUT SUMMARY;

-- Leaf vs cousin leaf: share great-grandparent
DATA BRANCH DIFF t9 AGAINST t11 OUTPUT SUMMARY;

-- Leaf vs cross-subtree cousin: opposite subtrees
DATA BRANCH DIFF t9 AGAINST t13 OUTPUT SUMMARY;

-- Reverse-direction deep cross-subtree pair
DATA BRANCH DIFF t18 AGAINST t9 OUTPUT SUMMARY;

DROP DATABASE test_tree_diff;

Note

The full tree construction and diff pairs are shown in the test suite. The key properties are:

  • LCA resolution works for any pair of nodes regardless of depth difference.

  • Diffs are mirror images when reversing direction.

  • Snapshot-scoped chain diffs replay at specific time points on each side.

  • The DAG walk is stable at arbitrary depths (depth 3 and beyond).

Notes

  1. Schema evolution compatibility: DIFF accepts schema evolution only when column identity and key semantics remain provable. The following matrix applies to both DIFF and MERGE; for MERGE, the source table is merged into the destination table.

    Schema change

    DIFF/MERGE behavior

    Pure column reorder with unchanged identities and attributes

    Supported

    Column rename with lineage that MatrixOne can prove

    Supported

    Target/source-only visible column with an explicit primary key and compatible definition

    Supported; DIFF includes it, while MERGE writes only columns shared with the destination

    A source/base column is missing from the target

    Rejected

    Drop and add a column with the same name (new identity)

    Rejected

    Type, nullability, generated-column definition, or primary-key mismatch

    Rejected

    Target-only column on a table that uses a hidden fake primary key

    Rejected

    A destination table named by OUTPUT AS is a new ordinary table and is not part of this schema-compatibility check.

  2. Primary Key Requirement: Although tables without primary keys are supported, it is recommended to use tables with primary keys for more accurate difference results.

  3. Performance Considerations: For large tables, difference comparison may take a long time. It is recommended to use OUTPUT COUNT first to understand the scale of differences.

  4. Snapshot Validity: When comparing using snapshots, ensure the snapshots exist and are valid.

  5. Output File: For a local path, MatrixOne creates missing parent directories with MkdirAll when the service process has permission; the resulting path must be writable. Each generated SQL filename includes a UTC timestamp and UUID and can be replayed to synchronize data.

  6. LCA Detection: The system automatically detects LCA without manual specification. The existence of LCA affects how differences are calculated.