cockroachdb / cockroachdb/cockroach

sql: no rows returned in `INSERT .. ON CONFLICT` with subquery in `RETURNING` clause

Open
#105,640 0 comments 0 reactions 0 assignees View on GitHub
C-bug T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

Consider the table and `INSERT` statement:

```sql
CREATE TABLE kv (k INT PRIMARY KEY, v INT);

INSERT INTO kv VALUES (1, 10)
ON CONFLICT (k) DO UPDATE SET v = 10
RETURNING k, v, (SELECT (k, v) FROM kv foo WHERE foo.k = kv.k);
```

In Postgres, the `INSERT` statement returns:

```
k | v | row
---+----+------
1 | 10 | NULL
(1 row)
```

In CRDB on v21.2 and later, the `INSERT` statement incorretly returns:

```
INSERT 0 0
```

In CRDB v21.1, the `INSERT` statement incorrectly returns:

```
k | v | ?column?
----+---+-----------
(0 rows)
```

So it looks like this has been buggy for many past releases, in different ways.

Jira issue: CRDB-29135

Contributor guide

Open the contributing guide

Research direction

Start by running the SQL reproduction from the issue against CockroachDB and compare its result with the documented PostgreSQL result. The payload names no repository files or tests, so locate the SQL execution or regression-test entry point that covers INSERT ... ON CONFLICT with RETURNING subqueries. Done means the statement returns the expected row and value consistently.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.