Should Ibis standardize the position of null values in sorted columns?

[ianmcook]

Currently Ibis does not standardize the position of nulls in ordered output. If a user calls t.order_by(_.x) or t.order_by(_.x.desc()) and the column x has a mix of null and non-null values, then whether the null values appear at the top (first) or the bottom (last) of the results depends entirely on the default behavior of the Ibis backend you are using. Also, Ibis currently does not expose an option to allow users to control whether nulls sort first or last.

The behavior of the backend engines is not consistent, but almost all of them do expose an option to control whether nulls sort first or last:

backend	default sort position of nulls	user can specify nulls first or last
bigquery	first for ascending, last for descending	yes
clickhouse	last	yes
dask	last	yes
datafusion	last for ascending, first for descending	yes
druid	?	?
duckdb	last (since 0.8)	yes
impala	last for ascending, first for descending	yes
mssql	first for ascending, last for descending	no
mysql	first for ascending, last for descending	no
oracle	last for ascending, first for descending	yes
pandas	last	yes
polars	first (but there's a proposal to change it to last)	yes
postgres	last for ascending, first for descending	yes
pyspark	first for ascending, last for descending	yes
snowflake	last for ascending, first for descending	yes
sqlite	first for ascending, last for descending	yes
trino	last	yes

For the backends that do not directly expose the option to control nulls first or nulls last in SQL (mssql, mysql), there is a simple workaround to control this: do ORDER BY x IS NOT NULL, x to get nulls first and do ORDER BY x IS NULL, x to get nulls last.

Note that this does not consider NaNs in float columns, which are another story and should probably be discussed separately.

Should Ibis standardize null sort order behavior across all the backends?

[cpcloud]

We have this issue we could resurrect #77

👍🏻 on supporting an API for this

[ianmcook]

FYI DuckDB changed their behavior from first to last in 0.8: duckdb/duckdb#7174

[lostmygithubaccount]

I'm in favor of a random.random() > .5 call to determine first/last personally

[saulpw]

I vote "last" as per my comment in #77: when I ask for the top N in a query, I want to get the top N, and not N nulls.

[ianmcook]

+1 to what @saulpw said. The DuckDB folks also argued this:

NULLS LAST is more intuitive when using ORDER BY in conjunction with LIMIT. With NULLS FIRST, the following queries return NULL values instead of the desired top-n results:

[ianmcook]

Because DuckDB (and maybe other engines?) provides a global option to change the default null sort order, Ibis should probably explicitly specify nulls last even when running queries on engines that default to nulls last.

[ianmcook]

A piece of implicit context that is worth making explicit here: all of these engines default to ascending sort order. So Ibis doesn't need to do anything new to standardize ascending/descending.

[cpcloud]

Thanks for the discussion all!

I think we'll default to NULLS LAST, and provide an API for specifying null ordering in table.order_by.

I've created #7252 to track this.