Condition pushdown below GROUP BY changes the query result: HAVING c = 'A' COLLATE utf8mb4_bin counts 1 row for the group of 'A' and 'a'

Likely cause: commit eb2c1474752a5f743db638d5b06612c9e3f07f74 (MDEV-9197), in the check that is now Item_func::excl_dep_on_grouping_fields(). The HAVING pushdown of MDEV-7486 uses the same check. CS 10.11.19 and ES 10.6.28-24 have it too.

SET NAMES utf8mb4;

CREATE TABLE t1 (c VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci);

INSERT INTO t1 VALUES ('A'),('a');

SELECT COUNT(*) INTO @c FROM t1 GROUP BY c HAVING c = 'A' COLLATE utf8mb4_bin;

--let $c= query_get_value(SELECT @c AS c, c, 1)

if ($c != 2)

{

  --die The group of 'A' and 'a' has 2 rows, but HAVING c = 'A' COLLATE utf8mb4_bin returns it with COUNT(*) $c

}

DROP TABLE t1;

Leads to:

mysqltest: At line 8: The group of 'A' and 'a' has 2 rows, but HAVING c = 'A' COLLATE utf8mb4_bin returns it with COUNT(*) 1

The pushdown from HAVING into WHERE changes the query result. With optimizer_switch='condition_pushdown_from_having=off', the query gives 2. The query also gives Warning 1292 Truncated incorrect BOOLEAN value: 'A'. The pushdown into a derived table gives 1 as well, for SELECT cnt FROM (SELECT c, COUNT(*) AS cnt FROM t1 GROUP BY c) AS dt WHERE dt.c = 'A' COLLATE utf8mb4_bin, and for the same query with COUNT(*) OVER (PARTITION BY c) and no GROUP BY. With condition_pushdown_for_derived=off, both give 2. A key on c and a prepared statement give the same. Optimized builds give the same results.

MySQL has no pushdown from HAVING into WHERE, and no window functions before 8.0. MySQL 8.0 and 9.1 give 1 for the two derived table queries as well:

+--------+--------+-------------------------+-----------------------------+

| MySQL  | HAVING | Derived table, GROUP BY | Derived table, PARTITION BY |

+--------+--------+-------------------------+-----------------------------+

| 5.5.62 | 2      | 2                       | -                           |

| 5.6.51 | 2      | 2                       | -                           |

| 5.7.44 | 2      | 2                       | -                           |

| 8.0.36 | 2      | 1                       | 1                           |

| 9.1.0  | 2      | 1                       | 1                           |

+--------+--------+-------------------------+-----------------------------+

From the code it seems that Item_func::excl_dep_on_grouping_fields() in sql/item_func.h accepts any function of the grouping columns, so the condition is moved below the grouping. The rows 'A' and 'a' are one group in utf8mb4_general_ci, but c = 'A' COLLATE utf8mb4_bin is true for 'A' only. The condition then removes the row 'a' before the rows are grouped.