|
Reproducible on 5.5-10.5, with Myisam/Innodb, on debug and non-debug versions
CREATE TABLE t1 ( i1 int, id int, pk int NOT NULL, PRIMARY KEY (pk), KEY id (id));
|
INSERT INTO t1 VALUES (NULL,NULL,8);
|
|
CREATE TABLE t2 ( pk int NOT NULL auto_increment, i1 int, id int, PRIMARY KEY (pk), KEY id (id));
|
INSERT INTO t2 VALUES (63,NULL,NULL),(64,NULL,NULL),(65,NULL,NULL),(66,NULL,NULL),(69,NULL,NULL),(67,NULL,NULL),(68,NULL,NULL),(70,NULL,NULL),(78,NULL,NULL),(77,NULL,NULL),(75,NULL,NULL),(76,NULL,NULL);
|
|
CREATE TABLE t3 ( id int, i1 int, pk int NOT NULL, PRIMARY KEY (pk), KEY id (id));
|
INSERT INTO t3 VALUES (NULL,NULL,3),(NULL,NULL,9);
|
|
CREATE TABLE t4 ( i1 int, id int, pk int NOT NULL, PRIMARY KEY (pk), KEY id (id));
|
INSERT INTO t4 VALUES (NULL,NULL,3);
|
|
CREATE TABLE t5 ( i1 int, pk int NOT NULL auto_increment, id int, PRIMARY KEY (pk), KEY id (id));
|
INSERT INTO t5 VALUES (NULL,116,NULL),(NULL,123,NULL),(NULL,124,NULL),(NULL,125,NULL),(NULL,126,NULL);
|
|
CREATE VIEW v1 AS
|
SELECT t2.i1, t2.id, t2.pk FROM t1 JOIN t2
|
WHERE exists
|
(SELECT at3.pk FROM (t3 JOIN t3 at3 on t3.pk = at3.i1 ) WHERE at3.id = t2.pk);
|
|
CREATE PROCEDURE sp()
|
SELECT
|
(SELECT max(v1.pk) FROM (v1 JOIN (t2 AS dt2 JOIN t4 AS dt4
|
ON dt4.i1 = dt2.id) ON dt4.i1 = dt2.id)
|
WHERE (dt2.pk > v1.i1 AND v1.pk > v1.i1)) AS f1,
|
a1.id AS f3
|
FROM t2 AS a1, v1 AS a2
|
WHERE NOT EXISTS (SELECT t5.id FROM t5 WHERE t5.pk IN
|
(SELECT t2.i1 FROM t2 LEFT JOIN t4 ON t4.i1 = t2.pk))
|
GROUP BY f1, f3;
|
|
call sp();
|
call sp();
|
|
5.5
|
#3 <signal handler called>
|
#4 update_depend_map_for_order (join=0x7f6f7ad86a50, order=0x7f6f7b13d320) at /5.5/sql/sql_select.cc:11342
|
#5 0x0000559575678677 in remove_const (join=0x7f6f7ad86a50, first_order=0x7f6f7b13d320, cond=0x7f6f7ad867c8, change_list=true, simple_order=0x7f6f7ad86d6c) at /5.5/sql/sql_select.cc:11406
|
#6 0x000055957565dfb6 in JOIN::optimize (this=0x7f6f7ad86a50) at /5.5/sql/sql_select.cc:1612
|
#7 0x00005595756633d1 in mysql_select (thd=0x7f6f7ec72ea0, rref_pointer_array=0x7f6f7ada3f40, tables=0x7f6f7b15ff38, wild_num=0, fields=..., conds=0x7f6f7ad867c8, og_num=2, order=0x0, group=0x7f6f7b13d320, having=0x0, proc_param=0x0, select_options=2147749632, result=0x7f6f7ad86a30, unit=0x7f6f7ada35b8, select_lex=0x7f6f7ada3ca0) at /5.5/sql/sql_select.cc:3121
|
#8 0x0000559575659aab in handle_select (thd=0x7f6f7ec72ea0, lex=0x7f6f7ada3508, result=0x7f6f7ad86a30, setup_tables_done_option=0) at /5.5/sql/sql_select.cc:312
|
#9 0x00005595756327de in execute_sqlcom_select (thd=0x7f6f7ec72ea0, all_tables=0x7f6f7b15ff38) at /5.5/sql/sql_parse.cc:4683
|
#10 0x000055957562b70f in mysql_execute_command (thd=0x7f6f7ec72ea0) at /5.5/sql/sql_parse.cc:2221
|
#11 0x000055957590a544 in sp_instr_stmt::exec_core (this=0x7f6f7b13d4b0, thd=0x7f6f7ec72ea0, nextp=0x7f6f81c8c368) at /5.5/sql/sp_head.cc:3221
|
#12 0x0000559575909cfd in sp_lex_keeper::reset_lex_and_exec_core (this=0x7f6f7b13d4f0, thd=0x7f6f7ec72ea0, nextp=0x7f6f81c8c368, open_tables=false, instr=0x7f6f7b13d4b0) at /5.5/sql/sp_head.cc:2999
|
#13 0x000055957590a272 in sp_instr_stmt::execute (this=0x7f6f7b13d4b0, thd=0x7f6f7ec72ea0, nextp=0x7f6f81c8c368) at /5.5/sql/sp_head.cc:3145
|
#14 0x00005595759060ce in sp_head::execute (this=0x7f6f7ada2878, thd=0x7f6f7ec72ea0, merge_da_on_success=true) at /5.5/sql/sp_head.cc:1432
|
#15 0x0000559575907e4a in sp_head::execute_procedure (this=0x7f6f7ada2878, thd=0x7f6f7ec72ea0, args=0x7f6f7ec76e68) at /5.5/sql/sp_head.cc:2198
|
#16 0x0000559575630c14 in mysql_execute_command (thd=0x7f6f7ec72ea0) at /5.5/sql/sql_parse.cc:4120
|
#17 0x000055957563538f in mysql_parse (thd=0x7f6f7ec72ea0, rawbuf=0x7f6f7b187078 "call sp()", length=9, parser_state=0x7f6f81c8ce50) at /5.5/sql/sql_parse.cc:5928
|
#18 0x0000559575628cb2 in dispatch_command (command=COM_QUERY, thd=0x7f6f7ec72ea0, packet=0x7f6f7f7ba5e1 "call sp()", packet_length=9) at /5.5/sql/sql_parse.cc:1067
|
#19 0x0000559575627ec7 in do_command (thd=0x7f6f7ec72ea0) at /5.5/sql/sql_parse.cc:793
|
#20 0x000055957572cb2b in do_handle_one_connection (thd_arg=0x7f6f7ec72ea0) at /5.5/sql/sql_connect.cc:1268
|
#21 0x000055957572c8a4 in handle_one_connection (arg=0x7f6f7ec72ea0) at /5.5/sql/sql_connect.cc:1184
|
#22 0x0000559575aad171 in pfs_spawn_thread (arg=0x7f6f7ec9d140) at /5.5/storage/perfschema/pfs.cc:1015
|
#23 0x00007f6f826eefa3 in start_thread (arg=<optimized out>) at pthread_create.c:486
|
#24 0x00007f6f81ddb4cf in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
|
the same on non-debug build:
[ERROR] mysqld got signal 11 ;
|
|
Server version: 10.5.5-MariaDB-log
|
|
??:0(__restore_rt)[0x7f5a1d170730]
|
sql/sql_select.cc:14095(update_depend_map_for_order)[0x55dcbe26c08a]
|
sql/sql_select.cc:2693(JOIN::optimize_stage2())[0x55dcbe26d6a3]
|
sql/sql_select.cc:1620(JOIN::optimize())[0x55dcbe26d7a1]
|
sql/sql_select.cc:4641(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x55dcbe26e16e]
|
sql/sql_select.cc:417(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55dcbe210c30]
|
sql/sql_parse.cc:6209(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55dcbe219903]
|
sql/sql_parse.cc:5951(mysql_execute_command(THD*))[0x55dcbe180535]
|
sql/sp_head.cc:3767(sp_instr_stmt::exec_core(THD*, unsigned int*))[0x55dcbe1879db]
|
sql/sp_head.cc:3492(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*))[0x55dcbe18840b]
|
sql/sp_head.cc:3671(sp_instr_stmt::execute(THD*, unsigned int*))[0x55dcbe1837cf]
|
sql/sp_head.cc:1435(sp_head::execute(THD*, bool))[0x55dcbe184ba7]
|
psi/mysql_sp.h:79(inline_mysql_end_sp)[0x55dcbe210008]
|
sql/sql_parse.cc:3044(do_execute_sp(THD*, sp_head*))[0x55dcbe21448c]
|
sql/sql_parse.cc:3288(Sql_cmd_call::execute(THD*))[0x55dcbe219932]
|
sql/sql_parse.cc:5951(mysql_execute_command(THD*))[0x55dcbe20c092]
|
sql/sql_parse.cc:8010(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55dcbe216bf7]
|
sql/sql_parse.cc:1869(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55dcbe217b4d]
|
sql/sql_parse.cc:1347(do_command(THD*))[0x55dcbe2fd8f0]
|
sql/sql_connect.cc:1411(do_handle_one_connection(CONNECT*, bool))[0x55dcbe2fdcbd]
|
sql/sql_connect.cc:1313(handle_one_connection)[0x55dcbe6272db]
|
nptl/pthread_create.c:487(start_thread)[0x7f5a1d165fa3]
|
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7f5a1c76e4cf]
|
Nearly the same case (without group by f3):
CREATE TABLE t1 ( i1 int, id int, pk int NOT NULL, PRIMARY KEY (pk), KEY id (id));
|
INSERT INTO t1 VALUES (NULL,NULL,8);
|
|
CREATE TABLE t2 ( pk int NOT NULL auto_increment, i1 int, id int, PRIMARY KEY (pk), KEY id (id));
|
INSERT INTO t2 VALUES (63,NULL,NULL),(64,NULL,NULL),(65,NULL,NULL),(66,NULL,NULL),(69,NULL,NULL),(67,NULL,NULL),(68,NULL,NULL),(70,NULL,NULL),(78,NULL,NULL),(77,NULL,NULL),(75,NULL,NULL),(76,NULL,NULL);
|
|
CREATE TABLE t3 ( id int, i1 int, pk int NOT NULL, PRIMARY KEY (pk), KEY id (id));
|
INSERT INTO t3 VALUES (NULL,NULL,3),(NULL,NULL,9);
|
|
CREATE TABLE t4 ( i1 int, id int, pk int NOT NULL, PRIMARY KEY (pk), KEY id (id));
|
INSERT INTO t4 VALUES (NULL,NULL,3);
|
|
CREATE TABLE t5 ( i1 int, pk int NOT NULL auto_increment, id int, PRIMARY KEY (pk), KEY id (id));
|
INSERT INTO t5 VALUES (NULL,116,NULL),(NULL,123,NULL),(NULL,124,NULL),(NULL,125,NULL),(NULL,126,NULL);
|
|
CREATE VIEW v1 AS
|
SELECT t2.i1, t2.id, t2.pk FROM t1 JOIN t2
|
WHERE exists
|
(SELECT at3.pk FROM (t3 JOIN t3 at3 on t3.pk = at3.i1 ) WHERE at3.id = t2.pk);
|
|
CREATE PROCEDURE sp()
|
SELECT
|
(SELECT max(v1.pk) FROM (v1 JOIN (t2 AS dt2 JOIN t4 AS dt4
|
ON dt4.i1 = dt2.id) ON dt4.i1 = dt2.id)
|
WHERE (dt2.pk > v1.i1 AND v1.pk > v1.i1)) AS f1
|
FROM t2 AS a1, v1 AS a2
|
WHERE NOT EXISTS (SELECT t5.id FROM t5 WHERE t5.pk IN
|
(SELECT t2.i1 FROM t2 LEFT JOIN t4 ON t4.i1 = t2.pk))
|
GROUP BY f1;
|
|
call sp();
|
call sp();
|
#3 <signal handler called>
|
#4 0x000055fc5b85794f in get_sort_by_table (a=0x0, b=0x0, tables=..., const_tables=2) at /5.5/sql/sql_select.cc:21318
|
#5 0x000055fc5b82cd67 in make_join_statistics (join=0x7fcf58786a40, tables_list=..., conds=0x7fcf587867b8, keyuse_array=0x7fcf58786d68) at /5.5/sql/sql_select.cc:3636
|
#6 0x000055fc5b824a66 in JOIN::optimize (this=0x7fcf58786a40) at /5.5/sql/sql_select.cc:1264
|
#7 0x000055fc5b82b3d1 in mysql_select (thd=0x7fcf5c7f10a0, rref_pointer_array=0x7fcf587a3f10, tables=0x7fcf58b5fda0, wild_num=0, fields=..., conds=0x7fcf587867b8, og_num=1, order=0x0, group=0x7fcf58b3c978, having=0x0, proc_param=0x0, select_options=2147749632, result=0x7fcf58786a20, unit=0x7fcf587a3588, select_lex=0x7fcf587a3c70) at /5.5/sql/sql_select.cc:3121
|
#8 0x000055fc5b821aab in handle_select (thd=0x7fcf5c7f10a0, lex=0x7fcf587a34d8, result=0x7fcf58786a20, setup_tables_done_option=0) at /5.5/sql/sql_select.cc:312
|
#9 0x000055fc5b7fa7de in execute_sqlcom_select (thd=0x7fcf5c7f10a0, all_tables=0x7fcf58b5fda0) at /5.5/sql/sql_parse.cc:4683
|
#10 0x000055fc5b7f370f in mysql_execute_command (thd=0x7fcf5c7f10a0) at /5.5/sql/sql_parse.cc:2221
|
#11 0x000055fc5bad2544 in sp_instr_stmt::exec_core (this=0x7fcf58b3c9b8, thd=0x7fcf5c7f10a0, nextp=0x7fcf5f1ad368) at /5.5/sql/sp_head.cc:3221
|
#12 0x000055fc5bad1cfd in sp_lex_keeper::reset_lex_and_exec_core (this=0x7fcf58b3c9f8, thd=0x7fcf5c7f10a0, nextp=0x7fcf5f1ad368, open_tables=false, instr=0x7fcf58b3c9b8) at /5.5/sql/sp_head.cc:2999
|
#13 0x000055fc5bad2272 in sp_instr_stmt::execute (this=0x7fcf58b3c9b8, thd=0x7fcf5c7f10a0, nextp=0x7fcf5f1ad368) at /5.5/sql/sp_head.cc:3145
|
#14 0x000055fc5bace0ce in sp_head::execute (this=0x7fcf587a2878, thd=0x7fcf5c7f10a0, merge_da_on_success=true) at /5.5/sql/sp_head.cc:1432
|
#15 0x000055fc5bacfe4a in sp_head::execute_procedure (this=0x7fcf587a2878, thd=0x7fcf5c7f10a0, args=0x7fcf5c7f5068) at /5.5/sql/sp_head.cc:2198
|
#16 0x000055fc5b7f8c14 in mysql_execute_command (thd=0x7fcf5c7f10a0) at /5.5/sql/sql_parse.cc:4120
|
#17 0x000055fc5b7fd38f in mysql_parse (thd=0x7fcf5c7f10a0, rawbuf=0x7fcf58b87078 "call sp()", length=9, parser_state=0x7fcf5f1ade50) at /5.5/sql/sql_parse.cc:5928
|
#18 0x000055fc5b7f0cb2 in dispatch_command (command=COM_QUERY, thd=0x7fcf5c7f10a0, packet=0x7fcf5d339961 "call sp()", packet_length=9) at /5.5/sql/sql_parse.cc:1067
|
#19 0x000055fc5b7efec7 in do_command (thd=0x7fcf5c7f10a0) at /5.5/sql/sql_parse.cc:793
|
#20 0x000055fc5b8f4b2b in do_handle_one_connection (thd_arg=0x7fcf5c7f10a0) at /5.5/sql/sql_connect.cc:1268
|
#21 0x000055fc5b8f48a4 in handle_one_connection (arg=0x7fcf5c7f10a0) at /5.5/sql/sql_connect.cc:1184
|
#22 0x000055fc5bc75171 in pfs_spawn_thread (arg=0x7fcf5c81c140) at /5.5/storage/perfschema/pfs.cc:1015
|
#23 0x00007fcf5fc0ffa3 in start_thread (arg=<optimized out>) at pthread_create.c:486
|
#24 0x00007fcf5f2fc4cf in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
|
Also if prepared statement is used instead of procedure:
sql/sql_select.cc:24906(get_sort_by_table(st_order*, st_order*, List<TABLE_LIST>&, unsigned long long))[0x55d915db88f8]
|
sql/sql_select.cc:5267(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x55d915d29ced]
|
sql/sql_select.cc:2242(JOIN::optimize_inner())[0x55d915d0a8df]
|
sql/sql_select.cc:1618(JOIN::optimize())[0x55d915d03e3d]
|
sql/sql_select.cc:4641(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x55d915d2424b]
|
sql/sql_select.cc:417(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55d915cf65b5]
|
sql/sql_parse.cc:6209(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55d915c66cf0]
|
sql/sql_parse.cc:3931(mysql_execute_command(THD*))[0x55d915c55f68]
|
sql/sql_prepare.cc:4791(Prepared_statement::execute(String*, bool))[0x55d915cc38ea]
|
sql/sql_prepare.cc:4280(Prepared_statement::execute_loop(String*, bool, unsigned char*, unsigned char*))[0x55d915cbf1f1]
|
sql/sql_prepare.cc:3393(mysql_sql_stmt_execute(THD*))[0x55d915cb93f0]
|
sql/sql_parse.cc:3948(mysql_execute_command(THD*))[0x55d915c55fad]
|
sql/sql_parse.cc:7993(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55d915c71e47]
|
sql/sql_parse.cc:1869(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55d915c488e2]
|
sql/sql_parse.cc:1347(do_command(THD*))[0x55d915c45160]
|
sql/sql_connect.cc:1411(do_handle_one_connection(CONNECT*, bool))[0x55d916073b5f]
|
sql/sql_connect.cc:1315(handle_one_connection)[0x55d9160734b8]
|
perfschema/pfs.cc:2203(pfs_spawn_thread)[0x55d916d38531]
|
nptl/pthread_create.c:463(start_thread)[0x7f66afa726db]
|
x86_64/clone.S:97(clone)[0x7f66aec58a3f]
|
|