In general, avoid invoking in the same SQL statement multiple methods on the same Java-SQL object. If at least one of them modifies the object, the order of evaluation can affect the outcome.
For example, in this example:
select * from emp E where Utility.F(E.home_addr) > Utility.F(E.home_addr)
the where clause passes the same home_addr column in two different method invocations. Consider the evaluation of the where clause for a row whose home_addr column has a 5-character zip, such as “95123.”
Adaptive Server can initially evaluate either the left or right side of the comparison. After the first evaluation completes, the second is processed. Because it executes faster this way, Adaptive Server may let the second invocation see the modifications of the argument made by the first invocation.
In the example, the first invocation chosen by Adaptive Server returns 1, and the second returns 0. If the left operand is evaluated first, the comparison is 1>0, and the where clause is true; if the right operand is evaluated first, the comparison is 0>1, and the where clause is false.
