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

LIMIT_ROWS_EXAMINED: query with the limit and NOT EXISTS, without GROUP BY or aggregate, returns rows, while the same query without the limit returns empty set

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 5.3.6
    • Component/s: None
    • Labels:
      None

      Description

      The following query

      SELECT * FROM t1 AS alias1, t2 AS alias2 
      WHERE NOT EXISTS ( 
        SELECT * FROM t1 LEFT OUTER JOIN t3 
          ON (d = a) 
        WHERE b <= alias1.b OR e != alias2.c  
      )
      LIMIT_ROWS_EXAMINED 20

      on the test data returns two rows, while the same query without LIMIT_ROWS_EXAMINED clause returns an empty set.

      bzr version-info

      revision-id: timour@askmonty.org-20120213145248-tlvsd794o83odf2r
      date: 2012-02-13 16:52:48 +0200
      build-date: 2012-02-21 07:04:39 +0400
      revno: 3417

      EXPLAIN:

      id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
      1       PRIMARY alias1  ALL     NULL    NULL    NULL    NULL    3       100.00
      1       PRIMARY alias2  ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Using join buffer (flat, BNL join)
      2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    3       100.00
      2       DEPENDENT SUBQUERY      t3      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Using join buffer (flat, BNL join)
      Warnings:
      Note    1276    Field or reference 'test.alias1.b' of SELECT #2 was resolved in SELECT #1
      Note    1276    Field or reference 'test.alias2.c' of SELECT #2 was resolved in SELECT #1
      Note    1003    select `test`.`alias1`.`a` AS `a`,`test`.`alias1`.`b` AS `b`,`test`.`alias2`.`c` AS `c` from `test`.`t1` `alias1` join `test`.`t2` `alias2` where (not(<expr_cache><`test`.`alias1`.`b`,`test`.`alias2`.`c`>(exists(select 1 from `test`.`t1` left join `test`.`t3` on((`test`.`t3`.`d` = `test`.`t1`.`a`)) where ((`test`.`t1`.`b` <= `test`.`alias1`.`b`) or (`test`.`t3`.`e` <> `test`.`alias2`.`c`))))))

      optimizer_switch:

      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

      Test case:

      CREATE TABLE t1 ( a INT, b INT );
      INSERT INTO t1 VALUES 
        (3911,17),(3847,33),(3857,26);
       
      CREATE TABLE t2 ( c VARCHAR(16) );
      INSERT INTO t2 VALUES 
        ('English'),('French'),('German');
       
      CREATE TABLE t3 ( d INT, e VARCHAR(32) );
      INSERT INTO t3 VALUES 
        (3813,'United States'),(3814,'United States');
       
      SELECT * FROM t1 AS alias1, t2 AS alias2 
      WHERE NOT EXISTS ( 
        SELECT * FROM t1 LEFT OUTER JOIN t3 
          ON (d = a) 
        WHERE b <= alias1.b OR e != alias2.c  
      );
       
      SELECT * FROM t1 AS alias1, t2 AS alias2 
      WHERE NOT EXISTS ( 
        SELECT * FROM t1 LEFT OUTER JOIN t3 
          ON (d = a) 
        WHERE b <= alias1.b OR e != alias2.c  
      )
      LIMIT_ROWS_EXAMINED 20;

      Result:

      SELECT * FROM t1 AS alias1, t2 AS alias2 
      WHERE NOT EXISTS ( 
      SELECT * FROM t1 LEFT OUTER JOIN t3 
      ON (d = a) 
      WHERE b <= alias1.b OR e != alias2.c  
      );
      a       b       c
      SELECT * FROM t1 AS alias1, t2 AS alias2 
      WHERE NOT EXISTS ( 
      SELECT * FROM t1 LEFT OUTER JOIN t3 
      ON (d = a) 
      WHERE b <= alias1.b OR e != alias2.c  
      )
      LIMIT_ROWS_EXAMINED 20;
      a       b       c
      3847    33      English
      3857    26      English
      Warnings:
      Warning 1930    Query execution was interrupted. The query examined at least 25 rows, which exceeds LIMIT_ROWS_EXAMINED. The query result may be incomplete.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              timour Timour Katchaounov (Inactive)
              Reporter:
              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.