cockroachdb / cockroachdb/cockroach
sql/opt: partial and unvalidated unique keys allow incorrect GROUP BY results
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
**Describe the problem**
Grouping validation treats partial unique indexes and unvalidated unique constraints as guarantees of uniqueness across the whole table.
This allows queries to select ungrouped columns whose values are ambiguous. CockroachDB silently adds those columns to the grouping keys, splitting groups instead of reporting an error.
**To reproduce**
Partial unique index:
```sql
CREATE TABLE grouping_partial_repro (
a INT PRIMARY KEY,
b INT NOT NULL,
c INT
);
CREATE UNIQUE INDEX grouping_partial_repro_idx
ON grouping_partial_repro (b) WHERE c > 0;
INSERT INTO grouping_partial_repro
VALUES (1, 10, -1), (2, 10, -2);
SELECT b, c, count(*)
FROM grouping_partial_repro
GROUP BY b;
```
Both rows are outside the index predicate, so duplicate `b` values are allowed.
Unvalidated unique constraint:
```sql
SET experimental_enable_unique_without_index_constraints = true;
CREATE TABLE grouping_unvalidated_repro (
a INT PRIMARY KEY,
b INT NOT NULL,
c INT
);
INSERT INTO grouping_unvalidated_repro
VALUES (1, 10, -1), (2, 10, -2);
ALTER TABLE grouping_unvalidated_repro
ADD CONSTRAINT unique_b UNIQUE WITHOUT INDEX (b) NOT VALID;
SELECT b, c, count(*)
FROM grouping_unvalidated_repro
GROUP BY b;
```
`NOT VALID` skips checking existing rows, so the duplicate values remain.
**Actual behavior**
Both queries return these rows, in either order:
```text
b c count
10 -1 1
10 -2 1
```
**Expected behavior**
Both queries should fail with SQLSTATE `42803`:
```text
column "c" must appear in the GROUP BY clause
or be used in an aggregate function
```
**Additional context**
`allowImplicitGroupingColumn` does not exclude partial indexes, partial unique constraints, or unvalidated unique constraints. The optimizer’s functional-dependency builder already excludes these guarantees.
The partial-constraint check also needs to cover synthesized constraints for hash-sharded and implicitly partitioned unique indexes.
**Environment**
Reproduced with SQL logic tests on a development checkout. The affected release range has not been established.
Jira issue: CRDB-68389
Contributor guide
Research direction
Start with allowImplicitGroupingColumn and the optimizer’s functional-dependency builder, then reproduce the partial-index and NOT VALID cases as SQL logic tests. Check how partial, unvalidated, hash-sharded, and implicitly partitioned unique constraints are classified. Done means both queries reject the ungrouped column with SQLSTATE 42803 instead of splitting the groups.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, sql
- Domain
- backend, databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 55/100