Description:
When executing a query of the type
SELECT *
FROM table
WHERE (field1, field2) IN (SELECT ...)
mysqld crashes (segmentation fault), with the following stacktrace:
---- called from signal handler with signal 11 (SIGSEGV) ------
[8] Field::is_null(this = 0x4587850, row_offset = 0), line 272 in "field.h"
[9] Item_field::val_int_result(this = 0x457b8d0), line 2231 in "item.cc"
[10] Item_ref::val_int(this = 0x4591940), line 6656 in "item.cc"
[11] Arg_comparator::compare_int_signed(this = 0x45917a0), line 1474 in "item_cmpfunc.cc"
[12] Arg_comparator::compare(this = 0x45917a0), line 88 in "item_cmpfunc.h"
[13] Item_func_eq::val_int(this = 0x45916f0), line 2044 in "item_cmpfunc.cc"
[14] Item::val_bool(this = 0x45916f0), line 200 in "item.cc"
[15] Item_cond_and::val_int(this = 0x4592790), line 4641 in "item_cmpfunc.cc"
[16] end_send(join = 0x4590d40, join_tab = 0x45947a0, end_of_records = false), line 18403 in "sql_select.cc"
[17] evaluate_join_record(join = 0x4590d40, join_tab = 0x45944f0, error = 0), line 17628 in "sql_select.cc"
[18] sub_select(join = 0x4590d40, join_tab = 0x45944f0, end_of_records = false), line 17337 in "sql_select.cc"
[19] do_select(join = 0x4590d40, fields = 0x457b0c8, table = (nil), procedure = (nil)), line 16868 in "sql_select.cc"
[20] JOIN::exec(this = 0x4590d40), line 3311 in "sql_select.cc"
[21] subselect_single_select_engine::exec(this = 0x458f138), line 2392 in "item_subselect.cc"
[22] Item_subselect::exec(this = 0x458eff8), line 311 in "item_subselect.cc"
[23] Item_in_subselect::exec(this = 0x458eff8), line 427 in "item_subselect.cc"
[24] Item_in_subselect::val_bool(this = 0x458eff8), line 1023 in "item_subselect.cc"
[25] Item::val_bool_result(this = 0x458eff8), line 868 in "item.h"
[26] Item_in_optimizer::val_int(this = 0x4591438), line 1947 in "item_cmpfunc.cc"
[27] evaluate_join_record(join = 0x458f230, join_tab = 0x4593348, error = 0), line 17505 in "sql_select.cc"
[28] sub_select(join = 0x458f230, join_tab = 0x4593348, end_of_records = false), line 17337 in "sql_select.cc"
[29] do_select(join = 0x458f230, fields = 0x45325d0, table = (nil), procedure = (nil)), line 16868 in "sql_select.cc"
[30] JOIN::exec(this = 0x458f230), line 3311 in "sql_select.cc"
[31] mysql_select(thd = 0x4530290, rref_pointer_array = 0x45326b0, tables = 0x457a7b0, wild_num = 1U, fields = CLASS, conds = 0x458eff8, og_num = 0, order = (nil), group = (nil), having = (nil), proc_param = (nil), select_options = 2147748608ULL, result = 0x458f210, unit = 0x4531e90, select_lex = 0x45324c8), line 3528 in "sql_select.cc"
[32] handle_select(thd = 0x4530290, lex = 0x4531de0, result = 0x458f210, setup_tables_done_option = 0), line 311 in "sql_select.cc"
[33] execute_sqlcom_select(thd = 0x4530290, all_tables = 0x457a7b0), line 4493 in "sql_parse.cc"
[34] mysql_execute_command(thd = 0x4530290), line 2089 in "sql_parse.cc"
[35] mysql_parse(thd = 0x4530290, rawbuf = 0x457a290 "SELECT * \nFROM transforms.where_subselect_15682 \nWHERE (field1, field2) IN ( \nSELECT DISTINCT `pk` AS field1 , `pk` AS field2 \nFROM CC AS alias1 \nWHERE ( \n( alias1 . `col_int_key` > 229 \nAND alias1 . `col_int_key` < ( 229 + 214 ) \nOR ( alias1 . `col_int_key` > 229 \nAND alias1 . `col_int_key` < ( 229 + 189 ) \nOR alias1 . `col_varchar_key` > 'y' \n) \n) OR alias1 . `col_varchar_key` IS NOT NULL )\nGROUP BY field1, field2 )", length = 422U, parser_state = 0xfffffd7ffea8fa88), line 5537 in "sql_parse.cc"
[36] dispatch_command(command = COM_QUERY, thd = 0x4530290, packet = 0x4572241 "", packet_length = 423U), line 1075 in "sql_parse.cc"
[37] do_command(thd = 0x4530290), line 815 in "sql_parse.cc"
[38] do_handle_one_connection(thd_arg = 0x4530290), line 745 in "sql_connect.cc"
[39] handle_one_connection(arg = 0x4530290), line 684 in "sql_connect.cc"
[40] pfs_spawn_thread(arg = 0x4525880), line 1078 in "pfs.cc"
A somewhat expanded test case (ENGINE=InnoDB, more rows, more columns, more AND/OR predicated in WHERE clause) results in a crash in:
[8] get_addon_fields(max_length_for_sort_data = 1024U, ptabfield = 0x458a6b0, sortlength = 8U, plength = 0xfffffd7ffea8af84), line 1750 in "filesort.cc"
[9] Sort_param::init_for_filesort(this = 0xfffffd7ffea8af78, sortlen = 8U, table = 0x45c1510, max_length_for_sort_data = 1024U, maxrows = 1ULL, sort_posit
ions = false), line 93 in "filesort.cc"
[10] filesort(thd = 0x4530ab0, table = 0x45c1510, sortorder = 0x45992c8, s_length = 2U, select = 0x45988e0, max_rows = 1ULL, sort_positions = false, exami
ned_rows = 0xfffffd7ffea8b3d0, found_rows = 0xfffffd7ffea8b3c8), line 192 in "filesort.cc"
[11] create_sort_index(thd = 0x4530ab0, join = 0x45b6270, order = 0x45b5560, filesort_limit = 1ULL, select_limit = 1ULL, is_order_by = true), line 20182 i
n "sql_select.cc"
[12] JOIN::exec(this = 0x45b6270), line 3276 in "sql_select.cc"
[13] subselect_single_select_engine::exec(this = 0x45b5810), line 2392 in "item_subselect.cc"
(...)
The latter may have the same root cause as the former, as it triggers on the same type of query.
This issue is observed against the implementation of WL#1393 (Optimizing filesort with small limit):
revision-id: tor.didriksen@oracle.com-20101206094649-dwt7fkm6m0esgyzv
date: 2010-12-06 10:46:49 +0100
revno: 3254
branch-nick: next-mr-opt-team-wl1393-merge
The issue seems to be isolated to the WL#1393 implementation - mysql-next-mr-opt-team and mysql-trunk-bugfixing branches are not affected.
How to repeat:
CREATE TABLE t1 (
pk INT NOT NULL AUTO_INCREMENT,
col_int_key INT DEFAULT NULL,
col_varchar_key VARCHAR(1) DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_varchar_key (col_varchar_key,col_int_key)
);
INSERT INTO t1 VALUES (27,7,'x');
INSERT INTO t1 VALUES (28,6,'m');
INSERT INTO t1 VALUES (29,4,'c');
CREATE TABLE where_subselect
SELECT DISTINCT `pk` AS field1 , `pk` AS field2
FROM t1 AS alias1
WHERE alias1 . `col_int_key` > 229
OR alias1 . `col_varchar_key` IS NOT NULL
GROUP BY field1, field2
;
SELECT *
FROM where_subselect
WHERE (field1, field2) IN (
SELECT DISTINCT `pk` AS field1 , `pk` AS field2
FROM t1 AS alias1
WHERE alias1 . `col_int_key` > 229
OR alias1 . `col_varchar_key` IS NOT NULL
GROUP BY field1, field2
);
