dagRuns API: start_date_gte filter matches terminal (failed/success) runs with a NULL start_date regardless of age
- Dominant language
- Python
- Stars
- 46.9k
- Forks
- 17.8k
- Avg merge
- 2d 9h
- Merged PRs (30d)
- 472
Description
### Under which category would you file this issue?
Airflow Core
### Apache Airflow version
3.2.2
### What happened and how to reproduce it?
### What happened
`GET /dags/~/dagRuns?start_date_gte=` returns dag runs whose `start_date` is `NULL`, no matter how old the run's `logical_date`/`end_date` is — even when the run is in a terminal state (`failed`, `success`).
Found while querying recent dag runs against `/dags/~/dagRuns` with `start_date_gte` set to a recent cutoff: the response included dag runs from **2021–2024** (`state: failed`, `start_date: null`, populated `end_date`) as if they had just occurred, alongside genuinely recent runs. Roughly 99% of a `-logical_date`-ordered page were these stale null-`start_date` rows, drowning out real recent activity.
### Root cause
`NullableDatetimeRangeFilter.to_orm` (added in #66696 to fix #66335's COALESCE index-scan performance issue) builds the lower-bound predicate as:
```python
select = select.where(or_(self.attribute >= x, self.attribute.is_(None)))
```
The docstring justifies this as: "For lower bounds the NULL branch passes unconditionally — a not-yet-started/ended task will eventually satisfy any past lower bound." That assumption only holds while the row is *non-terminal* (`running`/`queued`, genuinely pending a future start). It's false for a dag run that is already `failed`/`success` with a persisted `NULL` `start_date` — that row will never "eventually" start, yet it now matches every `start_date_gte` filter forever, regardless of the query's cutoff.
### How to reproduce
1. Have a dag run in a terminal state (`failed`/`success`) whose `start_date` is `NULL` (e.g. an old manual trigger that failed before the scheduler set `start_date`).
2. Query `/dags/~/dagRuns?start_date_gte=&order_by=-logical_date`.
3. Observe the terminal, null-`start_date` row is included in the response regardless of how far in the past its `logical_date`/`end_date` actually is.
### What you think should happen instead?
The NULL-passes-unconditionally branch should be scoped to non-terminal states (or the filter should additionally require `end_date IS NULL` alongside `start_date IS NULL`, since a genuinely-pending run shouldn't have an `end_date` either). A terminal dag run with a null `start_date` should not satisfy a `start_date_gte` lower bound at all — it never had a `start_date` within the window, or any window.
### Operating System
distroless image base
### Deployment
Official Apache Airflow Helm Chart
### Apache Airflow Provider(s)
_No response_
### Versions of Apache Airflow Providers
N/A
### Official Helm Chart version
Not Applicable
### Kubernetes Version
Not Applicable
### Helm Chart configuration
Not Applicable
### Docker Image customizations
Not Applicable (to this issue)
### Anything else?
This is the opposite failure mode from #66047 (UI not passing the filter param at all) and a different bug from #66335 (COALESCE hurting index usage) — #66696, which fixed #66335, introduced this NULL-passthrough regression as a side effect and marked itself "no user-visible behavior change."
### Are you willing to submit PR?
- [ ] Yes I am willing to submit a PR!
### Code of Conduct
- [x] I agree to follow this project's [Code of Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)
Contributor guide
Research direction
Start at NullableDatetimeRangeFilter.to_orm, then trace the dagRuns API handling of start_date_gte and its terminal-state data. Reproduce the query with an old terminal run whose start_date is NULL, and add coverage showing that it is excluded while genuinely pending NULL-start runs retain the intended behavior.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- python
- Domain
- api, databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 65/100