pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedpossible 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>
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 23:03 Ross J. Reedstrom <reedstrm@rice.edu>
parent: Joseph Shraibman <jks@selectacast.net>
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 23:05 Julie Hunt <jhunt@chac.qld.edu.au>
parent: 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 23:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: Joseph Shraibman <jks@selectacast.net>
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 23:16 Stephan Szabo <sszabo@kick.com>
parent: 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 23:18 Joseph Shraibman <jks@selectacast.net>
parent: Julie Hunt <jhunt@chac.qld.edu.au>
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 23:21 Joseph Shraibman <jks@selectacast.net>
parent: Stephan Szabo <sszabo@kick.com>
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-25 02:59 Tom Lane <tgl@sss.pgh.pa.us>
parent: Joseph Shraibman <jks@selectacast.net>
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