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

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

> The following bug has been logged on the website:
>
> Bug reference: 13592
> Logged by: Kristoffer Gustafsson
> Email address: kristoffer(dot)gustafsson(at)yves-rocher(dot)se
> PostgreSQL version: 9.4.3
> Operating system: Linux
> Description:
>
> "A program produces the wrong output for any given input."
>
> I’m including a description of the encountered issue because I can’t
> provide
> you with a reproducible set of data. Both because of the data in question
> but also because the last step ”corrected” the problem at hand and removed
> the information showcasing the issue.
>
> ​[...]​

>
> 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 skip
> the join relationship and explode TableA with TableC before using TableB as
> a filter for the final aggregate.
>
> ​[...]
​

> 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 for
> TableA and the query as a whole. Meaning when the optimizer throws away the
> 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 division
> by zero error, but those entries should not be touched.
>
> During investigation when TableB was checked in pgAdmin it indicated it was
> 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 intended
> by the original description. Regardless of TableB requiring vacuum/analyze,
> having the optimizer basically throw out the specified relationship and
> then
> use the incorrectly gathered result in calculations seems rather incorrect.
>

​I do not follow but the fact that a division-by-zero exception occurs in
some execution plans but not others is not a bug.​

​If you do not want any rows where (c.C_VAL01_DP = 0) to be considered 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.

​