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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-05-18 15:12:27 | Re: "Unvisible" duplicates in SELECT [...] HAVING count(attrib) > 1 |
Previous Message | pgsql-bugs | 2001-05-17 19:24:39 | Problems with avg on interval data type |