[rt-devel] Likely Bug: Searching Pg Indexed content ignores NOT

[Thomas Sibley]

On 11/02/2012 01:43 PM, jim.h.berry at frb.gov wrote:
> Hi List,
> 
> Using rt-4.0.8,  but this applies to earlier releases as well.  If
> searching over Postgres indexed content, RT ignores NOT.  For example,
>  the search
> 
>         Content LIKE 'apple' AND Content NOT LIKE 'banana'  
> is processed as
>         Content LIKE 'apple' AND Content LIKE 'banana'  
> 
> A simple check in Tickets.pm to add " !!" to the Postgres search operand
>  and a change in SearchBuilder.pm to allow "@@ !!" seems to fix this.  I
> would guess that searches on indexed Oracle and MySQL content would have
> the same problem with NOT,  but I don't know the appropriate fix.
> 
> Please let me know if I am missing something.   This negative search
> might be weird on tickets with multiple transactions, but if RT isn't
> going to honor NOT in this situation, then it would be better to abort
> rather than perform a different search.

You're not missing something.  It's a known lacking, unfortunately:

   https://github.com/bestpractical/rt/commit/4a6338c7

The full patch for Pg and Oracle shouldn't be too difficult, although I
may be lacking proper context.  Negation under MySQL may prove trouble
because of the syntax for querying Sphinx sometimes interacts poorly
with MySQL's optimizer, but I haven't thought it through.  (Content LIKE
"foo" AND Content LIKE "bar" is turned into sphinx_column="foo" AND
sphinx_column="bar" which MySQL optimizes as "never possible".  This
will hopefully be much better when MySQL has stable native FTS.)