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

Derived table for hash join is created with a key

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: In Progress (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4
    • Fix Version/s: 10.3, 10.4
    • Component/s: Optimizer
    • Labels:
      None

      Description

      create table ten(a int primary key);
      insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
      create table one_k(a int primary key);
      insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
      create table t1 (a varchar(750), b varchar(750));
      insert into t1 select a,a from one_k;
      create table t2 as select a from t1 limit 10;
      create ALGORITHM=TEMPTABLE view v1 as select a,b from t1;
      set optimizer_switch='derived_with_keys=on';
      set join_cache_level=4;
      

      explain select * from t1, v1 where t1.a=v1.a;
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1000	Using where
      1	PRIMARY	<derived2>	hash_ALL	key0	#hash#key0	753	test.t1.a	1000	Using join buffer (flat, BNLH join)
      2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	1000
      

      In the explain output we see for <derived2> key #hash#key0 , where key0 is the key on the derived table created by the derived_with_keys optimization.
      If the keys on derived table are created and it is the best choice plan then we will always do a ref access on the derived table with key0 (key on derived table).
      If the hash join is preferred then we need to drop the derived keys, we cannot perform hash join with these keys

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              psergei Sergei Petrunia
              Reporter:
              varun Varun Gupta (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

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