why does external java function terminate recursive cte?

0 votes
1 answer
97 views
                          I made a peculiar observation regarding external functions written in java which I hope someone can shed a light on. I tried it with several functions, but I'll use one of them as an example here:

    CREATE FUNCTION NYA.REMOVE_DIACRITICS( S VARCHAR(100))
      RETURNS varchar(100)
      FENCED THREADSAFE
      DETERMINISTIC
      NO SQL
      LANGUAGE JAVA
      PARAMETER STYLE JAVA
      RETURNS NULL ON NULL INPUT
      EXTERNAL NAME
    'StringUtil:se.uhr.nya.commons.db.procedures.DiacriticUtil!removeDiacritics'
      NO EXTERNAL ACTION

So, if I call the function outside the cte everything works as expected:

    with t(n) as ( values 65 
                   union all 
                   select n+1 from t where n+1 < 70 
                 ) 
        select n, nya.remove_diacritics(chr(n)) from t

    SQL0347W  The recursive common table expression "DB2INST1.T" may contain an 
    infinite loop.  SQLSTATE=01605

     65 A                                                                                                                                                                                                       
     66 B                                                                                                                                                                                                       
     67 C                                                                                                                                                                                                       
     68 D                                                                                                                                                                                                       
     69 E                           

    5 record(s) selected with 1 warning messages printed.

But if I move the function call inside the cte, it is terminated immediately:

    with t(n,s) as ( values (65,nya.remove_diacritics(chr(65))) 
                     union all 
                     select n+1, nya.remove_diacritics(chr(n+1)) from t where n+1 < 70 
                   ) 
        select n,s from t

    SQL0347W  The recursive common table expression "DB2INST1.T" may contain an 
    infinite loop.  SQLSTATE=01605

         65 A                                                                                                                                                                                                       

    1 record(s) selected with 1 warning messages printed.


If I replace the function call with a constant in the recursive leg of the cte, once again 5 rows are returned so I guess the recursion is terminated in that part:

    with t(n,s) as ( values (65,nya.remove_diacritics(chr(65))) 
                     union all 
                     select n+1, 'A' from t where n+1 < 70 
                   ) 
        select n,s from t
    ...
    5 record(s) selected with 1 warning messages printed.


FWIW, it is possible to trick the compiler by using a case statement and a non-obvious contradiction:

    with t(n,s) as ( values (65,nya.remove_diacritics(chr(65))) 
                     union all 
                     select n+1, case when n < 1000 then 
                                   nya.remove_diacritics(chr(n+1))
                                 else 'A' end 
                     from t where n+1 < 70  
                   ) 
       select n,s from t

         65 A                                                                                                                                                                                                       
         66 B                                                                                                                                                                                                       
         67 C                                                                                                                                                                                                       
         68 D                                                                                                                                                                                                       
         69 E

Is it any property on the function that can explain this behaviour? As mentioned I tried other functions as well, and they behave the same way.

This is not a problem per see, I'm only curious about why this happens. If there is some rationale behind it, it is nice to know for future use.


                        
Asked by Lennart - Slava Ukraini (23862 rep)
Feb 8, 2022, 10:16 AM
Last activity: Nov 2, 2023, 06:59 AM
why does external java function terminate recursive cte?

Related Questions
