Query optimization - Please help

This is a rare internal error (the optimizer contains logic to avoid stack problems in the most common cases). Related errors can occur as a result of having too many EXISTS clauses at the same level, for example. In this case it is likely you have too many nested subqueries, UNIONs or something of that nature.

In any case, the error message is pretty specific about the problem and what you should do about it. Encountering this sort of error is usually a pretty reliable sign that your query is too complex - probably for humans as well as optimizers. Find a way to break the logic into simpler stages.