February 25, 2016 – Alert: Incorrect SQL Results
InterSystems has corrected a defect that can cause incorrect results for certain SQL INSERT, UPDATE, and DELETE statements.
This defect is present only in Caché and Ensemble 2015.2 and HealthShare distributions based on them. The problem affects all platforms.
Incorrect results occur only when two or more of these statements are nested within each other.
The example below demonstrates one possible way for this problem to happen:
- The class has an UPDATE trigger that uses embedded SQL to modify a table.
- Because the SQL is embedded in the UPDATE trigger, it is a nested statement – the trigger’s UPDATE is called while the UPDATE that initiated the trigger is still being executed. (This is the condition that can lead to incorrect results.)
- In this case, the trigger is updating this table but the problem could happen even if a different table were being changed.
Given the following class, User.Test:
Class User.Test Extends %Persistent
{
Index idx On (x, y);Property x As %Integer;
Property y As %Integer;
Property z As %Integer;
Property flg As %Integer;
Property zz As %String;Trigger UA [ Event = UPDATE, Time = AFTER ]
{
if ({flg}=0) quit
&sql( UPDATE SQLUser.Test
SET flg=0
WHERE x=:{x} and y=:{y} and z=:{z}
)
quit
}
}
and that User.Test has the following two rows:
x y z flg zz
123 55 1 0
123 55 2 0
In this situation, the following call to UPDATE only modifies one row:
UPDATE SQLUser.Test SET flg=1,zz='done' WHERE x=123 and y=55
This call then has a result of:
x y z flg zz
123 55 1 1 done
123 55 2 0
The correction for this defect is identified as DPV4766. It will be included in all future releases of Caché, Ensemble, and HealthShare. The correction is also available via Ad Hoc distribution from InterSystems Worldwide Response Center (WRC). If you have any questions regarding this alert, please contact the Worldwide Response Center.