cockroachdb / cockroachdb/cockroach

sql: insert on conflict fails due to rowid included in star expansion

Open
#117,598 0 comments 0 reactions 0 assignees View on GitHub
A-sql-pgcompat C-bug O-pg-regress P-3 T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

During star expansion in an insert WHERE clause, the rowid is included which causes the insert to fail unexpectedly.

Repro:
```
create table insertconflicttest(key int4, fruit text);
create unique index plain on insertconflicttest(key);
insert into insertconflicttest as i values (23, 'Jackfruit') on conflict (key) do update set fruit = excluded.fruit where i.* != excluded.* returning *;
```

This is successful in postgres, but CRDB returns an error:
```
ERROR: expected tuple ((key, fruit, rowid) AS key, fruit, rowid) to have a length of 2
```

Relatedly, sometimes the rowid is also incorrectly exposed, such as in the following example:
```
insert into insertconflicttest as i values (23, 'Avocado') on conflict (key) do update set fruit = excluded.*::text returning *;
-- CRDB: 23 | (23,Avocado,933152993262665729)
-- postgres: 23 | (23,Avocado)
```

Found in pg_regress insert_conflict.sql

Jira issue: CRDB-35261

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.