| Bug #39414 | join->thd->examined_row_count set to 0 incorrectly in return_zero_rows | ||
|---|---|---|---|
| Submitted: | 12 Sep 2008 1:30 | Modified: | 10 Feb 2018 16:36 |
| Reporter: | r b | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S3 (Non-critical) |
| Version: | 5.0.67, 5.1, 6.0 bzr | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | last_rows_examined_count rows examined query | ||
[12 Sep 2008 9:04]
Sveta Smirnova
Thank you for the report.
EXPLAIN returns correct results for the query: 0 for PRIMARY query and 1 for DERIVED.
mysql> explain select * from (select f1.i from foo f1 force index (foo_i) straight_join foo f2 where f1.i = 0 and f2.i = 1 and f1.j = f2.j) Q\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: system
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 0
Extra: const row not found
*************************** 2. row ***************************
id: 2
select_type: DERIVED
table: f1
type: ref
possible_keys: foo_i
key: foo_i
key_len: 5
ref:
rows: 1
Extra: Using where
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: f2
type: ref
possible_keys: foo_i
key: foo_i
key_len: 5
ref:
rows: 1
Extra: Using where
3 rows in set (0.01 sec)
Why do you complain about value of join->thd->examined_row_count?
[12 Sep 2008 15:49]
r b
in this example at least 1 row had to be examined. The shortcircuit in the join() routine incorrectly clears the thd->examined_row_count in this case. My complaint is that with a larger dataset we may have examined thousands of rows before we call return_zero_rows and if this query was written to the slow query log the rows examined would show up as 0 but the query would have taken a long time to run.
[15 Sep 2008 6:55]
Sveta Smirnova
Thank you for the feedback. Verified as described. $cat bug39414.test create table foo(i int, j int); insert foo values(0, 0); create index foo_i on foo(i); select f1.i from foo f1 force index (foo_i) straight_join foo f2 where f1.i = 0 and f2.i = 1 and f1.j = f2.j; select * from (select f1.i from foo f1 force index (foo_i) straight_join foo f2 where f1.i = 0 and f2.i = 1 and f1.j = f2.j) Q; --exec cat $MYSQL_TEST_DIR/var/log/master-slow.log $cat bug39414-master.opt --log-slow-queries --long_query_time=0 $cat bug39414.result create table foo(i int, j int); insert foo values(0, 0); create index foo_i on foo(i); select f1.i from foo f1 force index (foo_i) straight_join foo f2 where f1.i = 0 and f2.i = 1 and f1.j = f2.j; i select * from (select f1.i from foo f1 force index (foo_i) straight_join foo f2 where f1.i = 0 and f2.i = 1 and f1.j = f2.j) Q; i /users/ssmirnova/src/mysql-6.0/sql/mysqld, Version: 6.0.8-alpha-debug-log (Source distribution). started with: Tcp port: 9306 Unix socket: /users/ssmirnova/src/mysql-6.0/mysql-test/var/tmp/master.sock Time Id Command Argument # Time: 080915 9:52:01 # User@Host: root[root] @ localhost [] # Query_time: 0.001407 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0 use test; SET timestamp=1221461521; create table foo(i int, j int); # User@Host: root[root] @ localhost [] # Query_time: 0.001008 Lock_time: 0.000762 Rows_sent: 0 Rows_examined: 0 SET timestamp=1221461521; insert foo values(0, 0); # User@Host: root[root] @ localhost [] # Query_time: 0.001371 Lock_time: 0.000945 Rows_sent: 0 Rows_examined: 0 SET timestamp=1221461521; select f1.i from foo f1 force index (foo_i) straight_join foo f2 where f1.i = 0 and f2.i = 1 and f1.j = f2.j; # User@Host: root[root] @ localhost [] # Query_time: 0.000901 Lock_time: 0.000307 Rows_sent: 0 Rows_examined: 0 SET timestamp=1221461521; select * from (select f1.i from foo f1 force index (foo_i) straight_join foo f2 where f1.i = 0 and f2.i = 1 and f1.j = f2.j) Q;
[15 Sep 2008 20:17]
r b
any idea of when the proposed fix might be "blessed"?
[18 Sep 2008 12:59]
Georgi Kodinov
r b, we don't have a finite date yet. But from how it looks there's high probability that this fix will be looked at after 5.1 goes GA.
[18 Sep 2008 17:38]
r b
updated the tags
[10 Feb 2018 16:36]
Roy Lyseng
Posted by developer: Starting from 5.7, return_zero_rows() sets THD::current_found_rows to zero, not examined_row_count. I think this is more correct.
Description: examine_row_count incorrectly maintained for nested select where one of the predicates does not match any rows How to repeat: create table foo(i int, j int) engine=innodb; insert foo values(0, 0); create index foo_i on foo(i); select f1.i from foo f1 force index (foo_i) straight_join foo f2 where f1.i = 0 and f2.i = 1 and f1.j = f2.j; -- at the end of this execution thd->last_rows_examined_count is set to 1; select * from (select f1.i from foo f1 force index (foo_i) straight_join foo f2 where f1.i = 0 and f2.i = 1 and f1.j = f2.j) Q; -- at the end of this execution thd->last_rows_examined_count is set to 0 -- The fact that the nested subselect returns 0 rows causes return_zero_rows to be invoked, which in turn sets join->thd->examined_row_count to 0. Why does return_zero_rows reset the examined row counter? Suggested fix: sql/sql_select.cc/return_zero_rows() 6456 { 6457 List_iterator_fast<Item> it(fields); 6458 Item *item; 6459 while ((item= it++)) 6460 item->no_rows_in_result(); 6461 result->send_data(fields); 6462 } 6463 result->send_eof(); // Should be safe 6464 } 6465 /* Update results for FOUND_ROWS */ // FIX: do NOT reset the thread local examined_row_count 6465 // join->thd->limit_found_rows= join->thd->examined_row_count= 0; join->thd->limit_found_rows= 0; 6467 DBUG_RETURN(0); 6468}