cockroachdb / cockroachdb/cockroach
sql: error if WHERE in INSERT .. ON CONFLICT ... WHERE ... DO UPDATE is invalid, e.g., references unknown columns
- 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
Assessment
This issue has not been assessed yet.