Description:
The MySQL Reference Manual, "Aggregate Function Descriptions" (Section 14.19.1),
states:
> For MIN()/MAX(), MySQL currently compares ENUM and SET columns by their string
> value rather than by the string's relative position in the set. This differs
> from how ORDER BY compares them.
**This report does not ask for that rule to be changed.** We are not arguing that
`MIN()`/`MAX()` on an `ENUM` should follow definition order; that request is
Bug #45300, and we are aware it was closed on the strength of the sentence above.
What we report is different. The documented sentence is **unconditional** — it
carries no qualification such as "unless a secondary index on the column is
used" — whereas the rule the implementation actually applies depends on whether
the MIN/MAX index optimization is chosen for the statement. Two consequences
follow:
1. **The result of an unchanged query depends on physical design.** With no
secondary index, `SELECT MIN(e), MAX(e)` returns the string-value extremes, as
documented. After `CREATE INDEX` on that column — same data, same query text —
the same statement returns the definition-order extremes. `DROP INDEX` restores
the first answer, `IGNORE INDEX` restores it as well, and `FORCE INDEX` keeps
the second. Creating an index is meant to be a performance decision, not a
semantic one.
2. **A single statement contradicts itself.** With the index present,
`(SELECT MAX(e) FROM en)` and `(SELECT DISTINCT MAX(e) OVER () FROM en LIMIT 1)`
are evaluated in the same statement, over the same table, and both denote "the
largest value of `e` in this table", yet they return different values.
Point 2 holds independently of which ordering is deemed correct: whichever
convention is chosen, the engine cannot be right under both at once. The engine
currently implements the string-value rule on the aggregation path and the
definition-order rule on the index endpoint path, and the plan choice leaks into
the result. No error and no warning is raised at any step.
How to repeat:
```sql
DROP DATABASE IF EXISTS bugrep_mysql;
CREATE DATABASE bugrep_mysql;
USE bugrep_mysql;
-- ENUM definition order: b=1, a=2, C=3, ab=4
-- String order under utf8mb4_0900_ai_ci: a < ab < b < C
CREATE TABLE en (id INT PRIMARY KEY, e ENUM('b','a','C','ab'));
INSERT INTO en VALUES (1,'b'),(2,'a'),(3,'C'),(4,'ab'),(5,'a'),(6,'b');
-- Step 1: no secondary index
SELECT MIN(e) AS min_e, MAX(e) AS max_e FROM en;
-- actual: ('a', 'C') expected: ('a', 'C') -- string order, as documented
CREATE INDEX ix_e ON en (e);
ANALYZE TABLE en;
-- Step 2: same data, same query text; the only change is the secondary index
SELECT MIN(e) AS min_e, MAX(e) AS max_e FROM en;
-- actual: ('b', 'ab') expected: ('a', 'C') <== result changed by DDL
-- Step 3: the access path alone decides the answer
SELECT MIN(e) AS min_e, MAX(e) AS max_e FROM en IGNORE INDEX (ix_e);
-- actual: ('a', 'C')
SELECT MIN(e) AS min_e, MAX(e) AS max_e FROM en FORCE INDEX (ix_e);
-- actual: ('b', 'ab')
-- Step 4: self-contradiction inside one statement (index present)
SELECT (SELECT MAX(e) FROM en) AS agg_max,
(SELECT DISTINCT MAX(e) OVER () FROM en LIMIT 1) AS window_max,
(SELECT e FROM en ORDER BY e DESC LIMIT 1) AS orderby_max;
-- actual: ('ab', 'C', 'ab')
-- agg_max and window_max both denote max(e) over the same table and disagree.
-- orderby_max = 'ab' is the documented ORDER BY (definition-order) answer, i.e.
-- the aggregate now agrees with ORDER BY instead of with its own documented rule.
SELECT (SELECT MIN(e) FROM en) AS agg_min,
(SELECT DISTINCT MIN(e) OVER () FROM en LIMIT 1) AS window_min,
(SELECT e FROM en ORDER BY e ASC LIMIT 1) AS orderby_min;
-- actual: ('b', 'a', 'b')
-- Step 5: control -- non-MIN/MAX comparisons are index-invariant
SELECT DISTINCT e FROM en WHERE e < 'b' ORDER BY e;
-- actual: ('a', 'ab')
SELECT DISTINCT e FROM en IGNORE INDEX (ix_e) WHERE e < 'b' ORDER BY e;
-- actual: ('a', 'ab') -- identical, so the defect is confined to MIN/MAX
SELECT MIN(e+0) AS min_pos, MAX(e+0) AS max_pos FROM en;
-- actual: (1, 4) -- the definition-order extremes are 'b' and 'ab'
-- Step 6: DROP INDEX restores the documented result (fully reversible)
DROP INDEX ix_e ON en;
SELECT MIN(e) AS min_e, MAX(e) AS max_e FROM en;
-- actual: ('a', 'C')
```
How to repeat:
Measured on MySQL 9.7.1 (Homebrew, macOS 26.4, arm64), default server
configuration, database collation `utf8mb4_0900_ai_ci`. The script above is
self-contained; run it with
`mysql -h 127.0.0.1 -u root --table < file`. Failure observations:
- Step 2 returns `('b','ab')` where step 1 returned `('a','C')` for the identical
statement. One of the two must be wrong.
- Step 4 returns `agg_max='ab'` and `window_max='C'` from a single statement.
- Step 6 returns `('a','C')` again once the index is dropped.
- `SHOW WARNINGS` is empty at every step.
Suggested fix:
We suspect the MIN/MAX index optimization reads the first and last key of `ix_e`
and returns the stored `ENUM` value without applying the string-value comparison
that the aggregation path applies. The index is ordered by the internal `ENUM`
position — the same order `ORDER BY` uses — so its endpoints are the
definition-order extremes. This is consistent with the observation that, with the
index present, `MIN(e)`/`MAX(e)` coincide with `ORDER BY e ASC/DESC LIMIT 1` and
with `MIN(e+0)`/`MAX(e+0)`. This is an inference from observed behaviour; we have
not confirmed it against the source.
