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

BKA join cache buffer is employed despite join_cache_level=3 (flat BNLH)

    XMLWordPrintable

Details

    Description

      Join_cache_level=3 allows using only flat/incremental BNL and flat BNLH join buffers, but in fact BKA buffer is employed:

      CREATE TABLE t10(a INT);
      INSERT INTO t10 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
      CREATE TABLE t12(a INT, b INT);
      INSERT INTO t12 SELECT a,a from t10;
      CREATE TABLE t13(a INT, b INT, c INT, filler CHAR(100), key (a,b));
      INSERT INTO t13 select a,a,a, 'filler-data' FROM t10;
       
      set optimizer_switch= 'join_cache_hashed=off,join_cache_bka=on,mrr=on';
      set join_cache_level = 3;
      EXPLAIN  SELECT * FROM t12, t13
        WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
       
      drop table t10, t12, t13;
      

      EXPLAIN  SELECT * FROM t12, t13
      WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
      +------+-------------+-------+------+---------------+------+---------+------------+------+------------------------------------------------------------------------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref        | rows | Extra                                                                              |
      +------+-------------+-------+------+---------------+------+---------+------------+------+------------------------------------------------------------------------------------+
      |    1 | SIMPLE      | t12   | ALL  | NULL          | NULL | NULL    | NULL       | 10   | Using where                                                                        |
      |    1 | SIMPLE      | t13   | ref  | a             | a    | 5       | test.t12.a | 1    | Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan |
      +------+-------------+-------+------+---------------+------+---------+------------+------+------------------------------------------------------------------------------------+
      

      Attachments

        Issue Links

          Activity

            People

              oleg.smirnov Oleg Smirnov
              oleg.smirnov Oleg Smirnov
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.