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.
So then I wanted to verify the behavior for Oracle and I found this:
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.