Hi Andrey, On Mon, Nov 30, 2020 at 2:51 PM Andrey V. Lepikhov <a.lepik...@postgrespro.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/
