opt: queries with a gin index discard predicate

[dbist]

Describe the problem

Considering a jsonb column with a payload like

{"$k": ["_id", "theaterId", "location"], "_id": {"$o": "59a47286cfa9a3a73e51e732"}, "location": {"$k": ["address", "geo"], "address": {"$k": ["street1", "city", "state", "zipcode"], "city": "Sparks", "state": "NV", "street1": "155 Los Altos Pkwy", "zipcode": "89436"}, "geo": {"$k": ["type", "coordinates"], "coordinates": [{"$f": -119.7412}, {"$f": 39.579536}], "type": "Point"}}, "theaterId": 1014}

Having a GIN index on the column

CREATE INDEX ON sample_mflix.theaters_cf846063 USING GIN(_jsonb);

Executing a query like

SELECT gateway_region(), "_jsonb"->'location'->'address'->>'state' FROM sample_mflix.theaters_cf846063 WHERE "_jsonb"->'location'->'address' @> '{"state":"NY"}' LIMIT 10;

returns the following output

  gateway_region | ?column?
-----------------+-----------
  aws-us-east-1  | MI
  aws-us-east-1  | MN
  aws-us-east-1  | MI
  aws-us-east-1  | MD
  aws-us-east-1  | MI
  aws-us-east-1  | MI
  aws-us-east-1  | MI
  aws-us-east-1  | MI
  aws-us-east-1  | MI
  aws-us-east-1  | NV

The incorrect result is returned irrelevant of the LIMIT.

Dropping the index

DROP INDEX sample_mflix.theaters_cf846063@theaters_cf846063__jsonb_idx;

The result is incorrect again

artem@artem-mr-7xw.aws-us-east-1.cockroachlabs.cloud:26257/ferretdb> SELECT gateway_region(), "_jsonb"->'location'->'address'->>'state' FROM sample_mflix.theaters_cf846063 WHERE "_jsonb"->'location'->'address' @> '{"state":"NY"}' LIMIT 1;
  gateway_region | ?column?
-----------------+-----------
  aws-us-east-1  | MI

The table has RBR locality

CREATE TABLE sample_mflix.theaters_cf846063 (
      _jsonb JSONB NULL,
      rowid INT8 NOT VISIBLE NOT NULL DEFAULT unique_rowid(),
      region public.crdb_internal_region NOT NULL AS (CASE WHEN (((_jsonb->'location':::STRING)->'address':::STRING)->>'state':::STRING) IN ('AK':::STRING, 'AL':::STRING, 'AR':::STRING, 'AZ':::STRING, 'CA':::STRING, 'CO':::STRING, 'CT':::STRING, 'DC':::STRING, 'DE':::STRING, 'FL':::STRING, 'GA':::STRING, 'HI':::STRING, 'IA':::STRING, 'ID':::STRING, 'IL':::STRING, 'IN':::STRING, 'KS':::STRING, 'KY':::STRING, 'LA':::STRING) THEN 'aws-us-west-2':::public.crdb_internal_region WHEN (((_jsonb->'location':::STRING)->'address':::STRING)->>'state':::STRING) IN ('MA':::STRING, 'MD':::STRING, 'ME':::STRING, 'MI':::STRING, 'MN':::STRING, 'MO':::STRING, 'MS':::STRING, 'MT':::STRING, 'NC':::STRING, 'ND':::STRING, 'NE':::STRING, 'NH':::STRING, 'NJ':::STRING, 'NM':::STRING, 'NV':::STRING, 'NY':::STRING) THEN 'aws-us-east-1':::public.crdb_internal_region WHEN (((_jsonb->'location':::STRING)->'address':::STRING)->>'state':::STRING) IN ('OH':::STRING, 'OK':::STRING, 'OR':::STRING, 'PA':::STRING, 'PR':::STRING, 'RI':::STRING, 'SC':::STRING, 'SD':::STRING, 'TN':::STRING, 'TX':::STRING, 'UT':::STRING, 'VA':::STRING, 'VT':::STRING, 'WA':::STRING, 'WI':::STRING, 'WV':::STRING, 'WY':::STRING) THEN 'aws-us-east-2':::public.crdb_internal_region END) STORED,
      state STRING NOT NULL AS (((_jsonb->'location':::STRING)->'address':::STRING)->>'state':::STRING) STORED,
      CONSTRAINT theaters_cf846063_pkey PRIMARY KEY (rowid ASC),
      INDEX theaters_cf846063_state_idx (state ASC),
      INVERTED INDEX theaters_cf846063__jsonb_idx1 (_jsonb)
  ) LOCALITY REGIONAL BY ROW AS region

The table schema

ALTER TABLE sample_mflix.theaters_cf846063 ADD COLUMN region crdb_internal_region NOT NULL AS (
  CASE WHEN ("_jsonb"->'location'->'address'->>'state') IN ('AK','AL','AR','AZ','CA','CO','CT','DC','DE','FL','GA','HI','IA','ID','IL','IN','KS','KY','LA') THEN 'aws-us-west-2'
  WHEN ("_jsonb"->'location'->'address'->>'state') IN ('MA','MD','ME','MI','MN','MO','MS','MT','NC','ND','NE','NH','NJ','NM','NV','NY') THEN 'aws-us-east-1'
  WHEN ("_jsonb"->'location'->'address'->>'state') IN ('OH','OK','OR','PA','PR','RI','SC','SD','TN','TX','UT','VA','VT','WA','WI','WV','WY') THEN 'aws-us-east-2'
  END
) STORED;

ALTER TABLE sample_mflix.theaters_cf846063 SET LOCALITY REGIONAL BY ROW AS "region";

The OPT for the select query

                                     info
------------------------------------------------------------------------------
  project
   ├── index-join theaters_cf846063
   │    └── locality-optimized-search
   │         ├── scan theaters_cf846063@theaters_cf846063__jsonb_idx1
   │         │    ├── constraint: /13: [/'aws-us-east-1' - /'aws-us-east-1']
   │         │    └── limit: 10
   │         └── scan theaters_cf846063@theaters_cf846063__jsonb_idx1
   │              ├── constraint: /20
   │              │    ├── [/'aws-us-east-2' - /'aws-us-east-2']
   │              │    └── [/'aws-us-west-2' - /'aws-us-west-2']
   │              └── limit: 10
   └── projections
        ├── 'aws-us-east-1'
        └── (("_jsonb"->'location')->'address')->>'state'

Expected behavior
The query should return all rows with state NY.

Environment:

• CockroachDB version 22.1.6 Dedicated
• Server OS: Cockroach Cloud
• Client app cockroach sql

Jira issue: CRDB-19660

[dbist]

without LIMIT query returns correct results

artem@artem-mr-7xw.aws-us-east-1.cockroachlabs.cloud:26257/ferretdb_with_bug> SELECT gateway_region(), "_jsonb"->'location'->'address'->>'state' FROM sample_mflix.theaters_cf846063 WHERE "_jsonb"->'location'->'address' @> '{"state":"NY"}';
  gateway_region | ?column?
-----------------+-----------
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY
  aws-us-east-1  | NY

[msirek]

@dbist Opened #88079 for this