What happens?

Given I have a table with a primary key and I perform an upsert with a where condition in the update I would expect rows that don't match the condition not be present in the returned rows, this is not the case.

To Reproduce

create table foo(id int primary key, bar text);
insert into foo select 1, 'zoo';
insert into foo select 1, 'zoo' returning *;

Constraint Error: Duplicate key "id: 1" violates primary key constraint. If this is an unexpected constraint violation please double check with the known index limitations section in our documentation (https://duckdb.org/docs/sql/indexes).

insert into foo select 1, 'zoo'
on conflict (id) do
    update set bar = excluded.bar
    where excluded.bar != 'zoo'
returning *;

┌───────┬─────────┐
│  id   │   bar   │
│ int32 │ varchar │
├───────┼─────────┤
│     1 │ zoo     │
└───────┴─────────┘

insert into foo select 1, 'zoom'
on conflict (id) do
    update set bar = excluded.bar
    where excluded.bar != 'zoom'
returning *;

┌───────┬─────────┐
│  id   │   bar   │
│ int32 │ varchar │
├───────┼─────────┤
│     1 │ zoom    │
└───────┴─────────┘

insert into foo select 1, 'zoom'
on conflict (id) do
    update set bar = excluded.bar
    where id != 1
returning *;

┌───────┬─────────┐
│  id   │   bar   │
│ int32 │ varchar │
├───────┼─────────┤
│     1 │ zoom    │
└───────┴─────────┘

select * from foo;

┌───────┬─────────┐
│  id   │   bar   │
│ int32 │ varchar │
├───────┼─────────┤
│     1 │ zoo     │
└───────┴─────────┘

See in the upserts the where condition prevents the update and I would expect it to prevent anything from being returned, the stored data was never changed.

The documentation only states

The RETURNING clause may be used to return the contents of the rows that were inserted

I think it would be fair in an upsert scenario to assume it would "return the contents of the rows that were upserted"

OS:

MacOs 13.6.7 (aarch64)

DuckDB Version:

v1.1.3

DuckDB Client:

CLI (mac via homebrew)

Hardware:

M1, 16Gb ram

Full Name:

Stephen Flavin

Affiliation:

N/A

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

Yes

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• Yes, I have