sql: incorrect joins to inverted expression indexes on json equality

[michae2]

Inverted indexes on JSON can be used both for (a) matching within the value and (b) matching the entire value. For some reason, though, it appears that inverted indexes on JSON expressions can only be used for (a) matching within the value and not (b) matching the entire value. And more alarming, sometimes we're constructing the inverted join to the inverted expression index incorrectly. Here's a demonstration using v23.2.0-alpha.2-dev:

CREATE TABLE t (i INT PRIMARY KEY, j JSONB);
INSERT INTO t SELECT i, to_json(i % 10) FROM generate_series(0, 9999) AS s(i);
INSERT INTO t SELECT i, json_build_object('a', i % 10) FROM generate_series(10000, 19999) AS s(i);
INSERT INTO t SELECT i, json_build_object('a', json_build_object('b', i % 10)) FROM generate_series(20000, 29999) AS s(i);

-- First, test an inverted index on the column.
CREATE INVERTED INDEX ON t (j);
ANALYZE t;

-- These all use the inverted index.
EXPLAIN SELECT i FROM t WHERE j = '5';
EXPLAIN SELECT i FROM t WHERE j = '{"a": 5}';
EXPLAIN SELECT i FROM t WHERE j = '{"a": {"b": 5}}';
EXPLAIN SELECT i FROM t WHERE j->'a' = '5';
EXPLAIN SELECT i FROM t WHERE j->'a' = '{"b": 5}';
EXPLAIN SELECT i FROM t WHERE j->'a'->'b' = '5';
EXPLAIN SELECT i FROM t WHERE j @> '5';
EXPLAIN SELECT i FROM t WHERE j @> '{"a": 5}';
EXPLAIN SELECT i FROM t WHERE j @> '{"a": {"b": 5}}';
EXPLAIN SELECT i FROM t WHERE j->'a' @> '5';
EXPLAIN SELECT i FROM t WHERE j->'a' @> '{"b": 5}';
EXPLAIN SELECT i FROM t WHERE j->'a'->'b' @> '5';

-- Then, test an inverted index on (j->'a').
DROP INDEX t_j_idx;
CREATE INVERTED INDEX ON t ((j->'a'));
ANALYZE t;

-- These should all be able to use the inverted expression index, but only the last one can.
EXPLAIN SELECT i FROM t WHERE j->'a' = '5';
EXPLAIN SELECT i FROM t WHERE j->'a' = '{"b": 5}';
EXPLAIN SELECT i FROM t WHERE j->'a'->'b' = '5';

-- More concerning, these should not use the inverted expression index, but they do, and
-- get incorrect results!
EXPLAIN SELECT i FROM t WHERE j = '5';
EXPLAIN SELECT i FROM t WHERE j = '{"a": 5}';
EXPLAIN SELECT i FROM t WHERE j = '{"a": {"b": 5}}';

-- Interestingly, the cases using @> seem to behave correctly.

Jira issue: CRDB-32156

[blathers-crl]

Hi @michae2, please add branch-* labels to identify which branch(es) this release-blocker affects.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[michae2]

Looks like this wasn't present in any releases prior to 23.2, adding release-blocker.

[mgartner]

One approach to diagnosing this is to git bisect to figure out what commit introduced this.

[mgartner]

Great catch @michae2. How did you discover this one? I'm surprised that randomized tests haven't caught this—maybe there is a hole in our inverted expression index testing.

[mgartner]

I've bisected this to 85400d3.

[mgartner]

This one is pretty bad. We'll scan an inverted index on a column that's not even filtered in the query:

defaultdb> CREATE TABLE t (
        ->   k INT PRIMARY KEY,
        ->   j1 JSON,
        ->   j2 JSON,
        ->   INVERTED INDEX (j1)
        -> );
CREATE TABLE

defaultdb> EXPLAIN SELECT * FROM t WHERE j2 = '5';
             info
-------------------------------
  distribution: local
  vectorized: true

  • filter
  │ filter: j2 = '5'
  │
  └── • index join
      │ table: t@t_pkey
      │
      └── • scan
            missing stats
            table: t@t_j1_idx
            spans: 2 spans
(13 rows)

As I work on fixing this, I'll try to figure out why our randomized testing did not catch this.