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

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.

"Joerg Kiegeland" <kiegeland(at)ikv(dot)de> writes:
> The GiST index seems not to be able to find empty integer arrays.

Yeah, this is a known issue. We are looking into what can be done about
it for 8.4.

regards, tom lane

>
>
>> The GiST index seems not to be able to find empty integer arrays.
>>
>
> Yeah, this is a known issue. We are looking into what can be done about
> it for 8.4.
>
Can you give me the bug ID if a similar bug is already reported?
I searched for such a bug before, but found nothing.
Then, I can keep track of the original bug, since my bug as duplicate
may be not cared for, I fear.

Thanks, Jörg

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. +

"Joerg Kiegeland" <kiegeland(at)ikv(dot)de> writes:
> 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.

Just for the archives' sake: this problem is now fixed, in both
GiST and GIN indexes, for 9.1.

regards, tom lane