"Unvisible" duplicates in SELECT [...] HAVING count(attrib) > 1

============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================

Your name : Krzysztof Gajdemski
Your email address : songo(at)vi(dot)pl

System Configuration
---------------------
Architecture (example: Intel Pentium) : Intel Pentium

Operating System (example: Linux 2.0.26 ELF) : 2.2.19 SMP

PostgreSQL version (example: PostgreSQL-7.1): PostgreSQL-7.1rc4

Compiler used (example: gcc 2.8.0) : gcc 2.95.3

Please enter a FULL description of your problem:
------------------------------------------------

SELECT attrib FROM table GROUP BY attrib HAVING count(attrib) > 1;

query doesn't produce reliable results for some specific circumstances
(I suppose that number of records and/or strange ASCII chars in varchar
fields are one of required conditions to reproduce this behaviour, but I
can't define additional for now). Table which I use in following example
is available at: http://i.use.vi.pl/postgres/dup_example.sql.gz (size
400KB, this is why I don't place it here).

Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
songo=> SELECT version();
version
----------------------------------------------------------------
PostgreSQL 7.1rc4 on i686-pc-linux-gnu, compiled by GCC 2.95.3
(1 row)

songo=> \i dup_example.sql
psql:dup_example.sql:14: NOTICE: CREATE TABLE/PRIMARY KEY will create implicit
index 'user_id_pkey' for table 'user_id'
CREATE
CHANGE
CHANGE
songo=> SELECT nick FROM user_id GROUP BY nick HAVING count(nick) > 1;
nick
------------
sexylady\@
(1 row)

songo=> DELETE FROM user_id WHERE nick IN (
songo(> SELECT nick FROM user_id GROUP BY nick HAVING count(nick) > 1);
DELETE 2
songo=> SELECT nick FROM user_id GROUP BY nick HAVING count(nick) > 1;
nick
------
(0 rows)

songo=> CREATE UNIQUE INDEX nick_idx ON user_id(nick);
ERROR: Cannot create unique index. Table contains non-unique values
songo=> SELECT nick FROM user_id WHERE nick LIKE 'dr dre%';
nick
--------------------
dr dre\$\$\$\$\$\$
dr dre\$\$\$\$\$\$
(2 rows)

songo=> SELECT nick FROM user_id GROUP BY nick HAVING count(nick) > 1;
nick
------
(0 rows)

If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------

k.
--
- - - - - - Krzysztof Gajdemski | songo @ vi.pl | KG4751-RIPE
Registered Linux User # 133457 | BLUG Registered Member # 0005
PGP public keys at: keyserver.linux.bydg.org * KeyID: D336705B
,,Szanuję was wszystkich, którzy pozostajecie w cieniu'' SNERG