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

DJ:
​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.

KG:
Sorry, I can't provide more info/working sample. The analyze fixed the evaluation order.

Should I interpret your response that the inner joins are of no actual value in determining the result sets used by the optimizer?

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.
C is only valid as part of B.
Selecting the full set of A and C without the limit from B includes all entries from C even when only the subset linked by B should be considered as valid according to FROM.

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

"c.C_VAL01_DP = 0" should per definition of the FROM never be included since none of the valid entries specified by the relationship has that value.

Doing the evaluation in this other order is one way of generating the result, but it is ignoring the relationship expressed by the FROM section and using it as any other filter.

But if this is working as intended so be it, will just have to cover for it with additional criteria in queries.

​
________________________________

Detta meddelande och alla bilagor är konfidentiella och avsedda för den namngivna mottagaren. Om du har fått detta meddelande av misstag, vänligen meddela omedelbart avsändaren och ta sedan bort meddelandet. All otillåten modifiering, användning eller spridning är förbjuden. Avsändaren är inte ansvarig för detta meddelande om det har ändrats, förfalskats, redigerats, smittat av ett virus eller spridits utan tillstånd. Skriv inte ut detta meddelande om det inte är nödvändigt, tänk på miljön.

This message and any attachments are confidential and intended for the named addressee(s) only. If you have received this message in error, please notify immediately the sender, then delete the message. Any unauthorized modification, edition, use or dissemination is prohibited. The sender is not liable for this message if it has been modified, altered, falsified, infected by a virus or even edited or disseminated without authorization. Do not print this message unless it is necessary, consider the environment.