At 12:29 AM 23/04/2009, you wrote:

>I have a query that behaves differently in FB 2.0 vs. 1.5. The
>statement is:
>
>select h.h_ronumber from header h
>where h.h_header_id not in (select a.header_id from account_totals a)
>
>The FB 2.0 plan is: PLAN (A NATURAL)PLAN (H NATURAL)
>The FB 1.5 plan is: PLAN (A INDEX (IDX_ACCOUNT_TOTALS))PLAN (H NATURAL)
>
>FB 1.5 takes 0.047 seconds to run and FB 2.0 takes 21 minutes to run.
>It does return the exact same data.
>
>Of course the statement is much better written as:
>
>select h.h_ronumber from header h
>where not exists(select 1 from account_totals a where a.header_id =
>h.h_header_id)
>
>Is this an intentional change in the optimizer?

Yes. It is explained in the Compatibility chapter of the release notes:

"The following changes should be noted as possible sources of performance loss:
Existence Predicates NOT IN and ALL May Be Slow
Firebird and, before that, InterBase, have produced incorrect results for the logical existence predicates ALL and NOT IN for many years. That problem has been corrected in Firebird 2.0, but the change means that indexes on the inner tables cannot be used and performance may be slow compared to the same query's performance in V.1.5. �Inner tables� are the tables used in the subquery argument inside an ALL or NOT IN expression.
Note
NOT EXISTS is approximately equivalent to NOT IN and will allow Firebird to use indexes."

> My concern is only that upgrading to FB 2.0 might expose some other

'newbie' SQL code in the application that was working fine, and now won't be.

It's a fully justified concern. For example, if your legacy code is prone to "sloppy" join and subquery syntax, you're likely to bump into situations where your old queries threw warnings under 1.5 (which developers usually ignore) and now throw exceptions.

New major versions have a Compatibility section which one ignores at ones peril. (For Fb 2.1.x, the compatibility section is in the separated Installation and Compatibility document.)

./heLen