InterSystems has corrected two issues that can cause a small number of SQL queries to return incorrect results. In addition, InterSystems has corrected an inconsistency in date/time datatype handling that may lead to different, unexpected – yet correct – results for existing applications that rely on the earlier, inconsistent behavior.
DP-436825: SQL Queries with Lateral Join May Return Wrong Results
The first issue (DP-436825) only affects SQL queries that use a lateral join, either implicitly or explicitly, on an instance configured with non-default per-process memory limit (" bbsiz" parameter in the .cpf file). A clean install of InterSystems IRIS has a default bbsiz of -1 (so there is no memory limit), while an upgrade from an older version would preserve the previous setting. When such a query uses parallelized execution at runtime, including cases where the system uses parallelized execution, the query may return incorrect results.
This issue affects versions 2023.3, 2024.1.0, 2024.1.1, 2024.1.2, 2024.2, and 2024.3 of the following products:
- InterSystems IRIS® data platform
- InterSystems IRIS® for Health
- HealthShare® Health Connect
The issue also affects other InterSystems products based on the above products including HealthShare® Unified Care Record and Suite: Version 2024.2 Although the Unified Care Record and suite doesn’t make use of SQL queries that use a lateral join in product code, HealthShare customers who upgrade to HealthShare 2024.2 with a non-default per-process memory limit that also write their own SQL queries that use a lateral join may be affected.
To avoid the issue, use one of the following three options:
- Remove the per-process memory limit by setting the bbsiz parameter to -1.
- Use the %NOPARALLEL keyword in queries that include a lateral join.
- Temporarily disable Adaptive Mode for the instance, which avoids automatic parallel processing of eligible queries
These all ensure that the query returns correct results.
The correction for this defect is identified as DP-436825 and will be included in all future product releases beginning with 2024.1.3 and 2025.1.0. It is also available via ad hoc distribution.
DP-436998: SQL queries with reverse sort by %ID entering endless loop
The second issue (DP-436998) impacts SQL queries that order by descending row ID, and in which the row ID is a positive (bitmap-compatible) integer and in particular, data-specific circumstances. Under these conditions, the query may enter an endless loop and keep returning the same set of results until it is aborted.
This issue affects versions 2022.2, 2022.3, 2023.x, and 2024.x of the following products:
- InterSystems IRIS® data platform
- InterSystems IRIS® for Health
- HealthShare® Health Connect
It also affects other InterSystems products based on the above products including HealthShare® Unified Care Record and Suite: Version 2024.1 and version 2024.2. Although the Unified Care Record and suite doesn’t make use of SQL queries with reverse sort by %ID in product code, HealthShare 2024.1 and HealthShare 2024.2 customers that write their own SQL queries with reverse sort by %ID may be affected.
The correction for this defect is identified as DP-436998. It will be included in all future product releases beginning with 2023.1.6, 2024.1.3, and 2025.1.0. It is also available via ad hoc distribution.
DP-436633: SQL Queries Comparing DATE and TIMESTAMP Values
InterSystems recently corrected an inconsistency in how DATE and TIMESTAMP values are compared using the <=, >, and BETWEEN operators. This correction changes the results of particular date and time comparisons.
The updated behavior was introduced in version 2023.3 of
- InterSystems IRIS® data platform
- InterSystems IRIS® for Health
- HealthShare® Health Connect
It also affects other InterSystems products based on the above products including HealthShare® Unified Care Record and Suite: Version 2024.2.
It applies to all subsequent releases.
With the updated behavior, DATE values are coerced to a TIMESTAMP value prior to the comparison. This conforms to the SQL standard of coercing the less-precise datatype to the most precise datatype.
For example, with the new behavior (which is standard-compliant), a query predicate “MyTimeStamp > MyDate” evaluates to FALSE when values for these fields correspond to the same calendar date, except when MyTimestamp corresponds exactly to midnight. Previously, the behavior was only fully standard-compliant when the MyTimeStamp field was defined with %PosixTime format, and returned non-compliant results in certain cases when using %Timestamp or certain combinations of special functions and variables.
To ensure predictable comparisons, InterSystems recommends using explicit CAST functions, especially when using CASE statements or SQL functions such as GETDATE(), NVL() and IFNULL(), where the resulting type may not be obvious.
To help assess which statements are affected by the change in behavior, InterSystems has introduced a warning in the query plan, and an optional system flag to make such statements raise an error at runtime. Enabling the flag can be helpful during regression testing of application code, and can offer an additional safety net for user queries at runtime. This informational feature is identified as DP-436633 and will be included in all future product releases beginning with 2024.1.4 and 2025.1.0.
Further Information
If you have any questions regarding this alert, please contact the
Worldwide Response Center.