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

Hint BNL() doesn't work when join_cache_level>= 5 or join_cache_hashed=off

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 12.0
    • 12.0.1
    • Optimizer
    • None

    Description

      Testcase:
      Tables:

      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;
      

      Enable join_cache_hashed=on and set join_cache_level = 3
      and check, that query plan use BNLH

      set optimizer_switch= 'join_cache_hashed=on,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);
      

      Actual result:

      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	hash_ALL	a	#hash#a	5	test.t12.a	10	Using where; Using join buffer (flat, BNLH join)
      

      Now set join_cache_level = 5 and use hint BNL():

      set join_cache_level = 5;
      EXPLAIN  SELECT /*+ BNL() */ * FROM t12, t13
        WHERE t12.a=t13.a AND (t13.b+1 <= t12.b+1);
      

      Actual result:

      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
      

      Expected result:
      It is expected to be used BNL(H) for query plan

      If disable join_cache_hashed=off it does not matter witch type of join_cache_level is used - BNL(H) doesn't enable with hint BNL()

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              lstartseva Lena Startseva
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.