889650 - Searching using "Match ALL of the following against the same field" for the field "Flags" throws "Table 'bugs_g2' doesn't exist" SQL error

Status: RESOLVED FIXED

(Bugzilla :: Query/Bug List, defect)

Description

[Simon Green]

Attachment: screen grab

See attached screen grab from Bugzilla 4.4 landfill. When doing this search, the following error is returned:

DBD::mysql::db selectcol_arrayref failed: Table 'bugs_bugzilla4_4_branch.bugs_g2' doesn't exist [for Statement "SELECT bugs.bug_id AS bug_id, map_product.name AS product, bugs.priority AS priority, bugs.bug_severity AS bug_severity
  FROM bugs
LEFT JOIN bug_group_map AS security_map ON bugs.bug_id = security_map.bug_id AND NOT ( security_map.group_id IN (7,6) )
LEFT JOIN cc AS security_cc ON bugs.bug_id = security_cc.bug_id AND security_cc.who = 57595
INNER JOIN products AS map_product ON bugs.product_id = map_product.id
INNER JOIN priority AS map_priority ON bugs.priority = map_priority.value
INNER JOIN bug_severity AS map_bug_severity ON bugs.bug_severity = map_bug_severity.value
LEFT JOIN bugs AS bugs_g2 ON bugs.bug_id = bugs_g2.bug_id
 WHERE bugs.creation_ts IS NOT NULL
   AND (security_map.group_id IS NULL
        OR (bugs.reporter_accessible = 1 AND bugs.reporter = 57595)
        OR (bugs.cclist_accessible = 1 AND security_cc.who IS NOT NULL)
        OR bugs.assigned_to = 57595
        OR bugs.qa_contact = 57595)
   AND  map_product.classification_id IN (1)  AND ( 1 = 1 AND ( EXISTS (
        SELECT 1
          FROM bugs_g2 bugs_2
          LEFT JOIN attachments AS attachments_2
                    ON bugs_2.bug_id = attachments_2.bug_id
          LEFT JOIN flags AS flags_2
                    ON bugs_2.bug_id = flags_2.bug_id
                       AND (flags_2.attach_id = attachments_2.attach_id
                            OR flags_2.attach_id IS NULL)
          LEFT JOIN flagtypes AS flagtypes_2
                    ON flags_2.type_id = flagtypes_2.id
     WHERE bugs_2.bug_id = bugs_g2.bug_id
           AND INSTR(CONCAT(flagtypes_2.name, flags_2.status), 'tahi') > 0
    ) ) AND ( EXISTS (
        SELECT 1
          FROM bugs_g2 bugs_2
          LEFT JOIN attachments AS attachments_2
                    ON bugs_2.bug_id = attachments_2.bug_id
          LEFT JOIN flags AS flags_2
                    ON bugs_2.bug_id = flags_2.bug_id
                       AND (flags_2.attach_id = attachments_2.attach_id
                            OR flags_2.attach_id IS NULL)
          LEFT JOIN flagtypes AS flagtypes_2
                    ON flags_2.type_id = flagtypes_2.id
     WHERE bugs_2.bug_id = bugs_g2.bug_id
           AND INSTR(CONCAT(flagtypes_2.name, flags_2.status), 'rua') > 0
    ) ) )
GROUP BY bugs.bug_id
ORDER BY map_priority.sortkey, map_priority.value, map_bug_severity.sortkey, map_bug_severity.value
LIMIT 500
"] at Bugzilla/Search.pm line 710
	Bugzilla::Search::data('Bugzilla::Search=HASH(0x9b5dfe8)') called at /var/www/html/bugzilla-4.4-branch/buglist.cgi line 736

Comment 1

[Frédéric Buclin]

glob, any chance to fix this bug before the release of Bugzilla 4.4.1?

Comment 2

[:glob ✱]

