Issue Details (XML | Word | Printable)

Key: CORE-1607
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Dmitry Yemanov
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Correlated subquery is optimized badly if it depends on the union stream

Created: 21/Nov/07 02:29 AM   Updated: 22/Sep/11 12:39 AM
Component/s: Engine
Affects Version/s: 2.0.0, 2.0.1, 2.1 Alpha 1, 2.1 Beta 1, 2.0.2, 2.0.3, 2.1 Beta 2
Fix Version/s: 2.5 Beta 1, 2.1.3

Time Tracking:
Not Specified

Issue Links:
Duplicate
 
Relate
 


 Description  « Hide
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)


 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 21/Dec/08 03:05 AM
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)

Philippe Makowski added a comment - 11/Mar/11 08:26 AM
QA test made