Alert: Risk of Silent Wrong Results When Unused Common Table Expressions Appear in SQL Queries

Alert ID	Product & Versions Affected	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

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.

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

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.

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