GROUP_CONCAT with an OFFSET that reaches past the last row of a group answers differently depending on how many times the result is asked for

Nothing says how many times a statement asks for the result of a group,
and the answer must not depend on it. For GROUP_CONCAT with an OFFSET
that reaches past the last row of the group, it does. HAVING on the
alias is the shortest statement that asks twice:

CREATE TABLE t1 (a VARCHAR(10));

INSERT INTO t1 VALUES ('a'),('b'),('c'),('d');

SELECT GROUP_CONCAT(a ORDER BY a LIMIT 2 OFFSET 4) AS v FROM t1;

v

SELECT GROUP_CONCAT(a ORDER BY a LIMIT 2 OFFSET 4) AS v FROM t1

  HAVING v LIKE '%';

v

a,b

One expression over one set of rows, two answers. The second is wrong
on its own terms as well: the group holds four rows and the offset is
four, so there is nothing left for the limit to take.

No error and no warning is raised, so the wrong answer is silent.

How to repeat

The whole of the following was run on 10.11.19-MariaDB-debug at
1dab253482d. The output shown is what the server produced.

CREATE TABLE t1 (g INT, a VARCHAR(10));

INSERT INTO t1 VALUES (1,'a'),(1,'b'),(1,'c'),(1,'d'),(2,'e'),(2,'f');

-- asked once

SELECT GROUP_CONCAT(a ORDER BY a LIMIT 2 OFFSET 4) AS v FROM t1 WHERE g = 1;

v

-- asked twice

SELECT GROUP_CONCAT(a ORDER BY a LIMIT 2 OFFSET 4) AS v FROM t1 WHERE g = 1

  HAVING v LIKE '%';

v

a,b

-- two conditions, so it is asked a third time

SELECT GROUP_CONCAT(a ORDER BY a LIMIT 2 OFFSET 4) AS v FROM t1 WHERE g = 1

  HAVING v LIKE '%' AND v NOT LIKE 'zz%';

v

a,b

-- DISTINCT reaches the same walk by the other route

SELECT GROUP_CONCAT(DISTINCT a LIMIT 2 OFFSET 4) AS v FROM t1 WHERE g = 1

  HAVING v LIKE '%';

v

a,b

A GROUP BY shows both answers in one statement. Group 1 holds four rows
and spends the offset exactly; group 2 holds two and does not, so only
group 1 is affected:

SELECT g, GROUP_CONCAT(a ORDER BY a LIMIT 2 OFFSET 4) AS v FROM t1

  GROUP BY g HAVING v LIKE '%' ORDER BY g;

g	v

1	a,b

2

An offset that stops inside the group is not affected, the walk having
written a row, and neither is a group with no LIMIT at all:

SELECT GROUP_CONCAT(a ORDER BY a LIMIT 2 OFFSET 1) AS v FROM t1 WHERE g = 1

  HAVING v LIKE '%';

v

b,c

SELECT GROUP_CONCAT(a ORDER BY a LIMIT 2 OFFSET 3) AS v FROM t1 WHERE g = 1

  HAVING v LIKE '%';

v

d

SELECT GROUP_CONCAT(a ORDER BY a) AS v FROM t1 WHERE g = 1

  HAVING v LIKE '%';

v

a,b,c,d

Root cause

Item_func_group_concat::val_str() walks the tree only while
result_finalized is false:

/* sql/item_sum.cc */

  if (!result_finalized) // Result yet to be written.

  {

    if (tree != NULL) // order by

      tree_walk(tree, &dump_leaf_key, this, left_root_right);

    else if (distinct) // distinct (and no order by).

      unique_filter->walk(table, &dump_leaf_key, this);

    else if (row_limit && copy_row_limit == (ulonglong)row_limit->val_int())

      return &result;

    else

      DBUG_ASSERT(false); // Can't happen

  }

dump_leaf_key() raises that flag for the first row it writes. A row that
falls inside the offset is skipped by an earlier return, which decrements
the offset counter and leaves the flag alone:

/* sql/item_sum.cc - dump_leaf_key() */

  ulonglong *offset_limit= &item->copy_offset_limit;

  ulonglong *row_limit = &item->copy_row_limit;

  if (item->limit_clause && !(*row_limit))

  {

    item->result_finalized= true;      /* the row-limit arm sets it */

    return 1;

  }

  tmp.length(0);

  if (item->limit_clause && (*offset_limit))

  {

    item->row_count++;

    (*offset_limit)--;

    return 0;                          /* the offset arm does not */

  }

  if (!item->result_finalized)

    item->result_finalized= true;

  else

    result->append(*item->separator);

So a walk in which every row was skipped writes nothing and raises
nothing. The next caller walks again, and by then the offset counter has
been spent, so the rows skipped the first time are written - into a
result buffer that belongs to the item and has already been handed out
once.

The row-limit arm immediately above it does raise the flag before its own
early return, so two adjacent early returns out of the same function
behave differently.

The replay is a crash, not only a wrong answer

Everything above assumes the duplicate filter still holds the whole group in memory. When it has spilled to disk the second walk is not merely wrong, it is unsupported. Unique::reset() carries the contract in a comment above it:

  Clear the tree and the file.

  You must call reset() if you want to reuse Unique after walk().

and inside it, "walk() does not reset any Unique member". val_str() walks again without ever calling reset().

The first walk flushed the in-memory tree to the file and deleted it. Unique::flush() appends one chunk per call with whatever the tree holds at the time, so the second walk appends a chunk of no rows:

/* sql/uniques.cc */

bool Unique::flush()

{

  Merge_chunk file_ptr;

  elements+= tree.elements_in_tree;

  file_ptr.set_rowcount(tree.elements_in_tree);

  file_ptr.set_file_position(my_b_tell(&file));

  ...

  delete_tree(&tree, 0);

  return 0;

}

merge_walk() then reads that chunk and gets nothing back. Zero is neither the error value it checks for nor a valid read:

/* sql/uniques.cc, merge_walk() */

    bytes_read= read_to_buffer(file, top, &sort_param, false);

    if (unlikely(bytes_read == (ulong) -1))

      goto end;

    DBUG_ASSERT(bytes_read);

    queue_insert(&queue, (uchar *) top);

A debug build aborts on that assertion. A build without assertions inserts the empty chunk into the queue and goes on to take keys from it.

How to repeat the crash

The filter has to spill, so the group needs more distinct rows than the memory allowed for it:

CREATE TABLE t1 (g INT, a VARCHAR(100));

INSERT INTO t1 SELECT seq MOD 4, LPAD(seq, 100, '0') FROM seq_1_to_2000;

SET @@tmp_memory_table_size=0;

SELECT g, GROUP_CONCAT(DISTINCT a LIMIT 5 OFFSET 1000) AS gc

  FROM t1 GROUP BY g HAVING gc <> 'x';

mariadbd: sql/uniques.cc:558: bool merge_walk(uchar*, size_t, uint, Merge_chunk*, Merge_chunk*, tree_walk_action, void*, qsort_cmp2, void*, IO_CACHE*, bool): Assertion `bytes_read' failed.

Server version: 13.1.0-MariaDB-debug-log

sql/uniques.cc:559(merge_walk(...))

sql/uniques.cc:697(Unique::walk(TABLE*, int (*)(void*, unsigned int, void*), void*))

sql/item_sum.cc:4737(Item_func_group_concat::val_str(String*))

sql/sql_type.cc:7729(Type_handler::Item_send_str(Item*, Protocol*, st_value*) const)

sql/protocol.cc:1358(Protocol::send_result_set_row(List<Item>*))

sql/sql_class.cc:3411(select_send::send_data(List<Item>&))

sql/sql_select.cc:26803(end_send_group(JOIN*, st_join_table*, bool))

sql/sql_select.cc:24786(do_select(JOIN*, Procedure*))

The stack is abridged. Note the frame above val_str(): the walk that aborts is the one performed while sending the row, after the HAVING clause has already evaluated the same aggregate for the same group.

On the release branches only the form without ORDER BY reaches the duplicate filter, since val_str() sends an aggregate with ORDER BY to tree_walk() instead. The reproduction above therefore needs DISTINCT and no ORDER BY.

What it affects beyond GROUP_CONCAT

JSON_ARRAYAGG is built on Item_func_group_concat and puts brackets round
what the parent returns. The second walk appends the group's elements
to a buffer whose closing bracket has already been written, so they land
outside the document:

SELECT JSON_ARRAYAGG(a ORDER BY a LIMIT 2 OFFSET 4) AS v FROM t1 WHERE g = 1;

v

[]

SELECT JSON_ARRAYAGG(a ORDER BY a LIMIT 2 OFFSET 4) AS v FROM t1 WHERE g = 1

  HAVING v LIKE '%';

v

[[]"a","b"]

SELECT JSON_VALID(JSON_ARRAYAGG(a ORDER BY a LIMIT 2 OFFSET 4)) AS v

  FROM t1 WHERE g = 1 HAVING v LIKE '%';

v

0

Warnings:

Note	4038	Syntax error in JSON text in argument 1 to function 'json_valid' at position 4

Not this bug: the extra pair of brackets

The value above carries two defects at once and only one of them is this
report. The elements standing outside the brackets are the replay
described here. The extra pair of brackets is MDEV-40612, in which both
JSON aggregates close their result in the call that hands it over rather
than when the group ends, so a second call wraps it again.

An offset that stops inside the group separates them. There the walk
wrote a row and finished the result, so the replay does not happen and
what is left is MDEV-40612 on its own:

SELECT JSON_ARRAYAGG(a ORDER BY a LIMIT 2 OFFSET 1) AS v FROM t1 WHERE g = 1

  HAVING v LIKE '%';

v

[["b","c"]]

Affected versions

Both the result_finalized flag and the guard in val_str() that reads it
were added by MDEV-11563 (commit a006e88cac0e, 2020-03-21, "GROUP_CONCAT
(DISTINCT ...) may produce a non-distinct list"), first released in
10.5.4. That commit put the flag on the row-limit early return and not
on the offset early return, which is the asymmetry above.

The LIMIT and OFFSET clauses themselves are older - MDEV-11297 (commit
6d63a0349029, 2017-12-08), first released in 10.3.3 - but the replay
needs the flag, so 10.5.4 is where the defect begins.

Every release from 10.5.4 onward is therefore expected to be affected.

The wrong result was verified on 10.11 (10.11.19-MariaDB-debug, commit 1dab253482d)
and the crash on 13.1 (13.1.0-MariaDB-debug). Other branches were not run.

Note

Found while testing an unrelated change to the JSON aggregates. No
existing test asks for the result of a group more than once with an
offset that reaches past its last row, which is why the defect has not
shown up.