Query returns rows that don't match its WHERE clause on a table with a hash-sharded primary key
Nobody has claimed this yet.
Assessment
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Newbie friendliness
- 48/100
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
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
- No Dockerfile or Docker Compose file
- No pull request template
- Read the contributing guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
More from cockroachdb/cockroach
-
C-bug O-community X-blathers-untriaged
Difficulty 2/5 1-3 hours Newbie friendliness 76/100
cockroachdb/cockroach#176245 · 1 comment ·
-
sql: pg_database.dattablespace is 0 but pg_tablespace.oid is 1663, so they no longer joinPossibly taken @1AbdullahButt claimed this 14 days ago. OpenC-bug O-community X-blathers-triaged X-move-internal
Difficulty 2/5 1-3 hours Newbie friendliness 68/100
cockroachdb/cockroach#176228 · 1 comment ·
-
PG compat: overflow in integer power calculation triggers incompatible SQLSTATEMay be free again @darshpatel1052 claimed this 18 days ago, and no pull request is open. OpenC-bug O-community X-blathers-untriaged
Difficulty 2/5 1-3 hours Newbie friendliness 62/100
cockroachdb/cockroach#176223 · 2 comments ·
-
util/log: bufferedSinkCloser.Close leaks a goroutine after timeoutPossibly taken @dajiaohuang claimed this 14 days ago. OpenC-bug O-community X-blathers-untriaged
Difficulty 1/5 Under an hour Newbie friendliness 84/100
cockroachdb/cockroach#176210 · 1 comment ·
-
C-bug O-community T-sql-queries X-blathers-triaged X-move-internal
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
cockroachdb/cockroach#176150 · 1 comment ·
All issues in cockroachdb/cockroach
Similar issues
-
area/import-export area/projects concern/ux
Difficulty 2/5 1-3 hours Newbie friendliness 68/100
go-vikunja/vikunja#4455 · 1 comment ·
Maintainers usually reply within 2 days
-
Dependabot PRs no longer merge automaticallyPossibly taken @dwisiswant0 claimed this today. Open
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
projectdiscovery/urlfinder#160 ·
Maintainers usually reply within 1 day
-
database
Difficulty 2/5 1-3 hours Newbie friendliness 72/100
chnm/legal-modernism#450 ·
Maintainers usually reply within 1 day
-
tech-debt
Difficulty 2/5 1-3 hours Newbie friendliness 76/100
Maintainers usually reply within 1 day
-
bug ready-for-triage ready-to-code
Difficulty 2/5 1-3 hours Newbie friendliness 62/100
RedHatInsights/insights-ingress-go#946 · 4 comments ·
Maintainers usually reply within 1 day