apache / apache/airflow

dagRuns API: start_date_gte filter matches terminal (failed/success) runs with a NULL start_date regardless of age

Open
#70,627 1 comment 0 reactions 0 assignees View on GitHub
area:API kind:bug needs-triage
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.