[Detail Bug] CockroachDB ingestion mixes partition metadata across schemas for same-named tables

[detail-app]

Detail Bug Report

https://app.detail.dev/org_3377c26d-da48-4ccd-b83a-22c542f4fe83/bugs/bug_1d7db4d8-72a4-4cd0-913e-3c6d4ad3a056

Introduced in #19108 by @akashverma0786 on Jan 7, 2025

Summary

• Context: The CockroachDB ingestion source extracts table metadata (including partitioning) from a CockroachDB cluster via custom SQL in ingestion/src/metadata/ingestion/source/database/cockroach/queries.py.
• Bug: COCKROACH_GET_PARTITION_DETAILS selects partition rows for a table using only tables.name = :table_name and never filters by schema_name, so when two schemas in the same database each contain a table with the same name, partitions from one schema's table are attributed to the other schema's table.
• Actual vs. expected: The query returns partition rows across all schemas for any table whose name matches, whereas it should return partition rows only for the specific (schema, table) being ingested — just like the sibling Postgres implementation (... and par.relnamespace::regnamespace::text=:schema_name).
• Impact: A table can be incorrectly flagged TableType.Partitioned and assigned wrong tablePartition columns that actually belong to a same-named table in a different schema, corrupting the published table metadata in OpenMetadata.

Code with Bug

ingestion/src/metadata/ingestion/source/database/cockroach/queries.py:

COCKROACH_GET_PARTITION_DETAILS = """
    SELECT
    partitions.name AS partition_name,
    column_names,
    CASE 
        WHEN list_value IS NOT NULL THEN 'list'
        ELSE 'range'
    END AS partition_type,
    tables.name AS table_name,
    database_name
FROM
    crdb_internal.partitions
JOIN
    crdb_internal.tables ON partitions.table_id = tables.table_id
WHERE
    tables.name = :table_name;  -- <-- BUG 🔴 missing `tables.schema_name` filter
"""

ingestion/src/metadata/ingestion/source/database/cockroach/metadata.py:

def get_table_partition_details(self, table_name: str, schema_name: str, inspector) -> tuple[bool, TablePartition]:
    with self.engine.connect() as conn:
        result = conn.execute(text(COCKROACH_GET_PARTITION_DETAILS), {"table_name": table_name}).all()  # <-- BUG 🔴 schema_name ignored
    if result:
        partition_details = TablePartition(
            columns=[
                PartitionColumnDetails(
                    columnName=row[1],
                    intervalType=INTERVAL_TYPE_MAP.get(row[2], PartitionIntervalTypes.COLUMN_VALUE),
                    interval=None,
                )
                for row in result
            ]
        )
        return True, partition_details
    return False, None

Explanation

CockroachDB allows same-named tables in different schemas within the same database (e.g., public.events and analytics.events). crdb_internal.tables.name contains only the bare table name, so filtering partitions by tables.name alone is ambiguous across schemas.

As a result, when ingesting a specific table (schema_name, table_name), the query returns partition rows for all tables with that name across schemas. get_table_partition_details then merges all returned rows into a single TablePartition, causing the currently-ingested table to be marked Partitioned and assigned partition columns that may belong to a different schema’s same-named table.

Codebase Inconsistency

• The Postgres sibling query scopes by schema (par.relnamespace::regnamespace::text=:schema_name), while Cockroach does not.
• The Cockroach caller always supplies schema_name, but the Cockroach override discards it.

Recommended Fix

Update the query and parameter binding to include schema_name:

In ingestion/src/metadata/ingestion/source/database/cockroach/queries.py:

WHERE
    tables.name = :table_name
    AND tables.schema_name = :schema_name;  -- <-- FIX 🟢

In ingestion/src/metadata/ingestion/source/database/cockroach/metadata.py:

result = conn.execute(
    text(COCKROACH_GET_PARTITION_DETAILS),
    {"table_name": table_name, "schema_name": schema_name},  # <-- FIX 🟢
).all()

History

This bug was introduced in commit b2898f7. PR #19108 added the Cockroach partition-details feature with a name-only predicate and ignored schema_name; later changes only reformatted parameter style and did not change the predicate logic.