RE: Found an example prooving bug - Mailing list pgsql-bugs

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@students.mimuw.edu.pl]
> Sent: 30 April 2001 16:30
> To: Tom Lane
> Cc: eru@mimuw.edu.pl; pgsql-bugs@postgresql.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@i.pl || eru@mimuw.edu.pl >
> : "The worst thing in life is that there's no background music"
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>