Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.2.5
-
None
-
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
|
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
Activity
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 |
|
+---------------------+---------------------+
|
Here is a simpler test case, but please check the original one as well after fixing:
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