cockroachdb / cockroachdb/cockroach

sql: error if WHERE in INSERT .. ON CONFLICT ... WHERE ... DO UPDATE is invalid, e.g., references unknown columns

Open
#119,389 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

When a non-partial or psuedo-partial (i.e., it has the predicate `WHERE true`) unique index exists for a set of columns in a table, `INSERT .. ON CONFLICT () WHERE DO ..` statements completely ignore ``. It is only checked for syntactic correctness, there is no semantic analysis performed on it. This means that invalid references for columns, functions, types, and more are allowed.

For example:

```sql
CREATE TABLE t (i INT);
CREATE UNIQUE INDEX ON t(i);

-- This INSERT should fail because column j does not exist in table t,
-- but it succeeds.
--
-- In Postgres error is:
--
-- psql:tmp.sql:5: ERROR: 42703: column "j" does not exist
-- LINE 1: INSERT INTO t VALUES (1) ON CONFLICT (i) WHERE j > 0 DO NOTH...
-- LOCATION: errorMissingColumn, parse_relation.c:3648
INSERT INTO t VALUES (1) ON CONFLICT (i) WHERE j > 0 DO NOTHING;
```

Jira issue: CRDB-36178

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.