Silent row loss: a merge racing `DETACH PARTITION` commits an empty part that covers the detached data, and `ATTACH PARTITION` then strands the rows as `inactive_*`

## Describe what's wrong

On plain `MergeTree`, a merge (or mutation) that is in flight when `ALTER TABLE ... DETACH PARTITION`
runs can commit a **result part with 0 rows whose block range covers a non-empty source part**.
The empty part becomes active. From that moment the partition's data is doomed:

1. A later `DETACH PARTITION` puts both the empty covering part and the real part into `detached/`.
2. `ATTACH PARTITION` resolves covering relationships *within* `detached/`, picks the empty part as
the containing part, renames the real part to `inactive_`, and attaches the empty one.
3. `inactive_`-prefixed parts are deliberately never attached, and `ATTACH PART 'inactive_...'` is
rejected with `Code: 233 Unexpected part name`.

Every statement returns success. The rows are still on disk but there is no SQL path back to them.

The first step is the actual defect: **a merge must never turn 300 rows into 0**. Steps 2 and 3 are
`ATTACH PARTITION` behaving as designed on top of a corrupt covering relationship, and they are what
makes the loss unrecoverable rather than merely transient.

Reproduces on **`26.9.1.943`** (re-verified) and `26.9.1.572`. Single node, plain `MergeTree`,
default settings apart from `old_parts_lifetime=1`, which only shortens the window.

**Two end states are possible.** Usually the rows survive on disk as `detached/inactive_*` and are
unreachable through SQL. Sometimes they are gone outright — this run ended with a single active part
holding **0 rows** and an **empty** `detached/`:

```
final active parts : [('0_38_38_0_40', 0)]
detached parts : []
```

## How to reproduce

Concurrently, against one table:

- a background thread cycling `SYSTEM STOP MERGES` / `SYSTEM START MERGES` and reading other partitions
- a foreground thread issuing an **unpaired** random mix of `DETACH PARTITION 0`, `ATTACH PARTITION 0`,
`OPTIMIZE ... FINAL`, `OPTIMIZE ... PARTITION n`, `ALTER ... UPDATE v = v WHERE p = 0`,
`ALTER ... CLEAR COLUMN s IN PARTITION 0`

Script (fires within a handful of seeds, a few seconds each):

```python
import time, threading, random, urllib.parse, urllib.request, urllib.error
BASE = "http://127.0.0.1:8123/"
def q(sql, s=None):
req = urllib.request.Request(BASE + "?" + urllib.parse.urlencode(dict(s or {})),
data=sql.encode(), method="POST")
try:
with urllib.request.urlopen(req, timeout=90) as r: return True, r.read().decode()
except urllib.error.HTTPError as e: return False, e.read().decode()[:200]
def sc(sql):
ok, o = q(sql + " FORMAT TSVRaw"); return o.strip() if ok else "ERR:" + o[:80]
T = "rp"
for seed in range(50):
random.seed(seed)
q("DROP TABLE IF EXISTS %s SYNC" % T)
q("CREATE TABLE %s (p Int32, id UInt64, v Int64, s String) ENGINE=MergeTree "
"PARTITION BY p ORDER BY (p,id) SETTINGS old_parts_lifetime=1" % T)
for b in range(6):
q("INSERT INTO %s SELECT number %% 4, number + %d, number, toString(number) FROM numbers(200)" % (T, b*1000))
want = sc("SELECT count() FROM %s WHERE p=0" % T)
stop = threading.Event()
def churn():
while not stop.is_set():
q("SYSTEM STOP MERGES %s" % T); q("SYSTEM START MERGES %s" % T)
q("SELECT count() FROM %s WHERE p!=0" % T)
th = threading.Thread(target=churn); th.start()
for _ in range(40):
op = random.choice(["D", "A", "OF", "OP", "U", "C"])
sql = {"D": "ALTER TABLE %s DETACH PARTITION 0" % T,
"A": "ALTER TABLE %s ATTACH PARTITION 0" % T,
"OF": "OPTIMIZE TABLE %s FINAL SETTINGS optimize_throw_if_noop=0" % T,
"OP": "OPTIMIZE TABLE %s PARTITION %d SETTINGS optimize_throw_if_noop=0" % (T, random.randrange(4)),
"U": "ALTER TABLE %s UPDATE v = v WHERE p = 0" % T,
"C": "ALTER TABLE %s CLEAR COLUMN s IN PARTITION 0" % T}[op]
q(sql, {"mutations_sync": "1"} if op in ("U", "C") else None)
stop.set(); th.join()
q("ALTER TABLE %s ATTACH PARTITION 0" % T)
got = sc("SELECT count() FROM %s WHERE p=0" % T)
if got != want:
print("seed %d LOST want=%s got=%s" % (seed, want, got))
print(sc("SELECT groupArray((name,rows)) FROM system.parts "
"WHERE table='%s' AND active AND partition='0'" % T))
print(sc("SELECT groupArray((name,reason)) FROM system.detached_parts "
"WHERE table='%s' AND partition_id='0'" % T))
break
```

## Captured trace

Row counts and active parts after every statement, seed 6. The partition holds 300 rows throughout
the first 32 steps.

