--- In firebird-support@yahoogroups.com, "Helmut Hartl"
<helmut.hartl@f...> wrote:

> > BTW, when anwsering is something bug in some version or not and what
> > about future version, it is very convenient to know version which you
> > use :)
>
> Maybe reading the Subject could have helped:-))
> ->> Possible BUG in PLAN Optimizer ? FB 1.5.4290 <<-

Yess! Got it! :))) Seems I should visit oculist and buy new
spectacles :)

> Try this one and you should see what i mean:
>
> INSERT INTO T1 (ID,F1,F2) VALUES (1,NULL,NULL);
>
> -> PLAN: PLAN JOIN (B NATURAL,A INDEX (T1_IDX1,PK_T1),C INDEX (FK_T3))

Hmm, really. BTW, I have examples when optimizer chaoices wrong
order of the tables in inner join without conditions on null too, but
it is rather complex queries and many composite indices on the tables.
Last time when I reported this to FB Release Coordinator privately he
said that seems he at least found the reason but did'nt said when it
will be fixed.

> I think you can imagine what this means if it was used with much more
> records.

Not only imagine but experience regularly. Unfortunately explicit
planning is my main occupation, how I drived nyself into a corner is a
long story, but... I too implement dynamically created Where and Order
By and finished on explicitly planning queries to optimize joining
don't bothering about filters. In most cases it works and shows
satisfactory performance, many filter conditions anyway are layed on
columns participated in join conditions, if not - well, natural filter
on well designed join works not too bad if we don't speak about
millions of rows. Can't say I recommend to all to go this way, but if
another ways are closed by some reasons, this can help. BTW, I believe
you know what you do and need, but I prefer to use nulls in timestamps
only, where they shows not happened yet events, this significantly
simplifies life.

> maybe, you have another good idea on the initial Problem.

Set's solution on ruling order of joining depriveing of
opportunities to use particular index from optimizer by introdicing
"dummy" expression on the column is good, perhaps the best. But this
is softened kind of explicit planning too. Let's wait further
optimizer improvement, Arno Brinkman already made and continues to
meake a great job on it.

Best regards,
Alexander.