Query Entity fails when using operator "in" against a very large array. It succeeds if I leave out the operator and return every result.

Question

I'm using a query against a table that contains a column with Appian usernames, and I am passing it a very large list of usernames to search for (3,900) using the "in" operator. When I call this query entity, I am getting the following error:

Expression evaluation error at function a!queryEntity: An error occurred while retrieving the data.

An example query entity that demonstrates the issue: 
 
 a!queryEntity(
 entity: cons!ENTITY_T_USER,
 query: a!query(
 filter: a!queryFilter(
 field: "UserName",
 operator: "in",
 value: touniformstring(enumerate(3000))
 ),
 pagingInfo: a!pagingInfo(
 startIndex: 1,
 batchSize: - 1
 )
 )
).data If you play with the size of the enumerate, it will work when it's lower (i.e. 2,000 still works for me), but fail when it gets higher. Setting a batchSize doesn't help (since that would effect the number of results returned, but this is failing because a filter is too long). 
 I've been able to work-around this issue by instead removing the filter, and using looping functions to filter the results after I return everything. This doesn't seem ideal, since it's now a multi-step process (get everything from db, then filter it - rather than using the query to filter).

Vinay Kumar Rai · Answer

Another approach is fetch all the Users from the User Table and use the appian function to retrieve the match Users based on UserName, 
 
=load( 
 local!AllUsers : rule!GetAllUsers(), 
 
 index( 
 local!AllUsers, 
 wherecontains( 
 touniformstring(enumerate(3000)), 
 tointeger(local!AllUsers.UserName) 
 ),{} 
 ) 
 
)

Carlos Santander · Answer

Given that this is nightly process that doesn't have user interaction, I don't think it's a big concern to make multiple trips to the database, so yes, you should try to break your list into smaller batches.

kondetiv · Answer

It looks like timeout issue. Since you are passing large array of usernames, it is taking long time. 
 
The maximum amount of time in seconds that a query waits for a response from the database before timing out is configured using the following property: 
 
conf.data.query.timeout=10 
 
The default value is 10 (ten seconds).