Bug #94811 To run “select LAST_VALUE()”, MySql Server would be always core down
Submitted: 28 Mar 2019 10:09 Modified: 9 Apr 2019 20:57
Reporter: juncai meng Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S6 (Debug Builds)
Version:8.0.12, 8.0.15 OS:CentOS
Assigned to: CPU Architecture:Other

[28 Mar 2019 10:09] juncai meng
Description:
Exec sql: select LAST_VALUE(val) OVER (ORDER BY val DESC RANGE BETWEEN 4 preceding AND CURRENT ROW) AS 'last', mysql server core down, core info as follow:

(gdb) bt
#0  0x00007f8b565659b1 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000003da2d92 in my_write_core (sig=6) at /data/mysql-8.0.12/mysys/stacktrace.cc:278
#2  0x0000000002c12d1f in handle_fatal_signal (sig=6) at /data/mysql-8.0.12/sql/signal_handler.cc:249
#3  <signal handler called>
#4  0x00007f8b549ea1f7 in raise () from /lib64/libc.so.6
#5  0x00007f8b549eb8e8 in abort () from /lib64/libc.so.6
#6  0x00007f8b549e3266 in __assert_fail_base () from /lib64/libc.so.6
#7  0x00007f8b549e3312 in __assert_fail () from /lib64/libc.so.6
#8  0x0000000002fce24a in read_frame_buffer_row (rowno=3, w=0x7f8a90120ae8, for_nth_value=false) at /data/mysql-8.0.12/sql/sql_executor.cc:3735
#9  0x0000000002fce694 in bring_back_frame_row (thd=0x7f8a90000970, w=..., out_param=0x7f8a90b4e210, rowno=3, reason=Window::REA_LAST_IN_FRAME, fno=0)
    at /data/mysql-8.0.12/sql/sql_executor.cc:3843
#10 0x0000000002fd0247 in process_buffered_windowing_record (thd=0x7f8a90000970, param=0x7f8a90b4e210, new_partition_or_eof=true, output_row_ready=0x7f8b40cf296e)
    at /data/mysql-8.0.12/sql/sql_executor.cc:4673
#11 0x0000000002fd12d1 in end_write_wf (join=0x7f8a90122728, qep_tab=0x7f8a90b4d960, end_of_records=true) at /data/mysql-8.0.12/sql/sql_executor.cc:5055
#12 0x0000000002fd4f0d in QEP_tmp_table::put_record (this=0x7f8a90b4e480, end_of_records=true) at /data/mysql-8.0.12/sql/sql_executor.cc:6241
#13 0x0000000002fd4f72 in QEP_tmp_table::end_send (this=0x7f8a90b4e480) at /data/mysql-8.0.12/sql/sql_executor.cc:6259
#14 0x0000000002fc7bc9 in sub_select_op (join=0x7f8a90122728, qep_tab=0x7f8a90b4d960, end_of_records=true) at /data/mysql-8.0.12/sql/sql_executor.cc:1281
#15 0x0000000002fc7d38 in sub_select (join=0x7f8a90122728, qep_tab=0x7f8a90b4d7e8, end_of_records=true) at /data/mysql-8.0.12/sql/sql_executor.cc:1432
#16 0x0000000002fc7846 in do_select (join=0x7f8a90122728) at /data/mysql-8.0.12/sql/sql_executor.cc:1172
#17 0x0000000002fc4c08 in JOIN::exec (this=0x7f8a90122728) at /data/mysql-8.0.12/sql/sql_executor.cc:294
#18 0x0000000002ada013 in Sql_cmd_dml::execute_inner (this=0x7f8a90121970, thd=0x7f8a90000970) at /data/mysql-8.0.12/sql/sql_select.cc:651
#19 0x0000000002ad9ac6 in Sql_cmd_dml::execute (this=0x7f8a90121970, thd=0x7f8a90000970) at /data/mysql-8.0.12/sql/sql_select.cc:554
#20 0x0000000002a82536 in mysql_execute_command (thd=0x7f8a90000970, first_level=true) at /data/mysql-8.0.12/sql/sql_parse.cc:4210
#21 0x0000000002a84742 in mysql_parse (thd=0x7f8a90000970, parser_state=0x7f8b40cf4370) at /data/mysql-8.0.12/sql/sql_parse.cc:4925
#22 0x0000000002a7ad4e in dispatch_command (thd=0x7f8a90000970, com_data=0x7f8b40cf4cc0, command=COM_QUERY) at /data/mysql-8.0.12/sql/sql_parse.cc:1607
#23 0x0000000002a796c4 in do_command (thd=0x7f8a90000970) at /data/mysql-8.0.12/sql/sql_parse.cc:1232
#24 0x0000000002c011fc in handle_connection (arg=0x6975d50) at /data/mysql-8.0.12/sql/conn_handler/connection_handler_per_thread.cc:308
#25 0x0000000003ef9d1e in pfs_spawn_thread (arg=0x6b7c5a0) at /data/mysql-8.0.12/storage/perfschema/pfs.cc:2836
#26 0x00007f8b56560e25 in start_thread () from /lib64/libpthread.so.0
#27 0x00007f8b54aad34d in clone () from /lib64/libc.so.6

How to repeat:
CREATE TABLE window_func_1(val int,time TIME,subject varchar(50));
insert into window_func_1 values(1,'07:00:00','xs');
insert into window_func_1 values(2,'08:00:00','js');
insert into window_func_1 values(3,'07:15:00','js');
insert into window_func_1 values(4,'06:45:00','js');

select LAST_VALUE(val) OVER (ORDER BY val DESC RANGE BETWEEN 4 preceding AND CURRENT ROW) AS 'last' FROM window_func_1;

That error is always triggered.
[28 Mar 2019 10:50] juncai meng
Make debug mysql server
[28 Mar 2019 11:00] MySQL Verification Team
Hello juncai meng,

Thank you for the report and test case.
Observed that 8.0.15 debug build is affected.

thanks,
Umesh
[9 Apr 2019 20:55] Dag Wanvik
Fixed in 8.0.16.