dolthub / dolthub/dolt

`dolt_query_diff` assumes a specific ordering of the tables under comparison, and returns the wrong results when the order is different.

Open
#6,687 2 comments 0 reactions 2 assignees Claimed by @zachmu View on GitHub
bug correctness sql version control
Dominant language
Go
Stars
24.4k
Forks
873
Avg merge
1d 5h
Merged PRs (30d)
108

Description

Case 1: `dolt_query_diff` returns incorrect results for tables where the primary key column order does not match the schema column order

Steps to reproduce:

```
create table xy (x int, y int, primary key (y, x));
insert into xy values (1, 2), (2, 1);

call dolt_add('.');
call dolt_commit('-am');

delete from xy where x = 2;
insert into xy values (3, 0);

select * from dolt_query_diff('select * from xy as of `HEAD`', 'select * from xy');
```

Expected output:
```
+--------+--------+------+------+-----------+
| from_x | from_y | to_x | to_y | diff_type |
+--------+--------+------+------+-----------+
| 1 | 2 | NULL | NULL | deleted |
| NULL | NULL | 3 | 0 | added |
+--------+--------+------+------+-----------+
```

Actual output:
```
+--------+--------+------+------+-----------+
| from_x | from_y | to_x | to_y | diff_type |
+--------+--------+------+------+-----------+
| 2 | 1 | NULL | NULL | deleted |
| 1 | 2 | NULL | NULL | deleted |
| NULL | NULL | 3 | 0 | added |
| NULL | NULL | 1 | 2 | added |
+--------+--------+------+------+-----------+
```

This happens because the logic in `dolt_query_diff_table_function.go` finds the primary key by iterating over the schema columns in order and checking to see if they're part of the primary key. If the primary key has the columns in a different order than the schema, then the comparison logic will be incorrect.

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.