Received: from om.kick.com (IDENT:root@adsl-63-202-92-187.dsl.snfc21.pacbell.net [63.202.92.187]) by hub.org (8.9.3/8.9.3) with ESMTP id TAA13229 for ; Wed, 24 May 2000 19:16:33 -0400 (EDT) (envelope-from sszabo@kick.com) Received: from planetblargh (adsl-63-202-92-189.dsl.snfc21.pacbell.net [63.202.92.189]) by om.kick.com (8.9.3/8.9.3) with SMTP id QAA13228; Wed, 24 May 2000 16:16:30 -0700 Message-ID: <029101bfc5d6$09f3a960$0c64010a@kick.com> From: "Stephan Szabo" To: "Joseph Shraibman" Cc: References: <392C5819.2FD488A8@selectacast.net> Subject: Re: possible bug with group by? Date: Wed, 24 May 2000 16:16:06 -0700 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2919.6700 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700 X-Archive-Number: 200005/130 > 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. 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;