Skip to content

UPDATE ...FROM bug #19950

@kosiew

Description

@kosiew

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:
 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

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't working

    Type

    No type
    No fields configured for issues without a type.

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions