SCIM returns faulty totalResults when the filter parameter is given with pagination parameters

[mifrazmurthaja]

Describe the issue: As per wso2/docs-is#1495, the totalResults should have the total result count with pagination parameters in JDBC userstore. However, when the filter parameter is given along with pagination parameters as follows, the value of the totalResults is incorrect.

https://localhost:9443/scim2/Users?startindex=1&count=3&filter=meta.resourceType+eq+User

The said behavior occurs, since the totalResults logic is implemented at [1][2], but not considered at [3].

[1] https://github.com/wso2-extensions/identity-inbound-provisioning-scim2/blob/v1.5.46/components/org.wso2.carbon.identity.scim2.common/src/main/java/org/wso2/carbon/identity/scim2/common/impl/SCIMUserManager.java#L477 [2] https://github.com/wso2-extensions/identity-inbound-provisioning-scim2/blob/v1.5.46/components/org.wso2.carbon.identity.scim2.common/src/main/java/org/wso2/carbon/identity/scim2/common/impl/SCIMUserManager.java#L518 [3] https://github.com/wso2-extensions/identity-inbound-provisioning-scim2/blob/v1.5.46/components/org.wso2.carbon.identity.scim2.common/src/main/java/org/wso2/carbon/identity/scim2/common/impl/SCIMUserManager.java#L475

Environment information

• Product Version: IS 5.11.0

Related issues:

7320

[rksk]

With the fix, it is working fine until the totalResults <= 100. Later, the totalResults count is returned as 100 for all. The totalResults is capped with the max user list limit userstore configuration.

[isuruirj]

There's a behavior change with the recent changes in this fix. As per https://github.com/wso2/docs-is/issues/1495, the totalResults should have the results per a page count with pagination parameters in LDAP userstore. However, when the filter parameter is given along with pagination parameters as follows, the value of the totalResults is incorrect.

curl -v -k --user admin:admin 'https://localhost:9443/scim2/Users?startIndex=1&count=10&filter=userName+sw+is'

Response: {"totalResults":25,"startIndex":1,"itemsPerPage":10," .......

Once we remove the filter parameter it's giving the results as expected.

curl -v -k --user admin:admin 'https://localhost:9443/scim2/Users?startIndex=1&count=10'

Response: {"totalResults":10,"startIndex":1,"itemsPerPage":10," ........

Product Version: IS-5.10.0 Userstore: LDAP

[tharakawijekoon]

We need to re-evaluate this fix, this is causing DB CPU to spike when scim2 APIs are called since the following query with the LIKE '%' is executed each time the API is called. SELECT COUNT(UM_USER_NAME) AS RESULT FROM UM_USER WHERE UM_USER_NAME LIKE '%' AND UM_TENANT_ID = -1234

[tharakawijekoon]

https://github.com/wso2/product-is/issues/15598