Re: Removing unneeded self joins

Hi Andrey,

On Mon, Nov 30, 2020 at 2:51 PM Andrey V. Lepikhov
<a(dot)lepikhov(at)postgrespro(dot)ru> wrote:
>
> On 11/29/20 10:10 PM, Heikki Linnakangas wrote:
> > On 28/11/2020 19:21, Andrey Lepikhov wrote:
> >> On 27.11.2020 21:49, Heikki Linnakangas wrote:
> >> CREATE TABLE a(x int, y int);
> >> CREATE UNIQUE INDEX ON a(x);
> >> SELECT a1.* FROM a a1, a a2 WHERE a1.x = a2.x; -- self-join
> >> CREATE UNIQUE INDEX ON a(y);
> >> SELECT a1.* FROM a a1, a a2 WHERE a1.x = a2.y; -- self-join too
> >
> > The latter join is not "useless". The patch is returning incorrect
> > result for that query:
> >
> >> postgres=# insert into a values (1, 2);
> >> INSERT 0 1
> >> postgres=# insert into a values (2, 1);
> >> INSERT 0 1
> >> postgres=# SELECT a1.* FROM a a1, a a2 WHERE a1.x = a2.y; -- WRONG RESULT
> >> x | y ---+---
> >> (0 rows)
> >>
> >> postgres=# set enable_self_join_removal=off;
> >> SET
> >> postgres=# SELECT a1.* FROM a a1, a a2 WHERE a1.x = a2.y; -- CORRECT
> >> RESULT
> >> x | y ---+---
> >> 1 | 2
> >> 2 | 1
> >> (2 rows)
>
> Thanks, it is my fault. I tried to extend this patch with foreign key
> references and made a mistake.
> Currently I rollback this new option (see patch in attachment), but will
> be working for a while to simplify this patch.

Are you working to simplify the patch? This patch has been "Waiting on
Author" for 1 month and doesn't seem to pass cfbot tests. Please
update the patch.

Regards,

--
Masahiko Sawada
EnterpriseDB: https://www.enterprisedb.com/