Обсуждение: select distinct error ?

hello all,

i am just experiencing something weird:

select distinct foo from bar where foobar like 'foobar%';

gives:

 foo
--------
 090700
 090701
 090702
 090700
(4 rows)

foo is character(6)

How can this happen??


Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com          Tel. +49 4141 991230
mail: steffen@topconcepts.com       Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

> i am just experiencing something weird:
>
> select distinct foo from bar where foobar like 'foobar%';
>
> gives:
>
>  foo
> --------
>  090700
>  090701
>  090702
>  090700
> (4 rows)
>

try:
SELECT distinct foo, length(foo) from bar where foobar like 'foobar%';

maybe one has a blank attached?

"Mario Weilguni" <mweilguni@sime.com> writes:
> try:
> SELECT distinct foo, length(foo) from bar where foobar like 'foobar%';
> maybe one has a blank attached?

He said the column was char(6), so they should all be padded to length
6.  I was wondering about nonprinting characters (carriage returns etc)
in the data, myself.

            regards, tom lane

On Thu, 25 Jul 2002, Henrik Steffen wrote:

> select distinct foo from bar where foobar like 'foobar%';
>
> gives:
>
>  foo
> --------
>  090700
>  090701
>  090702
>  090700
> (4 rows)
>
> foo is character(6)
>
> How can this happen??

Do you have a simple dataset that you can replicate this with
that you can send a dump of (tables, data).  Also, what version
and what locale are you running with?

hello,

I did now:

select foo,count(foobar),length(foo) from bar where foobar like 'foobar%' group by foo;

foo    | count | length
------------------------
090700 |   494 |      6
090701 |     6 |      6
090702 |     4 |      6
090700 |   237 |      6


AAAAAAAAAA!!!!!!

now I found it!!!! User-input error: someone entered O90700 instead of 090700
with an 'O' (big 'o') instead of '0' (zero).... going crazy here.....

excuse me for this stupid question....

Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com          Tel. +49 4141 991230
mail: steffen@topconcepts.com       Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
To: "Henrik Steffen" <steffen@city-map.de>
Cc: "pg" <pgsql-general@postgresql.org>; "Sönke Ruempler" <soenke@ruempler.de>
Sent: Thursday, July 25, 2002 5:55 PM
Subject: Re: [GENERAL] select distinct error ?


> On Thu, 25 Jul 2002, Henrik Steffen wrote:
>
> > select distinct foo from bar where foobar like 'foobar%';
> >
> > gives:
> >
> >  foo
> > --------
> >  090700
> >  090701
> >  090702
> >  090700
> > (4 rows)
> >
> > foo is character(6)
> >
> > How can this happen??
>
> Do you have a simple dataset that you can replicate this with
> that you can send a dump of (tables, data).  Also, what version
> and what locale are you running with?
>
>
>
>

How about:

SELECT distinct foo, length(foo) from bar where foobar like 'foobar%'
and foobar ~'^[0-9]*$';

I have a feeling that you may have an O in stead of a zero.


Tom Lane wrote:
>
> "Mario Weilguni" <mweilguni@sime.com> writes:
> > try:
> > SELECT distinct foo, length(foo) from bar where foobar like 'foobar%';
> > maybe one has a blank attached?
>
> He said the column was char(6), so they should all be padded to length
> 6.  I was wondering about nonprinting characters (carriage returns etc)
> in the data, myself.
>
>                         regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html