Add option for sorting nulls first

[npgsql]

Add option for sorting nulls first

[roji]

Probably provide a mechanism for opting out of this.

Think hard about what this means for existing databases.

[roji]

The main trigger for this feature was the EF Core functional test suite, which was written and tested on SQL Server where nulls are sorted first, so many tests would fail and had to be skipped.

As this feature probably isn't interesting in any other context, implemented a context option for generating ORDER BY ... NULLS FIRST, but without any index generation - at the moment this is solely for running the EF Core tests properly. The methods involved are internal to avoid users invoking them and triggering a performance degradation (because indexes are still nulls last).

If anyone is interested in having this exposed publicly please post here.

[richardsheppard-avalara]

@roji I would be interested in using this feature if it were exposed publicly.

[roji]

@richardsheppard-avalara would it be possible to know more about your use case, and why you think you need this?

[richardsheppard-avalara]

@roji I might have misread the purpose this ticket, but the use case is we have certain numeric fields in our database that are nullable which cause issues when sorting. We have APIs wrapped around our tables, so that user can specify which properties they would like to sort on and the direction (asc or desc), but when they try to sort a numeric field using desc the null values sort to the top, they would rather see them sort last. I believe that would be the nulls last option.

[roji]

@richardsheppard-avalara thanks for the details - it does sound like this feature could be what you want. If I understood correctly you want nulls to always sort at the end, but when specifying a descending sort they're sorted at the beginning. This makes me see that I've implemented NULLS FIRST, but there's no option for specifying NULLS LAST (which would sort last regardless of ascending/descending).

FWIW, in C# OrderBy() places nulls at the beginning, but when you use OrderByDescending() they get sorted to the end. In other words, in C# you have no option for specifying null sorting - nulls are always considered "smaller" than non-nulls (whereas PostgreSQL considers them "greater" than non-nulls by default).

Simply tacking on NULLS FIRST or NULLS LASTis easy enough, but this needs to happen on indices as well (https://www.postgresql.org/docs/current/static/indexes-ordering.html), and anyone changing the null sorting behavior in a migration needs to be duly warned etc.

I definitely won't be able to implement this anytime soon. Anyone else who's interested in this should post or vote to let me know they need it.

[roji]

FYI pushed aa2d342 which places NULLS LAST on descending sorts (previously we were placing NULLS FIRST on all sorts). This makes PostgreSQL behave just like C# OrderBy/OrderByDescending.