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

Correlated subquery is optimized badly if it depends on the union stream [CORE1607] #2028

Closed
firebird-automations opened this issue Nov 21, 2007 · 14 comments

Comments

@firebird-automations
Copy link
Collaborator

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 13480 ] => Firebird [ 14270 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

One more indirectly related test case (which internally involves an implicit union):

select 1
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 NATURAL)

select 1
from ( select rdb$relation_name, ( select 1 from rdb$database ) as c from rdb$relations ) r
where exists ( select * from rdb$relation_fields f where f.rdb$relation_name = r.rdb$relation_name )
--PLAN (R RDB$DATABASE NATURAL)
--PLAN (F NATURAL)
--PLAN (R RDB$RELATIONS NATURAL)

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

description: Test case (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 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)

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: In Progress [ 3 ] => Open [ 1 ]

@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: @dyemanov

Fix Version: 2.1.3 [ 10302 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is duplicated by CORE2327 [ CORE2327 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA231 [ QA231 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pmakowski

QA test made

@firebird-automations
Copy link
Collaborator Author

Modified by: @pmakowski

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