--- In firebird-support@yahoogroups.com, "martinknappe" <martin@...>
wrote:

>
> hello
>
> i'm experiencing some very (to my mind) odd behaviour with firebird
> 1.5 with the following query:
>
> select first 1 dic.id
> from dicentries dic
> inner join dskrptlink_dicentries dl on dic.id = dl.id_dicentry
> inner join dskrpts d on dl.id_dskrpt = d.id
> where d.asdskrpt = :asdskrptinput
> and dic.id >= :idin
> order by dic.id ascending
>
> now, i KNOW that with :asdskrptinput set to '^' and :idin to 1, the
> requested record id should be 1; however when i launch this query,
> ibexpert starts to work and seems like it won't ever stop so that i
> have to kill it
> what's even odder is that when i change the last but one line to
>
> "and dic.id = :idin" ('=' as opposed to '>=')
>
> I get the desired result (which is 1 as i knew before) immediately.
> The same when I comment out the whole line (the last but one i mean)
>
> is that a bug? or am I not seeing the wood for trees?

What is the plan for both queries?

To get this through iSQL type

SET PLAN;

then run each query.

If they differ, that is your reason.

There are many ways to run through this query. When you use '= :idin',
then Firebird will choose to start with dicentries and use the PK
index on this table. When you use >=, perhaps this index is less
useful for reducing the possible records, so it starts with the
dskrpts table.

If you change the first join to:

inner join dskrptlink_dicentries dl on dic.id+0 = dl.id_dicentry

You will probably find the plan changes, but this is not necessarily
the quickest. Your job as the programmer is to analyse the expected
data shapes of the various tables and formulate an expected optimal
plan. You may also need to know the weaknesses of the optimiser(s) you
deal with and massage the query so it avoids such decisions.

Adam