Fixed relative cursors to handle nullable types.

[sleepertassly]

🧭 Paging Cursor Enhancements: Nullable Keys and Null Ordering Support

Summary

This PR introduces enhancements to the paging logic to correctly handle sorting and filtering when cursor keys are nullable. The main change is that the WHERE condition used in pagination must now adapt based on three factors:

1. Whether the cursor key is nullable.
2. Whether the actual cursor value is null or not.
3. Whether nulls should be ordered first or last, as explicitly controlled by the cursor.

---

🔍 Why This Is Needed

When performing cursor-based pagination on fields that can be null, it's essential to construct the WHERE clause and ordering logic correctly. Incorrect handling of null values can result in missing or duplicated records across pages.

By explicitly controlling null ordering, we avoid relying on database-specific behavior and ensure consistent, predictable results.

---

🔧 Implementation Details

To support this behavior, the following changes were made:

• Cursor Key Metadata
  Cursor keys now include a property (IsNullable) to indicate whether the field is nullable.

• Null Ordering in Cursor
  Cursors carry a NullsFirst flag to indicate how null values should be ordered.
  Null ordering is enforced by appending a secondary SQL clause for all nullable cursor keys:

  • For nulls last:
    ORDER BY column IS NULL ASC

  • For nulls first:
    ORDER BY column IS NULL DESC

• Ordering Extraction and Rewriting
  When cursors are constructed from the expression's OrderBy methods, all original OrderBy conditions are removed.
  These are later reconstructed from the cursor keys, ensuring that the resulting order is explicit and correct for the generated pagination WHERE clause.

---

✅ Benefits

• Correct pagination over nullable fields
• Eliminates edge cases where null records might be skipped or duplicated
• Consistent behavior regardless of underlying database sort behavior
• Explicit, predictable handling of null values in cursor logic

---

⚠️ Side Effects / Limitations

• The use of an additional sort condition for null handling may slightly affect query performance.
  However, it ensures correctness and removes ambiguity when dealing with nullable fields.

• If the original expression contains an OrderBy that cannot be transformed into a cursor key (e.g., because it uses an unsupported expression in Green Donut), that ordering will be skipped.
  As a result, pagination may not follow the full original sort semantics in such cases.

[CLAassistant]

All committers have signed the CLA.

[michaelstaib]

The PR looks very good.

I'm planning to add this extra ORDER BY condition before all nullable cursor key, but that hasn't been implemented yet. I'm still thinking about the performance impact. The computed orderby expression OrderBy(t => t.FoundedDate == null) which moves all null values to the beginning can significantly slow down sorting, but I believe it's still faster than OFFSET/LIMIT pagination.

This is another aspect we were also looking into ... especially as with postgres null order can be configured to be at the beginning of a set or at the and of a set.

I will copy over some new tests that we had in our work branch for nullable cursor values. Ping me on slack once you have solved the null ordering issue and we can see to get this one merged.

[michaelstaib]

Ping me once you need help, want another review or are ready to merge.

[glen-84]

Hey @sleepertassly – just checking in to see if you're planning on getting this PR over the line?

[sleepertassly]

Hi @glen-84 — yes, I'm working on it. I estimate it will take about 5–6 more hours to finish, but I’m not sure when I’ll have that much free time this week or next.

For the cursor to work properly across all databases, we need additional information: whether the database places nulls at the beginning or end of the sorted list. My initial idea — to determine this behavior from the first page of results — didn’t work out.

I’m now working on a solution where we explicitly force nulls to appear at either the beginning or end by adding an extra ordering condition for all nullable fields. For example: ORDER BY (name IS NULL) ASC, name.

[michaelstaib]

@sleepertassly we gonna take over the work on this issue if its OK with you as we want to move this in one of the next patch releases.

[sleepertassly]

michaelstaib

No problem. Tomorrow, I will clean up and push all my local changes so they’re accessible to you.

[sleepertassly]

@MichaelStaib I’ve pushed my local changes. Currently, the ExpressionHelpers.ApplyCursorKeyOrdering method is not yet finished, and the related code is still untested.

The goal is to remove all original ordering conditions when generating the cursor keys, and then rebuild the ordering from those cursor keys after the pagination WHERE conditions have been created.

[ErlendSW]

@michaelstaib Any progress on this PR?

[michaelstaib]

Hey @ErlendSW,

yes ... however we moved work on this to #8431.

This issue will be addressed with Hot Chocolate 16, we are actively working on this.