search

FirebirdSQL / firebird

Firebird server, client and tools
https://www.firebirdsql.org/
1.21k stars 209 forks source link

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 #7937

Closed gsbelarus closed 3 months ago

gsbelarus commented 6 months ago

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 commented 6 months ago

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 commented 6 months ago

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 commented 6 months ago

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 commented 6 months ago

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

dyemanov commented 6 months ago

What input parameter values should be used to reproduce?

YRozmysl commented 6 months ago

EMPLSDATE = 05.01.2024 USR$POSID = 1 COMPANY_KEY = 147002705

dyemanov commented 6 months ago

Reproduced, thanks.

dyemanov commented 6 months ago

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
danidummer commented 3 months ago

Only an addendum to #8036. The "CROSS JOIN" suggestion fails in the discontinued version 2.5.9. I know it won't be fixed, it's ok. Only to inform about.

pavel-zotov commented 3 months ago

QA issue: no data sample to reproduce.