Skip to content
Search to learn about InterSystems products and solutions, career opportunities, and more.

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} 
    ) 
    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.

Latest Alerts & Advisories

Sign Up Today

Receive notifications on support alerts, critical issues,
fixes, and product releases.
*Required Fields
Highlighted fields are required
*Required Fields
Highlighted fields are required
By submitting this form, you give consent to receive notifications concerning support alerts, critical issues, important updates, fixes, and product releases via email. In addition, you consent to your business contact information being entered into our CRM solution that is hosted in the United States, but maintained consistent with applicable data protection laws.
**By clicking here, you give consent to be contacted for news, updates and other marketing purposes related to existing and future InterSystems products, offerings, and events.