Re: [HACKERS] views and group by (formerly: create view as selec

I now get. I am sure it instills confidence in our users:

test=> CREATE TABLE t1 (a int4, b int4);
CREATE
test=>
test=> CREATE VIEW v1 AS SELECT b, count(b) FROM t1 GROUP BY b;
CREATE
test=> SELECT count FROM v1;
ERROR: union_planner: query is marked hasAggs, but I don't see any

> jwieck(at)debis(dot)com (Jan Wieck) wrote:
> > CREATE TABLE t1 (a int4, b int4);
> > CREATE VIEW v1 AS SELECT b, count(b) FROM t1 GROUP BY b;
> > SELECT count FROM v1;
> > [ ... ker-boom ... ]
>
> and I said I thought it was the same planner bug I was chasing in
> non-VIEW-using examples of GROUP BY. It turns out it's not the same.
> In the above example, the breakage happens in rewrite before the
> planner ever sees it. When make_groupPlan is called, it sees a
> GroupClause like this:
>
> (gdb) p *((GroupClause *) 0x400b8690)->entry
> $107 = {type = T_TargetEntry, resdom = 0x400b86c0, fjoin = 0x0,
> expr = 0x400b8700}
> (gdb) p *((GroupClause *) 0x400b8690)->entry->resdom
> $108 = {type = T_Resdom, resno = 1, restype = 23, restypmod = -1,
> resname = 0x400b86e8 "b", reskey = 0, reskeyop = 0, resjunk = 0}
> (gdb) p *(Var*)((GroupClause *) 0x400b8690)->entry->expr
> $114 = {type = T_Var, varno = 4, varattno = 2, vartype = 23, vartypmod = -1,
> varlevelsup = 0, varnoold = 4, varoattno = 2}
>
> and a target list like this:
>
> (gdb) p *(TargetEntry*)0x400b8a70
> $118 = {type = T_TargetEntry, resdom = 0x400b8a88, fjoin = 0x0,
> expr = 0x400b8ac8}
> (gdb) p *((TargetEntry*)0x400b8a70)->resdom
> $119 = {type = T_Resdom, resno = 1, restype = 23, restypmod = -1,
> resname = 0x400b8ab0 "count", reskey = 0, reskeyop = 0, resjunk = 0}
> (gdb) p *(Aggref*)((TargetEntry*)0x400b8a70)->expr
> $121 = {type = T_Aggref, aggname = 0x400b8af0 "count", basetype = 0,
> aggtype = 23, target = 0x400b8b08, aggno = 0, usenulls = 0 '\000'}
> (gdb) p *(Var*)((Aggref*)((TargetEntry*)0x400b8a70)->expr)->target
> $123 = {type = T_Var, varno = 4, varattno = 2, vartype = 23, vartypmod = -1,
> varlevelsup = 0, varnoold = 4, varoattno = 2}
>
> which is all fine except that the two different expressions have been
> given the same Resdom number (resno = 1 in both). That confuses
> make_groupPlan into thinking that they are the same expression, and
> trouble ensues.
>
> If I understand this stuff correctly, the rewriter should have been
> careful to assign different Resdom numbers to distinct expressions
> in the target and group-by lists of the rewritten query. That's how
> things look in an un-rewritten query, anyway. So I think this is a
> rewrite bug.
>
> If you don't like that answer, it might be possible to change the
> planner so that it doesn't put any faith in the Resdom numbers, but
> uses equal() on the expr fields to decide whether target and group-by
> entries are the same. That would be a slower but probably much more
> robust approach. Jan, what do you think?
>
> regards, tom lane
>
>

--
Bruce Momjian | http://www.op.net/~candle
maillist(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026