Why does a GROUP_CONCAT report silently lose values for the largest groups?

## The symptom A report has looked correct for months. Then someone notices that a handful of rows — always the ones with the most children — show lists that end mid-word, or that are missing entries a colleague can see in the source table. Small groups are perfect. No error was raised, nothing was logged by the application, and re-running the query reproduces it exactly. That combination — correct for small groups, silently short for large ones, deterministic — is the signature of a **result-length cap on the string aggregate**. ## Engines disagree about the boundary The cap exists everywhere; the *behaviour at the cap* is the part that varies, and it is the variation that makes this a portability trap. **MySQL and MariaDB** cap `GROUP_CONCAT` at the `group_concat_max_len` system variable, whose default is 1024 bytes. Exceeding it truncates the result and raises a warning rather than an error. Most drivers and ORMs do not surface warnings, so the application sees a well-formed, shorter string. `group_concat_max_len` is settable per session or globally, and the practical value is also bounded by `max_allowed_packet`. **Oracle** takes the strict route: `LISTAGG` raises an error when the concatenated result exceeds the maximum length of the returned character type. Since 12.2 the function accepts an `ON OVERFLOW` clause — the default is `ON OVERFLOW ERROR`, and `ON OVERFLOW TRUNCATE` returns a shortened value instead, optionally with a truncation indicator and, with `WITH COUNT`, the number of omitted values. ```sql SELECT order_id, LISTAGG(sku, ',' ON OVERFLOW TRUNCATE '…' WITH COUNT) WITHIN GROUP (ORDER BY sku) AS skus FROM order_lines GROUP BY order_id; ``` **SQL Server** infers `STRING_AGG`'s result type from its input: aggregate a `varchar(50)` column and the result is a bounded `varchar`, which errors when the concatenation overflows 8000 bytes. Casting the input to a max-length type — `STRING_AGG(CAST(sku AS varchar(max)), ',')` — gives a large-object result and removes the practical ceiling. **PostgreSQL** has no small dedicated limit for `string_agg`; the effective bound is the general text/field size limit, which large lists reach only in extreme cases. The absence of a low cap is convenient, and it is also why a query developed there can start truncating when it is ported. The portable takeaway: never assume a concatenated list is complete just because the statement succeeded, and never assume it will error if it is not. ## Diagnosing it The cheapest confirmation is to compare what the string claims against what the group contains, in one query: ```sql SELECT order_id, COUNT(*) AS line_count, LENGTH(GROUP_CONCAT(sku)) AS list_bytes, GROUP_CONCAT(sku) AS skus FROM order_lines GROUP BY order_id HAVING LENGTH(GROUP_CONCAT(sku)) >= 1024; ``` If the affected rows all land on the same byte length, and that length is the configured cap, the diagnosis is finished. On MySQL, `SHOW WARNINGS` immediately after the statement reports the rows that were cut. It is worth doing this proactively in any batch job whose output feeds another system: a `HAVING` guard that fails loudly beats a report that quietly loses data. ## Fixing it, in order of preference **Bound the list, do not just enlarge the box.** If the business truth is "a customer has at most a dozen roles", raise the session limit and add an assertion that trips if a group ever exceeds it. If the list has no natural bound — every event for an account, every line for an order — no limit is large enough, and raising it converts a truncation bug into a memory and payload problem. **Change the result shape.** The sound fix for an unbounded list is not to build the string in SQL at all: return one row per value and let the caller assemble it, or return an array where the engine has one, so element boundaries survive and the client streams instead of materialising a multi-megabyte cell. **Limit deliberately, and say so.** When the consumer only wants a preview, produce a *bounded* list on purpose — take the top few values per group and append a count of the rest — rather than letting the engine decide where to cut. A user-visible "and 47 more" is a feature; a string that stops mid-SKU is a bug. **Do not lean on the delimiter for structure.** Truncation is one of several ways a delimited list loses fidelity; values containing the delimiter are another. Both arguments point the same way: a concatenated string is a presentation format, not a data-interchange format. ## Why this is a senior-flavoured question Everything about the failure resists ordinary detection. It affects a minority of rows, it does not throw, it survives retries, and it depends on a setting that lives outside the codebase — so it is usually found by a person noticing wrong output, not by a test. Interviewers use it to see whether you reason from the *shape* of the symptom (only big groups; deterministic; no error) to a configuration-level cause, and whether your instinct is to raise a limit or to question the result shape.