Subject RE: [firebird-support] Stored Procedures in a Join
Author Svein Erling Tysvær
>I'm trying to use two stored procedures as if they were two tables in a join query (right outer join).
>Both procedures have a good deal of calculations and/or conditional processing.
>Using IBExpert, the query runs and produces results, however, if I try to scroll to new records, or Fetch All,
>I get the following "memory" error. It mentions the stored procedure that contains the greates number of rows,
>and has the largest number of calculations.
>I suppose my question is; Is it acceptable to join procedures in this manner? if so, are there precautions
>I should take that will prevent out-of-memory issues?
>"Unsuccessful execution caused by an unavailable resource.
>unable to allocate memory from operating system.
>At procedure 'P_ASSEMBLE_LOGS_TABLE_FIELDS' line: 47, col: 32."

Yes, I think it should be possible to use outer joins to stored procedures, at least if done in the correct direction. Years ago, there was a problem with inner joins: the optimizer chose the wrong order and having the stored procedure before the table in the plan gave problems in queries like:

FROM Table1
JOIN SP1(Table1.Field1) ON SP1.ResultField = Table1.IndexedField

Naturally, if you have SP1 before Table1 in the plan, the input field (Table1.Field1) isn't available and an error occurs. Later versions of Firebird (1.5 and later?) ascertains that Table1 will be before SP1 in the plan and then there is no problem, though of course

FROM Table1
RIGHT JOIN SP1(Table1.Field1) ON SP1.ResultField = Table1.IndexedField

will fail (you're telling SP1 to execute before its input parameter is available).

Talking about IB Expert, it has a kind of "stored procedure step-through debugger". Firebird doesn't support any such thing, so IB Expert is actually emulating the stored procedure rather than using it. Maybe your error is from IB Expert and not Firebird (it doesn't look like a Firebird error message). What happens if you run you query through ISQL, IB_SQL or FlameRobin?

If it is not an IB Expert error, then show us the query and the plan and maybe we may spot something.