From: | Marcin Zukowski <mz174771(at)students(dot)mimuw(dot)edu(dot)pl> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | eru(at)mimuw(dot)edu(dot)pl, pgsql-bugs(at)postgresql(dot)org |
Subject: | Found an example prooving bug |
Date: | 2001-04-30 15:30:04 |
Message-ID: | Pine.LNX.4.21.0104301656480.1687-100000@zodiac.mimuw.edu.pl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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"
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-04-30 16:03:56 | Re: Re: v7.1.1 branched and released on Tuesday ... |
Previous Message | Thomas Lockhart | 2001-04-30 15:02:08 | Re: v7.1.1 branched and released on Tuesday ... |