Re: BUG #14210: filter by "=" constraint doesn't work when hash index is present on a column

Yes. If i delete the index and recreate it, the bug is replicated. I have
uploaded a pg_dump .sql file to
https://www.dropbox.com/s/4dxuo2uthj721od/hash_issue_db.sql?dl=0 that you
can use to recreate the issue.

After creating the database when I run:

>>> select * from hash_issue_table where hash_issue_column = '1';

I get no results.

When I run:
>>> drop index hash_issue_index;
>>> select * from hash_issue_table where hash_issue_column = '1';

I get 531 rows of results.

When I run:

>>> create index hash_issue_index on hash_issue_table using
hash(hash_issue_column);
>>> select * from hash_issue_table where hash_issue_column = '1';

I get 0 results again. I have repeated this successfully multiple times and
on a coworker's machine as well.

Interestingly, I modified the pg_dump file a bit. At the end, it says:

CREATE INDEX hash_issue_index ON hash_issue_table USING hash
> (hash_issue_column);
>
> DROP INDEX hash_issue_index;
>
> CREATE INDEX hash_issue_index ON hash_issue_table USING hash
> (hash_issue_column);
>

This is because the issue was not replicating (for some reason) when it
built the index the first time.

On Thu, Jun 23, 2016 at 1:04 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> danielnewman(at)umich(dot)edu writes:
> > When I run the query `select * from hash_issue_table where
> hash_issue_column
> > like '2184';` I get 701 results. When I run the query
> > When I run the query `select * from hash_issue_table where
> hash_issue_column
> > = '2184';`, I get 0 results. However, if I drop the hash index and rerun
> the
> > second query, I get the same results.
>
> Sounds like a corrupted hash index to me; which is not terribly surprising
> given hash indexes' lack of WAL support. Can you reproduce this after
> rebuilding the hash index?
>
> regards, tom lane
>