NULLS LAST/FIRST

[lloydtabb]

Malloy should have an attitude about NULL and sorting. I'm thinking NULLS LAST is what we generally want. You can always COALESE to a low value if you want to sort them first.

Discussion?

[greg-finley]

A quick survey of various languages:

By default: nulls first when ascending, nulls last when descending:

• BigQuery: https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#order_by_clause
• MySQL: https://dev.mysql.com/doc/refman/8.0/en/working-with-null.html

By default: nulls last when ascending, nulls first when descending:

• Snowflake: https://docs.snowflake.com/en/sql-reference/constructs/order-by.html#usage-notes
• Postgres: https://www.postgresql.org/docs/current/queries-order.html

Three of the four let you throw in a NULLS FIRST or NULLS LAST, though MySQL has this weird minus syntax.

[greg-finley]

In general I agree with the sentiment that nulls are the least important generally and could always be last, but it's weird to diverge from seemingly every language that by default orders them to the top in one of its sort orders.

If the column is high cardinality, it's possible you'd never notice the nulls at all if you have a record limit.