dolthub / dolthub/dolt

`DOLT_QUERY_DIFF` falls back to keyless diff on cross-table or cross-database queries with matching schemas

Open
#11,621 0 comments 0 reactions 0 assignees View on GitHub
bug sql version control
Dominant language
Go
Stars
24.4k
Forks
873
Avg merge
1d 5h
Merged PRs (30d)
108

Description

When diffing two queries across different tables or databases, `DOLT_QUERY_DIFF` falls back to a keyless diff (emitting all rows as added/deleted) even when the projected columns and primary keys match.

TODO in code:
https://github.com/dolthub/dolt/blob/583165a5c83abb5be82970ce78dd56d43ed26b92/go/libraries/doltcore/sqle/dtablefunctions/dolt_query_diff.go#L214-L220

---

```sql
CREATE TABLE t (i INT PRIMARY KEY, j INT);
INSERT INTO t VALUES (1, 1), (2, 2);

CREATE TABLE tt (i INT PRIMARY KEY, j INT);
INSERT INTO tt VALUES (1, 1), (2, 3);

SELECT * FROM DOLT_QUERY_DIFF('SELECT * FROM t', 'SELECT * FROM tt');
```

```text
+--------+--------+------+------+-----------+
| from_i | from_j | to_i | to_j | diff_type |
+--------+--------+------+------+-----------+
| 1 | 1 | NULL | NULL | deleted |
| 2 | 2 | NULL | NULL | deleted |
| NULL | NULL | 1 | 1 | added |
| NULL | NULL | 2 | 3 | added |
+--------+--------+------+------+-----------+
```

Rows matching on primary key `i` should be compared and output as modified rather than treating all rows as added and deleted.

Contributor guide

No contributing guide indexed for this repository

Research direction

Start at go/libraries/doltcore/sqle/dtablefunctions/dolt_query_diff.go around lines 214-220, where the cross-table and cross-database fallback is marked TODO. Reproduce the behavior with the SQL example in this issue, then trace how projected columns and primary keys are handled. Done means matching primary-key rows are compared and reported as modified instead of all rows being reported as added or deleted.

Written by the indexing model from the issue text.

Assessment

Tech stack
go
Domain
databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Active
Clarity
Clearly specified
Newbie friendliness
75/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.