apache / apache/datafusion

`UPDATE ...FROM` bug

Open
#19,950 1 comment 0 reactions 0 assignees View on GitHub
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.