Written by

Article Joe Fu · Mar 7 2m read

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

#HealthShare #InterSystems IRIS #Indexing #Namespace #SQL

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!

Discussion (0)0

Add reply

Comments

Vitaliy Serdtsev · Mar 10

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':

<FONT COLOR="#000080">Class %Library.Username Extends %String
</FONT><FONT COLOR="#000000">{
</FONT><FONT COLOR="#000080">Parameter </FONT><FONT COLOR="#000000">MAXLEN </FONT><FONT COLOR="#000080">As </FONT><FONT COLOR="#000000">INTEGER = </FONT><FONT COLOR="#000080">160</FONT><FONT COLOR="#000000">;
}</FONT>

Namespace 'USER':

<FONT COLOR="#000080">Class dc.Username Extends %String
</FONT><FONT COLOR="#000000">{
</FONT><FONT COLOR="#000080">Parameter </FONT><FONT COLOR="#000000">MAXLEN </FONT><FONT COLOR="#000080">As </FONT><FONT COLOR="#000000">INTEGER = </FONT><FONT COLOR="#000080">160</FONT><FONT COLOR="#000000">;
}</FONT>
<FONT COLOR="#000080">Class dc.a Extends %Persistent
</FONT><FONT COLOR="#000000">{
</FONT><FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">is1 </FONT><FONT COLOR="#000080">On </FONT><FONT COLOR="#000000">s1;
</FONT><FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">is2 </FONT><FONT COLOR="#000080">On </FONT><FONT COLOR="#000000">s2;
</FONT><FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">is3 </FONT><FONT COLOR="#000080">On </FONT><FONT COLOR="#000000">s3;
</FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">s1 </FONT><FONT COLOR="#000080">As %String</FONT><FONT COLOR="#000000">;
</FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">s2 </FONT><FONT COLOR="#000080">As %Username</FONT><FONT COLOR="#000000">;
</FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">s3 </FONT><FONT COLOR="#000080">As dc.Username</FONT><FONT COLOR="#000000">;
</FONT><FONT COLOR="#000080">ClassMethod </FONT><FONT COLOR="#000000">Test()
{
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000000">..</FONT><FONT COLOR="#0000ff">%KillExtent</FONT><FONT COLOR="#000000">()
  
  </FONT><FONT COLOR="#0000ff">f </FONT><FONT COLOR="#808000">s</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008000">"joefu"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"JoeFu" </FONT><FONT COLOR="#800080">{
    </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#808000">t</FONT><FONT COLOR="#000000">=..</FONT><FONT COLOR="#0000ff">%New</FONT><FONT COLOR="#000000">()
    </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#808000">t</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">s1</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#808000">s
    </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#808000">t</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">s2</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#808000">s
    </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#808000">t</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">s3</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#808000">s
    </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#808000">t</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">%Save</FONT><FONT COLOR="#000000">()
  </FONT><FONT COLOR="#800080">}
  </FONT><FONT COLOR="#0000ff">zw </FONT><FONT COLOR="#000000">^dc.aD,^dc.aI
}
}</FONT>

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 Mar 10 to Vitaliy Serdtsev

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.cl…

0 0

Vitaliy Serdtsev Mar 10 to Joe Fu

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

0 0