Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-154

LIMIT_ROWS_EXAMINED: query with OUTER JOIN in view or derived table, NOT IN examines 5 times more rows than the limit allows: limit ~ 30K, examined ~150K

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Won't Fix
    • None
    • None
    • None
    • None

    Description

      The following query

      SELECT * FROM v, t2
      WHERE ( c, c ) NOT IN ( SELECT c, d FROM t2, t3 )
      LIMIT_ROWS_EXAMINED 30000;

      where v is a view

      CREATE VIEW v AS
        SELECT alias1.*
        FROM t1 AS alias1 LEFT OUTER JOIN t1 AS alias2
          ON alias1.a = alias2.b;

      finishes with the warning "Warning 1930 Query execution was interrupted. The query examined at least 150491 rows" (the number is somewhat greater with InnoDB). It is confirmed by the status variables:

      Handler_read_key        1
      Handler_read_rnd        143742
      Handler_read_rnd_next   3461
      Handler_tmp_write       3289

      EXPLAIN:

      id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered    Extra
      1       PRIMARY alias1  system  NULL    NULL    NULL    NULL    1       100.00
      1       PRIMARY alias2  system  NULL    NULL    NULL    NULL    1       100.00
      1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    274     100.00  Using where
      2       MATERIALIZED    t3      ALL     NULL    NULL    NULL    NULL    12      100.00
      2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    274     100.00  Using join buffer (flat, BNL join)
      Warnings:
      Note    1003    select 'USAEnglish' AS `a`,86 AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` `alias1` left join `test`.`t1` `alias2` on(('USAEnglish' = NULL)) join `test`.`t2` where (not(<expr_cache><`test`.`t2`.`c`>(<in_optimizer>((`test`.`t2`.`c`,`test`.`t2`.`c`),(`test`.`t2`.`c`,`test`.`t2`.`c`) in ( <materialize> (select `test`.`t2`.`c`,`test`.`t3`.`d` from `test`.`t2` join `test`.`t3` ), <primary_index_lookup>(`test`.`t2`.`c` in <temporary table> on distinct_key where ((`test`.`t2`.`c` = `<subquery2>`.`c`) and (`test`.`t2`.`c` = `<subquery2>`.`d`))))))))

      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 05:49:21 +0400
      revno: 3417

      Test case is attached.

      Attachments

        Issue Links

          Activity

            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.