Formally, this is a performance regression introduced while fixing
. 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:
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.