[MDEV-14791] Crash with order by expression containing window functions - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.2.24, 10.3.15, 10.4.5
Component/s: Optimizer - Window functions
Labels:
None

Description

CREATE TABLE t1 (b1 BIT, b2 BIT, b3 BIT, b4 BIT , b5 BIT, b6 BIT);

INSERT INTO t1 VALUES (1,0,0,1,0,1),(0,1,0,0,1,0);

SELECT b1

from t1

order by row_number() over (ORDER BY b6) + 1;

# Cleanup

DROP TABLE t1;

#0  0x0000555555a5124a in Field::set_notnull (this=0x0, row_offset=0)

    at /home/vicentiu/Workspace/MariaDB/sql/field.h:1137

#1  0x0000555555d9cfb5 in Item::save_in_field (this=0x7fffd4013348, field=0x0, no_conversions=true)

    at /home/vicentiu/Workspace/MariaDB/sql/item.cc:6441

#2  0x0000555555ccba48 in save_window_function_values (window_functions=..., tbl=0x7fffd402d168,

    rowid_buf=0x7fffd4006c50 "P7\003\324\377\177") at /home/vicentiu/Workspace/MariaDB/sql/sql_window.cc:2517

#3  0x0000555555ccbe7f in compute_window_func (thd=0x7fffd4000d60, window_functions=..., cursor_managers=...,

    tbl=0x7fffd402d168, filesort_result=0x7fffd404d0e0) at /home/vicentiu/Workspace/MariaDB/sql/sql_window.cc:2636

#4  0x0000555555ccc238 in Window_func_runner::exec (this=0x7fffd4015be0, thd=0x7fffd4000d60, tbl=0x7fffd402d168,

    filesort_result=0x7fffd404d0e0) at /home/vicentiu/Workspace/MariaDB/sql/sql_window.cc:2742

#5  0x0000555555ccc351 in Window_funcs_sort::exec (this=0x7fffd4015bd8, join=0x7fffd40136d0)

    at /home/vicentiu/Workspace/MariaDB/sql/sql_window.cc:2767

#6  0x0000555555ccc817 in Window_funcs_computation::exec (this=0x7fffd4015bb8, join=0x7fffd40136d0)

    at /home/vicentiu/Workspace/MariaDB/sql/sql_window.cc:2886

#7  0x0000555555b8c8e0 in AGGR_OP::end_send (this=0x7fffd4015860)

    at /home/vicentiu/Workspace/MariaDB/sql/sql_select.cc:26530

#8  0x0000555555b77b65 in sub_select_postjoin_aggr (join=0x7fffd40136d0, join_tab=0x7fffd4014b38, end_of_records=true)

    at /home/vicentiu/Workspace/MariaDB/sql/sql_select.cc:18369

#9  0x0000555555b77e94 in sub_select (join=0x7fffd40136d0, join_tab=0x7fffd4014788, end_of_records=true)

    at /home/vicentiu/Workspace/MariaDB/sql/sql_select.cc:18605

#10 0x0000555555b7769d in do_select (join=0x7fffd40136d0, procedure=0x0)

    at /home/vicentiu/Workspace/MariaDB/sql/sql_select.cc:18200

#11 0x0000555555b5226a in JOIN::exec_inner (this=0x7fffd40136d0)

    at /home/vicentiu/Workspace/MariaDB/sql/sql_select.cc:3530

#12 0x0000555555b51718 in JOIN::exec (this=0x7fffd40136d0) at /home/vicentiu/Workspace/MariaDB/sql/sql_select.cc:3325

#13 0x0000555555b528dc in mysql_select (thd=0x7fffd4000d60, tables=0x7fffd4012a20, wild_num=0, fields=..., conds=0x0,

    og_num=1, order=0x7fffd4013590, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608,

    result=0x7fffd40136b0, unit=0x7fffd4004900, select_lex=0x7fffd4005038)

    at /home/vicentiu/Workspace/MariaDB/sql/sql_select.cc:3725

#14 0x0000555555b47110 in handle_select (thd=0x7fffd4000d60, lex=0x7fffd4004838, result=0x7fffd40136b0,

    setup_tables_done_option=0) at /home/vicentiu/Workspace/MariaDB/sql/sql_select.cc:361

#15 0x0000555555b13221 in execute_sqlcom_select (thd=0x7fffd4000d60, all_tables=0x7fffd4012a20)

    at /home/vicentiu/Workspace/MariaDB/sql/sql_parse.cc:6459

#16 0x0000555555b08ee9 in mysql_execute_command (thd=0x7fffd4000d60)

    at /home/vicentiu/Workspace/MariaDB/sql/sql_parse.cc:3463

#17 0x0000555555b16bd1 in mysql_parse (thd=0x7fffd4000d60,

    rawbuf=0x7fffd40127e8 "SELECT b1\nfrom t1\norder by row_number() over (ORDER BY b6) + 1", length=62,

    parser_state=0x7fffeb582170, is_com_multi=false, is_next_command=false)

    at /home/vicentiu/Workspace/MariaDB/sql/sql_parse.cc:7900

#18 0x0000555555b04819 in dispatch_command (command=COM_QUERY, thd=0x7fffd4000d60, packet=0x7fffd4008ba1 "",

    packet_length=62, is_com_multi=false, is_next_command=false)

    at /home/vicentiu/Workspace/MariaDB/sql/sql_parse.cc:1804

#19 0x0000555555b031b2 in do_command (thd=0x7fffd4000d60) at /home/vicentiu/Workspace/MariaDB/sql/sql_parse.cc:1359

#20 0x0000555555c4fbd8 in do_handle_one_connection (connect=0x555557bea3f0)

    at /home/vicentiu/Workspace/MariaDB/sql/sql_connect.cc:1335

#21 0x0000555555c4f958 in handle_one_connection (arg=0x555557bea3f0)

    at /home/vicentiu/Workspace/MariaDB/sql/sql_connect.cc:1241

#22 0x000055555648a035 in pfs_spawn_thread (arg=0x555557bf2e60)

    at /home/vicentiu/Workspace/MariaDB/storage/perfschema/pfs.cc:1863

#23 0x00007ffff7bc108a in start_thread () from /usr/lib/libpthread.so.0

#24 0x00007ffff5ac542f in clone () from /usr/lib/libc.so.6

Attachments

Issue Links

duplicates

MDEV-16736 crash on lag() over() and order not working

Closed

MDEV-17242 Wrong result from distinct and arithmetic expression using window function

Closed

is duplicated by

MDEV-15212 server crashed, query using ORDER BY (expression with window function)

Closed

MDEV-16835 Server crash in Item_sum_hybrid::clear()

Closed

MDEV-19072 Wrong results from query, using expression with aggregated function and window function

Closed

MDEV-24191 A bug occurs when an operation is put in the OVER query. Mysqld will be restart

Closed

relates to

MDEV-16835 Server crash in Item_sum_hybrid::clear()

Closed

MDEV-24374 Crash with order by expression containing window functions

Closed

MDEV-25630 Crash with window function in left expr of IN subquery

Closed

MDEV-26416 A SEGV in Field::set_notnull/Item::save_real_in_field

Confirmed

(1 is duplicated by, 4 relates to)

Activity

Ascending order - Click to sort in descending order

Alice Sherepa added a comment - 2018-05-22 11:40

on 10.3 fe3bf136b6 ( Item::save_real_in_field or Item::save_int_in_field) (just to make it searchable)

Thread 1 (Thread 0x7f70ba436700 (LWP 8345)):

#0  __pthread_kill (threadid=<optimized out>, signo=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:62

#1  0x0000562ca354df0f in my_write_core (sig=11) at /home/alice/git/10.3/mysys/stacktrace.c:481

#2  0x0000562ca2d3c951 in handle_fatal_signal (sig=11) at /home/alice/git/10.3/sql/signal_handler.cc:305

#3  <signal handler called>

#4  0x0000562ca29474d6 in Field::set_notnull (this=0x0, row_offset=0) at /home/alice/git/10.3/sql/field.h:1171

#5  0x0000562ca2d69540 in Item::save_real_in_field (this=0x7f706c184480, field=0x0, no_conversions=true) at /home/alice/git/10.3/sql/item.cc:6923

#6  0x0000562ca2c30bc6 in Type_handler_real_result::Item_save_in_field (this=0x562ca428ae30 <type_handler_double>, item=0x7f706c184480, field=0x0, no_conversions=true) at /home/alice/git/10.3/sql/sql_type.cc:2540

#7  0x0000562ca2d69715 in Item::save_in_field (this=0x7f706c184480, field=0x0, no_conversions=true) at /home/alice/git/10.3/sql/item.cc:6951

#8  0x0000562ca2c43a02 in save_window_function_values (window_functions=..., tbl=0x7f706c034b30, rowid_buf=0x7f706c14b478 "") at /home/alice/git/10.3/sql/sql_window.cc:2687

