Internal error "inverted column should have type encodedkey" for empty array containment on inverted index
Nobody has claimed this yet.
Assessment
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Newbie friendliness
- 42/100
Research direction
Start by reproducing the SQL in the issue with vectorization and DistSQL enabled, then inspect the stack trace at pkg/sql/rowexec/inverted_filterer.go:237 and the inverted filter/index join plan. The payload does not identify a test file, so locate the existing inverted index containment tests and add a case for INT8[] @> ARRAY[]::INT8[]. Done means the query returns successfully and preserves the expected rows on the inverted index path.
Written by the indexing model from the issue text.
Description
Describe the problem
A valid query that uses the array containment operator @> with an empty array on an INT8[] column produces an internal error when the optimizer chooses an inverted index scan plus index join:
ERROR: internal error: inverted column should have type encodedkey
DETAIL:
pkg/sql/rowexec/inverted_filterer.go:237: readInput()
pkg/sql/rowexec/inverted_filterer.go:173: Next()
pkg/sql/colfetcher/index_join.go:388: next()
The predicate is c2 @> ARRAY[]::INT8[], which is always true for any array (including NULL → NULL, but here c2 has non‑NULL values). It should not filter out any rows; at most it should simply be a no‑op.
Instead, because an inverted index vp_source_c2_inv exists on c2, the optimizer picks an inverted index scan with an inverted filter and an index join, and the inverted filterer then fails with an internal error.
A semantically equivalent multi‑table query that reconstructs the same rows through a join does not hit this path: it uses a plain filter and returns a result. This confirms the error is specific to the inverted index / inverted filter / index join execution path.
The issue is triggered by:
- an INT8[] column with an inverted index,
- a containment predicate whose right-hand side is an empty array (
ARRAY[]::INT8[]), - the optimizer choosing an inverted index scan plus index join.
To Reproduce
What did you do?
Created a table with an INT8[] column and an inverted index, inserted rows, and ran a query using c2 @> ARRAY[]::INT8[]. Compared it with an equivalent join-based reconstruction.
Steps to reproduce:
- Start a CockroachDB cluster (single-node or multi-node, insecure mode).
- Connect with psql.
- Run the following SQL:
DROP DATABASE IF EXISTS repro_tidb617_db0_min CASCADE;
CREATE DATABASE repro_tidb617_db0_min;
USE repro_tidb617_db0_min;
SET allow_unsafe_internals = true;
SET vectorize = 'on';
SET distsql = 'on';
CREATE TABLE vp_source (
vp_rowid INT8 NOT NULL PRIMARY KEY,
c0 INT8,
c1 JSONB,
c2 INT8[],
c3 STRING COLLATE de,
c4 STRING,
c5 INT8,
rowid INT8,
vp_computed INT8 AS (vp_rowid * 2) STORED,
FAMILY "primary" (vp_rowid),
FAMILY payload_a (c0, c2, c4, rowid),
FAMILY payload_b (c1, c3, c5, vp_computed)
);
CREATE INVERTED INDEX vp_source_c1_inv ON vp_source (c1);
CREATE INVERTED INDEX vp_source_c2_inv ON vp_source (c2);
CREATE INDEX vp_source_cover ON vp_source (rowid, c4, c0, c3, c5);
CREATE TABLE vp_l (
vp_rowid INT8 NOT NULL PRIMARY KEY,
c1 JSONB,
c2 INT8[],
c4 STRING,
rowid INT8,
vp_computed INT8 AS (vp_rowid * 2) STORED,
FAMILY "primary" (vp_rowid),
FAMILY payload (c1, c2, c4, rowid, vp_computed)
);
CREATE INVERTED INDEX vp_l_c1_inv ON vp_l (c1);
CREATE INVERTED INDEX vp_l_c2_inv ON vp_l (c2);
CREATE TABLE vp_r (
vp_rowid INT8 NOT NULL PRIMARY KEY,
c0 INT8,
c3 STRING COLLATE de,
c5 INT8,
FAMILY "primary" (vp_rowid),
FAMILY payload (c0, c3, c5)
);
INSERT INTO vp_source
(vp_rowid, c0, c1, c2, c3, c4, c5, rowid)
SELECT
i,
i,
CASE i % 4
WHEN 0 THEN NULL
WHEN 1 THEN '{"vp":1}'::JSONB
WHEN 2 THEN '{"k":null}'::JSONB
ELSE '[1,2]'::JSONB
END,
CASE i % 4
WHEN 0 THEN NULL
WHEN 1 THEN ARRAY[]::INT8[]
WHEN 2 THEN ARRAY[1,NULL]::INT8[]
ELSE ARRAY[0,1,1]::INT8[]
END,
CASE i % 4
WHEN 0 THEN NULL
WHEN 1 THEN 'Aa'
WHEN 2 THEN 'a'
ELSE 'b'
END,
'm_last',
i % 1000,
i
FROM generate_series(1, 4096) AS g(i);
INSERT INTO vp_l (vp_rowid, c1, c2, c4, rowid)
SELECT vp_rowid, c1, c2, c4, rowid
FROM vp_source;
INSERT INTO vp_r (vp_rowid, c0, c3, c5)
SELECT vp_rowid, c0, c3, c5
FROM vp_source;
-- Single-table query: internal error
SELECT DISTINCT
NULL::TIME,
COUNT(vp_computed),
VARIANCE(vp_rowid)
FROM vp_source
WHERE c2 @> ARRAY[]::INT8[]
AND (c3 IS NULL OR c3 IS NULL OR c3 = c3)
GROUP BY NULL::TIME
HAVING COUNT(*) > 0
ORDER BY NULL::TIME
LIMIT 2;
-- Multi-table query: succeeds
SELECT DISTINCT
NULL::TIME,
COUNT(r.vp_rowid),
VARIANCE(l.vp_rowid)
FROM vp_l@{FORCE_INDEX=vp_l_pkey} AS l
JOIN vp_r AS r
ON l.vp_rowid = r.vp_rowid
WHERE l.c2 @> ARRAY[]::INT8[]
AND (r.c3 IS NULL OR r.c3 IS NULL OR r.c3 = r.c3)
GROUP BY NULL::TIME
HAVING COUNT(*) > 0
ORDER BY NULL::TIME
LIMIT 2;
- Observe that the single-table query fails with the internal error above, while the multi-table query returns a result.
Expected behavior
c2 @> ARRAY[]::INT8[] is always true for any array value; it must not filter out rows and must never cause an internal error.
The single-table query should return the same rows as the semantically equivalent multi-table query, and no internal error should be raised regardless of whether the optimizer chooses an inverted index scan or a plain filter.
Additional data / screenshots
Single-table plan (partial):
group-by (streaming)
└── index-join vp_source
└── inverted-filter
├── inverted column: c2
└── scan vp_source@vp_source_c2_inv,inverted
EXPLAIN (DISTSQL) for the single-table query:
distribution: local
└── index join
└── inverted filter
├── inverted column: c2_inverted_key
└── scan vp_source@vp_source_c2_inv
The error occurs exactly on the inverted-filter / index-join path.
Multi-table plan:
merge join
├── scan vp_r@vp_r_pkey
└── scan vp_l@vp_l_pkey
└── filter c2 @> ARRAY[]
Environment:
- CockroachDB version: CockroachDB CCL v26.2.3
- Server OS: Ubuntu 20.04.6 LTS x86_64, Linux 5.4.0-204-generic
- Client app: psql (PostgreSQL connection, sslmode=disable)
- Cluster mode: single-node or multi-node, insecure
- Relevant settings:
vectorize = 'on',distsql = 'on',allow_unsafe_internals = true
Additional context
What was the impact?
Add any other context about the problem here.
Jira issue: CRDB-70109
- 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