person wrote:
I have just installed the latest release of Firebird 2 Release Candidate 3 SuperServer version on Windows XP. It appears to me that if I now attempt to run a query with a not in clause eg.
Select * from a where fielda not in (Select fieldb from b)
that the query does not complete and the Firebird server starts utilizing 97-99% of CPU activity possibly for hours, until I manually kill it. It's a bug?
Dmitry Yemanov answers:
It's a bugfix, actually. NOT IN and ALL don't use indices anymore, as it may cause wrong results in some cases (we had three related bugreports). But as subqueries still executed per every master row, you'll have a very long execution time. Sad but true.
A workaround is to use a left outer join with IS NULL clause in the WHERE section.