Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. 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

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • None
    • None
    • None
    • 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.

      Attachments

        Issue Links

          Activity

            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)
            @@ -736,7 +737,7 @@ bool st_select_lex_unit::exec()
            fake_select_lex->options, result)))

            { fake_select_lex->table_list.empty(); - DBUG_RETURN(TRUE); + goto err; }

            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);
            }

            timour Timour Katchaounov (Inactive) added a comment - 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) @@ -736,7 +737,7 @@ bool st_select_lex_unit::exec() fake_select_lex->options, result))) { fake_select_lex->table_list.empty(); - DBUG_RETURN(TRUE); + goto err; } 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); }

            The fix and test case added to MDEV-28.

            timour Timour Katchaounov (Inactive) added a comment - The fix and test case added to MDEV-28 .

            People

              timour Timour Katchaounov (Inactive)
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.