[#MDEV-153] LIMIT_ROWS_EXAMINED: query with UNION, GROUP BY examines 25 times more rows than the limit allows: limit ~50K, Handler_tmp_write+Handler_read_rnd

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.

[MDEV-153] LIMIT_ROWS_EXAMINED: query with UNION, GROUP BY examines 25 times more rows than the limit allows: limit ~50K, Handler_tmp_write+Handler_read_rnd_next ~ 1.2M Created: 2012-02-15 Updated: 2012-02-17 Resolved: 2012-02-17
Status:	Closed
Project:	MariaDB Server
Component/s:	None
Affects Version/s:	None
Fix Version/s:	None

[MDEV-153] LIMIT_ROWS_EXAMINED: query with UNION, GROUP BY examines 25 times more rows than the limit allows: limit ~50K, Handler_tmp_write+Handler_read_rnd_next ~ 1.2M Created: 2012-02-15 Updated: 2012-02-17 Resolved: 2012-02-17