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

On Wed, Apr 20, 2016 at 12:47 PM, <vojta(dot)rylko(at)gmail(dot)com> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 14105
> Logged by: Vojtech Rylko
> Email address: vojta(dot)rylko(at)gmail(dot)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.
​