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

[aaronburtle]

Found from this discussion: #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

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