Bug #120422 ORDER BY ignored when internal_tmp_mem_storage_engine = TempTable with GROUP BY on CTE

• Submitted: 8 May 8:56
• Reporter: Zining Zhou
• Status: Open
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.7.0 (also reproducible in 8.0.x)
• OS: Any
• CPU Architecture: Any

[8 May 8:56] Zining Zhou

Description:
The optimizer incorrectly removes the outer ORDER BY clause when GROUP BY is present, relying on the temporary table's auto-generated index (<auto_key0>) to provide ordering. However, when internal_tmp_mem_storage_engine = TempTable, the execution plan uses access_type: ALL (full table scan) while index_provides_order is still true. A full table scan on a TempTable does not guarantee index-ordered output, causing wrong results.

With MEMORY engine, access_type: index is chosen, which correctly preserves order.

How to repeat:
```sql
SET SESSION internal_tmp_mem_storage_engine = TempTable;

CREATE DATABASE IF NOT EXISTS test_db;
USE test_db;

CREATE TABLE profit_table (
  period_code VARCHAR(7) NOT NULL,
  consol_org VARCHAR(64) NOT NULL,
  ytenant_id VARCHAR(32) NOT NULL,
  net_profit_ptd DECIMAL(18,2) DEFAULT 0,
  net_profit_ytd DECIMAL(18,2) DEFAULT 0
);

CREATE TABLE cash_table (
  period_code VARCHAR(7) NOT NULL,
  consol_org VARCHAR(64) NOT NULL,
  ytenant_id VARCHAR(32) NOT NULL,
  net_cash_flow_oper DECIMAL(18,2) DEFAULT 0,
  net_cash_flow_oper_ytd DECIMAL(18,2) DEFAULT 0
);

INSERT INTO profit_table VALUES
('2023-12', 'org001', 'tenant001', 100, 1200),
('2024-12', 'org001', 'tenant001', 150, 1800),
('2025-12', 'org001', 'tenant001', 200, 2400),
('2026-06', 'org001', 'tenant001', 120, 3600);

INSERT INTO cash_table VALUES
('2023-12', 'org001', 'tenant001', 80, 900),
('2024-12', 'org001', 'tenant001', 120, 1500),
('2025-12', 'org001', 'tenant001', 160, 2100),
('2026-06', 'org001', 'tenant001', 100, 3000);

SELECT ipo.period_code AS u_period_code, SUM(nprofit) AS total
FROM (
  SELECT po.period_code AS period_code, IFNULL(pd.net_profit, 0) / 1 AS nprofit
  FROM (
    WITH period_offsets AS (
      SELECT '2024-06' AS period_code, '2026-06' AS period_0
      UNION ALL SELECT '2025-12', '2025-12'
      UNION ALL SELECT '2024-12', '2024-12'
      UNION ALL SELECT '2023-12', '2023-12'
    ),
    profit_base AS (
      SELECT period_code, SUM(net_profit_ytd) AS net_profit_ytd
      FROM profit_table
      WHERE period_code BETWEEN '2023-01' AND '2026-06'
        AND consol_org = 'org001'
        AND ytenant_id = 'tenant001'
      GROUP BY period_code
    ),
    cash_base AS (
      SELECT period_code, SUM(net_cash_flow_oper_ytd) AS ocf_ytd
      FROM cash_table
      WHERE period_code BETWEEN '2023-01' AND '2026-06'
        AND consol_org = 'org001'
        AND ytenant_id = 'tenant001'
      GROUP BY period_code
    ),
    base_aggregated AS (
      SELECT po.period_code,
        IFNULL(p.net_profit_ytd, 0) * 12 / NULLIF(CAST(SUBSTRING(po.period_code, 6, 2) AS DECIMAL), 0) AS net_profit,
        IFNULL(c.ocf_ytd, 0) * 12 / NULLIF(CAST(SUBSTRING(po.period_code, 6, 2) AS DECIMAL), 0) AS ocf
      FROM period_offsets po
      LEFT JOIN profit_base p ON p.period_code = po.period_0
      LEFT JOIN cash_base c ON c.period_code = po.period_0
    )
    SELECT po.period_code, IFNULL(pd.net_profit, 0) / 1 AS nprofit
    FROM period_offsets po
    LEFT JOIN base_aggregated pd ON pd.period_code = po.period_code
    ORDER BY po.period_code
  ) inner_q
) ipo
GROUP BY ipo.period_code
ORDER BY u_period_code ASC;
```

**Actual result (TempTable):**
```
2024-06   -- wrong order (insertion order from period_offsets)
2025-12
2024-12
2023-12
```

**Expected result (same as MEMORY engine):**
```
2023-12
2024-06
2024-12
2025-12
```

**Workaround:** SET SESSION internal_tmp_mem_storage_engine = MEMORY;

---

### Root Cause (from OPTIMIZER TRACE)

**TempTable engine:**
```json
"optimizing_distinct_group_by_order_by": {
  "removed_order_by": true
},
"reconsidering_access_paths_for_index_ordering": {
  "clause": "GROUP BY",
  "index_order_summary": {
    "index": "<auto_key0>",
    "access_type": "ALL",
    "index_provides_order": true,
    "plan_changed": true
  }
}
```

**MEMORY engine:**
```json
"reconsidering_access_paths_for_index_ordering": {
  "index_order_summary": {
    "index": "<auto_key0>",
    "access_type": "index",
    "index_provides_order": true,
    "plan_changed": true
  }
}
```

The optimizer removes ORDER BY because it believes the <auto_key0> index on the TempTable provides ordering. However, when access_type: ALL is chosen for TempTable, the full table scan returns rows in insertion order (from the UNION ALL CTE), not in index order. This is a mismatch between the optimizer's assumption and the storage engine's behavior.

---

Suggested fix:
Prevent index_provides_order from being true when access_type: ALL on TempTable, or force filesort in join_execution when the access method cannot guarantee index ordering.