Nested loop plan is chosen instead of the sort merge for joining independent streams using keys of different types [CORE3553] #3909
Assignees
Comments
|
Commented by: @dyemanov @ Volker: the workaround in your case is: select o.objekt, o.beschreibung, o.modultext, o.thema, o.feldtyp |
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Modified by: @dyemanovstatus: Open [ 1 ] => In Progress [ 3 ] |
Modified by: @dyemanovreporter: Dmitry Yemanov [ dimitr ] => Volker Rehn [ vr2@s18 ] |
Modified by: @dyemanovstatus: In Progress [ 3 ] => Open [ 1 ] |
Modified by: @dyemanovstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 2.1.5 [ 10420 ] Fix Version: 3.0 Alpha 1 [ 10331 ] Fix Version: 2.5.1 [ 10333 ] |
Modified by: @pmakowski |
Modified by: @pmakowski |
Modified by: @pmakowski |
Modified by: @pmakowskistatus: Resolved [ 5 ] => Closed [ 6 ] |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Submitted by: Volker Rehn (vr2_s18)
Is related to CORE1752
Is related to CORE1274
Relate to QA461
Formally, this is a performance regression introduced while fixing CORE1274 and CORE1752. 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.
Commits: 8080bb2 4b55a0c 73be4bf
The text was updated successfully, but these errors were encountered: