Cortex Search: METADATA$RANK removed from CORTEX_SEARCH_BATCH (Pending)¶

When this behavior change bundle is enabled, the METADATA$RANK column is no longer returned from CORTEX_SEARCH_BATCH. Any query that references METADATA$RANK will fail with the following SQL compilation error:

referencing METADATA$RANK is not supported.

This impacts any query using CORTEX_SEARCH_BATCH that references METADATA$RANK, including SELECT lists, WHERE clauses, or QUALIFY filters on that column.

Before the change:

The CORTEX_SEARCH_BATCH table function returns a METADATA$RANK column. METADATA$RANK is a 1-based integer that reflects the physical row delivery order of results within each METADATA$REQUEST_ID partition.

After the change:

METADATA$RANK is no longer returned from CORTEX_SEARCH_BATCH. Queries that reference METADATA$RANK fail with a SQL compilation error. The returned results are still in ranked order.

The rank position of each result can be determined directly from the order in which CORTEX_SEARCH_BATCH returns rows within a partition, making METADATA$RANK unnecessary. This change simplifies the function output by removing a redundant column.

How to update your code¶

Replace any reference to METADATA$RANK with a ROW_NUMBER() window function partitioned by METADATA$REQUEST_ID and ordered by SEQ8():

ROW_NUMBER() OVER (
    PARTITION BY METADATA$REQUEST_ID
    ORDER BY SEQ8()
) AS row_rank

This reproduces the same 1-based row number within each request partition that METADATA$RANK previously provided. For example:

SELECT
    q.query_text,
    r.*,
    ROW_NUMBER() OVER (
        PARTITION BY r.METADATA$REQUEST_ID
        ORDER BY SEQ8()
    ) AS row_rank
FROM my_queries AS q,
LATERAL CORTEX_SEARCH_BATCH(
    service_name => 'my_db.my_schema.my_service',
    query        => q.query_text,
    limit        => 10
) AS r;

Ref: 2418