(no subject)

Some people are mean to cats in October.

(no subject)

I don't suppose any of you speak SQL?

Quoth Mozilla:

This is one of those cases where it's best to do explicit inner joins instead of
the implicit comma joins, which is, in fact, what we did to fix it...

2.18 has this:

> push @supptables, "profiles AS map_assigned_to";
> push @wherepart, "bugs.assigned_to = map_assigned_to.userid";

where 2.20 has this:

> push @supptables, "INNER JOIN profiles AS map_assigned_to " .
> "ON bugs.assigned_to = map_assigned_to.userid";

I suspect that 2.18 will no longer be supported by the time MySQL 5 is
considered production-ready, so we probably don't need to rush on backporting
this. The type of change needed to Search.pm is fairly high-risk, and I'd be
hesitant to backport it anyway, even if it was considered production-ready,
since 2.20 and up work. Anyone who can run a cutting-edge MySQL can run a
cutting-edge Bugzilla.

What they have failed to realise, however, is that some of us are wanting a non-stone-age MySQL while still being stuck with Testrunner, which can't upgrade past 2.18.

The explanation of exactly what's going on comes from the MySQL team, here:

This is a change that was made in 5.0.15 to make MySQL more compliant with the standard.
According to the SQL:2003

(from clause) ::= FROM (table reference list)
(table reference list) ::=
(table reference) [ { (comma) (table reference) }... ]
(table reference) ::=
(table factor)
| (joined table)
(joined table) ::=
(cross join)
| (qualified join)
| (natural join)
...

Thus when you write

... FROM t1 , t2 LEFT JOIN t3 ON (expr)

it is parsed as

(1) ... FROM t1 , (t2 LEFT JOIN t3 ON (expr))

and not as

(2) ... FROM (t1 , t2) LEFT JOIN t3 ON (expr)

so, from expr you can only refer to columns of t2 and t3 - operands of the join.
Workaround - to put parentheses explicitly as in (2). Then you can refer to t1 columns
from expr.

So:
Here's the statement that's causing the problem.

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, COUNT(flagexclusions.type_id) AS num_exclusions

FROM flagtypes , flaginclusions

LEFT JOIN flagexclusions ON (flagtypes.id = flagexclusions.type_id AND (flagexclusions.product_id = 4 OR flagexclusions.product_id IS NULL) AND (flagexclusions.component_id = 21 OR flagexclusions.component_id IS NULL))

WHERE 1=1 AND flagtypes.target_type = 'b' AND flagtypes.id = flaginclusions.type_id AND (flaginclusions.product_id = 4 OR flaginclusions.product_id IS NULL) AND (flaginclusions.component_id = 21 OR flaginclusions.component_id IS NULL)

GROUP BY flagtypes.id HAVING num_exclusions = 0

ORDER BY flagtypes.sortkey, flagtypes.name

How do I go from this statement, to one with the change as described above? SQL is Greek to me in all the important ways - I can see what they're trying to do, I can read most of the letters, but the verbs and grammar escape me almost completely.

Would I be correct in thinking I just need to put parentheses around the From statement, so that it is FROM (flagtypes , flaginclusions)? If so, why isn't that working?

And as an antidote to the geekiness: