[Bug]: IVFFLAT mode=auto hangs or returns zero inside CTE and derived tables

## Description

IVFFLAT `mode=auto` does not fall back from an empty POST result when the vector top-k is inside a CTE or derived table. A row-producing outer consumer can expose the empty inner result as wrong data, while a row-preserving outer consumer repeatedly retries and does not complete.

## Environment

- Branch: `main`
- Commit: `01d60e1c4ded1b0f3fc1a4ecd75ce54e95e23b90`
- Deployment: local 1 Log / 1 TN / 2 CN, four independent `mo-service` processes

## Steps to reproduce

```sql
SET experimental_ivf_index=1;
DROP DATABASE IF EXISTS ivf_auto_nested;
CREATE DATABASE ivf_auto_nested;
USE ivf_auto_nested;

CREATE TABLE t(
id BIGINT PRIMARY KEY,
category INT NOT NULL,
v VECF32(2)
);

INSERT INTO t
SELECT result,
IF(result <= 500, 0, 1),
CAST(CONCAT('[', result, ',0]') AS VECF32(2))
FROM generate_series(1, 1000, 1) g;

CREATE INDEX ix USING ivfflat ON t(v)
LISTS=1 OP_TYPE 'vector_l2_ops';
SET probe_limit=1;
```

Top-level control, which correctly performs the AUTO POST-to-PRE retry:

```sql
SELECT id
FROM t
WHERE category=1
ORDER BY l2_distance(v,'[0,0]')
LIMIT 10 BY RANK WITH OPTION 'mode=auto';
```

Nested forms:

```sql
WITH q AS (
SELECT id FROM t
WHERE category=1
ORDER BY l2_distance(v,'[0,0]')
LIMIT 10 BY RANK WITH OPTION 'mode=auto'
)
SELECT id FROM q ORDER BY id;

SELECT id
FROM (
SELECT id FROM t
WHERE category=1
ORDER BY l2_distance(v,'[0,0]')
LIMIT 10 BY RANK WITH OPTION 'mode=auto'
) q
ORDER BY id;

SELECT COUNT(*)
FROM (
SELECT id FROM t
WHERE category=1
ORDER BY l2_distance(v,'[0,0]')
LIMIT 10 BY RANK WITH OPTION 'mode=auto'
) q;
```

## Actual behavior

- The top-level control returns IDs `501..510` immediately.
- The CTE and derived-table row queries do not complete on this 1,000-row fixture; each was externally stopped after 5 seconds.
- The outer aggregate returns immediately, but returns `COUNT(*) = 0`.

The initial POST path has no surviving candidates because the nearest candidates all have `category=0`. AUTO is expected to retry with PRE, as it does for the top-level control.

## Expected behavior

All three nested forms must complete and return the same logical result as the top-level AUTO query: IDs `501..510`, or `COUNT(*) = 10` for the aggregate.

## Stability and controls

- CTE AUTO row query: 3/3 on CN1 and 3/3 on CN2 exceeded 5 seconds without returning a row.
- Derived-table AUTO row query: reproduced on both CNs and exceeded 5 seconds.
- Derived-table AUTO aggregate: repeated 3/3 on both CNs and returned `0`.
- Top-level AUTO control: both CNs returned 10 rows, IDs `501..510`.
- Nested `mode=pre` and `mode=force` controls: both returned `COUNT(*) = 10` immediately.
- No-filter/non-empty POST paths complete normally.
- The statements are read-only; table contents and index metadata remain unchanged after the client cancels the non-completing statements.

## Evidence

The two-CN result matrix, timeout exit code `124`, process-list entry for the active CTE statement, and control outputs are retained in the local exploration evidence for the fixed commit above.

## Code analysis

`pkg/sql/colexec/output/output.go` requests PRE retry only when the final `Output` operator has `rowCount == 0`.

- An outer aggregate emits one row containing zero, so the retry condition is hidden even though the nested vector result is empty.
- A row-preserving outer query reaches final zero rows and requests retry, but the nested AUTO rank option is not effectively changed in the rebuilt execution; the statement requests the same retry again instead of reaching PRE.

`pkg/sql/compile/compile2.go` rewrites AUTO to PRE only after `ErrVectorNeedRetryWithPreMode`. The current recursion has tests for synthetic FROM-subquery AST nodes but no end-to-end CTE/derived AUTO fallback coverage. The exact failure point in parsed CTE/derived AST handling or retry-plan rebuilding needs confirmation by the component owner.

## Regression coverage

Add end-to-end IVFFLAT AUTO fallback cases for:

1. a CTE consumed as rows;
2. a derived table consumed as rows;
3. `COUNT(*)` over the same derived table;
4. literal and prepared variants;
5. both single-CN and multi-CN execution.

Each case must force an empty initial POST candidate set and verify that PRE returns all 10 qualifying rows without a retry loop.

## Related

- Source exploration: #28943
- #28719 / PR #28833 fixed result metadata during a top-level AUTO retry with INCLUDE columns; this reproduction needs no INCLUDE columns and fails only after nesting the vector query.
- #28988 tracks fixed HNSW post-filter under-fill and is a separate algorithm/path.