sql: JSONB inverted index scans for `j->N = v` (integer `N`) never fetch scalar documents, so SELECT, UPDATE and DELETE skip rows where `j` is the scalar `v`
Nobody has claimed this yet.
Assessment
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Newbie friendliness
- 43/100
Research direction
Start with pkg/sql/opt/invertedidx/json_array.go, especially extractJSONFetchValEqCondition, extractJSONFetchValContainsCondition, and extractJSONInCondition; compare their probes with scalar and array JSONB index spans in pkg/util/json/json.go. Run the existing inverted_index logic tests and add cases for scalar documents with integer fetch keys 0 and -1. Done means indexed SELECT, UPDATE, and DELETE match the primary-index results for the reported predicates.
Written by the indexing model from the issue text.
Description
Describe the problem
With an integer key, -> treats a scalar JSONB document as a one-element array: '7'::JSONB -> 0 and
'7'::JSONB -> -1 are 7, as in PostgreSQL. When the column has an inverted index, the optimizer
accelerates j->N = v by scanning the spans of j @> '[v]'. Those spans only contain keys of array
documents, and a scalar document v is indexed under its scalar key, so it is never fetched. The plan
re-applies the original filter, but only to the rows that the index returned, so scalar documents that
match the predicate are dropped from the result.
j->N @> v, j->N IN (...) and chains such as j->0->0 = v are affected the same way. UPDATE and
DELETE with these predicates skip the matching scalar rows and report success. Forcing the primary
index gives the correct rows. No error or warning is reported.
To Reproduce
cockroach start-single-node --insecure- In
cockroach sql --insecure(default settings):
CREATE TABLE e (id INT PRIMARY KEY, j JSONB, done BOOL DEFAULT false, INVERTED INDEX e_j_idx (j));
INSERT INTO e (id, j) VALUES (1, '"red"'), (2, '["red", "blue"]'), (3, '7'), (4, '[7]'), (5, 'true'),
(6, '[true, 1]'), (7, '{"0": "red"}'), (8, '[[7]]');
SELECT '7'::JSONB -> 0 AS a, '"red"'::JSONB -> -1 AS b, '[7]'::JSONB -> 0 AS c;
-- a | b | c
-- ----+-------+----
-- 7 | "red" | 7
-- default plan (index join over e_j_idx)
SELECT array_agg(id ORDER BY id) FROM e WHERE j->0 = '"red"';
SELECT array_agg(id ORDER BY id) FROM e WHERE j->0 = '7';
SELECT array_agg(id ORDER BY id) FROM e WHERE j->-1 = '7';
SELECT array_agg(id ORDER BY id) FROM e WHERE j->0 = 'true';
SELECT array_agg(id ORDER BY id) FROM e WHERE j->0 @> '7';
SELECT array_agg(id ORDER BY id) FROM e WHERE j->0 IN ('7', '"red"');
SELECT array_agg(id ORDER BY id) FROM e WHERE j->0->0 = '7';
-- the same predicates through the primary index
SELECT array_agg(id ORDER BY id) FROM e@e_pkey WHERE j->0 = '"red"';
SELECT array_agg(id ORDER BY id) FROM e@e_pkey WHERE j->0 = '7';
SELECT array_agg(id ORDER BY id) FROM e@e_pkey WHERE j->-1 = '7';
SELECT array_agg(id ORDER BY id) FROM e@e_pkey WHERE j->0 = 'true';
SELECT array_agg(id ORDER BY id) FROM e@e_pkey WHERE j->0 @> '7';
SELECT array_agg(id ORDER BY id) FROM e@e_pkey WHERE j->0 IN ('7', '"red"');
SELECT array_agg(id ORDER BY id) FROM e@e_pkey WHERE j->0->0 = '7';
Output on master (identical on v26.3.5):
| predicate | default plan (inverted index) | primary index (e@e_pkey) |
|---|---|---|
j->0 = '"red"' |
{2} |
{1,2} |
j->0 = '7' |
{4} |
{3,4} |
j->-1 = '7' |
{4} |
{3,4} |
j->0 = 'true' |
{6} |
{5,6} |
j->0 @> '7' |
{4,8} |
{3,4,8} |
j->0 IN ('7', '"red"') |
{2,4} |
{1,2,3,4} |
j->0->0 = '7' |
{8} |
{3,4,8} |
Every row missing from the default plan is a scalar document ("red", 7, true).
EXPLAIN SELECT id FROM e WHERE j->0 = '7';
distribution: local
• filter
│ filter: (j->0) = '7'
│
└── • index join
│ table: e@e_pkey
│
└── • scan
missing stats
table: e@e_j_idx
spans: 1 span
- An
UPDATEwith the same predicate skips the scalar row:
UPDATE e SET done = true WHERE j->0 = '7'; -- UPDATE 1
SELECT id, j, done FROM e WHERE id IN (3, 4) ORDER BY id;
id | j | done
-----+-----+-------
3 | 7 | f
4 | [7] | t
A DELETE does the same:
DELETE FROM e WHERE j->0 = '"red"' RETURNING id; -- returns only 2
SELECT id, j FROM e WHERE id IN (1, 2) ORDER BY id;
id | j
-----+--------
1 | "red"
CREATE TABLE firsts AS SELECT id, j FROM e WHERE j->0 = '"red"' (run before the DELETE) likewise
stores only id 2.
Expected behavior
The default plan returns the same rows as the primary index, the UPDATE changes rows 3 and 4, and
the DELETE removes rows 1 and 2.
PostgreSQL (20devel) returns {1,2}, {3,4}, {3,4}, {5,6}, {3,4,8}, {1,2,3,4} and {3,4,8}
for the seven predicates, the primary-index sets above (a GIN index cannot serve ->, so these come
from a sequential scan even with a GIN index on j and enable_seqscan = off).
Additional data / screenshots
Cause (permalinks at d30c905fff79, the current public master HEAD; the master build tested gives the
output above):
-
An integer fetch on a scalar returns the scalar for index 0 and -1:
https://github.com/cockroachdb/cockroach/blob/d30c905fff79ef825adc96bcc647f1872a90f2ff/pkg/util/json/json.go#L2106-L2120 -
extractJSONFetchValEqConditionbuilds a containment probe from the fetch keys withbuildObject
and generates the spans forj @> <probe>:
https://github.com/cockroachdb/cockroach/blob/d30c905fff79ef825adc96bcc647f1872a90f2ff/pkg/sql/opt/invertedidx/json_array.go#L653-L709
For an integer key,buildObjectwraps the value in a one-element array:
https://github.com/cockroachdb/cockroach/blob/d30c905fff79ef825adc96bcc647f1872a90f2ff/pkg/sql/opt/invertedidx/json_array.go#L952-L972
extractJSONFetchValContainsCondition(j->N @> v) builds its probes with the same function, and
extractJSONInCondition(j->N IN (...)) callsextractJSONFetchValEqConditionfor each element. -
The spans of
j @> '[7]'start with the array marker
(https://github.com/cockroachdb/cockroach/blob/d30c905fff79ef825adc96bcc647f1872a90f2ff/pkg/util/json/json.go#L1283-L1305),
while the scalar document7is indexed as<prefix> 00 01 <7>, without that marker
('7'::JSONB @> '[7]'is false, so the probe is narrower than the predicate). Integer keys mark the
expression as not tight, which re-applies the filter to the fetched rows but cannot add rows that were
never fetched:
https://github.com/cockroachdb/cockroach/blob/d30c905fff79ef825adc96bcc647f1872a90f2ff/pkg/sql/opt/invertedidx/json_array.go#L702-L707
A possible fix: when a fetch key is 0 or -1 (the positions at which a scalar matches), also include the
spans for the scalar v at the position of that key, or do not accelerate integer fetches that can
match a scalar.
The integer-key acceleration was added by #95848 (j->0 = ..., for #94666) and #96202 (j->0 @> ... and
j->0 <@ ..., for #94667). Before those changes these filters were not planned on the inverted index.
The logic tests for these filters (inverted_index, table f) contain no scalar document other than
null.
Environment:
- CockroachDB version: master edge build v27.1.0-alpha.00000000-dev-fc36d2fe578aab6d0c861e46403f029f938c5a5a (built 2026-11-08) and v26.3.5; the main reproduction gives identical output on both (the additional checks were run on master)
- Server OS: Linux x86_64 (Ubuntu 22.04), single node,
--insecure - Client app:
cockroach sql, psycopg2
Additional context
Columns that hold either one value or a list of values ("red" or ["red", "blue"]) are common, and
j->0 is a natural way to read the first value of both forms. Once an inverted index exists on the
column, the default plan of such a query misses every scalar row: UPDATE and DELETE leave those
rows unchanged, and INSERT ... SELECT / CREATE TABLE AS store incomplete results. No error is
returned.
Jira issue: CRDB-71978
- 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