New post
Article
· Feb 25, 2016 2m read

Alert: Incorrect SQL Results

#Alerts #Caché

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.

Discussion (5)2
Log in or sign up to continue
Brendan Bannon · Mar 3, 2016

March 3, 2016 – Alert: Incorrect SQL Results – Update

After further study of this problem, InterSystems has decided to replace all 2015.2.* full kits on the WRC distribution page. We have removed the current kits and will post new 2015.2.* kits as soon as they are available. We will also post an alert announcing that the new kits are available.

If you are using Cache 2015.2.*, Ensemble 2015.2.*, or a HealthShare kit based on 2015.2.*, InterSystems recommends that you get a full-kit Ad Hoc that contains the correction. Once you have upgraded to a new kit containing the correction, you must compile all the classes and routines in your application.

If you have any questions regarding this alert, please contact the Worldwide Response Center.

1 0
Brendan Bannon · Mar 3, 2016

Here is a little more detail on the scope of this alert.  This problem can happen when a non-cursor based query is nested in a second one and both  make use of a temp index (temp-file in Show Plan text).

 

Non-cursor based queries are:

SELECT - that return only 1 row.  These would only happen in Embedded SQL ie &SQL(SELECT….)

All INSERTs - can happen in Dynamic, xDBC or Embedded

All UPDATEs - can happen in Dynamic, xDBC or Embedded

All DELETEs   - can happen in Dynamic, xDBC or Embedded

 

Some examples of queries in the Samples Namespace that would generate problem code are:

 

&SQL(SELECT name, Age, $$Sample.Person_ UpdateProcTest FROM SQLUser.testidkey2 WHERE (:K1 is not null and Age = :K1) OR (:K1 IS NULL AND Age IS NULL))

 

INSERT INTO Sample.Person (Name, SSN) SELECT Name, SSN FROM Sample.Person WHERE (:K1 is not null and Age = :K1) OR (:K1 IS NULL AND Age IS NULL)

 

UPDATE Sample.Person SET Name = 'Bannon,Brendan' WHERE SSN = ?

 

DELETE FROM Sample.person WHERE name Like ? and Age > ?

 

If you look at Show Plans for these queries the thing that they all have in common and is the text that says the query can lead to the problem is building a temp-file.  This temp-file can have a couple of different formats so the text can vary some.  Two examples are:

 

 Add ID bit to bitmap temp-file A.

 

and

 

 Add a row to temp-file A, subscripted by a counter

 

 

For the problem to happen you would need to have 2 queries that both make use of a temp-file and one is nested inside the other.  Some examples of nesting would be:

 

  1. The SELECT above that has a $$ function call.  That Stored Procedure does an SQL UPDATE.
  2. A SELECT that has a Computed Field that uses a non-cursor base query.
  3. Issuing an INSERT UPDATE or DELETE that would invoke a trigger that contained a non-cursor based query.
  4. Doing an INSERT UPDATE or DELETE against a Parent class that uses relationships
  5. Doing an INSERT UPDATE or DELETE against a class that uses a Foreign Key with Cascade
  6. Doing a %Save() or a %Delete() against a class that uses cache SQL Storage and has triggers that contained a non-cursor based query.
  7. Doing a %Save() or a %Delete() against a Parent class that uses relationships
  8. Doing a %Save() or a %Delete() against a class that uses a Foreign Key with Cascade
  9. A Stored Procedure that contains a non-cursor based query and calls a second SP that also has a non-cursor based query
  10. ?????
0 0