>I have this simple select
>select sum(apos.f_gewicht) as Gewicht
>from t_apos apos
>where COALESCE(apos.f_dat_anlieferung, apos.f_dat_erstellung)
>between '2013-01-15' and '2013-01-16'
>
>which does an natural scan on table t_apos although an index is on
>both fields.
>
>select sum(apos.f_gewicht) as Gewicht
>from t_apos apos
>where apos.f_dat_anlieferung between '2013-01-15' and '2013-01-16'
>
>and
>
>select sum(apos.f_gewicht) as Gewicht
>from t_apos apos
>where apos.f_dat_erstellung between '2013-01-15' and '2013-01-16'
>
>both uses the index .
>Why is the index not used when coalesce is used?
>I use Firebird 2.5.2 64bit on Windows 7.

That's three very different queries, Carsten! A fairer comparison would have been:

select sum(apos.f_gewicht) as Gewicht
from t_apos apos
where apos.f_dat_anlieferung between '2013-01-15' and '2013-01-16'
or apos.f_dat_erstellung between '2013-01-15' and '2013-01-16'

Actually, I don't know whether Firebird treats coalesce as if OR had been used. In some cases, I suspect it in theory could, but COALESCE(a, b) IS NULL would have to translate to AND rather than OR. Don't know whether this is implemented, or even if it would be beneficial if implemented - whenever I use coalesce in a WHERE clause, it is very rarely referring to different fields within the same tuple.

HTH,
Set