BUG #14105: regression for right join - failed to build any 2-way joins

On Wed, Apr 20, 2016 at 12:47 PM, <vojta.rylko@gmail.com> wrote:

The following bug has been logged on the website:

Bug reference: 14105
Logged by: Vojtech Rylko
Email address: vojta.rylko@gmail.com
PostgreSQL version: 9.5.2
Operating system: Linux version 3.16.0-30-generic
Description:

I have problem with right join which uses coalesce in join condition. With
other type of join or without coalesce query works.

Minimal reproducer:

create table a as (select 1 as id);
select *
from ((
a as a1
full join (select 1 as id) as tt
on (a1.id = tt.id)
)
right join (select 1 as id) as tt2
on (coalesce(tt.id) = tt2.id)
)
;
ERROR: XX000: failed to build any 2-way joins
LOCATION: standard_join_search, allpaths.c:1832

It works on PostgreSQL 9.2.13., returning:
id | id | id
----+----+----
1 | 1 | 1
(1 row)

Cheers,
V.

​Sounds familiar...

Anyway I confirmed on 9.5.2​

​and also see it on 9.3.12

No ready access to 9.4 or 9.2

David J.
​

vojta.rylko@gmail.com writes:

I have problem with right join which uses coalesce in join condition. With
other type of join or without coalesce query works.

I've pushed a fix for this. Thanks for the report and test case!

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs