Firebird 5 issue, query not working compared to Firebird 4

[C Fraser]

Hi,

I have found an issue with Firebird 5 which I have whittled down from our larger query/stored proc to a simple example in a slightly modified empolyee.fdb db.

The query works in Firebird 2.5, 3, 4 but not 5. In 5, I get an error: 

The cursor identified in the UPDATE or DELETE statement is not positioned on a row.
no current record for fetch operation.
-----------------------------------------------------------------------------------
SQLCODE: -508
SQLSTATE: 22000
GDSCODE: 335544348

Not sure if it is by design due to some change, or some bug...

Here is the process to duplicate the issue... In the shipped employee.fdb db change the GET_EMP_PROJ stored proc to also return the given emp_no, like so:

create or alter procedure Get_Emp_Proj (Emp_No smallint)
returns (
  Proj_Id char(5),
  Emp_No_Out smallint)
as
BEGIN
    FOR SELECT emp_no, proj_id
        FROM employee_project
        WHERE emp_no = :emp_no
        INTO :emp_no_out, :proj_id
    DO
        SUSPEND;
END;

Then try the following query:

select *
from
  Employee E
  left join Get_Emp_Proj(E.Emp_No) P on E.Emp_No = P.Emp_No_Out
  where p.Proj_Id = 'VBASE'

As mentioned, the query works on Firbird 2.5 (Win and Linux), 3 (only tried Linux), 4 (only tried Linux) but not on 5 (again, only tried Linux).

We have quite a few queries with joins on stored procedures, and of the ones tested, they seem to have the issue (some work if you don't reference a stored proc column in the where clause).

Regards

CF

Attention:

The information in this email and in any attachments is confidential. If you are not the intended recipient then please do not distribute, copy or use this information. Please notify us immediately and then delete the message from your computer. Any views or opinions presented are solely those of the author.

[Dimitry Sibiryakov]

C Fraser wrote 12.06.2024 5:41:
> Not sure if it is by design due to some change, or some bug...

It may be some bug in design change.
First, try fresh snapshot https://firebirdsql.org/en/snapshot-builds/
Second, try to set "OuterJoinConversion = false" in firebird.conf:
https://github.com/FirebirdSQL/firebird/issues/8115#issuecomment-2109366168

--
WBR, SD.

[C Fraser]

Thanks for that.

I tried the latest snapshot and the error is still there.

I then tried OuterJoinConversion = false and that did fix the problem.

Should I add a comment to the mentioned issue? (The employee db is an easy test case...)

Thanks.

[C Fraser]

I have added a new issue since result was different to the linked issue (error vs not returning records).

Thanks.