Search code examples
sqlfirebirdfirebird2.1

SQL Firebird conditional where not using index

I am using Firebird SQL version 2.1.

I have a sql query string in which parameters are passed. If a value is passed is not in my hand, but if it is passed, it is the right type (Timestamp or else..)

Now my query looks like this:

select r.* 
from TableA 
where r.ACTION_DATE >= iif('2019-10-09 00:00:00' is null or '2019-10-09 00:00:00' = '', r.ACTION_DATE, '2019-10-09 00:00:00')

In words, when a value is passed I want to load all records that have an action date greater then '2019-10-09 00:00:00', else get all records.

I have an Index on column ACTION_DATE. For the query I mentioned the index is not applied!

For the same query without the condition the index is applied:

select r.* 
from TableA 
where r.ACTION_DATE >= '2019-10-09 00:00:00'
Solution

  • Expressions on columns generally kill the use of indexes. Some databases can handle an explicit OR:

    select r.* 
from TableA 
where r.ACTION_DATE >= '2019-10-09 00:00:00' OR
      '2019-10-09 00:00:00' IS NULL;

    Otherwise, UNION ALL can handle it:

    select r.* 
from TableA r
where r.ACTION_DATE >= '2019-10-09 00:00:00' 
union all
select r.*
from TableA r
where '2019-10-09 00:00:00' IS NULL;

    Note that the first subquery returns no rows if the value is NULL so there is no danger of duplicate rows.