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`

Open
#176,235 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
43/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Active
Tech stack
go, postgresql, sql
Domain
backend, databases

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

C-bug O-community T-sql-queries X-blathers-triaged X-move-internal

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

  1. cockroach start-single-node --insecure
  2. 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
  1. An UPDATE with 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):

  1. 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

  2. extractJSONFetchValEqCondition builds a containment probe from the fetch keys with buildObject
    and generates the spans for j @> <probe>:
    https://github.com/cockroachdb/cockroach/blob/d30c905fff79ef825adc96bcc647f1872a90f2ff/pkg/sql/opt/invertedidx/json_array.go#L653-L709
    For an integer key, buildObject wraps 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 (...)) calls extractJSONFetchValEqCondition for each element.

  3. 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 document 7 is 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

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.