ONLY_FULL_GROUP_BY refuses a select item identical to a GROUP BY expression with COLLATE (ERROR 1055), unless the collation is binary

Under ONLY_FULL_GROUP_BY, a select item that is the same expression as a GROUP BY expression is accepted, but not when the expression carries COLLATE: the column inside it is then reported as not in GROUP BY. The one COLLATE that is accepted is COLLATE `binary`.

SET sql_mode= 'ONLY_FULL_GROUP_BY';

CREATE TABLE t1 (s VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin, b VARBINARY(20), l VARCHAR(20) CHARACTER SET latin1);

INSERT INTO t1 VALUES ('Ab', 'x', 'x'), ('ab', 'y', 'y'), ('b', 'x', 'X'), (NULL, NULL, NULL);

SELECT LOWER(s), COUNT(*) FROM t1 GROUP BY LOWER(s);

SELECT LOWER(s) COLLATE utf8mb4_bin, COUNT(*) FROM t1 GROUP BY LOWER(s) COLLATE utf8mb4_bin;

SELECT s COLLATE utf8mb4_general_ci, COUNT(*) FROM t1 GROUP BY s COLLATE utf8mb4_general_ci;

SELECT l COLLATE latin1_bin, COUNT(*) FROM t1 GROUP BY l COLLATE latin1_bin;

SELECT b COLLATE `binary`, COUNT(*) FROM t1 GROUP BY b COLLATE `binary`;

SELECT LOWER(s) COLLATE utf8mb4_bin AS k, COUNT(*) FROM t1 GROUP BY k;

SELECT LOWER(s) COLLATE utf8mb4_bin, COUNT(*) FROM t1 GROUP BY 1;

DROP TABLE t1;

Leads to, on 12.3.3 and 13.0.2 alike:

GROUP BY LOWER(s)                       3 groups (NULL 1, ab 2, b 1)

GROUP BY LOWER(s) COLLATE utf8mb4_bin   ERROR 1055 (42000): 'test.t1.s' isn't in GROUP BY

GROUP BY s COLLATE utf8mb4_general_ci   ERROR 1055 (42000): 'test.t1.s' isn't in GROUP BY

GROUP BY l COLLATE latin1_bin           ERROR 1055 (42000): 'test.t1.l' isn't in GROUP BY

GROUP BY b COLLATE `binary`             3 groups (NULL 1, x 2, y 1)

GROUP BY k (the item's alias)           3 groups

GROUP BY 1                              3 groups

Without ONLY_FULL_GROUP_BY the second query answers (3 groups).

Expected: every query answers. MySQL 8.4.13 under ONLY_FULL_GROUP_BY answers the same kind of queries grouped by (LOWER(s) COLLATE utf8mb4_bin) and (s COLLATE utf8mb4_bin).

From the code: find_order_in_list() matches the GROUP BY expression against the select list through find_item_in_list(), which calls find->eq(item, 0) while the GROUP BY item is not yet fixed. Item_func_set_collation::eq() (sql/item_strfunc.cc) also requires collation.collation == item->collation.collation, but collation is set only by fix_length_and_dec(); until then it is DTCollation's default, &my_charset_bin, while the COLLATE clause itself is held in m_set_collation. So the unfixed GROUP BY expression equals the fixed select item only when the COLLATE resolves to binary, which is the one form accepted above. Comparing m_set_collation instead would let identical expressions match.

A CAST(... AS CHAR) inside such an expression is refused for a separate reason, MDEV-39835 (Item_char_typecast::eq() compares typeid(this) with typeid(item)), so the examples above avoid CAST. Related: MDEV-25133 and MDEV-12922.