12.20.4 Detection of Functional Dependence

The following query selects, for each country, a count of spoken languages:

co.Code is a primary key of co, so all columns of co are functionally dependent on it, as expressed using this notation:

Thus, co.name is functionally dependent on GROUP BY columns and the query is valid.

A UNIQUE index over a NOT NULL column could be used instead of a primary key and the same functional dependence would apply. (This is not true for a UNIQUE index that permits NULL values because it permits multiple NULL values and in that case uniqueness is lost.)

This query selects, for each country, a list of all spoken languages and how many people speak them:

The pair (cl.CountryCode, cl.Language) is a two-column composite primary key of cl, so that column pair uniquely determines all columns of cl:

Moreover, because of the equality in the WHERE clause:

And, because co.Code is primary key of co:

“Uniquely determines” relationships are transitive, therefore:

As a result, the query is valid.

As with the previous example, a UNIQUE key over NOT NULL columns could be used instead of a primary key.

An INNER JOIN condition can be used instead of WHERE. The same functional dependencies apply:

Whereas an equality test in a WHERE condition or INNER JOIN condition is symmetric, an equality test in an outer join condition is not, because tables play different roles.

Assume that referential integrity has been accidentally broken and there exists a row of countrylanguage without a corresponding row in country. Consider the same query as in the previous example, but with a LEFT JOIN:

For a given value of cl.CountryCode, the value of co.Code in the join result is either found in a matching row (determined by cl.CountryCode) or is NULL-complemented if there is no match (also determined by cl.CountryCode). In each case, this relationship applies:

cl.CountryCode is itself functionally dependent on {cl.CountryCode, cl.Language} which is a primary key.

If in the join result co.Code is NULL-complemented, co.Name is as well. If co.Code is not NULL-complemented, then because co.Code is a primary key, it determines co.Name. Therefore, in all cases:

Which yields:

As a result, the query is valid.

However, suppose that the tables are swapped, as in this query:

Now this relationship does not apply:

Indeed, all NULL-complemented rows made for cl will be put into a single group (they have both GROUP BY columns equal to NULL), and inside this group the value of co.Name can vary. The query is invalid and MySQL rejects it.

Functional dependence in outer joins is thus linked to whether determinant columns belong to the left or right side of the LEFT JOIN. Determination of functional dependence becomes more complex if there are nested outer joins or the join condition does not consist entirely of equality comparisons.

Suppose that a view on countries produces their code, their name in uppercase, and how many different official languages they have:

This definition is valid because:

In the view result, the first selected column is co.Code, which is also the group column and thus determines all other selected expressions:

MySQL understands this and uses this information, as described following.

This query displays countries, how many different official languages they have, and how many cities they have, by joining the view with the city table:

This query is valid because, as seen previously:

MySQL is able to discover a functional dependency in the result of a view and use that to validate a query which uses the view. The same would be true if country2 were a derived table (or common table expression), as in:

MySQL is able to combine all of the preceding types of functional dependencies (key based, equality based, view based) to validate more complex queries.