Обсуждение: [HACKERS] NULL field records handling in order clause

PostgreSQL 6.1 , Linux RedHat 4.2 Kernel 2.0.30

Selecting all records and ordering ascending and descending on a field :

template1=> select * from pers order by cod;
             cod|nume  |salariu
- ----------------+------+-------
10              |Vasile|  45300
20              |Ion   | 192300
30              |Mihai |  92300
                |Fane  |
(4 rows)

template1=> select * from pers order by cod desc;
             cod|nume  |salariu
- ----------------+------+-------
30              |Mihai |  92300
20              |Ion   | 192300
10              |Vasile|  45300
                |Fane  |
(4 rows)


Record with nume='Fane' has NULL value in 'cod' field.

I think that ordering ascending the records on 'cod' field, records
having NULL values in 'cod' field should appear first. Should them ?


Other database information :

Database    = template1
 +------------------+----------------------------------+----------+
 |  Owner           |             Relation             |   Type   |
 +------------------+----------------------------------+----------+
 | postgres         | pers                             | table    |
 | postgres         | pers_cod                         | index    |
 +------------------+----------------------------------+----------+
template1=> \d pers

Table    = pers
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-------+
| cod                              | (bp)char
|    16 |
| nume                             | varchar
|     0 |
| salariu                          | float8
|     8 |
+----------------------------------+----------------------------------+-------+
template1=>

'pers_cod' index is unique btree indexed on field cod.

Constantin Teodorescu
FLEX Consulting
Braila, ROMANIA
E-mail: teo@flex.ro

------------------------------

Constantin Teodorescu wrote:
> PostgreSQL 6.1 , Linux RedHat 4.2 Kernel 2.0.30
> Selecting all records and ordering ascending and descending on a field :
> template1=> select * from pers order by cod;
> template1=> select * from pers order by cod desc;
> ("cod" fields which are null _always_ show up at the end)

This is an interesting feature. The reason for this behavior is that
"null" fields should (I think) always return FALSE in comparisons, and
the order-by is done using "<" or ">" operators. Remember, "null" is
nothing, *not anything*, and hence is not comparable to anything which
is not null. Even two null values should compare FALSE, I think.

I believe that this is correct behavior. Is anyone aware of anything
goofy in some SQL standards which would have this behave differently??

            - Tom

------------------------------

On Sat, 14 Jun 1997, Thomas G. Lockhart wrote:

> Constantin Teodorescu wrote:
> > PostgreSQL 6.1 , Linux RedHat 4.2 Kernel 2.0.30
> > Selecting all records and ordering ascending and descending on a field :
> > template1=> select * from pers order by cod;
> > template1=> select * from pers order by cod desc;
> > ("cod" fields which are null _always_ show up at the end)
>
> This is an interesting feature. The reason for this behavior is that
> "null" fields should (I think) always return FALSE in comparisons, and
> the order-by is done using "<" or ">" operators. Remember, "null" is
> nothing, *not anything*, and hence is not comparable to anything which
> is not null. Even two null values should compare FALSE, I think.
>

absolutely correct: true != NULL; false != NULL; NULL != NULL;
I'm not sure whether its part of the standard but most databases implement
the 'is' keyword as in: ...where xyx is NULL or xyz is NOT NULL;

as for sort order, hmm. i would intuitively consider nulls to sort after
everything else, but i'm not sure what the standard says

sean

________________________________________________________________________
Sean Lyndersay                        Time is natures way of making sure
lynders@hcs.harvard.edu                 things don't happen all at once.
________________________________________________________________________
[finger for all other info]          http://www.hcs.harvard.edu/~lynders

------------------------------

> as for sort order, hmm. i would intuitively consider nulls to sort after
> everything else, but i'm not sure what the standard says

On commercial databases, I usually see them at the beginning.

- --
Bruce Momjian
maillist@candle.pha.pa.us

------------------------------