[rt-users] Re: [3.6][FIX] Searches by custom fields

[Joshua Colson]

On Thu, 2006-05-11 at 15:41 -0700, Joshua Colson wrote:
> On Thu, 2006-05-11 at 21:55 +0400, Ruslan Zakirov wrote:
> > I reproduced problem when people search by one field with several
> > restrictions, for example "CF.{cf1} = 'foo' OR CF.{cf1} = 'bar'".
> > Also, change should fix issue when RT generate wrong SQL query with
> > reference to 'main.name' column that doesn't exist.
> > 
> > If you see other issues feel free to send links to the archives.
> 
> Okay, after some digging, I found this SQL query (line numbers added):
> 
> ----------------------------------------------------------------
> 1  SELECT DISTINCT main.* 
> 2  FROM Tickets main  
> 3  LEFT JOIN ObjectCustomFields ObjectCustomFields_1  
> 4    ON ((ObjectCustomFields_1.ObjectId = '0')) 
> 5    AND(  ObjectCustomFields_1.ObjectId = main.Queue)
> 6  LEFT JOIN CustomFields CustomFields_2
> 7    ON ( CustomFields_2.id = ObjectCustomFields_1.CustomField)
> 8  LEFT JOIN ObjectCustomFieldValues ObjectCustomFieldValues_3
> 9    ON ((ObjectCustomFieldValues_3.ObjectId = main.id))
> 10   AND(ObjectCustomFieldValues_3.CustomField = CustomFields_2.id)
> 11   AND( (ObjectCustomFieldValues_3.Disabled = '0'))
> 12   AND( (ObjectCustomFieldValues_3.ObjectType = 'RT::Ticket'))
> 13 WHERE ((CustomFields_2.Name = 'Registrar-Lock'))
> 14   AND ((main.EffectiveId = main.id))
> 15   AND ((main.Status != 'deleted'))
> 16   AND ((main.Type = 'ticket'))
> 17   AND ((main.Queue = '3')
> 18   AND ( (ObjectCustomFieldValues_3.Content = 'Yes') ) )
> 19 ORDER BY main.id ASC
> 20 LIMIT 50;
> ----------------------------------------------------------------
> 
> I ran this against the DB and got zero results. So after closer
> inspection, it seems that this could only ever be true if the custom
> field is Global. The reason for this lies in line 5 of the statement.
> The aggregator should be 'OR', not 'AND'. If I change that, I get an
> accurate listing of tickets in the result set. However, I tried digging
> at the code to find where it is being set and haven't found it, yet. I
> figured I would send this to the list in case somebody knows off the the
> top of their head.

On further inspection, it seems that this query could *never* return
results, even with Global Custom Fields.

-- 
Joshua Colson <jcolson at voidgate.org>