Incorrect Filtering Results for Negative Number Attributes Using “Between” Operator in Clarity PPM Workspaces
Article ID: 442701
Updated On:
Products
Issue/Introduction
In Clarity PPM, across workspaces, incorrect results are returned when applying filters using the Between operator on number-type attributes containing negative values. Records with negative numbers are not filtered as expected, resulting in inaccurate output.
Steps to Reproduce:
- Log in to Clarity PPM with appropriate access rights.
- Navigate to Administration → Objects.
- Create one or more Number type attributes on an existing object and populate the API ID to make them available in the Modern UX (MUX).
- Populate the newly created attributes with both positive and negative values (for example:
100,200,-100,-200). - Navigate to the object workspace.
- Apply a filter using the created number attribute.
- Select the Between operator and define the filter range as
-100to-200.
Actual Result:The filter returns zero results.
Expected Result:The filter should return records where the attribute values fall within the specified negative range (-100 to -200).
Environment
Clarity all supported version
Cause
The BETWEEN operator is part of the standard ANSI SQL specification, which means almost all relational databases handle it the exact same way. Because it evaluates this strictly from left to right, putting the larger number first (BETWEEN -100 AND -200) will always result in zero rows being returned, regardless of whether you are using PostgreSQL, Oracle, MS SQL.
Resolution
When filtering a range in a database, the system always reads from the lowest mathematical value to the highest mathematical value (left to right on a number line). Because -200 is a lower number than -100, we must ask the database for values BETWEEN -200 AND -100 so it can correctly find everything in that window.
Additional Information
Documentation from database vendors on how to use between operator
Feedback
