Equality predicate distribution does not work for some complex queries
----------------------------------------------------------------------
Key: CORE-4365
URL: http://tracker.firebirdsql.org/browse/CORE-4365
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0 Alpha 2, 3.0 Alpha 1, 2.5.2 Update 1, 2.1.5 Update 1,
2.5.2, 2.1.5, 2.5.1, 2.1.4, 2.5.0, 2.1.3, 2.1.2, 2.1.1, 2.1.0
Reporter: Dmitry Yemanov
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))
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Learn Graph Databases - Download FREE O'Reilly Book
"Graph Databases" is the definitive new guide to graph databases and their
applications. Written by three acclaimed leaders in the field,
this first edition is now available. Download your free book today!
http://p.sf.net/sfu/13534_NeoTech
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
