Обсуждение: BUG #14105: regression for right join - failed to build any 2-way joins

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.

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. Wit=
h
> 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 =3D tt.id)
>       )
>       right join (select 1 as id) as tt2
>       on (coalesce(tt.id) =3D 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.
>

=E2=80=8BSounds familiar...

Anyway I confirmed on 9.5.2=E2=80=8B

=E2=80=8Band also see it on 9.3.12

No ready access to 9.4 or 9.2

David J.
=E2=80=8B

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