public inbox for [email protected]help / color / mirror / Atom feed
Re: pg_get_viewdef() produces non-round-trippable SQL for views with USING join on mismatched integer types 4+ messages / 2 participants [nested] [flat]
* Re: pg_get_viewdef() produces non-round-trippable SQL for views with USING join on mismatched integer types @ 2026-02-27 08:13 Richard Guo <[email protected]> 2026-02-27 15:21 ` Re: pg_get_viewdef() produces non-round-trippable SQL for views with USING join on mismatched integer types Tom Lane <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Richard Guo @ 2026-02-27 08:13 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Swirl Smog Dowry <[email protected]>; [email protected] On Fri, Feb 27, 2026 at 12:23 PM Richard Guo <[email protected]> wrote: > On Fri, Feb 27, 2026 at 3:27 AM Tom Lane <[email protected]> wrote: > > 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. > Right. We should keep the unmodified GROUP BY expressions in the > parse tree, and then rely on the planner to flatten the join alias > vars within them. > > +1 to the fix. I am on the fence about whether this fix is safe to back-patch to v18. I cautiously think it is safe, as it does not change the parsetree's external representation and does not require an initdb. However, this fix will only apply to newly created views. Users who have existing views affected by this bug will have to recreate them after upgrading to get the corrected pg_get_viewdef output. (really kicking myself for this.) - Richard ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_get_viewdef() produces non-round-trippable SQL for views with USING join on mismatched integer types 2026-02-27 08:13 Re: pg_get_viewdef() produces non-round-trippable SQL for views with USING join on mismatched integer types Richard Guo <[email protected]> @ 2026-02-27 15:21 ` Tom Lane <[email protected]> 2026-02-27 18:08 ` Re: pg_get_viewdef() produces non-round-trippable SQL for views with USING join on mismatched integer types Tom Lane <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Tom Lane @ 2026-02-27 15:21 UTC (permalink / raw) To: Richard Guo <[email protected]>; +Cc: Swirl Smog Dowry <[email protected]>; [email protected] Richard Guo <[email protected]> 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. > However, this fix will only apply to newly created views. Users who > have existing views affected by this bug will have to recreate them > after upgrading to get the corrected pg_get_viewdef output. Yeah :-(. What's really annoying is that probably people will not notice until they try to upgrade to v19, and by then recreating the view correctly might be difficult. But I'm not seeing a way to smooth their path. regards, tom lane ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_get_viewdef() produces non-round-trippable SQL for views with USING join on mismatched integer types 2026-02-27 08:13 Re: pg_get_viewdef() produces non-round-trippable SQL for views with USING join on mismatched integer types Richard Guo <[email protected]> 2026-02-27 15:21 ` Re: pg_get_viewdef() produces non-round-trippable SQL for views with USING join on mismatched integer types Tom Lane <[email protected]> @ 2026-02-27 18:08 ` Tom Lane <[email protected]> 2026-03-02 05:53 ` Re: pg_get_viewdef() produces non-round-trippable SQL for views with USING join on mismatched integer types Richard Guo <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Tom Lane @ 2026-02-27 18:08 UTC (permalink / raw) To: Richard Guo <[email protected]>; +Cc: Swirl Smog Dowry <[email protected]>; [email protected] I wrote: > Richard Guo <[email protected]> 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_get_viewdef() produces non-round-trippable SQL for views with USING join on mismatched integer types 2026-02-27 08:13 Re: pg_get_viewdef() produces non-round-trippable SQL for views with USING join on mismatched integer types Richard Guo <[email protected]> 2026-02-27 15:21 ` Re: pg_get_viewdef() produces non-round-trippable SQL for views with USING join on mismatched integer types Tom Lane <[email protected]> 2026-02-27 18:08 ` Re: pg_get_viewdef() produces non-round-trippable SQL for views with USING join on mismatched integer types Tom Lane <[email protected]> @ 2026-03-02 05:53 ` Richard Guo <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Richard Guo @ 2026-03-02 05:53 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Swirl Smog Dowry <[email protected]>; [email protected] On Sat, Feb 28, 2026 at 3:08 AM Tom Lane <[email protected]> wrote: > So I went ahead and pushed this, using your test case. Thank you for handling this, Tom. - Richard ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-03-02 05:53 UTC | newest] Thread overview: 4+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-02-27 08:13 Re: pg_get_viewdef() produces non-round-trippable SQL for views with USING join on mismatched integer types Richard Guo <[email protected]> 2026-02-27 15:21 ` Tom Lane <[email protected]> 2026-02-27 18:08 ` Tom Lane <[email protected]> 2026-03-02 05:53 ` Richard Guo <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox