[MDEV-16714] Derived table for hash join is created with a key Created: 2018-07-09  Updated: 2023-12-07

Status: Stalled
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Varun Gupta (Inactive) Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-16307 Incorrect results when using BNLH joi... Closed

 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

 Comments   
Comment by Varun Gupta (Inactive) [ 2018-07-09 ]

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

Comment by Varun Gupta (Inactive) [ 2018-07-12 ]

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.

Generated at Thu Feb 08 08:31:00 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.