cockroachdb / cockroachdb/cockroach
sql: statement summary drops DISTINCT / DISTINCT ON, making SELECT DISTINCT indistinguishable in DB Console Statements page
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
**Describe the problem**
The query summary text shown on the DB Console **SQL Activity → Statements** page (the `querySummary` field of statement statistics) omits the `DISTINCT` and `DISTINCT ON (...)` clauses. As a result, `SELECT DISTINCT * FROM t` is displayed identically to `SELECT * FROM t`.
These are correctly tracked as **two separate statement fingerprints** (the fingerprint uses the anonymized full statement, which retains `DISTINCT`), so the page shows two rows with identical-looking summary text. This makes it impossible to tell the two statements apart at a glance, and hides the fact that a `DISTINCT` (and its implied sort/dedup work) is involved.
**To Reproduce**
1. Start a single node: `cockroach start-single-node --insecure`
2. Run:
```sql
CREATE DATABASE testdb;
USE testdb;
CREATE TABLE t (id INT PRIMARY KEY, name STRING);
INSERT INTO t VALUES (1,'a'),(2,'b'),(3,'b');
SET application_name = 'distinct_demo';
SELECT * FROM t;
SELECT DISTINCT * FROM t;
SELECT DISTINCT name FROM t;
SELECT DISTINCT ON (name) id, name FROM t;
```
3. Open the DB Console → **SQL Activity → Statements**, filter Application Name to `distinct_demo`.
4. Observe that the `SELECT DISTINCT * FROM t` row displays as `SELECT * FROM t`, identical to the plain `SELECT * FROM t` row.
The mapping between the stored fingerprint and the displayed summary can be seen directly:
```sql
SELECT metadata->>'query' AS fingerprint, metadata->>'querySummary' AS query_summary
FROM crdb_internal.statement_statistics WHERE app_name = 'distinct_demo';
```
| fingerprint (`metadata->>'query'`) | query_summary (`metadata->>'querySummary'`) |
|---|---|
| `SELECT * FROM t` | `SELECT * FROM t` |
| `SELECT DISTINCT * FROM t` | `SELECT * FROM t` |
| `SELECT DISTINCT name FROM t` | `SELECT name FROM t` |
| `SELECT DISTINCT ON (name) id, name FROM t` | `SELECT id, name FROM t` |
**Expected behavior**
The query summary should retain `DISTINCT` / `DISTINCT ON (...)` so that distinct statements are visually distinguishable from their non-distinct counterparts, e.g. `SELECT DISTINCT * FROM t` and `SELECT DISTINCT id, name FROM t`.
**Root cause**
The summary is produced by `FormatStatementSummary` using the `FmtSummary` flag. In `SelectClause.Format`, the `FmtSummary` branch returns early after emitting `SELECT ... FROM ...`, before the code that would emit the `DISTINCT` / `DISTINCT ON` clause:
- [`pkg/sql/sem/tree/select.go` — `SelectClause.Format`, `FmtSummary` early return](https://github.com/cockroachdb/cockroach/blob/master/pkg/sql/sem/tree/select.go#L92-L101) (the `node.Distinct` handling immediately below is skipped)
- [`pkg/sql/sem/tree/format.go` — `FormatStatementSummary`](https://github.com/cockroachdb/cockroach/blob/master/pkg/sql/sem/tree/format.go#L910)
- [`pkg/sql/statement.go` — `StmtSummary` assignment](https://github.com/cockroachdb/cockroach/blob/master/pkg/sql/statement.go#L87)
By contrast, the fingerprint (`StmtNoConstants`) is produced with `FmtHideConstants` (not `FmtSummary`) and correctly retains `DISTINCT`, which is why the two statements are distinct fingerprints.
This `FmtSummary` branch has been present since 2021 (`ba94984c27b`), so this affects all currently supported versions; it is not a regression. It appears to be an oversight rather than intentional behavior.
**Fix considerations**
As of v26.3, statement statistics (including the summary text) are persisted to a system table. A fix to the summary format only affects newly-recorded fingerprints — it will **not** retroactively update summaries already persisted for existing fingerprints. This argues for landing the fix and backporting it to 26.3 before persisted summaries accumulate without the `DISTINCT` keyword.
**Screenshots**
**Environment:**
- CockroachDB version: reproduced on v26.2.1; code present on master
- Client app: `cockroach sql`, DB Console
Jira issue: CRDB-65374
Contributor guide
Research direction
Start in pkg/sql/sem/tree/select.go at SelectClause.Format and inspect the FmtSummary early return, then trace how pkg/sql/sem/tree/format.go and pkg/sql/statement.go use the summary. Reproduce the listed DISTINCT and non-DISTINCT statements, and verify that newly generated summaries retain DISTINCT or DISTINCT ON while fingerprints remain unchanged.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Quiet
- Clarity
- Clearly specified
- Newbie friendliness
- 72/100