[MDEV-18467] Server crashes in fix_semijoin_strategies_for_picked_join_order - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.3.14
Component/s: Optimizer
Labels:
None

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

Attachments

Issue Links

is duplicated by

MDEV-22793 when i using specific query, mariadb restarting.

Closed

Activity

Ascending order - Click to sort in descending order

Alice Sherepa created issue - 2019-02-04 13:17

Alice Sherepa made changes - 2019-02-04 13:18

Field	Original Value	New Value
Assignee		Igor Babaev [ igor ]

Alice Sherepa made changes - 2019-02-04 13:28

Status

Open [ 1 ]

Confirmed [ 10101 ]

Alice Sherepa made changes - 2019-02-04 13:31

Description

{code:sql}
--source include/have_innodb.inc
SET default_storage_engine= InnoDB;

create table t1 (f1 int, f2 int , f3 int , f4 int, key(f2));
create table t2 (f1 int, f2 int, f3 int , f4 int, primary key (f1, f2, f3));

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);
{code}

{noformat:title=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

{noformat}

Repeatable with InnoDB on 10.3-10.4, not with MyIsam
{code:sql}
--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);
{code}

{noformat:title=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

{noformat}

Igor Babaev (Inactive) added a comment - 2019-02-24 22:37

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

Igor Babaev (Inactive) added a comment - 2019-02-24 22:37 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

Igor Babaev (Inactive) added a comment - 2019-02-24 22:50

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

Igor Babaev (Inactive) added a comment - 2019-02-24 22:50 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

Igor Babaev (Inactive) made changes - 2019-03-05 07:28

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Igor Babaev (Inactive) added a comment - 2019-03-14 20:11

A fix for this bug was pushed into 10.3

Igor Babaev (Inactive) added a comment - 2019-03-14 20:11 A fix for this bug was pushed into 10.3

Igor Babaev (Inactive) made changes - 2019-03-14 20:11

Fix Version/s		10.3.14 [ 23216 ]
Fix Version/s	10.3 [ 22126 ]
Fix Version/s	10.4 [ 22408 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Rick Pizzi (Inactive) made changes - 2019-05-15 13:58

Link

This issue relates to ~~MDEV-19484~~ [ ~~MDEV-19484~~ ]

Alice Sherepa made changes - 2019-05-20 16:31

Link

This issue relates to ~~MDEV-19484~~ [ ~~MDEV-19484~~ ]

Alice Sherepa made changes - 2019-06-03 13:26

Link

This issue relates to ~~MDEV-19615~~ [ ~~MDEV-19615~~ ]

Alice Sherepa made changes - 2019-06-03 13:28

Link

This issue relates to ~~MDEV-19615~~ [ ~~MDEV-19615~~ ]

Alice Sherepa made changes - 2020-06-04 07:12

Link

This issue is duplicated by ~~MDEV-22793~~ [ ~~MDEV-22793~~ ]

Sergei Golubchik made changes - 2021-12-06 21:48

Workflow

MariaDB v3 [ 92248 ]

MariaDB v4 [ 155635 ]

People

Assignee:: Igor Babaev (Inactive)

Reporter:: Alice Sherepa

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2019-02-04 13:17

Updated:: 2020-06-04 07:12

Resolved:: 2019-03-14 20:11

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration