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

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


Your name        : Krzysztof Gajdemski
Your email address    : songo@vi.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