#9  0x0000562ca2c43e87 in compute_window_func (thd=0x7f706c000a98, window_functions=..., cursor_managers=..., tbl=0x7f706c034b30, filesort_result=0x7f706c049100) at /home/alice/git/10.3/sql/sql_window.cc:2813

#10 0x0000562ca2c4423c in Window_func_runner::exec (this=0x7f706c03b738, thd=0x7f706c000a98, tbl=0x7f706c034b30, filesort_result=0x7f706c049100) at /home/alice/git/10.3/sql/sql_window.cc:2919

#11 0x0000562ca2c44356 in Window_funcs_sort::exec (this=0x7f706c03b730, join=0x7f706c0304f0) at /home/alice/git/10.3/sql/sql_window.cc:2944

#12 0x0000562ca2c44853 in Window_funcs_computation::exec (this=0x7f706c03b6e0, join=0x7f706c0304f0) at /home/alice/git/10.3/sql/sql_window.cc:3063

#13 0x0000562ca2acd4e8 in AGGR_OP::end_send (this=0x7f706c037f80) at /home/alice/git/10.3/sql/sql_select.cc:27229

#14 0x0000562ca2ab79d6 in sub_select_postjoin_aggr (join=0x7f706c0304f0, join_tab=0x7f706c033e50, end_of_records=true) at /home/alice/git/10.3/sql/sql_select.cc:18959

#15 0x0000562ca2ab7d0f in sub_select (join=0x7f706c0304f0, join_tab=0x7f706c033aa0, end_of_records=true) at /home/alice/git/10.3/sql/sql_select.cc:19195

#16 0x0000562ca2ab74c4 in do_select (join=0x7f706c0304f0, procedure=0x0) at /home/alice/git/10.3/sql/sql_select.cc:18785

#17 0x0000562ca2a90796 in JOIN::exec_inner (this=0x7f706c0304f0) at /home/alice/git/10.3/sql/sql_select.cc:3996

#18 0x0000562ca2a8fc06 in JOIN::exec (this=0x7f706c0304f0) at /home/alice/git/10.3/sql/sql_select.cc:3790

#19 0x0000562ca2a90e71 in mysql_select (thd=0x7f706c000a98, tables=0x7f706c02f990, wild_num=0, fields=..., conds=0x0, og_num=2, order=0x7f706c071d60, group=0x0, having=0x0, proc_param=0x0, select_options=2147748609, result=0x7f706c0304a0, unit=0x7f706c004918, select_lex=0x7f706c0050b0) at /home/alice/git/10.3/sql/sql_select.cc:4195

#20 0x0000562ca2a82e9a in handle_select (thd=0x7f706c000a98, lex=0x7f706c004850, result=0x7f706c0304a0, setup_tables_done_option=0) at /home/alice/git/10.3/sql/sql_select.cc:382

#21 0x0000562ca2a4ceca in execute_sqlcom_select (thd=0x7f706c000a98, all_tables=0x7f706c02f990) at /home/alice/git/10.3/sql/sql_parse.cc:6545

#22 0x0000562ca2a43190 in mysql_execute_command (thd=0x7f706c000a98) at /home/alice/git/10.3/sql/sql_parse.cc:3768

#23 0x0000562ca2a50bac in mysql_parse (thd=0x7f706c000a98, rawbuf=0x7f706c121530 "SELECT DISTINCT SUM(`col_int_nokey` % `col_datetime_nokey`) OVER () AS field1,\nAVG(@A := 'urndgqbngekmygyibbotkbxpuernkfpxuwvyptiaagfbkfbpjqumratns') OVER () AS field2,\nGROUP_CONCAT(CHARSET(`col_date_"..., length=1629, parser_state=0x7f70ba4355d0, is_com_multi=false, is_next_command=false) at /home/alice/git/10.3/sql/sql_parse.cc:8019

#24 0x0000562ca2a3db8f in dispatch_command (command=COM_QUERY, thd=0x7f706c000a98, packet=0x7f706c00a589 "", packet_length=1629, is_com_multi=false, is_next_command=false) at /home/alice/git/10.3/sql/sql_parse.cc:1846

#25 0x0000562ca2a3c5ac in do_command (thd=0x7f706c000a98) at /home/alice/git/10.3/sql/sql_parse.cc:1391

#26 0x0000562ca2ba709b in do_handle_one_connection (connect=0x562ca5723688) at /home/alice/git/10.3/sql/sql_connect.cc:1402

