RE: Found an example prooving bug

From my point of view, NULL is neither bigger, nor smaller, you can't
compare it with a number.

So it just comes at the end if you sort at all.

(Perhaps you need to take a think about what NULL means in your data. Should
NULL sort as if it's 0?, +infinity?, -infinity? if so why?)

regards,

Piers Scannell
System Architect, GlobeCast France Telecom
Tel: +44 1707 667 228 Fax: +44 1707 667 206

> -----Original Message-----
> From: Marcin Zukowski [mailto:mz174771(at)students(dot)mimuw(dot)edu(dot)pl]
> Sent: 30 April 2001 16:30
> To: Tom Lane
> Cc: eru(at)mimuw(dot)edu(dot)pl; pgsql-bugs(at)postgresql(dot)org
> Subject: [BUGS] Found an example prooving bug
>
>
> I found an example when postgres while executing the same
> query uses null
> values as sometimes bigger than everything and sometimes smaller.
> And I think it's BAD.
> Check this out:
>
> -------------------------------------------------------------------
>
> DROP TABLE NUTKI ;
> CREATE TABLE NUTKI (
> ID INT4 PRIMARY KEY,
> VAL INT4,
> REF INT4
> );
> CREATE INDEX NUTKI_VAL ON NUTKI(VAL);
> CREATE INDEX NUTKI_KEY ON NUTKI(KEY);
> INSERT INTO NUTKI VALUES(1,1,null);
> INSERT INTO NUTKI VALUES(2,2,1);
> INSERT INTO NUTKI VALUES(3,3,1);
> INSERT INTO NUTKI VALUES(4,null,1);
> INSERT INTO NUTKI VALUES(5,5,5);
> INSERT INTO NUTKI VALUES(7,null,7);
> INSERT INTO NUTKI VALUES(8,8,7);
> SET ENABLE_INDEXSCAN TO ON ;
> SET ENABLE_SEQSCAN TO OFF ;
> SET ENABLE_SORT TO OFF;
> SELECT * FROM NUTKI N1, NUTKI N2 WHERE N1.ID = N2.REF
> ORDER BY N1.VAL DESC, N2.VAL;
>
> --------------------------------------------------------------
> -----------
> ( well, i think all the index creation and switches are not
> necessary )
>
> The result is:
>
> id | val | ref | id | val | ref
> ----+-----+-----+----+-----+-----
> 5 | 5 | 5 | 5 | 5 | 5
> 1 | 1 | | 2 | 2 | 1
> 1 | 1 | | 3 | 3 | 1
> 1 | 1 | | 4 | | 1
> 7 | | 7 | 8 | 8 | 7
> 7 | | 7 | 7 | | 7
>
> Tested on:
> PostgreSQL 7.0.3 on i586-pc-linux-gnu, compiled by gcc egcs-2.91.66
>
> So, as you can see, values in 2nd column are sorted descending, with
> null smaller than everything. In the 5th column, val's are sorted
> ascending, with null BIGGER than everything.
> I really think it's a bug.
> Please let me know, what do you think about it, and please
> make it go to
> the pgsql-bugs, because my mails aren't accepted there. I
> didn't get any
> reply for my previous letter, and I don't know what to think.
>
> best regards,
>
> Marcin
>
> --
> : Marcin Zukowski < eru(at)i(dot)pl || eru(at)mimuw(dot)edu(dot)pl >
> : "The worst thing in life is that there's no background music"
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>