Scheduler crash-loops on MySQL-compatible backends without CTE-in-DML support (e.g. Vitess): asset orphanage DELETE uses WITH

### Apache Airflow version

3.3.0 (also present in 3.3.1 and current `main`)

### What happened?

`SchedulerJobRunner._update_asset_orphanage` builds its asset reference-count query as a CTE (`.cte()`) and hands it to `_orphan_unreferenced_assets`, which executes:

```sql
WITH anon_1 AS (
SELECT asset.id, asset.name, asset.uri, ...
FROM asset
LEFT OUTER JOIN dag_schedule_asset_reference ON ...
LEFT OUTER JOIN task_outlet_asset_reference ON ...
LEFT OUTER JOIN task_inlet_asset_reference ON ...
GROUP BY asset.id
HAVING count(...) + count(...) + count(...) = %s
)
DELETE FROM asset_active WHERE EXISTS (SELECT * FROM anon_1 WHERE asset_active.name = anon_1.name AND asset_active.uri = anon_1.uri)
```

MySQL-compatible backends whose query planner does not support CTEs in DML statements reject this at plan time. On Vitess (and PlanetScale, which is built on Vitess) this fails with:

```
sqlalchemy.exc.NotSupportedError: (MySQLdb.NotSupportedError)
(1235, 'VT12001: unsupported: WITH expression in DELETE statement')
```

Because `_update_asset_orphanage` runs on the scheduler-loop timer (`[scheduler] parsing_cleanup_interval`, default 60s), the exception propagates out of `_run_scheduler_loop` and the scheduler crash-loops every ~60 seconds. CTEs in DML are unsupported in every Vitess release to date (SELECT-side CTEs landed in v19/v21, but DML has none as of v24.0.2), so there is no backend-side upgrade path.

### What you think should happen instead?

The CTE is not semantically required here: `orphan_query` and `activate_query` are each consumed by a single statement, so a plain derived-table subquery (`.subquery()`) is equivalent. Compiled against the MySQL dialect that produces:

```sql
DELETE FROM asset_active WHERE EXISTS (SELECT * FROM (SELECT ... GROUP BY ... HAVING ...) AS anon_1 WHERE ...)
```

which the Vitess planner accepts. The DELETE target (`asset_active`) is not referenced inside the subquery, so MySQL's "can't delete from a table referenced in a subquery" restriction does not apply either. This is a small compatibility widening in the spirit of #40349 (dialect-compat fix for the same method on Postgres).

I understand Vitess is not an officially supported metadata backend — this proposal does not add support for it, it just removes an unnecessary SQL construct that blocks an otherwise MySQL-compatible family of backends (similar planner limits have bitten other generic tools, e.g. prisma/studio#1398, Metabase in planetscale/discussion#161).

### How to reproduce

1. Point `[database] sql_alchemy_conn` at any Vitess keyspace (`mysql+mysqldb://...:.../keyspace?charset=utf8mb4`), e.g. a PlanetScale database.
2. Run `airflow db migrate` (passes) and start the scheduler.
3. Wait one `parsing_cleanup_interval` (60s) — the scheduler exits with the traceback above.

### Are you willing to submit PR?

Yes — a PR replacing the two `.cte()` calls with `.subquery()` (plus the corresponding type hints) is ready.

### Anything else?

Airflow 2.x is unaffected (the dataset-orphan path there does not use a CTE in DML).

Start at SchedulerJobRunner._update_asset_orphanage and _orphan_unreferenced_assets, where the issue identifies the two CTE call sites and related type hints. Inspect the generated MySQL DELETE and verify that the derived-table form is accepted by Vitess. Done means the scheduler no longer crash-loops during orphan cleanup on MySQL-compatible backends without CTE-in-DML support.

Written by the indexing model from the issue text.