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 <[EMAIL PROTECTED]> _______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com We're hiring! Come hack Perl for Best Practical: http://bestpractical.com/about/jobs.html