We recently changed the 'UserID" property in a "User" class from type of %String to be %Library.Username. This is for better consistency across our codebase regarding MAXLEN limit.
%Library.Username is a system wrapper datatype which extends %String and has a MAXLEN of 160. This change should have minimal/no impact on code behavior. However, we found that some SQL query cannot return expected rows after the change. Query will return empty values even if the entry is in the table.
After investigation, we found that re-building index could solve the issue, which means the data type change caused some inconsistency for existing index on the property. But why would a simple wrapper class of String make any difference in SQL table?
==================================================================================
Answer:
The reason index stopped working is that it was built when the property was still a %String.
So what's the difference between %Library.String and %Library.Username? They are both %String. But aside from MAXLEN, another key difference is the default COLLATION.
In SQL, by default, an index on a given property (or properties) uses the collation type of the property data. For %Library.String, there is a system-wide (namespace-wide) default COLLATION="SQLUPPER" (this default can be changed, see the documentation below). This means that if we have a row with UserID="JoeFu", it’s stored in the UserIDIndex global as " JOEFU".
For WHERE clause comparison: UserID="JoeFu", with default collation of UserID="SQLUPPER", the SQL engine evaluates it as %SQLUPPER(UserID)=%SQLUPPER("JoeFu").
However, when we switch to %Library.Username, there is no default COLLATION anymore (sadly, even if this type is pretty much just %String). Now, without "SQLUPPER" collation, SQL engine evaluates this equal condition as it is: UserID="JoeFu", then query optimizer decides to use the index built upon UserID, without rebuilding this index, we still have %SQLUPPER(UserID) in this index global. When it looks for "JoeFu" without such collation, it cannot find a match.
NOTE: when creating/using datatype that replacing %String, should pay attention to the usage of the property and its index, it's very possible that you need to set COLLATION="SQLUPPER" to match things in the existing SQL table
See https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_stringmanipulation#RSQL_stringmanipulation_desc for SQL string collation.
See https://docs.intersystems.com/iris20243/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_collation#GSQL_collation_ns for the setting of default collation on IRIS
See https://docs.intersystems.com/iris20243/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_collation#GSQL_collation_indexdef for Index collation.
Thanks Yuchen Liu for answering my initial question!