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

Swirl Smog Dowry <swirl-smog-dowry(at)duck(dot)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(at)postgresql(dot)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