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"
