Skip to content
Puede usar nuestro buscador para encontrar información sobre los productos y soluciones de InterSystems, las oportunidades de desarrollo profesional, los casos de uso, novedades y mucho más.

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

Summary

Alert ID
Product & Versions Affected
Risk Category & ScoreExplicit 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 RiskSpecific use of OUTER JOIN when the inner leg has an index on the join fields

Issue

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.

Impact

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

Resolution

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.

For More Information

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

Latest Alerts & Advisories

oct 07 2025
Risk Category & Score Explicit Requirements DP-443396 InterSystems IRIS® data platform InterSystems IRIS® for Health HealthShare® Health Connect versions 2024.1.0 – 2024.1.4, 2024.2.0, 2024.3.0, and 2025.1.0, and 2025.1.1 HealthShare® Unified Care Record versions 2024.2 and 2025.1 Wrong Results: Low Risk Using Common Table Expressions in Dynamic SQL
oct 07 2025
Risk Category & Score Explicit Requirements HSHC-5268 HealthShare® Health Connect and InterSystems IRIS® for Health versions 2025.1.1 Functional: Medium Risk Occurs when performing SDA3 -> FHIR transformations involving the Encounter resource.
sept 30 2025
In InterSystems IRIS, InterSystems IRIS for Health, and HealthShare Health Connect, versions 2025.1.1 and 2025.2.0, the new “ Mirror Database Download” functionality does not include certain globals.
sept 24 2025
Risk Category & Score Explicit Requirements DP-444551 InterSystems IRIS® data platform InterSystems IRIS® for Health HealthShare® Health Connect versions 2025.1.0, 2025.1.1, and 2025.2
sept 03 2025
Risk Category & Score Explicit Requirements DP-442440 InterSystems IRIS ® data platform 2025.1.1.308.0 InterSystems IRIS for Health HealthShare® Health Connect Operational:
jul 23 2025
InterSystems IRIS 2025.2 introduces the IRISSECURITY database, the new home for security data. Unlike IRISSYS, the previous home for security data, IRISSECURITY can be encrypted, which secures your sensitive data at rest. In a future version, IRISSECURITY will be mirrorable.
jul 23 2025
InterSystems is pleased to announce the General Availability (GA) of the 2025.2 release of InterSystems IRIS® data platform. This is a Continuous Delivery (CD) release. Please note that the GA versions of InterSystems IRIS for Health™ and HealthShare® Health Connect™ 2025.2 are currently withheld due to mirroring limitations introduced by security updates (details below).
jun 10 2025
Advisory ID Product & Versions Affected Risk Category & Score Explicit Requirements DP-439649 Products: