`UPDATE ...FROM` bug
- Dominant language
- Rust
- Stars
- 9.3k
- Forks
- 2.4k
- Avg merge
- 3d 7h
- Merged PRs (30d)
- 344
Description
## Summary
`UPDATE ... FROM` is not successfully updating joined column values.
## Repro steps
1) Via `datafusion-cli`, run these lines manually to see the stale updates:
```sql
create table t1(a int, b varchar, c double, d int);
create table t2(a int, b varchar, c double, d int);
insert into t1 values (1, 'zoo', 2.0, 10), (2, 'qux', 3.0, 20), (3, 'bar', 4.0, 30);
insert into t2 values (1, 'updated_b', 5.0, 40), (2, 'updated_b2', 2.5, 50), (4, 'updated_b3', 1.5, 60);
update t1 set b = t2.b, c = t2.a, d = 1 from t2 where t1.a = t2.a and t1.b > 'foo' and t2.c > 1.0;
select * from t1 order by a;
```
### Actual
```
+---+-----+-----+----+
| a | b | c | d |
+---+-----+-----+----+
| 1 | zoo | 1.0 | 1 |
| 2 | qux | 2.0 | 1 |
| 3 | bar | 4.0 | 30 |
+---+-----+-----+----+
3 row(s) fetched.
```
#### Expected
```
+---+------------+-----+----+
| a | b | c | d |
+---+------------+-----+----+
| 1 | updated_b | 1.0 | 1 |
| 2 | updated_b2 | 2.0 | 1 |
| 3 | bar | 4.0 | 30 |
+---+------------+-----+----+
3 row(s) fetched.
```
## Verification of the Expected Result
### Initial state
Table `t1` starts with:
* (1, 'zoo', 2.0, 10)
* (2, 'qux', 3.0, 20)
* (3, 'bar', 4.0, 30)
Table `t2` contains:
* (1, 'updated_b', 5.0, 40)
* (2, 'updated_b2', 2.5, 50)
* (4, 'updated_b3', 1.5, 60)
### Rows participating in the UPDATE
The `UPDATE ... FROM` statement uses the following conditions:
* `t1.a = t2.a`
* `t1.b > 'foo'`
* `t2.c > 1.0`
Rows with `a = 1` and `a = 2` satisfy all three conditions:
* `'zoo' > 'foo'` → true
* `'qux' > 'foo'` → true
* `t2.c` is greater than `1.0` for both rows
Row `a = 3` has no matching row in `t2` and is therefore not updated.
### Application of SET expressions
For rows `a = 1` and `a = 2`, the `SET` clause specifies:
* `b = t2.b` → values should become `updated_b` and `updated_b2`
* `c = t2.a` → values become `1` and `2` (stored as doubles: `1.0`, `2.0`)
* `d = 1` → both rows set to `1`
### Final expected state
After the update:
* Rows `1` and `2` are updated using values from the joined `t2` rows
* Row `3` remains unchanged
Encountered this while working on #19884
Contributor guide
Research direction
Reproduce the stale joined-column values using datafusion-cli and the SQL statement in the issue. Then locate the UPDATE ... FROM execution path and existing UPDATE tests, and add regression coverage showing that matching t2 values update t1.b, t1.c, and t1.d while unmatched rows remain unchanged.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- rust, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 48/100