Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:None
Description
The following query
SELECT a AS field1, alias2.d AS field2, alias2.f AS field3, alias2.e AS field4, b AS field5 FROM t1, t2 AS alias2, t2 AS alias3 WHERE alias3.c IN ( SELECT 9 UNION SELECT 4 ) GROUP BY field1, field2, field3, field4, field5 LIMIT_ROWS_EXAMINED 50338;
on the test data finishes with the warning " 1930 Query execution was interrupted. The query examined at least 50339 rows...". According to the session status, it examines over 1.2 million rows:
Handler_read_key 449 Handler_read_rnd 4990 Handler_read_rnd_next 426055 Handler_tmp_write 840053 Handler_write 4
EXPLAIN:
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 100.00 Using temporary; Using filesort 1 PRIMARY alias2 ALL NULL NULL NULL NULL 500 100.00 Using join buffer (flat, BNL join) 1 PRIMARY alias3 index NULL c 5 NULL 500 100.00 Using where; Using index; Using join buffer (incremental, BNL join) 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 select `test`.`t1`.`a` AS `field1`,`test`.`alias2`.`d` AS `field2`,`test`.`alias2`.`f` AS `field3`,`test`.`alias2`.`e` AS `field4`,`test`.`t1`.`b` AS `field5` from `test`.`t1` join `test`.`t2` `alias2` join `test`.`t2` `alias3` where <expr_cache><`test`.`alias3`.`c`>(<in_optimizer>(`test`.`alias3`.`c`,<exists>(select 9 having (<cache>(`test`.`alias3`.`c`) = <ref_null_helper>(9)) union select 4 having (<cache>(`test`.`alias3`.`c`) = <ref_null_helper>(4))))) group by `test`.`t1`.`a`,`test`.`alias2`.`d`,`test`.`alias2`.`f`,`test`.`alias2`.`e`,`test`.`t1`.`b`
Optimizer_switch (default):
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on
bzr version-info
revision-id: timour@askmonty.org-20120213145248-tlvsd794o83odf2r date: 2012-02-13 16:52:48 +0200 build-date: 2012-02-15 03:34:36 +0400 revno: 3417
Test case is attached.
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
MDEV-28 LIMIT ROWS EXAMINED clause to limit the number of rows examined during SELECT processing
-
- Closed
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
The fix and test case added to MDEV-28.
The problem was due to the fact that if UNION processing was aborted due to
exceeding LIMIT_ROWS_EXAMINED, the current implementation disabled the
limit in order to produce the partial result from the UNION. The limit was not
re-enabled, resulting in all subsequent query processing to continue normally.
The fix is to re-enable LIMIT_ROWS_EXAMINED:
=== modified file 'sql/sql_union.cc'
— sql/sql_union.cc 2012-02-13 14:52:48 +0000
+++ sql/sql_union.cc 2012-02-16 09:54:10 +0000
@@ -603,6 +603,7 @@ bool st_select_lex_unit::exec()
SELECT_LEX *select_cursor=first_select();
ulonglong add_rows=0;
ha_rows examined_rows= 0;
+ bool examined_rows_limit_enabled_save= thd->examined_rows_limit_enabled;
DBUG_ENTER("st_select_lex_unit::exec");
if (executed && !uncacheable && !describe)
{ fake_select_lex->table_list.empty(); - DBUG_RETURN(TRUE); + goto err; }@@ -736,7 +737,7 @@ bool st_select_lex_unit::exec()
fake_select_lex->options, result)))
fake_select_lex->join->no_const_tables= TRUE;
@@ -808,6 +809,8 @@ bool st_select_lex_unit::exec()
}
}
thd->lex->current_select= lex_select_save;
+err:
+ thd->examined_rows_limit_enabled= examined_rows_limit_enabled_save;
DBUG_RETURN(saved_error);
}