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

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

Closed
firebird-automations opened this issue Oct 18, 2008 · 9 comments

Comments

@firebird-automations
Copy link
Collaborator

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
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 2.5 Beta 1 [ 10251 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA370 [ QA370 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pcisar

QA test added.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test => Done successfully

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants