Thread: NULLs and sort order

The JDBC driver's test suite currently fails on DatabaseMetaData
methods that provide information about NULLs and sort order.
I've looked through the documentation, but couldn't find
anything about it.

The JDBC driver returns different values, depending on the
backend version (<7.2 vs. >= 7.2), so this suggests something
changed recently. This is probably also the cause of the test
suite failure, since the test case has no conditional coding for
different backend versions. So presumably, the test suite needs
to be updated, but I wanted to doublecheck the functionality on
this list.

What we need to know is:
- Do null values sort higher or lower than any other value in a
domain? Higher would mean that null values appear at the end in
an ascending sort order.
- Will null values appear at the start or the end regardless of
the sort order?

Currently the JDBC driver says:
- Backend >= 7.2 sorts nulls higher than any other value in a
domain. In other words: ascending means nulls at the end,
descending means nulls at the start.
- Backend < 7.2 puts nulls at the end regardless of sort order.

Can someone confirm if this is correct?

Would it be useful to add this information to the documentation,
e.g. the documentation of ORDER BY in SELECT?

Regards,
René Pijlman <rene@lab.applinet.nl>

Rene Pijlman writes:

> Currently the JDBC driver says:
> - Backend >= 7.2 sorts nulls higher than any other value in a
> domain. In other words: ascending means nulls at the end,
> descending means nulls at the start.
> - Backend < 7.2 puts nulls at the end regardless of sort order.

That is correct.

> Would it be useful to add this information to the documentation,
> e.g. the documentation of ORDER BY in SELECT?

Most likely.

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

On Sun, 9 Sep 2001 15:25:17 +0200 (CEST), you wrote:
>That is correct.

Thanks.

>> Would it be useful to add this information to the documentation,
>> e.g. the documentation of ORDER BY in SELECT?
>
>Most likely.

I'll post it on the docs list.

Regards,
René Pijlman <rene@lab.applinet.nl>

Peter Eisentraut <peter_e@gmx.net> writes:
> Rene Pijlman writes:
>> Currently the JDBC driver says:
>> - Backend >= 7.2 sorts nulls higher than any other value in a
>> domain. In other words: ascending means nulls at the end,
>> descending means nulls at the start.
>> - Backend < 7.2 puts nulls at the end regardless of sort order.

> That is correct.

Actually it's more complex than that.  7.2 will provide the above-stated
consistent ordering of nulls relative to non-nulls.  The problem with
earlier versions is that the ordering of nulls depends on what plan the
optimizer chooses for the query: sorting based on a scan of a btree
index would work the same as is described for 7.2, whereas sorting
based on an explicit sort step would put the nulls at the end (for
either ASC or DESC sort).  So there was *no* consistent behavior at all
in prior versions.  The fix that's been applied for 7.2 is to make
explicit sorts act the same as indexscans already did.

            regards, tom lane