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-issue-importer opened this issue Nov 21, 2007 · 14 comments

Comments

@firebird-issue-importer

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 28, 2008

Modified by: @pcisar

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 31, 2008

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 19, 2008

Modified by: @dyemanov

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 21, 2008

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 21, 2008

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 21, 2008

Modified by: @dyemanov

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 21, 2008

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 2.5 Beta 1 [ 10251 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 21, 2008

Modified by: @dyemanov

Fix Version: 2.1.3 [ 10302 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 20, 2009

Modified by: @dyemanov

Link: This issue is duplicated by CORE2327 [ CORE2327 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 16, 2009

Modified by: @pcisar

Link: This issue is related to QA231 [ QA231 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Mar 11, 2011

Commented by: @pmakowski

QA test made

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Mar 11, 2011

Modified by: @pmakowski

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 19, 2016

Modified by: @pavel-zotov

QA Status: No test

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 19, 2016

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