[MDEV-15212] server crashed, query using ORDER BY (expression with window function) Created: 2018-02-05  Updated: 2019-05-05  Resolved: 2019-05-05

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - Window functions
Affects Version/s: 10.2, 10.3, 10.4
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Alice Sherepa Assignee: Varun Gupta (Inactive)
Resolution: Duplicate Votes: 0
Labels: None

Issue Links:
Duplicate
duplicates MDEV-14791 Crash with order by expression contai... Closed
is duplicated by MDEV-18965 ORDER BY is incorrect with window fun... Closed
Relates
relates to MDEV-16736 crash on lag() over() and order not ... Closed

 Description    

CREATE TABLE t1(i int, j int);
 
INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(10,1),(20,2),(30,3),(20,1),(40,2),(60,3),(30,1),(60,2),(90,3);
 
 
 
SELECT SUM(i) OVER (PARTITION BY j ORDER BY i) as k, i, j FROM t1 order by k ;
 
 
 
SELECT 1+ (SUM(i) OVER (PARTITION BY j ORDER BY i)) as k,i,j FROM t1 order by k ;  # results are not ordered by k
 
 
 
# server crash:
 
SELECT 1+ (SUM(i) OVER (PARTITION BY j ORDER BY i)) as k,i,j FROM t1 order by 1+ (SUM(i) OVER (PARTITION BY j ORDER BY i)) ;


SELECT SUM(i) OVER (PARTITION BY j ORDER BY i) as k, i, j FROM t1 order by k ;
 
k	i	j
 
1	1	1
 
2	2	2
 
3	3	3
 
11	10	1
 
22	20	2
 
31	20	1
 
33	30	3
 
61	30	1
 
62	40	2
 
93	60	3
 
122	60	2
 
183	90	3
 
SELECT 1+ (SUM(i) OVER (PARTITION BY j ORDER BY i)) as k,i,j FROM t1 order by k ;
 
k	i	j
 
2	1	1
 
184	90	3
 
32	20	1
 
3	2	2
 
63	40	2
 
4	3	3
 
94	60	3
 
12	10	1
 
62	30	1
 
23	20	2
 
123	60	2
 
34	30	3
 



Thread 1 (Thread 0x7fcd90066700 (LWP 16924)):
 
#0  __pthread_kill (threadid=<optimized out>, signo=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:62
 
#1  0x0000565343fad403 in my_write_core (sig=11) at /home/alice/git/10.2/mysys/stacktrace.c:477
 
#2  0x000056534383d6f1 in handle_fatal_signal (sig=11) at /home/alice/git/10.2/sql/signal_handler.cc:305
 
#3  <signal handler called>
 
#4  0x0000565343513c2e in Field::set_notnull (this=0x0, row_offset=0) at /home/alice/git/10.2/sql/field.h:1137
 
#5  0x0000565343863782 in Item::save_in_field (this=0x7fcd7417ed90, field=0x0, no_conversions=true) at /home/alice/git/10.2/sql/item.cc:6456
 
#6  0x00005653437926ec in save_window_function_values (window_functions=..., tbl=0x7fcd740605f0, rowid_buf=0x7fcd74066058 "\030\315\002t\315\177") at /home/alice/git/10.2/sql/sql_window.cc:2517
 
#7  0x0000565343792b23 in compute_window_func (thd=0x7fcd74000a98, window_functions=..., cursor_managers=..., tbl=0x7fcd740605f0, filesort_result=0x7fcd74147e60) at /home/alice/git/10.2/sql/sql_window.cc:2636
 
#8  0x0000565343792ed8 in Window_func_runner::exec (this=0x7fcd7406bb68, thd=0x7fcd74000a98, tbl=0x7fcd740605f0, filesort_result=0x7fcd74147e60) at /home/alice/git/10.2/sql/sql_window.cc:2742
 
#9  0x0000565343792ff2 in Window_funcs_sort::exec (this=0x7fcd7406bb60, join=0x7fcd740fceb0) at /home/alice/git/10.2/sql/sql_window.cc:2767
 
#10 0x00005653437934af in Window_funcs_computation::exec (this=0x7fcd74120be0, join=0x7fcd740fceb0) at /home/alice/git/10.2/sql/sql_window.cc:2886
 
#11 0x0000565343650aaa in AGGR_OP::end_send (this=0x7fcd7406ac90) at /home/alice/git/10.2/sql/sql_select.cc:26540
 
