Re: Removing unneeded self joins

On Tue, Jan 9, 2024 at 8:08 AM Alexander Korotkov <aekorotkov(at)gmail(dot)com> wrote:
> On Tue, Jan 9, 2024 at 6:00 AM Alexander Lakhin <exclusion(at)gmail(dot)com> wrote:
> > 09.01.2024 01:09, Alexander Korotkov wrote:
> > > Fixed in 30b4955a46.
> >
> > Thank you for fixing that!
> >
> > I've found another anomaly coined with d3d55ce57. This query:
> > CREATE TABLE t(a int PRIMARY KEY, b int);
> > INSERT INTO t VALUES (1, 1), (2, 1);
> >
> > WITH t1 AS (SELECT * FROM t)
> > UPDATE t SET b = t1.b + 1 FROM t1
> > WHERE t.a = t1.a RETURNING t.a, t1.b;
> >
> > gives "ERROR: variable not found in subplan target lists" on d3d55ce57, but
> > starting from a7928a57b it gives an incorrect result:
> > a | b
> > ---+---
> > 1 | 2
> > 2 | 2
> > (2 rows)
>
> I see. It seems to be not safe to apply SJE to the modify table
> target relation because it could use a different snapshot for the
> RETURNING clause. I think we should just forbid SJE to involve the
> modify table target relation. I'm planning to fix this later today.

Fixed in 8c441c08279.

------
Regards,
Alexander Korotkov