Alert: Risk of Silent Wrong Results When Using Specific OUTER JOIN Patterns in SQL Queries

This alert has been corrected - please see: October 10 - Correction Notice: Updated Alert for DP-442892

Alert ID	Product & Versions Affected	Risk Category & Score	Explicit Requirements
DP-442892	InterSystems IRIS® data platform InterSystems IRIS® for Health HealthShare® Health Connect versions 2023.3.0, 2024.1.0 – 2024.1.3, 2024.2.0, 2024.3.0, and 2025.1.0 HealthShare® Unified Care Record versions 2024.2 and 2025.1	Wrong Results: High Risk	Specific use of OUTER JOIN when the inner leg has an index on the join fields

In InterSystems IRIS versions 2023.3.0, 2024.1.0 – 2024.1.3, 2024.2.0, 2024.3.0, and 2025.1.0, for queries that use an OUTER JOIN and where InterSystems IRIS performs parallel execution, the query may silently return wrong results.

In order for a query to be at risk, the query needs to satisfy all of the following conditions:

• It uses an outer join (RIGHT, LEFT, OUTER, or FULL) where the inner leg (for example, t2 in “t1 LEFT JOIN t2”) is a view or subquery; all the values from that view or subquery that participate in the join condition (directly in the ON clause or in the WHERE clause) are fields; and those fields are indexed in the tables from which that view or subquery selects.
• The view or subquery in the inner leg does not contain any of the following:
  
  • a UNION clause
  • a TOP (or equivalent LIMIT/OFFSET or OFFSET/FETCH) clause
  • any non-grouped aggregate functions
  • any grouped aggregate functions (GROUP BY or %FOREACH), where the set of grouping values is not equal to nor a superset of the values used in the join condition

For example, the following cases are affected:

SELECT * FROM t1 LEFT JOIN (SELECT field2, field3 FROM t2) AS v2 ON t1.field1 = v2.field2
when t2 has an index on field2

SELECT * FROM (SELECT field1, field3 FROM t1) AS v1 RIGHT JOIN t2 WHERE v1.field1 = t2.field2
when t1 has an index on field1

But the following cases are not:

SELECT * FROM t1 LEFT JOIN (SELECT field2, field3 FROM t2 UNION SELECT field4, field5 FROM t3) AS v2 ON t1.field1 = v2.field2
because it includes a UNION

SELECT * FROM t1 LEFT JOIN (SELECT TOP 10 field2, field3 FROM t2) AS v2 ON t1.field1 = v2.field2
because it includes a TOP clause

SELECT * FROM t1 LEFT JOIN (SELECT SUM(field2) FROM t2) AS v2 ON t1.field1 = v2.field2
because it includes a non-grouped aggregate

SELECT * FROM t1 LEFT JOIN (SELECT SUM(field2), field3 FROM t2 GROUP BY field3) AS v2 ON t1.field1 = v2.field2
because it has an aggregate that is not grouped by the join fields

If a query that meets these criteria is executed in parallel (because of an optimizer decision based on large table size or an explicit %PARALLEL hint) on one of the affected versions, then the query may return wrong results.

This issue may cause InterSystems IRIS SQL to silently return wrong results.

The issue has been corrected in versions 2024.1.4, 2025.1.1, 2025.2.0 and any more recent version.

• In the interim, customers who require the correction prior to these releases may request an ad hoc distribution for the fix (DP-442948) through the Worldwide Response Center.

• As a mitigation, customers may include the %NOPARALLEL hint in possibly affected SQL queries or temporarily disable automatic query parallelization using the AutoParallel setting.

If you have questions or need assistance, please contact the InterSystems Worldwide Response Center (WRC).