[rt-users] search query returning unexpected results

[Chanel Wheeler]

YLN Topic is a mandatory single value dropdown list. 

Here's the SQL that was generated:

SELECT main.* FROM Tickets main LEFT JOIN ObjectCustomFieldValues ObjectCustomFieldValues_1  ON ( ObjectCustomFieldValues_1.CustomField = '2' ) AND ( ObjectCustomFieldValues_1.Disabled = '0' ) AND ( ObjectCustomFieldValues_1.ObjectType = 'RT::Ticket' ) AND ( ObjectCustomFieldValues_1.ObjectId = main.id )  WHERE (main.IsMerged IS NULL) AND (main.Status != 'deleted') AND (main.Type = 'ticket') AND (main.Owner = '30' AND  ( main.Status = 'new' OR main.Status = 'open' )  AND main.Queue = '3' AND  (  (  ( ObjectCustomFieldValues_1.Content != 'Student Import System' OR ObjectCustomFieldValues_1.LargeContent IS NOT NULL )  )  OR ObjectCustomFieldValues_1.id IS NULL ) )


But I discovered something else.  I did some more experimentation but this time I was logged in as root. The query works fine if I'm logged in as root. When I'm logged in as myself (general ticket owner, queue moderator, etc.), it ignores the YLN Topic constraint. That makes me think it's a permissions issue. 

More digging ...

Aha! Figured it out. I had created two user groups and mostly set permissions via those which worked fine for everything else we do with custom fields. I had not turned on "View custom field values" for Privileged users (we already could see and use them just fine) and that turned out to be what made the difference.  It works now.

Thanks Kevin for your help. And hopefully my documented struggle will help someone in the future.

chanel

> -----Original Message-----
> From: rt-users [mailto:rt-users-bounces at lists.bestpractical.com] On Behalf
> Of Kevin Falcone
> Sent: Wednesday, September 03, 2014 2:08 PM
> To: rt-users at lists.bestpractical.com
> Subject: Re: [rt-users] search query returning unexpected results
> 
> On Tue, Aug 26, 2014 at 02:05:41PM -0700, Chanel Wheeler wrote:
> > This is the search query I have (built via the Query Builder):
> >
> > Owner = 'cwheeler' AND (  Status = 'new' OR Status = 'open' ) AND Queue =
> 'YLN'
> > AND 'CF.{YLN Topic}' != 'Student Import System'
> >
> > The search results *include* the tickets tagged with Student Import
> > System. If I change the ‘not equal’ to ‘equal’ it limits the search as you’d
> expect it to.
> > Same thing happens with LIKE vs NOT LIKE. This is looking a lot like a
> > bug but I’m wondering if manipulating custom fields in this way just isn’t
> allowed.
> > I’m on 4.2.6.
> 
> You neglected to tell us what kind of Custom Field YLN Topic is.
> 
> If I make a global CF called Foo and make it an enter one value and have two
> tickets, one where Foo has no value and one where Foo has the value foo
> and I ask for
>     CF.foo != 'foo'
> I correctly get 1 ticket.
> 
> You can find the code I posted earlier today for seeing the SQL generated by
> a query and run that and show us what you get for your != query.
> 
> -kevin