Received: from p1.selectacast.net (IDENT:root@p1.selectacast.net [206.41.1.251]) by hub.org (8.9.3/8.9.3) with ESMTP id TAA22170 for ; Wed, 24 May 2000 19:21:37 -0400 (EDT) (envelope-from jks@selectacast.net) Received: from selectacast.net (IDENT:jks@d1 [209.219.91.66]) by p1.selectacast.net (8.9.3/8.9.3) with ESMTP id TAA10155; Wed, 24 May 2000 19:21:35 -0400 Message-ID: <392C63FF.F0186004@selectacast.net> Date: Wed, 24 May 2000 19:21:35 -0400 From: Joseph Shraibman Organization: Xtenit X-Mailer: Mozilla 4.72 [en] (X11; U; Linux 2.2.15 i686) X-Accept-Language: en MIME-Version: 1.0 To: Stephan Szabo CC: pgsql-sql@postgresql.org Subject: Re: possible bug with group by? References: <392C5819.2FD488A8@selectacast.net> <029101bfc5d6$09f3a960$0c64010a@kick.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200005/133 Stephan Szabo wrote: > > > Is this a bug or am I just misunderstanding something? > > > > playpen=> create table tablea ( a int,b int , c int ); > > CREATE > > playpen=> insert into tablea(a, b) values (1 ,2); > > INSERT 28299 1 > > playpen=> insert into tablea(a, b, c) values (2 ,3, 4); > > INSERT 28300 1 > > playpen=> select a, b, case when c is null then 'not set' else 'set' end > > as z from tablea; > > a|b|z > > -+-+------- > > 1|2|not set > > 2|3|set > > (2 rows) > > > > > > playpen=> select a, b, case when c is null then 'not set' else 'set' end > > as z from tablea group by a, b, z; > > ERROR: Unable to identify an operator '<' for types 'unknown' and > > 'unknown' > > You will have to retype this query using an explicit cast > > playpen=> > > I'm not 100% sure, but my guess would be that it's not certain what > type 'not set' and 'set' are going to be (hence type 'unknown') and when > it tries to group it, it's unable to determine how to tell what's greater > than > something else. But why would group by need to sort it? To insert it into a tree to make lookups of distinct values faster? > > As a workaround, you should be able to do something like the following: > select a,b, case when c is null then cast('not set' as text) else cast('set' > as text) > end as z from tablea group by a, b, z; That does work. Thanks.