Alert: Outer Join Query Results

June 25, 2018 – Alert: Outer Join Query Results

InterSystems has corrected two defects that can cause SQL outer joins to return incorrect results. These defects can also impact DeepSee in Caché and Ensemble, as well as InterSystems Business Intelligence in InterSystems IRIS; in these cases, building and synchronizing some analytic models may result in build errors.

These issues exist on all platforms for the following released InterSystems Data Platform products:

• InterSystems IRIS Data Platform 2018.1.1
• Caché and Ensemble 2017.2.0 and 2017.2.1
• HealthShare Health Connect 15.03 on Ensemble 2017.2.0.744.0
• HealthShare Health Connect 15.03 on Ensemble 2017.2.1.801.0
• HealthShare Health Connect 15.031 on Ensemble 2017.2.1.801.3.18095

Queries with an OUTER JOIN that Use TOP with ORDER BY 

The first defect appears in queries that use an OUTER JOIN together with TOP with ORDER BY. The defect results in either extra rows being returned or the query never completing. A query is susceptible to this defect when there are NULL rows in the outer table. An example of a query that could experience the problem is:

SELECT TOP 1 E.Name, C.Name As CompanyName
  FROM Sample.Employee E 
  LEFT OUTER JOIN Sample.Company C ON E.Company = C.Id
  ORDER BY E.Name

Queries with multiple OUTER JOINs that use Read Committed Mode 

The second defect appears in queries with multiple OUTER JOINs that use Read Committed Mode. In join conditions where there is no matching data in the outer table the row is erroneously omitted from the results. 

For details on Read Committed Mode, please refer to the section “ISOLATION LEVEL in Effect” in the SET TRANSACTION page of the InterSystems SQL Reference.

Either of the following queries could return incorrect results:

SELECT Name, Spouse->Name As SpouseName,
    Company->CompanyName As CompanyName
  FROM Sample.Employee 
  WHERE ID = ?

SELECT E.Name, S.Name As SpouseName, C.Name As CompanyName
  FROM Sample.Employee E 
  LEFT OUTER JOIN Sample.Person S ON E.Spouse = S.Id
  LEFT OUTER JOIN Sample.Company C ON E.Company = C.Id
  WHERE E.ID = ?

Since InterSystems Business Intelligence and DeepSee use Read Committed Mode, this defect may be triggered when building and synchronizing cubes.  In this case, the issue results in the following error:

         ERROR #5001: Error fetching row: (100)

Availability of Corrections

The corrections for these defects are identified as TRW1600, TRW1605, TRW1610, and HSU250.  These will be included in all future releases of InterSystems IRIS, Caché, Ensemble, and HealthShare. These corrections are 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.