Issue Details (XML | Word | Printable)

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

If you were logged in you would be able to see more operations.
Firebird Core

Index is not used for some date/time expressions in dialect 1

Created: 01/Apr/08 04:26 AM   Updated: 25/Apr/13 01:29 PM
Component/s: Engine
Affects Version/s: 2.1 RC1, 2.1 RC2
Fix Version/s: 2.1.1, 2.5 Beta 1

Time Tracking:
Not Specified

Environment: Any
Issue Links:
Relate
 

Planning Status: Included to release


 Description  « Hide
This is a regression introduced in v2.1 and it's caused by a more strict checking for datatypes being compatible for an index scan. In dialect 1, expression ('now' - <const>) has the datatype "double precision" which is now considered incompatible with a timestamp. A workaround is to use CAST or the explicit datatype prefix before 'now'. This issue affects other date/time constants ('today', etc) as well.

In case we'll intend to fix that issue, a possible solution would be to check not only the descriptor datatype but also node flags (nod_date / nod_double). This should allow better compatibility with the legacy dialect.

Test case (database dialect 1):

create table t (col timestamp) ;
create index it on t (col) ;
commit ;

select * from t where col > timestamp 'now' - 7 ;
-- PLAN (T INDEX (IT))
-- OK

select * from t where col > 'now' - 7 ;
-- PLAN (T NATURAL)
-- PROBLEM


 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Philippe Makowski added a comment - 03/Dec/08 07:47 AM
Q/A test ok

Philippe Makowski added a comment - 21/Jun/11 01:05 PM
Seems that we have a regression here with 2.5.1.26308
select * from t where col > 'now' - 7 ;
-- PLAN (T NATURAL)
-- PROBLEM

Dmitry Yemanov added a comment - 21/Jun/11 01:23 PM
Yes, I know. It was introduced while implementing CORE-3355. To be researched really soon.

Dmitry Yemanov added a comment - 21/Jul/11 10:20 AM
Regression should be fixed now.

Pavel Cisar added a comment - 25/Apr/13 01:29 PM
Test added.