> Yes, this does work. We were rather hoping that it would work as is
> i.e. if postgresql and SQL Server can do it, why can't Firebird? Is
> there a problem with the optimiser.

Well, I've twisted my SQL in ways similar to this as long as I've used
Firebird/InterBase, so yes, the optimiser does not always select the
best plan. Though we do not want it to do that either, at the
beginning I've heard that InterBase always chose the best plan, but
figuring out which plan to use took hours in some cases.

Arno has done some good things to the optimizer, and hopefully he will
improve it even more in the future. For the time being, we can just
make sure we are careful with which indexes we implement and twist our
SQL when neccessary.

Set