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

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