5.2 Apparent Bug in Multi Select Column Search

[ashgreen]

Hello,

I have a custom field, with multiple values. Created in 4.XX and recently upgraded to 5.2

I was not searching this field until 5.2 so am not sure if this is a new or pre-existing issue.

Data appears to be stored for the multi value colum in the form

X^,[^Y^,]^Z

i.e.

3^,^14^,^23^,^2

The issue I have been experiencing is, when I run a search I'm quite often receiving results I did not expect to receive.

I ran a trace and the query was as follows:

 SELECT  TOP 21  contacts.id  ,contacts_cstm.custom_classification_c, ISNULL(contacts.first_name,'') + ' ' + 
ISNULL(contacts.last_name,'') as name, contacts.first_name , contacts.last_name , contacts.salutation  , accounts.name account_name, 
jtl0.account_id account_id, contacts.title , contacts.phone_work , contacts.assigned_user_id  FROM contacts   LEFT JOIN contacts_cstm 
ON contacts.id = contacts_cstm.id_c   LEFT JOIN  accounts_contacts jtl0 ON contacts.id=jtl0.contact_id  AND jtl0.deleted=0
 LEFT JOIN  accounts accounts ON accounts.id=jtl0.account_id AND accounts.deleted=0
 AND accounts.deleted=0 where ((contacts.first_name like 'Luke%' ) AND ( contacts.last_name like 'ha%' ) AND ( 
contacts_cstm.custom_classification_c like '3' or contacts_cstm.custom_classification_c like '%3^%' or 
contacts_cstm.custom_classification_c like '%^3%' or contacts_cstm.custom_classification_c like '%^3^%')) AND contacts.deleted=0

As can be seen, the issue appears quite obvious. The search is searching for too broad a match of '3'. While the middle values are wrapped by a ^ on either side, the start and end are not. The two matches '%^3%' and '%3^%' are too broad and will return results including (for example) '...^34^...'

In many ways this whole mechanism should probably be updated as it appears to be fairly repetative to check 4 times for a single value. However for the purposes of finding a quick and simple fix removing the non ^ % seems appropriate?

i.e.

 SELECT  TOP 21  contacts.id  ,contacts_cstm.custom_classification_c, ISNULL(contacts.first_name,'') + ' ' + 
ISNULL(contacts.last_name,'') as name, contacts.first_name , contacts.last_name , contacts.salutation  , accounts.name account_name, 
jtl0.account_id account_id, contacts.title , contacts.phone_work , contacts.assigned_user_id  FROM contacts   LEFT JOIN contacts_cstm 
ON contacts.id = contacts_cstm.id_c   LEFT JOIN  accounts_contacts jtl0 ON contacts.id=jtl0.contact_id  AND jtl0.deleted=0
 LEFT JOIN  accounts accounts ON accounts.id=jtl0.account_id AND accounts.deleted=0
 AND accounts.deleted=0 where ((contacts.first_name like 'Luke%' ) AND ( contacts.last_name like 'ha%' ) AND ( 
contacts_cstm.custom_classification_c like '3' or contacts_cstm.custom_classification_c like '%3^%' or 
contacts_cstm.custom_classification_c like '%^3%' or contacts_cstm.custom_classification_c like '%^3^%')) AND contacts.deleted=0

The highlighted %s are causing the issue and should be removed. These appear to be for checking the end values and are un-needed. Note, as the use of ^s has been adopted it seems to me it would make sense to put them around all values, including start and end, including when there is only 1 selection. Thus the code could be modified to having a single match check [CODE] like '%^X^%'.

I also realise it would be possible to fix this issue by ensuring all "name" values are complete unique strings of equal length. However number as they are what 4.X defaulted to and requiring a unique set length name seems like a work around non-technical staff won't appreciate.

Has anyone else experienced this issue and have any better work arounds?

What are the timelines for bug fixes?

Regards,
-Ash

Additional - No customisation of any of the php code or sql database has taken place