Nested loop plan is chosen instead of the sort merge for joining independent streams using keys of different types [CORE3553]

[firebird-automations]

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

[firebird-automations]

Commented by: @dyemanov

@ Volker: the workaround in your case is:

select o.objekt, o.beschreibung, o.modultext, o.thema, o.feldtyp
from get_w_objektlookup('FAC') o
join get_mcb_modul(5) m on m.modul = cast(o.modul as varchar(30))
order by 4,3,2

[firebird-automations]

Modified by: @dyemanov

Link: This issue is related to CORE1752 [ CORE1752 ]

[firebird-automations]

Modified by: @dyemanov

Link: This issue is related to CORE1274 [ CORE1274 ]

[firebird-automations]

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

[firebird-automations]

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

[firebird-automations]

Modified by: @dyemanov

reporter: Dmitry Yemanov [ dimitr ] => Volker Rehn [ vr2@s18 ]

[firebird-automations]

Modified by: @dyemanov

status: In Progress [ 3 ] => Open [ 1 ]

[firebird-automations]

Modified by: @dyemanov

status: 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 ]

[firebird-automations]

Modified by: @pmakowski

Link: This issue block progress on QA461 [ QA461 ]

[firebird-automations]

Modified by: @pmakowski

Link: This issue block progress on QA461 [ QA461 ] =>

[firebird-automations]

Modified by: @pmakowski

Link: This issue relate to QA461 [ QA461 ]

[firebird-automations]

Modified by: @pmakowski

status: Resolved [ 5 ] => Closed [ 6 ]

[firebird-automations]

Modified by: @pavel-zotov

status: Closed [ 6 ] => Closed [ 6 ]

QA Status: Done successfully