Re: 7.4, 'group by' default ordering?

Tom Lane wrote:

>Bruno Wolff III <bruno(at)wolff(dot)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