Re: [HACKERS] views and group by (formerly: create view as selec - Mailing list pgsql-hackers

I now get.  I am sure it instills confidence in our users:test=> CREATE TABLE t1 (a int4, b int4);CREATEtest=> test=>
CREATEVIEW v1 AS SELECT b, count(b) FROM t1 GROUP BY b;CREATEtest=> SELECT count FROM v1;ERROR:  union_planner: query
ismarked hasAggs, but I don't see any
 


> jwieck@debis.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@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026