Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8
-
None
Description
I am setting it to Minor despite it being a non-debug crash because of the low value of join_buffer_space_limit in the test, which can be considered a corner case. However, strangely it doesn't crash when the value is even lower, so it needs to be made sure that the problem is indeed in the extremely low limit and not in the certain (scalable) combination of the limit and the data volume.
Still, even if the problem is specific to the low value, it's worth ensuring that it doesn't crash, given that the session values can be changed by anyone.
The explicit setting of optimize_join_buffer_size switch is for 10.2-10.3, in 10.4+ it's on by default.
It's possible that the query can be simplified further, I didn't manage to do it in a reasonable time.
CREATE TABLE t1 ( |
a varchar(1024) CHARACTER SET utf8, |
b varchar(1024) CHARACTER SET utf8, |
c int |
) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES ('a','b',1),('c','d',2); |
|
CREATE TABLE t2 ( |
d varchar(1024) CHARACTER SET utf8, |
e varchar(8), |
f int |
) ENGINE=MyISAM;
|
INSERT INTO t2 VALUES ('g', 'h', 5) , ('i', 'j', 6); |
|
CREATE TABLE t3 (g int) ENGINE=MyISAM; |
INSERT INTO t3 VALUES (3),(4); |
|
CREATE TABLE t4 (h varchar(8)) ENGINE=MyISAM; |
INSERT INTO t4 VALUES ('e'),('f'); |
|
SET join_buffer_space_limit= 16384, optimizer_switch= 'optimize_join_buffer_size=on'; |
|
SELECT * FROM t1 LEFT JOIN t2 AS t2a LEFT JOIN t3 AS t3a ON t3a.g = t2a.f JOIN t2 AS t2b ON t2a.d = t2b.e JOIN t3 AS t3b ON t2a.f = t3b.g ON t1.c = t2a.f LEFT JOIN t4 ON t4.h = t1.a; |
|
# Cleanup
|
DROP TABLE t1, t2, t3, t4; |
10.2 6e09e7c1 |
#3 <signal handler called>
|
#4 0x000055fbad3c9fd3 in JOIN_CACHE::get_offset (this=0x7f478405a0c0, ofs_sz=4, ptr=0x7f47a5a5a5a1 <error: Cannot access memory at address 0x7f47a5a5a5a1>) at /data/src/10.2/sql/sql_join_cache.h:119
|
#5 0x000055fbad3ca0b9 in JOIN_CACHE::get_rec_ref (this=0x7f478405a0c0, ptr=0x7f47a5a5a5a5 <error: Cannot access memory at address 0x7f47a5a5a5a5>) at /data/src/10.2/sql/sql_join_cache.h:380
|
#6 0x000055fbad3c549b in JOIN_CACHE::get_record_by_pos (this=0x7f478405a1f0, rec_ptr=0x7f47a5a5a5a5 <error: Cannot access memory at address 0x7f47a5a5a5a5>) at /data/src/10.2/sql/sql_join_cache.cc:1645
|
#7 0x000055fbad3c5421 in JOIN_CACHE::get_record (this=0x7f478405a320) at /data/src/10.2/sql/sql_join_cache.cc:1613
|
#8 0x000055fbad3c833e in JOIN_CACHE_BNL::read_next_candidate_for_match (this=0x7f478405a320, rec_ptr=0x7f4784062f65 "") at /data/src/10.2/sql/sql_join_cache.cc:3585
|
#9 0x000055fbad3c6664 in JOIN_CACHE::join_matching_records (this=0x7f478405a320, skip_last=false) at /data/src/10.2/sql/sql_join_cache.cc:2292
|
#10 0x000055fbad3c5fd2 in JOIN_CACHE::join_records (this=0x7f478405a320, skip_last=false) at /data/src/10.2/sql/sql_join_cache.cc:2087
|
#11 0x000055fbad3c6172 in JOIN_CACHE::join_records (this=0x7f478405a1f0, skip_last=false) at /data/src/10.2/sql/sql_join_cache.cc:2133
|
#12 0x000055fbad3c6172 in JOIN_CACHE::join_records (this=0x7f478405a0c0, skip_last=false) at /data/src/10.2/sql/sql_join_cache.cc:2133
|
#13 0x000055fbad2a1de2 in sub_select_cache (join=0x7f4784052e00, join_tab=0x7f478405dc60, end_of_records=true) at /data/src/10.2/sql/sql_select.cc:18652
|
#14 0x000055fbad2a1ffc in sub_select (join=0x7f4784052e00, join_tab=0x7f478405d8b0, end_of_records=true) at /data/src/10.2/sql/sql_select.cc:18824
|
#15 0x000055fbad2a1ffc in sub_select (join=0x7f4784052e00, join_tab=0x7f478405d500, end_of_records=true) at /data/src/10.2/sql/sql_select.cc:18824
|
#16 0x000055fbad2a1ffc in sub_select (join=0x7f4784052e00, join_tab=0x7f478405d150, end_of_records=true) at /data/src/10.2/sql/sql_select.cc:18824
|
#17 0x000055fbad2a1805 in do_select (join=0x7f4784052e00, procedure=0x0) at /data/src/10.2/sql/sql_select.cc:18419
|
#18 0x000055fbad27b271 in JOIN::exec_inner (this=0x7f4784052e00) at /data/src/10.2/sql/sql_select.cc:3638
|
#19 0x000055fbad27a72c in JOIN::exec (this=0x7f4784052e00) at /data/src/10.2/sql/sql_select.cc:3433
|
#20 0x000055fbad27b8e2 in mysql_select (thd=0x7f4784000af0, tables=0x7f4784012780, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f47840183d8, unit=0x7f47840046e8, select_lex=0x7f4784004e28) at /data/src/10.2/sql/sql_select.cc:3833
|
#21 0x000055fbad26faa0 in handle_select (thd=0x7f4784000af0, lex=0x7f4784004628, result=0x7f47840183d8, setup_tables_done_option=0) at /data/src/10.2/sql/sql_select.cc:361
|
#22 0x000055fbad23b4ec in execute_sqlcom_select (thd=0x7f4784000af0, all_tables=0x7f4784012780) at /data/src/10.2/sql/sql_parse.cc:6218
|
#23 0x000055fbad231d6d in mysql_execute_command (thd=0x7f4784000af0) at /data/src/10.2/sql/sql_parse.cc:3524
|
#24 0x000055fbad23f223 in mysql_parse (thd=0x7f4784000af0, rawbuf=0x7f4784012458 "SELECT * FROM t1 LEFT JOIN t2 AS t2a LEFT JOIN t3 AS t3a ON t3a.g = t2a.f JOIN t2 AS t2b ON t2a.d = t2b.e JOIN t3 AS t3b ON t2a.f = t3b.g ON t1.c = t2a.f LEFT JOIN t4 ON t4.h = t1.a", length=181, parser_state=0x7f47a0297610, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:7733
|
#25 0x000055fbad22d54f in dispatch_command (command=COM_QUERY, thd=0x7f4784000af0, packet=0x7f478408d1a1 "", packet_length=181, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:1824
|
#26 0x000055fbad22bfca in do_command (thd=0x7f4784000af0) at /data/src/10.2/sql/sql_parse.cc:1377
|
#27 0x000055fbad382099 in do_handle_one_connection (connect=0x55fbb0c12cf0) at /data/src/10.2/sql/sql_connect.cc:1336
|
#28 0x000055fbad381e04 in handle_one_connection (arg=0x55fbb0c12cf0) at /data/src/10.2/sql/sql_connect.cc:1241
|
#29 0x000055fbadb98aa8 in pfs_spawn_thread (arg=0x55fbb0c1dae0) at /data/src/10.2/storage/perfschema/pfs.cc:1869
|
#30 0x00007f47a76eb4a4 in start_thread (arg=0x7f47a0298700) at pthread_create.c:456
|
#31 0x00007f47a581fd0f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:97
|
Reproducible the same way on debug, non-debug and asan builds.
Couldn't reproduce on 10.1.
Couldn't reproduce at least with this test case with InnoDB or Aria instead of MyISAM.
Execution plan:
explain extended
|
SELECT * FROM t1 LEFT JOIN t2 AS t2a LEFT JOIN t3 AS t3a ON t3a.g = t2a.f JOIN t2 AS t2b ON t2a.d = t2b.e JOIN t3 AS t3b ON t2a.f = t3b.g ON t1.c = t2a.f LEFT JOIN t4 ON t4.h = t1.a;
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
|
1 SIMPLE t2a ALL NULL NULL NULL NULL 2 100.00 Using where
|
1 SIMPLE t3a ALL NULL NULL NULL NULL 2 100.00 Using where
|
1 SIMPLE t2b ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
|
1 SIMPLE t3b ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join)
|
1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join)
|
Warnings:
|
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2a`.`d` AS `d`,`test`.`t2a`.`e` AS `e`,`test`.`t2a`.`f` AS `f`,`test`.`t3a`.`g` AS `g`,`test`.`t2b`.`d` AS `d`,`test`.`t2b`.`e` AS `e`,`test`.`t2b`.`f` AS `f`,`test`.`t3b`.`g` AS `g`,`test`.`t4`.`h` AS `h` from `test`.`t1` left join (`test`.`t2` `t2a` left join `test`.`t3` `t3a` on(`test`.`t3a`.`g` = `test`.`t1`.`c`) join `test`.`t2` `t2b` join `test`.`t3` `t3b`) on(`test`.`t2a`.`f` = `test`.`t1`.`c` and `test`.`t3b`.`g` = `test`.`t1`.`c` and `test`.`t2a`.`d` = convert(`test`.`t2b`.`e` using utf8)) left join `test`.`t4` on(convert(`test`.`t4`.`h` using utf8) = `test`.`t1`.`a`) where 1
|