Alert: Incomplete Query Results with ‘ORDER BY DESC’

InterSystems has corrected a defect that can cause incomplete query results. This defect affects:

• InterSystems IRIS and InterSystems IRIS for Health 2019.1.0, 2019.1.1, 2019.2, 2019.3, 2019.4, 2020.1.0, 2020.2, and 2020.3
• HealthShare Health Connect 2019.1.0, 2019.1.1, 2020.1.0
• HealthShare Products 2019.2, 2020.1 and 2020.2

(In HealthShare and HealthShare Health Connect, this defect only affects Personal Community with 64,000 patient proxies and possibly customer-built custom queries. For more details on how this defect affects HealthShare, please see Advisory: Incomplete Query Results with ‘ORDER BY DESC’-HealthShare)

A query can trigger the defect only if it meets all of the following conditions:

• The query contains an ORDER BY clause sorting in descending (DESC) order
• The field used for that sorting is the table’s row ID
• The query contains conditions on multiple indexed fields
• The query selects data from only one table

You can modify an individual query to ensure correct results by replacing

ORDER BY <row ID field> DESC

with

ORDER BY <row ID field>+0 DESC

Note that some InterSystems search utilities, including the Interoperability Message Viewer, use queries that could be affected by this defect and cannot be edited. The only way to mitigate this defect system-wide is to install a version that includes the corrections.

The corrections for this defect are identified as DP-402745 and DP-403046 and will be included in all future product releases. They are also available via Ad hoc distribution from the InterSystems Worldwide Response Center (WRC).

If you have any questions regarding this alert, please contact the Worldwide Response Center.