Re: [HACKERS] views and group by (formerly: create view as selec
От | Tom Lane |
---|---|
Тема | Re: [HACKERS] views and group by (formerly: create view as selec |
Дата | |
Msg-id | 12498.925602693@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | Re: [HACKERS] views and group by (formerly: create view as selec (jwieck@debis.com (Jan Wieck)) |
Ответы |
Re: [HACKERS] views and group by (formerly: create view as selec
|
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления: