Skip site navigation (1) Skip section navigation (2)

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

From: Krzysztof Gajdemski <songo(at)vi(dot)pl>
To: pgsql-bugs(at)postgresql(dot)org
Subject: "Unvisible" duplicates in SELECT [...] HAVING count(attrib) > 1
Date: 2001-05-18 14:20:24
Message-ID: 20010518142024.A27058@s1.psi-net.pl (view raw or flat)
Thread:
Lists: pgsql-bugs
============================================================================
                        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 

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2001-05-18 15:12:27
Subject: Re: "Unvisible" duplicates in SELECT [...] HAVING count(attrib) > 1
Previous:From: pgsql-bugsDate: 2001-05-17 19:24:39
Subject: Problems with avg on interval data type

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group