BTW, it suddenly strikes me that if anyone actually is using this
syntax in the field, they're most likely doing it like this:
regression=# create view v as
regression-# select * from (values (1),(2),(3) order by 1) v(x);
CREATE VIEW
which nicely sidesteps the question of what column aliases appl
Robert Haas writes:
> To be honest, that pushdown feels really uncomfortable to me. To me,
> the natural syntax for associating an alias with a VALUES clause would
> be something like "VALUES (...) myalias" or, if you also wanted column
> aliasing, "VALUES (...) myalias(a,b,c)". That would feel ju
On Mon, Jan 6, 2025 at 3:45 PM Tom Lane wrote:
> SELECT ... FROM (VALUES (...) ORDER BY a) v(a,b,c)
>
> If it'd been like that all along, nobody would blink at it I think,
> even though you could argue that it's action-at-a-distance to let
> an outer alias affect what happens inside the im
Robert Haas writes:
> On Thu, Jan 2, 2025 at 4:41 PM Tom Lane wrote:
>> (Or we could decide to simplify
>> things at the cost of breaking such SQL code, since there probably
>> is none in the field. It's still not clear to me which choice is
>> better.)
> This part I don't understand.
Sorry, n
On Thu, Jan 2, 2025 at 4:41 PM Tom Lane wrote:
> I'm not seeing where there's a correctness issue here? The parser
> is charged with assigning aliases to RTEs that lack one, and with
> this patch that's still true. It's just assigning a different alias
> than it did before. There is no question
Robert Haas writes:
> On Sat, Nov 2, 2024 at 1:09 PM Tom Lane wrote:
>> It seems sufficient to avoid alias pushdown when there's an ORDER BY
>> inside the VALUES subquery. We disallow a locking clause, and
>> while there can be LIMIT/OFFSET, those aren't allowed to reference the
>> VALUES output
On Sat, Nov 2, 2024 at 1:09 PM Tom Lane wrote:
> regression=# create view vv as SELECT * FROM (VALUES (4),(2),(3),(1) ORDER BY
> t1.x LIMIT 2) AS t1(x);
> CREATE VIEW
> regression=# \d+ vv
> View "public.vv"
> Column | Type | Collation | Nullable | Default | Stora
Just to follow up here --- I put this patch on hold for a few days
because I had to work on release notes. Now I'm glad I did, because
Robert Haas is pushing a proposal that would change the basis of
discussion:
https://www.postgresql.org/message-id/flat/CA%2BTgmoYSYmDA2GvanzPMci084n%2BmVucv0bJ0H
On 11/3/24 00:09, Tom Lane wrote:
Ashutosh Bapat writes:
On Tue, Oct 29, 2024 at 10:49 PM Tom Lane wrote:
regression=# SELECT x
regression-#FROM ( VALUES (4), (2), (3), (1)
regression(# ORDER BY t1_1.x
regression(# LIMIT 2) t1(x);
ERROR: missing FROM-clause entry for t
Ashutosh Bapat writes:
> On Tue, Oct 29, 2024 at 10:49 PM Tom Lane wrote:
>> 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
On Tue, Oct 29, 2024 at 10:49 PM Tom Lane wrote:
>
> Andrei Lepikhov 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
On 10/30/24 00:19, Tom Lane wrote:
Andrei Lepikhov 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 n
Andrei Lepikhov 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 w
Andrei Lepikhov writes:
> Thanks for the detailed explanation. I agree it make sense.
Cool, I think we're all agreed then.
> Also, after skimming the code, I propose some extra tests:
Most of these are covered well enough by existing tests, aren't
they?
regards, tom lan
On Mon, Oct 28, 2024 at 8:46 PM Tom Lane wrote:
>
> Ashutosh Bapat writes:
> > The patch looks good to me, except the name of the new member.
>
> > CommonTableExpr *p_parent_cte; /* this query's containing CTE */
> > + Alias*p_parent_alias; /* parent's alias for this query */
>
> > the two
On 10/28/24 22:05, Tom Lane wrote:
Andrei Lepikhov writes:
My goal is to understand why the implementation follows this pattern. As
I see, previously, we had consistent behaviour, according to which we
removed the pulling-up subquery's alias as well. And I want to know, is
it really the only wa
On Mon, Oct 28, 2024 at 8:16 PM Tom Lane wrote:
> Ashutosh Bapat writes:
> > The patch looks good to me, except the name of the new member.
>
> > CommonTableExpr *p_parent_cte; /* this query's containing CTE */
> > + Alias*p_parent_alias; /* parent's alias for this query */
>
> > the two "
Ashutosh Bapat writes:
> The patch looks good to me, except the name of the new member.
> CommonTableExpr *p_parent_cte; /* this query's containing CTE */
> + Alias*p_parent_alias; /* parent's alias for this query */
> the two "parent"s here mean different things and that might lead one
>
Andrei Lepikhov writes:
> My goal is to understand why the implementation follows this pattern. As
> I see, previously, we had consistent behaviour, according to which we
> removed the pulling-up subquery's alias as well. And I want to know, is
> it really the only way to break this behavior? M
On 28/10/2024 20:19, Ashutosh Bapat wrote:
On Mon, Oct 28, 2024 at 10:17 AM Andrei Lepikhov wrote:
On 10/28/24 03:15, Yasir wrote:
By design of my solution, I was not taking it as a bug. But now, I agree
with your opinion.
I think the case provided by Ashutosh was initially correct, and noth
On Mon, Oct 28, 2024 at 10:17 AM Andrei Lepikhov wrote:
>
> On 10/28/24 03:15, Yasir wrote:
> > By design of my solution, I was not taking it as a bug. But now, I agree
> > with your opinion.
> I think the case provided by Ashutosh was initially correct, and nothing
> needs to change. Look at the
On 10/28/24 03:15, Yasir wrote:
By design of my solution, I was not taking it as a bug. But now, I agree
with your opinion.
I think the case provided by Ashutosh was initially correct, and nothing
needs to change. Look at the similar case:
EXPLAIN SELECT x,y FROM (
SELECT oid,relname FROM pg
On Mon, Oct 28, 2024 at 1:07 AM Tom Lane wrote:
> Yasir writes:
> > On Sat, Oct 26, 2024 at 12:21 AM Tom Lane wrote:
> >> I forgot to mention a third problem, which is that reassigning the
> >> alias during subquery pullup means it doesn't happen if subquery
> >> pullup doesn't happen.
>
> > Ye
Yasir writes:
> On Sat, Oct 26, 2024 at 12:21 AM Tom Lane wrote:
>> I forgot to mention a third problem, which is that reassigning the
>> alias during subquery pullup means it doesn't happen if subquery
>> pullup doesn't happen.
> Yes, that is by design.
By design? How can you claim that's not
On Sat, Oct 26, 2024 at 12:21 AM Tom Lane wrote:
> I wrote:
> > However ... I don't like this implementation, not even a little
> > bit.
>
> I forgot to mention a third problem, which is that reassigning the
> alias during subquery pullup means it doesn't happen if subquery
> pullup doesn't happe
On Fri, Oct 25, 2024 at 10:35 PM Tom Lane wrote:
> Yasir writes:
> > I have fixed the code to produce desired output by adding a few lines in
> > pull_up_simple_subquery().
> > Attached patch is divided in 2 files:
> > - 001-Fix-Alias-VALUES-RTE.patch contains the actual fix.
> > - 002-Fix-Ali
I wrote:
> However ... I don't like this implementation, not even a little
> bit.
I forgot to mention a third problem, which is that reassigning the
alias during subquery pullup means it doesn't happen if subquery
pullup doesn't happen. As an example, with your patch:
regression=# explain verbos
Yasir writes:
> I have fixed the code to produce desired output by adding a few lines in
> pull_up_simple_subquery().
> Attached patch is divided in 2 files:
> - 001-Fix-Alias-VALUES-RTE.patch contains the actual fix.
> - 002-Fix-Alias-VALUES-RTE.patch contains the expected output changes
> agai
Hi Ashutosh & PG Hackers,
I have fixed the code to produce desired output by adding a few lines in
pull_up_simple_subquery().
Attached patch is divided in 2 files:
- 001-Fix-Alias-VALUES-RTE.patch contains the actual fix.
- 002-Fix-Alias-VALUES-RTE.patch contains the expected output changes
agai
On Mon, Jul 1, 2024 at 3:17 PM Ashutosh Bapat
wrote:
> Hi All,
> While reviewing Richard's patch for grouping sets, I stumbled upon
> following explain output
>
> explain (costs off)
> select distinct on (a, b) a, b
> from (values (1, 1), (2, 2)) as t (a, b) where a = b
> group by grouping sets((
30 matches
Mail list logo