```
32 ATTACH ok rows=300 active=[('0_39_39_0_41', 0), ('0_40_40_0_41', 300)] det=[]
33 DETACH ok rows=0 active=[('0_39_39_1_41', 0), ('0_40_40_1_41', 0)] det=['0_40_40_0_41','0_39_39_0_41']
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
a merge of the 300-row part 0_40_40_0_41 committed 0_40_40_1_41 with 0 rows
...
39 DETACH ok rows=0 det=['0_40_40_0_41','0_39_39_1_44','0_40_40_1_44','0_39_39_0_41']
(final ATTACH PARTITION)
active = [('0_46_46_0', 0), ('0_47_47_0', 0)]
detached = [('inactive_0_40_40_0_41','inactive'), ('inactive_0_39_39_0_41','inactive')]
```

On disk after the final `ATTACH PARTITION`, the rows are intact but orphaned:

```
detached/inactive_0_40_40_0_41/count.txt = 300 <-- the data
detached/inactive_0_39_39_0_41/count.txt = 0
```

`ATTACH PART 'inactive_0_40_40_0_41'` -> `Code: 233. DB::Exception: Unexpected part name`.

An earlier step in the same run shows the empty part being minted for the first time, here by a
mutation rather than a merge:

```
1 OPTIMIZE ok rows=300 active=[('0_1_21_1', 300)] det=[]
2 DETACH ok rows=0 active=[('0_1_21_2_25', 0)] det=['0_1_21_1']
```

Once such a part exists it survives every subsequent `DETACH`/`ATTACH` cycle (renumbered each time)
until its block range happens to cover the real part inside `detached/`.

## Where it becomes unrecoverable

`MergeTreeData::getPartsFromDetached` (src/Storages/MergeTree/MergeTreeData.cpp:10405-10430) builds an
`ActiveDataPartSet` from the detached part *names* only, and for every detached part that is covered by
another detached part does:

```cpp
/// Inactive parts are renamed so they can not be attached in case of repeated ATTACH.
if (containing_part != part_info.dir_name)
part_info.disk->moveDirectory(fs::path(relative_data_path) / source_dir / part_info.dir_name,
fs::path(relative_data_path) / source_dir / ("inactive_" + part_info.dir_name));
```

That rule is correct in normal operation, but it trusts the name-level covering relationship. Because
`0_40_40_1_44` (0 rows) covers `0_40_40_0_41` (300 rows), the real data is renamed away and the empty
part is attached. Nothing warns, and the operation reports success.

## Expected behaviour

- A merge or mutation whose source parts were removed from the working set by a concurrent
`DETACH PARTITION` must be cancelled, not committed. It must never publish an empty part that
covers a non-empty range.
- Failing that, `ATTACH PARTITION` should refuse to strand a non-empty part behind an empty covering
part, or at least log a warning naming the parts it is about to make unattachable.

## Frequency

It is a race, but a very reachable one.

| harness | shape | rounds losing rows |
| --- | --- | --- |
| single DDL thread, 6 operations | the script above | **~30%** (57 of 187) |
| **two DDL threads**, plus `FREEZE`/`UNFREEZE`/`MOVE PARTITION TO DISK` | widened | **82%** (27 of 33) |
| same, with `MOVE PARTITION TO TABLE` added | widened | 66% (19 of 29) |

The script above fires within about seven seeds, a few seconds each.

**The widened recipe** — what takes it from 30% to 82% — is running **two** concurrent DDL threads
over the same partition, drawing from `DETACH PARTITION`, `ATTACH PARTITION`, `OPTIMIZE FINAL`,
`OPTIMIZE PARTITION`, `ALTER UPDATE`, `CLEAR COLUMN IN PARTITION`, `MOVE PARTITION TO DISK`,
`FREEZE PARTITION WITH NAME` and `UNFREEZE PARTITION WITH NAME`, while a third thread cycles
`SYSTEM STOP MERGES` / `SYSTEM START MERGES` and reads the other partitions.

Controls that stay clean and narrow the trigger:
- paired `DETACH` then `ATTACH` with only `OPTIMIZE` churn: 24 trials, no loss
- a single slow mutation (`sleepEachRow`) racing one `DETACH`: 12 trials across 4 timings, no loss --
the in-flight mutation is cancelled correctly in that shape
- partitions not targeted by the DDL: 205,999/205,999 reads saw the exact expected contents
throughout, so the damage is confined to the partition the DDL names
- the same widened op mix with `use_query_condition_cache=0` and caches dropped per round behaves
identically, so this is not a caching artefact

The trigger needs the **unpaired** DETACH/ATTACH mix (so parts accumulate at several levels and
mutation versions) together with concurrent merges and `SYSTEM STOP/START MERGES`.

## Related

- #50922 -- same symptom reported in 2023 ("Data in detached partition may disappear completely after
attach partition"), with lightweight deletes, labelled `question`, no mechanism or repro; still open
- #116633 -- concurrent `DETACH PARTITION` silently reverting an acknowledged lightweight `UPDATE`;
different mechanism (patch-part TOCTOU in `assertNoPatchesForParts`), same "DETACH does not wait for
in-flight work" family
- #108264 -- `MOVE/ATTACH/DETACH PARTITION` dropping un-merged patch parts
- commit `fc46effe16f` "Fix race between DETACH and merges" -- an earlier fix in this area

Start with the Python reproduction script and inspect MergeTreeData::getPartsFromDetached in src/Storages/MergeTree/MergeTreeData.cpp:10405-10430, then review commit fc46effe16f and the related race issues. Done means concurrent DETACH, ATTACH, merge, and mutation activity cannot publish an empty covering part or strand non-empty data as inactive_ parts, with the reproduction retaining its expected row count.

Written by the indexing model from the issue text.