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

Derived table for hash join is created with a key

Details

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

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16307 Incorrect results when using BNLH join instead of BNL join with views

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            varun Varun Gupta (Inactive) added a comment -

            In the debugger
            I put a breakpoint on ha_heap::write_row

            (lldb) b ha_heap::write_row
            		 
            Breakpoint 1: where = mysqld`ha_heap::write_row(unsigned char*) + 20 at ha_heap.cc:234, address = 0x00000001007f2af4

            then i run the above select query and see

            (lldb) bt
            		 
            * thread #2, stop reason = breakpoint 1.1
            		 
              * frame #0: 0x00000001007f2af4 mysqld`ha_heap::write_row(this=0x00000001050c8a88, buf="?) at ha_heap.cc:234
            		 
                frame #1: 0x00000001004f239a mysqld`handler::ha_write_tmp_row(this=0x00000001050c8a88, buf="?) at sql_class.h:6267
            		 
                frame #2: 0x0000000100563941 mysqld`select_unit::send_data(this=0x00000001049dd3a0, values=0x00000001049db058) at sql_union.cc:149
            		 
                frame #3: 0x00000001004a7283 mysqld`end_send(join=0x00000001049dd488, join_tab=0x00000001050cbe38, end_of_records=false) at sql_select.cc:20413
            		 
                frame #4: 0x00000001004caa81 mysqld`evaluate_join_record(join=0x00000001049dd488, join_tab=0x00000001050cba88, error=0) at sql_select.cc:19453
            		 
                frame #5: 0x00000001004c9d31 mysqld`sub_select(join=0x00000001049dd488, join_tab=0x00000001050cba88, end_of_records=false) at sql_select.cc:19233
            		 
                frame #6: 0x00000001004afa0a mysqld`do_select(join=0x00000001049dd488, procedure=0x0000000000000000) at sql_select.cc:18773
            		 
                frame #7: 0x00000001004ae668 mysqld`JOIN::exec_inner(this=0x00000001049dd488) at sql_select.cc:4054
            		 
                frame #8: 0x00000001004ad76e mysqld`JOIN::exec(this=0x00000001049dd488) at sql_select.cc:3848
            		 
                frame #9: 0x0000000100483261 mysqld`mysql_select(thd=0x0000000105069a70, tables=0x00000001049dc1f8, wild_num=0, fields=0x00000001049db058, conds=0x0000000000000000, og_num=0, order=0x0000000000000000, group=0x0000000000000000, having=0x0000000000000000, proc_param=0x0000000000000000, select_options=2416184064, result=0x00000001049dd3a0, unit=0x00000001049da7c0, select_lex=0x00000001049daf30) at sql_select.cc:4253
            		 
                frame #10: 0x00000001003b4492 mysqld`mysql_derived_fill(thd=0x0000000105069a70, lex=0x000000010506d850, derived=0x00000001049d9b38) at sql_derived.cc:1152
            		 
                frame #11: 0x00000001003b4fd7 mysqld`mysql_handle_single_derived(lex=0x000000010506d850, derived=0x00000001049d9b38, phases=96) at sql_derived.cc:197
            		 
                frame #12: 0x00000001004bdca0 mysqld`st_join_table::preread_init(this=0x00000001050ce630) at sql_select.cc:12480
            		 
                frame #13: 0x00000001004ab8f7 mysqld`join_init_read_record(tab=0x00000001050ce630) at sql_select.cc:20160
            		 
                frame #14: 0x000000010067c758 mysqld`JOIN_TAB_SCAN::open(this=0x00000001050cf9b8) at sql_join_cache.cc:3348
            		 
                frame #15: 0x000000010067a023 mysqld`JOIN_CACHE::join_matching_records(this=0x00000001050b8c88, skip_last=false) at sql_join_cache.cc:2251
            		 
                frame #16: 0x00000001006799f3 mysqld`JOIN_CACHE::join_records(this=0x00000001050b8c88, skip_last=false) at sql_join_cache.cc:2088
            		 
                frame #17: 0x00000001004ca236 mysqld`sub_select_cache(join=0x00000001049dce30, join_tab=0x00000001050ce630, end_of_records=false) at sql_select.cc:19033
            		 
                frame #18: 0x00000001004caa81 mysqld`evaluate_join_record(join=0x00000001049dce30, join_tab=0x00000001050ce280, error=0) at sql_select.cc:19453
            		 
                frame #19: 0x00000001004c9f18 mysqld`sub_select(join=0x00000001049dce30, join_tab=0x00000001050ce280, end_of_records=false) at sql_select.cc:19272
            		 
                frame #20: 0x00000001004afa0a mysqld`do_select(join=0x00000001049dce30, procedure=0x0000000000000000) at sql_select.cc:18773
            		 
                frame #21: 0x00000001004ae668 mysqld`JOIN::exec_inner(this=0x00000001049dce30) at sql_select.cc:4054
            		 
                frame #22: 0x00000001004ad76e mysqld`JOIN::exec(this=0x00000001049dce30) at sql_select.cc:3848


            So we inside the view v1
            		 
            (lldb) p table->alias.Ptr = 0x00000001049d9b30 "v1"
            		 
            (lldb) p table->s->keys
            		 
            (uint) $1 = 1

            We see that the view still has the key, this is incorrect

            varun Varun Gupta (Inactive) added a comment - In the debugger I put a breakpoint on ha_heap::write_row (lldb) b ha_heap::write_row Breakpoint 1: where = mysqld`ha_heap::write_row(unsigned char*) + 20 at ha_heap.cc:234, address = 0x00000001007f2af4 then i run the above select query and see (lldb) bt * thread #2, stop reason = breakpoint 1.1 * frame #0: 0x00000001007f2af4 mysqld`ha_heap::write_row(this=0x00000001050c8a88, buf="?) at ha_heap.cc:234 frame #1: 0x00000001004f239a mysqld`handler::ha_write_tmp_row(this=0x00000001050c8a88, buf="?) at sql_class.h:6267 frame #2: 0x0000000100563941 mysqld`select_unit::send_data(this=0x00000001049dd3a0, values=0x00000001049db058) at sql_union.cc:149 frame #3: 0x00000001004a7283 mysqld`end_send(join=0x00000001049dd488, join_tab=0x00000001050cbe38, end_of_records=false) at sql_select.cc:20413 frame #4: 0x00000001004caa81 mysqld`evaluate_join_record(join=0x00000001049dd488, join_tab=0x00000001050cba88, error=0) at sql_select.cc:19453 frame #5: 0x00000001004c9d31 mysqld`sub_select(join=0x00000001049dd488, join_tab=0x00000001050cba88, end_of_records=false) at sql_select.cc:19233 frame #6: 0x00000001004afa0a mysqld`do_select(join=0x00000001049dd488, procedure=0x0000000000000000) at sql_select.cc:18773 frame #7: 0x00000001004ae668 mysqld`JOIN::exec_inner(this=0x00000001049dd488) at sql_select.cc:4054 frame #8: 0x00000001004ad76e mysqld`JOIN::exec(this=0x00000001049dd488) at sql_select.cc:3848 frame #9: 0x0000000100483261 mysqld`mysql_select(thd=0x0000000105069a70, tables=0x00000001049dc1f8, wild_num=0, fields=0x00000001049db058, conds=0x0000000000000000, og_num=0, order=0x0000000000000000, group=0x0000000000000000, having=0x0000000000000000, proc_param=0x0000000000000000, select_options=2416184064, result=0x00000001049dd3a0, unit=0x00000001049da7c0, select_lex=0x00000001049daf30) at sql_select.cc:4253 frame #10: 0x00000001003b4492 mysqld`mysql_derived_fill(thd=0x0000000105069a70, lex=0x000000010506d850, derived=0x00000001049d9b38) at sql_derived.cc:1152 frame #11: 0x00000001003b4fd7 mysqld`mysql_handle_single_derived(lex=0x000000010506d850, derived=0x00000001049d9b38, phases=96) at sql_derived.cc:197 frame #12: 0x00000001004bdca0 mysqld`st_join_table::preread_init(this=0x00000001050ce630) at sql_select.cc:12480 frame #13: 0x00000001004ab8f7 mysqld`join_init_read_record(tab=0x00000001050ce630) at sql_select.cc:20160 frame #14: 0x000000010067c758 mysqld`JOIN_TAB_SCAN::open(this=0x00000001050cf9b8) at sql_join_cache.cc:3348 frame #15: 0x000000010067a023 mysqld`JOIN_CACHE::join_matching_records(this=0x00000001050b8c88, skip_last=false) at sql_join_cache.cc:2251 frame #16: 0x00000001006799f3 mysqld`JOIN_CACHE::join_records(this=0x00000001050b8c88, skip_last=false) at sql_join_cache.cc:2088 frame #17: 0x00000001004ca236 mysqld`sub_select_cache(join=0x00000001049dce30, join_tab=0x00000001050ce630, end_of_records=false) at sql_select.cc:19033 frame #18: 0x00000001004caa81 mysqld`evaluate_join_record(join=0x00000001049dce30, join_tab=0x00000001050ce280, error=0) at sql_select.cc:19453 frame #19: 0x00000001004c9f18 mysqld`sub_select(join=0x00000001049dce30, join_tab=0x00000001050ce280, end_of_records=false) at sql_select.cc:19272 frame #20: 0x00000001004afa0a mysqld`do_select(join=0x00000001049dce30, procedure=0x0000000000000000) at sql_select.cc:18773 frame #21: 0x00000001004ae668 mysqld`JOIN::exec_inner(this=0x00000001049dce30) at sql_select.cc:4054 frame #22: 0x00000001004ad76e mysqld`JOIN::exec(this=0x00000001049dce30) at sql_select.cc:3848 So we inside the view v1 (lldb) p table->alias.Ptr = 0x00000001049d9b30 "v1" (lldb) p table->s->keys (uint) $1 = 1 We see that the view still has the key, this is incorrect
            varun Varun Gupta (Inactive) added a comment -

            An approach to handle this case would be that for join_cache_level=4 , we don't try to create a ref access because currently in the code we always switch ref access to HASH JOIN unconditionally. So looks like this is an overkill for now.

            varun Varun Gupta (Inactive) added a comment - An approach to handle this case would be that for join_cache_level=4 , we don't try to create a ref access because currently in the code we always switch ref access to HASH JOIN unconditionally. So looks like this is an overkill for now.

            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.