285555 - FlagType::match uses a HAVING clause that PostgreSQL does not support

Status: RESOLVED FIXED

(Bugzilla :: Attachments & Requests, defect, P1)

Description

[Max Kanat-Alexander]

Apparently, PostgreSQL does not support having an alias in the HAVING clause.

Is there some way that we can restructure this code? I'm a bit lost here.

Here's a cleaned-up version of the error:

DBD::Pg::st execute failed: ERROR: Attribute "num_exclusions" not found

SELECT 1, flagtypes.id, flagtypes.name, flagtypes.description, 
flagtypes.cc_list, flagtypes.target_type, flagtypes.sortkey, 
flagtypes.is_active, flagtypes.is_requestable, flagtypes.is_requesteeble,
 flagtypes.is_multiplicable, flagtypes.grant_group_id, 
flagtypes.request_group_id, COUNT(flagexclusions.type_id) AS num_exclusions 
FROM flagtypes 
  INNER JOIN flaginclusions ON flagtypes.id = flaginclusions.type_id 
  LEFT JOIN flagexclusions ON (flagtypes.id = flagexclusions.type_id
            AND (flagexclusions.product_id = 1 
                 OR flagexclusions.product_id IS NULL)
            AND (flagexclusions.component_id = 1 
                 OR flagexclusions.component_id IS NULL)) 
WHERE 1=1 
      AND flagtypes.target_type = 'b' 
      AND (flaginclusions.product_id = 1 OR flaginclusions.product_id IS NULL)
      AND (flaginclusions.component_id = 1 
           OR flaginclusions.component_id IS NULL) 
GROUP BY flagtypes.id, flagtypes.name, flagtypes.description, 
         flagtypes.cc_list, flagtypes.target_type, flagtypes.sortkey, 
         flagtypes.is_active, flagtypes.is_requestable, 
         flagtypes.is_requesteeble, flagtypes.is_multiplicable, 
         flagtypes.grant_group_id, flagtypes.request_group_id
HAVING num_exclusions = 0 
ORDER BY flagtypes.sortkey, flagtypes.name

at Bugzilla/DB.pm line 69 
Bugzilla::DB::SendSQL('SELECT 1, flagtypes.id, flagtypes.name,
flagtypes.description...') called at Bugzilla/FlagType.pm line 148 
Bugzilla::FlagType::match('HASH(0x8f000d0)') called at Bugzilla/Bug.pm line 325 
Bugzilla::Bug::flag_types('Bugzilla::Bug=HASH(0x8e1e678)') called at
data/template/template/en/default/bug/edit.html.tmpl line 390
...
Template::process('Bugzilla::Template=HASH(0x8c81e68)','bug/create/created.html.tmpl','HASH(0x8c4ca5c)')
called at /var/www/html/dbinstall/post_bug.cgi line 523

Comment 1

[Myk Melez [:myk] [@mykmelez]]

HAVING is a restriction that happens in the result set after the query has run
and the result set has been generated.  A PostGreSQL-specific version of this
query could do the same thing in Perl after retrieving the result set but before
returning it to the code which requested it.

Comment 2

[Tomas Kopal]

The simplest is to just change "HAVING num_exclusions = 0" to "HAVING
COUNT(flagexclusions.type_id) = 0", if possible...

Comment 3

[Max Kanat-Alexander]

Attachment: Fix the HAVING clause to be Pg-compatible

I talked with myk in IRC, also, and looked at the Pg docs, and this is what I
came up with. :-) It works on Pg.

Comment 4

[Joel Peshkin]

The having goes away altogether if you fix the SQL so that it does the join
properly.

The flagtypes joins should be left joins and put a "xxx IS NOT NULL" in the
wherepart.  That will make boolean charts with flags work properly (they
currently do not, especially with negation) and will eliminate the HAVING issue
altogether.

Comment 5

[Tomas Kopal]

Comment on attachment 177081 [details] [diff] [review]
Fix the HAVING clause to be Pg-compatible

