The Subquery That Returned Correct Results in 5.7 and Wrong Results in 8.0

Two weeks after upgrading from MySQL 5.7 to 8.0, the finance team flagged that the daily revenue report was showing totals 23% higher than the payment processor's numbers. The report had been accurate for three years. No code changes. No schema changes. The same SQL returned different numbers on the new version.

The Alert

We ran the revenue query on both the 5.7 read replica (not yet decommissioned) and the 8.0 primary. Same data — verified by checksumming both tables three times.

SELECT
  DATE(o.created_at) AS order_date,
  SUM(d.amount) AS daily_revenue
FROM orders o
JOIN (
  SELECT DISTINCT order_id, line_id, amount
  FROM order_line_items
  WHERE status = 'completed'
) d ON d.order_id = o.id
WHERE o.created_at >= '2026-06-01'
GROUP BY DATE(o.created_at)
ORDER BY order_date;

MySQL 5.7 result: $847,291. MySQL 8.0 result: $1,042,168. Difference: 23%. We ran both three times. Same numbers each time — perfectly reproducible. The 5.7 number matched the payment processor. The 8.0 number was wrong.

First Hypothesis: Data Inconsistency

Maybe the upgrade process corrupted data, or replication had diverged. We verified data integrity three ways: COUNT(*) on both tables matched. Checksum matched. A manual spot-check of 50 random orders showed identical amounts on both versions. The data was identical. The query was returning different results from the same data.

The Discovery

We compared execution plans.

EXPLAIN FORMAT=TREE
SELECT
  DATE(o.created_at) AS order_date,
  SUM(d.amount) AS daily_revenue
FROM orders o
JOIN (
  SELECT DISTINCT order_id, line_id, amount
  FROM order_line_items
  WHERE status = 'completed'
) d ON d.order_id = o.id
WHERE o.created_at >= '2026-06-01'
GROUP BY DATE(o.created_at);

MySQL 5.7 plan: materialized the subquery first (with DISTINCT), then joined. MySQL 8.0 plan: merged the derived table into the outer query — the derived_merge optimization. The DISTINCT was eliminated during the merge. Without DISTINCT, duplicate line items from a historical schema migration were counted twice, inflating the total by 23%.

We verified three times: on 5.7, EXPLAIN showed <derived2> (materialized). On 8.0, no derived table — the subquery was flattened inline. We confirmed by disabling derived_merge on 8.0:

SET SESSION optimizer_switch = 'derived_merge=off';

-- Re-run the query
SELECT DATE(o.created_at), SUM(d.amount)
FROM orders o
JOIN (SELECT DISTINCT order_id, line_id, amount
      FROM order_line_items WHERE status = 'completed') d
  ON d.order_id = o.id
WHERE o.created_at >= '2026-06-01'
GROUP BY DATE(o.created_at);

With derived_merge=off: $847,291. Matching 5.7 and the payment processor. We ran this three times. Identical each time. The derived_merge optimization was the cause — it incorrectly eliminated the DISTINCT when merging the subquery.

Incident Timeline

Root Cause — Product Bug

This was a known MySQL optimizer bug. The derived_merge optimization, enabled by default in MySQL 8.0, incorrectly merges derived tables containing DISTINCT into the outer query in certain join patterns. When the derived table is used in a SUM() aggregation, the deduplication step is lost, and duplicate rows inflate the aggregate. MySQL has acknowledged the bug in multiple bug reports. The optimizer should not merge a derived table if the DISTINCT is semantically required for correctness — but the cost-based decision does not account for correctness, only performance.

The 23% discrepancy was exact: the order_line_items table had historical duplicates from a data migration three years earlier. The DISTINCT subquery was a deliberate workaround for those duplicates. The optimizer optimization silently removed the workaround.

The Fix

-- Disable derived_merge optimizer switch
SET GLOBAL optimizer_switch = 'derived_merge=off';

-- Add to my.cnf for persistence
-- [mysqld]
-- optimizer_switch = "derived_merge=off"

-- Verify
SELECT @@optimizer_switch\G

Prevention

Every major MySQL upgrade now includes a validation suite that runs the 20 highest-value queries on both versions and compares result sets — not just execution time, but actual row-level output. A result set difference of even one row blocks the upgrade until the discrepancy is investigated. derived_merge remains disabled globally until MySQL provides a fix that respects DISTINCT semantics during merge.

The historical duplicates in order_line_items are being cleaned up as a separate project, but the DISTINCT remains as defense-in-depth. Removing the duplicates eliminates the symptom, but the optimizer bug would still exist — a future schema change or data migration could reintroduce duplicates, and without the DISTINCT, the incorrect results would return silently.

The 14-day detection gap was the most alarming aspect. For two weeks, the business operated on revenue numbers inflated by 23%. Downstream dashboards, forecasts, and automated pricing decisions all consumed the wrong data. We now run a daily reconciliation between the database revenue total and the payment processor total. A divergence exceeding 0.5% triggers an immediate alert and halts the automated reporting pipeline until the discrepancy is explained. This would have caught the bug on day one of the upgrade, not day fourteen.

New optimizer features are enabled one at a time in staging with result-set comparison testing before production rollout. We maintain a regression test suite of 200 queries with known-good result sets. Any optimizer flag change runs against this suite. A single result difference is a blocking finding regardless of the performance improvement the flag provides. Correctness is non-negotiable — performance is always secondary. that runs the 20 highest-value queries on both versions and compares result sets — not just performance. derived_merge remains disabled globally until MySQL provides a fix that respects DISTINCT semantics during merge. The historical duplicates in order_line_items are being cleaned up, but the DISTINCT remains as defense-in-depth. New optimizer features are enabled one-at-a-time in staging with result-set comparison testing before production rollout.