[MDEV-13068] Crash in Item::split_sum_func2 with INSERT SELECT using window functions Created: 2017-06-13  Updated: 2020-08-25  Resolved: 2017-06-20

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - Window functions
Affects Version/s: 10.2.4, 10.2.6, 10.2
Fix Version/s: 10.2.7

Type: Bug Priority: Blocker
Reporter: Nilnandan Joshi Assignee: Vicențiu Ciorbaru
Resolution: Fixed Votes: 1
Labels: None


 Description    

2017-06-13 12:26:54 140396794492704 [Note] /home/nil/10.2.6/bin/mysqld: ready for connections.
 
Version: '10.2.6-MariaDB'  socket: '/tmp/mysql_sandbox10206.sock'  port: 10206  MariaDB Server
 
170613 12:53:22 [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 https://mariadb.com/kb/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.2.6-MariaDB
 
key_buffer_size=134217728
 
read_buffer_size=131072
 
max_used_connections=1
 
max_threads=153
 
thread_count=7
 
It is possible that mysqld could use up to
 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467201 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
 
Thread pointer: 0x7fb080000a88
 
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 = 0x7fb09b2d0e48 thread_stack 0x49000
 
/home/nil/10.2.6/bin/mysqld(my_print_stacktrace+0x2e)[0xddc65e]
 
/home/nil/10.2.6/bin/mysqld(handle_fatal_signal+0x444)[0x7d3374]
 
/lib64/libpthread.so.0[0x385520f7e0]
 
/home/nil/10.2.6/bin/mysqld(_ZN4Item15split_sum_func2EP3THD20Bounds_checked_arrayIPS_ER4ListIS_EPS3_j+0xe5)[0x7f8ec5]
 
/home/nil/10.2.6/bin/mysqld(_ZN9Item_func14split_sum_funcEP3THD20Bounds_checked_arrayIP4ItemER4ListIS3_Ej+0x61)[0x8561f1]
 
/home/nil/10.2.6/bin/mysqld(_Z12setup_fieldsP3THD20Bounds_checked_arrayIP4ItemER4ListIS2_E17enum_mark_columnsPS6_b+0x139)[0x5931f9]
 
/home/nil/10.2.6/bin/mysqld(_ZN13select_insert7prepareER4ListI4ItemEP18st_select_lex_unit+0x81)[0x5cade1]
 
/home/nil/10.2.6/bin/mysqld(_ZN4JOIN7prepareEP10TABLE_LISTjP4ItemjP8st_orderbS5_S3_S5_P13st_select_lexP18st_select_lex_unit+0x137e)[0x638b5e]
 
/home/nil/10.2.6/bin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x9c2)[0x648752]
 
/home/nil/10.2.6/bin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x244)[0x649b24]
 
/home/nil/10.2.6/bin/mysqld(_Z21mysql_execute_commandP3THD+0x98f0)[0x5f0010]
 
/home/nil/10.2.6/bin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x3a0)[0x5f0e60]
 
/home/nil/10.2.6/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x23ca)[0x5f38ba]
 
/home/nil/10.2.6/bin/mysqld(_Z10do_commandP3THD+0x16d)[0x5f40fd]
 
/home/nil/10.2.6/bin/mysqld(_Z24do_handle_one_connectionP7CONNECT+0x235)[0x6d39a5]
 
/home/nil/10.2.6/bin/mysqld(handle_one_connection+0x3f)[0x6d3b6f]
 
/lib64/libpthread.so.0[0x3855207aa1]
 
/lib64/libc.so.6(clone+0x6d)[0x3854ee8aad]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7fb08000efe0): INSERT INTO T1 SELECT (SELECT a.C3 FROM T2 a WHERE UPPER(a.C2) = 'T1') + (row_number() over()), 696005, C3, C4, C5, C6, C7, 'batch_user', CURRENT_TIMESTAMP, C10,C11 FROM T1 WHERE  C2=672009
 
Connection ID (thread ID): 8
 
Status: NOT_KILLED
 
 
 
Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on
 
 
 
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
 
information that should help you find out what is causing the crash.
 
170613 12:53:24 mysqld_safe Number of processes running now: 0
 
