cockroachdb / cockroachdb/cockroach

sql: warn/guard again misuse of `INSERT .. ON CONFLICT` and partial indexes

Open
#119,117 17 comments 0 reactions 0 assignees View on GitHub
C-bug O-community T-sql-queries X-blathers-triaged
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

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.