Re: [BUGS] Improper const-evaluation of HAVING with grouping sets and subquery pullup

I wrote:
> I think that Heikki's expectation is the correct one, and the reason the
> output looks the way it does is that setrefs.c is dropping the ball
> somehow and confusing the two "g" references.  ...
> We should have used ressortgroupref matching to prevent this, but without
> having checked the code right now, I think that we might only apply that
> logic to non-Var tlist entries.  If the Agg output tlist had mentioned
> column 2 not column 1 of the child node, I bet we'd get the right answer.

Indeed, the attached patch passes all regression tests and produces the
same answers for both of Heikki's examples:

regression=# SELECT g as newalias1, g as newalias3
FROM generate_series(1,3) g
GROUP BY newalias1, ROLLUP(newalias3);
 newalias1 | newalias3 
-----------+-----------
         1 |         1
         3 |         3
         2 |         2
         2 |          
         3 |          
         1 |          
(6 rows)


			regards, tom lane

diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index 1382b67..9aeaf87 100644
*** a/src/backend/optimizer/plan/setrefs.c
--- b/src/backend/optimizer/plan/setrefs.c
*************** set_upper_references(PlannerInfo *root, 
*** 1744,1751 ****
  		TargetEntry *tle = (TargetEntry *) lfirst(l);
  		Node	   *newexpr;
  
! 		/* If it's a non-Var sort/group item, first try to match by sortref */
! 		if (tle->ressortgroupref != 0 && !IsA(tle->expr, Var))
  		{
  			newexpr = (Node *)
  				search_indexed_tlist_for_sortgroupref(tle->expr,
--- 1744,1751 ----
  		TargetEntry *tle = (TargetEntry *) lfirst(l);
  		Node	   *newexpr;
  
! 		/* If it's a sort/group item, first try to match by sortref */
! 		if (tle->ressortgroupref != 0)
  		{
  			newexpr = (Node *)
  				search_indexed_tlist_for_sortgroupref(tle->expr,
*************** search_indexed_tlist_for_non_var(Expr *n
*** 2113,2119 ****
  
  /*
   * search_indexed_tlist_for_sortgroupref --- find a sort/group expression
-  *		(which is assumed not to be just a Var)
   *
   * If a match is found, return a Var constructed to reference the tlist item.
   * If no match, return NULL.
--- 2113,2118 ----

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs