[MDEV-15180] server crashed with NTH_VALUE() Created: 2018-02-02  Updated: 2020-10-06  Resolved: 2020-08-06

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - Window functions
Affects Version/s: 10.2, 10.3, 10.4
Fix Version/s: 10.2.35, 10.3.26, 10.4.16, 10.5.7

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

Issue Links:
Duplicate
is duplicated by MDEV-20592 window functions crash Closed
Relates
relates to MDEV-19064 Inconsistent behavior while using nes... Closed

 Description    

CREATE TABLE t1 (i1 int, a int);
 
INSERT INTO t1 VALUES (1, 1), (2, 2),(3, 3);
 
 
 
CREATE TABLE t2 (i2 int);
 
INSERT INTO t2 VALUES (1),(2),(5),(1),(7),(4),(3);
 
 
 
SELECT NTH_VALUE(i1, i1) OVER (PARTITION BY i1)
 
FROM (SELECT * FROM t1,t2 WHERE t1.i1=t2.i2) t;


#3  <signal handler called>
 
#4  0x0000560fe20e66c1 in Item_field::used_tables (this=0x7fb66817e350) at /10.2/sql/item.cc:2876
 
#5  0x0000560fe21a378e in Item_sum::update_used_tables (this=0x7fb668179870) at /10.2/sql/item_sum.cc:546
 
#6  0x0000560fe21a2be6 in Item_sum::check_sum_func (this=0x7fb668179870, thd=0x7fb668000a98, ref=0x7fb668111580) at /10.2/sql/item_sum.cc:312
 
#7  0x0000560fe22b8018 in Item_sum_hybrid_simple::fix_fields (this=0x7fb668179870, thd=0x7fb668000a98, ref=0x7fb668111580) at /10.2/sql/item_windowfunc.cc:273
 
#8  0x0000560fe22b77eb in Item_window_func::fix_fields (this=0x7fb66817e470, thd=0x7fb668000a98, ref=0x7fb668111580) at /10.2/sql/item_windowfunc.cc:102
 
#9  0x0000560fe1df2065 in setup_fields (thd=0x7fb668000a98, ref_pointer_array=..., fields=..., mark_used_columns=MARK_COLUMNS_READ, sum_func_list=0x7fb668062400, pre_fix=0x7fb668004ee0, allow_sum_func=true) at /10.2/sql/sql_base.cc:7058
 
#10 0x0000560fe1e98b4a in JOIN::prepare (this=0x7fb6680620b0, tables_init=0x7fb6681842b0, 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=0x7fb668004d90, unit_arg=0x7fb668004638) at /10.2/sql/sql_select.cc:806
 
#11 0x0000560fe1ea3080 in mysql_select (thd=0x7fb668000a98, tables=0x7fb6681842b0, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fb668179e50, unit=0x7fb668004638, select_lex=0x7fb668004d90) at /10.2/sql/sql_select.cc:3713
 
#12 0x0000560fe1e9783b in handle_select (thd=0x7fb668000a98, lex=0x7fb668004570, result=0x7fb668179e50, setup_tables_done_option=0) at /10.2/sql/sql_select.cc:373
 
#13 0x0000560fe1e637e4 in execute_sqlcom_select (thd=0x7fb668000a98, all_tables=0x7fb6681842b0) at /10.2/sql/sql_parse.cc:6456
 
#14 0x0000560fe1e59562 in mysql_execute_command (thd=0x7fb668000a98) at /10.2/sql/sql_parse.cc:3467
 
#15 0x0000560fe1e67158 in mysql_parse (thd=0x7fb668000a98, rawbuf=0x7fb66806aa10 "SELECT NTH_VALUE(i1, i1) OVER (PARTITION BY i1)\nFROM (SELECT * FROM t1,t2 WHERE t1.i1=t2.i2) t", length=94, parser_state=0x7fb6842561f0, is_com_multi=false, is_next_command=false) at /10.2/sql/sql_parse.cc:7898
 
