On Tue, Jun 21, 2011 at 4:17 AM, Knut Anders Hatlen <knut.hatlen@oracle.com> wrote:
Masood Mortazavi <masoodmortazavi@gmail.com> writes:

> When a Derby "select" executes in the case when "where" is followed by
> multiple conjoined AND clauses, will all the AND clauses be evaluated
> even if the first one is false, in the scan?

Derby will stop evaluating the clauses once one evaluates to false. Note
however that the optimizer may reorder the AND clauses for efficiency,
so if you code your application to rely on the short-circuiting (for
example if the AND clauses call functions with side effects), the
results may not be what you expect.

Thanks. That's interesting.

I had hoped that if the AND clause involves matching (through "IN") with columns from two other tables, and if I put the smaller matching table at the start, the optimizer will keep that order. If optimizer does not make any provisions based on the size but only decides ordering based on syntactic complexity of each of the clauses, it may get it wrong, in my particular case.

- Masood