Details
Minor Description
A materialized derived table containing 1,000 rows for the same generated-key value is assigned rec_per_key_estimate=1 when the projected column alias matches the name of a different column used by GROUP BY. EXPLAIN consequently estimates one row for the ref lookup, while ANALYZE FORMAT=JSON reads all 1,000 rows.
This occurs when a derived table groups by one column but projects another column under an alias matching the grouping column's name.
Reproducer
Run the following SQL in one session:
CREATE DATABASE IF NOT EXISTS test; |
USE test; |
DROP TABLE IF EXISTS probe; |
DROP TABLE IF EXISTS alias_src; |
CREATE TABLE alias_src ( |
alias_value INT NOT NULL, |
real_group INT NOT NULL, |
KEY(real_group) |
) ENGINE=InnoDB;
|
INSERT INTO alias_src |
SELECT 1, seq FROM seq_1_to_1000; |
CREATE TABLE probe ( |
id INT NOT NULL PRIMARY KEY |
) ENGINE=InnoDB;
|
INSERT INTO probe VALUES (1); |
ANALYZE TABLE alias_src, probe; |
 |
SET optimizer_trace='enabled=on'; |
EXPLAIN FORMAT=JSON
|
SELECT SQL_NO_CACHE COUNT(*) |
FROM probe AS p |
JOIN ( |
SELECT s.alias_value AS real_group |
FROM alias_src AS s |
GROUP BY s.real_group |
) AS d ON d.real_group=p.id; |
SELECT 'INFERENCE_TRACE' AS marker, |
JSON_COMPACT(JSON_EXTRACT(trace,'$**.infer_derived_key_statistics')) AS detail |
FROM information_schema.optimizer_trace; |
 |
ANALYZE FORMAT=JSON
|
SELECT SQL_NO_CACHE COUNT(*) |
FROM probe AS p |
JOIN ( |
SELECT s.alias_value AS real_group |
FROM alias_src AS s |
GROUP BY s.real_group |
) AS d ON d.real_group=p.id; |
 |
SELECT 'ACTUAL_MAX_ROWS_PER_KEY' AS marker, MAX(rows_per_key) AS n |
FROM ( |
SELECT real_group, COUNT(*) AS rows_per_key |
FROM ( |
SELECT s.alias_value AS real_group |
FROM alias_src AS s |
GROUP BY s.real_group |
) AS grouped_rows |
GROUP BY real_group |
) AS per_key; |
 |
SET optimizer_trace=DEFAULT; |
DROP TABLE probe, alias_src; |
Decisive observation
The optimizer trace records a one-row-per-key inference:
INFERENCE_TRACE [{"table_alias":"d","key_name":"key0","key_parts":1,"select":["group_list_in_key"],"rec_per_key_estimate":1}]
|
The derived-table ref access in EXPLAIN FORMAT=JSON has rows: 1, but the same access in ANALYZE FORMAT=JSON reports r_rows: 1000. The independent SQL check confirms the actual key distribution:
ACTUAL_MAX_ROWS_PER_KEY 1000
|
Why the estimate is invalid
The subquery creates 1,000 groups from the distinct values of s.real_group. Every output row nevertheless projects the constant s.alias_value=1 under the alias real_group, so the generated key on that projected value contains 1,000 rows for lookup key 1. It cannot have one row per key.
The evidence is consistent with the key-statistics inference treating the matching display names of the grouping column and projected alias as column identity. Column lineage differs here: the grouping expression is s.real_group, whereas the generated-key value comes from s.alias_value.
Attachments
Issue Links
- relates to
-
MDEV-37936 Followup to MDEV-36321: out_rows for GROUP BY: use of item names?
-
- In Review
-