[MDEV-659] LP:807198 - Rows_examined can be reset for sub queries Created: 2011-07-07  Updated: 2013-05-01  Resolved: 2013-05-01

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.1.67, 5.2.14
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Eric Bergen Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug807198.xml    

 Description   

Rows_examined in the slow query log can be reset by the outer query when using a sub query. For these two queries the first one sets Rows_examined correctly but the second one has it reset even though the row stats work correctly. 

# Time: 110702  9:44:23
 
# User@Host: [ebergen] @ localhost []
 
# Thread_id: 1  Schema: test  QC_hit: No
 
# Query_time: 1.396374  Lock_time: 0.000093  Rows_sent: 1  Rows_examined: 677984
 
# Row_Stats: test:rows_read=677984,rows_changed=0,rows_changed_x_indexes=0;
 
# Index_Stats: test:rows_read=677984;
 

SET timestamp=1309625063;
 
select count(*) from t2;
 

# Time: 110702  9:44:49
 
# User@Host: [ebergen] @ localhost []
 
# Thread_id: 1  Schema: test  QC_hit: No
 
# Query_time: 2.262701  Lock_time: 0.000126  Rows_sent: 1  Rows_examined: 0
 
# Row_Stats: test:rows_read=677984,rows_changed=0,rows_changed_x_indexes=0;#temp#:rows_read=0,rows_changed=677984,rows_changed_x_indexes=677984;
 
# Index_Stats: test:rows_read=677984;
 

SET timestamp=1309625089;
 
select count(*) from (select 1 from t2) as t;

The table I'm using looks like:

CREATE TABLE `t2` (
 
  `t` bigint(20) DEFAULT NULL,
 
  `u` bigint(20) DEFAULT NULL,
 
  `b` text,
 
  KEY `t` (`t`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1



 Comments   
Comment by Rasmus Johansson (Inactive) [ 2011-07-07 ]

Launchpad bug id: 807198

Comment by Elena Stepanova [ 2013-05-01 ]

Reproducible on MariaDB 5.1 and 5.2, but not on 5.3:

MariaDB Version 5.2.14

CREATE TABLE `t2` (
`t` bigint(20) DEFAULT NULL,
`u` bigint(20) DEFAULT NULL,
`b` text,
KEY `t` (`t`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO t2 VALUES (1,1,'a'),(2,2,'b'),(3,3,'c'),(4,4,'d');
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
SET GLOBAL slow_query_log = 1;
SET GLOBAL log_output = 'TABLE';
SET GLOBAL long_query_time = 0.0001;
select count from t2;
count
8192
select count from (select 1 from t2) as t;
count
8192
select start_time, rows_examined, sql_text from mysql.slow_log;
start_time rows_examined sql_text
2013-05-01 17:39:13 8192 select count from t2
2013-05-01 17:39:13 0 select count from (select 1 from t2) as t

MariaDB Version 5.3.12

CREATE TABLE `t2` (
`t` bigint(20) DEFAULT NULL,
`u` bigint(20) DEFAULT NULL,
`b` text,
KEY `t` (`t`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO t2 VALUES (1,1,'a'),(2,2,'b'),(3,3,'c'),(4,4,'d');
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
SET GLOBAL slow_query_log = 1;
SET GLOBAL log_output = 'TABLE';
SET GLOBAL long_query_time = 0.0001;
select count from t2;
count
8192
select count from (select 1 from t2) as t;
count
8192
select start_time, rows_examined, sql_text from mysql.slow_log;
start_time rows_examined sql_text
2013-05-01 17:39:32.628289 8192 select count from t2
2013-05-01 17:39:32.731437 8192 select count from (select 1 from t2) as t

Comment by Elena Stepanova [ 2013-05-01 ]

Fixed in 5.3 and further, I suppose it's good enough, not critical to fix it in 5.1 and 5.2.

Generated at Thu Feb 08 06:30:24 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.