Skip to content
Search to learn about InterSystems products and solutions, career opportunities, and more.

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

Summary

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

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).

Latest Alerts & Advisories

Sign Up Today

Receive notifications on support alerts, critical issues,
fixes, and product releases.
*Required Fields
Highlighted fields are required
*Required Fields
Highlighted fields are required
By submitting this form, you give consent to receive notifications concerning support alerts, critical issues, important updates, fixes, and product releases via email. In addition, you consent to your business contact information being entered into our CRM solution that is hosted in the United States, but maintained consistent with applicable data protection laws.
**By clicking here, you give consent to be contacted for news, updates and other marketing purposes related to existing and future InterSystems products, offerings, and events.