#27 0x0000562ca2ba6dec in handle_one_connection (arg=0x562ca5723688) at /home/alice/git/10.3/sql/sql_connect.cc:1308

#28 0x0000562ca34cfeb0 in pfs_spawn_thread (arg=0x562ca57cae28) at /home/alice/git/10.3/storage/perfschema/pfs.cc:1862

#29 0x00007f70c0c2f6ba in start_thread (arg=0x7f70ba436700) at pthread_create.c:333

#30 0x00007f70c00c441d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

Alice Sherepa added a comment - 2018-05-22 11:40 on 10.3 fe3bf136b6 ( Item::save_real_in_field or Item::save_int_in_field) (just to make it searchable) Thread 1 (Thread 0x7f70ba436700 (LWP 8345)): #0 __pthread_kill (threadid=<optimized out>, signo=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:62 #1 0x0000562ca354df0f in my_write_core (sig=11) at /home/alice/git/10.3/mysys/stacktrace.c:481 #2 0x0000562ca2d3c951 in handle_fatal_signal (sig=11) at /home/alice/git/10.3/sql/signal_handler.cc:305 #3 <signal handler called> #4 0x0000562ca29474d6 in Field::set_notnull (this=0x0, row_offset=0) at /home/alice/git/10.3/sql/field.h:1171 #5 0x0000562ca2d69540 in Item::save_real_in_field (this=0x7f706c184480, field=0x0, no_conversions=true) at /home/alice/git/10.3/sql/item.cc:6923 #6 0x0000562ca2c30bc6 in Type_handler_real_result::Item_save_in_field (this=0x562ca428ae30 <type_handler_double>, item=0x7f706c184480, field=0x0, no_conversions=true) at /home/alice/git/10.3/sql/sql_type.cc:2540 #7 0x0000562ca2d69715 in Item::save_in_field (this=0x7f706c184480, field=0x0, no_conversions=true) at /home/alice/git/10.3/sql/item.cc:6951 #8 0x0000562ca2c43a02 in save_window_function_values (window_functions=..., tbl=0x7f706c034b30, rowid_buf=0x7f706c14b478 "") at /home/alice/git/10.3/sql/sql_window.cc:2687 #9 0x0000562ca2c43e87 in compute_window_func (thd=0x7f706c000a98, window_functions=..., cursor_managers=..., tbl=0x7f706c034b30, filesort_result=0x7f706c049100) at /home/alice/git/10.3/sql/sql_window.cc:2813 #10 0x0000562ca2c4423c in Window_func_runner::exec (this=0x7f706c03b738, thd=0x7f706c000a98, tbl=0x7f706c034b30, filesort_result=0x7f706c049100) at /home/alice/git/10.3/sql/sql_window.cc:2919 #11 0x0000562ca2c44356 in Window_funcs_sort::exec (this=0x7f706c03b730, join=0x7f706c0304f0) at /home/alice/git/10.3/sql/sql_window.cc:2944 #12 0x0000562ca2c44853 in Window_funcs_computation::exec (this=0x7f706c03b6e0, join=0x7f706c0304f0) at /home/alice/git/10.3/sql/sql_window.cc:3063 #13 0x0000562ca2acd4e8 in AGGR_OP::end_send (this=0x7f706c037f80) at /home/alice/git/10.3/sql/sql_select.cc:27229 #14 0x0000562ca2ab79d6 in sub_select_postjoin_aggr (join=0x7f706c0304f0, join_tab=0x7f706c033e50, end_of_records=true) at /home/alice/git/10.3/sql/sql_select.cc:18959 #15 0x0000562ca2ab7d0f in sub_select (join=0x7f706c0304f0, join_tab=0x7f706c033aa0, end_of_records=true) at /home/alice/git/10.3/sql/sql_select.cc:19195 #16 0x0000562ca2ab74c4 in do_select (join=0x7f706c0304f0, procedure=0x0) at /home/alice/git/10.3/sql/sql_select.cc:18785 #17 0x0000562ca2a90796 in JOIN::exec_inner (this=0x7f706c0304f0) at /home/alice/git/10.3/sql/sql_select.cc:3996 #18 0x0000562ca2a8fc06 in JOIN::exec (this=0x7f706c0304f0) at /home/alice/git/10.3/sql/sql_select.cc:3790 #19 0x0000562ca2a90e71 in mysql_select (thd=0x7f706c000a98, tables=0x7f706c02f990, wild_num=0, fields=..., conds=0x0, og_num=2, order=0x7f706c071d60, group=0x0, having=0x0, proc_param=0x0, select_options=2147748609, result=0x7f706c0304a0, unit=0x7f706c004918, select_lex=0x7f706c0050b0) at /home/alice/git/10.3/sql/sql_select.cc:4195 #20 0x0000562ca2a82e9a in handle_select (thd=0x7f706c000a98, lex=0x7f706c004850, result=0x7f706c0304a0, setup_tables_done_option=0) at /home/alice/git/10.3/sql/sql_select.cc:382 #21 0x0000562ca2a4ceca in execute_sqlcom_select (thd=0x7f706c000a98, all_tables=0x7f706c02f990) at /home/alice/git/10.3/sql/sql_parse.cc:6545 #22 0x0000562ca2a43190 in mysql_execute_command (thd=0x7f706c000a98) at /home/alice/git/10.3/sql/sql_parse.cc:3768 #23 0x0000562ca2a50bac in mysql_parse (thd=0x7f706c000a98, rawbuf=0x7f706c121530 "SELECT DISTINCT SUM(`col_int_nokey` % `col_datetime_nokey`) OVER () AS field1,\nAVG(@A := 'urndgqbngekmygyibbotkbxpuernkfpxuwvyptiaagfbkfbpjqumratns') OVER () AS field2,\nGROUP_CONCAT(CHARSET(`col_date_"..., length=1629, parser_state=0x7f70ba4355d0, is_com_multi=false, is_next_command=false) at /home/alice/git/10.3/sql/sql_parse.cc:8019 #24 0x0000562ca2a3db8f in dispatch_command (command=COM_QUERY, thd=0x7f706c000a98, packet=0x7f706c00a589 "", packet_length=1629, is_com_multi=false, is_next_command=false) at /home/alice/git/10.3/sql/sql_parse.cc:1846 #25 0x0000562ca2a3c5ac in do_command (thd=0x7f706c000a98) at /home/alice/git/10.3/sql/sql_parse.cc:1391 #26 0x0000562ca2ba709b in do_handle_one_connection (connect=0x562ca5723688) at /home/alice/git/10.3/sql/sql_connect.cc:1402 #27 0x0000562ca2ba6dec in handle_one_connection (arg=0x562ca5723688) at /home/alice/git/10.3/sql/sql_connect.cc:1308 #28 0x0000562ca34cfeb0 in pfs_spawn_thread (arg=0x562ca57cae28) at /home/alice/git/10.3/storage/perfschema/pfs.cc:1862 #29 0x00007f70c0c2f6ba in start_thread (arg=0x7f70ba436700) at pthread_create.c:333 #30 0x00007f70c00c441d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

