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

["AaronCoquet-Easypark (via GitHub)" <gi...@apache.org>]

AaronCoquet-Easypark opened a new issue, #70627:
URL: https://github.com/apache/airflow/issues/70627

   ### 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=<recent-timestamp>` 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=<any-recent-timestamp>&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)
   


-- 
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: commits-unsubscribe@airflow.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

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

["waterWang (via GitHub)" <gi...@apache.org>]

waterWang commented on issue #70627:
URL: https://github.com/apache/airflow/issues/70627#issuecomment-5106040035

   I will fix this. The issue is in `NullableDatetimeRangeFilter.to_orm()` — for lower bounds on `start_date`, the `NULL` branch passes unconditionally, which causes terminal dag runs with `NULL start_date` to match every `start_date_gte` filter. The fix: add an `extra_null_condition` parameter to `NullableDatetimeRangeFilter`, and for `start_date` lower bounds, require `end_date IS NULL` alongside `start_date IS NULL`.


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

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

["pierrejeambrun (via GitHub)" <gi...@apache.org>]

pierrejeambrun closed issue #70627: dagRuns API: start_date_gte filter matches terminal (failed/success) runs with a NULL start_date regardless of age
URL: https://github.com/apache/airflow/issues/70627


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org