# Wrong result: `GROUP_CONCAT`/`JSON_ARRAYAGG` returns 0 rows when a materialized derived table containing a BLOB-family column is accessed via `derived_with_keys` (`ref=const`) **Type:** Bug **Component/s:** Optimizer, Temporary Tables **Affects Version/s:** 10.11.16 (verified) **Severity:** Critical — *silent wrong results, no error, no warning* **Labels:** wrong-result, derived_with_keys, group_concat, blob --- ## Summary A `SELECT ... GROUP BY` using `GROUP_CONCAT()` (or `JSON_ARRAYAGG()`) returns **zero rows** instead of the correct aggregated row, when **all** of the following hold: 1. the query reads a **materialized** (non-mergeable) derived table / CTE; 2. that derived table's select list contains **at least one BLOB-family column** (`TINYTEXT`, `TEXT`, `LONGTEXT`, `BLOB`, `JSON`) — it does **not** need to be the aggregated column, nor be referenced anywhere else in the query; 3. the derived table is joined on an equality against a **constant table**, which makes `derived_with_keys` generate `key0` and pick `type=ref ... ref=const`; 4. the aggregate is of the `GROUP_CONCAT` family; 5. a `GROUP BY` clause is present. Replacing `GROUP_CONCAT()` with `MAX()` — **without changing anything else in the plan** — returns the correct result. `SET SESSION optimizer_switch='derived_with_keys=off'` also returns the correct result. The failure is **completely silent**: no error, no warning, `SHOW WARNINGS` is empty. An application simply sees "no data". --- ## Environment ``` Server version : 10.11.16-MariaDB-log version_comment: managed by https://aws.amazon.com/rds/ Storage engine : InnoDB optimizer_switch: derived_with_keys=on (default) condition_pushdown_for_derived=on split_materialized=on group_concat_max_len = 16777216 sql_mode: default ``` Originally found on production views on real (non-temporary) InnoDB tables; the test case below is a reduced, self-contained version. --- ## Minimal reproducible test case ```sql CREATE DATABASE IF NOT EXISTS bugtest; USE bugtest; CREATE TABLE docs ( id INT NOT NULL PRIMARY KEY, org CHAR(11) NOT NULL, sect VARCHAR(15) NOT NULL, doc LONGTEXT NULL -- any BLOB-family type triggers the bug ) ENGINE=InnoDB; CREATE TABLE users ( id VARCHAR(32) NOT NULL PRIMARY KEY, org CHAR(11) NOT NULL ) ENGINE=InnoDB; INSERT INTO users VALUES ('alice','01234567890'), ('bob' ,'09876543210'); INSERT INTO docs VALUES (1,'01234567890','S1','{"n":"a1"}'), (2,'01234567890','S1','{"n":"a2"}'), (3,'09876543210','S2','{"n":"a3"}'); -- ##### THE BUG ##### -- The LIMIT only serves to make the derived table non-mergeable. SELECT t.sect, GROUP_CONCAT(t.org) AS orgs FROM (SELECT org, sect, doc FROM docs LIMIT 100) t JOIN (SELECT u.org FROM users u WHERE u.id = 'alice') uo ON t.org = uo.org GROUP BY t.sect; ``` ### Actual result ``` Empty set (0 rows) -- SHOW WARNINGS; -> Empty set ``` ### Expected result ``` +------+-------------------------+ | sect | orgs | +------+-------------------------+ | S1 | 01234567890,01234567890 | +------+-------------------------+ ``` ### Control query — identical plan shape, only the aggregate changes ```sql SELECT t.sect, MAX(t.org) AS orgs, COUNT(*) AS n FROM (SELECT org, sect, doc FROM docs LIMIT 100) t JOIN (SELECT u.org FROM users u WHERE u.id = 'alice') uo ON t.org = uo.org GROUP BY t.sect; -- +------+-------------+---+ -- | sect | orgs | n | -- +------+-------------+---+ -- | S1 | 01234567890 | 2 | -- +------+-------------+---+ ``` So the two rows **are** there and **are** matched; only the `GROUP_CONCAT` variant loses them. --- ## `ANALYZE` output — the smoking gun Same query, same data, same access method. Only the aggregate function differs. ### Broken (`GROUP_CONCAT`) — `r_rows = 0.00` ``` id select_type table type key key_len ref rows r_rows Extra 1 PRIMARY u const PRIMARY 34 const 1 NULL 1 PRIMARY ref key0 11 const 0 0.00 Using where; Using filesort 2 DERIVED docs ALL NULL NULL NULL 3 3.00 ``` ### Correct (`MAX`) — `r_rows = 2.00` ``` id select_type table type key key_len ref rows r_rows Extra 1 PRIMARY u const PRIMARY 34 const 1 NULL Using temporary; Using filesort 1 PRIMARY ref key0 11 const 0 2.00 Using where 2 DERIVED docs ALL NULL NULL NULL 3 3.00 ``` Observations: * The derived table **is** materialized correctly in both cases (`docs`, `r_rows = 3.00`). * The `ref` access on `key0` reports `r_rows = 2.00` with `MAX`, but `r_rows = 0.00` with `GROUP_CONCAT`. * The distinguishing plan feature is the placement of the sort: with `GROUP_CONCAT` the optimizer drops `Using temporary` and pushes **`Using filesort` onto the derived table itself**. **Hypothesis (not verified against the source):** in the `GROUP_CONCAT` path the sort is performed by rowid rather than by full record, and the second pass re-reads the rows from the materialized temporary table through the auto-generated `key0`. When the temporary table contains a blob field this re-read yields nothing. The fact that a *mere presence* of a BLOB column in the derived table's select list is enough (see matrix #5/#6 below) points at the temporary table's record format / blob handling rather than at the aggregate function itself. --- ## Isolation matrix Every row is a single-variable change from the baseline test case above, executed on 10.11.16. ❌ = wrong result (0 rows), ✅ = correct result. | # | Variation | Result | |----|--------------------------------------------------------------------------|--------| | 1 | **Baseline** as written above | ❌ | | 2 | `MAX(t.org)` instead of `GROUP_CONCAT(t.org)` | ✅ | | 3 | `JSON_ARRAYAGG(t.org)` instead of `GROUP_CONCAT(t.org)` | ❌ | | 4 | `GROUP_CONCAT(t.doc)` — aggregate the blob column itself | ❌ | | 5 | `doc` **removed from the derived select list** (all else identical) | ✅ | | 6 | `doc` declared `VARCHAR(4000)` instead of `LONGTEXT` | ✅ | | 7 | `doc` declared `TINYTEXT` | ❌ | | 8 | `doc` declared `BLOB` | ❌ | | 9 | `GROUP BY t.sect` removed (single-group aggregate) | ✅ | | 10 | `WHERE t.org = '01234567890'` literal instead of the const-table join | ✅ | | 11 | derived made mergeable (`LIMIT 100` removed) | ✅ | | 12 | `UNION ALL` used instead of `LIMIT` to force materialization | ❌ | | 13 | `SET SESSION optimizer_switch='derived_with_keys=off'` | ✅ | Key takeaways: * **#5 vs #4**: the blob does not have to be aggregated, or even used. Merely being projected by the derived table is enough. * **#6 vs #7**: it is the *type family*, not the length. `TINYTEXT` (max 255 bytes) fails while `VARCHAR(4000)` succeeds. * **#10**: a plain literal predicate is fine — the equality must be against a **const table**, which is what triggers `derived_with_keys` to build `key0`. * **#11 vs #12**: the way materialization is forced is irrelevant. * **#9**: `GROUP BY` is required. --- ## Original real-world shape Found in production views of the following form (document-management application, ~25 affected views). `properties` is a `JSON`/`LONGTEXT` column: ```sql WITH ts AS ( SELECT d.rep_org_id, d.item_sect, JSON_SET(d.properties, '$.x', 1) AS doc FROM drc_ses_vehicle d UNION ALL SELECT d.rep_org_id, d.item_sect, JSON_SET(d.properties, '$.x', 1) FROM doc_ses_vehicle d ) SELECT ts.item_sect, JSON_ARRAYAGG(ts.doc) AS documents FROM (SELECT * FROM ts) ts CROSS JOIN (SELECT u.org_id FROM app_users u WHERE u.id = 'someuser') uo WHERE ts.rep_org_id = uo.org_id GROUP BY ts.item_sect; ``` This returned `0` rows for every user, while the same query with `MAX(ts.doc)` returned the expected rows. The impact was that an entire class of user-facing "list my documents" endpoints silently returned empty results. --- ## Workarounds For the record, in case they help characterise the problem: 1. `SET SESSION optimizer_switch='derived_with_keys=off';` Works, but is **not expressible inside a `CREATE VIEW`**, which made it unusable for us. 2. Remove the top-level equality between a derived-table column and a const table, e.g. by moving the join **inside** the CTE/derived table: ```sql WITH ts AS ( SELECT d.rep_org_id, d.item_sect, JSON_SET(d.properties,'$.x',1) AS doc FROM drc_ses_vehicle d JOIN app_users u ON u.org_id = d.rep_org_id AND u.id = 'someuser' UNION ALL ... ) ``` With no `col = const_table.col` predicate left at the top level, `derived_with_keys` has nothing to build `key0` from, and the correct result is returned. 3. Wrapping the constant in `CAST(... AS CHAR)` also happens to suppress `key0` generation and hides the bug. (We had such a `CAST` in one view, added years ago for exactly this reason and since forgotten — a good illustration of how easy it is to "fix" this accidentally and never diagnose it.) Things that are **not** the cause, all tested and excluded: `group_concat_max_len` (16 MB here, output is ~40 bytes), JSON truncation (warning 1260 never raised), `split_materialized`, `condition_pushdown_for_derived`, `ORDER BY NULL`. --- ## Not verified * Only reproduced on **10.11.16** (AWS RDS build). We have not been able to test other major versions (10.6, 11.4, 11.8, 12.3) or a non-RDS build. * We have not inspected the server source, so the mechanism described above is a hypothesis derived purely from black-box observation and `ANALYZE` output. --- ## Why we consider this Critical * The result is **wrong, not slow**: rows silently disappear. * There is **no error and no warning** of any kind. * It is triggered by a **default** `optimizer_switch` setting. * The trigger conditions are extremely common in reporting/document workloads: a materialized CTE carrying a JSON/TEXT payload, filtered by the current user's organisation looked up from a single-row table, aggregated with `JSON_ARRAYAGG` and grouped. * Because the query returns *zero* rows rather than *fewer* rows, it is easy to misattribute to permissions or to missing data, and it may go undetected for a long time.