The following bug has been logged online:
Bug reference: 4806
Logged by: Joerg Kiegeland
Email address: kiegel...@ikv.de
PostgreSQL version: PostgreSQL8.3.7
Operating system: Windows XP
Description: Bug with GiST index and empty integer array?
Details:
The GiST index seems not to be able to find empty integer arrays.
The bug can be easily reproduced on a simple test database:
To create the table and the data execute:
CREATE TABLE test_intarray_table (
id text NOT NULL PRIMARY KEY,
intarray_column integer[]
);
INSERT INTO test_intarray_table (id, intarray_column) VALUES ('x', '{}');
CREATE INDEX intarray_index ON test_intarray_table USING gist
(intarray_column);
To query the data execute:
SET ENABLE_SEQSCAN TO OFF; --disable sequential scan, which is performed for
small tables
SELECT * FROM test_intarray_table WHERE "intarray_column" = '{}';
The result set of this query does not include the row with id x, though the
condition should match! When deleting the index intarray_index, x is found!
So we proposed our customer to simply delete the index. However it goes
slower then. I would expect the GiST index to either report an error that
empty array queries are not supported (like GIN index does) or otherwise to
return the correct result, since the result of a query should be independent
of an index usage.
As we reduced this bug to this little example from a very large database and
a much larger query (took hours for this "simplification"), we disabled the
sequential scan, however in our large database we could reproduce this error
without disabling the sequential scan.
The bug also appears with PostgreSQL8.4 Beta and with PostgreSQL8.3.6.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
