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
Correlated subquery is optimized badly if it depends on the union stream [CORE1607] #2028
Comments
Modified by: @pcisarWorkflow: jira [ 13480 ] => Firebird [ 14270 ] |
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Modified by: @dyemanovstatus: Open [ 1 ] => In Progress [ 3 ] |
Commented by: @dyemanov One more indirectly related test case (which internally involves an implicit union): select 1 select 1 |
Modified by: @dyemanovdescription: Test case (with union): select ( select first 1 r.rdb$relation_name PLAN (R NATURAL) But (without union): select ( select first 1 r.rdb$relation_name PLAN (R INDEX (RDB$INDEX_1)) => Test case 1 (with union): select ( select first 1 r.rdb$relation_name PLAN (R NATURAL) But (without union): select ( select first 1 r.rdb$relation_name PLAN (R INDEX (RDB$INDEX_1)) Test case 2 (with union): select 1 PLAN (F NATURAL) But (without union): select 1 PLAN (F INDEX (RDB$INDEX_4)) |
Modified by: @dyemanovstatus: In Progress [ 3 ] => Open [ 1 ] |
Modified by: @dyemanovstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 2.5 Beta 1 [ 10251 ] |
Modified by: @dyemanovFix Version: 2.1.3 [ 10302 ] |
Commented by: @pmakowski QA test made |
Modified by: @pmakowskistatus: Resolved [ 5 ] => Closed [ 6 ] |
Modified by: @pavel-zotovQA Status: No test |
Modified by: @pavel-zotovQA Status: No test => Done successfully |
Submitted by: @dyemanov
Is duplicated by CORE2327
Is related to QA231
Test case 1 (with union):
select ( select first 1 r.rdb$relation_name
from rdb$relations r
where r.rdb$relation_id = d.rdb$relation_id - 1 )
from (
select * from rdb$database
union all
select * from rdb$database
) d
PLAN (R NATURAL)
-- R is scanned naturally
PLAN (D RDB$DATABASE NATURAL)
PLAN (D RDB$DATABASE NATURAL)
But (without union):
select ( select first 1 r.rdb$relation_name
from rdb$relations r
where r.rdb$relation_id = d.rdb$relation_id - 1 )
from (
select * from rdb$database
) d
PLAN (R INDEX (RDB$INDEX_1))
-- R is read through the index
PLAN (D RDB$DATABASE NATURAL)
Test case 2 (with union):
select 1
from (
select * from rdb$relations
union all
select * from rdb$relations
) r
where exists ( select * from rdb$relation_fields f where f.rdb$relation_name = r.rdb$relation_name )
PLAN (F NATURAL)
PLAN (R RDB$RELATIONS NATURAL)
PLAN (R RDB$RELATIONS NATURAL)
But (without union):
select 1
from ( select * from rdb$relations ) r
where exists ( select * from rdb$relation_fields f where f.rdb$relation_name = r.rdb$relation_name )
PLAN (F INDEX (RDB$INDEX_4))
PLAN (R RDB$RELATIONS NATURAL)
Commits: d5bf9a7 05299f6
The text was updated successfully, but these errors were encountered: