Query returns rows that don't match its WHERE clause on a table with a hash-sharded primary key

Open
#176,242 2 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
48/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Active
Tech stack
go, sql
Domain
databases

Research direction

Start by reading the SQL optimizer and execution paths for hash-sharded primary keys, especially how OR predicates are combined with filters on the primary key. Reproduce the report using the supplied SQL against a table with a hash-sharded primary key, then trace why the ts <= '2025-12-01' filter is lost for shard 15. Done means the query returns 0 rows and regression coverage verifies the OR case while preserving the no-OR behavior.

Written by the indexing model from the issue text.

Description

O-community X-blathers-untriaged

Describe the problem

A query returns rows that don't match its WHERE clause on a table with a hash-sharded primary key. I noticed it because our row-level TTL job was reading rows that hadn't expired yet.

To Reproduce

CREATE TABLE t (
  ts TIMESTAMPTZ NOT NULL,
  k UUID NOT NULL,
  PRIMARY KEY (ts, k) USING HASH WITH (bucket_count = 16)
);
INSERT INTO t
  SELECT '2026-01-01'::TIMESTAMPTZ + g * INTERVAL '1 hour', md5(g::STRING)::UUID
  FROM generate_series(1, 2000) AS g;

SELECT count(*) FROM t
WHERE ts <= '2025-12-01'
  AND (crdb_internal_k_ts_shard_16 < 15
       OR (crdb_internal_k_ts_shard_16 = 15 AND ts < '2026-03-01'));

Expected behavior

0, since every row is after 2026-01-01. It returns 88. Without the OR it correctly returns 0, so the ts <= '2025-12-01' filter seems to get dropped for shard 15.

Environment:

  • CockroachDB v26.2.11 and v26.4.0
  • Client: cockroach sql

Additional context

The TTL job never deletes the wrong rows, since its delete checks expiry again, but it reads rows it shouldn't.

Dominant language
Go
Stars
32.6k
Forks
4.2k
PR merge metrics
No merged PRs in 30d

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from cockroachdb/cockroach

All issues in cockroachdb/cockroach

Similar issues

More Go issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.