Issue Details (XML | Word | Printable)

Key: CORE-2132
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

Indexed retrieval cannot be chosen if a stored procedure is used inside the comparison predicate

Created: 18/Oct/08 01:19 PM   Updated: 27/Apr/11 11:06 AM
Component/s: Engine
Affects Version/s: None
Fix Version/s: 2.5 Beta 1

Time Tracking:
Not Specified

Environment: Any
Issue Links:
Relate
 

Planning Status: Unspecified


 Description  « Hide
Test case:

create table t1 (col int primary key);
create procedure p1 returns (ret int) as begin ret = 0; suspend; end;
create procedure p2 (prm int) returns (ret int) as begin ret = prm; suspend; end;
commit;
insert into t1 (col) values (0);
commit;

Below comments indicate the expected result:

select * from t1 where col = 0;
-- index
select * from t1 where col = col;
-- natural
select * from t1 where col = ( select 0 from rdb$database );
-- index
select * from t1 where col = ( select col from rdb$database );
-- natural
select * from t1 where col = ( select 0 from p1 );
-- index (currently natural)
select * from t1 where col = ( select ret from p1 );
-- index (currently natural)
select * from t1 where col = ( select col from p1 );
-- natural
select * from t1 where col = ( select 0 from p2(0) );
-- index (currently natural)
select * from t1 where col = ( select ret from p2(0) );
-- index (currently natural)
select * from t1 where col = ( select col from p2(0) );
-- natural
select * from t1 where col = ( select 0 from p2(col) );
-- natural
select * from t1 where col = ( select ret from p2(col) );
-- natural
select * from t1 where col = ( select col from p2(col) );
-- natural


 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Sean Leyne added a comment - 18/Oct/08 01:53 PM
Dmitry,

Shouldn't this statement:

  select * from t1 where col = ( select 0 from p2(col) )

also use an index, just this statement does:

  select * from t1 where col = ( select 0 from p1 )

?

Dmitry Yemanov added a comment - 18/Oct/08 01:58 PM
Nope, it shouldn't. Please note that we pass "T1.COL" to the procedure input. It means that T1 should be read before procedure execution, while an index scan implies that a procedure is executed before the table access.

Pavel Cisar added a comment - 27/Apr/11 11:06 AM
QA test added.