"no current record for fetch operation" error with select stored procedures

[danidummer]

Hello,

There's a bug working with selectable stored procedures on Firebird 5 (checked on the official release and snapshot 5.0.1.1347). I couldn't detect exactly what happens inside to trigger the exception, but I could create a sample database to replicate the error.

This error happens when you try to use a join with a select procedure. The database attach show the problem.

Use the following command to reproduce the error:

SELECT P.ID, P.DESCRIPTION, T.TAX_PERC FROM PRODUCTS P JOIN GET_TAX(P.TAX_ID, CURRENT_DATE) T ON (T.TAX_ID_RET = P.TAX_ID)

This error happens only in Firebird 5. Version 4 and below works fine.

BUG.zip

[dyemanov]

Looks like #7937, although I was sure that v4 also has this issue.

Out of curiosity, why do you duplicate the join condition for P.TAX_ID both inside the procedure parameter and inside the ON clause? AFAIU, you just need to write:

SELECT P.ID, P.DESCRIPTION, T.TAX_PERC
FROM PRODUCTS P JOIN GET_TAX(P.TAX_ID, CURRENT_DATE) T ON 1 = 1

or

SELECT P.ID, P.DESCRIPTION, T.TAX_PERC
FROM PRODUCTS P CROSS JOIN GET_TAX(P.TAX_ID, CURRENT_DATE)

[danidummer]

Yes, I've double checked and this cenario fails too in FB 4. You're right.

It's strange that, in my production cenario (other tables and other data aswell), it only happens in FB 5. FB4 and prior work perfectly. Very odd.

I'll take your suggestion of using "CROSS JOIN". Personally, never liked the tautologic syntax "1=1", that's why I've used since ever a single JOIN with columns.

Thanks for your reply.