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-issue-importer opened this issue Feb 21, 2011 · 16 comments
Closed

Comments

@firebird-issue-importer

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 21, 2011

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 21, 2011

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 21, 2011

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 21, 2011

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 21, 2011

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 22, 2011

Commented by: @dyemanov

Implemented Vlad's suggestion.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 22, 2011

Commented by: @hvlad

Great!

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 23, 2011

Modified by: @pcisar

Link: This issue is related to QA288 [ QA288 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 28, 2011

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 28, 2011

Modified by: @pmakowski

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

resolution: Fixed [ 1 ] =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 28, 2011

Commented by: @dyemanov

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 28, 2011

Modified by: @pmakowski

Version: 2.1.4 [ 10361 ]

Fix Version: 2.1.5 [ 10420 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 28, 2011

Modified by: @pmakowski

Fix Version: 2.1.4 [ 10361 ] =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 28, 2011

Modified by: @pmakowski

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

resolution: Fixed [ 1 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented May 16, 2012

Modified by: @pmakowski

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jul 15, 2015

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