On Tue, Oct 29, 2024 at 10:49 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > > Andrei Lepikhov <lepi...@gmail.com> writes: > > -- New behavior > > EXPLAIN (COSTS OFF, VERBOSE) > > SELECT * FROM (VALUES (4),(2),(3),(1) ORDER BY t1.x LIMIT 2) AS t1(x); > > SELECT * FROM (VALUES (4),(2),(3),(1) ORDER BY t1.x LIMIT 2) AS t1(x); > > After taking a closer look at that, yeah it's new behavior, and > I'm not sure we want to change it. (The existing behavior is that > you'd have to write 'column1' or '"*VALUES*".column1' in the > subquery's ORDER BY.) > > This example also violates my argument that the user thinks they > are attaching the alias directly to VALUES. > > So what I now think > is that we ought to tweak the patch so that the parent alias is > pushed down only when the subquery contains just VALUES, no other > clauses. Per a look at the grammar, ORDER BY, LIMIT, and FOR > UPDATE could conceivably appear alongside VALUES; although > FOR UPDATE would draw "FOR UPDATE cannot be applied to VALUES", > so maybe we needn't worry about it. > > Thoughts?
If the user writes it in this manner, I think they intend to attach the alias to VALUES() since there's no other way to do it. What is weird is that they can use the alias before it's declared. For the sake of eliminating this weirdness, your proposed tweak sounds fine to me. Even if we don't add that tweak, it's not easy for users to find out that they can write the query this way. But it's better to plug the hole before somebody starts exploiting it. -- Best Wishes, Ashutosh Bapat