#12 0x000056534363bc26 in sub_select_postjoin_aggr (join=0x7fcd740fceb0, join_tab=0x7fcd740697d0, end_of_records=true) at /home/alice/git/10.2/sql/sql_select.cc:18379
 
#13 0x000056534363bf4a in sub_select (join=0x7fcd740fceb0, join_tab=0x7fcd74069420, end_of_records=true) at /home/alice/git/10.2/sql/sql_select.cc:18615
 
#14 0x000056534363b75b in do_select (join=0x7fcd740fceb0, procedure=0x0) at /home/alice/git/10.2/sql/sql_select.cc:18210
 
#15 0x0000565343616185 in JOIN::exec_inner (this=0x7fcd740fceb0) at /home/alice/git/10.2/sql/sql_select.cc:3540
 
#16 0x0000565343615626 in JOIN::exec (this=0x7fcd740fceb0) at /home/alice/git/10.2/sql/sql_select.cc:3335
 
#17 0x00005653436167f6 in mysql_select (thd=0x7fcd74000a98, tables=0x7fcd74115de0, wild_num=0, fields=..., conds=0x0, og_num=1, order=0x7fcd740fc8b0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fcd74010080, unit=0x7fcd74004638, select_lex=0x7fcd74004d90) at /home/alice/git/10.2/sql/sql_select.cc:3735
 
#18 0x000056534360aeff in handle_select (thd=0x7fcd74000a98, lex=0x7fcd74004570, result=0x7fcd74010080, setup_tables_done_option=0) at /home/alice/git/10.2/sql/sql_select.cc:373
 
#19 0x00005653435d6e82 in execute_sqlcom_select (thd=0x7fcd74000a98, all_tables=0x7fcd74115de0) at /home/alice/git/10.2/sql/sql_parse.cc:6456
 
#20 0x00005653435ccc00 in mysql_execute_command (thd=0x7fcd74000a98) at /home/alice/git/10.2/sql/sql_parse.cc:3467
 
#21 0x00005653435da81d in mysql_parse (thd=0x7fcd74000a98, rawbuf=0x7fcd7402bb50 "SELECT 1+ (SUM(i) OVER (PARTITION BY j ORDER BY i)) as k,i,j FROM t1 order by 1+ (SUM(i) OVER (PARTITION BY j ORDER BY i))", length=122, parser_state=0x7fcd900651f0, is_com_multi=false, is_next_command=false) at /home/alice/git/10.2/sql/sql_parse.cc:7897
 
#22 0x00005653435c849f in dispatch_command (command=COM_QUERY, thd=0x7fcd74000a98, packet=0x7fcd74007fe9 "", packet_length=123, is_com_multi=false, is_next_command=false) at /home/alice/git/10.2/sql/sql_parse.cc:1806
 
#23 0x00005653435c6dfb in do_command (thd=0x7fcd74000a98) at /home/alice/git/10.2/sql/sql_parse.cc:1360
 
#24 0x000056534371501f in do_handle_one_connection (connect=0x565345e96d48) at /home/alice/git/10.2/sql/sql_connect.cc:1335
 
#25 0x0000565343714d9f in handle_one_connection (arg=0x565345e96d48) at /home/alice/git/10.2/sql/sql_connect.cc:1241
 
#26 0x0000565343a72bc4 in pfs_spawn_thread (arg=0x565345df6908) at /home/alice/git/10.2/storage/perfschema/pfs.cc:1862
 
#27 0x00007fcd961cf6ba in start_thread (arg=0x7fcd90066700) at pthread_create.c:333
 
#28 0x00007fcd9566441d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109



 Comments   
Comment by Alice Sherepa [ 2019-03-20 ]

Test case from MDEV-18965:

CREATE TABLE test (a int , b int);
 
INSERT INTO test VALUES (1,100),(1,90),(2,5),(2,2);
 
 
 
SELECT a,
 
       row_number() over (partition by a),
 
       if(row_number() over (partition by a) =1,1,0) as c
 
FROM test
 
ORDER BY c,b; # wrong order
 
 
 
SELECT a,
 
       row_number() over (partition by a),
 
       if(row_number() over (partition by a) =1,1,0) as c
 
FROM test
 
ORDER BY if(row_number() over (partition by a) =1,1,0), b;

