Alert: Incorrect Query Results with Non-Standard ‘GROUP BY’ Query

July 21, 2021 – Alert: Incorrect Query Results with Non-Standard ‘GROUP BY’ Query

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

• All major releases and maintenance versions of InterSystems IRIS and InterSystems IRIS for Health, starting with 2019.1.0

A query block may encounter the defect only if it meets all the following conditions:

• The query block contains a GROUP BY clause but does not include any aggregates, such as COUNT(*).
• The SELECT clause includes a field that is not in the GROUP BY clause*, and the query block contains a WHERE condition on that field.
• The results of the query block can be determined entirely from a single index, and that index starts with the field in the GROUP BY clause of the query block.

*: note that this specific use of non-grouping fields in the SELECT clause is an InterSystems SQL Extension and not part of the SQL Standard.

Some examples of affected and unaffected queries are shown at the end of this document.

The result of the defect is that the WHERE condition in the above list is not correctly applied to the query results. Hence, the query can return rows that do not meet the conditions of the query.

The correction for this defect is identified as AK1043 and will be included in all future product releases, including InterSystems IRIS and InterSystems IRIS for Health 2020.1.2 and 2021.1.1. It is also available via Ad hoc distribution from the InterSystems Worldwide Response Center (WRC).

Note to HealthShare Customers

InterSystems has reviewed this issue against the HealthShare family of products and found that they do not contain any queries that match the above criteria. HealthShare customers using InterSystems IRIS features independent of their HealthShare product installation will need to review their logic and any customizations to determine whether they could be impacted by this defect.

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

Examples

For illustration purposes, below is an example of a table (test.TestTable) with three integer properties and an index on two of those properties:

Property Int1 As %Integer;

Property Int2 As %Integer;

Property Int3 As %Integer;

Index GroupBy On (Int1, Int2);

Some examples of affected queries:

SELECT Int1, Int2 FROM test.TestTable WHERE Int2 > 1 GROUP BY Int1

• This query meets all the conditions outlined above. As a result of the defect, this query also returns rows with Int2 <= 1.

SELECT Int1, ID FROM test.TestTable WHERE Int2 <= 1 GROUP BY Int1

• This query is also affected because the ID field is included in every index, resulting in this query returning rows with Int2 > 1.

Some examples of unaffected queries:

SELECT Int1, Int2 FROM test.TestTable WHERE Int2 > 1

• This query does not include a GROUP BY clause, so it is unaffected.

SELECT Int1, Int2, COUNT(*) FROM test.TestTable WHERE Int2 > 1 GROUP BY Int1

• This query contains an aggregate in the SELECT list, so it is unaffected.

SELECT Int1, Int2 FROM test.TestTable WHERE Int1 > 1 GROUP BY Int1

SELECT Int1, Int2 FROM test.TestTable GROUP BY Int1

• Neither of these queries contain a WHERE condition on a field that is NOT in the GROUP BY clause, so they are unaffected.

SELECT Int1, Int2 FROM test.TestTable WHERE Int1 > 1 GROUP BY Int2

• The table’s compound index does not start with the field in the GROUP BY clause, so this query is unaffected.

SELECT Int1, Int2 FROM test.TestTable WHERE Int2 > 1 AND Int3 > 1 GROUP BY Int1

SELECT Int1, Int3 FROM test.TestTable WHERE Int2 > 1 GROUP BY Int 1

• These queries both use the value of Int3, which is not stored in the index. Since they cannot be resolved using only the index, they are unaffected.