Skip to Content
Technical Articles
Author's profile photo Mark Gearhart

Working with the Recursive SQLScript limit in HANA

The SQLScript Reference Manual for HANA contains a description for using resursive logic within procedures and functions. Recursion in procedures has a limitation that sets the maximum call depth to 32. I ran across this as I was rewriting some database-related JAVA code as an SQLScript procedure.
 
While this call-depth check would normally be a showstopper, it looks like with some thought and knowledge of SQLScript capabilities, the limit can be easily overcome.
 
I assume this limit applies to procedures and not functions because it is related to the fact that procedures can execute inserts, updates, and deletes to tables, whereas functions cannot. The origin of the limit “32” is likewise not known. It does turn out that the SAP ASE database system likewise has the same type of recursive capability and limitation even though it is not documented at all, so I would not view the HANA limitation negatively.
 

The Business Application

 
The code under review involves inventory cost methods for equity stock security purchases and sales. When we sell a security, FIFO, LIFO, and moving average accounting methods are applied to the 2nd part of the accounting journal entry, which is the cost part rather than the revenue part.
 
For cost, we calculate the reduction in inventory and the cost of securities sold. The recursive goal is to figure out which securities to use for costing. Although we could use an SQL loop to select the oldest securities (in the case of FIFO), recursion is also viable. When implementing this, we have many cases where positions for more than 32 small security purchases need to be unwound, in order match one large security sale.
 

The Recursion Limit Solution

 
The solution to the recursive call-depth limit is surprising simple.
 
First, the design of each recursive iteration must be transactionally complete, so that the input of the next iteration does not depend on the result of the previous iteration. This disqualifies things like the factorial example in the HANA documentation. Instead, we must have a design where each iteration commits results to the database so that the next iteration can continue from a known point. If SQL functions had a call-depth limit (which they do not), they too would have been disqualified since they cannot persist results in the form of updates, inserts, and deletes to tables.
 
Second, we need a way to continue the recursive iteration if it stops. Fortunately, SQLScript has EXIT and CONTINUE exception handlers. With this, we can trap SQL error code 1340 and continue the recursive iteration.
 
The following is a sample data set, code, and report for a recursive FIFO security costing method which does not abort on a ERR_SQLSCRIPT_NESTED_CALL_TOO_DEEP error. In addition a recursion example, we also have a useful example of the HANA hierarchy feature:
 

Here is the data set

 


-- Chronological inventory of purchases (-) and sales (+)
create table inventory (
id         int          not null,
settleDate date         not null,
qty        int          not null,
cost       decimal(4,2)     null,
position   int          not null);

-- Hierarchy for fifo match
create table f_fifo (
parent_id  int              null,
node_id    int          not null,
ord        int          not null,
settleDate date         not null,
qty        int          not null,
cost       decimal(4,2) not null);

create sequence ord start with 1 increment by 1;

 


insert into inventory values(1004286312,to_date('10/01/2019','MM/DD/YYYY'),-100,10.00,-100);
insert into inventory values(1004286313,to_date('10/03/2019','MM/DD/YYYY'),-100,11.00,-100);
insert into inventory values(1004286314,to_date('10/05/2019','MM/DD/YYYY'), 150,null, 150);
insert into inventory values(1004286315,to_date('10/06/2019','MM/DD/YYYY'),-100,10.80,-100);
insert into inventory values(1004286316,to_date('10/07/2019','MM/DD/YYYY'),  50,null, 50);

 

Here is the code

 


