Set,

> With 31 aliased tables in the main select + 30 further references in NOT
> EXISTS clauses (or something like that), I wanted to check the plan
> before execution to avoid things taking days to finish. However, the
> plan that appeared in IB_SQL and Database Workbench wasn't complete -
> some tables (more specifically, those that were part of my main select,
> 24 'subplans' seemed to be present) simply weren't in the plan at all!
> Removing the repeated tables but for the first two occurences, made the
> main select reappear in the plan.
>
> As I write, I'm executing the real query (with all tables) and have no
> reason to believe there is anything wrong with the result or the way
> Firebird executes the query. But I'm surprised that the reported plan
> was incomplete. I just checked through isql, and that tool didn't report
> the full plan either.

Welcome to one of the old-time bugs in Firebird and InterBase.

Not reporting the full plan.

This can either result in total garbage being returned, a crashing server
(used to be, cannot remember if this was fixed) or, as you can see,
an incomplete plan.

There's a certain length to the plan buffer and apparently, when plans
get too complex/long (as in: when they are actually useful ;) ), you
won't see any.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com