Default ordering of NULLs when using ORDER BY

[roji]

When porting Npgsql to rc3, I got a failure on test Optional_Navigation_Null_Coalesce_To_Clr_Type in the GearsOfWar test suite. The root reason for this is the different ordering of NULLs by ORDER BY clauses: while SqlServer sorts nulls first, PostgreSQL sorts them last by default, but supports the SQL Standard option to specify NULLS FIRST or NULLS LAST.

Beyond this particular failing test, I'm wondering what should be done here. To the extent that we're trying to mimic C#/.NET behavior as much as possible, and .NET behavior seems to be to sort nulls first (LINQ OrderBy, List.Sort...), it seems like the PostgreSQL provider should always render ORDER BY ... NULLS FIRST (this could also be a standard option in DefaultQuerySqlGenerator as this is an SQL Standard feature). On the downside, this goes against the provider's natural behavior (i.e. surprising for PostgreSQL people), and might have some performance impact (not sure about this)

What are your thoughts about this?

[bricelam]

Triage: Where possible, providers should match the semantics of .NET--in this case, order null values first. However, providing a mechanism for users to opt-out might be a good idea given the implications.