Description:
select avg(null) as `n`, collation(avg(null)) as `collate`, coercibility(avg(null)) as `coerce`, ifnull(avg(null),'a')='A' as `test`;
select min(null) as`n`, collation(min(null)) as `collate`, coercibility(min(null)) as `coerce`, ifnull(min(null),'a')='A' as `test`;
Notice that AVG always yields "binary" collation, but MIN returns the correct collation based on its arguments' collations. MAX and GROUP_CONCAT also work "as expected" (correct collations); STDDEV, SUM, VARIANCE, etc., all work the same way as AVG (binary collation regardless of arguments).
The reason seems to be that AVG, SUM, etc. are missing some collation propagation logic. GROUP_CONCAT explicitly supplies this logic, because whoever implemented it realized that they couldn't get away without it. MIN and MAX are special because they return references to (or copies of) their existing arguments, rather than constructing new values to return.
How to repeat:
mysql> select avg(null) as `n`, collation(avg(null)) as `collate`, coercibility(avg(null)) as `coerce`, ifnull(avg(null),'a')='A' as `test`;
+------+---------+--------+------+
| n | collate | coerce | test |
+------+---------+--------+------+
| NULL | binary | 4 | 0 |
+------+---------+--------+------+
1 row in set (0.00 sec)
mysql> select min(null) as`n`, collation(min(null)) as `collate`, coercibility(min(null)) as `coerce`, ifnull(min(null),'a')='A' as `test`;
+------+---------+--------+------+
| n | collate | coerce | test |
+------+---------+--------+------+
| NULL | binary | 6 | 1 |
+------+---------+--------+------+
1 row in set (0.00 sec)
mysql> select group_concat(null) as `n`, collation(group_concat(null)) as `collate`, coercibility(group_concat(null)) as `coerce`, ifnull(group_concat(null),'a')='A' as `test`;
+------+---------+--------+------+
| n | collate | coerce | test |
+------+---------+--------+------+
| NULL | binary | 6 | 1 |
+------+---------+--------+------+
Suggested fix:
AVG, SUM, etc. should use the same logic as MIN and MAX to determine the collation and coercibility of their results.
