Issue Details (XML | Word | Printable)

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

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

Equality predicate distribution does not work for some complex queries

Created: 15/Mar/14 04:03 PM   Updated: 23/Sep/15 12:40 PM
Component/s: Engine
Affects Version/s: 2.1.0, 2.1.1, 2.1.2, 2.1.3, 2.5.0, 2.1.4, 2.5.1, 2.1.5, 2.5.2, 2.1.5 Update 1, 2.5.2 Update 1, 3.0 Alpha 1, 3.0 Alpha 2
Fix Version/s: 3.0 Beta 1

Issue Links:
Relate
 

QA Status: Done successfully


 Description  « Hide
Artificial test case:

set planonly;

select * from (
  select rdb$relation_id as id
  from rdb$relations r
    join (
      select rdb$generator_id as id from rdb$generators
      union all
      select rdb$generator_id as id from rdb$generators
    ) rf on rf.id = r.rdb$relation_id
) where id = 1

PLAN JOIN (RF RDB$GENERATORS INDEX (RDB$INDEX_46), RF RDB$GENERATORS INDEX (RDB$INDEX_46), R INDEX (RDB$INDEX_1))

Note that "id = 1" means "r.rdb$relation_id = 1", given both this boolean and "rf.id = r.rdb$relation_id", the optimizer derives "rf.id = 1" that can be pushed inside the union to become "rdb$generator_id = 1" thus using indices for filtering. So far so good.

select * from (
  select rdb$relation_id as id
  from rdb$relations r
    join (
      select rdb$generator_id as id from rdb$generators
      union all
      select rdb$generator_id as id from rdb$generators
    ) rf on rf.id = r.rdb$relation_id
    left join rdb$procedures p on p.rdb$procedure_id = rf.id
) where id = 1

PLAN JOIN (JOIN (RF RDB$GENERATORS NATURAL, RF RDB$GENERATORS NATURAL, R INDEX (RDB$INDEX_1)), P INDEX (RDB$INDEX_22))

And here we see that an extra unrelated join completely breaks equality distribution so that no new boolean is injected and no indices are used inside the union.

Expected plan:
PLAN JOIN (JOIN (RF RDB$GENERATORS INDEX (RDB$INDEX_46), RF RDB$GENERATORS INDEX (RDB$INDEX_46), R INDEX (RDB$INDEX_1)), P INDEX (RDB$INDEX_22))


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Pavel Cisar added a comment - 23/Sep/15 12:40 PM
Test created.