Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.9, 10.3(EOL)
-
None
-
Crash is reproducible on server versions 10.3.5 to 10.3.9 inclusive.
Description
Run these statements and most of the time the server crashes on the SELECT statement:
SET optimizer_switch='split_materialized=on'; |
|
CREATE DATABASE IF NOT EXISTS `testdb` DEFAULT CHARACTER SET utf8; |
USE testdb; |
|
DROP TABLE IF EXISTS `t`; |
CREATE TABLE `t` ( |
`tId` bigint(20) NOT NULL, |
`tgId` bigint(20) DEFAULT NULL, |
`sgId` bigint(20) DEFAULT NULL, |
PRIMARY KEY (`tId`), |
KEY `fk_t_tgId` (`tgId`), |
KEY `sgId` (`sgId`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
INSERT INTO t (tId, tgId, sgId) |
VALUES ('1', NULL, '1'); |
|
DROP TABLE IF EXISTS `sgh`; |
CREATE TABLE `sgh` ( |
`sgId` bigint(20) NOT NULL, |
`psd` timestamp(3) NOT NULL DEFAULT '0000-00-00 00:00:00.000', |
PRIMARY KEY (`sgId`,`psd`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
INSERT INTO sgh (sgId, psd) VALUES |
('1', '2018-08-16 07:30:00.000'); |
|
DROP TABLE IF EXISTS `l`; |
CREATE TABLE `l` ( |
`lId` bigint(20) NOT NULL AUTO_INCREMENT, |
`a` bigint(20) NOT NULL, |
PRIMARY KEY (`lId`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
INSERT INTO l (a) VALUES (1); |
|
SELECT lId, sdate |
FROM
|
(
|
SELECT l.lId, sgh.psd AS sdate, t.sgId |
FROM l |
INNER JOIN t ON (l.a=t.tId) |
INNER JOIN sgh ON sgh.sgId=t.sgId |
GROUP BY l.lId, sdate, t.sgId |
) us
|
INNER JOIN sgh ON sgh.sgId=us.sgId AND sgh.psd=sdate |
GROUP BY lId, sdate; |
If you change the optization switch to this:
SET optimizer_switch='split_materialized=off'; |
You get the single (expected) row returned.
The split_materialized switch was added 10.3.4 and the code works in that version regardless of its value, a change in 10.3.5 seems to have broken it.
Note the crash occurs on Windows and Ubuntu host machines. I have attached the crash output from my syslog.
Regards,
Mark.
Thanks for the report and the test case!
Reproducible on Mariadb 10.3 with Innodb.
10.3 c5a9a63293f868f074def37c985fe2
#4 0x000055e729126073 in Index_statistics::get_avg_frequency (this=0x8f8f8f8f8f8f8f8f, i=0) at /10.3/sql/sql_statistics.h:416
#5 0x000055e729189476 in st_key::actual_rec_per_key (this=0x7f85fc0965c0, i=0) at /10.3/sql/table.cc:8473
#6 0x000055e729272099 in st_join_table::choose_best_splitting (this=0x7f85fc0440d8, record_count=1, remaining_tables=1) at /10.3/sql/opt_split.cc:927
#7 0x000055e7290bdb09 in best_access_path (join=0x7f85fc01a4d0, s=0x7f85fc0440d8, remaining_tables=1, idx=1, disable_jbuf=false, record_count=1, pos=0x7f85fc044b68, loose_scan_pos=0x7f86501d7c90) at /10.3/sql/sql_select.cc:6723
#8 0x000055e7290c2910 in best_extension_by_limited_search (join=0x7f85fc01a4d0, remaining_tables=1, idx=1, record_count=1, read_time=1.2, search_depth=61, prune_level=1, use_cond_selectivity=1) at /10.3/sql/sql_select.cc:8686
#9 0x000055e7290c2ded in best_extension_by_limited_search (join=0x7f85fc01a4d0, remaining_tables=3, idx=0, record_count=1, read_time=0, search_depth=62, prune_level=1, use_cond_selectivity=1) at /10.3/sql/sql_select.cc:8757
#10 0x000055e7290c0ed4 in greedy_search (join=0x7f85fc01a4d0, remaining_tables=3, search_depth=62, prune_level=1, use_cond_selectivity=1) at /10.3/sql/sql_select.cc:7920
#11 0x000055e7290c03bc in choose_plan (join=0x7f85fc01a4d0, join_tables=3) at /10.3/sql/sql_select.cc:7498
#12 0x000055e7290b9265 in make_join_statistics (join=0x7f85fc01a4d0, tables_list=..., keyuse_array=0x7f85fc01a7c0) at /10.3/sql/sql_select.cc:4992
#13 0x000055e7290ae1ba in JOIN::optimize_inner (this=0x7f85fc01a4d0) at /10.3/sql/sql_select.cc:1895
#14 0x000055e7290ac7f3 in JOIN::optimize (this=0x7f85fc01a4d0) at /10.3/sql/sql_select.cc:1448
#15 0x000055e7290b634a in mysql_select (thd=0x7f85fc000b00, tables=0x7f85fc018be0, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f85fc01a4a8, unit=0x7f85fc0049b0, select_lex=0x7f85fc005120) at /10.3/sql/sql_select.cc:4220
#16 0x000055e7290a8340 in handle_select (thd=0x7f85fc000b00, lex=0x7f85fc0048e8, result=0x7f85fc01a4a8, setup_tables_done_option=0) at /10.3/sql/sql_select.cc:382
#17 0x000055e729072c1c in execute_sqlcom_select (thd=0x7f85fc000b00, all_tables=0x7f85fc018be0) at /10.3/sql/sql_parse.cc:6547
#18 0x000055e729069073 in mysql_execute_command (thd=0x7f85fc000b00) at /10.3/sql/sql_parse.cc:3769
#19 0x000055e729076ba1 in mysql_parse (thd=0x7f85fc000b00, rawbuf=0x7f85fc014d18 "SELECT id3\nFROM\n(SELECT t3.id3, t2.i2, t1.id2 \nFROM t3 \nJOIN t1 ON t3.i3=t1.id1\nJOIN t2 ON t2.id2=t1.id2 \nGROUP BY t3.id3, t1.id2\n) tbl\nJOIN t2 ON t2.id2=tbl.id2", length=162, parser_state=0x7f86501d9470, is_com_multi=false, is_next_command=false) at /10.3/sql/sql_parse.cc:8089
#20 0x000055e729063b46 in dispatch_command (command=COM_QUERY, thd=0x7f85fc000b00, packet=0x7f85fc00b221 "SELECT id3\nFROM\n(SELECT t3.id3, t2.i2, t1.id2 \nFROM t3 \nJOIN t1 ON t3.i3=t1.id1\nJOIN t2 ON t2.id2=t1.id2 \nGROUP BY t3.id3, t1.id2\n) tbl\nJOIN t2 ON t2.id2=tbl.id2", packet_length=162, is_com_multi=false, is_next_command=false) at /10.3/sql/sql_parse.cc:1850
#21 0x000055e729062563 in do_command (thd=0x7f85fc000b00) at /10.3/sql/sql_parse.cc:1395
#22 0x000055e7291c8fe9 in do_handle_one_connection (connect=0x55e72c1c75c0) at /10.3/sql/sql_connect.cc:1402
#23 0x000055e7291c8d3a in handle_one_connection (arg=0x55e72c1c75c0) at /10.3/sql/sql_connect.cc:1308
#24 0x000055e729a9e0de in pfs_spawn_thread (arg=0x55e72c1daf60) at /10.3/storage/perfschema/pfs.cc:1862
#25 0x00007f8656fc16ba in start_thread (arg=0x7f86501da700) at pthread_create.c:333
#26 0x00007f865645641d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109