Criteria for when a function call can be reused?

0 votes
0 answers
67 views
                          I must admit I have not given it much thought previously, but I always assumed that a NOT DETERMINISTIC function would not be reused between function calls, Example:

    CREATE OR REPLACE FUNCTION TEST1()
    RETURNS INT
    CONTAINS SQL
    NO EXTERNAL ACTION
    NOT DETERMINISTIC
            RETURN VALUES INT(1000*RAND())
    @

But if I try that as:

    db2 "select test1() from ( values (1),(2) )"
    
    1          
    -----------
            868
            868
    
I get the same value for both 1 and 2. I assume this is because test1() is evaluated once and the result is reused for both 1 and 2. I thought not deterministic would prevent this, but apparently, I was wrong. If I instead create the function as:

    CREATE OR REPLACE FUNCTION TEST2()
    RETURNS INT
    CONTAINS SQL
    NO EXTERNAL ACTION
    NOT DETERMINISTIC
    BEGIN ATOMIC
            RETURN VALUES INT(1000*RAND());
    END @


    db2 "select test2() from ( values (1),(2) )"
    
    1          
    -----------
            596
             16

It behaves as I would have expected.

Any links to docs that can shed some light on this?
 
EDIT: Yet another simplified scenario, but closer to the real deal

    CREATE TABLE T ( X INT NOT NULL PRIMARY KEY ) @

    CREATE OR REPLACE FUNCTION TEST3()
    RETURNS INT
    READS SQL DATA
    NO EXTERNAL ACTION
    NOT DETERMINISTIC
            RETURN
                WITH TT (X,N) AS (
                    VALUES (INT(1000*RAND()),0)
                    UNION ALL
                    SELECT INT(1000*RAND()), N+1
                    FROM TT, T
                    WHERE N < 10000 AND T.X = TT.X
                )
                SELECT X FROM TT
                WHERE N = (SELECT MAX(N) FROM TT)
    @
    
    CREATE OR REPLACE FUNCTION TEST4()
    RETURNS INT
    READS SQL DATA
    NO EXTERNAL ACTION
    NOT DETERMINISTIC
    BEGIN ATOMIC
            RETURN
                WITH TT (X,N) AS (
                    VALUES (INT(1000*RAND()),0)
                    UNION ALL
                    SELECT INT(1000*RAND()), N+1
                    FROM TT, T
                    WHERE N < 10000 AND T.X = TT.X
                )
                SELECT X FROM TT
                WHERE N = (SELECT MAX(N) FROM TT);
    END @


    db2 "select test3() from ( values (1),(2) )"
    
    1          
    -----------
            320
            320
    
    db2 "select test4() from ( values (1),(2) )"
    
    1          
    -----------
            846
            836

I don't mind using BEGIN ATOMIC in my function definition, I'm mostly curious why db2 reuses the value from a not deterministic function

EDIT2: From the plan it appears as if test3 is inlined (that I would have expected from a deterministic function without a begin atomic block)

    Optimized Statement:
    -------------------
    SELECT 
      (SELECT 
         Q11.$C0 
       FROM 
         (SELECT 
            MAX(Q9.$C0) 
          FROM 
            (SELECT 
               Q8.$C1 
             FROM 
               (SELECT 
                  INT((+1.00000000000000E+003 * RAND())),
                  (Q4.$C1 + 1) 
                FROM 
                  DB2INST1.T AS Q3,
                  Q8 AS Q4 
                WHERE 
                  (Q3.X = Q4.$C0) AND 
                  (Q4.$C1 < 10000) 
                UNION ALL 
                SELECT 
                  INT((+1.00000000000000E+003 * RAND())),
                  0 
                FROM 
                  (VALUES 
                     1) AS Q6
               ) AS Q8 
             WHERE 
               Q8.$C1 IS NOT NULL
            ) AS Q9
         ) AS Q10,
         Q8 AS Q11 
       WHERE 
         (Q11.$C1 = Q10.$C0)
      ) 
    FROM 
      (SELECT 
         $INTERNAL_FUNC$() 
       FROM 
         (VALUES 
            1,
            2) AS Q1
      ) AS Q2


                        
Asked by Lennart - Slava Ukraini (23862 rep)
Jan 19, 2022, 03:40 PM
Last activity: Jan 19, 2022, 08:11 PM
Criteria for when a function call can be reused?

Related Questions
