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.
Thanks.
--
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
