|
Don Lindsay, thanks for the report. It is reproducible on Mariadb 5.5,10.0,10.1,10.2,10.3, with innodb and myisam
test case
CREATE TABLE `bugs` (`bug_id` int NOT NULL PRIMARY KEY, `product_id` int NOT NULL);
|
INSERT INTO `bugs` VALUES (45199,1184);
|
|
CREATE TABLE `maintainer` (`product_id` int NOT NULL,`userid` int NOT NULL, PRIMARY KEY (`product_id`,`userid`));
|
INSERT INTO `maintainer` VALUES (1184,103),(1184,624),(1184,1577),(1184,1582);
|
|
CREATE TABLE `product_groups` (`id` int NOT NULL PRIMARY KEY,`name` varchar(64));
|
CREATE TABLE `products` (`id` int NOT NULL PRIMARY KEY, `name` varchar(64));
|
|
CREATE TABLE `profiles` ( `userid` int NOT NULL PRIMARY KEY, `login_name` varchar(255));
|
INSERT INTO `profiles` VALUES (103,'foo'),(624,'foo'),(1577,'foo'),(1582,'foo');
|
|
select (
|
select login_name from profiles where userId = (
|
select userid from maintainer where product_id = bugs.product_id
|
union
|
select userid from maintainer where product_id = (
|
select id from products where name = (select name from product_groups where id = bugs.product_id)) limit 1 )
|
) from bugs where (bugs.bug_id=45199);
|
explain extended
|
select (
|
select login_name from profiles where userId = (
|
select userid from maintainer where product_id = bugs.product_id
|
union
|
select userid from maintainer where product_id = (
|
select id from products where name = (select name from product_groups where id = bugs.product_id)) limit 1 )
|
) from bugs where (bugs.bug_id=45199);
|
+------+--------------------+------------+--------+---------------+---------+---------+-------+------+----------+-----------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+--------------------+------------+--------+---------------+---------+---------+-------+------+----------+-----------------------------------------------------+
|
| 1 | PRIMARY | bugs | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | |
|
| 2 | DEPENDENT SUBQUERY | profiles | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 100.00 | Using where |
|
| 3 | SUBQUERY | maintainer | ref | PRIMARY | PRIMARY | 4 | const | 4 | 100.00 | Using index |
|
| 4 | DEPENDENT UNION | maintainer | ref | PRIMARY | PRIMARY | 4 | func | 2 | 100.00 | Using where; Using index |
|
| 5 | DEPENDENT SUBQUERY | products | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
|
| 6 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
|
| NULL | UNION RESULT | <union3,4> | ALL | NULL | NULL | NULL | NULL | NULL | NULL | |
|
+------+--------------------+------------+--------+---------------+---------+---------+-------+------+----------+-----------------------------------------------------+
|
7 rows in set, 3 warnings (0.00 sec)
|
|
Note (Code 1276): Field or reference 'test.bugs.product_id' of SELECT #3 was resolved in SELECT #1
|
Note (Code 1276): Field or reference 'test.bugs.product_id' of SELECT #6 was resolved in SELECT #1
|
Note (Code 1003): select <expr_cache><>((select `test`.`profiles`.`login_name` from `test`.`profiles` where `test`.`profiles`.`userid` = <expr_cache><>((select `test`.`maintainer`.`userid` from `test`.`maintainer` where `test`.`maintainer`.`product_id` = 1184 union select `test`.`maintainer`.`userid` from `test`.`maintainer` where `test`.`maintainer`.`product_id` = <expr_cache><>((select `test`.`products`.`id` from `test`.`products` where `test`.`products`.`name` = (select NULL from `test`.`product_groups` where 0))) limit 1)))) AS `(
|
select login_name from profiles where userId = (
|
select userid from maintainer where product_id = bugs.product_id
|
union
|
select userid from maintainer where product_id = (
|
select id from products where name = (select name from product_` from `test`.`bugs` where 1
|
stack trace from 10.2.9-debug
Thread 1 (Thread 0x7fa0c828a700 (LWP 19408)):
|
#0 0x0000007ec49f4d71 in join_read_next_same (info=0x7fa058096a58) at /home/alice/git/10.2/sql/sql_select.cc:19378
|
#1 0x0000007ec49f322b in sub_select (join=0x7fa05808c110, join_tab=0x7fa058096990, end_of_records=false) at /home/alice/git/10.2/sql/sql_select.cc:18582
|
#2 0x0000007ec49f26f2 in do_select (join=0x7fa05808c110, procedure=0x0) at /home/alice/git/10.2/sql/sql_select.cc:18107
|
#3 0x0000007ec49cd2ad in JOIN::exec_inner (this=0x7fa05808c110) at /home/alice/git/10.2/sql/sql_select.cc:3483
|
#4 0x0000007ec49cc74e in JOIN::exec (this=0x7fa05808c110) at /home/alice/git/10.2/sql/sql_select.cc:3278
|
#5 0x0000007ec4a69fdb in st_select_lex_unit::exec (this=0x7fa058077a70) at /home/alice/git/10.2/sql/sql_union.cc:1005
|
#6 0x0000007ec4cc162d in subselect_union_engine::exec (this=0x7fa058010670) at /home/alice/git/10.2/sql/item_subselect.cc:3888
|
#7 0x0000007ec4cb64af in Item_subselect::exec (this=0x7fa05807cea0) at /home/alice/git/10.2/sql/item_subselect.cc:716
|
#8 0x0000007ec4cb818d in Item_singlerow_subselect::val_int (this=0x7fa05807cea0) at /home/alice/git/10.2/sql/item_subselect.cc:1318
|
#9 0x0000007ec48c036f in Item::val_int_result (this=0x7fa05807cea0) at /home/alice/git/10.2/sql/item.h:1178
|
#10 0x0000007ec4c20cd5 in Item_cache_int::cache_value (this=0x7fa05809fb40) at /home/alice/git/10.2/sql/item.cc:9552
|
#11 0x0000007ec4c2aa9c in Item_cache_wrapper::cache (this=0x7fa05809fa50) at /home/alice/git/10.2/sql/item.cc:8275
|
#12 0x0000007ec4c1cf3a in Item_cache_wrapper::val_int (this=0x7fa05809fa50) at /home/alice/git/10.2/sql/item.cc:8329
|
#13 0x0000007ec4c31240 in Arg_comparator::compare_int_signed (this=0x7fa05807d0f0) at /home/alice/git/10.2/sql/item_cmpfunc.cc:989
|
#14 0x0000007ec4c44254 in Arg_comparator::compare (this=0x7fa05807d0f0) at /home/alice/git/10.2/sql/item_cmpfunc.h:87
|
#15 0x0000007ec4c3356f in Item_func_eq::val_int (this=0x7fa05807d030) at /home/alice/git/10.2/sql/item_cmpfunc.cc:1783
|
#16 0x0000007ec49f352c in evaluate_join_record (join=0x7fa05808b8e0, join_tab=0x7fa058094420, error=0) at /home/alice/git/10.2/sql/sql_select.cc:18658
|
#17 0x0000007ec49f315e in sub_select (join=0x7fa05808b8e0, join_tab=0x7fa058094420, end_of_records=false) at /home/alice/git/10.2/sql/sql_select.cc:18563
|
#18 0x0000007ec49f26f2 in do_select (join=0x7fa05808b8e0, procedure=0x0) at /home/alice/git/10.2/sql/sql_select.cc:18107
|
#19 0x0000007ec49cd2ad in JOIN::exec_inner (this=0x7fa05808b8e0) at /home/alice/git/10.2/sql/sql_select.cc:3483
|
#20 0x0000007ec49cc74e in JOIN::exec (this=0x7fa05808b8e0) at /home/alice/git/10.2/sql/sql_select.cc:3278
|
#21 0x0000007ec4cc13e6 in subselect_single_select_engine::exec (this=0x7fa058046b20) at /home/alice/git/10.2/sql/item_subselect.cc:3861
|
#22 0x0000007ec4cb64af in Item_subselect::exec (this=0x7fa05807d1d0) at /home/alice/git/10.2/sql/item_subselect.cc:716
|
#23 0x0000007ec4cb82a5 in Item_singlerow_subselect::val_str (this=0x7fa05807d1d0, str=0x7fa0580a08a8) at /home/alice/git/10.2/sql/item_subselect.cc:1335
|
#24 0x0000007ec48c03a2 in Item::str_result (this=0x7fa05807d1d0, tmp=0x7fa0580a08a8) at /home/alice/git/10.2/sql/item.h:1179
|
#25 0x0000007ec4c223c0 in Item_cache_str::cache_value (this=0x7fa0580a07a0) at /home/alice/git/10.2/sql/item.cc:9923
|
#26 0x0000007ec4c2aa9c in Item_cache_wrapper::cache (this=0x7fa0580a06b0) at /home/alice/git/10.2/sql/item.cc:8275
|
#27 0x0000007ec4c1d22e in Item_cache_wrapper::val_str (this=0x7fa0580a06b0, str=0x7fa0c8287a60) at /home/alice/git/10.2/sql/item.cc:8383
|
#28 0x0000007ec4c18e09 in Item::send (this=0x7fa0580a06b0, protocol=0x7fa058001030, buffer=0x7fa0c8287a60) at /home/alice/git/10.2/sql/item.cc:6889
|
#29 0x0000007ec4c1c99e in Item_cache_wrapper::send (this=0x7fa0580a06b0, protocol=0x7fa058001030, buffer=0x7fa0c8287a60) at /home/alice/git/10.2/sql/item.cc:8181
|
#30 0x0000007ec48bc1db in Protocol::send_result_set_row (this=0x7fa058001030, row_items=0x7fa058004ec0) at /home/alice/git/10.2/sql/protocol.cc:979
|
#31 0x0000007ec493ddb6 in select_send::send_data (this=0x7fa05808b120, items=...) at /home/alice/git/10.2/sql/sql_class.cc:2774
|
#32 0x0000007ec49f5cfa in end_send (join=0x7fa05808b170, join_tab=0x0, end_of_records=false) at /home/alice/git/10.2/sql/sql_select.cc:19726
|
#33 0x0000007ec49f2447 in do_select (join=0x7fa05808b170, procedure=0x0) at /home/alice/git/10.2/sql/sql_select.cc:18064
|
#34 0x0000007ec49cd2ad in JOIN::exec_inner (this=0x7fa05808b170) at /home/alice/git/10.2/sql/sql_select.cc:3483
|
#35 0x0000007ec49cc74e in JOIN::exec (this=0x7fa05808b170) at /home/alice/git/10.2/sql/sql_select.cc:3278
|
#36 0x0000007ec49cd91e in mysql_select (thd=0x7fa058000a98, tables=0x7fa05807d360, wild_num=0, fields=..., conds=0x7fa05807dad0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fa05808b120, unit=0x7fa058004638, select_lex=0x7fa058004d90) at /home/alice/git/10.2/sql/sql_select.cc:3678
|
#37 0x0000007ec49c21c9 in handle_select (thd=0x7fa058000a98, lex=0x7fa058004570, result=0x7fa05808b120, setup_tables_done_option=0) at /home/alice/git/10.2/sql/sql_select.cc:373
|
#38 0x0000007ec498e1b0 in execute_sqlcom_select (thd=0x7fa058000a98, all_tables=0x7fa05807d360) at /home/alice/git/10.2/sql/sql_parse.cc:6446
|
#39 0x0000007ec4983eb1 in mysql_execute_command (thd=0x7fa058000a98) at /home/alice/git/10.2/sql/sql_parse.cc:3460
|
#40 0x0000007ec4991b29 in mysql_parse (thd=0x7fa058000a98, rawbuf=0x7fa05801e440 "select (\n select login_name from profiles where userId = (\n select userid from maintainer where product_id = bugs.product_id\n union\n select userid from maintainer where product_id = (\n "..., length=353, parser_state=0x7fa0c8289240, is_com_multi=false, is_next_command=false) at /home/alice/git/10.2/sql/sql_parse.cc:7886
|
#41 0x0000007ec497f84b in dispatch_command (command=COM_QUERY, thd=0x7fa058000a98, packet=0x7fa058007e89 "", packet_length=353, is_com_multi=false, is_next_command=false) at /home/alice/git/10.2/sql/sql_parse.cc:1812
|
#42 0x0000007ec497e1be in do_command (thd=0x7fa058000a98) at /home/alice/git/10.2/sql/sql_parse.cc:1360
|
#43 0x0000007ec4acab66 in do_handle_one_connection (connect=0x7ec778a4f8) at /home/alice/git/10.2/sql/sql_connect.cc:1354
|
#44 0x0000007ec4aca8e6 in handle_one_connection (arg=0x7ec778a4f8) at /home/alice/git/10.2/sql/sql_connect.cc:1260
|
#45 0x00007fa0cba5a6ba in start_thread (arg=0x7fa0c828a700) at pthread_create.c:333
|
#46 0x00007fa0caeef3dd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109
|
error log
70912 11:10:17 [ERROR] mysqld got signal 11 ;
|
Server version: 10.2.9-MariaDB-debug
|
|
Thread pointer: 0x7fa058000a98
|
Attempting backtrace. You can use the following information to find out
|
where mysqld died. If you see no messages after this, something went
|
terribly wrong...
|
stack_bottom = 0x7fa0c8289ec0 thread_stack 0x49000
|
/data/bld/10.2/bin/mysqld(my_print_stacktrace+0x38)[0x7ec5364a15]
|
/data/bld/10.2/bin/mysqld(handle_fatal_signal+0x3a3)[0x7ec4bf125e]
|
/lib/x86_64-linux-gnu/libpthread.so.0(+0x11390)[0x7fa0cba64390]
|
/data/bld/10.2/bin/mysqld(+0x61bd71)[0x7ec49f4d71]
|
/data/bld/10.2/bin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x3b0)[0x7ec49f322b]
|
mysys/stacktrace.c:267(my_print_stacktrace)[0x7ec49f26f2]
|
sql/sql_select.cc:19378(join_read_next_same(READ_RECORD*))[0x7ec49cd2ad]
|
sql/sql_select.cc:3279(JOIN::exec())[0x7ec49cc74e]
|
sql/sql_union.cc:1006(st_select_lex_unit::exec())[0x7ec4a69fdb]
|
sql/item_subselect.cc:3888(subselect_union_engine::exec())[0x7ec4cc162d]
|
sql/item_subselect.cc:716(Item_subselect::exec())[0x7ec4cb64af]
|
sql/item_subselect.cc:1318(Item_singlerow_subselect::val_int())[0x7ec4cb818d]
|
/data/bld/10.2/bin/mysqld(+0x4e736f)[0x7ec48c036f]
|
/data/bld/10.2/bin/mysqld(_ZN14Item_cache_int11cache_valueEv+0x55)[0x7ec4c20cd5]
|
/data/bld/10.2/bin/mysqld(_ZN18Item_cache_wrapper5cacheEv+0x68)[0x7ec4c2aa9c]
|
/data/bld/10.2/bin/mysqld(_ZN18Item_cache_wrapper7val_intEv+0xee)[0x7ec4c1cf3a]
|
/data/bld/10.2/bin/mysqld(_ZN14Arg_comparator18compare_int_signedEv+0x78)[0x7ec4c31240]
|
/data/bld/10.2/bin/mysqld(_ZN14Arg_comparator7compareEv+0x64)[0x7ec4c44254]
|
/data/bld/10.2/bin/mysqld(_ZN12Item_func_eq7val_intEv+0x49)[0x7ec4c3356f]
|
/data/bld/10.2/bin/mysqld(+0x61a52c)[0x7ec49f352c]
|
/data/bld/10.2/bin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x2e3)[0x7ec49f315e]
|
/data/bld/10.2/bin/mysqld(+0x6196f2)[0x7ec49f26f2]
|
/data/bld/10.2/bin/mysqld(_ZN4JOIN10exec_innerEv+0xac1)[0x7ec49cd2ad]
|
/data/bld/10.2/bin/mysqld(_ZN4JOIN4execEv+0xcc)[0x7ec49cc74e]
|
/data/bld/10.2/bin/mysqld(_ZN30subselect_single_select_engine4execEv+0x620)[0x7ec4cc13e6]
|
/data/bld/10.2/bin/mysqld(_ZN14Item_subselect4execEv+0x141)[0x7ec4cb64af]
|
sql/item.h:1178(Item::val_int_result())[0x7ec4cb82a5]
|
sql/sql_select.cc:18107(do_select(JOIN*, Procedure*))[0x7ec48c03a2]
|
sql/item.cc:9923(Item_cache_str::cache_value())[0x7ec4c223c0]
|
sql/item.cc:8276(Item_cache_wrapper::cache())[0x7ec4c2aa9c]
|
sql/item.cc:8384(Item_cache_wrapper::val_str(String*))[0x7ec4c1d22e]
|
sql/item.cc:6889(Item::send(Protocol*, String*))[0x7ec4c18e09]
|
sql/item.cc:8182(Item_cache_wrapper::send(Protocol*, String*))[0x7ec4c1c99e]
|
sql/protocol.cc:979(Protocol::send_result_set_row(List<Item>*))[0x7ec48bc1db]
|
sql/sql_class.cc:2774(select_send::send_data(List<Item>&))[0x7ec493ddb6]
|
sql/sql_select.cc:19726(end_send(JOIN*, st_join_table*, bool))[0x7ec49f5cfa]
|
sql/sql_select.cc:18064(do_select(JOIN*, Procedure*))[0x7ec49f2447]
|
sql/sql_select.cc:3483(JOIN::exec_inner())[0x7ec49cd2ad]
|
sql/sql_select.cc:3279(JOIN::exec())[0x7ec49cc74e]
|
sql/sql_select.cc:3680(mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x7ec49cd91e]
|
sql/sql_select.cc:373(handle_select(THD*, LEX*, select_result*, unsigned long))[0x7ec49c21c9]
|
sql/sql_parse.cc:6446(execute_sqlcom_select(THD*, TABLE_LIST*))[0x7ec498e1b0]
|
sql/sql_parse.cc:3460(mysql_execute_command(THD*))[0x7ec4983eb1]
|
sql/sql_parse.cc:7886(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x7ec4991b29]
|
sql/sql_parse.cc:1814(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x7ec497f84b]
|
sql/sql_parse.cc:1360(do_command(THD*))[0x7ec497e1be]
|
sql/sql_connect.cc:1354(do_handle_one_connection(CONNECT*))[0x7ec4acab66]
|
sql/sql_connect.cc:1261(handle_one_connection)[0x7ec4aca8e6]
|
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7fa0cba5a6ba]
|
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7fa0caeef3dd]
|
|
Trying to get some variables.
|
Some pointers may be invalid and cause the dump to abort.
|
Query (0x7fa05801e440): select ( select login_name from profiles where userId = ( select userid from maintainer where product_id = bugs.product_id union select userid from maintainer where product_id = ( select id from products where name = (select name from product_groups where id = bugs.product_id)) limit 1 ) ) as aa from bugs where (bugs.bug_id=45199)
|
Connection ID (thread ID): 8
|
Status: NOT_KILLED
|
on mysql 5.7.18, 5.6.23:
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| (
|
select login_name from profiles where userId = (
|
select userid from maintainer where product_id = bugs.product_id
|
union
|
select userid from maintainer where product_id = (
|
select id from products where name = (select name from product_ |
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| foo |
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0,18 sec)
|
|
|
+----+--------------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+--------------------------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+--------------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+--------------------------------+
|
| 1 | PRIMARY | bugs | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
|
| 2 | DEPENDENT SUBQUERY | profiles | NULL | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 100.00 | Using where |
|
| 3 | DEPENDENT SUBQUERY | maintainer | NULL | ref | PRIMARY | PRIMARY | 4 | const | 4 | 100.00 | Using index |
|
| 4 | DEPENDENT UNION | maintainer | NULL | ref | PRIMARY | PRIMARY | 4 | func | 4 | 100.00 | Using where; Using index |
|
| 5 | DEPENDENT SUBQUERY | products | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
|
| 6 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |
|
| NULL | UNION RESULT | <union3,4> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
|
+----+--------------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+--------------------------------+
|
7 rows in set, 4 warnings (0,05 sec)
|
|
Warning (Code 1681): 'EXTENDED' is deprecated and will be removed in a future release.
|
Note (Code 1276): Field or reference 'test.bugs.product_id' of SELECT #3 was resolved in SELECT #1
|
Note (Code 1276): Field or reference 'test.bugs.product_id' of SELECT #6 was resolved in SELECT #1
|
Note (Code 1003): /* select#1 */ select (/* select#2 */ select `test`.`profiles`.`login_name` from `test`.`profiles` where (`test`.`profiles`.`userid` = (/* select#3 */ select `test`.`maintainer`.`userid` from `test`.`maintainer` where (`test`.`maintainer`.`product_id` = '1184') union /* select#4 */ select `test`.`maintainer`.`userid` from `test`.`maintainer` where (`test`.`maintainer`.`product_id` = (/* select#5 */ select `test`.`products`.`id` from `test`.`products` where (`test`.`products`.`name` = (/* select#6 */ select NULL from `test`.`product_groups` where multiple equal('1184', NULL))))) limit 1))) AS `(
|
select login_name from profiles where userId = (
|
select userid from maintainer where product_id = bugs.product_id
|
union
|
select userid from maintainer where product_id = (
|
select id from products where name = (select name from product_` from `test`.`bugs` where 1
|
|
killer_query.sql