We were testing upgrades on our internal systems recently, and were initially surprised to see a change in some instances of SQL behavior. On further investigation, the changes make a lot of sense, and are easy to adapt to. But, to hopefully save you some time and provide additional information in case this is something you too find curious or interesting, we thought we would share our understanding of the changes and how we got around it to preserve previous behavior where desired.
Starting in IRIS version 2024.2.0, slight tweaks were made to SQL UNION and SELECT DISTINCT behaviors to consistently enforce the implicit DISTINCT function. The upgrade note is available on the Upgrade Impact Checklist here under "DP-429949: Apply collation for UNION"
Background
Previously, for UNION actions the field values were taken in their given state, and the column was assumed to use EXACT collation. This happened regardless of the fields' collation or the namespace default settings. This meant that the casing provided was the casing returned. In the new behavior, the default field collation for that column, which is typically SQLUPPER, will be applied to each value.
A similar change is seen with SELECT DISTINCT calls. Previously, a SELECT DISTINCT call returned the provided casing. In the new behavior, the default field collation for that column, which is typically SQLUPPER, will be applied to each value.
What to do if I see this come up?
If you would prefer to retain the previous provided casing behavior, like we needed to in a few distinct areas, there are a number of ways to enforce this.
(1) Add %EXACT to your fields.
Adding %EXACT to either UNION or SELECT DISTINCT queries will preserve the same behavior as original. %EXACT can be added to any columns for which you want to prevent the default SQLUPPER behavior.
See below for examples.
Returned results from a simple SELECT DISTINCT query prior to IRIS 2024.2.0:
SELECT DISTINCT FirstName FROM DNames| FirstName |
|---|
| David |
| Dimitri |
| Daniel |
| Darren |
| Dany |
Versus returned results with IRIS version in or after 2024.2.0:
| FirstName |
|---|
| DAVID |
| DIMITRI |
| DANIEL |
| DARREN |
| DANY |
Instead, in IRIS 2024.2.0 or newer using SELECT DISTINCT with %EXACT:
NOTE: Because %EXACT makes the returned column an expression, you will need to specify as <column name> to retain it in the result
SELECT DISTINCT %EXACT FirstName as FirstName FROM DNames| FirstName |
|---|
| David |
| Dimitri |
| Daniel |
| Darren |
| Dany |
The same %EXACT principle as applied to a UNION queries:
SELECT %EXACT FirstName as AllDNames FROM DNames
UNION
SELECT %EXACT LastName FROM DNames| FirstName |
|---|
| David |
| Dimitri |
| Daniel |
| DeStefanis |
| De Lorenzo |
(2) Change from using UNION to UNION ALL.
UNION ALL returns the results without applying the default SQLUPPER collation.
It is important to note that UNION and UNION ALL have varying behavior. UNION filters out duplicate rows in cases where all columns in the results are the same, whereas UNION ALL does not. If your dataset has no duplicate rows (for example, say each row has a unique %GUID or other identifier), then UNION ALL will return the exact same results as UNION.
However, if your data does contains duplicates which need to be filtered, this will not work as a valid replacement.
See below for examples.
Returned results from a simple UNION query prior to 2024.2.0:
SELECT FirstName as AllDNames FROM DNames
UNION
SELECT LastName FROM DNames| FirstName |
|---|
| David |
| Dimitri |
| Daniel |
| DeStefanis |
| De Lorenzo |
Versus returned results with IRIS version in or after 2024.2.0:
| FirstName |
|---|
| DAVID |
| DIMITRI |
| DANIEL |
| DESTEFANIS |
| DE LORENZO |
Instead, in IRIS 2024.2.0 or newer using UNION ALL:
SELECT FirstName as AllDNames FROM DNames
UNION ALL
SELECT LastName FROM DNames| FirstName |
|---|
| David |
| Dimitri |
| Daniel |
| DeStefanis |
| De Lorenzo |
(3) Override the default SQLUPPER collation behavior namespace wise.
From the documentation, "Each namespace has a current string collation setting. This string collation is defined for the data type in %Library.StringOpens in a new tab. The default is SQLUPPER. This default can be changed." The documentation provides instructions for how to easily change this default.
Please note that because this change is namespace wide, you should be careful to test thoroughly for unintended affects upon making the change.