SearchEngine produces wrong results with multiple OR criteria groups
Status: Reported
Description
Build a search:
- Find all associates where
- (Role_id = 1 OR role_id=2)
- AND
- (Group_idx =2 OR group_idx=3)
With this script for search:
#setLanguageLevel 3;
// Build SQL to find associate SearchEngine seAssociate; seAssociate.addField ("associate.associate_id"); seAssociate.addField ("associate.group_idx.name");
// add role criteria seAssociate.addCriteria("associate.(UserRoleLink->associate_id).role_id","OperatorEquals","1","OperatorOr",2); seAssociate.addCriteria("associate.(UserRoleLink->associate_id).role_id","OperatorEquals","2","OperatorAnd",2);
// Add criteria for groups seAssociate.addCriteria("associate.group_idx","OperatorEquals","2","OperatorOr",3); seAssociate.addCriteria("associate.group_idx","OperatorEquals","3","OperatorAnd",3);
printLine ("SQL: " + seAssociate.buildSql());
printLine(seAssociate.executeHTMLTable());
|
The SearchEngine SQL is:
select a0.associate_id,a1.name from associate a0 left join UserGroup a1 on (a1.UserGroup_id = a0.group_idx) left join UserRoleLink a2 on (a2.associate_id = a0.associate_id) where (((a2.role_id = ? or a2.role_id = ?) and ((a0.group_idx = ? or a0.group_idx = ?))))
Result:
associate.associate_id | associate.group_idx.name |
2 | DA:"Administration";US:"Administration" |
16 | DA:"Salg";US:"Sales" |
18 | DA:"Salg";US:"Sales" |
19 | DA:"Salg";US:"Sales" |
20 | DA:"Salg";US:"Sales" |
Observe: the first row not expected, should not be there.
Translated to MSSQL with values like in script:
select a0.associate_id,a1.name from crm7.associate a0 left join crm7.UserGroup a1 on (a1.UserGroup_id = a0.group_idx) left join crm7.UserRoleLink a2 on (a2.associate_id = a0.associate_id) where (((a2.role_id = 1 or a2.role_id = 2) and ((a0.group_idx = 2 or a0.group_idx = 3)))) |
Result
associate_id | Name |
16 | DA:"Salg";US:"Sales" |
18 | DA:"Salg";US:"Sales" |
19 | DA:"Salg";US:"Sales" |
20 | DA:"Salg";US:"Sales" |
Details
| Issue id | 25596 |
| Registered | 9 Mar 2022 |
| Last modified | 5 Mar 2024 |
| Severity | Medium |
| Area | Service |
| Status | Reported |
| Target release | |
| Type | Bug |