Re: 7.4, 'group by' default ordering? - Mailing list pgsql-general

Tom Lane wrote:

>Bruno Wolff III <bruno@wolff.to> writes:
>
>
>>On Thu, Jan 08, 2004 at 13:42:33 -0600,
>>
<snip>

>>
>>
>>>On a mostly unrelated topic, does the SQL standard indicate whether NULL
>>>should sort to the front or the back?  Is there a way to force it to
>>>one or the other independent of whether the order by clause uses
>>>ascending or descending order?
>>>
>>>
>
>
>
>>In SQL for Smarties, Joe Ceclko says that either NULLs should all be first
>>or all be last (independent of whether the sort is ascending or descending).
>>
>>
>
>If Celko really says that, I think he's wrong.  SQL92 13.1 general rule
>3 says:
>
>              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.
>
>Since they use the phraseology "greater than" and "less than", I'd
>expect that switching between ASC and DESC order would reverse the
>output ordering, just as it would for two ordinary values one of which
>is greater than the other.
>
>We actually went to some trouble to make this happen, a release or three
>back.  IIRC, at one time PG did sort NULLs to the end regardless of
>ASC/DESC, but we were persuaded that this was contrary to spec.
>
>            regards, tom lane
>
>
Celko uses the SQL92 13.1 rule wording: "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 will
either be considered greater than all non-NULL values or be considered
less than all non-NULL values. There are SQL products that do it either
way."  2nd Ed SQL For Smarties.

And of more interest, he also points out that in SQL-89, the last
General Rule of <comparison predicate> should still be applied:
"Although 'x=y' is unkown if both x and y are NULL values, in the
context of GROUP BY, ORDER BY, and DISTINCT, a NULL value is identical
to or is a duplicate of another NULL value." So NULL=NULL for purposes
of GROUP BY, ORDER BY and DISTINCT. PostgresSQL seems to treat them this
way and puts them after non-NULL values.

Here's my test case:

Welcome to psql 7.4beta5, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

celko=# CREATE TABLE sortable (a INT DEFAULT NULL, b INT);
celko=# INSERT INTO sortable (b) VALUES (8);
INSERT 60836961 1
celko=# INSERT INTO sortable (b) VALUES (4);
INSERT 60836962 1
celko=# SELECT a,b FROM sortable ORDER BY a,b;
 a | b
---+---
   | 4
   | 8
(2 rows)

celko=# INSERT INTO sortable (a,b) VALUES (5,5);
INSERT 60836963 1
celko=# SELECT a,b FROM sortable ORDER BY a,b;
 a | b
---+---
 5 | 5
   | 4
   | 8
(3 rows)

celko=# INSERT INTO sortable (b) VALUES (5);
INSERT 60836964 1
celko=# SELECT a,b FROM sortable ORDER BY a,b;
 a | b
---+---
 5 | 5
   | 4
   | 5
   | 8
(4 rows)

celko=# INSERT INTO sortable (a,b) VALUES (2,2);
INSERT 60836965 1
celko=# SELECT a,b FROM sortable ORDER BY a,b;
 a | b
---+---
 2 | 2
 5 | 5
   | 4
   | 5
   | 8
(5 rows)

celko=# SELECT a,b FROM sortable ORDER BY b,a;
 a | b
---+---
 2 | 2
   | 4
 5 | 5
   | 5
   | 8
(5 rows)

FYI
Ron