Technical Advisory 82576

WITH
    tables
        AS (
            SELECT
                *
            FROM
                (
                    SELECT
                        id,
                        crdb_internal.pb_to_json(
                            'cockroach.sql.sqlbase.Descriptor',
                            descriptor
                        )->'table'
                            AS tab
                    FROM
                        system.descriptor
                )
            WHERE
                tab IS NOT NULL
        ),
    columns_using_sequence_ids
        AS (
            SELECT
                table_id,
                (c->'id')::INT8 AS column_id,
                json_array_elements(c->'usesSequenceIds')::INT8 AS seq_id
            FROM
                (
                    SELECT
                        id AS table_id, c
                    FROM
                        tables,
                        ROWS FROM (json_array_elements(tab->'columns')) AS t (c)
                )
            WHERE
                (c->'usesSequenceIds') IS NOT NULL
                AND json_array_length(c->'usesSequenceIds') = 1
        ),
    sequences_with_missing_depended_on_by
        AS (
            SELECT
                seq_id, (dep->>'id')::INT8 AS table_id, ord, dep
            FROM
                (
                    SELECT
                        id AS seq_id, dep, ord - 1 AS ord
                    FROM
                        tables,
                        ROWS FROM (
                            json_array_elements(tab->'dependedOnBy')
                        ) WITH ORDINALITY
                            AS t (dep, ord)
                    WHERE
                        (tab->'sequenceOpts') IS NOT NULL
                        AND EXISTS(
                                SELECT
                                    *
                                FROM
                                    ROWS FROM (
                                        json_array_elements(tab->'dependedOnBy')
                                    )
                                        AS t (dep)
                                WHERE
                                    dep->'columnIds' @> '[0]'::JSONB
                            )
                )
            WHERE
                (dep->>'byId')::BOOL
        )
SELECT
    count(*)
FROM
    sequences_with_missing_depended_on_by;

 WITH tables AS (
                SELECT *
                  FROM (
                        SELECT id,
                               crdb_internal.pb_to_json(
                                'cockroach.sql.sqlbase.Descriptor',
                                descriptor
                               )->'table' AS tab
                          FROM system.descriptor
                       )
                 WHERE tab IS NOT NULL
              ),
       columns_using_sequence_ids AS (
                                    SELECT table_id,
                                           (c->'id')::INT8 AS column_id,
                                           json_array_elements(c->'usesSequenceIds')::INT8 AS seq_id
                                      FROM (
                                            SELECT id AS table_id, c
                                              FROM tables,
                                                   ROWS FROM (json_array_elements(tab->'columns')) AS t
                                                                                                    (c)
                                           )
                                     WHERE (c->'usesSequenceIds') IS NOT NULL
                                       AND json_array_length(c->'usesSequenceIds') = 1
                                  ),
       sequences_with_missing_depended_on_by AS (
                                                SELECT seq_id, (dep->>'id')::INT8 AS table_id, ord, dep
                                                  FROM (
                                                        SELECT id AS seq_id, dep, ord - 1 AS ord
                                                          FROM tables,
                                                               ROWS FROM (
                                                                json_array_elements(tab->'dependedOnBy')
                                                               ) WITH ORDINALITY AS t (dep, ord)
                                                         WHERE (tab->'sequenceOpts') IS NOT NULL
                                                           AND EXISTS(
                                                                SELECT *
                                                                  FROM ROWS FROM (
                                                                        json_array_elements(
                                                                            tab->'dependedOnBy'
                                                                        )
                                                                       ) AS t (dep)
                                                                 WHERE dep->'columnIds' @> '[0]'::JSONB
                                                               )
                                                       )
                                                 WHERE (dep->>'byId')::BOOL
                                             ),
       depended_on_by_entries AS (
                                  SELECT s.seq_id, ord, json_agg(t.column_id) AS column_ids
                                    FROM columns_using_sequence_ids AS t
                                    JOIN sequences_with_missing_depended_on_by AS s ON t.table_id
                                                                                       = s.table_id
                                                                                   AND t.seq_id
                                                                                       = s.seq_id
                                GROUP BY s.seq_id, s.table_id, ord
                              ),
       updated_entries AS (
                        SELECT seq_id, ord, json_set(d, ARRAY['columnIds'], column_ids) AS d
                          FROM depended_on_by_entries
                          JOIN tables ON seq_id = tables.id
                          JOIN ROWS FROM (
                                json_array_elements(tab->'dependedOnBy')
                               ) WITH ORDINALITY AS jae (d, idx) ON ord = idx - 1
                       ),
       depended_on_by_arrs AS (
                              SELECT seq_id, json_agg(d ORDER BY ord ASC) AS depended_on_by
                                FROM updated_entries
                            GROUP BY seq_id
                           )
SELECT crdb_internal.unsafe_upsert_descriptor(
        seq_id,
        crdb_internal.json_to_pb(
            'cockroach.sql.sqlbase.Descriptor',
            json_build_object(
                'table',
                json_remove_path(
                    json_set(
                        json_set(tab, ARRAY['dependedOnBy'], depended_on_by),
                        ARRAY['version'],
                        ((tab->>'version')::INT8 + 1)::STRING::JSONB
                    ),
                    ARRAY['modificationTime']
                )
            )
        ),
        true
       )
  FROM depended_on_by_arrs JOIN tables ON id = seq_id;