Description:
An aggregation through an internal temporary table that uses a hash key (any
GROUP BY key longer than 512 characters, or a TEXT/BLOB/JSON key) adds a row
to the wrong group when the in-memory TempTable runs out of temptable_max_ram
while an *existing* group is being updated. After the table is moved to disk,
the row to update is looked up by the 64-bit hash only, and the first group
with that hash gets the row's aggregates.
With a NO PAD collation whose hash ignores trailing spaces (utf8mb4_0900_bin),
'x' and 'x ' are different groups but always have the same hash, so the error
is systematic for such data: one row per spill is counted (COUNT, SUM, MIN/MAX)
under the other group. There is no error or warning, and CREATE TABLE ...
SELECT / INSERT ... SELECT store the wrong aggregates.
The repro lowers temptable_max_ram to its documented minimum (2 MiB) only to
make the spill happen at a fixed row. temptable_max_ram is a server-wide limit
shared by all sessions, so on a busy server the same spill happens with the
default limit at whatever row exceeds it. The result is correct with
big_tables=ON, with internal_tmp_mem_storage_engine=MEMORY, or when the
TempTable does not fill up. The hypergraph optimizer (hypergraph_optimizer=on)
uses the same "Aggregate using temporary table" plan here and returns the same
wrong result.
How to repeat:
CREATE DATABASE m201; USE m201;
CREATE TABLE t (id INT PRIMARY KEY, g VARCHAR(600) COLLATE utf8mb4_0900_bin, p VARCHAR(3000));
SET SESSION cte_max_recursion_depth = 100000;
-- id 1: g = 'x'; ids 2..4000: g = 'x ' (trailing space); p grows MAX(p) in the temporary table row
INSERT INTO t WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM s WHERE n < 4000)
SELECT n, IF(n = 1, 'x', 'x '), REPEAT('z', n % 3000) FROM s;
SELECT HEX(g), COUNT(*), SUM(id) FROM t GROUP BY HEX(g) ORDER BY 1;
SET GLOBAL temptable_max_ram = 2097152; -- documented minimum (2 MiB)
CREATE TABLE agg_spill AS SELECT g, COUNT(*) AS cnt, SUM(id) AS sum_id, MAX(p) AS max_p FROM t GROUP BY g;
SET SESSION big_tables = ON; -- same query, on-disk temporary table from the start
CREATE TABLE agg_disk AS SELECT g, COUNT(*) AS cnt, SUM(id) AS sum_id, MAX(p) AS max_p FROM t GROUP BY g;
SET SESSION big_tables = OFF;
SET GLOBAL temptable_max_ram = DEFAULT;
SELECT HEX(g), cnt, sum_id FROM agg_spill ORDER BY 1;
SELECT HEX(g), cnt, sum_id FROM agg_disk ORDER BY 1;
Result on 26.10.0:
the data: 78 -> 1 row (sum 1); 7820 -> 3999 rows (sum 8001999)
agg_spill: 78 -> 2 rows (sum 2953); 7820 -> 3998 rows (sum 7999047) <- wrong
agg_disk: 78 -> 1 row (sum 1); 7820 -> 3999 rows (sum 8001999)
Row 2952, the row being aggregated when the TempTable became full, was counted
under 'x' (hex 78). SHOW SESSION STATUS LIKE 'Created_tmp_disk_tables' goes up
by 1 for the statement. The plain query is wrong in the same way; with the
2 MiB limit,
SELECT HEX(g), COUNT(*), SUM(id), LENGTH(MAX(p)) FROM t GROUP BY g;
returned 78 | 2 | 2975 | 2974 and 7820 | 3998 | 7999025 | 2999 (the misplaced
row moves with the memory state; 'x' even gets the MAX(p) of the other
group's row), and 78 | 1 | 1 | 1, 7820 | 3999 | 8001999 | 2999 after
SET GLOBAL temptable_max_ram = DEFAULT. The plan is "Aggregate using temporary
table".
The same happens with a TEXT COLLATE utf8mb4_0900_bin key (78 | 2 | 2975),
and with interleaved data ('x' for odd ids, 'x ' for even ids: 2001 / 1999
rows instead of 2000 / 2000). It needs an aggregate that grows the
temporary-table row (here MAX(p)), so that an update of an existing group is
what fills the TempTable; without MAX(p) the same GROUP BY is correct. The
misplaced row is the one being processed when the TempTable became full, so it
depends on the limit and on the TempTable memory in use (row 2952 in the
CREATE TABLE ... SELECT above, row 2974 in the later plain SELECT).
Expected: 'x' 1 row, 'x ' 3999 rows, as with big_tables=ON.
Suggested fix:
TemptableAggregateIterator::DoInit() finds an existing group with
check_unique_fields() in the normal path, which looks up the hash and then
compares the group columns of every row with that hash (ha_index_next_same +
group_rec_cmp), so hash collisions are handled:
https://github.com/mysql/mysql-server/blob/06bcb0eb0ad281e08dd2b4d81b99ad25491433e7/sql/it...
https://github.com/mysql/mysql-server/blob/06bcb0eb0ad281e08dd2b4d81b99ad25491433e7/sql/sq...
When ha_update_row() of an existing group fails because the TempTable is full,
the table is moved to disk and "the row to be updated" is re-read with a single
ha_index_read_map(record[1], hash, HA_WHOLE_KEY, HA_READ_KEY_EXACT), without
the group column comparison:
https://github.com/mysql/mysql-server/blob/06bcb0eb0ad281e08dd2b4d81b99ad25491433e7/sql/it...
The on-disk hash index is not unique, so this returns the first (older) group
with the same hash, and the current row's aggregates are written into it.
The hash is calc_field_hash() with the collation's hash_sort; for
utf8mb4_0900_bin that is my_hash_sort_mb_bin, which strips trailing spaces,
while the collation compares with NO PAD, so 'x' and 'x ' always collide.
With other collations only accidental 64-bit collisions reach this path.
The set-operation materialization handles the same situation by repositioning
with check_unique_fields() after the spill (spill_to_disk_and_retry_update_row):
https://github.com/mysql/mysql-server/blob/06bcb0eb0ad281e08dd2b4d81b99ad25491433e7/sql/it...
Using check_unique_fields() (or a group_rec_cmp() check over the rows with the
same hash) in the GROUP BY spill path would fix it.
Description: An aggregation through an internal temporary table that uses a hash key (any GROUP BY key longer than 512 characters, or a TEXT/BLOB/JSON key) adds a row to the wrong group when the in-memory TempTable runs out of temptable_max_ram while an *existing* group is being updated. After the table is moved to disk, the row to update is looked up by the 64-bit hash only, and the first group with that hash gets the row's aggregates. With a NO PAD collation whose hash ignores trailing spaces (utf8mb4_0900_bin), 'x' and 'x ' are different groups but always have the same hash, so the error is systematic for such data: one row per spill is counted (COUNT, SUM, MIN/MAX) under the other group. There is no error or warning, and CREATE TABLE ... SELECT / INSERT ... SELECT store the wrong aggregates. The repro lowers temptable_max_ram to its documented minimum (2 MiB) only to make the spill happen at a fixed row. temptable_max_ram is a server-wide limit shared by all sessions, so on a busy server the same spill happens with the default limit at whatever row exceeds it. The result is correct with big_tables=ON, with internal_tmp_mem_storage_engine=MEMORY, or when the TempTable does not fill up. The hypergraph optimizer (hypergraph_optimizer=on) uses the same "Aggregate using temporary table" plan here and returns the same wrong result. How to repeat: CREATE DATABASE m201; USE m201; CREATE TABLE t (id INT PRIMARY KEY, g VARCHAR(600) COLLATE utf8mb4_0900_bin, p VARCHAR(3000)); SET SESSION cte_max_recursion_depth = 100000; -- id 1: g = 'x'; ids 2..4000: g = 'x ' (trailing space); p grows MAX(p) in the temporary table row INSERT INTO t WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM s WHERE n < 4000) SELECT n, IF(n = 1, 'x', 'x '), REPEAT('z', n % 3000) FROM s; SELECT HEX(g), COUNT(*), SUM(id) FROM t GROUP BY HEX(g) ORDER BY 1; SET GLOBAL temptable_max_ram = 2097152; -- documented minimum (2 MiB) CREATE TABLE agg_spill AS SELECT g, COUNT(*) AS cnt, SUM(id) AS sum_id, MAX(p) AS max_p FROM t GROUP BY g; SET SESSION big_tables = ON; -- same query, on-disk temporary table from the start CREATE TABLE agg_disk AS SELECT g, COUNT(*) AS cnt, SUM(id) AS sum_id, MAX(p) AS max_p FROM t GROUP BY g; SET SESSION big_tables = OFF; SET GLOBAL temptable_max_ram = DEFAULT; SELECT HEX(g), cnt, sum_id FROM agg_spill ORDER BY 1; SELECT HEX(g), cnt, sum_id FROM agg_disk ORDER BY 1; Result on 26.10.0: the data: 78 -> 1 row (sum 1); 7820 -> 3999 rows (sum 8001999) agg_spill: 78 -> 2 rows (sum 2953); 7820 -> 3998 rows (sum 7999047) <- wrong agg_disk: 78 -> 1 row (sum 1); 7820 -> 3999 rows (sum 8001999) Row 2952, the row being aggregated when the TempTable became full, was counted under 'x' (hex 78). SHOW SESSION STATUS LIKE 'Created_tmp_disk_tables' goes up by 1 for the statement. The plain query is wrong in the same way; with the 2 MiB limit, SELECT HEX(g), COUNT(*), SUM(id), LENGTH(MAX(p)) FROM t GROUP BY g; returned 78 | 2 | 2975 | 2974 and 7820 | 3998 | 7999025 | 2999 (the misplaced row moves with the memory state; 'x' even gets the MAX(p) of the other group's row), and 78 | 1 | 1 | 1, 7820 | 3999 | 8001999 | 2999 after SET GLOBAL temptable_max_ram = DEFAULT. The plan is "Aggregate using temporary table". The same happens with a TEXT COLLATE utf8mb4_0900_bin key (78 | 2 | 2975), and with interleaved data ('x' for odd ids, 'x ' for even ids: 2001 / 1999 rows instead of 2000 / 2000). It needs an aggregate that grows the temporary-table row (here MAX(p)), so that an update of an existing group is what fills the TempTable; without MAX(p) the same GROUP BY is correct. The misplaced row is the one being processed when the TempTable became full, so it depends on the limit and on the TempTable memory in use (row 2952 in the CREATE TABLE ... SELECT above, row 2974 in the later plain SELECT). Expected: 'x' 1 row, 'x ' 3999 rows, as with big_tables=ON. Suggested fix: TemptableAggregateIterator::DoInit() finds an existing group with check_unique_fields() in the normal path, which looks up the hash and then compares the group columns of every row with that hash (ha_index_next_same + group_rec_cmp), so hash collisions are handled: https://github.com/mysql/mysql-server/blob/06bcb0eb0ad281e08dd2b4d81b99ad25491433e7/sql/it... https://github.com/mysql/mysql-server/blob/06bcb0eb0ad281e08dd2b4d81b99ad25491433e7/sql/sq... When ha_update_row() of an existing group fails because the TempTable is full, the table is moved to disk and "the row to be updated" is re-read with a single ha_index_read_map(record[1], hash, HA_WHOLE_KEY, HA_READ_KEY_EXACT), without the group column comparison: https://github.com/mysql/mysql-server/blob/06bcb0eb0ad281e08dd2b4d81b99ad25491433e7/sql/it... The on-disk hash index is not unique, so this returns the first (older) group with the same hash, and the current row's aggregates are written into it. The hash is calc_field_hash() with the collation's hash_sort; for utf8mb4_0900_bin that is my_hash_sort_mb_bin, which strips trailing spaces, while the collation compares with NO PAD, so 'x' and 'x ' always collide. With other collations only accidental 64-bit collisions reach this path. The set-operation materialization handles the same situation by repositioning with check_unique_fields() after the spill (spill_to_disk_and_retry_update_row): https://github.com/mysql/mysql-server/blob/06bcb0eb0ad281e08dd2b4d81b99ad25491433e7/sql/it... Using check_unique_fields() (or a group_rec_cmp() check over the rows with the same hash) in the GROUP BY spill path would fix it.