Summary
Alert ID
|
Product & Versions Affected
| Risk Category & Score | Explicit Requirements |
DP-442892 |
| Wrong Results: High Risk | Specific 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).