create or replace procedure sp_fifo (in sId int)
as
begin
  declare sPosition, diff, pPosition, pId int;
  declare sSettleDate date;
  declare pCost decimal(10,2);

  -- The oldest purchase (fifo)
  select id, position, cost
  into pId, pPosition, pCost default null, 0, null
  from inventory where id =
    (select min(id)
    from inventory where position < 0)
  for update;

  -- The sale
  select position, settleDate
  into sPosition, sSettleDate default 0, null
  from inventory where id = :sId for update;

  if :pPosition < 0 and :sPositon > 0 then
    diff = greatest (:sPosition + :pPosition, 0);

    update inventory set position = position + (:sPosition - :diff) where id = :pId;
    update inventory set position = :diff where id = :sId;
    insert into f_fifo values (:pId,:sId,ord.nextval,:sSettleDate, :sPosition - :diff,:pCost);
    commit;

    call sp_fifo (:sId);
  end if;
end;

 


create or replace procedure sp_unwind (in sId int)
as
begin
  using sqlscript_print as prtlib;
  using sqlscript_string as strlib;
  declare msg nvarchar(5000) =
    strlib:format('recursion limit {} exceeded. continuing.',32);

  -- Restart fifo if recursion limit reached.
  declare continue handler for sql_error_code 1340
  begin
    prtlib:print_line(:msg);
    call sp_fifo(:sId);
  end;

  call sp_fifo(:sId);
end;

 

Here is how to run it

 


call sp_unwind (1004286314); -- unwind the first sale
call sp_unwind (1004286316); -- unwind the second sale

 

And here is the result

 
We start with inventory input table, showing three purchases and two sales:
 


id          settleDate  ps        qty   cost
----------  ----------  --------  ----  -----
1004286312  2019-10-01  purchase  -100  10.00
1004286313  2019-10-03  purchase  -100  11.00
1004286314  2019-10-05  sale       150
1004286315  2019-10-06  purchase  -100  10.80
1004286316  2019-10-07  sale        50

 
After executing sp_unwind(), the f_fifo table contains costing rows for the sales. Next, we create a Hierarchy data structure as a view over the f_fifo sales posted via sp_unwind(), along with the original purchases posted in the inventory table:
 


create view h_fifo as
select *
from hierarchy (
  source (
    select * from f_fifo
    union all
    select null as parent_id, id as node_id, id as ord, settleDate, qty, cost
    from inventory where qty < 0)
  sibling order by ord);

 


ps        parent_id   node_id     settleDate  qty   cost
--------  ----------  ----------  ----------  ----  -----
purchase  null        1004286312  2019-10-01  -100  10.00
purchase  null        1004286313  2019-10-03  -100  11.00
sale      1004286312  1004286314  2019-10-05   100  10.00
sale      1004286313  1004286314  2019-10-05    50  11.00
purchase  null        1004286315  2019-10-06  -100  10.80
sale      1004286313  1004286316  2019-10-07    50  11.00

 
I am using a Hierarchy data structure over both purchases and sales so that I can net the notionals (qty*cost) to compute a balance. For a parent purchase node which is a (-) quantity, sales are applied to it with a (+) quantity as a child node. Child nodes are added as the sale proceeds recursively. If the purchase has been completely unwound, then the balance is 0.0. The hierarchy_descendants_aggregate() function is used to net parent purchases with matching child sales.
 
Now we compose the query for the final report, showing notionals and balances:
 


select
  case when hierarchy_aggregate_type = 0 then 'entry' else 'balance' end "type",
  case when hierarchy_level = 1 then 'purchase'
    when hierarchy_level != 1 and hierarchy_aggregate_type = 0 then 'sale'
    else null end "ps",
  node_id "id",
  settleDate "settleDate",
  qty "qty",
  cost "cost",
  qty*cost "notional",
  balance "balance"
from hierarchy_descendants_aggregate (
  source h_fifo
  measures (
    sum(qty*cost) as balance
  ) with total null
) order by hierarchy_aggregate_type,settleDate,ord;

 


type    ps       id          settleDate qty  cost  notional balance
------- -------- ----------- ---------- ---- ----- -------- --------
entry   purchase  1004286312 2019-10-01 -100 10.00 -1000.00     0.00
entry   purchase  1004286313 2019-10-03 -100 11.00 -1100.00     0.00
entry   sale      1004286314 2019-10-05  100 10.00  1000.00  1000.00
entry   sale      1004286314 2019-10-05   50 11.00   550.00   550.00
entry   purchase  1004286315 2019-10-06 -100 10.80 -1080.00 -1080.00
entry   sale      1004286316 2019-10-07   50 11.00   550.00   550.00
balance                                                     -1080.00

 
With this, we show that a combination of HANA features for recursion and hierarchies can be used together in just a few lines of code to efficiently solve what used to be a considerable investment in an external JAVA application.
 

Assigned Tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Lars Breddemann
      Lars Breddemann

      And another well-written blog post from you, Mark!

      The scenario presented is really interesting and your solution approach uses a feature that is not seen a lot with HANA (recursive function call).
      My guess concerning the limit of 32 is that it is very easy to produce stack overruns with recursion and that is a thing one wants to avoid with a (usually) centrally shared resource like the HANA database.

      Speaking of shared resources, I think that the procedure in its current form it is not safe to run multiple times in parallel. Or, put in another way, I think the procedure should make sure that the records it is working on are exclusively locked while it is active.

      This can be easily achieved by using the

      SELECT ... INTO ... FROM ... WHERE ... FOR UPDATE;

      construct.

      I was a bit surprised to find that the procedure did not contain a COMMIT statement, while you explicitly mentioned that the state of each iteration should be saved.
      Adding a COMMIT in the sp_fifo procedure right before the recursive call ensures that the data is in fact committed and that the potentially interrupted processing can be picked up again later.

      The last thing I stumbled over was the CASE statement:

      diff = case
            when :sPosition + :pPosition < 0 then 0
            else :sPosition + :pPosition
          end;

      If I understand this correctly, then diff should be the sum of both positions, but never smaller than 0.

      This can be rewritten as 

      diff := GREATEST  (:sPosition + :pPosition, 0);

      To me, that's easier on the eyes and the mind.

      With these changes made, the procedure should have no problems with other processes writing into the inventory table.

       

      Thanks again for the very interesting use case and the implementation example.

      Looking forward to your next post.

      Cheers,

      Lars

      Author's profile photo Mark Gearhart
      Mark Gearhart
      Blog Post Author

      Yes, thanks very much Lars. Your comments are very much appreciated as I come up to speed on HANA. I was thinking about the commit just as I was posting, and I was also very excited about finding a solution to the recursion limit so I posted the code without my usual thought delay. One of those “agile” things…

      I will have to research commits, if they operate differently in HANA than in other database systems.

      Author's profile photo Lars Breddemann
      Lars Breddemann

      Thanks for taking the comments so well. It's so much easier to find improvements after one has done the hard work thinking through the problem.

      One thing with the recursion: I'm actually not sure that recursion is required here.

      From what I understand, the solution fetches the oldest open position (FIFO approach) where the position is negative and updates it once the calculation - based on the sales ID (sID)-  is done.
      Then it calls itself with nothing but the sales ID.

      This can rather easily be changed into a WHILE loop that just checks if there is any open purchase left (pID). If not, then the loop can be exited.
      My point here is that the recursion call does not depend on the result of the previous call (as you've engineered it).

      By circumventing the recursion limit you actually worked out a way without recursion at all. 🙂

       

      And, please... I wish my polished blog posts would be as well produced as yours. Nothing rushed in here!

      Cheers,

      Lars

      Author's profile photo Mark Gearhart
      Mark Gearhart
      Blog Post Author

      Yes, that is correct. I could have used a while loop, which is what the original massive amount of JAVA code and JAVA plumbing did. But now that I’ve cracked the “32” limit problem, I actually do have an even better use case for recursion, and can use the fifo code as an model going forward. Which, I think, is a good reason for blogging this fifo recursion example.

      Also, your tips work perfectly. GREATEST works. FOR UPDATE and COMMIT works. Learned some new HANA things just now.

      Thanks, Mark