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
Comments
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Modified by: @dyemanovstatus: 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 ] |
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. |
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 I.e. val < timestamp'1998-01-04 12:00:00.0000' could be evaluated using /*indexed*/ val <= date '1998-01-04' |
Commented by: @hvlad More general : date_field OP ts_value could be transformed into a) OP is ">" or ">=" b) OP is "<" or "<=" c) OP is "=" d) OP is "<>" - this is can't be evaluated using index anyway, so expression shouldn't be changed |
Commented by: @dyemanov Implemented Vlad's suggestion. |
Commented by: @hvlad Great! |
Commented by: @pmakowski seems that it is not fixed in 2.1.4
============ SQL> select count(*) from tdate where val < timestamp'1998-01-04 12:00:00.0000';
============ SQL> drop index tdateix1;
============ SQL> select count(*) from tdate where val < timestamp'1998-01-04 12:00:00.0000';
============ |
Commented by: @dyemanov The ticket was resolved with incorrect version, the fix has been committed into v2.1.5. |
Modified by: @pmakowskiFix Version: 2.1.4 [ 10361 ] => |
Modified by: @pmakowskistatus: Resolved [ 5 ] => Closed [ 6 ] |
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
The text was updated successfully, but these errors were encountered: