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.

What it affects beyond GROUP_CONCAT

JSON_ARRAYAGG is built on Item_func_group_concat and puts brackets round
what the parent hands back. 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.

Verified on 10.11 only (10.11.19-MariaDB-debug, commit 1dab253482d).
Other branches were not tested.

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.