Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Wrong comparsion of DATE and TIMESTAMP if index is used [CORE3355] #3721

Closed
firebird-automations opened this issue Feb 21, 2011 · 16 comments
Closed

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @aafemt

Is related to QA288

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';

Commits: af76dc8 4ca0af4 edc4a4c b0e3c9f 167c026 6260b76

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.1.4 [ 10361 ]

Fix Version: 2.5.1 [ 10333 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

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'

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Implemented Vlad's suggestion.

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Great!

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA288 [ QA288 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pmakowski

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pmakowski

status: Resolved [ 5 ] => Reopened [ 4 ]

resolution: Fixed [ 1 ] =>

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pmakowski

Version: 2.1.4 [ 10361 ]

Fix Version: 2.1.5 [ 10420 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pmakowski

Fix Version: 2.1.4 [ 10361 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @pmakowski

status: Reopened [ 4 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pmakowski

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Closed [ 6 ] => Closed [ 6 ]

QA Status: Done successfully

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment