Issue Details (XML | Word | Printable)

Key: CORE-3355
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Dimitry Sibiryakov
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
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
 

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: Ascending order - Click to sort in descending order
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.

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'

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

Dmitry Yemanov added a comment - 22/Feb/11 12:30 PM
Implemented Vlad's suggestion.

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

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


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.