feat: support for NULLS FIRST / NULLS LAST in ordering

[wesm]

Issue by wesm Saturday Jan 24, 2015 at 20:55 GMT Originally opened as http://github.mtv.cloudera.com/wesm/ibis/issues/77

---

[saulpw]

I think NULLS LAST should be the default, because when I ask for the top N in a query, I want to get the top N, and not N nulls. I don't know that we need an option to do something different though, to be honest.

I'm closing this for now, in the absence of a concrete use case. If anyone needs this, please comment here and we'll consider reopening.

[goodwanghan]

@saulpw @cpcloud NULLS FIRST and NULLS LAST are part of the SQL that major backends all support. I think this should be implemented.

https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#order_by_clause

https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-order-by/

https://duckdb.org/docs/sql/query_syntax/orderby

Also notice different backends have different default behaviors:

PostgresSQL: By default, null values sort as if larger than any non-null value; that is, NULLS FIRST is the default for DESC order, and NULLS LAST otherwise.

DuckDB: By default if no modifiers are provided, DuckDB sorts ASC NULLS FIRST, i.e. the values are sorted in ascending order and null values are placed first. This is identical to the default sort order of SQLite. PostgreSQL by default sorts in ASC NULLS LAST order.

So it is important to expose this so that users can get deterministic (and configurable) behavior on different backends.