[I] Cursor pagination silently drops rows when sorting by a nullable column [airflow]

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

steveahnahn opened a new issue, #68858:
URL: https://github.com/apache/airflow/issues/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 `NULL`s 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):
   
   ```python
   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
   - [x] I agree to follow this project's Code of Conduct
   


-- 
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] Cursor pagination silently drops rows when sorting by a nullable column [airflow]

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

pierrejeambrun closed issue #68858: Cursor pagination silently drops rows when sorting by a nullable column
URL: https://github.com/apache/airflow/issues/68858


-- 
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