170613 12:53:24 mysqld_safe mysqld restarted

How to repeat : 

MariaDB [test]> CREATE TABLE `T1` (
 
    ->   `C1` int(11) NOT NULL,
 
    ->   `C2` int(11) NOT NULL,
 
    ->   `C3` int(11) NOT NULL,
 
    ->   `C4` int(11) NOT NULL,
 
    ->   `C5` int(11) DEFAULT NULL,
 
    ->   `C6` int(11) DEFAULT NULL,
 
    ->   `C7` char(1) COLLATE utf8_bin NOT NULL DEFAULT '1',
 
    ->   `C8` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT 'SYSTEM',
 
    ->   `C9` timestamp NOT NULL DEFAULT current_timestamp(),
 
    ->   `C10` char(3) COLLATE utf8_bin DEFAULT NULL,
 
    ->   `C11` smallint(6) NOT NULL DEFAULT 1,
 
    ->   PRIMARY KEY (`C1`),
 
    ->   UNIQUE KEY `T1_UI1` (`C3`,`C2`,`C4`,`C5`),
 
    ->   KEY `T1_IDX5` (`C3`,`C2`,`C4`),
 
    ->   KEY `T1_IDX7` (`C2`,`C4`,`C3`),
 
    ->   KEY `T1_FK04` (`C4`),
 
    ->   CONSTRAINT `T1_CK11` CHECK (`C11` >= 0),
 
    ->   CONSTRAINT `T1_CK7` CHECK (`C7` in ('0','1'))
 
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC
 
    -> ;
 
Query OK, 0 rows affected (0.04 sec)
 
 
 
MariaDB [test]> CREATE TABLE `T2` (
 
    ->   `C1` int(11) NOT NULL,
 
    ->   `C2` varchar(32) COLLATE utf8_bin NOT NULL,
 
    ->   `C3` bigint(20) NOT NULL,
 
    ->   `SEQNUMRANGE` bigint(20) NOT NULL,
 
    ->   PRIMARY KEY (`C1`),
 
    ->   UNIQUE KEY `T2_UI1` (`C2`)
 
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC
 
    -> ;
 
Query OK, 0 rows affected (0.02 sec)
 
 
 
MariaDB [test]> INSERT INTO T1 SELECT (SELECT a.C3 FROM T2 a WHERE UPPER(a.C2) = 'T1') + (row_number() over()), 696005, C3, C4, C5, C6, C7, 'batch_user', CURRENT_TIMESTAMP, C10,C11 FROM T1 WHERE  C2=672009;
 
ERROR 2013 (HY000): Lost connection to MySQL server during query
 
MariaDB [test]>



 Comments   
Comment by Elena Stepanova [ 2017-06-13 ]

Here is a smaller test case, but please check the original one too after fixing:

CREATE TABLE t1 (c1 INT);
 
# This insert is optional, fails on an empty table too
 
INSERT INTO t1 VALUES (1),(2);
 
 
 
INSERT INTO t1 SELECT 1 + row_number() over() FROM t1;

10.2 7a12894de11ab

 
#3  <signal handler called>
 
#4  0x000056386b4c76a2 in Item::split_sum_func2 (this=0x7fbf5c013c10, thd=0x7fbf5c000b00, ref_pointer_array=..., fields=..., ref=0x7fbf5c012e50, split_flags=3) at /data/src/10.2/sql/item.cc:1966
 
#5  0x000056386b5258a4 in Item_func::split_sum_func (this=0x7fbf5c012db8, thd=0x7fbf5c000b00, ref_pointer_array=..., fields=..., flags=2) at /data/src/10.2/sql/item_func.cc:442
 
#6  0x000056386b1dbea7 in setup_fields (thd=0x7fbf5c000b00, ref_pointer_array=..., fields=..., mark_used_columns=MARK_COLUMNS_READ, sum_func_list=0x0, allow_sum_func=false) at /data/src/10.2/sql/sql_base.cc:7074
 
#7  0x000056386b224388 in select_insert::prepare (this=0x7fbf5c0135c8, values=..., u=0x7fbf5c0045e0) at /data/src/10.2/sql/sql_insert.cc:3528
 
#8  0x000056386b2824d1 in JOIN::prepare (this=0x7fbf5c013670, tables_init=0x7fbf5c012f18, wild_num=0, conds_init=0x0, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7fbf5c004d18, unit_arg=0x7fbf5c0045e0) at /data/src/10.2/sql/sql_select.cc:1021
 
#9  0x000056386b28b647 in mysql_select (thd=0x7fbf5c000b00, tables=0x7fbf5c012f18, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=3489925888, result=0x7fbf5c0135c8, unit=0x7fbf5c0045e0, select_lex=0x7fbf5c004d18) at /data/src/10.2/sql/sql_select.cc:3646
 
#10 0x000056386b280072 in handle_select (thd=0x7fbf5c000b00, lex=0x7fbf5c004518, result=0x7fbf5c0135c8, setup_tables_done_option=1073741824) at /data/src/10.2/sql/sql_select.cc:373
 
#11 0x000056386b2453ac in mysql_execute_command (thd=0x7fbf5c000b00) at /data/src/10.2/sql/sql_parse.cc:4513
 
#12 0x000056386b24fd4c in mysql_parse (thd=0x7fbf5c000b00, rawbuf=0x7fbf5c012368 "INSERT INTO t1 SELECT 1 + row_number() over() FROM t1", length=53, parser_state=0x7fbfa05f2200, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:7870
 
#13 0x000056386b23de24 in dispatch_command (command=COM_QUERY, thd=0x7fbf5c000b00, packet=0x7fbf5c18c751 "INSERT INTO t1 SELECT 1 + row_number() over() FROM t1", packet_length=53, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:1812
 
#14 0x000056386b23c794 in do_command (thd=0x7fbf5c000b00) at /data/src/10.2/sql/sql_parse.cc:1362
 
#15 0x000056386b387a79 in do_handle_one_connection (connect=0x56386f61b700) at /data/src/10.2/sql/sql_connect.cc:1354
 
#16 0x000056386b387806 in handle_one_connection (arg=0x56386f61b700) at /data/src/10.2/sql/sql_connect.cc:1260
 
#17 0x000056386b7a20d8 in pfs_spawn_thread (arg=0x56386f6bdd10) at /data/src/10.2/storage/perfschema/pfs.cc:1862
 
#18 0x00007fbfb10ca494 in start_thread (arg=0x7fbfa05f3700) at pthread_create.c:333
 
#19 0x00007fbfaf24293f in clone () from /lib/x86_64-linux-gnu/libc.so.6

Comment by Alice Sherepa [ 2017-06-13 ]

The same problem for all window functions

CREATE TABLE `t1` (`i` int); 
 
 
INSERT INTO t1 SELECT 1 + (row_number() over());
 
 
 
INSERT INTO t1 
	SELECT 1 + ( DENSE_RANK() over(order by i)) from t1;
 
 
 
INSERT INTO t1 
	SELECT 1 + (rank() over());
 
 
 
INSERT INTO t1 
	SELECT 1 + ( ntile(1) over(order by i)) from t1;
 
 
 
INSERT INTO t1 
	SELECT 1 + ( PERCENT_RANK() over(order by i)) from t1;
 
 
 
INSERT INTO t1 
	SELECT 1 + ( Count(*) over(order by i)) from t1;
 
 
 
INSERT INTO t1 
	SELECT 1 + ( avg(i) over(order by i)) from t1;
 
 
 
INSERT INTO t1 
	SELECT 1 + ( sum(i) over(order by i)) from t1;

Comment by Vicențiu Ciorbaru [ 2017-06-16 ]

Hi Sergey!

Can you please review a fix for this MDEV? The explanation is in the commit message.

http://lists.askmonty.org/pipermail/commits/2017-June/011251.html

Thanks,
Vicențiu

Comment by Sergei Petrunia [ 2017-06-18 ]

Following the discussion on Slack

  • Let's add a comment to this fix noting that the setup_fields call does not seem to be useful
  • And push this fix

Ok to push the the above.

Then, let's try commenting out the setup_fields call in 10.3.

Generated at Thu Feb 08 08:02:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.