#1257 ORDER BY NULL Logic Not Consistent

[Anonymous]

We use HSQLDB for testing to mimic Oracle.. Just recently I upgraded HSQLDB and was surprised that our test cases were failing when checking if NULL was first or last.

This is when I first learned about NULL FIRST/ NULL LAST. Unfortunately we use Hibernate which doesn't support those options.
https://hibernate.onjira.com/browse/HHH-465

So then I wanted to verify the behavior for Oracle and I found this:
http://docs.oracle.com/javadb/10.8.2.2/ref/rrefsqlj13658.html

In HSQLDB, by default it's either always first or always last. There isn't logic to have a best guess. I would recommend that by default it uses the "best guess" in order to prevent surprises on upgraders.

[Fred Toussi]

There can be no "guessing" in SQL processing. What you mean is if the NULLS clause is not used and you are setting the SET DATABASE SQL SYNTAX ORA TRUE, then rows with null should be ordered according to ASC / DESC setting.

[Fred Toussi]

BTW, your link on the Oracle site is for JavaDB / Derby, not Oracle database. However, the null ordering seems to be the same in those databases.

[Fred Toussi]

Setting new property sql.nulls_order=false will result in null ordering changing with ASC and DESC. You also need to set sql.nulls_first=false to change the default null ordering (with ASC) to NULLS LAST.