Technical Advisory 99561

WITH descriptors AS (
  SELECT
    id,
    crdb_internal.pb_to_json(
      'cockroach.sql.sqlbase.Descriptor',
      descriptor, false
    ) AS descriptor
  FROM
    system.descriptor
),
table_descriptors AS (
  SELECT
    id,
    descriptor -> 'table' AS descriptor
  FROM
    descriptors
  WHERE
    (descriptor -> 'table') IS NOT NULL
    AND (
      descriptor -> 'table' -> 'viewQuery'
    ) IS NULL
),
column_ids AS (
  SELECT
    id,
    json_array_elements(descriptor -> 'columns')-> 'id' AS columnid,
    json_array_elements(descriptor -> 'columns')-> 'name' AS columnname,
    (
      json_array_elements(descriptor -> 'columns')-> 'virtual'
    ):: STRING AS virtual
  FROM
    table_descriptors
),
primary_storing_columns AS (
  SELECT
    id,
    json_array_elements(
      descriptor -> 'primaryIndex' -> 'storeColumnIds'
    ) AS storecolumnid
  FROM
    table_descriptors
),
primary_key_columns AS (
  SELECT
    id,
    json_array_elements(
      descriptor -> 'primaryIndex' -> 'keyColumnIds'
    ) AS keycolumnid
  FROM
    table_descriptors
),
secondary_indexes AS (
  SELECT
    id,
    json_array_elements(descriptor -> 'indexes') AS idx
  FROM
    table_descriptors
),
bad_secondary_indexes_store_columns AS (
  SELECT
    id,
    idx -> 'name' AS name,
    json_array_elements(idx -> 'storeColumnIds') AS storecolumnid
  FROM
    secondary_indexes
  WHERE
    (idx -> 'encodingType'):: INT8 = 1
),
bad_secondary_indexes AS (
  SELECT
    id,
    name
  FROM
    bad_secondary_indexes_store_columns AS bsc
  WHERE
    storecolumnid NOT IN (
      SELECT
        storecolumnid
      FROM
        primary_storing_columns AS psc
      WHERE
        psc.id = bsc.id
    )
),
bad_secondary_indexes_with_encoding AS (
  SELECT
    id,
    idx -> 'name' AS name
  FROM
    secondary_indexes
  WHERE
    (idx -> 'encodingType'):: INT8 = 1
    AND (id, idx -> 'name') NOT IN (
      SELECT
        id,
        name
      FROM
        bad_secondary_indexes
    )
),
primary_index_is_missing_data AS (
  SELECT
    id,
    columnid,
    columnname
  FROM
    column_ids AS cs
  WHERE
    "virtual" is NULL
    AND columnid NOT IN (
      SELECT
        storecolumnid
      FROM
        primary_storing_columns AS psc
      WHERE
        psc.id = cs.id
      UNION
      SELECT
        keycolumnid
      FROM
        primary_key_columns AS ksc
      WHERE
        ksc.id = cs.id
      UNION
      SELECT
        storecolumnid
      FROM
        bad_secondary_indexes_store_columns AS bsc
      WHERE
        bsc.id = cs.id
    )
)
SELECT
  tbl.database_name || '.' || tbl.schema_name || '.' || tbl.name AS object_name,
  'columns are not stored in primary index' AS type,
  idx.name AS index_name_or_col_name
FROM
  bad_secondary_indexes AS idx,
  crdb_internal.tables AS tbl
WHERE
  table_id = id
UNION
SELECT
  tbl.database_name || '.' || tbl.schema_name || '.' || tbl.name AS object_name,
  'secondary index has incorrect encoding type' AS type,
  idx.name AS index_name_or_col_name
FROM
  bad_secondary_indexes_with_encoding AS idx,
  crdb_internal.tables AS tbl
WHERE
  table_id = id
UNION
SELECT
  tbl.database_name || '.' || tbl.schema_name || '.' || tbl.name AS object_name,
  'column maybe missing be data' AS type,
  badcols.columnname AS index_name_or_col_name
FROM
  primary_index_is_missing_data AS badcols,
  crdb_internal.tables AS tbl
WHERE
  table_id = id;