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

Swirl Smog Dowry <swirl-smog-dowry@duck.com> writes:
> When a view uses a USING join on columns with different integer
> types (integer vs bigint) and the SELECT clause contains an explicit
> narrowing cast, pg_get_viewdef() produces SQL that PostgreSQL itself
> rejects. This makes pg_dump produce dumps that fail on restore for
> any such view.

Hmm, yeah.  This used to work as-expected, too.  "git bisect" finds
that it broke at

247dea89f7616fdf06b7272b74abafc29e8e5860 is the first bad commit
commit 247dea89f7616fdf06b7272b74abafc29e8e5860
Author: Richard Guo <rguo@postgresql.org>
Date:   Tue Sep 10 12:35:34 2024 +0900

    Introduce an RTE for the grouping step

Looking at the parse tree for the problem query, I see

	      {RANGETBLENTRY 
	      :alias <> 
	      :eref 
	         {ALIAS 
	         :aliasname *GROUP* 
	         :colnames ("?column?" "label")
	         }
	      :rtekind 9 
	      :groupexprs (
	         {FUNCEXPR 
	         :funcid 481 
	         :funcresulttype 20 
	         :funcretset false 
	         :funcvariadic false 
	         :funcformat 2 
	         :funccollid 0 
	         :inputcollid 0 
	         :args (
	            {VAR 
	            :varno 1 
	            :varattno 1 
	            :vartype 23 
	            :vartypmod -1 
	            :varcollid 0 
	            :varnullingrels (b)
	            :varlevelsup 0 
	            :varreturningtype 0 
	            :varnosyn 1 
	            :varattnosyn 1 
	            :location -1
	            }
	         )
	         :location -1
	         }
	         {VAR 
	         :varno 2 
	         :varattno 2 
	         :vartype 25 
	         :vartypmod -1 
	         :varcollid 100 
	         :varnullingrels (b 3)
	         :varlevelsup 0 
	         :varreturningtype 0 
	         :varnosyn 2 
	         :varattnosyn 2 
	         :location 32
	         }
	      )
	      :lateral false 
	      :inFromCl false 
	      :securityQuals <>
	      }

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?

			regards, tom lane