[MDEV-12851] Window functions with is null function in query crashes server Created: 2017-05-19  Updated: 2017-08-30  Resolved: 2017-06-28

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - Window functions
Affects Version/s: 10.2.5
Fix Version/s: 10.2.7

Type: Bug Priority: Critical
Reporter: David Thompson (Inactive) Assignee: Vicențiu Ciorbaru
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-12840 view with ifnull around window functi... Closed
relates to MDEV-13552 Bug: Server Shutsdown when using LEAD... Closed
relates to MDEV-13683 crash in Item_window_func::update_use... Closed
Sprint: 10.2.7-1

 Description   

The following window function expression crashes the server. The original goal is to have it run in a view but the select alone fails. This is related to MDEV-12840 as that bug was my attempt to rewrite the query in a more sane fashion but the following is technically valid and it may be broader scope.

create table t1(dt datetime);
 
insert into t1 values ('2017-05-17'), ('2017-05-18');
 
 
 
select dt,
 
    -> case when (max(dt) over (order by dt rows between 1 following and 1 following) is null)
 
    -> then '9999-12-31 12:00:00'
 
    -> else max(dt) over (order by dt rows between 1 following and 1 following)
 
    -> end x
 
    -> from t1;
 
ERROR 2006 (HY000): MySQL server has gone away
 
No connection. Trying to reconnect...
 
Connection id:    8
 
Current database: genesis
 
 
 
ERROR 2013 (HY000): Lost connection to MySQL server during query



 Comments   
Comment by Elena Stepanova [ 2017-05-19 ]

Here is a simpler test case, but please check the original one as well after fixing:

create table t1(i int);
 
insert into t1 values (1),(2);
 
select max(i) over (order by i) is null from t1;

10.2 f9069a3dc0a957191587f3809ca6a444ec22c89f

 
#3  <signal handler called>
 
#4  0x00007fb5cf33205d in Item_field::used_tables (this=0x7fb5ac012670) at /data/src/10.2/sql/item.cc:2869
 
#5  0x00007fb5cf500d5f in Item_window_func::update_used_tables (this=0x7fb5ac012838) at /data/src/10.2/sql/item_windowfunc.cc:64
 
#6  0x00007fb5cf36a9b2 in Item_func_isnull::update_used_tables (this=0x7fb5ac012918) at /data/src/10.2/sql/item_cmpfunc.h:1807
 
#7  0x00007fb5cf36a8f9 in Item_func_isnull::fix_length_and_dec (this=0x7fb5ac012918) at /data/src/10.2/sql/item_cmpfunc.h:1776
 
#8  0x00007fb5cf38cf59 in Item_func::fix_fields (this=0x7fb5ac012918, thd=0x7fb5ac000b00, ref=0x7fb5ac0129e0) at /data/src/10.2/sql/item_func.cc:236
 
#9  0x00007fb5cf044946 in setup_fields (thd=0x7fb5ac000b00, ref_pointer_array=..., fields=..., mark_used_columns=MARK_COLUMNS_READ, sum_func_list=0x7fb5ac0134b0, allow_sum_func=true) at /data/src/10.2/sql/sql_base.cc:7053
 
#10 0x00007fb5cf0e9f2c in JOIN::prepare (this=0x7fb5ac013198, tables_init=0x7fb5ac012a88, 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=0x7fb5ac004d18, unit_arg=0x7fb5ac0045e0) at /data/src/10.2/sql/sql_select.cc:806
 
#11 0x00007fb5cf0f41b3 in mysql_select (thd=0x7fb5ac000b00, tables=0x7fb5ac012a88, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fb5ac013178, unit=0x7fb5ac0045e0, select_lex=0x7fb5ac004d18) at /data/src/10.2/sql/sql_select.cc:3644
 
#12 0x00007fb5cf0e8c12 in handle_select (thd=0x7fb5ac000b00, lex=0x7fb5ac004518, result=0x7fb5ac013178, setup_tables_done_option=0) at /data/src/10.2/sql/sql_select.cc:373
 
#13 0x00007fb5cf0b4f1d in execute_sqlcom_select (thd=0x7fb5ac000b00, all_tables=0x7fb5ac012a88) at /data/src/10.2/sql/sql_parse.cc:6431
 
#14 0x00007fb5cf0aaf5c in mysql_execute_command (thd=0x7fb5ac000b00) at /data/src/10.2/sql/sql_parse.cc:3448
 
#15 0x00007fb5cf0b88f0 in mysql_parse (thd=0x7fb5ac000b00, rawbuf=0x7fb5ac012348 "select max(i) over (order by i) is null from t1", length=47, parser_state=0x7fb5c2e27200, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:7874
 
#16 0x00007fb5cf0a69c8 in dispatch_command (command=COM_QUERY, thd=0x7fb5ac000b00, packet=0x7fb5ac0dfd11 "select max(i) over (order by i) is null from t1", packet_length=47, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:1812
 
#17 0x00007fb5cf0a5338 in do_command (thd=0x7fb5ac000b00) at /data/src/10.2/sql/sql_parse.cc:1362
 
#18 0x00007fb5cf1f0085 in do_handle_one_connection (connect=0x7fb5d1842b40) at /data/src/10.2/sql/sql_connect.cc:1354
 
#19 0x00007fb5cf1efe12 in handle_one_connection (arg=0x7fb5d1842b40) at /data/src/10.2/sql/sql_connect.cc:1260
 
#20 0x00007fb5cf609c46 in pfs_spawn_thread (arg=0x7fb5d1788e60) at /data/src/10.2/storage/perfschema/pfs.cc:1862
 
#21 0x00007fb5ce6c6494 in start_thread (arg=0x7fb5c2e28700) at pthread_create.c:333
 
#22 0x00007fb5cca4993f in clone () from /lib/x86_64-linux-gnu/libc.so.6

Comment by David Thompson (Inactive) [ 2017-05-19 ]

A possible workaround is to separate the window function in a subquery and apply the expression in the outer query:

select dt, 
ifnull(dtnext, '9999-12-31 12:00:00') dtnext 
from ( 
  select dt, 
  lead(dt) over (order by dt) dtnext from t1
 
) t;
 
+---------------------+---------------------+
 
| dt                  | dtnext              |
 
+---------------------+---------------------+
 
| 2017-05-17 00:00:00 | 2017-05-18 00:00:00 |
 
| 2017-05-18 00:00:00 | 9999-12-31 12:00:00 |
 
+---------------------+---------------------+

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