Re: BUG #4806: Bug with GiST index and empty integer array?

I can reproduce this but in current CVS by installing /contrib/intarray.

---------------------------------------------------------------------------

Joerg Kiegeland wrote:
>
> The following bug has been logged online:
>
> Bug reference: 4806
> Logged by: Joerg Kiegeland
> Email address: kiegeland(at)ikv(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com
PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do
+ If your life is a hard drive, Christ can be your backup. +