In our production database the following statement crashes the query optimizer:
This statement is a reduced one, that is the real statement is much larger, but this seems to be the part that crashes the optimizer. When we select slightly different date values, the query can be optimized with the following execution plan:
If we replace the left outer join by inner join, the statement works. If we drop the subselect and replace it with something static, it works, too. If we remove the "where" clause and write this condition into the "on" clause (which of course changes the statement), it works, too.
In the log file of the server we find the following error report:
Now I hope, that this is enough information for you to find out, what is happening here. If not, please feel free to ask for anything missing.