(In reply to Frédéric Buclin from comment #1)
> glob, any chance to fix this bug before the release of Bugzilla 4.4.1?

no; i have not done any work on this issue (i'm not the assignee).

i can say it's because on mysql a subselect cannot use an outer table alias, and would probably be significant effort to resolve.

Comment 3

[Frédéric Buclin]

(In reply to Byron Jones ‹:glob› from comment #2)
> and would probably be significant effort to resolve.

So not a blocker, but a fix is still welcome.

Comment 4

[Peter Jackson]

I can see the same issue on the current installation of Bugzilla, with the table "bugs.bugs_g3", which is slightly different to the current summary.

https://bugzilla.mozilla.org/buglist.cgi?list_id=12321243&j2=AND_G&o1=substring&o4=notregexp&v1=tracking-b2g&v4=.*4.4&f1=target_milestone&o3=regexp&v3=approval.*&resolution=---&f4=flagtypes.name&query_format=advanced&f3=flagtypes.name&f2=OP&f5=CP&f6=flagtypes.name

DBD::mysql::db selectcol_arrayref failed: Table 'bugs.bugs_g3' doesn't exist [for Statement "SELECT bugs.bug_id AS bug_id, bugs.priority AS priority, bugs.bug_severity AS bug_severity FROM bugs LEFT JOIN bug_group_map AS security_map ON bugs.bug_id = security_map.bug_id AND NOT ( security_map.group_id IN (69) ) LEFT JOIN cc AS security_cc ON bugs.bug_id = security_cc.bug_id AND security_cc.who = 321641 INNER JOIN priority AS map_priority ON bugs.priority = map_priority.value INNER JOIN bug_severity AS map_bug_severity ON bugs.bug_severity = map_bug_severity.value LEFT JOIN bugs AS bugs_g3 ON bugs.bug_id = bugs_g3.bug_id WHERE bugs.creation_ts IS NOT NULL AND (security_map.group_id IS NULL OR (bugs.reporter_accessible = 1 AND bugs.reporter = 321641) OR (bugs.cclist_accessible = 1 AND security_cc.who IS NOT NULL) OR bugs.assigned_to = 321641 OR bugs.qa_contact = 321641) AND bugs.resolution IN ('') AND ( INSTR(bugs.target_milestone, 'tracking-b2g') > 0 AND ( 1 = 1 AND ( EXISTS ( SELECT 1 FROM bugs_g3 bugs_3 LEFT JOIN attachments AS attachments_3 ON bugs_3.bug_id = attachments_3.bug_id LEFT JOIN flags AS flags_3 ON bugs_3.bug_id = flags_3.bug_id AND (flags_3.attach_id = attachments_3.attach_id OR flags_3.attach_id IS NULL) LEFT JOIN flagtypes AS flagtypes_3 ON flags_3.type_id = flagtypes_3.id WHERE bugs_3.bug_id = bugs_g3.bug_id AND CONCAT(flagtypes_3.name, flags_3.status) REGEXP 'approval.*' ) ) AND ( NOT( EXISTS ( SELECT 1 FROM bugs_g3 bugs_3 LEFT JOIN attachments AS attachments_3 ON bugs_3.bug_id = attachments_3.bug_id LEFT JOIN flags AS flags_3 ON bugs_3.bug_id = flags_3.bug_id AND (flags_3.attach_id = attachments_3.attach_id OR flags_3.attach_id IS NULL) LEFT JOIN flagtypes AS flagtypes_3 ON flags_3.type_id = flagtypes_3.id WHERE bugs_3.bug_id = bugs_g3.bug_id AND CONCAT(flagtypes_3.name, flags_3.status) REGEXP '.*4.4' ) ) ) ) ) GROUP BY bugs.bug_id ORDER BY map_priority.sortkey, map_priority.value, map_bug_severity.sortkey, map_bug_severity.value LIMIT 500 "]

Comment 5

[Frédéric Buclin]

Bugzilla 4.4 is now restricted to security fixes only.

Comment 7

[Rainer Plischke]

Unfortunatly this bug still applies to Bugzilla 5.2.

Comment 8

[BMO Github Automation]

Attachment: [bugzilla/bugzilla] [5.2] Bug 889650: Fix crash when searching "match all of the same field" on Flags (#237)

Comment 9

[Dave Miller [:justdave]]

I'm going to take this on 5.0.4 as well, since it actually fixes a crash.

Comment 10

[BMO Github Automation]

Attachment: [bugzilla/bugzilla] [5.0.4] Bug 889650: Fix crash when searching "match all of the same field" on… (#238)

Comment 11

[Dave Miller [:justdave]]

Comment on attachment 9617940 [details] [review]
[bugzilla/bugzilla] [5.0.4] Bug 889650: Fix crash when searching "match all of the same field" on… (#238)

this is a duplicate

Comment 12

[BMO Github Automation]

Attachment: [bugzilla/harmony] Bug 889650: Fix crash when searching "match all of the same field" on Flags (#179)

Comment 13

[Dave Miller [:justdave]]

Comment on attachment 9617939 [details] [review]
[bugzilla/bugzilla] [5.0.4] Bug 889650: Fix crash when searching "match all of the same field" on… (#238)

Landed on 5.0.4
https://github.com/bugzilla/bugzilla/commit/473fd2347baa4966029833962a987ee943b79d75

Comment 14

[Dave Miller [:justdave]]

Comment on attachment 9617938 [details] [review]
[bugzilla/bugzilla] [5.2] Bug 889650: Fix crash when searching "match all of the same field" on Flags (#237)

Landed on 5.2:
https://github.com/bugzilla/bugzilla/commit/d859529265b30a92ed7b60701d70fad5dcf60d2d

Comment 15

[Dave Miller [:justdave]]

I missed that the harmony version wasn't ready yet (status said it was, so I started landing everything, but then got to that one and the text of it said it wasn't, after that status change in the timeline). I revoked the approval on it until the requested changes are addressed.

Comment 16

[Dave Miller [:justdave]]

Comment on attachment 9620002 [details] [review]
[bugzilla/harmony] Bug 889650: Fix crash when searching "match all of the same field" on Flags (#179)

Landed on harmony:
https://github.com/bugzilla/harmony/commit/e30fa7fc20c386d8195dfc176599124a94973ad8