Thread: BUG #14111: After minor upgrade (9.2.6 -> 9.2.16): ERROR: failed to build any 2-way joins

The following bug has been logged on the website:

Bug reference:      14111
Logged by:          Benedikt Grundmann
Email address:      bgrundmann@janestreet.com
PostgreSQL version: 9.2.16
Operating system:   Linux
Description:

>From all I know so far we have a query that used to work last week and after
upgrading from 9.2.6 to 9.2.16 this weekend, we get

ERROR: failed to build any 2-way joins

when trying to plan the same query (that is either run the query or even
just explain it).  The query is this:

explain

SELECT

  COALESCE(x11.x13, x10.x13) AS x13,

  SUM(ABS(COALESCE(x11.x14,0) - COALESCE(x10.x14,0))) AS x15,

  SUM(COALESCE(x11.x14,0) - COALESCE(x10.x14,0)) AS x16,

  SUM(ABS((COALESCE(x11.x14,0) - COALESCE(x10.x14,0)) * COALESCE(x2.x17,0)))
AS x18,
  SUM((COALESCE(x11.x14,0) - COALESCE(x10.x14,0)) * COALESCE(x2.x17,0)) AS
x25,
  COALESCE(SUM(ABS((COALESCE(x11.x14,0) - COALESCE(x10.x14,0)) *
COALESCE(x2.x17,0)))/(x4.x12 * x5.divisor),0)
  AS gross_pct,

  COALESCE(SUM((COALESCE(x11.x14,0) - COALESCE(x10.x14,0)) *
COALESCE(x2.x17,0))/(x4.x12 * x5.divisor),0) AS net_pct
FROM (

  SELECT x3.x22, x3.x13, x3.x23, x3.x14 FROM x3 WHERE x3.x22 = '4/25/2016'

) x11 FULL JOIN (

  SELECT x3.x22, x3.x13, x3.x23, x3.x14 FROM x3 WHERE x3.x22 = '4/22/2016'

) x10 ON x11.x13 = x10.x13 AND x11.x23 = x10.x23

LEFT JOIN x2 ON x2.x22 = (( SELECT x20('4/25/2016', (-1)) AS x20)) AND
x2.x24 = COALESCE(x11.x23,x10.x23)
LEFT JOIN x4 ON ('4/25/2016' = x4.x22 AND COALESCE(x11.x13,x10.x13) =
x4.x13)
LEFT JOIN x5 ON ('4/25/2016' = x5.x22 AND COALESCE(x11.x13,x10.x13) =
x5.x13)
WHERE (COALESCE(x11.x14, 0) - COALESCE(x10.x14, 0)) <> 0

GROUP BY COALESCE(x11.x13, x10.x13), (x4.x12 * x5.divisor)

ORDER BY COALESCE(SUM(ABS((COALESCE(x11.x14,0) - COALESCE(x10.x14,0)) *
COALESCE(x2.x17,0)))/(x4.x12 * x5.divisor),0)
DESC;

On Mon, Apr 25, 2016 at 5:03 AM, <bgrundmann@janestreet.com> wrote:

> The following bug has been logged on the website:
>
> Bug reference:      14111
> Logged by:          Benedikt Grundmann
> Email address:      bgrundmann@janestreet.com
> PostgreSQL version: 9.2.16
> Operating system:   Linux
> Description:
>
> >From all I know so far we have a query that used to work last week and
> after
> upgrading from 9.2.6 to 9.2.16 this weekend, we get
>
> ERROR: failed to build any 2-way joins
>
> when trying to plan the same query (that is either run the query or even
> just explain it).  The query is this:
>
>
=E2=80=8BThis sounds like the same as bug #
 14105

=E2=80=8B
http://www.postgresql.org/message-id/20160420194758.22924.80319@wrigleys.po=
stgresql.org
=E2=80=8B

=E2=80=8BThat one has been corrected and will go out in the next release.

http://git.postgresql.org/pg/commitdiff/d9742ac4636ec847f9fd54c9d690da
7402319063

Someone with source compilation can hopefully confirm that your specific
example is indeed fixed.

The combinations of left and full join lead me to suspect that indeed these
are caused by the same bug.

David J.
=E2=80=8B

thanks.  Any idea when that release will go out?

On Mon, Apr 25, 2016 at 5:14 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

> On Mon, Apr 25, 2016 at 5:03 AM, <bgrundmann@janestreet.com> wrote:
>
>> The following bug has been logged on the website:
>>
>> Bug reference:      14111
>> Logged by:          Benedikt Grundmann
>> Email address:      bgrundmann@janestreet.com
>> PostgreSQL version: 9.2.16
>> Operating system:   Linux
>> Description:
>>
>> >From all I know so far we have a query that used to work last week and
>> after
>> upgrading from 9.2.6 to 9.2.16 this weekend, we get
>>
>> ERROR: failed to build any 2-way joins
>>
>> when trying to plan the same query (that is either run the query or even
>> just explain it).  The query is this:
>>
>>
> =E2=80=8BThis sounds like the same as bug #
>  14105
>
> =E2=80=8B
> http://www.postgresql.org/message-id/20160420194758.22924.80319@wrigleys.=
postgresql.org
> =E2=80=8B
>
> =E2=80=8BThat one has been corrected and will go out in the next release.
>
> http://git.postgresql.org/pg/commitdiff/d9742ac4636ec847f9fd54c9d690da
> 7402319063
>
> Someone with source compilation can hopefully confirm that your specific
> example is indeed fixed.
>
> The combinations of left and full join lead me to suspect that indeed
> these are caused by the same bug.
>
> David J.
> =E2=80=8B
>
>
>

On Mon, Apr 25, 2016 at 11:53 PM, Benedikt Grundmann <
bgrundmann@janestreet.com> wrote:

> thanks.  Any idea when that release will go out?
>

=E2=80=8B9.6 Beta 1 and the next batch of minor releases are being targeted=
 for May
12th, 2016

http://www.postgresql.org/developer/roadmap/

The beta info is from other recent list threads.

David J.
=E2=80=8B