Skip to content
Effectuer une recherche pour en savoir plus sur les produits et solutions InterSystems, les offres d'emploi, etc.

Alert: Incorrect SQL Results - Feb 25, 2016

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.