As per Joels comment.

Comment 6

[Max Kanat-Alexander]

Actually, I'd like to just do this Pg fix for now, and do the better fix later.
Flags are cryptic, so they tend to take up a lot of my time just trying to
figure them out and test them.

I believe that we already have a bug filed for the better fix, yes?

Comment 7

[Tomas Kopal]

Attachment: V2

What about this, is this what you were expecting, Joel? It actually cleaned the
code a bit and removed few GROUP BYs :-). Tested on Postgres only.

Comment 8

[Joel Peshkin]

Comment on attachment 177108 [details] [diff] [review]
V2


DBD::mysql::st execute failed: Mixing of GROUP columns (MIN(),MAX(),COUNT()...)
with no GROUP columns is illegal if there is no GROUP BY clause [for Statement
"SELECT 1, flagtypes.id, flagtypes.name, flagtypes.description,
flagtypes.cc_list, flagtypes.target_type, flagtypes.sortkey,
flagtypes.is_active, flagtypes.is_requestable, flagtypes.is_requesteeble,
flagtypes.is_multiplicable, flagtypes.grant_group_id,
flagtypes.request_group_id, COUNT(flags.id) FROM flagtypes LEFT OUTER JOIN
flags ON flagtypes.id = flags.type_id WHERE 1=1 AND flagtypes.target_type = 'b'
ORDER BY flagtypes.sortkey, flagtypes.name"] at Bugzilla/DB.pm line 69
	Bugzilla::DB::SendSQL('SELECT 1, flagtypes.id, flagtypes.name,
flagtypes.description...') called at Bugzilla/FlagType.pm line 142
	Bugzilla::FlagType::match('HASH(0x86da064)',1) called at
xxx/editflagtypes.cgi line 94
	main::list() called at xxxx/editflagtypes.cgi line 73

Comment 9

[Tomas Kopal]

Attachment: V2.1

Ok, I was a bit trigger-happy when cleaning up, sorry :-). Fixed.

Comment 10

[Joel Peshkin]

Comment on attachment 177351 [details] [diff] [review]
V2.1

r=joel if you've tested it

Comment 11

[Tomas Kopal]

(In reply to comment #10)
> (From update of attachment 177351 [details] [diff] [review] [edit])
> r=joel if you've tested it
> 

Yes, I tested it on both Postgres and MySQL.

Comment 12

[Myk Melez [:myk] [@mykmelez]]

Comment on attachment 177351 [details] [diff] [review]
V2.1

You need to update the comment as well to no longer reference use of a HAVING
clause.  Also, there's something that makes me uneasy about this fix.  As I
recall, there was a reason I used HAVING here, although I can't think of it
now.  We should be very careful about watching Bugzilla after making this
change to make sure we haven't introduced any regressions.

Comment 13

[Max Kanat-Alexander]

CC'ing our regression-finding experts. :-)

Comment 14

[Tomas Kopal]

Attachment: V2.2

(In reply to comment #12)
> (From update of attachment 177351 [details] [diff] [review] [edit])
> You need to update the comment as well to no longer reference use of a HAVING

> clause.

Ahhh, good catch, thanks. Fixed.

>  Also, there's something that makes me uneasy about this fix.  As I
> recall, there was a reason I used HAVING here, although I can't think of it
> now.	We should be very careful about watching Bugzilla after making this
> change to make sure we haven't introduced any regressions.

Pitty you don't remember it now. As far as I can tell, this should be
functionally identical (and I spent a lot of time thinking how it did work and
how it works now :-) ). So as you say, let's keep an eye on flags after this
lands and lets hope your feeling will not come true :-).

Comment 15

[Tomas Kopal]

Attachment: V2.2 unbitrotted

Just unbitrotted, no other change.

Comment 16

[Max Kanat-Alexander]

Checking in Bugzilla/FlagType.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/FlagType.pm,v  <--  FlagType.pm
new revision: 1.13; previous revision: 1.12
done