Bug #120638 The value of Rows_examined is incorrect when there is a query involving a union of derived tables

• Submitted: 9 Jun 8:53
• Modified: 10 Jun 6:43
• Reporter: gang chen (OCA)
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.32
• OS: Any
• CPU Architecture: Any

[9 Jun 8:53] gang chen

Description:
The value of Rows_examined is incorrect when there is a query involving a union of derived tables

It is not possible to quickly locate the problematic SQL that causes 100% CPU usage based on the values of innodb_rows_read and innodb_buffer_pool_read_requests

How to repeat:
The reproduction method is as follows:
Enable the slow query log and execute the following SQL：

drop table if exists test_Rows_examined;  
create table  if not exists test_Rows_examined
(
 id int
);
insert into test_Rows_examined
values(1),(2),(3),(4),(5),(6),(7),(8),(9),(0);
SELECT SUM(num) as num FROM (
SELECT COUNT(1) num FROM test_Rows_examined 
UNION ALL
SELECT COUNT(1) num FROM  test_Rows_examined  where sleep(1)=0
) a;

-----------------------------------------------------------------
slow.log：

# Time: 2026-06-09T08:31:17.393565Z
# User@Host: root[root] @ localhost [::1]  Id:     9
# Query_time: 10.083400  Lock_time: 0.000003 Rows_sent: 1  Rows_examined: 2
use sss;
SET timestamp=1780993867;
SELECT SUM(num) as num FROM (
SELECT COUNT(1) num FROM test_Rows_examined 
UNION ALL

SELECT COUNT(1) num FROM  test_Rows_examined  where sleep(1)=0
) a
LIMIT 0, 300;

Rows_examined is displayed as 2 (incorrect), it should be 20

Suggested fix:
Fixing it will make it easier for users to locate the problem

[10 Jun 6:43] Øystein Grøvlen

Thank you, for your bug report.
This is a duplicate of Bug#109034.

The issue is not specific to derived tables with union, but can be observed for any derived table:

mysql> SELECT SUM(num) as num FROM ( SELECT COUNT(1) num FROM test_Rows_examined ) a;
+------+
| num  |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

mysql> select thread_id, digest_text, timer_wait/1000000000 "Time (ms)", rows_examined, rows_sent from performance_schema.events_statements_history order by timer_start desc limit 1\G
*************************** 1. row ***************************
    thread_id: 69
  digest_text: SELECT SUM ( `num` ) AS `num` FROM ( SELECT COUNT (?) `num` FROM `test_Rows_examined` ) `a`
    Time (ms): 0.8860
rows_examined: 1
    rows_sent: 1
1 row in set (0.00 sec)