Re: BUG #13592: Optimizer throws out join constraint causing incorrect result

On Wed, Aug 26, 2015 at 3:50 PM, <kristoffer.gustafsson@yves-rocher.se>
wrote:

> The following bug has been logged on the website:
>
> Bug reference:      13592
> Logged by:          Kristoffer Gustafsson
> Email address:      kristoffer.gustafsson@yves-rocher.se
> PostgreSQL version: 9.4.3
> Operating system:   Linux
> Description:
>
> "A program produces the wrong output for any given input."
>
> I=E2=80=99m including a description of the encountered issue because I ca=
n=E2=80=99t
> provide
> you with a reproducible set of data. Both because of the data in question
> but also because the last step =E2=80=9Dcorrected=E2=80=9D the problem at=
 hand and removed
> the information showcasing the issue.
>
> =E2=80=8B[...]=E2=80=8B


>
> Except suddenly that resulted in division by zero. Division is done in
> three
> places, one fixed value and twice from TableC where each entry linked by
> TableB has values greater than 0. I.e. none of the supposedly included
> entries should be able to result in division by zero.
>
> But, it seems the optimizer decided to restructure the whole thing to ski=
p
> the join relationship and explode TableA with TableC before using TableB =
as
> a filter for the final aggregate.
>
> =E2=80=8B[...]
=E2=80=8B


> TableA and TableC have no direct relation to each other. Only the entries
> in
> TableC which can be bound via the bridge of TableB contain valid values f=
or
> TableA and the query as a whole. Meaning when the optimizer throws away t=
he
> relation and combines TableA with TableC it is using values which contain
> invalid values for the where criteria. TableC in this case contains
> multiple
> entries of 0 which in the calculated threshold criteria results in divisi=
on
> by zero error, but those entries should not be touched.
>
> During investigation when TableB was checked in pgAdmin it indicated it w=
as
> in need of vaccum/analyze after which the query which had been throwing
> division by zero was re-arranged by the optimizer to again work as intend=
ed
> by the original description. Regardless of TableB requiring vacuum/analyz=
e,
> having the optimizer basically throw out the specified relationship and
> then
> use the incorrectly gathered result in calculations seems rather incorrec=
t.
>

=E2=80=8BI do not follow but the fact that a division-by-zero exception occ=
urs in
some execution plans but not others is not a bug.=E2=80=8B

=E2=80=8BIf you do not want any rows where (c.C_VAL01_DP =3D 0) to be consi=
dered you
should alter the query so that instead of linking to everything in "c" you
only consider those rows having a non-zero C_VAL01_DP attribute.

At worse this is a performance-related issue that happens to manifest as a
division-by-zero.  however, your report is inadequate to consider that
particular dynamic.

David J.



=E2=80=8B