Marcin Zukowski (eru@mimuw.edu.pl) reports a bug with a severity of 2
The lower the number the more severe it is.
Short Description
Optimalisation options change query results
Long Description
I had a problem with retrieving data when some of the multi-column index fields were NULL - it was always at the end.
SinceI wanted null fields be treated either 'bigger than everything' or 'smaller than everything' I tried to use some
optimalisationoptions to force sorting method.
And it worked.
And I think it should not :)
I have tried it on:
PostgreSQL 7.0.3 on i586-pc-linux-gnu, compiled by gcc egcs-2.91.66
If you run example code you will see, that after changing some query optimalisation switches the result CHANGED.
My conclusion: when sorting data null values are always set at the end, so during retrieving in DESC order using index
theyare first.
When making Sort during query execution, they are always last.
Sample Code
DROP TABLE NUTKI ;
CREATE TABLE NUTKI (
ID INT4 PRIMARY KEY,
VAL INT4
);
CREATE INDEX NUTKI_VAL ON NUTKI(VAL);
INSERT INTO NUTKI VALUES(1,1);
INSERT INTO NUTKI VALUES(2,null);
SET ENABLE_INDEXSCAN TO OFF ;
SET ENABLE_SEQSCAN TO ON ;
SET ENABLE_SORT TO ON ;
EXPLAIN SELECT * FROM NUTKI ORDER BY VAL DESC ;
SELECT * FROM NUTKI ORDER BY VAL DESC ;
SET ENABLE_INDEXSCAN TO ON ;
SET ENABLE_SEQSCAN TO OFF ;
SET ENABLE_SORT TO OFF ;
EXPLAIN SELECT * FROM NUTKI ORDER BY VAL DESC ;
SELECT * FROM NUTKI ORDER BY VAL DESC ;
No file was uploaded with this report