SQL Query Stopped Working After Changing %String Property to Wrapper Types

Article

Joe Fu · Mar 7 2m read

#Indexing #Namespace #SQL #HealthShare #InterSystems IRIS

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!

Class dc.Username Extends %String { Parameter MAXLEN As INTEGER = 160; } Class dc.a Extends %Persistent { Index is1 On s1; Index is2 On s2; Index is3 On s3; Property s1 As %String; Property s2 As %Username; Property s3 As dc.Username; ClassMethod Test() { d ..%KillExtent() f s="joefu","JoeFu" { s t=..%New() s t.s1=s s t.s2=s s t.s3=s d t.%Save() } zw ^dc.aD,^dc.aI } }

select FIELD_NAME,DATATYPE,COLLATION_FUNCTION,MAXLEN from %Library.SQLCatalog_SQLFields('dc.a')

FIELD_NAME	DATATYPE	COLLATION_FUNCTION	MAXLEN
ID	%Library.BigInt	(null)	(null)
s1	%Library.String	SQLUPPER	50
s2	%Library.Username	SQLUPPER	160
s3	dc.Username	SQLUPPER	160
x__classname	%Library.RawString	(null)	(null)

select INDEX_NAME,FIELDS from %Library.SQLCatalog_SQLIndices('dc.a')

INDEX_NAME	FIELDS
is1	$$SQLUPPER({dc.a.s1})
is2	$$SQLUPPER({dc.a.s2})
is3	$$SQLUPPER({dc.a.s3})

USER>d ##class(dc.a).Test() ^dc.aD=2 ^dc.aD(1)=$lb("","joefu","joefu","joefu") ^dc.aD(2)=$lb("","JoeFu","JoeFu","JoeFu") ^dc.aI("is1"," JOEFU",1)="" ^dc.aI("is1"," JOEFU",2)="" ^dc.aI("is2"," JOEFU",1)="" ^dc.aI("is2"," JOEFU",2)="" ^dc.aI("is3"," JOEFU",1)="" ^dc.aI("is3"," JOEFU",2)=""

USER>w $zv IRIS for Windows (x86-64) 2024.3 (Build 217U) Thu Nov 14 2024 17:59:58 EST USER>w $$GetEnvironment^%apiOBJ("collation","%Library.String",.collval),! zw collval 1 USER>w $$GetEnvironment^%apiOBJ("collation","dc.Username",.collval),! zw collval 1 USER>zn "%SYS" %SYS>w $$GetEnvironment^%apiOBJ("collation","%Library.String",.collval),! zw collval 1 %SYS>w $$GetEnvironment^%apiOBJ("collation","%Library.Username",.collval),! zw collval 1

Vitaliy Serdtsev · 8 hr ago

Strangely, I had no issues with the %Library.Username type. I didn't explicitly specify the collation anywhere: neither in the data type class nor for the Namespace-wide.

Namespace '%SYS':

Class %Library.Username Extends %String
{

Parameter MAXLEN As INTEGER = 160;

}

Namespace 'USER':

select FIELD_NAME,DATATYPE,COLLATION_FUNCTION,MAXLEN from %Library.SQLCatalog_SQLFields('dc.a')



FIELD_NAME
DATATYPE
COLLATION_FUNCTION
MAXLEN


ID
%Library.BigInt
(null)
(null)


s1
%Library.String
SQLUPPER
50


s2
%Library.Username
SQLUPPER
160


s3
dc.Username
SQLUPPER
160


x__classname
%Library.RawString
(null)
(null)


select INDEX_NAME,FIELDS from %Library.SQLCatalog_SQLIndices('dc.a')



INDEX_NAME
FIELDS


is1
$$SQLUPPER({dc.a.s1})


is2
$$SQLUPPER({dc.a.s2})


is3
$$SQLUPPER({dc.a.s3})


USER>d ##class(dc.a).Test()
^dc.aD=2
^dc.aD(1)=$lb("","joefu","joefu","joefu")
^dc.aD(2)=$lb("","JoeFu","JoeFu","JoeFu")
^dc.aI("is1"," JOEFU",1)=""
^dc.aI("is1"," JOEFU",2)=""
^dc.aI("is2"," JOEFU",1)=""
^dc.aI("is2"," JOEFU",2)=""
^dc.aI("is3"," JOEFU",1)=""
^dc.aI("is3"," JOEFU",2)=""

0 0

Joe Fu · 3 hr ago

Hi Vitaliy,

Thanks for your experiment! This is very interesting. I tried to run the same query (as you did in the last step) on my class, and I got empty collation value for %Library.Username. Out of curiosity, what version of IRIS are you on? Could you try to run the commands in doc here to check namespace environment setting for collation value? https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls...

Vitaliy Serdtsev · 3 hr ago

1 0