Bug #73448 error rows_exmained in queries like "in (A union B)"

• Submitted: 31 Jul 2014 14:17
• Modified: 16 Mar 2015 15:41
• Reporter: xiaobin lin (OCA)
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.5+, 5.5.40
• OS: Any
• CPU Architecture: Any
• Tags: Rows_examined, UNION

[31 Jul 2014 14:17] xiaobin lin

Description:
error rows_examined calculation when queries like "in (A union B)"
It is because when calculating local variable examined_rows, the existing value of thd->rows_examined do not consider correctly. 

How to repeat:
drop table if exists t1;
create table t1(a int(11) , key(a));
insert into t1 values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19);
truncate table mysql.slow_log;
set long_query_time= 0;
set global log_output= 'TABLE';
select * from t1 z where z.a not in (select b.a from t1 b union select c.a from t1 c);
select rows_examined from mysql.slow_log where sql_text like 'select%';
drop table if exists t1;

Suggested fix:
As the patch file.

[31 Jul 2014 14:18] xiaobin lin

base 5.5. patch cooperator: [e-mail]

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bugfix_rows_examined.diff (application/octet-stream, text), 1.85 KiB.

[1 Aug 2014 8:22] MySQL Verification Team

Hello Xiaobin,

Thank you for the report and contribution.

Thanks,
Umesh

[16 Mar 2015 15:41] MySQL Verification Team

This is marked as duplicate of internal bug 17059925 which is closed with the change log:

Noted in 5.5.39, 5.6.20, 5.7.5 changelogs. 

For UNION statements, the rows-examined value was calculated 
incorrectly. This was manifest as too-large values for the 
ROWS_EXAMINED column of Performance Schema statement tables 
(such as events_statements_current).