#16 0x0000560fe1e54e01 in dispatch_command (command=COM_QUERY, thd=0x7fb668000a98, packet=0x7fb668007fe9 "SELECT NTH_VALUE(i1, i1) OVER (PARTITION BY i1)\nFROM (SELECT * FROM t1,t2 WHERE t1.i1=t2.i2) t", packet_length=94, is_com_multi=false, is_next_command=false) at /10.2/sql/sql_parse.cc:1806
 
#17 0x0000560fe1e5375d in do_command (thd=0x7fb668000a98) at /10.2/sql/sql_parse.cc:1360
 
#18 0x0000560fe1fa18e7 in do_handle_one_connection (connect=0x560fe4008d48) at /10.2/sql/sql_connect.cc:1335
 
#19 0x0000560fe1fa1667 in handle_one_connection (arg=0x560fe4008d48) at /10.2/sql/sql_connect.cc:1241
 
#20 0x0000560fe22ff01a in pfs_spawn_thread (arg=0x560fe3f68908) at /10.2/storage/perfschema/pfs.cc:1862
 
#21 0x00007fb68a3c06ba in start_thread (arg=0x7fb684257700) at pthread_create.c:333
 
#22 0x00007fb68985541d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109



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

still reproducible on 10.2

10.2 26432e49d37a37

 
 
 
==11537==ERROR: AddressSanitizer: SEGV on unknown address 0x000000000018 (pc 0x56362a69f395 bp 0x7fc2c015de10 sp 0x7fc2c015de00 T27)
 
    #0 0x56362a69f394 in Item_field::used_tables() const /10.2/sql/item.cc:2942
 
    #1 0x56362a85c60b in Item_sum::update_used_tables() /10.2/sql/item_sum.cc:546
 
    #2 0x56362a85a2e5 in Item_sum::check_sum_func(THD*, Item**) /10.2/sql/item_sum.cc:312
 
    #3 0x56362aaed82e in Item_sum_hybrid_simple::fix_fields(THD*, Item**) /10.2/sql/item_windowfunc.cc:275
 
    #4 0x56362aaec1d0 in Item_window_func::fix_fields(THD*, Item**) /10.2/sql/item_windowfunc.cc:102
 
    #5 0x563629fa2d4c in setup_fields(THD*, Bounds_checked_array<Item*>, List<Item>&, enum_mark_columns, List<Item>*, List<Item>*, bool) /10.2/sql/sql_base.cc:7160
 
    #6 0x56362a1250c0 in JOIN::prepare(TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*) /10.2/sql/sql_select.cc:814
 
    #7 0x56362a141e07 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:3796
 
    #8 0x56362a1217e4 in handle_select(THD*, LEX*, select_result*, unsigned long) /10.2/sql/sql_select.cc:376
 
    #9 0x56362a0a9154 in execute_sqlcom_select /10.2/sql/sql_parse.cc:6477
 
    #10 0x56362a09661a in mysql_execute_command(THD*) /10.2/sql/sql_parse.cc:3535
 
    #11 0x56362a0b18b8 in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /10.2/sql/sql_parse.cc:8011
 
    #12 0x56362a08cf6d in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /10.2/sql/sql_parse.cc:1832
 
    #13 0x56362a08a0ec in do_command(THD*) /10.2/sql/sql_parse.cc:1386
 
    #14 0x56362a3b1d61 in do_handle_one_connection(CONNECT*) /10.2/sql/sql_connect.cc:1335
 
    #15 0x56362a3b1769 in handle_one_connection /10.2/sql/sql_connect.cc:1241
 
    #16 0x56362b562fef in pfs_spawn_thread /10.2/storage/perfschema/pfs.cc:1862
 
    #17 0x7fc2d775a6b9 in start_thread (/lib/x86_64-linux-gnu/libpthread.so.0+0x76b9)
 
    #18 0x7fc2d6bef41c in clone (/lib/x86_64-linux-gnu/libc.so.6+0x10741c)

Comment by Alice Sherepa [ 2019-03-27 ]

Another test case, but this one crashes on 10.2-10.4. 

create table t1 (a int);
 
insert into t1 values (1), (2), (3);
 
select lead(max(a) over (partition by a)) over (order by a)  from t1 group by a;