Varun Gupta (Inactive) added a comment - 2018-09-20 12:34

Investigation

The issue here is that for a window function in the ORDER BY clause, we were not
creating an extra field in the temporary table for the window function
(which is contained inside an expression).

An extra field is created for aggregate functions with the help of split_sum_func.

Also if we have an expression containing a window function in the select list, we
create an extra field in the temp table for the window function.

Varun Gupta (Inactive) added a comment - 2018-09-20 12:34 Investigation The issue here is that for a window function in the ORDER BY clause, we were not creating an extra field in the temporary table for the window function (which is contained inside an expression). An extra field is created for aggregate functions with the help of split_sum_func. Also if we have an expression containing a window function in the select list, we create an extra field in the temp table for the window function.

Varun Gupta (Inactive) added a comment - 2018-09-20 12:44

Patch
http://lists.askmonty.org/pipermail/commits/2018-September/012960.html

Varun Gupta (Inactive) added a comment - 2018-09-20 12:44 Patch http://lists.askmonty.org/pipermail/commits/2018-September/012960.html

Vicențiu Ciorbaru added a comment - 2018-10-14 22:22

OK to push if no failures in buildbot.

Vicențiu Ciorbaru added a comment - 2018-10-14 22:22 OK to push if no failures in buildbot.

Varun Gupta (Inactive) added a comment - 2018-11-02 17:01

Updated patch
http://lists.askmonty.org/pipermail/commits/2018-November/013055.html

Varun Gupta (Inactive) added a comment - 2018-11-02 17:01 Updated patch http://lists.askmonty.org/pipermail/commits/2018-November/013055.html

Igor Babaev added a comment - 2019-05-02 18:28

Ok to push into 10.2

Igor Babaev added a comment - 2019-05-02 18:28 Ok to push into 10.2