Cursor pagination silently drops rows when sorting by a nullable column #68858

Under which category would you file this issue?

Airflow Core

Apache Airflow version

main (and any released 3.x line that ships cursor pagination; affected code in airflow/api_fastapi/common/cursors.py)

What happened and how to reproduce it?

The public REST list endpoints GET .../dagRuns and GET .../taskInstances, when paginated with cursor and sorted by a nullable column (start_date, end_date, duration, run_after), silently drop rows — no error. When the column has NULLs and the result spans more than one page, every row on one side of the NULL/non-NULL boundary is never returned.

All backends are affected — which side vanishes flips with NULL ordering: SQLite/MySQL ASC (and Postgres DESC) drop the non-NULL rows; the reverse drops the NULL rows.

Reproduction (self-contained, uses the real pagination helpers):

from sqlalchemy import create_engine, Integer, String, Column, select
from sqlalchemy.orm import declarative_base, Session
from airflow.api_fastapi.common.parameters import SortParam
from airflow.api_fastapi.common.cursors import encode_cursor, apply_cursor_filter

Base = declarative_base()

class M(Base):
    __tablename__ = "m"
    id = Column(Integer, primary_key=True)
    val = Column(String, nullable=True)   # nullable sort column, e.g. start_date

eng = create_engine("sqlite://")
Base.metadata.create_all(eng)
with Session(eng) as s:
    s.add_all([M(id=i, val=v) for i, v in [(1, None), (2, None), (3, None), (4, "a"), (5, "b"), (6, "c")]])
    s.commit()

sort = SortParam(allowed_attrs=["val"], model=M); sort.set_value(["val"])  # ORDER BY val ASC
returned, token = [], None
with Session(eng) as s:
    while True:
        stmt = sort.to_orm(select(M)).limit(2)
        if token is not None:
            stmt = apply_cursor_filter(stmt, token, sort)
        rows = s.scalars(stmt).all()
        if not rows:
            break
        returned.extend(m.id for m in rows)
        token = encode_cursor(rows[-1], sort)

print(returned)   # [1, 2, 3]  -> rows 4, 5, 6 silently dropped

ASC returns [1, 2, 3] (rows 4,5,6 missing); DESC drops [1, 2, 3].

Root cause (airflow/api_fastapi/common/cursors.py): for a NULL cursor value, _nested_keyset_predicate reduces to col IS NULL AND pk > pk_value — the col IS NOT NULL branch meant to cross the boundary is dead. Non-NULL comparisons are also UNKNOWN for NULL rows, so pagination never crosses the boundary from either side. And SortParam.to_orm emits a plain ASC/DESC (no NULLS FIRST/LAST), so NULL position is the dialect default the predicate can't match.

What you think should happen instead?

Every matching row returned exactly once, regardless of NULLs or backend. A fix needs the ORDER BY and the keyset predicate to agree on a deterministic NULL placement — e.g. an explicit null-rank sort key also encoded in the cursor — which would make NULL ordering consistent across dialects (a behavior change on some dialects, worth discussing).

Operating System

Any — backend-dependent, not OS-dependent.

Deployment

Other (local reproduction; not deployment-specific).

Versions of Apache Airflow Providers

Not applicable (airflow-core).

Official Helm Chart version

Not Applicable

Anything else?

Happens every time the conditions hold; silent, so clients (UIs, scripts, exports) can't tell rows are missing.

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct