[#CORE-3355] Wrong comparsion of DATE and TIMESTAMP if index is used

Firebird Core

Wrong comparsion of DATE and TIMESTAMP if index is used

Created: 21/Feb/11 10:57 AM Updated: 16/May/12 11:38 AM

Component/s:

Engine

Affects Version/s:

2.1.3, 3.0 Initial, 2.0.6, 2.5.0, 2.1.4

Fix Version/s:

2.5.1, 2.1.5, 3.0 Alpha 1

Time Tracking:

Not Specified

Environment:

Any

Issue Links:

Relate

This issue is related to:

~~QA-288~~ Test for CORE-3355

Planning Status:

Unspecified

Description

« Hide

Folowing script gives result 1 and 4 while it should be 1 and 5:

create table tdate (id integer not null primary key, val date);
create index tdateix1 on tdate (val);
commit;
insert into tdate values (0, '1997-12-31');
insert into tdate values (1, '1998-01-01');
insert into tdate values (2, '1998-01-02');
insert into tdate values (3, '1998-01-03');
insert into tdate values (4, '1998-01-04');
insert into tdate values (5, '1998-01-05');
commit;
select count(*) from tdate where val >= timestamp'1998-01-04 12:00:00.0000';
select count(*) from tdate where val < timestamp'1998-01-04 12:00:00.0000';

All

Comments

Work Log

Change History

Version Control

Subversion Commits

Sort Order:

[ Permalink | « Hide ]

Dmitry Yemanov added a comment - 21/Feb/11 11:26 AM

The only solution is to disable index usage in this case. v1.5 works only by coincidence -- it doesn't support strong inequalities in index lookups, thus executing "<" as "<=" and later filtering out the redundant row.

[ Permalink | « Hide ]

Vlad Khorsun added a comment - 21/Feb/11 01:18 PM

I don't agree that we should disable index here. Instead we could create search key using truncated constant value and correct comparison predicate. Original predicate will be used at filter stage, after index scan.

I.e. val >= timestamp'1998-01-04 12:00:00.0000' could be evaluated using

/*indexed*/ val >= date '1998-01-04'
and
/*non-indexed*/ val >= timestamp'1998-01-04 12:00:00.0000'

and

I.e. val < timestamp'1998-01-04 12:00:00.0000' could be evaluated using

/*indexed*/ val <= date '1998-01-04'
and
/*non-indexed*/ val < timestamp'1998-01-04 12:00:00.0000'

[ Permalink | « Hide ]

Vlad Khorsun added a comment - 21/Feb/11 01:24 PM

More general :

date_field OP ts_value

could be transformed into

a) OP is ">" or ">="
date_field >= CAST(ts_value as DATE) AND date_field OP ts_value

b) OP is "<" or "<="
date_field <= CAST(ts_value as DATE) AND date_field OP ts_value

c) OP is "="
date_field = CAST(ts_value as DATE) AND date_field OP ts_value

d) OP is "<>" - this is can't be evaluated using index anyway, so expression shouldn't be changed

[ Permalink | « Hide ]

Dmitry Yemanov added a comment - 22/Feb/11 12:30 PM

Implemented Vlad's suggestion.

[ Permalink | « Hide ]

Vlad Khorsun added a comment - 22/Feb/11 02:14 PM

Great!

[ Permalink | « Hide ]

Philippe Makowski added a comment - 28/Apr/11 03:37 PM - edited

seems that it is not fixed in 2.1.4
see :
SQL> create table tdate (id integer not null primary key, val date);
SQL> create index tdateix1 on tdate (val);
SQL> commit;
SQL> insert into tdate values (0, '1997-12-31');
SQL> insert into tdate values (1, '1998-01-01');
SQL> insert into tdate values (2, '1998-01-02');
SQL> insert into tdate values (3, '1998-01-03');
SQL> insert into tdate values (4, '1998-01-04');
SQL> insert into tdate values (5, '1998-01-05');
SQL> commit;
SQL> select count(*) from tdate where val >= timestamp'1998-01-04 12:00:00.0000';

       COUNT
============
           1

SQL> select count(*) from tdate where val < timestamp'1998-01-04 12:00:00.0000';

       COUNT
============
           4

SQL> drop index tdateix1;
SQL> commit;
SQL> select count(*) from tdate where val >= timestamp'1998-01-04 12:00:00.0000';

       COUNT
============
           1

SQL> select count(*) from tdate where val < timestamp'1998-01-04 12:00:00.0000';

       COUNT
============
           5

[ Permalink | « Hide ]

Dmitry Yemanov added a comment - 28/Apr/11 03:48 PM

The ticket was resolved with incorrect version, the fix has been committed into v2.1.5.