zh-jn commented on issue #58554:
URL: https://github.com/apache/airflow/issues/58554#issuecomment-5535044661
Adding a root-cause data point for future readers landing here. On Airflow
**3.2.2** the same symptom (backfill dag_run `state=running`, all task
instances `state=NULL`, `last_scheduling_decision=NULL` forever) reproduces
deterministically whenever running
non-backfill dag_runs ≥ `max_dagruns_per_loop_to_schedule`. Raising the
config does mask it, but the underlying cause is a sort-direction bug in
`get_running_dag_runs_to_examine`.
**Root cause** — `airflow-core/src/airflow/models/dagrun.py:619`:
```python
.order_by(
nulls_first(cast("ColumnElement[Any]", BackfillDagRun.sort_ordinal),
session=session), # ← reversed
nulls_first(cast("ColumnElement[Any]", cls.last_scheduling_decision),
session=session),
cls.run_after,
)
```
`sort_ordinal` lives on the LEFT-JOINed `backfill_dag_run` row and is `NULL`
for every non-backfill dag_run. `nulls_first` therefore sorts *non-backfill*
runs before backfill runs, exactly the opposite of the field's apparent intent.
With
`.limit(DEFAULT_DAGRUNS_TO_EXAMINE)` (default 20) and any healthy multi-DAG
scheduled workload, backfill dag_runs never fit into the window and are
silently skipped — no log, no `scheduled_by_job_id`, no
`last_scheduling_decision`.
**Reproducer** (read-only, in the scheduler pod):
```python
# Production query — backfill dag_run absent
from airflow.models.dagrun import DagRun
from airflow.utils.session import create_session
with create_session() as s:
runs = list(DagRun.get_running_dag_runs_to_examine(session=s))
print(len(runs), sum(1 for r in runs if r.backfill_id is not None))
# Same query with nullslast(sort_ordinal) — backfill dag_runs jump to the top
from sqlalchemy import select, false, func, nullslast
from airflow.models.backfill import BackfillDagRun
from airflow.models.dag import DagModel
from airflow.utils.state import DagRunState
with create_session() as s:
q = (select(DagRun)
.where(DagRun.state == DagRunState.RUNNING)
.join(DagModel, DagModel.dag_id == DagRun.dag_id)
.join(BackfillDagRun, BackfillDagRun.dag_run_id == DagRun.id,
isouter=True)
.where(DagModel.is_paused == false(), DagModel.is_stale == false())
.order_by(nullslast(BackfillDagRun.sort_ordinal), DagRun.run_after)
.where(DagRun.run_after <= func.now())
.limit(DagRun.DEFAULT_DAGRUNS_TO_EXAMINE))
runs = list(s.scalars(q).unique())
print(len(runs), sum(1 for r in runs if r.backfill_id is not None))
```
On the same session we saw production return 20 runs / 0 backfill, and
`nullslast` return 16 runs / **2 backfill** (both stuck ones, top of the list).
**One-line fix** — swap `nulls_first` → `nulls_last` on the `sort_ordinal`
column only (keep `nulls_first` on `last_scheduling_decision`, that one is
intentional):
```diff
- nulls_first(cast("ColumnElement[Any]", BackfillDagRun.sort_ordinal),
session=session),
+ nulls_last (cast("ColumnElement[Any]", BackfillDagRun.sort_ordinal),
session=session),
```
Because this issue is closed I'll open a fresh one referencing here + #49508
+ #69658 so the fix has a place to land. Wanted to leave the diagnosis in this
thread since it's the top search hit for the symptom.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