10.2 00572a0b0cc81c38f198adcc55

 
    #0 0x560af8ef9e08 in Field::set_notnull(long long) /10.2/sql/field.h:1143
 
    #1 0x560af96b9949 in Item::save_in_field(Field*, bool) /10.2/sql/item.cc:6414
 
    #2 0x560af94c2a75 in save_window_function_values /10.2/sql/sql_window.cc:2517
 
    #3 0x560af94c3296 in compute_window_func(THD*, List<Item_window_func>&, List<Cursor_manager>&, TABLE*, SORT_INFO*) /10.2/sql/sql_window.cc:2636
 
    #4 0x560af94c390a in Window_func_runner::exec(THD*, TABLE*, SORT_INFO*) /10.2/sql/sql_window.cc:2742
 
    #5 0x560af94c3b59 in Window_funcs_sort::exec(JOIN*, bool) /10.2/sql/sql_window.cc:2767
 
    #6 0x560af94c4820 in Window_funcs_computation::exec(JOIN*, bool) /10.2/sql/sql_window.cc:2893
 
    #7 0x560af91d8e4d in AGGR_OP::end_send() /10.2/sql/sql_select.cc:26671
 
    #8 0x560af91a13a4 in sub_select_postjoin_aggr(JOIN*, st_join_table*, bool) /10.2/sql/sql_select.cc:18473
 
    #9 0x560af91a1b23 in sub_select(JOIN*, st_join_table*, bool) /10.2/sql/sql_select.cc:18709
 
    #10 0x560af91a07b8 in do_select /10.2/sql/sql_select.cc:18304
 
    #11 0x560af9142fcb in JOIN::exec_inner() /10.2/sql/sql_select.cc:3623
 
    #12 0x560af9140cd1 in JOIN::exec() /10.2/sql/sql_select.cc:3418
 
    #13 0x560af914401e in mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) /10.2/sql/sql_select.cc:3818
 
    #14 0x560af91237e4 in handle_select(THD*, LEX*, select_result*, unsigned long) /10.2/sql/sql_select.cc:376
 
    #15 0x560af90ab154 in execute_sqlcom_select /10.2/sql/sql_parse.cc:6477
 
    #16 0x560af909861a in mysql_execute_command(THD*) /10.2/sql/sql_parse.cc:3535
 
    #17 0x560af90b38b8 in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /10.2/sql/sql_parse.cc:8011
 
    #18 0x560af908ef6d in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /10.2/sql/sql_parse.cc:1832
 
    #19 0x560af908c0ec in do_command(THD*) /10.2/sql/sql_parse.cc:1386
 
    #20 0x560af93b3d61 in do_handle_one_connection(CONNECT*) /10.2/sql/sql_connect.cc:1335
 
    #21 0x560af93b3769 in handle_one_connection /10.2/sql/sql_connect.cc:1241
 
    #22 0x560afa564fd9 in pfs_spawn_thread /10.2/storage/perfschema/pfs.cc:1862
 
    #23 0x7f965ccf36b9 in start_thread (/lib/x86_64-linux-gnu/libpthread.so.0+0x76b9)
 
    #24 0x7f965c18841c in clone (/lib/x86_64-linux-gnu/libc.so.6+0x10741c)

Comment by Varun Gupta (Inactive) [ 2019-04-12 ]

Looks like a duplicate of MDEV-14791

Comment by Varun Gupta (Inactive) [ 2019-05-05 ]

Fixed by MDEV-14791

MariaDB [test]> SELECT a,
 
    ->        row_number() over (partition by a),
 
    ->        if(row_number() over (partition by a) =1,1,0) as c
 
    -> FROM test
 
    -> ORDER BY c,b; # wrong order
 
+------+------------------------------------+---+
 
| a    | row_number() over (partition by a) | c |
 
+------+------------------------------------+---+
 
|    2 |                                  2 | 0 |
 
|    1 |                                  2 | 0 |
 
|    2 |                                  1 | 1 |
 
|    1 |                                  1 | 1 |
 
+------+------------------------------------+---+
 
4 rows in set (0.01 sec)
 
 
 
MariaDB [test]> 
MariaDB [test]> SELECT a,
 
    ->        row_number() over (partition by a),
 
    ->        if(row_number() over (partition by a) =1,1,0) as c
 
    -> FROM test
 
    -> ORDER BY if(row_number() over (partition by a) =1,1,0), b;
 
+------+------------------------------------+---+
 
| a    | row_number() over (partition by a) | c |
 
+------+------------------------------------+---+
 
|    2 |                                  2 | 0 |
 
|    1 |                                  2 | 0 |
 
|    2 |                                  1 | 1 |
 
|    1 |                                  1 | 1 |
 
+------+------------------------------------+---+
 
4 rows in set (0.00 sec)

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