Deterministic Java functions in expressions

Deterministic expressions and functions always return the same result if they are evaluated with the same set of input values. All Java functions in Adaptive Server are deterministic. As a result, if the parameters and input values in an expression involving a Java function do not change, Adaptive Server treats the entire expression as deterministic.

When Adaptive Server encounters a Java function in an expression, Adaptive Server calculates the expression immediately so that the calculation is performed only once and not repeated for each row. This improves performance, but may cause unexpected behavior.

Consider this example:

1> create table CaseTest
2> (TestValue varchar(50))
3> go
1> insert into CaseTest values(’07’)
2> go
(1 row affected)

1> declare @IntArray sybase.cpp.value.client/common.IntArray
2> select @IntArray = new sybase.cpp.value.client.common.IntArray()
3> SELECT CASE
4> WHEN CT.TestValue = ’07’
5> THEN @IntArray >> setInt(new java.lang.Integer(10))
6> ELSE @IntArray >> setInt(new java.lang.Integer(11))
7> END
8> FROM CaseTest CT
9> select @IntArray >> getInt(0) as GetObjAfter0
10> select @IntArray >> getInt(1) as GetObjAfter1
11> select @IntArray >> getArraySize() as NumObjectsOnArray
12> go

----------------------------------------------
sybase.cpp.value.client.common.IntArray@22cc0f30

(1 row affected)
GetObjAfter0
----------------------------------------------
11

(1 row affected)
NumObjectsOnArray
---------------
2

(1 row affected)

You might expect one branch of the case statement to evaluate to true and thus have only one value (10) inserted into the integer array, but because the expressions setInt(new java.lang.Integer(10)) and setInt(new java.lang.Integer(11)) are deterministic, Adaptive Server “precalculates” the result, and populates the array with both values.

You can make expressions nondeterministic by adding a reference to columns so that Adaptive Server does not know that the expressions produce the same result for each execution. For example, make these changes to the Transact-SQL statements in the example:

1> declare @IntArray Sybase.cpp.value.client.common.IntArray
2> select @IntArray = new sybase.cpp.value.client.common.IntArray()
3> SELECT CASE
4> WHEN CT.TestValue = ’07’
5> THEN @IntArray >> setInt(new java.lang.Integer(10 + convert(int,CT.TestValue) - convert(int,CT.TestValue)))
6> ELSE @IntArray >> setInt(new java.lang.Integer(11 + convert(int,CT.TestValue) - convert(int,CT.TestValue)))
7> END
8> FROM CaseTest CT
9> select @IntArray >> getInt(0) as GetObjAfter0
10> select @IntArray >> getInt(1) as GetObjAfter1
11> select @IntArray >> getArraySize() as NumObjectsOnArray
12> go

By including the column references in the THEN and ELSE portions of the case statement, the optimizer no longer treats the statements as constants and does not precalculate the Java insert statement.