Initial test case from user:
SELECT A.JEDIN
FROM ARDACO_EDESK_INFO A
JOIN ADRESAR B ON B.ICO = A.ICO
JOIN ARDACO_EDESK_ADDRESS C ON C.ARDACO_EDESK_INFO = A.JEDIN
WHERE B.JEDIN = 0 AND A.CORPORATE_NAME = B.NAZOV_DLHY
-- 3.0.8 PLAN SORT (JOIN (B INDEX (RDB$PRIMARY3), A INDEX (IDX_ARDACO_EDESK_INFO_CORP_NAME), C INDEX (FK_ARDACO_EDESK_ADDRESS_INFO)))
-- 3.0.9 PLAN SORT (HASH (JOIN (C NATURAL, A INDEX (PK_ARDACO_EDESK_INFO)), B INDEX (RDB$PRIMARY3)))
The point here is that A depends on both B and C and one of these dependencies is unique (A.JEDIN). The optimizer wrongly assumed that A is independent from B, but it also considered B independent from anything else, because filter on B is also unique (B.JEDIN = 0). Hence the nested loop join was used only for (A, C) and B was hash joined to the result.
The bug was introduced while fixing #3357 / #7118.
Initial test case from user:
The point here is that A depends on both B and C and one of these dependencies is unique (
A.JEDIN). The optimizer wrongly assumed that A is independent from B, but it also considered B independent from anything else, because filter on B is also unique (B.JEDIN = 0). Hence the nested loop join was used only for (A, C) and B was hash joined to the result.The bug was introduced while fixing #3357 / #7118.