cockroachdb / cockroachdb/cockroach

sql/opt: partial and unvalidated unique keys allow incorrect GROUP BY results

Open
#175,471 1 comment 0 reactions 1 assignee Assigned to @bowencrl View on GitHub
A-sql-optimizer C-bug O-agent T-sql-queries
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.