On 13-3-2010 12:40, svanderclock wrote:

> hello,
>
> on FB1.5 NULLs were always placed at the end of a sorted set, no matter whether the order was ascending or descending.
>
> In Firebird 2, NULLs are considered "smaller" than anything else when it comes to sorting. Consequently, they come first in ascending sorts and last in descending sorts
>
> 1. why this behavior ? it's more logical to put null at the end of a sorted set like it was in FB1.5 ...

The SQL standard specifies that the sort order of NULL should be
consistent (that is it is either greater than all values, or smaller).
Which implies that if they are last for ascending sorts, they should be
first for descending sorts and vice versa. So the current behavior
conforms to the SQL standards, the old one did not.

The quote from SQL 92:
Whether a sort key value that is null is considered greater or less than
a non-null value is implementation-defined, but all sort key values that
are null shall either be considered greater than all non-null values or
be considered less than all non-null values.

> yes we have the option to override the default NULLs placement, but in this way no index will be used for sorting :(

I actually don't see why the index would not work in those cases, but
that could depend on the implementation

--
Mark Rotteveel