[MDEV-18467] Server crashes in fix_semijoin_strategies_for_picked_join_order Created: 2019-02-04  Updated: 2020-06-04  Resolved: 2019-03-14

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.3, 10.4
Fix Version/s: 10.3.14

Type: Bug Priority: Major
Reporter: Alice Sherepa Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Duplicate
is duplicated by MDEV-22793 when i using specific query, mariadb ... Closed

 Description   

Repeatable with InnoDB on 10.3-10.4, not with MyIsam

--source include/have_innodb.inc
 
create table t1 (f1 int, f2 int , f3 int , f4 int, key(f2)) engine=InnoDB;
 
create table t2 (f1 int, f2 int, f3 int , f4 int, primary key (f1, f2, f3)) engine=InnoDB;
 
 
 
create view v1 as select f1, max(f2) as f2, f3, f4 from t2 group by f1;
 
create view v2 as select t1.* from v1 join t1 using (f1) where t1.f2 = null;
 
 
 
select v2.f2 from t1 left join v2 using (f1);

10.3 36be0a5aef0376c526d68007da1

 
#4  0x000055aaf80c5a5f in fix_semijoin_strategies_for_picked_join_order (join=0x7f176403d020) at /10.3/sql/opt_subselect.cc:3556
 
#5  0x000055aaf7f64ea1 in JOIN::get_best_combination (this=0x7f176403d020) at /10.3/sql/sql_select.cc:9559
 
#6  0x000055aaf7f4ec38 in JOIN::optimize_stage2 (this=0x7f176403d020) at /10.3/sql/sql_select.cc:1935
 
#7  0x000055aaf7f4d08f in JOIN::optimize (this=0x7f176403d020) at /10.3/sql/sql_select.cc:1444
 
#8  0x000055aaf7ebfe2e in mysql_derived_optimize (thd=0x7f1764000b00, lex=0x7f17640048f0, derived=0x7f1764018b20) at /10.3/sql/sql_derived.cc:935
 
#9  0x000055aaf7ebe3f6 in mysql_handle_single_derived (lex=0x7f17640048f0, derived=0x7f1764018b20, phases=4) at /10.3/sql/sql_derived.cc:197
 
#10 0x000055aaf802a094 in TABLE_LIST::handle_derived (this=0x7f1764018b20, lex=0x7f17640048f0, phases=4) at /10.3/sql/table.cc:8180
 
#11 0x000055aaf7ee1dc1 in st_select_lex::handle_derived (this=0x7f1764016e68, lex=0x7f17640048f0, phases=4) at /10.3/sql/sql_lex.cc:4103
 
#12 0x000055aaf802a05a in TABLE_LIST::handle_derived (this=0x7f17640155b8, lex=0x7f17640048f0, phases=4) at /10.3/sql/table.cc:8177
 
#13 0x000055aaf7ee1dc1 in st_select_lex::handle_derived (this=0x7f1764005128, lex=0x7f17640048f0, phases=4) at /10.3/sql/sql_lex.cc:4103
 
#14 0x000055aaf7f4ec7d in JOIN::optimize_stage2 (this=0x7f176403c9d0) at /10.3/sql/sql_select.cc:1938
 
#15 0x000055aaf7f4eb39 in JOIN::optimize_inner (this=0x7f176403c9d0) at /10.3/sql/sql_select.cc:1914
 
#16 0x000055aaf7f4d0c3 in JOIN::optimize (this=0x7f176403c9d0) at /10.3/sql/sql_select.cc:1451
 
#17 0x000055aaf7f56c84 in mysql_select (thd=0x7f1764000b00, tables=0x7f1764014f20, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f176403c9a8, unit=0x7f17640049b8, select_lex=0x7f1764005128) at /10.3/sql/sql_select.cc:4225
 
#18 0x000055aaf7f48cae in handle_select (thd=0x7f1764000b00, lex=0x7f17640048f0, result=0x7f176403c9a8, setup_tables_done_option=0) at /10.3/sql/sql_select.cc:385
 
#19 0x000055aaf7f13657 in execute_sqlcom_select (thd=0x7f1764000b00, all_tables=0x7f1764014f20) at /10.3/sql/sql_parse.cc:6551
 
#20 0x000055aaf7f099f3 in mysql_execute_command (thd=0x7f1764000b00) at /10.3/sql/sql_parse.cc:3772
 
#21 0x000055aaf7f1760c in mysql_parse (thd=0x7f1764000b00, rawbuf=0x7f1764014ce8 "select v2.f2 from t1 left join v2 using (f1)", length=44, parser_state=0x7f17b5297470, is_com_multi=false, is_next_command=false) at /10.3/sql/sql_parse.cc:8095
 
#22 0x000055aaf7f044af in dispatch_command (command=COM_QUERY, thd=0x7f1764000b00, packet=0x7f17641683f1 "select v2.f2 from t1 left join v2 using (f1)", packet_length=44, is_com_multi=false, is_next_command=false) at /10.3/sql/sql_parse.cc:1854
 
#23 0x000055aaf7f02e80 in do_command (thd=0x7f1764000b00) at /10.3/sql/sql_parse.cc:1396
 
#24 0x000055aaf806abf5 in do_handle_one_connection (connect=0x55aafb6f8fd0) at /10.3/sql/sql_connect.cc:1403
 
#25 0x000055aaf806a946 in handle_one_connection (arg=0x55aafb6f8fd0) at /10.3/sql/sql_connect.cc:1309
 
#26 0x000055aaf8944b60 in pfs_spawn_thread (arg=0x55aafb701570) at /10.3/storage/perfschema/pfs.cc:1862
 
#27 0x00007f17bc8c86ba in start_thread (arg=0x7f17b5298700) at pthread_create.c:333
 
#28 0x00007f17bbd5d41d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109



 Comments   
Comment by Igor Babaev [ 2019-02-24 ]

With setting

set optimizer_switch='split_materialized=off';

there is no problem

MariaDB [test]> select v2.f2 from t1 left join v2 using (f1);
 
Empty set

Comment by Igor Babaev [ 2019-02-24 ]

With non-empty tables

insert into t1 values (3,33,303,405), (7,77,707,805), (1,11,101,205);
 
insert into t2 values (3,33,303,405), (9,99,909,805), (1,11,101,205);

the crash still can be observed

After conversion to MyISAM tables the crash disappears:

 
 
MariaDB [test]> alter table t1 engine=myisam;
 
Query OK, 3 rows affected (0.035 sec)              
Records: 3  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> alter table t2 engine=myisam;
 
Query OK, 3 rows affected (0.052 sec)              
Records: 3  Duplicates: 0  Warnings: 0
 
MariaDB [test]> select v2.f2 from t1 left join v2 using (f1);
 
+------+
 
| f2   |
 
+------+
 
| NULL |
 
| NULL |
 
| NULL |
 
+------+
 
3 rows

Comment by Igor Babaev [ 2019-03-14 ]

A fix for this bug was pushed into 10.3

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