Обсуждение: possible bug with group by?
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=>
On Wed, May 24, 2000 at 06:30:49PM -0400, Joseph Shraibman wrote:
> Is this a bug or am I just misunderstanding something?
>
Not a bug, pgsql is just less willing to cast things willy-nilly
in 7.0 than it was in 6.x. In this case, the system doesn't know what
'not set' and 'set' are supposed to be, so if can't decide what operator
to use to compare them for grouping. Try something like:
select a, b, case when c is null then 'not set'::text else 'set'::text end as z from tablea group by a, b, z;
Or even SQL92 compliant:
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;
>
> 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=>
>
Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
Joseph Shraibman wrote: > > > 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=> Hi, should your group by be a, b, c not a, b, z? Regards Julie
> 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;
Joseph Shraibman <jks@selectacast.net> writes:
> 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
It's not GROUP BY's fault, it's just the oft-repeated issue about
quoted string literals not having any definite type in Postgres.
The parser postpones assigning a type until it sees the literal used
in a context where a type can be determined --- and in a case like
this, it never can. You need to force the issue with a cast, eg
select a, b, case when c is null then 'not set'::text else 'set'::text end as z
from tablea group by a, b, z;
regards, tom lane
Julie Hunt wrote: > > Joseph Shraibman wrote: > > > > > > > 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=> > > Hi, > > should your group by be a, b, c not a, b, z? > That would work for this example. But shouldn't it work for z also? I discovered that if I replace z with text I don't get that error, but then I can't name my columns.
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.
Joseph Shraibman <jks@selectacast.net> writes:
> But why would group by need to sort it? To insert it into a tree to
> make lookups of distinct values faster?
No, to bring identical values together. GROUP BY and DISTINCT are both
implemented as basically a "sort | uniq" pipeline.
regards, tom lane