The following query works well on Firebird 2.5, but on Firebird 5, it results in a no current record for fetch operation error message.
SELECT
LAST_CARD.USR$LISTNUMBER,
EMPL.ID,
CARD.USR$FIRSTMOVE,
EMPL.NAME,
LAST_CARD.USR$BASETSALARY,
LAST_CARD.USR$STAFFNUMBER,
LAST_CARD.USR$TSALARY,
LAST_CARD.USR$MSALARY,
LAST_CARD.USR$THOURRATE,
LAST_CARD.USR$MHOURRATE
FROM
USR$WG_MOVEMENTLINE CARD
LEFT JOIN GD_DOCUMENT DOC ON DOC.ID = CARD.DOCUMENTKEY
LEFT JOIN USR$WG_P_EMPLMOVESTATE_FMK(CARD.USR$EMPLKEY, :EMPLSDATE) MOV ON MOV.FIRSTMOVEKEY = CARD.USR$FIRSTMOVE
LEFT JOIN USR$WG_MOVEMENTLINE LAST_CARD ON LAST_CARD.DOCUMENTKEY = MOV.ID
LEFT JOIN GD_CONTACT EMPL ON EMPL.ID = CARD.USR$EMPLKEY
WHERE
CARD.USR$STAFFLISTPOSID = :USR$POSID
AND
DOC.COMPANYKEY = :COMPANY_KEY
AND
CARD.DOCUMENTKEY = MOV.ID
AND
LAST_CARD.USR$MOVEMENTTYPE <> 3
ORDER BY
EMPL.NAME
The error arises due to two conditions: CARD.DOCUMENTKEY = MOV.ID and LAST_CARD.USR$MOVEMENTTYPE <> 3. Both conditions fail because the corresponding entries are absent in the joined tables. Therefore, the conditions should be interpreted as CARD.DOCUMENTKEY = null and null <> 3, which both evaluate to False.
Should the query be modified as:
SELECT
LAST_CARD.USR$LISTNUMBER,
EMPL.ID,
CARD.USR$FIRSTMOVE,
EMPL.NAME,
LAST_CARD.USR$BASETSALARY,
LAST_CARD.USR$STAFFNUMBER,
LAST_CARD.USR$TSALARY,
LAST_CARD.USR$MSALARY,
LAST_CARD.USR$THOURRATE,
LAST_CARD.USR$MHOURRATE
FROM
USR$WG_MOVEMENTLINE CARD
LEFT JOIN GD_DOCUMENT DOC ON DOC.ID = CARD.DOCUMENTKEY
LEFT JOIN USR$WG_P_EMPLMOVESTATE_FMK(CARD.USR$EMPLKEY, :EMPLSDATE) MOV ON MOV.FIRSTMOVEKEY = CARD.USR$FIRSTMOVE
LEFT JOIN USR$WG_MOVEMENTLINE LAST_CARD ON LAST_CARD.DOCUMENTKEY = MOV.ID
LEFT JOIN GD_CONTACT EMPL ON EMPL.ID = CARD.USR$EMPLKEY
WHERE
CARD.USR$STAFFLISTPOSID = :USR$POSID
AND
DOC.COMPANYKEY = :COMPANY_KEY
AND
CARD.DOCUMENTKEY = COALESCE(MOV.ID, 0)
AND
COALESCE(LAST_CARD.USR$MOVEMENTTYPE, 0) <> 3
ORDER BY
EMPL.NAME
It begins to execute correctly on Firebird 5.
Please do not suggest changing LEFT JOIN to JOIN. We require LEFT JOIN in this context to ensure that the server utilizes the optimal query plan. Switching to JOIN results in the query being ten times slower.
The following query works well on Firebird 2.5, but on Firebird 5, it results in a
no current record for fetch operationerror message.The error arises due to two conditions:
CARD.DOCUMENTKEY = MOV.IDandLAST_CARD.USR$MOVEMENTTYPE <> 3. Both conditions fail because the corresponding entries are absent in the joined tables. Therefore, the conditions should be interpreted asCARD.DOCUMENTKEY = nullandnull <> 3, which both evaluate toFalse.Should the query be modified as:
It begins to execute correctly on Firebird 5.
Please do not suggest changing
LEFT JOINtoJOIN. We requireLEFT JOINin this context to ensure that the server utilizes the optimal query plan. Switching toJOINresults in the query being ten times slower.