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`
Nessuno ha ancora preso questa issue.
Valutazione
- Difficoltà
- 4/5
- Tempo stimato
- 3-5 giorni
- Idoneità per principianti
- 43/100
Direzione di ricerca
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.
Scritto dal modello di indicizzazione a partire dal testo della issue.
Descrizione
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
- Lingua principale
- Go
- Stelle
- 32.6k
- Fork
- 4.2k
- Metriche di merge delle PR
- Nessuna PR unita negli ultimi 30g
Preparare l'ambiente
- Nessun Dockerfile né file Docker Compose
- Nessun modello di pull request
- Leggi la guida per i contributori
Come iniziare
- Leggi tutta la issue e poi la guida ai contributi del progetto.
- Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
- Fai un fork del repository e lavora su un branch.
- Apri una pull request che faccia riferimento al numero della issue.
Altre issue di cockroachdb/cockroach
-
C-bug O-community X-blathers-untriaged
Difficoltà 2/5 1-3 ore Idoneità per principianti 76/100
cockroachdb/cockroach#176245 · 1 commento ·
-
sql: pg_database.dattablespace is 0 but pg_tablespace.oid is 1663, so they no longer joinForse già presa @1AbdullahButt l’ha presa 14 giorni fa. ApertaC-bug O-community X-blathers-triaged X-move-internal
Difficoltà 2/5 1-3 ore Idoneità per principianti 68/100
cockroachdb/cockroach#176228 · 1 commento ·
-
PG compat: overflow in integer power calculation triggers incompatible SQLSTATEForse di nuovo libera @darshpatel1052 l’ha presa 18 giorni fa e non c’è nessuna pull request aperta. ApertaC-bug O-community X-blathers-untriaged
Difficoltà 2/5 1-3 ore Idoneità per principianti 62/100
cockroachdb/cockroach#176223 · 2 commenti ·
-
util/log: bufferedSinkCloser.Close leaks a goroutine after timeoutForse già presa @dajiaohuang l’ha presa 15 giorni fa. ApertaC-bug O-community X-blathers-untriaged
Difficoltà 1/5 Meno di un'ora Idoneità per principianti 84/100
cockroachdb/cockroach#176210 · 1 commento ·
-
C-bug O-community T-sql-queries X-blathers-triaged X-move-internal
Difficoltà 2/5 1-3 ore Idoneità per principianti 78/100
cockroachdb/cockroach#176150 · 1 commento ·
Tutte le issue di cockroachdb/cockroach
Issue simili
-
☢️ Bug v3
Difficoltà 2/5 1-3 ore Idoneità per principianti 82/100
I maintainer di solito rispondono entro 1 giorno
-
area/import-export area/projects concern/ux
Difficoltà 2/5 1-3 ore Idoneità per principianti 68/100
go-vikunja/vikunja#4455 · 1 commento ·
I maintainer di solito rispondono entro 2 giorni
-
Dependabot PRs no longer merge automaticallyForse già presa @dwisiswant0 l’ha presa oggi. Aperta
Difficoltà 2/5 1-3 ore Idoneità per principianti 78/100
projectdiscovery/urlfinder#160 ·
I maintainer di solito rispondono entro 1 giorno
-
database
Difficoltà 2/5 1-3 ore Idoneità per principianti 72/100
chnm/legal-modernism#450 ·
I maintainer di solito rispondono entro 1 giorno
-
tech-debt
Difficoltà 2/5 1-3 ore Idoneità per principianti 76/100
I maintainer di solito rispondono entro 1 giorno