Nested loop plan is chosen instead of the sort merge for joining independent
streams using keys of different types
------------------------------------------------------------------------------------------------------------------
Key: CORE-3553
URL: http://tracker.firebirdsql.org/browse/CORE-3553
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 2.1.4
Reporter: Dmitry Yemanov
Formally, this is a performance regression introduced while fixing CORE-1274
and CORE-1752. Now, instead of sorting/merging the streams which delivered
wrong results, a safer nested loop join method is chosen. Unfortunately, it's
also much slower if the streams are independent. As it was an intended change,
it doesn't count as a bug per se, more as a "lesser evil". But better solutions
are possible.
Artificial test case:
select count(*)
from rdb$database d1 join rdb$database d2
on cast(d1.rdb$relation_id as char(10)) = cast(d2.rdb$relation_id as char(20))
-- Reported (bad) plan:
-- PLAN JOIN (D1 NATURAL, D2 NATURAL)
-- Expected (good) plan:
-- PLAN MERGE (SORT (D2 NATURAL), SORT (D1 NATURAL))
All branches are affected, but v2.1.4 is the only released version which has
the issue. The workaround is to explicitly cast the join keys to the same data
type.
--
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
------------------------------------------------------------------------------
AppSumo Presents a FREE Video for the SourceForge Community by Eric
Ries, the creator of the Lean Startup Methodology on "Lean Startup
Secrets Revealed." This video shows you how to validate your ideas,
optimize your ideas and identify your business strategy.
http://p.sf.net/sfu/appsumosfdev2dev
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
