Re: pg_get_viewdef() produces non-round-trippable SQL for views with USING join on mismatched integer types - Mailing list pgsql-bugs

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