'Duplicate key' error when update column with list data type and table has a primary key

[lpfy]

What happens?

This behaviour happens for both Python and WASM (https://shell.duckdb.org/) environments. There is no way to update the column with the list data type and the table has a primary key. The UPDATE query always return 'Duplicate key' error.

To Reproduce

create or replace table test (
	aid INT, 
        comments TEXT,
	alist STRUCT(username VARCHAR, lid INT)[],
	PRIMARY KEY (aid)
);

insert into test select 1, 'abc', [{username:'t1',lid:1},{username:'t2',lid:2}];
insert into test select 2, 'def', [{username:'t3',lid:3},{username:'t4',lid:4}];
UPDATE test SET comments = 'test test' WHERE aid = 1;
UPDATE test SET alist = [{username:'t5',lid:5},{username:'t6',lid:6}] WHERE aid = 1;

the last update query will return the following error:

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

I am not updating the primary key column, and the comments column can be updated, just the column with list data type is causing the problem.

OS:

Browser / Windows / Linux

DuckDB Version:

0.10.2

DuckDB Client:

WASM or Python

Full Name:

Sasha Zhang

Affiliation:

Unisa

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

[szarnyasg]

Hi @lpfy, thanks for raising this issue. As described in this (quite well-hidden) comment, updates on lists are implemented as as sequence of delete and insert operations, which can trigger duplicates. I'll update this in the documentation.

[lpfy]

@szarnyasg Thanks, I have noticed the new documentation is live. https://duckdb.org/docs/sql/data_types/list