[Rt-devel] Command line query involving Custom Fields

[Rangarajan Radhakrishnan]

I have some custom field that I am trying to query as follows:
rt list "Queue = 'TestQueue' and owner = 'nobody' AND ('CF.{My Field1}' 
= 'My Value1')
(This works).

However, when I add another condition:
rt list "Queue = 'TestQueue' and owner = 'nobody' AND ('CF.{My Field1}' 
= 'My Value1' OR 'CF.{My Field1}' = 'My Value2')

It never returns any row. This has been tried from the graphical 
interface too.

Details:
Using RT 3.6.0pre0. Using 1.43 for DBIx::SearchBuilder. Have tried it 
with 1.39 of SearchBuilder.pm too.
Database is Oracle 10g (10.1)

Actual query used with "rt list" command is given below:
-------------------------------------
Queue = 'Assay Development'  AND  ('CF.{Assay Stage}' = 'Assay Request' 
OR 'CF.{Assay Stage}' = 'Feasibility' OR 'CF.{Assay Stage}' = 'Assay 
Development' OR 'CF.{Assay Stage}' = 'Assay Validation' OR 'CF.{Assay 
Stage}' = 'Sample Analysis' OR 'CF.{Assay Stage}' = 'Data Loading' )
-------------------------------------
It seems to breaks down even if I just use two clauses involving same 
customfield within parenthesis.

This results in QueryString (Select) as printed from within 
DBIx::SearchBuilder is:


SELECT * FROM ( SELECT limitquery.*,rownum limitrownum FROM ( SELECT 
main.* FROM ( SELECT DISTINCT main.id FROM (((Tickets main  LEFT JOIN 
ObjectCustomFields ObjectCustomFields_1  ON 
((ObjectCustomFields_1.ObjectId = '0')) OR (  
ObjectCustomFields_1.ObjectId = main.Queue))  LEFT JOIN CustomFields 
CustomFields_2  ON ( CustomFields_2.id = 
ObjectCustomFields_1.CustomField))  LEFT JOIN ObjectCustomFieldValues 
ObjectCustomFieldValues_3  ON ((ObjectCustomFieldValues_3.ObjectId = 
main.id)) AND (  ObjectCustomFieldValues_3.CustomField = 
CustomFields_2.id) AND ( (ObjectCustomFieldValues_3.Disabled = '0')) AND 
( (ObjectCustomFieldValues_3.ObjectType = 'RT::Ticket')))   WHERE 
((CustomFields_2.name = 'Assay Stage')) AND ((main.EffectiveId = 
main.id)) AND ((main.Status != 'deleted')) AND ((main.Type = 'ticket')) 
AND ((main.name = 'Assay Stage')AND(main.name = 'Assay 
Stage')AND(main.name = 'Assay Stage')AND(main.name = 'Assay 
Stage')AND(main.name = 'Assay Stage')) AND ((main.Queue = '3')AND (  ( 
(ObjectCustomFieldValues_3.Content = 'Assay Request') ) OR ( 
(ObjectCustomFieldValues_3.Content = 'Feasibility') ) OR ( 
(ObjectCustomFieldValues_3.Content = 'Assay Development') ) OR ( 
(ObjectCustomFieldValues_3.Content = 'Assay Validation') ) OR ( 
(ObjectCustomFieldValues_3.Content = 'Sample Analysis') ) OR ( 
(ObjectCustomFieldValues_3.Content = 'Data Loading') )  ) )  ) 
distinctquery, Tickets main WHERE (main.id = distinctquery.id)  ORDER BY 
main.id ASC  ) limitquery WHERE rownum <= 50 ) WHERE limitrownum >= 1

On running this query using SQLPLUS
ERROR at line 1:
ORA-00904: "MAIN"."NAME": invalid identifier

Has anybody seen this before? Thanks in advance for your help.