10.2 1e9c2b2305c10ccaad2

 
   ==14474==ERROR: AddressSanitizer: SEGV on unknown address 0x000000000018 (pc 0x55864c9ece4d bp 0x7f93aa69fdd0 sp 0x7f93aa69fdc0 T27)
 
    #0 0x55864c9ece4c in Item_field::used_tables() const /10.2/sql/item.cc:2942
 
    #1 0x55864ce3934a in Item_window_func::update_used_tables() /10.2/sql/item_windowfunc.cc:58
 
    #2 0x55864cba9c7e in Item_sum::update_used_tables() /10.2/sql/item_sum.cc:545
 
    #3 0x55864cba7a73 in Item_sum::check_sum_func(THD*, Item**) /10.2/sql/item_sum.cc:312
 
    #4 0x55864ce3b110 in Item_sum_hybrid_simple::fix_fields(THD*, Item**) /10.2/sql/item_windowfunc.cc:275
 
    #5 0x55864ce39ab2 in Item_window_func::fix_fields(THD*, Item**) /10.2/sql/item_windowfunc.cc:102
 
    #6 0x55864c2f04dc in setup_fields(THD*, Bounds_checked_array<Item*>, List<Item>&, enum_mark_columns, List<Item>*, List<Item>*, bool) /10.2/sql/sql_base.cc:7160
 
    #7 0x55864c472850 in JOIN::prepare(TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*) /10.2/sql/sql_select.cc:814
 
    #8 0x55864c48f597 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:3796
 
    #9 0x55864c46ef74 in handle_select(THD*, LEX*, select_result*, unsigned long) /10.2/sql/sql_select.cc:376
 
    #10 0x55864c3f68e4 in execute_sqlcom_select /10.2/sql/sql_parse.cc:6479
 
    #11 0x55864c3e3daa in mysql_execute_command(THD*) /10.2/sql/sql_parse.cc:3537
 
    #12 0x55864c3ff048 in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /10.2/sql/sql_parse.cc:8013
 
    #13 0x55864c3da6fd in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /10.2/sql/sql_parse.cc:1832
 
    #14 0x55864c3d787c in do_command(THD*) /10.2/sql/sql_parse.cc:1386
 
    #15 0x55864c6ff65d in do_handle_one_connection(CONNECT*) /10.2/sql/sql_connect.cc:1335
 
    #16 0x55864c6ff065 in handle_one_connection /10.2/sql/sql_connect.cc:1241
 
    #17 0x55864d8b6a85 in pfs_spawn_thread /10.2/storage/perfschema/pfs.cc:1862
 
    #18 0x7f93c1ca06b9 in start_thread (/lib/x86_64-linux-gnu/libpthread.so.0+0x76b9)
 
    #19 0x7f93c113541c in clone (/lib/x86_64-linux-gnu/libc.so.6+0x10741c)

Comment by Alice Sherepa [ 2019-09-16 ]

please check also test case from MDEV-20592:

drop table if exists t;
 
 create table t(a int) engine=innodb;
 
 select ntile(5) over(), 
  last_value(std(0) over(w2 partition by a)) 
  over(order by a) 
 from t window w1 as (w2 order by a);

Comment by Varun Gupta (Inactive) [ 2020-06-22 ]

This example posted by alice above

create table t1 (a int);
 
insert into t1 values (1), (2), (3);
 
select lead(max(a) over (partition by a)) over (order by a)  from t1 group by a;

We should throw an error here, because by standard for window function, its arguments cannot have a window function.
The same is the case with the second example:

select ntile(5) over(), 
  last_value(std(0) over(w2 partition by a)) 
  over(order by a) 
 from t window w1 as (w2 order by a);

Have opened a separate issue for these 2 cases as we should report an error here. The issue is
MDEV-22984

Comment by Varun Gupta (Inactive) [ 2020-06-23 ]

Patch
https://github.com/mariadb/server/commit/d527daa499d26e61cbc96b6a11a8d4281de1f6a3

Comment by Sergei Petrunia [ 2020-08-06 ]

Ok to push.

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