Likely Bug: Searching Pg Indexed content ignores NOT

RT Developers
1

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.

Thanks
Jim

2

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:

Needing to handle negative searches is for all databases, not just Pg · bestpractical/rt@4a6338c · GitHub

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