Skip site navigation (1) Skip section navigation (2)

Re: possible bug with group by?

From: "Stephan Szabo" <sszabo(at)kick(dot)com>
To: "Joseph Shraibman" <jks(at)selectacast(dot)net>
Cc: <pgsql-sql(at)postgresql(dot)org>
Subject: Re: possible bug with group by?
Date: 2000-05-24 23:16:06
Message-ID: 029101bfc5d6$09f3a960$0c64010a@kick.com (view raw or flat)
Thread:
Lists: pgsql-sql
> 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;




In response to

Responses

pgsql-sql by date

Next:From: Joseph ShraibmanDate: 2000-05-24 23:18:48
Subject: Re: possible bug with group by?
Previous:From: Tom LaneDate: 2000-05-24 23:07:47
Subject: Re: possible bug with group by?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group