MySQL 5.5.35 and MariaDB 10.0.12 crash when ORDER BY 1 when using GROUP_CONCAT(IF
SQL that causes the crash:
INSERT INTO tempdb.tt_markets
SELECT mv.marketId,
NULL,
GROUP_CONCAT(IF (tp.overridesExchangeCode, IFNULL(tp.symbolCode,v.symbolCode), v.symbolCode) ORDER BY 1 SEPARATOR ',' ),
GROUP_CONCAT(v.`permissionsCode` ORDER BY 1 SEPARATOR ',' ),
GROUP_CONCAT(IF (tp.overridesExchangeCode, IFNULL(tp.tradingPlatformName, v.venueAcronym), v.venueAcronym) ORDER BY 1 SEPARATOR ',' )
FROM marketvenues mv
JOIN market mkt ON mv.marketId = mkt.marketId
JOIN markettemplate mt ON mkt.marketTemplateId = mt.marketTemplateId
JOIN marketinstruments mi ON mkt.marketId = mi.marketId
JOIN instrument i ON i.instrumentId = mi.instrumentId
JOIN debtinstrument di ON i.instrumentId = di.instrumentId
JOIN product p ON i.productId = p.productId
JOIN producttype pt ON p.productTypeId = pt.productTypeId
JOIN venue v ON mv.venueId = v.venueId
LEFT JOIN tradingplatform tp ON tp.tradingPlatformId = v.tradingPlatformId
JOIN tempdb.tt_MktVenIds ttmvIds
ON mv.marketVenueId=ttmvIds.ttv_MarketVenueId
WHERE mi.marketSequence = 1 -- Limit the marketinstrument join to one record
AND p.rowStatusId < compareStatus -- Limits it to active or expired products
AND pt.productTypeCode = 'DBT' -- Limits it to debt products
AND i.rowStatusId < compareStatus -- Limits it to active or expired instruments
AND mkt.rowStatusId < compareStatus -- Limits it to active or expired markets
AND mt.marketTemplateCode = "OUT" -- Limits it to outright markets
AND di.maturityDate >= IF (IncludeInActive='Y', 0, CONVERT(CURDATE(),UNSIGNED)) -- Limits it to unexpired/expired instruments
GROUP BY mv.marketId;
Crash is caused by "ORDER BY 1", if I replace it by one of the value of IF it stops crashing. For example:
GROUP_CONCAT(IF (tp.overridesExchangeCode, IFNULL(tp.symbolCode,v.symbolCode), v.symbolCode) ORDER BY v.symbolCode SEPARATOR ',' ),
GROUP_CONCAT(v.`permissionsCode` ORDER BY 1 SEPARATOR ',' ),
GROUP_CONCAT(IF (tp.overridesExchangeCode, IFNULL(tp.tradingPlatformName, v.venueAcronym), v.venueAcronym) ORDER BY v.venueAcronym SEPARATOR ',' )
But that's not equivalent.
'IF' returned value cannot have an alias assigned, e.g: IF(1,2,3) as if_output.
I guess it would crash anyway since following is crashing too:
GROUP_CONCAT(IF (tp.overridesExchangeCode, IFNULL(tp.symbolCode,v.symbolCode), v.symbolCode) ORDER BY IF (tp.overridesExchangeCode, IFNULL(tp.symbolCode,v.symbolCode), v.symbolCode) SEPARATOR ',' )
error log:
140821 18:10:35 [ERROR] mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
To report this bug, see http://kb.askmonty.org/en/reporting-bugs
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.
Server version: 10.0.12-MariaDB-log
key_buffer_size=33554432
read_buffer_size=262144
max_used_connections=33
max_threads=1002
thread_count=25
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 823012 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x0x7f39eac4d008
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
Some pointers may be invalid and cause the dump to abort.
Query (0x7f39de6398d0): INSERT INTO tempdb.tt_markets SELECT mv.marketId, NULL, GROUP_CONCAT(IF (tp.overridesExchangeCode, IFNULL(tp.symbolCode,v.symbolCode), v.symbolCode) ORDER BY 1 SEPARATOR ',' ), GROUP_CONCAT(v.`permissionsCode` ORDER BY 1 SEPARATOR ',' ), GROUP_CONCAT(IF (tp.overridesExchangeCode, IFNULL(tp.tradingPlatformName, v.venueAcronym), v.venueAcronym) ORDER BY 1 SEPARATOR ',' ) FROM marketvenues mv JOIN market mkt ON mv.marketId = mkt.marketId JOIN markettemplate mt ON mkt.marketTemplateId = mt.marketTemplateId JOIN marketinstruments mi ON mkt.marketId = mi.marketId JOIN instrument i ON i.instrumentId = mi.instrumentId JOIN debtinstrument di ON i.instrumentId = di.instrumentId JOIN product p ON i.productId = p.productId JOIN producttype pt ON p.productTypeId = pt.productTypeId JOIN venue v ON mv.venueId = v.venueId LEFT JOIN tradingplatform tp ON tp.tradingPlatformId = v.tradingPlatformId JOIN tempdb.tt_MktVenIds ttmvIds ON mv.marketVenueId=ttmvIds.ttv_MarketVenueId WHERE mi.marketSequence = 1 AND p.rowStatusId < NAME_CONST('compareStatus',2) AND pt.productTypeCode = 'DBT' AND i.rowStatusId < NAME_CONST('compareStatus',2) AND mkt.rowStatusId < NAME_CONST('compareStatus',2) AND mt.marketTemplateCode = "OUT" AND di.maturityDate >= IF ( NAME_CONST('IncludeInActive',_utf8'N' COLLATE 'utf8_general_ci')='Y', 0, CONVERT(CURDATE(),UNSIGNED)) GROUP BY mv.marketId
There are about a dozen tables in the query:
marketvenues
market
markettemplate
marketinstruments
instrument
debtinstrument
product
producttype
venue
tradingplatform
Elena Stepanova
added a comment - Ivan,
There are about a dozen tables in the query:
marketvenues
market
markettemplate
marketinstruments
instrument
debtinstrument
product
producttype
venue
tradingplatform
#8 0x000000000065ee61 in handle_select (thd=0x385d760, lex=0x390ae00, result=0x3912a78, setup_tables_done_option=0) at 5.5/sql/sql_select.cc:319
#9 0x0000000000638413 in execute_sqlcom_select (thd=0x385d760, all_tables=0x390d2c0) at 5.5/sql/sql_parse.cc:4688
#10 0x00000000006315f5 in mysql_execute_command (thd=0x385d760) at 5.5/sql/sql_parse.cc:2233
#11 0x0000000000652465 in Prepared_statement::execute (this=0x3908bf0, expanded_query=0x7fd3859e2cc0, open_cursor=false) at 5.5/sql/sql_prepare.cc:3928
#12 0x000000000065157c in Prepared_statement::execute_loop (this=0x3908bf0, expanded_query=0x7fd3859e2cc0, open_cursor=false, packet=0x0, packet_end=0x0) at 5.5/sql/sql_prepare.cc:3587
#13 0x000000000064f6a0 in mysql_sql_stmt_execute (thd=0x385d760) at 5.5/sql/sql_prepare.cc:2737
#14 0x0000000000631623 in mysql_execute_command (thd=0x385d760) at 5.5/sql/sql_parse.cc:2243
#15 0x000000000063ab22 in mysql_parse (thd=0x385d760, rawbuf=0x38db488 "EXECUTE stmt", length=12, parser_state=0x7fd3859e3650) at 5.5/sql/sql_parse.cc:5799
#16 0x000000000062eb6d in dispatch_command (command=COM_QUERY, thd=0x385d760, packet=0x38d3c71 "EXECUTE stmt", packet_length=12) at 5.5/sql/sql_parse.cc:1079
#17 0x000000000062dcf9 in do_command (thd=0x385d760) at 5.5/sql/sql_parse.cc:793
#18 0x000000000072fd5a in do_handle_one_connection (thd_arg=0x385d760) at 5.5/sql/sql_connect.cc:1266
#19 0x000000000072f819 in handle_one_connection (arg=0x385d760) at 5.5/sql/sql_connect.cc:1181
#20 0x0000000000b64569 in pfs_spawn_thread (arg=0x396afe0) at 5.5/storage/perfschema/pfs.cc:1015
#21 0x00007fd38a010b50 in start_thread (arg=<optimized out>) at pthread_create.c:304
#22 0x00007fd388b6020d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
Also reproducible on all of MySQL 5.1-5.7.
Elena Stepanova
added a comment - - edited Test case:
CREATE TABLE t1 (pk INT , t2_id INT , t5_id INT , PRIMARY KEY (pk));
INSERT INTO t1 VALUES (1,3,12),(2,3,15);
CREATE TABLE t2 (pk INT , PRIMARY KEY (pk));
INSERT INTO t2 VALUES (4),(5);
CREATE TABLE t3 (t2_id INT , t4_id INT );
INSERT INTO t3 VALUES (6,11),(7,12);
CREATE TABLE t4 (id INT );
INSERT INTO t4 VALUES (13),(14);
CREATE TABLE t5 (pk INT , f VARCHAR (50), t6_id INT , PRIMARY KEY (pk));
INSERT INTO t5 VALUES (9, 'FOO' , NULL );
CREATE TABLE t6 (pk INT , f VARCHAR (120), b TINYINT(4), PRIMARY KEY (pk));
PREPARE stmt FROM "
SELECT t1.t2_id, GROUP_CONCAT(IF (t6.b, t6.f, t5.f) ORDER BY 1)
FROM t1
JOIN t2 ON t1.t2_id = t2.pk
JOIN t3 ON t2.pk = t3.t2_id
JOIN t4 ON t4.id = t3.t4_id
JOIN t5 ON t1.t5_id = t5.pk
LEFT JOIN t6 ON t6.pk = t5.t6_id
GROUP BY t1.t2_id
" ;
EXECUTE stmt;
EXECUTE stmt;
EXECUTE stmt;
Stack trace from 5.5 revno 4292:
#3 <signal handler called>
#4 0x000000000089025c in Item_func_group_concat::fix_fields (this=0x390ce88, thd=0x385d760, ref=0x390ca68) at 5.5/sql/item_sum.cc:3417
#5 0x00000000005de668 in setup_fields (thd=0x385d760, ref_pointer_array=0x3912a98, fields=..., mark_used_columns=MARK_COLUMNS_READ, sum_func_list=0x38dbc10, allow_sum_func=true) at 5.5/sql/sql_base.cc:8166
#6 0x000000000065fb90 in JOIN::prepare (this=0x38db8e0, rref_pointer_array=0x390b7f8, tables_init=0x390d2c0, wild_num=0, conds_init=0x38db6a8, og_num=1, order_init=0x0, skip_order_by=false, group_init=0x3912a38, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x390b588, unit_arg=0x390aeb0) at 5.5/sql/sql_select.cc:723
#7 0x00000000006682df in mysql_select (thd=0x385d760, rref_pointer_array=0x390b7f8, tables=0x390d2c0, wild_num=0, fields=..., conds=0x38db6a8, og_num=1, order=0x0, group=0x3912a38, having=0x0, proc_param=0x0, select_options=2416184064, result=0x3912a78, unit=0x390aeb0, select_lex=0x390b588) at 5.5/sql/sql_select.cc:3074
#8 0x000000000065ee61 in handle_select (thd=0x385d760, lex=0x390ae00, result=0x3912a78, setup_tables_done_option=0) at 5.5/sql/sql_select.cc:319
#9 0x0000000000638413 in execute_sqlcom_select (thd=0x385d760, all_tables=0x390d2c0) at 5.5/sql/sql_parse.cc:4688
#10 0x00000000006315f5 in mysql_execute_command (thd=0x385d760) at 5.5/sql/sql_parse.cc:2233
#11 0x0000000000652465 in Prepared_statement::execute (this=0x3908bf0, expanded_query=0x7fd3859e2cc0, open_cursor=false) at 5.5/sql/sql_prepare.cc:3928
#12 0x000000000065157c in Prepared_statement::execute_loop (this=0x3908bf0, expanded_query=0x7fd3859e2cc0, open_cursor=false, packet=0x0, packet_end=0x0) at 5.5/sql/sql_prepare.cc:3587
#13 0x000000000064f6a0 in mysql_sql_stmt_execute (thd=0x385d760) at 5.5/sql/sql_prepare.cc:2737
#14 0x0000000000631623 in mysql_execute_command (thd=0x385d760) at 5.5/sql/sql_parse.cc:2243
#15 0x000000000063ab22 in mysql_parse (thd=0x385d760, rawbuf=0x38db488 "EXECUTE stmt", length=12, parser_state=0x7fd3859e3650) at 5.5/sql/sql_parse.cc:5799
#16 0x000000000062eb6d in dispatch_command (command=COM_QUERY, thd=0x385d760, packet=0x38d3c71 "EXECUTE stmt", packet_length=12) at 5.5/sql/sql_parse.cc:1079
#17 0x000000000062dcf9 in do_command (thd=0x385d760) at 5.5/sql/sql_parse.cc:793
#18 0x000000000072fd5a in do_handle_one_connection (thd_arg=0x385d760) at 5.5/sql/sql_connect.cc:1266
#19 0x000000000072f819 in handle_one_connection (arg=0x385d760) at 5.5/sql/sql_connect.cc:1181
#20 0x0000000000b64569 in pfs_spawn_thread (arg=0x396afe0) at 5.5/storage/perfschema/pfs.cc:1015
#21 0x00007fd38a010b50 in start_thread (arg=<optimized out>) at pthread_create.c:304
#22 0x00007fd388b6020d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
Also reproducible on all of MySQL 5.1-5.7.
Ivan,
There are about a dozen tables in the query:
marketvenues
market
markettemplate
marketinstruments
instrument
debtinstrument
product
producttype
venue
tradingplatform