Internal error "inverted column should have type encodedkey" for empty array containment on inverted index

Open
#176,163 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
42/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Active
Tech stack
go, postgresql, sql
Domain
backend, databases

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

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

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:

  1. Start a CockroachDB cluster (single-node or multi-node, insecure mode).
  2. Connect with psql.
  3. 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;
  1. 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

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.