cockroachdb / cockroachdb/cockroach
sql: no rows returned in `INSERT .. ON CONFLICT` with subquery in `RETURNING` clause
- 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
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