Azure / Azure/data-api-builder

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

Abierto
#3,234 0 comentarios 0 reacciones 1 asignado Reclamado por @aaronburtle Ver en GitHub
2.x bug
Lenguaje dominante
C#
Estrellas
1.5k
Forks
370
Merge medio
3 d 22 h
PR fusionados (30 d)
9

Descripción

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

Guía de contribución

Abrir la guía de contribución

Evaluación

Este issue todavía no se ha evaluado.

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.