Broken "isn't" search for CF with multiple values

As I mentioned in an earlier message, reading through the code for
searching by Custom Field values led to finding another behavior that
I consider incorrect.

Basically, allowing CF’s to take multiple values is nice, but the
support for searching these CF’s isn’t there, and that reduces the
usefulness of the feature.

Once you allow multiple values in a single field, the search logic
gets more complicated. The behavior I consider intuitive is:

  1. For positive searches (“is”/“contains”), the search should be
    interpreted as “ANY of the values match this criterion”.

  2. For negative searches (“isn’t”/“doesn’t contain”), the search should be
    interpreted as “NONE of the values match this criterion”.

I’ve already mentioned case (1), and posted a patch to make RT behave
the way I want, so I won’t give examples here.

But the current behavior for case (2) is, IMHO, even worse.

Suppose we have a CF named “Tags” that accepts multiple values, and
suppose ticket 123 has values “A” and “B” for “Tags”.

So if the user searches for tickets that are not tagged with “A”,
ticket 123 won’t be found, right?

Wrong! :frowning:

The SQL that gets generated for this search will effectively find the
"B" tag, and since that meets the criterion “!= ‘A’”, the ticket will
be returned.

What we need is SQL something like this:
select main.* from Tickets main
where …
and not exists (select id from ObjectCustomFieldValues obj1
where obj1.Content = 'A’
and obj1.ObjectId = main.id)

I started to look into getting Tickets_Overlay.pm to do this, and
ran into a road-block: afaict, DBIx::SearchBuilder doesn’t provide a
way to generate this kind of SQL. I couldn’t find any support for
subselects.

I’ll be glad to work on a patch, if some one comes up with an approach
that doesn’t require restructuring lots of the existing code …

Harry