Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id A656E632EEA for ; Fri, 8 May 2009 06:28:55 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 29535-04 for ; Fri, 8 May 2009 06:28:46 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.126.171]) by mail.postgresql.org (Postfix) with ESMTP id 78E90632CA6 for ; Fri, 8 May 2009 06:28:43 -0300 (ADT) Received: from [10.0.0.189] ([217.9.61.84]) by mrelayeu.kundenserver.de (node=mrelayeu5) with ESMTP (Nemesis) id 0ML25U-1M2MNk3RQt-0001wz; Fri, 08 May 2009 11:28:41 +0200 User-Agent: Microsoft-Entourage/11.4.0.080122 Date: Fri, 08 May 2009 11:28:40 +0200 Subject: Re: Distinct oddity From: Maximilian Tyrtania To: Scott Marlowe CC: Message-ID: Thread-Topic: [SQL] Distinct oddity Thread-Index: AcnPv184nYA8yDuyEd6V4AAjMt4/tA== In-Reply-To: Mime-version: 1.0 Content-type: text/plain; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable X-Provags-ID: V01U2FsdGVkX1/7Q2VUhlPlMUai533gzsY0BXB2S5monsxrtAx EvIpo7sjryVlrcDgNbgnNU1k+BYnxDwMiW5wdKmNTjSsbs/o6k 8oefgixsNiw2SDYewSbqbd32G5m8pFPzLY5kSpH9MW4AOweaIB YAw== X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200905/21 X-Sequence-Number: 32513 am 07.05.2009 20:54 Uhr schrieb Scott Marlowe unter scott.marlowe@gmail.com= : > On Thu, May 7, 2009 at 2:21 AM, Maximilian Tyrtania > wrote: >> Hi there, >>=20 >> does this look right? >>=20 >> FAKDB=3D# select count(distinct(f.land)) from firmen f where >> f.typlist=3D'Redaktion'; >> =A0count >> ------- >> =A01975 >> (1 row) >>=20 >> FAKDB=3D# select count(distinct(f.land||'1')) from firmen f where >> f.typlist=3D'Redaktion'; >> =A0count >> ------- >> =A04944 >> (1 row) >=20 > Yeah, that does seem odd. Could it be something like nulls in your > data set? just guessing really. If you could make a small test case > that shows it happening and allows others to reproduce it you're > likely to get more bites. It doesn't seem to be related to null values (which wouldn't explain it anyway) nor to this particular field... FAKDB=3D# select count(*) from firmen where bezeichnung is null; count=20 ------- 0 (1 row) FAKDB=3D# select count(distinct(f.bezeichnung)) from firmen f; count=20 ------- 72698 (1 row) FAKDB=3D# select count(distinct(f.bezeichnung||'e')) from firmen f; count=20 ------- 72892 (1 row) My attempts at reproducing this with a freshly created table failed, of course. FAKDB=3D# create table concattest(mytext text); CREATE TABLE FAKDB=3D# insert into concattest (mytext) select(generate_series(1,10000)::text); INSERT 0 10000 FAKDB=3D# insert into concattest (mytext) select(generate_series(1,10000)::text); INSERT 0 10000 FAKDB=3D# select count(distinct(mytext)) from concattest; count=20 ------- 10000 (1 row) FAKDB=3D# select count(distinct(mytext||'2')) from concattest; count=20 ------- 10000 (1 row) best, Maximilian Tyrtania