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:

  1. The class has an UPDATE trigger that uses embedded SQL to modify a table.
  2. 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.)
  3. 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} 

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.