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: <852982.1772205703@sss.pgh.pa.us>
References: <CA+-gibjCg_vjcq3hWTM0sLs3_TUZ6Q9rkv8+pe2yJrdh4o4uoQ@mail.gmail.com> <643857.1772122240@sss.pgh.pa.us> <717830.1772130430@sss.pgh.pa.us> <CAMbWs4_FLicDsNpotM0uEFkeveJ+gULe4VP0PyJ=FVsD9iYCGA@mail.gmail.com> <CAMbWs49AUBVtMG=BXwfB5xdg1Zd9dwJg42C1hLayYtO2Kmxqpw@mail.gmail.com> <852982.1772205703@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Fri, 27 Feb 2026 10:21:43 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <988125.1772215716.1@sss.pgh.pa.us>
Date: Fri, 27 Feb 2026 13:08:36 -0500
Message-ID: <988126.1772215716@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/988126.1772215716%40sss.pgh.pa.us>
Precedence: bulk

I wrote:
> Richard Guo <guofenglinux@gmail.com> writes:
>> I am on the fence about whether this fix is safe to back-patch to v18.

> I don't think we have a lot of choice.  The cases where it makes a
> difference are pretty broken.  Fortunately, I think these cases
> are rare.  JOIN USING combining two different-type columns has got
> to be an edge-case usage, and I think it likely doesn't matter much
> in other cases.

I spent a bit of effort on determining which cases actually cause
wrong output, and AFAICT it's very narrow: you need "SELECT ...
t1 LEFT JOIN t2 USING (x) GROUP BY x" where t1.x and t2.x are
different data types and t1.x is the side requiring coercion.
With no coercion, or if the join side to be coerced is nullable, we
show the flattened alias Var but that doesn't actually break anything.

So I went ahead and pushed this, using your test case.

			regards, tom lane