Re: Removing unneeded self joins

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.

--
regards,
Andrey Lepikhov
Postgres Professional