Inner join raises error "no current record for fetch operation" if a stored procedure depends on some table via input parameter and also has an indexed relationship with another table

[gsbelarus]

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.

[dyemanov]

Using COALESCE is one option, setting OuterJoinConversion = false in firebird.conf is another one. Regardless, this error should not happen, so this looks like a bug anyway. Could you please show the plan?

[gsbelarus]

Plan for the query which raises error (FB5):

PLAN SORT (JOIN (JOIN (JOIN (CARD INDEX (RDB$PRIMARY123), 
LAST_CARD INDEX (RDB$PRIMARY123), DOC INDEX (GD_PK_DOCUMENT)), 
MOV NATURAL), 
EMPL INDEX (GD_PK_CONTACT)))

Plan, when error is fixed using COALESCEs:

PLAN SORT (JOIN (JOIN (JOIN (JOIN (CARD INDEX (USR$_X_USR$WG_POSID), 
DOC INDEX (GD_PK_DOCUMENT)), MOV NATURAL), 
LAST_CARD INDEX (RDB$PRIMARY123)), 
EMPL INDEX (GD_PK_CONTACT)))

Plan for FB 2.5, no error, no coalesces:

PLAN SORT (JOIN (JOIN (JOIN (JOIN (CARD INDEX (USR$_X_USR$WG_POSID), 
DOC INDEX (RDB$PRIMARY105)), 
SORT (ML INDEX (USR$FKUSR$WG_MOVEMENTLINE284, RDB$FOREIGN870))), 
ML1 ORDER RDB$FOREIGN870 INDEX (USR$FKUSR$WG_MOVEMENTLINE284)), 
SORT (ML INDEX (USR$FKUSR$WG_MOVEMENTLINE284, RDB$FOREIGN870)))
JOIN (ML1 ORDER RDB$FOREIGN870 INDEX (RDB$FOREIGN871, 
USR$FKUSR$WG_MOVEMENTLINE284), 
WT INDEX (USR$FKWG_EMPLWORKTERM290, 
USR$FKWG_EMPLWORKTERM280))
(LAST_CARD INDEX (RDB$PRIMARY867))(EMPL INDEX (RDB$PRIMARY17)))

[dyemanov]

I tried to recreate your example from scratch, but I see a different plan with empty tables:

SORT (JOIN (JOIN (JOIN (CARD INDEX (RDB$PRIMARY8), DOC INDEX (RDB$PRIMARY4), LAST_CARD INDEX (RDB$PRIMARY8)), MOV NATURAL), EMPL INDEX (RDB$PRIMARY7)))

Could you provide a reproducible test case (probably simplified)?

[gsbelarus]

The database file is 323 GB. Let me think how to get to the simplified version of it.

[dyemanov]

What input parameter values should be used to reproduce?

[YRozmysl]

EMPLSDATE = 05.01.2024
USR$POSID = 1
COMPANY_KEY = 147002705

[dyemanov]

Reproduced, thanks.

[dyemanov]

Simplified test query:

SELECT count(*)
FROM USR$WG_MOVEMENTLINE CARD
  JOIN USR$WG_P_EMPLMOVESTATE_FMK(CARD.USR$EMPLKEY, date '05.01.2024') MOV ON MOV.FIRSTMOVEKEY = CARD.USR$FIRSTMOVE
  JOIN USR$WG_MOVEMENTLINE LAST_CARD ON LAST_CARD.DOCUMENTKEY = MOV.ID