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