Empty data result when using SQLFormatter.InClause method with empty in-list (4381971)

With version 10.0 of Identity Manager, a Defect, 508199, has been fixed to change the loading and filtering behavior when the API is requested to load objects with an IN filter with the help of SQLFormatter’s method InClause.

Example: C#

Session.Source().GetCollection(
   Query.From("Person")
   .Where(SqlFormatter.InClause("UID_PersonHead", ValType.String, new string[0]))
   .SelectDisplays(), EntityCollectionLoadType.Slim);

Example: VB.Net

Session.Source().GetCollection(
   Query.From("Person") _
   .Where(SqlFormatter.InClause("UID_PersonHead", ValType.String, New String() {})) _
   .SelectDisplays(), EntityCollectionLoadType.Slim)

In earlier versions, the passed empty list resulted in the SQL where clause:

     WHERE ISNULL(UID_PersonHead, '') = ''

This would return in error all identities with no manager assigned (column has a NULL or empty value).

Now in version 10, the request translates to this SQL where clause:

     WHERE 1=0

This is the correct transformation when comparing an element to an empty set.

This is only relevant for an empty list without any elements in it. Having a (or multiple) element(s) with an empty string as part of the list still translates to a comparison of the column with a fixed empty string.

This is a fixed filter transformation and is by design. Please verify that there are no customization scripts in place that use the SQLFormatter.InClause passing an empty list (directly or by a variable) to compare a column to.

If in use, it is recommended that the customization be rewritten to check for an empty list in advance. In case empty lists as input for the InClause method are unexpected, let the code handle this situation before sending the API request and fetching unexpected results. This is specifically the case when the request filter also has OR conditions which makes the expected result even more unpredictable.

For use in comparison to an empty value, please use one of the following:

For UIDs:

• SQLFormatter.UidComparison

For any other column:

• SQLFormatter.Comparison

To directly filter for unpopulated columns:

• SQLFormatter.EmptyClause

All of the above methods expect a single reference value, not a list of values.