Details
Description
GROUP_CONCAT() with ORDER BY and LIMIT offset, row_count returns the wrong rows, or an empty string, once the tree that sorts the rows of a group has been repacked. No warning is issued. JSON_ARRAYAGG(), which is implemented on top of GROUP_CONCAT(), is affected the same way.
A repack keeps the leading rows of the group in ORDER BY order until their lengths add up to group_concat_max_len and drops the rest. The rows skipped by the offset are counted toward that length, although they never appear in the result. When the skipped rows alone are longer than group_concat_max_len, the repack drops the rows that belong to the result.
Up to 13.1, a repack only happens once the strings in the tree add up to more than 1024 times group_concat_max_len, so it needs a small group_concat_max_len or a very large group. In 13.2, the tree is repacked when it reaches two thirds of its memory limit, which is the larger of group_concat_max_len and the smaller of tmp_memory_table_size and max_heap_table_size.
How to repeat
On 13.2:
--source include/have_sequence.inc
|
CREATE TABLE t1 (v VARCHAR(10)) CHARACTER SET latin1; |
INSERT INTO t1 SELECT LPAD(seq, 5, '0') FROM seq_1_to_2000; |
SET SESSION group_concat_max_len= 100; |
SELECT GROUP_CONCAT(v ORDER BY v LIMIT 100, 5) AS r FROM t1; |
SELECT JSON_ARRAYAGG(v ORDER BY v LIMIT 100, 5) AS r FROM t1; |
SET SESSION tmp_memory_table_size= 0; |
SELECT GROUP_CONCAT(v ORDER BY v LIMIT 100, 5) AS r FROM t1; |
SHOW WARNINGS;
|
SELECT JSON_ARRAYAGG(v ORDER BY v LIMIT 100, 5) AS r FROM t1; |
SHOW WARNINGS;
|
DROP TABLE t1; |
With the default tmp_memory_table_size there is no repack, and both queries return the expected result:
SELECT GROUP_CONCAT(v ORDER BY v LIMIT 100, 5) AS r FROM t1;
|
r
|
00101,00102,00103,00104,00105
|
SELECT JSON_ARRAYAGG(v ORDER BY v LIMIT 100, 5) AS r FROM t1;
|
r
|
["00101","00102","00103","00104","00105"]
|
With tmp_memory_table_size set to 0 the tree is repacked, and both queries return an empty result without a warning:
SET SESSION tmp_memory_table_size= 0;
|
SELECT GROUP_CONCAT(v ORDER BY v LIMIT 100, 5) AS r FROM t1;
|
r
|
 |
SHOW WARNINGS;
|
Level Code Message
|
SELECT JSON_ARRAYAGG(v ORDER BY v LIMIT 100, 5) AS r FROM t1;
|
r
|
[]
|
SHOW WARNINGS;
|
Level Code Message
|
On 10.11, where the repack needs the strings to exceed 1024 times group_concat_max_len:
--source include/have_sequence.inc
|
CREATE TABLE t1 (v VARCHAR(10)) CHARACTER SET latin1; |
INSERT INTO t1 SELECT LPAD(seq, 5, '0') FROM seq_1_to_5000; |
SELECT v FROM t1 ORDER BY v LIMIT 100, 1; |
SET SESSION group_concat_max_len= 10; |
SELECT GROUP_CONCAT(v ORDER BY v LIMIT 100, 1) AS r FROM t1; |
SHOW WARNINGS;
|
SET SESSION group_concat_max_len= 100; |
SELECT GROUP_CONCAT(v ORDER BY v LIMIT 100, 1) AS r FROM t1; |
SHOW WARNINGS;
|
SET SESSION group_concat_max_len= DEFAULT; |
DROP TABLE t1; |
Expected: 00101 for both GROUP_CONCAT() queries.
Actual: with group_concat_max_len = 10 the 5000 values (25000 bytes) cause repacks, and the query returns a row from much later in the group, without a warning. With group_concat_max_len = 100 there is no repack, and the result is correct:
SELECT v FROM t1 ORDER BY v LIMIT 100, 1;
|
v
|
00101
|
SET SESSION group_concat_max_len= 10;
|
SELECT GROUP_CONCAT(v ORDER BY v LIMIT 100, 1) AS r FROM t1;
|
r
|
04601
|
SHOW WARNINGS;
|
Level Code Message
|
SET SESSION group_concat_max_len= 100;
|
SELECT GROUP_CONCAT(v ORDER BY v LIMIT 100, 1) AS r FROM t1;
|
r
|
00101
|
SHOW WARNINGS;
|
Level Code Message
|
Root cause
Item_func_group_concat::repack_tree() in sql/item_sum.cc walks the tree in ORDER BY order and copies each row into a new tree with copy_to_tree(). That function adds up the length of every row it copies, starting with the first row of the group, and stops the walk once the total exceeds gconcat_max_len() (10.11):
st->len += len;
|
return st->len > st->maxlen; |
dump_leaf_key(), which builds the result from the tree, first skips the offset rows without counting their length (10.11):
if (item->limit_clause && (*offset_limit)) |
{
|
item->row_count++;
|
(*offset_limit)--;
|
return 0; |
}
|
So the rows that copy_to_tree() keeps are the ones in front of the result, and the rows that it drops include the result itself. In 13.2, copy_to_tree() sets st->truncated only when the new tree reaches its memory limit, not when the walk stops at gconcat_max_len(), so result_cut is not set and no warning is issued. Before 13.2 there is no such flag, and no warning is issued either.
Affected versions
copy_to_tree() and repack_tree() are identical on 10.11, 11.4, 11.8, 12.3 and 13.1. 13.2 changes when the repack happens and how much memory it may use, but not where copy_to_tree() stops. The repeat cases above were run on 10.11 and 13.2.