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

Krzysztof Gajdemski <songo(at)vi(dot)pl> writes:
> PostgreSQL version (example: PostgreSQL-7.1): PostgreSQL-7.1rc4

> query doesn't produce reliable results for some specific circumstances

I tried your example in current sources and get reasonable-looking
behavior:

regression=# SELECT nick FROM user_id GROUP BY nick HAVING count(nick) > 1;
nick
--------------------
dr dre\$\$\$\$\$\$
sexylady\@
(2 rows)

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

regression=# CREATE UNIQUE INDEX nick_idx ON user_id(nick);
CREATE
regression=# SELECT nick FROM user_id WHERE nick LIKE 'dr dre%';
nick
------
(0 rows)

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

regression=#

It could be that there's a locale dependency here (I did this in a
non-locale-enabled compilation), but I think it's more likely that
you've run into one of the complex-query bugs we corrected since rc4.
Please update to 7.1.1 (or better 7.1.2, which should be out very soon)
and see if you still see a problem.

regards, tom lane