OData API does not return data containing the underscore (_) character
kbt165287
PRODUCTIssue
SmartObject call through the OData API Service does not return data containing the underscore (_) character when using the Equal (Eq) query filter.
Reproduction steps:
- Create SmartObject.
* ID
* Name
* Surname
- Generate Item View with All properties.
- Add a few records (At least 1 Name or Surname value must contain an Underscore).
- Navigate to Management - OData.
- Make sure the SmartObject is added as Return type for OData.
- Navigate to the OData URL.
- NOTE: All Values are returned.
- Add to QueryString ?$filter=Name eq 'Value1'.
-Change the Value1 to existing value containing NO underscore and WITH underscore.
- NOTE: Values without an underscore are retrieved, those without the underscore are not.
Symptoms
This has been confirmed as per design in K2 and OData.
The following Characters are reserved characters for the use of K2 OData:
- [
- %
- _
If any of these characters are used it will be placed inside [ ] thus the query string will try to return "eq 'Jon[_]Snow'" which does not exist in the values retrieved, resulting in 0 data found.
Resolution
This is as designed and will not change due to the huge risk impact.
As a workaround for now you can use the Odata 'substringof' function which returns all data as required.
Workaround
- Add to QueryString ?$filter=substringof('Value1', Name) eq true
- Change the Value1 to the existing value containing no underscore and with underscore
- NOTE: Values without underscore are retrieved, with underscore are also retrieved.