Skip to main content
Database Administrators

Return to Question

added 1349 characters in body
Source Link
Lennart - Slava Ukraini
Lennart - Slava Ukraini
  • 23.6k
  • 3
  • 33
  • 71

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

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
edited tags
Link
mustaccio
mustaccio
  • 26.9k
  • 24
  • 58
  • 74
Source Link
Lennart - Slava Ukraini
Lennart - Slava Ukraini
  • 23.6k
  • 3
  • 33
  • 71

why does external java function terminate recursive cte?

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.

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.