Here is an email from Gavin showing a problem with subqueries and casts
causing errors when they shouldn't.
---------------------------------------------------------------------------
Gavin Sherry wrote:
> Hi Bruce,
>
> Thanks to a user query (handle: lltd, IRC) I came across a bug in the
> planner. The query was:
>
> ---
> select o1.timestamp::date as date, count(*), (select sum(oi.price) from
> "order" o2, "order_item" oi where oi.order_id = o2.id and
> o2.timestamp::date = o1.timestamp::date and o2.timestamp is not
> null) as total from "order" o1 where o1.timestamp is not null
> group by o1.timestamp::date order by o1.timestamp::date desc;
> ---
>
> The error he was receiving:
>
> ---
> ERROR: Sub-SELECT uses un-GROUPed attribute o1.timestamp from outer query
> ---
>
> After a bit of looking around, I determined that the cast in the order by
> clause was causing the error, not the fact that the query had an ungrouped
> attribute in the outer query.
>
> I have come up with a simpler demonstration which works under 7.1 and CVS.
>
> create table a ( i int);
> insert into a values(1);
> insert into a values(1);
> insert into a values(1);
> insert into a values(1);
> insert into a values(1);
> insert into a values(2);
> insert into a values(2);
> insert into a values(2);
> insert into a values(2);
> insert into a values(3);
> insert into a values(3);
> insert into a values(3);
> insert into a values(3);
>
> --- NO ERROR ---
>
> select o1.i::smallint,count(*),(select sum(o2.i) from a o2 where
> o2.i=o1.i::smallint) as sum from a o1 group by o1.i;
>
> --- ERROR ---
> select o1.i::smallint,count(*),(select sum(o2.i) from a o2 where
> o2.i=o1.i::smallint) as sum from a o1 group by o1.i::smallint;
>
> ----
>
> Notice that the difference is only the cast in the order by clause. Here
> are my results:
>
> template1=# select version();
> version
> ------------------------------------------------------------------------
> PostgreSQL 7.3devel on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
> (1 row)
>
> template1=# \d a
> Table "public.a"
> Column | Type | Modifiers
> --------+---------+-----------
> i | integer |
>
> template1=# select * from a;
> i
> ---
> 1
> 1
> 1
> 1
> 1
> 2
> 2
> 2
> 2
> 3
> 3
> 3
> 3
> (13 rows)
>
> te1=# select o1.i::smallint,count(*),(select sum(o2.i) from a o2 where
> o2.i=o1.i::smallint) as sum from a o1 group by o1.i;
> i | count | sum
> ---+-------+-----
> 1 | 5 | 5
> 2 | 4 | 8
> 3 | 4 | 12
> (3 rows)
>
> template1=# select o1.i::smallint,count(*),(select sum(o2.i) from a o2
> where o2.i=o1.i::smallint) as sum from a o1 group by o1.i::smallint;
> ERROR: Sub-SELECT uses un-GROUPed attribute o1.i from outer query
>
> [under patched version]
>
> template1=# select o1.i::smallint,count(*),(select sum(o2.i) from a o2
> where o2.i=o1.i::smallint) as sum from a o1 group by o1.i;
> i | count | sum
> ---+-------+-----
> 1 | 5 | 5
> 2 | 4 | 8
> 3 | 4 | 12
> (3 rows)
>
> template1=# select o1.i::smallint,count(*),(select sum(o2.i) from a o2
> where o2.i=o1.i::smallint) as sum from a o1 group by o1.i::smallint;
> i | count | sum
> ---+-------+-----
> 1 | 5 | 5
> 2 | 4 | 8
> 3 | 4 | 12
> (3 rows)
>
>
> As it works out, the bug is caused by these lines in
> optimizer/util/clauses.c
>
> if (equal(thisarg, lfirst(gl)))
> {
> contained_in_group_clause = true;
> break;
> }
>
> 'thisarg' is an item from the args list used by Expr *. We only access
> this code inside check_subplans_for_ungrouped_vars_walker() if the node is
> a subplan. The problem is that equal() is not sufficiently intelligent to
> consider the equality of 'thisarg' and lfirst(gl) (an arg from the group
> by clause) considering that thisarg and lfirst(gl) are not necessarily of
> the same node type. This means we fail out in equal():
>
> /*
> * are they the same type of nodes?
> */
> if (nodeTag(a) != nodeTag(b))
> return false;
>
>
> The patch below 'fixes' this (and possibly breaks everything else). I
> haven't tested it rigorously and it *just* special cases group by
> clauses with functions in them. Here's the patch:
>
> Index: ./src/backend/optimizer/util/clauses.c
> ===================================================================
> RCS
> file: /projects/cvsroot/pgsql-server/src/backend/optimizer/util/clauses.c,v
> retrieving revision 1.107
> diff -2 -c -r1.107 clauses.c
> *** ./src/backend/optimizer/util/clauses.c 2002/08/31 22:10:43 1.107
> --- ./src/backend/optimizer/util/clauses.c 2002/09/30 15:02:47
> ***************
> *** 703,706 ****
> --- 703,718 ----
> contained_in_group_clause = true;
> break;
> + } else {
> + if(IsA(lfirst(gl),Expr) &&
> + length(((Expr *)lfirst(gl))->args) == 1 &&
> + IsA(lfirst(((Expr *)lfirst(gl))->args),Var) ) {
> +
> + Var *tvar = (Var *) lfirst(((Expr *)lfirst(gl))->args);
> + if(var->varattno == tvar->varattno) {
> + contained_in_group_clause = true;
> + break;
> + }
> +
> + }
> }
> }
>
> ----
>
> There are two assumptions here: 1) the only time this bug occurs is when
> the group by clause argument is an expression and a function at that (even
> though I do no test for this correctly) 2) We can see whether thisarg ==
> lfirst(gl) by looking at the varattno of each and comparing. It occurs to
> me that this is just plain wrong and works only for the specific query.
>
> The reason why I've sent this email to you and not to the list is I do not
> have time to follow through on this -- as much as I would like to. I
> simply do no have the time. :-(
>
> Gavin
>
>
--
Bruce Momjian | http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
