Azure / Azure/data-api-builder
[Bug]: Cursor-based pagination returns empty pages when sorting by columns with NULL values
- Dominant language
- C#
- Stars
- 1.5k
- Forks
- 370
- Avg merge
- 3d 17h
- Merged PRs (30d)
- 8
Description
Found from this discussion: https://github.com/Azure/data-api-builder/discussions/2953
## Description
Keyset (cursor-based) pagination produces incorrect results when the ORDER BY column contains NULL values. Subsequent pages return zero rows when the page boundary falls between NULL and non-NULL values.
## Root Cause
`BaseSqlQueryBuilder.MakePaginationInequality` generates the WHERE clause for "after cursor" pagination. When the cursor column's value is NULL, it emits a direct comparison like:
`WHERE [Lastname] > @param -- @param is NULL`
In SQL, any comparison against NULL using >, <, or = evaluates to UNKNOWN (not TRUE or FALSE), so the predicate filters out all rows and subsequent pages are empty.
Similarly, when the cursor value is non-NULL but NULL rows exist further in the sort order, those NULL rows are silently dropped because NULL > 'SomeValue' also evaluates to UNKNOWN.
## Steps to Reproduce
* Have a table with a nullable column (e.g., Lastname) where some rows have NULL values
* Query with pagination (e.g., $first=5) and sort by that nullable column ($orderby=Lastname desc)
* Navigate pages until the page boundary falls between non-NULL and NULL values
* Observe that the next page returns no rows (or drops all NULL rows)
## Expected Behavior
All rows should be returned across pages regardless of NULL values in the sorted column. The pagination cursor should correctly handle the transition between NULL and non-NULL values.
## Actual Behavior
* If page 1 contains only non-NULL values and page 2 starts with NULLs → page 2 is empty
* If page 1 contains only NULL values and page 2 starts with non-NULLs → page 2 is empty
* If page 1 contains a mix of NULL and non-NULL values → subsequent pages work correctly
The endCursor returned from the last valid page, when passed as input for the next page, returns no results and a NULL endCursor, confirming the query itself is broken.
### Version
1.7
### Code of Conduct
- [x] I agree to follow this project's Code of Conduct
Contributor guide
Assessment
This issue has not been assessed yet.