User-Agent: Microsoft-Entourage/11.4.0.080122
Date: Sat, 09 May 2009 09:22:24 +0200
Subject: Re: Distinct oddity 
From: Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: <pgsql-sql@postgresql.org>
Message-ID: <C62AFBD0.35F11%maximilian.tyrtania@onlinehome.de>
Thread-Topic: [SQL] Distinct oddity 
Thread-Index: AcnQduX8JMqYODxqEd6smgAjMt4/tA==
In-Reply-To: <13494.1241812363@sss.pgh.pa.us>
Mime-version: 1.0
Content-type: text/plain;
	charset="ISO-8859-1"
Content-transfer-encoding: quoted-printable

am 08.05.2009 16:55 Uhr schrieb Rob Sargent unter robsargent@rocketmail.com=
:

>Is firmen a table or a view?

It's a table.

am 08.05.2009 21:52 Uhr schrieb Tom Lane unter tgl@sss.pgh.pa.us:

>> It doesn't seem to be related to null values (which wouldn't explain it
>> anyway) nor to this particular field...
>=20
> Can you reproduce it in other contexts than specifically count(distinct)?
> In particular I'd try
>=20
> select count(*) from
>  (select distinct f.bezeichnung from firmen f) ss;

FAKDB=3D# select count(*) from
FAKDB-#   (select distinct f.bezeichnung from firmen f) ss;
 count=20
-------
 73437
(1 row)

>=20
> select count(*) from
>  (select distinct f.bezeichnung||'e' from firmen f) ss;

FAKDB=3D# select count(*) from
(select distinct f.bezeichnung||'e' from firmen f) ss;
 count=20
-------
 72535
(1 row)

> If those give the same numbers as you're showing here, then the
> next step would be to dump out the actual results of the SELECT DISTINCT
> queries and compare them --- looking at the actual data values should
> give some insight as to what's happening.

FAKDB=3D# select distinct f.bezeichnung from firmen f order by 1 limit 5
FAKDB-# ;
               bezeichnung
-----------------------------------------
=20
 =8Asterreichisches Verkehrsb=8Dro AG
 \x01Assistenz
 \x10Frohstoff Design & Textilveredelung
 "1. Mittelschule ""Am Kupferberg"""
(5 rows)


FAKDB=3D# select distinct f.bezeichnung||'e' from firmen f order by 1 limit 5
;
                 ?column?
------------------------------------------
 =D6sterreich/Welt (Ltg.)e
 =8Asterreichisches Verkehrsb=8Dro AGe
 \x01Assistenze
 \x10Frohstoff Design & Textilveredelunge
 "1. Mittelschule ""Am Kupferberg"""e
(5 rows)


Aha, the "=D6sterreich/Welt (Ltg.)"-entry is missing in the 1st query. So tha=
t
does smell like a locale problem.

> BTW, what is the datatype of f.bezeichnung,

It's character varying(255). Just for the record:

FAKDB=3D# explain analyze select distinct f.bezeichnung||'e' from firmen f
order by 1 limit 5;
                                                             QUERY PLAN
---------------------------------------------------------------------------=
-
---------------------------------------------------------
 Limit  (cost=3D16173.07..16174.08 rows=3D5 width=3D18) (actual
time=3D1667.841..1667.855 rows=3D5 loops=3D1)
   ->  Unique  (cost=3D16173.07..16933.66 rows=3D3765 width=3D18) (actual
time=3D1667.839..1667.851 rows=3D5 loops=3D1)
         ->  Sort  (cost=3D16173.07..16553.36 rows=3D152117 width=3D18) (actual
time=3D1667.837..1667.844 rows=3D5 loops=3D1)
               Sort Key: (((bezeichnung)::text || 'e'::text))
               Sort Method:  external merge  Disk: 4640kB
               ->  Seq Scan on firmen f  (cost=3D0.00..13646.46 rows=3D152117
width=3D18) (actual time=3D0.069..353.777 rows=3D152118 loops=3D1)
 Total runtime: 1669.998 ms
(7 rows)

> and what locale are you
> running in?

 lc_collate                      | de_DE
| Shows the collation order locale.
 lc_ctype                        | de_DE
| Shows the character classification and case conversion locale.

The encoding is UTF-8.

Best,

Maximilian Tyrtania