[MDEV-17211] Server crash on query Created: 2018-09-17  Updated: 2018-10-05  Resolved: 2018-09-18

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.3.9, 10.3
Fix Version/s: 10.3.10

Type: Bug Priority: Major
Reporter: Mark Anstice Assignee: Igor Babaev
Resolution: Fixed Votes: 1
Labels: None
Environment:

Crash is reproducible on server versions 10.3.5 to 10.3.9 inclusive.

Attachments: Text File crash.log    

 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.

 Comments   
Comment by Alice Sherepa [ 2018-09-17 ]

Thanks for the report and the test case!
Reproducible on Mariadb 10.3 with Innodb. 

CREATE TABLE t1 ( id1 int, i1 int, id2 int, PRIMARY KEY (id1), KEY (i1), KEY (id2)) ENGINE=InnoDB;
 
INSERT INTO t1 VALUES (1,1,1);
 
 
CREATE TABLE t2 (id2 int, i2 int) ENGINE=InnoDB;
 
INSERT INTO t2  VALUES (1, 1);
 
 
CREATE TABLE t3 (id3 int, i3 int, PRIMARY KEY (id3)) ENGINE=InnoDB;
 
INSERT INTO t3 VALUES (1,1);
 
 
SELECT id3 FROM 
(SELECT t3.id3, t2.i2, t1.id2  FROM t3 
   JOIN t1 ON t3.i3=t1.id1
 
   JOIN t2 ON t2.id2=t1.id2 
   GROUP BY t3.id3, t1.id2) tbl
 
 JOIN t2 ON t2.id2=tbl.id2;


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

Comment by Igor Babaev [ 2018-09-18 ]

A fix for this bug was pushed into 10.3.

Comment by Mark Anstice [ 2018-10-05 ]

I can confirm this issue is fixed for me in 10.3.10. Thank you to all involved.

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