Alert: Risk of Silent Wrong Results When Using Field Expressions Repeatedly in SQL Queries

Alert ID	Product & Versions Affected	Risk Category & Score	Explicit Requirements
DP-445015	InterSystems IRIS® data platform  InterSystems IRIS® for Health  HealthShare® Health Connect  versions 2020.4.0, 2021.1.0 – 2021.1.4, 2021.2.0, 2022.1.0 – 2022.1.7, 2022.2.0, 2022.3.0, 2023.1.0 – 2023.1.7, 2023.2.0, 2023.3.0, 2024.1.0 – 2024.1.5, 2024.2.0, 2024.3.0, 2025.1.0 – 2025.1.2, and 2025.2.0	Wrong Results: Low Risk	Using a SQL query with an expression in the WHERE clause that includes arithmetic operators in the BETWEEN clause or specific unary functions with at least two occurrences.

In the versions specified above, a SQL query may return wrong results when it meets either of the following conditions:

• The query uses a BETWEEN operator that involves an arithmetic expression of a field in the WHERE clause
• The query uses the exact same arithmetic expression involving a unary function applied to a field more than once (with at least once occurrence in the WHERE clause). The affected unary functions are:
  
  • unary negation or %MINUS()
  • unary +, TO_NUMBER() or %PLUS()
  • collation functions such as %SQLUPPER(), %SQLSTRING(), %UPPER(), and %ALPHAUP
  • %KEY and %VALUE predicates on collection properties
  • TRUNCATE()

Note that a BETWEEN clause counts as two conditions; this means that a query using such an expression in a BETWEEN clause is affected by this issue.

For example, the following statement is affected because it meets the first condition:

SELECT * FROM t1 WHERE 4*(c1+2) BETWEEN 0 AND 1
SELECT * FROM t1 WHERE (1-TO_NUMBER(c1))/2 BETWEEN 0 AND 1

The following statements are affected because they meet the second condition: they include an expression involving a unary function that occurs more than once:

SELECT -c1 FROM t1 WHERE -c1 > 0
SELECT * FROM t1 WHERE -c1 > 0 HAVING0 < -c1
SELECT * FROM t1 WHERE -c1 > 0 ORDER BY –c1             
SELECT * FROM t1 WHERE -c1-1 > 0 AND -c1-1 < 5
SELECT * FROM t1 WHERE %MINUS(c1) > 0 AND 0 < %MINUS(c1)+2
SELECT TRUNCATE(c2,2) FROM t1 WHERE TRUNCATE(c2,2)-20>0
SELECT favoriteColors FROM sample.person WHERE FOR SOME %ELEMENT(favoriteColors) ( %KEY = 2 OR %KEY + 1 = 7 ) ORDER BY 1

The following statements are not affected, as there is no BETWEEN operator used with an arithmetic expression (first condition), and no single expression with a unary function appears more than once (second condition):

SELECT c1 FROM t1 WHERE -c1 > 0
SELECT * FROM t1 WHERE 2*c1 > 0 AND 0 < TO_NUMBER(c1)
SELECT 2*c1 FROM t1 WHERE 2*c1 > 1
SELECT * FROM t1 WHERE c1/2 > 1 or c1/2 < 5
SELECT * FROM t1 WHERE 2*c1+3 > 0 AND 0 < -c1

This issue may cause InterSystems SQL to silently return wrong results by replacing the repeated field expression with a different value during processing. This can cause various problems:

• Some rows may be wrongly excluded or included in the result.
• There may be mistakes in result set ordering.
• Columns based on that field reference may have incorrect values.

The issue has been corrected in versions 2024.1.6, 2025.1.3, 2025.3.0 and any more recent version.

• 2025.3.0 is scheduled for availability on November 19, 2025.
• 2025.1.3 is scheduled for availability on January 28, 2026.
• 2024.1.6 is scheduled for availability on April 15, 2026.

In the interim, customers who require the correction prior to these releases may request an ad hoc distribution for the fix (DP-445844) through the Worldwide Response Center. 

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