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

Kristoffer Gustafsson <kristoffer.gustafsson@yves-rocher.se> writes:
> The FROM has set A which is joined with set B which links set C.
> There is a path with keys going from A to B to C.
> There is no link from A to C.

Actually, there is: the WHERE clause involving A and C is itself a join
clause, since it allows filtering out some pairs of A and C rows, albeit
only after performing a nestloop join.  It seems somewhat unlikely that
the optimizer would choose that approach in preference to equijoins, but
since you've not shown us any concrete details, it can't be ruled out.
For example, if both A and C are small and both could usefully be used in
an indexscan on a large B table, joining A to C first would make perfect
sense.

> Of course you can write the WHERE criteria to not contain expressions/calculations and duplicate the constraint
expressedby the FROM section, but doesn't that defeat the purpose of declaring the joins in FROM? 

There is *no* semantic difference between writing a join clause in WHERE
and writing it in an (inner) JOIN/ON clause.  There is certainly no
promise about the execution order.  See
http://www.postgresql.org/docs/9.4/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL

Personally I'd dodge the whole problem by reformulating the WHERE to avoid
division, that is

    (
        ((a.A_VAL01_DP + a.A_VAL02_DP) > (:THRESHOLD_01 * c.C_VAL01_DP))
    or
        ((a.A_VAL03_DP - a.A_VAL02_DP) > (:THRESHOLD_02 * c.C_VAL01_DP))
    )

If you can't fix it in that sort of way, the traditional approach to
forcing the join order in Postgres is to put the desired innermost join
in a sub-SELECT with OFFSET 0, which works as an optimization fence.
(The optimizer can't push joins or WHERE clauses past a LIMIT/OFFSET
for fear of changing the set of rows returned.)  But JOIN/ON is most
certainly not an optimization fence.

            regards, tom lane