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

[firebird-automations]

Submitted by: @dyemanov

Is related to QA370

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

Commits: a58aaaf

[firebird-automations]

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

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 )

?

[firebird-automations]

Commented by: @dyemanov

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.

[firebird-automations]

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 2.5 Beta 1 [ 10251 ]

[firebird-automations]

Modified by: @pcisar

Link: This issue is related to QA370 [ QA370 ]

[firebird-automations]

Commented by: @pcisar

QA test added.

[firebird-automations]

Modified by: @pcisar

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

[firebird-automations]

Modified by: @pavel-zotov

QA Status: No test

[firebird-automations]

Modified by: @pavel-zotov

QA Status: No test => Done successfully