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

Derived table for hash join is created with a key

    XMLWordPrintable

Details

    • Bug
    • Status: Stalled (View Workflow)
    • Major
    • Resolution: Unresolved
    • 5.5, 10.0, 10.1, 10.2, 10.3, 10.4
    • 10.4
    • Optimizer
    • 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

              psergei Sergei Petrunia
              varun Varun Gupta (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.