Today’s Quiz. How many times is a user function called?


Run the code below. How many times is the function FOO called? Why?


 


The first SELECT is a single table query. The next is a 2 table self-join with no join condition.


 


 


PUBLIC nCount


nCount=0


CREATE CURSOR test (name c(10))


nRecs=5


FOR i = 1 TO nRecs


      INSERT INTO test VALUES (“test”+TRANSFORM(i))


ENDFOR


SELECT  name,foo(name) AS foo FROM test INTO CURSOR result


?”Count = “,nCount


 


nCount=0


 


CLOSE DATABASES all


 


nCount=0


CREATE CURSOR test (name c(10))


FOR i = 1 TO nRecs


      INSERT INTO test VALUES (“test”+TRANSFORM(i))


ENDFOR


 


 


SELECT  a.name,foo(a.name) AS foo FROM test a, test b INTO CURSOR result


?”Count = “,nCount


 


PROCEDURE foo(cName)


      ?PROGRAM(),cName


      nCount=nCount+1


      RETURN nCount


 


RETURN


 

Comments (6)

  1. Jens Brand says:

    I think, select makes one call to determine the structure of the resluting cursor and then one call for each row in the result.

  2. Fabrizio says:

    Coverage Profiler returned 32 hits.

  3. Lou Harris says:

    The first call appears to use the first record value to determine the field type and size in order to have the field structure for the resulting query.

    I’ve found that if I forget to pad a function call to the largest desired width, I end up with truncated values for many of the records in my query result.

  4. Fabio Lunardon says:

    Hi Calvin, if you have time, look here

    CLEAR

    PUBLIC nCount

    CREATE CURSOR test (name c(10))

    nRecs=0

    FOR i = 1 TO nRecs

         INSERT INTO test VALUES ("test"+TRANSFORM(i))

    ENDFOR

    nCount=0

    SELECT  name,foo(name) AS foo FROM test INTO CURSOR result

    ?"Count = ",nCount,"  expected : ",nRecs+1

    nCount=0

    * BUG

    SELECT  name,INT(foo(name)) AS foo FROM test INTO CURSOR result

    ?"Count = ",nCount,"  expected : ",nRecs+1

    nCount=0

    * BUG

    SELECT  name,CAST(CAST(CAST(foo(name) AS I) AS I) AS I) AS foo FROM test INTO CURSOR result

    ?"Count = ",nCount,"  expected : ",nRecs+1," with an optimizad implementation :",nRecs

    nCount=0

    SELECT  name FROM test WHERE foo(name)=0 INTO CURSOR result

    ?"Count = ",nCount,"  expected : ",nRecs+1

    nCount=0

    SELECT  name FROM test HAVING foo(name)=0 INTO CURSOR result

    ?"Count = ",nCount,"  expected : ",nRecs+1

    PROCEDURE foo(cName)

         nCount=nCount+1

         RETURN nCount

  5. Alejandro Sosa says:

    First one 5 times, one for each record

    Second one 25 times.  Each record of test a is joined with each record of test b, so 5 * 5 = 25.  Function is called once for each record produced.

  6. 1: once to determine structure for cursor and once for each record = 6 total

    2: once to determine structure for cursor and 25 times for unjoined tables = 26