Equality predicate distribution does not work for some complex queries [CORE4365] #4687
Labels
affect-version: 2.1.0
affect-version: 2.1.1
affect-version: 2.1.2
affect-version: 2.1.3
affect-version: 2.1.4
affect-version: 2.1.5 Update 1
affect-version: 2.1.5
affect-version: 2.5.0
affect-version: 2.5.1
affect-version: 2.5.2 Update 1
affect-version: 2.5.2
affect-version: 3.0 Alpha 1
affect-version: 3.0 Alpha 2
component: engine
fix-version: 3.0 Beta 1
priority: major
qa: done successfully
type: bug
Submitted by: @dmitry-starodubov
Is related to QA595
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 http://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 "http://rf.id = r.rdb$relation_id", the optimizer derives "http://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 http://rf.id = r.rdb$relation_id
left join rdb$procedures p on p.rdb$procedure_id = http://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))
Commits: 383b484 FirebirdSQL/fbt-repository@7d3dd47
The text was updated successfully, but these errors were encountered: