I think we’ve found a bug in custom field searching - I can’t offer a
patch, but here’s what I’ve found out.
The symptom is that searching on a queue custom field with the criterion
"CF does not contain R14 " returns tickets that do contain R14 in the CF.
It seems that this problem occurs when there is more than one custom field
in the system with the same name (attached to different queues).
Looking at the sql the search generates, and at _CustomFieldLimit, it
appears that the CustomFields table is joined in the query only by name
which in our case is not enough to uniquely identify the correct row.
What I don’t understand, and why I haven’t been able to fix this, is that
_CustomFieldLimit always seems to receive the “field” name argument as
“{Software Version}” (with no queue specifier), so $queue remains zero, the
correct custom field row is never loaded and the join uses a custom field
name match.
The versions I’ve seen this on are 3.4.2 and 3.4.5.
Here’s the sql generated by the search (prettied up a bit):
SELECT *
FROM
( SELECT limitquery.,rownum limitrownum FROM
( SELECT main. FROM ( SELECT DISTINCT main.id FROM
((Tickets main
LEFT JOIN CustomFields CustomFields_1 ON ( CustomFields_1.Name =
‘Software Version’))
LEFT JOIN ObjectCustomFieldValues ObjectCustomFieldValues_2
ON ( ObjectCustomFieldValues_2.CustomField = CustomFields_1.id)
AND( (ObjectCustomFieldValues_2.Disabled = ‘0’))
AND( (ObjectCustomFieldValues_2.ObjectId = main.id))
AND( (ObjectCustomFieldValues_2.ObjectType = ‘RT::Ticket’)))
WHERE
((main.EffectiveId = main.id)) AND ((main.Status != ‘deleted’)) AND
((main.Type = ‘ticket’)) AND ((main.Queue = ‘953’)AND (
(ObjectCustomFieldValues_2.Content NOT LIKE ‘%R14%’)
OR(ObjectCustomFieldValues_2.Content IS NULL) ) ) ) distinctquery, Tickets
main WHERE (main.id = distinctquery.id) ORDER BY main.id ASC )
limitquery WHERE rownum <= 50 ) WHERE limitrownum >= 1
Steve