cockroachdb / cockroachdb/cockroach
sql: warn/guard again misuse of `INSERT .. ON CONFLICT` and partial indexes
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
**Describe the problem**
Postgres offers [two ways](https://www.postgresql.org/docs/current/sql-insert.html) to express conditional upserts with `INSERT INTO`:
1. `ON CONFLICT ... WHERE ... DO UPDATE`
2. `ON CONFLICT ... DO UPDATE ... WHERE`
(Though the Postgres docs say in style 1, the `WHERE` only takes an `index_predicate`, which I'm not sure what the difference is)
Most users use style 2, which works fine. However, with 1, the condition in the `WHERE` clause is **silently dropped**.
We hit this because with the Rust [Diesel ORM](http://diesel.rs/), the only way to express such a query appears to be with style 1.
We're tracking this in https://github.com/oxidecomputer/omicron/issues/5047.
**To Reproduce**
Start up a single-node cluster:
```
./cockroach start-single-node --insecure
```
Then, connect to it with `cockroach sql` in another terminal and issue the following commands:
```
> CREATE TABLE foo (id UUID PRIMARY KEY, data INT NOT NULL);
> INSERT INTO foo (id, data) VALUES ('fe0abe6b-f07a-43c4-a82f-9258a6ed89cc', 1);
> INSERT INTO foo (id, data) VALUES ('fe0abe6b-f07a-43c4-a82f-9258a6ed89cc', 1) ON CONFLICT (id) WHERE data = 2 DO UPDATE SET data = 3;
> SELECT * FROM foo;
```
The output of the `SELECT` command is:
```
id | data
---------------------------------------+-------
fe0abe6b-f07a-43c4-a82f-9258a6ed89cc | 3
```
It is even possible to pass in a column that does not exist:
```
> INSERT INTO foo (id, data) VALUES ('fe0abe6b-f07a-43c4-a82f-9258a6ed89cc', 1) ON CONFLICT (id) WHERE non_existent = 1 DO UPDATE SET data = 4;
> SELECT * FROM foo;
id | data
---------------------------------------+-------
fe0abe6b-f07a-43c4-a82f-9258a6ed89cc | 4
```
**Expected behavior**
I expected to either see `data = 1` for the row, or for the query to be rejected.
**Environment:**
- CockroachDB version: 23.2.0 (also repros with v22.1.9)
- Server OS: Linux x86_64, though I doubt this is OS-specific
- Client app: `cockroach sql`, as documented above
Jira issue: CRDB-36038
Contributor guide
Assessment
This issue has not been assessed yet.