From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Guo <guofenglinux@gmail.com>
cc: Swirl Smog Dowry <swirl-smog-dowry@duck.com>,
        pgsql-bugs@lists.postgresql.org
Subject: Re: pg_get_viewdef() produces non-round-trippable SQL for views with USING join on mismatched integer types
In-reply-to: <643857.1772122240@sss.pgh.pa.us>
References: <CA+-gibjCg_vjcq3hWTM0sLs3_TUZ6Q9rkv8+pe2yJrdh4o4uoQ@mail.gmail.com> <643857.1772122240@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Thu, 26 Feb 2026 11:10:40 -0500"
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <717822.1772130426.0@sss.pgh.pa.us>
Date: Thu, 26 Feb 2026 13:27:10 -0500
Message-ID: <717830.1772130430@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/717830.1772130430%40sss.pgh.pa.us>
Precedence: bulk

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <717822.1772130426.1@sss.pgh.pa.us>

I wrote:
> The first groupexpr is the same as the joinaliasvars entry for that
> column in the JOIN RTE.  This surprises me: I'd expect to see a
> reference to the join output column there, ie Var 3/1, because I'm
> pretty sure that's what parsing of "GROUP BY year" would have produced
> initially.  If it were like that, I think ruleutils would produce the
> desired output.  So I'd tentatively classify this as "join alias Vars
> are being flattened too soon".  Richard, any thoughts?

The problem is obvious after looking at parseCheckAggregates: the
RTE_GROUP RTE is manufactured using the groupClauses list after we
have flattened that, which we are only doing for comparison purposes;
it shouldn't affect what goes into the parse tree.  I experimented
with just changing the order of operations, and that seems to fix it.
The lack of any effect on check-world shows we need more test cases
here ...

			regards, tom lane


------- =_aaaaaaaaaa0
Content-Type: text/x-diff;
	name="wip-preserve-join-aliases-in-RTE_GROUP.patch";
	charset="us-ascii"
Content-ID: <717822.1772130426.2@sss.pgh.pa.us>
Content-Description: wip-preserve-join-aliases-in-RTE_GROUP.patch

diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index 25ee0f87d93..d0187ea84a0 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -1213,8 +1213,8 @@ parseCheckAggregates(ParseState *pstate, Query *qry)
 	}
 
 	/*
-	 * Build a list of the acceptable GROUP BY expressions for use by
-	 * substitute_grouped_columns().
+	 * Build a list of the acceptable GROUP BY expressions to save in the
+	 * RTE_GROUP RTE, and for use by substitute_grouped_columns().
 	 *
 	 * We get the TLE, not just the expr, because GROUPING wants to know the
 	 * sortgroupref.
@@ -1231,6 +1231,23 @@ parseCheckAggregates(ParseState *pstate, Query *qry)
 		groupClauses = lappend(groupClauses, expr);
 	}
 
+	/*
+	 * If there are any acceptable GROUP BY expressions, build an RTE and
+	 * nsitem for the result of the grouping step.  (It's important to do this
+	 * before flattening join alias vars in groupClauses, because the RTE
+	 * should preserve any alias vars that were in the input.)
+	 */
+	if (groupClauses)
+	{
+		pstate->p_grouping_nsitem =
+			addRangeTableEntryForGroup(pstate, groupClauses);
+
+		/* Set qry->rtable again in case it was previously NIL */
+		qry->rtable = pstate->p_rtable;
+		/* Mark the Query as having RTE_GROUP RTE */
+		qry->hasGroupRTE = true;
+	}
+
 	/*
 	 * If there are join alias vars involved, we have to flatten them to the
 	 * underlying vars, so that aliased and unaliased vars will be correctly
@@ -1266,21 +1283,6 @@ parseCheckAggregates(ParseState *pstate, Query *qry)
 		}
 	}
 
-	/*
-	 * If there are any acceptable GROUP BY expressions, build an RTE and
-	 * nsitem for the result of the grouping step.
-	 */
-	if (groupClauses)
-	{
-		pstate->p_grouping_nsitem =
-			addRangeTableEntryForGroup(pstate, groupClauses);
-
-		/* Set qry->rtable again in case it was previously NIL */
-		qry->rtable = pstate->p_rtable;
-		/* Mark the Query as having RTE_GROUP RTE */
-		qry->hasGroupRTE = true;
-	}
-
 	/*
 	 * Replace grouped variables in the targetlist and HAVING clause with Vars
 	 * that reference the RTE_GROUP RTE.  Emit an error message if we find any

------- =_aaaaaaaaaa0--