pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
possible bug with group by?
8+ messages / 5 participants
[nested] [flat]

* possible bug with group by?
@ 2000-05-24 22:30 Joseph Shraibman <jks@selectacast.net>
  2000-05-24 23:03 ` Re: possible bug with group by? Ross J. Reedstrom <reedstrm@rice.edu>
  2000-05-24 23:05 ` Re: possible bug with group by? Julie Hunt <jhunt@chac.qld.edu.au>
  2000-05-24 23:07 ` Re: possible bug with group by? Tom Lane <tgl@sss.pgh.pa.us>
  2000-05-24 23:16 ` Re: possible bug with group by? Stephan Szabo <sszabo@kick.com>
  0 siblings, 4 replies; 8+ messages in thread

From: Joseph Shraibman @ 2000-05-24 22:30 UTC (permalink / raw)
  To: 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=>




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: possible bug with group by?
  2000-05-24 22:30 possible bug with group by? Joseph Shraibman <jks@selectacast.net>
@ 2000-05-24 23:03 ` Ross J. Reedstrom <reedstrm@rice.edu>
  3 siblings, 0 replies; 8+ messages in thread

From: Ross J. Reedstrom @ 2000-05-24 23:03 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: possible bug with group by?
  2000-05-24 22:30 possible bug with group by? Joseph Shraibman <jks@selectacast.net>
@ 2000-05-24 23:05 ` Julie Hunt <jhunt@chac.qld.edu.au>
  2000-05-24 23:18   ` Re: possible bug with group by? Joseph Shraibman <jks@selectacast.net>
  3 siblings, 1 reply; 8+ messages in thread

From: Julie Hunt @ 2000-05-24 23:05 UTC (permalink / raw)
  To: Joseph Shraibman <jks@selectacast.net>; +Cc: pgsql-sql



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




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: possible bug with group by?
  2000-05-24 22:30 possible bug with group by? Joseph Shraibman <jks@selectacast.net>
  2000-05-24 23:05 ` Re: possible bug with group by? Julie Hunt <jhunt@chac.qld.edu.au>
@ 2000-05-24 23:18   ` Joseph Shraibman <jks@selectacast.net>
  0 siblings, 0 replies; 8+ messages in thread

From: Joseph Shraibman @ 2000-05-24 23:18 UTC (permalink / raw)
  To: jhunt@chac.qld.edu.au; +Cc: pgsql-sql

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.



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: possible bug with group by?
  2000-05-24 22:30 possible bug with group by? Joseph Shraibman <jks@selectacast.net>
@ 2000-05-24 23:07 ` Tom Lane <tgl@sss.pgh.pa.us>
  3 siblings, 0 replies; 8+ messages in thread

From: Tom Lane @ 2000-05-24 23:07 UTC (permalink / raw)
  To: Joseph Shraibman <jks@selectacast.net>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: possible bug with group by?
  2000-05-24 22:30 possible bug with group by? Joseph Shraibman <jks@selectacast.net>
@ 2000-05-24 23:16 ` Stephan Szabo <sszabo@kick.com>
  2000-05-24 23:21   ` Re: possible bug with group by? Joseph Shraibman <jks@selectacast.net>
  3 siblings, 1 reply; 8+ messages in thread

From: Stephan Szabo @ 2000-05-24 23:16 UTC (permalink / raw)
  To: Joseph Shraibman <jks@selectacast.net>; +Cc: 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;






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: possible bug with group by?
  2000-05-24 22:30 possible bug with group by? Joseph Shraibman <jks@selectacast.net>
  2000-05-24 23:16 ` Re: possible bug with group by? Stephan Szabo <sszabo@kick.com>
@ 2000-05-24 23:21   ` Joseph Shraibman <jks@selectacast.net>
  2000-05-25 02:59     ` Re: possible bug with group by? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 8+ messages in thread

From: Joseph Shraibman @ 2000-05-24 23:21 UTC (permalink / raw)
  To: Stephan Szabo <sszabo@kick.com>; +Cc: pgsql-sql

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.



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: possible bug with group by?
  2000-05-24 22:30 possible bug with group by? Joseph Shraibman <jks@selectacast.net>
  2000-05-24 23:16 ` Re: possible bug with group by? Stephan Szabo <sszabo@kick.com>
  2000-05-24 23:21   ` Re: possible bug with group by? Joseph Shraibman <jks@selectacast.net>
@ 2000-05-25 02:59     ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 8+ messages in thread

From: Tom Lane @ 2000-05-25 02:59 UTC (permalink / raw)
  To: Joseph Shraibman <jks@selectacast.net>; +Cc: Stephan Szabo <sszabo@kick.com>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2000-05-25 02:59 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-05-24 22:30 possible bug with group by? Joseph Shraibman <jks@selectacast.net>
2000-05-24 23:03 ` Ross J. Reedstrom <reedstrm@rice.edu>
2000-05-24 23:05 ` Julie Hunt <jhunt@chac.qld.edu.au>
2000-05-24 23:18   ` Joseph Shraibman <jks@selectacast.net>
2000-05-24 23:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-05-24 23:16 ` Stephan Szabo <sszabo@kick.com>
2000-05-24 23:21   ` Joseph Shraibman <jks@selectacast.net>
2000-05-25 02:59     ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox