Advisory: Risk of Silent Wrong Results When Using New OFFSET Keyword in SQL Queries

Advisory ID	Product & Versions Affected	Risk Category & Score	Explicit Requirements
DP-445340	InterSystems IRIS® data platform InterSystems IRIS® for Health HealthShare® Health Connect versions 2025.1.0 – 2025.1.2, and 2025.2.0	Wrong Results: Low Risk	Using OFFSET keyword in SQL queries

In InterSystems IRIS versions 2025.1.0 – 2025.1.2 and 2025.2.0, SQL queries using the new OFFSET keyword to page through results may return wrong results when executed in parallel. This happens with both the ANSI-style OFFSET / FETCH syntax and the alternative LIMIT / OFFSET syntax. (This occurs because of an optimizer decision based on large table size or an explicit %PARALLEL hint.)

Queries that use LIMIT or FETCH without an OFFSET clause (or that use the preexisting TOP clause) are not affected.

The following queries are at risk because of this issue:

SELECT * FROM LargeTable OFFSET 50 FETCH 10
SELECT * FROM LargeTable LIMIT 10 OFFSET 50

The following queries are not affected by this issue because they explicitly avoid parallel execution:

SELECT * FROM %NOPARALLEL LargeTable OFFSET 50 FETCH 10
SELECT * FROM %NOPARALLEL LargeTable LIMIT 10 OFFSET 50

The following queries are not affected by this issue because they do not use the OFFSET keyword:

SELECT * FROM LargeTable LIMIT 10
SELECT * FROM LargeTable FETCH 10
SELECT TOP 10 * FROM LargeTable

This issue may cause InterSystems SQL to silently return wrong results. The incorrect results may manifest as either or both: missing rows that should have been returned and extra rows that do not match the paging criteria.

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

• 2025.1.3 is scheduled for availability on January 28, 2026.
• 2025.3.0 is scheduled for availability on November 19, 2025.
• In the interim, customers who require the correction prior to these releases may request an ad hoc distribution for the fix (DP-445425) through the Worldwide Response Center. 
• As a mitigation, customers have the option of modifying queries in either of the following ways:
  
  • Omit the OFFSET # clause and skip through the first # results manually
  • Include the %NOPARALLEL hint to ensure the query does not use parallelized execution

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