Advisory: Incomplete Query Results with ‘ORDER BY <row ID field> DESC’ – HealthShare

February 11, 2021 – Advisory: Incomplete Query Results with ‘ORDER BY <row ID field> DESC’ – HealthShare

InterSystems has corrected a defect that can cause incomplete query results.  This defect affects the platforms underlying HealthShare and HealthShare Health Connect:

• InterSystems IRIS and InterSystems IRIS for Health 2019.1.0, 2019.1.1, and 2020.1.0

See Alert: Incomplete Query Results with ‘ORDER BY <row ID field> DESC’ for further detail

Customers using HealthShare Personal Community should evaluate the number of proxy relationships they have established or removed. Customers that have established or removed proxy relationships more than 64,000 times are at risk for this defect.

Other than Personal Community, this defect does not affect query logic in the HealthShare or HealthShare Health Connect software.  However, Customers that have developed custom queries using the functions provided by InterSystems IRIS for Health, could be affected if they have the following HealthShare products and versions:

• HealthShare 2019.2, 2020.1 and 2020.2
• Health Connect 2019.1.0, 2019.1.1, 2020.1.0

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.

Users 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

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

This advisory is also on the InterSystems Product Alerts and Advisories page

If you have any questions regarding this advisory, please contact the Worldwide Response Center (WRC) at support@InterSystems.com or  +1.617.621.0700.