sorting/grouping/(non-)unique indexes bug - Mailing list pgsql-general

Greetings!

Before I present the following, I must say that 'simple' index
corruption is highly improbable; the server did not suffer hard
reboots.

rdw=# \d reg_user
                                      Table "reg_user"
      Column      |          Type          |                   Modifiers
------------------+------------------------+------------------------------------------------
 user_id          | integer                | not null default nextval('reg_user_seq'::text)
 user_email       | character varying(50)  | not null
 user_passwd      | character varying(32)  | not null
 user_active      | boolean                | not null default 't'
 user_allow_pauth | boolean                | default 'f'
 user_full_name   | character varying(100) |
 user_pseudonym   | character varying(100) |
 user_who         | character(1)           |
Primary key: reg_user_pkey
Unique keys: reg_user_email_key

rdw=# \d reg_user_email_key
     Index "reg_user_email_key"
   Column   |         Type
------------+-----------------------
 user_email | character varying(50)
unique btree

rdw=# create table broken as select user_email from reg_user;
SELECT

rdw=# -- one may think that emails are unique...
rdw=# select count(user_email) from broken;
 count
--------
 212400
(1 row)

rdw=# select count(distinct user_email) from broken;
 count
--------
 212397
(1 row)

rdw=# -- look closely at the numbers
rdw=# select user_email, count(user_email) from broken group by user_email order by 2 desc;
                     user_email                     | count
----------------------------------------------------+-------
 [duplicated email]                                 |     2
 [some email]                                       |     1
 [some email]                                       |     1
 [some email]                                       |     1

rdw=# delete from broken where user_email = '[duplicated email]';
DELETE 2

rdw=# select user_email, count(user_email) from broken group by user_email order by 2 desc;
                     user_email                     | count
----------------------------------------------------+-------
 [another email]                                    |     2
 [some email]                                       |     1
 [some email]                                       |     1

rdw=# delete from broken where user_email = '[another email]';
DELETE 2

rdw=# select user_email, count(user_email) from broken group by user_email order by 2 desc;
                     user_email                     | count
----------------------------------------------------+-------
 [some email]                                       |     1
 [some email]                                       |     1
 [some email]                                       |     1

rdw=# --finally, no more duplicates!
rdw=# create unique index broken_email_key on broken (user_email);
ERROR:  Cannot create unique index. Table contains non-unique values

rdw=# select version();
                           version
-------------------------------------------------------------
 PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

$psql -l
        List of databases
   Name    |  Owner   | Encoding
-----------+----------+-----------
...
 rdw       | alex     | WIN
...

The server is run under ru_RU.CP1251 locale, initdb was done under the
same locale.

As you probably guessed, reg_user table contains actual email
addresses of our site's registered users. I can provide them for
testing (without passwords and stuff, of course) if this is going to
help.

--
Yours, Alexey V. Borzov, Webmaster of RDW.ru