cockroachdb / cockroachdb/cockroach

sql: statement summary drops DISTINCT / DISTINCT ON, making SELECT DISTINCT indistinguishable in DB Console Statements page

Open
#172,208 1 comment 0 reactions 0 assignees View on GitHub
A-sql-console-general A-sql-observability C-bug O-agent T-observability
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**

Image
Image
Image

**Environment:**
- CockroachDB version: reproduced on v26.2.1; code present on master
- Client app: `cockroach sql`, DB Console

Jira issue: CRDB-65374

Contributor guide

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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.