286227 - Boolean Chart for "Group Does Not Contain the String" fails to find bugs without groups

Status: RESOLVED INVALID

(Bugzilla :: Query/Bug List, defect)

Description

[Paulo Casanova]

User-Agent:       Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.6) Gecko/20050225 Firefox/1.0.1
Build Identifier: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.6) Gecko/20050225 Firefox/1.0.1

I'm trying to search for all public bugs in my bugzilla installation. I'm the
administrator so I can access all groups. I tried using the advanced boolean
charts but it doesn't work.

I only have one group: "Development Bugs". So I tried to put:

"Group" "does not contain the string" "development".

I get 0 bugs and, looking at the query I found: (marked with (...) uninteresting
parts :))

SELECT (...)
FROM
   bugs, 
   profiles AS map_assigned_to
   LEFT JOIN bug_group_map bug_group_map_0
      ON (bugs.bug_id = bug_group_map_0.bug_id)
   LEFT JOIN groups groups_0 ON (...)
   LEFT JOIN bug_group_map (...)
   LEFT JOIN cc (...)
WHERE bugs.assigned_to = map_assigned_to.userid
   AND ((INSTR(LOWER(groups_0.name), 'development') = 0))
   AND (...)
GROUP BY bugs.bug_id ORDER BY bugs.bug_id

With a quick analysis, the problem seems to be:
- public bugs have no entry in bug_group_map;
- public bugs will have group_0.* as null;
- any expression (instr, regexp, ...) with "null" is null and null is false;

Obviously :) some is null is missing somewhere.

Is it a condition that we're missing on the advanced search?

Reproducible: Always

Steps to Reproduce:

Comment 1

[Paulo Casanova]

BTW, I don't mind making a patch for this... the main problem is what should the
solution be (or is it possible to search and I'm missing something? :))

Comment 2

[Max Kanat-Alexander]

Looks like we need an IS NULL in the ON condition for that LEFT JOIN, I'd
imagine. Of course, how we should do that is up to Joel.

This might also be the intended behavior, in which case it should be doco'ed.

Comment 3

[Joel Peshkin]

That is how it is supposed to work.  You are searching for bugs that HAVE a
group that DOES NOT contain the string.  

You want to say that "There IS NO GROUP that DOES contain the string"

That is what negation is for.

Negate the chart and search for "group" "contains" "whatever"

Comment 4

[Max Kanat-Alexander]

OK, then we just need to doc this clearly in the Boolean Chart section.

Comment 5

[Paulo Casanova]

Ok, so let's see if I got it :)

I want all bugs *not* in group X. So you say I should say:

"NOT" "Group" "contains the string" "development".

I get Zarro bugs anyway. I guess this is because with the "NOT" BZ places in the
query

AND NOT((INSTR(LOWER(groups_0.name), 'development')))
    ^^^

However, if the bug doesn't belong to any group, groups_0.name is null and
INSTR(NULL,X) is NULL. However, NOT NULL is NULL :) Everything in SQL with NULL
is NULL except the "IS NULL" or "IS NOT NULL" operators :)

Should I reopen the bug? Or am I missing something?

Comment 6

[Joel Peshkin]

Which version?

That should work in the tip

Comment 7

[Paulo Casanova]

I'm currently using 2.19.2.