Azure / Azure/data-api-builder

[Bug]: Cursor-based pagination returns empty pages when sorting by columns with NULL values

Open
#3,234 0 comments 0 reactions 1 assignee Claimed by @aaronburtle View on GitHub
2.x bug
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.