Summary
Alert ID
|
Product & Versions Affected
| Risk Category & Score | Explicit Requirements |
DP-443396 |
| Wrong Results: Low Risk | Using Common Table Expressions in Dynamic SQL |
Issue
In InterSystems IRIS versions 2024.1.0 – 2024.1.4, 2024.2.0, 2024.3.0, 2025.1.0, and 2025.1.1, specific SQL queries issued through Dynamic SQL that use Common Table Expressions (CTE) may silently return wrong results. The issue only occurs when the query statement includes CTE definitions that involve query parameters, but these CTEs are not used in the query itself.
For example, the following query is affected:
WITH
aaa AS (SELECT * FROM t1 WHERE f = 'abc'),
bbb AS (SELECT * FROM t2 WHERE f = 'efg')
SELECT * FROM bbb,
because the CTE aaa is not used in the query itself and includes a query parameter ‘abc’.
When such statements are issued through Embedded SQL or over xDBC connections such as JDBC or ODBC, the issue does not occur.
Impact
This issue may cause InterSystems IRIS SQL to silently return wrong results.
Resolution
The issue has been corrected in versions 2024.1.5, 2025.1.2, 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-443588) through the Worldwide Response Center.
- As a mitigation, customers can simply remove the unused CTE definition from the statement.
For More Information
If you have questions or need assistance, please contact the InterSystems
Worldwide Response Center (WRC).