[MDEV-16230] Server crashes when Analyze format=json is run with a window function with empty PARTITION BY and ORDER BY clauses Created: 2018-05-21  Updated: 2020-06-09  Resolved: 2020-06-04

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - Window functions
Affects Version/s: 10.2, 10.3, 10.4
Fix Version/s: 10.5.4, 10.2.33, 10.3.24, 10.4.14

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

Attachments: File mdev16230.diff    

 Description   

The mtr test 

create table t1 (a int);
 
insert into t1 values (1),(2),(3);
 
analyze format=json
 
SELECT DISTINCT BIT_OR(100) OVER () FROM t1
 
GROUP BY LEFT('2018-08-24', 100) ;
 
drop table t1;

The query runs fine without the ANALYZE format=json

 Comments   
Comment by Varun Gupta (Inactive) [ 2018-05-21 ]

Here is the stack trace

#0  0x00007f85c3d66121 in pthread_kill () from /lib64/libpthread.so.0
 
#1  0x00007f85c517223d in my_write_core (sig=11) at /home/varun/varun/stable/mysys/stacktrace.c:477
 
#2  0x00007f85c4a12b72 in handle_fatal_signal (sig=11) at /home/varun/varun/stable/sql/signal_handler.cc:305
 
#3  <signal handler called>
 
#4  0x00007f85c4a3bf62 in Item_field::print (this=0x7f85ac015988, str=0x7f85bc6ced00, query_type=QT_EXPLAIN) at /home/varun/varun/stable/sql/item.cc:7384
 
#5  0x00007f85c492404b in append_item_to_str (out=0x7f85bc6ced00, item=0x7f85ac015988) at /home/varun/varun/stable/sql/sql_explain.cc:1367
 
#6  0x00007f85c492288a in Explain_aggr_filesort::print_json_members (this=0x7f85ac015d80, writer=0x7f85bc6cefc0, is_analyze=true) at /home/varun/varun/stable/sql/sql_explain.cc:957
 
#7  0x00007f85c49229ff in Explain_aggr_window_funcs::print_json_members (this=0x7f85ac015d58, writer=0x7f85bc6cefc0, is_analyze=true) at /home/varun/varun/stable/sql/sql_explain.cc:978
 
#8  0x00007f85c492257d in Explain_select::print_explain_json (this=0x7f85ac015c40, query=0x7f85ac015ad0, writer=0x7f85bc6cefc0, is_analyze=true) at /home/varun/varun/stable/sql/sql_explain.cc:899
 
#9  0x00007f85c491fcd8 in Explain_query::print_explain_json (this=0x7f85ac015ad0, output=0x7f85ac016510, is_analyze=true) at /home/varun/varun/stable/sql/sql_explain.cc:224
 
#10 0x00007f85c491fa33 in Explain_query::send_explain (this=0x7f85ac015ad0, thd=0x7f85ac000b00) at /home/varun/varun/stable/sql/sql_explain.cc:167
 
#11 0x00007f85c47a153f in execute_sqlcom_select (thd=0x7f85ac000b00, all_tables=0x7f85ac012bf8) at /home/varun/varun/stable/sql/sql_parse.cc:6484
 
#12 0x00007f85c479741a in mysql_execute_command (thd=0x7f85ac000b00) at /home/varun/varun/stable/sql/sql_parse.cc:3483
 
#13 0x00007f85c47a4f12 in mysql_parse (thd=0x7f85ac000b00, rawbuf=0x7f85ac0124f8 "analyze format=json \nSELECT DISTINCT BIT_OR(100) OVER () FROM t1\nGROUP BY LEFT('2018-08-24', 100)", length=97, parser_state=0x7f85bc6d0200, is_com_multi=false, is_next_command=false) at /home/varun/varun/stable/sql/sql_parse.cc:7924
 
#14 0x00007f85c4792c7e in dispatch_command (command=COM_QUERY, thd=0x7f85ac000b00, packet=0x7f85ac16bb11 "", packet_length=98, is_com_multi=false, is_next_command=false) at /home/varun/varun/stable/sql/sql_parse.cc:1820
 
#15 0x00007f85c47915c7 in do_command (thd=0x7f85ac000b00) at /home/varun/varun/stable/sql/sql_parse.cc:1374
 
#16 0x00007f85c48e2d0a in do_handle_one_connection (connect=0x7f85c83f6ab0) at /home/varun/varun/stable/sql/sql_connect.cc:1335
 
#17 0x00007f85c48e2a97 in handle_one_connection (arg=0x7f85c83f6ab0) at /home/varun/varun/stable/sql/sql_connect.cc:1241
 
#18 0x00007f85c4c3adc2 in pfs_spawn_thread (arg=0x7f85c8419490) at /home/varun/varun/stable/storage/perfschema/pfs.cc:1862
 
#19 0x00007f85c3d610db in start_thread () from /lib64/libpthread.so.0
 
#20 0x00007f85c230ee3d in clone () from /lib64/libc.so.6

Comment by Varun Gupta (Inactive) [ 2018-05-21 ]

There is a crash even without GROUP BY
analyze format=json
SELECT DISTINCT BIT_OR(100) OVER () FROM t1;

Comment by Varun Gupta (Inactive) [ 2018-05-21 ]

Here are a couple of ideas to solve the above issue

  • 1) would be for an empty order by clause in the window function , we should instead of adding the first field in the select list should instead try to add a constant. It doesn't make sense to do sorting based on any field.
  • 2) Should not do filesort for empty order by clause.
Comment by Alice Sherepa [ 2020-04-02 ] 

analyze format = json 
select rn from (select row_number() over() rn from (select 1)a)b;

10.2 05e4a87c8b074910f4bbf2b

 
Version: '10.2.32-MariaDB-debug-log' 
=================================================================
 
==11546==ERROR: AddressSanitizer: heap-use-after-free on address 0x6190000e6128 at pc 0x000000e77a1e bp 0x7ff4241cbd10 sp 0x7ff4241cbd00
 
READ of size 8 at 0x6190000e6128 thread T27
 
    #0 0xe77a1d in Item_field::print(String*, enum_query_type) /10.2/sql/item.cc:7365
 
    #1 0xbce6bd in append_item_to_str /10.2/sql/sql_explain.cc:1372
 
    #2 0xbcbcdc in Explain_aggr_filesort::print_json_members(Json_writer*, bool) /10.2/sql/sql_explain.cc:962
 
    #3 0xbcc019 in Explain_aggr_window_funcs::print_json_members(Json_writer*, bool) /10.2/sql/sql_explain.cc:983
 
    #4 0xbcb7b1 in Explain_select::print_explain_json(Explain_query*, Json_writer*, bool) /10.2/sql/sql_explain.cc:904
 
    #5 0xbcfed0 in Explain_table_access::print_explain_json(Explain_query*, Json_writer*, bool) /10.2/sql/sql_explain.cc:1705
 
    #6 0xbcc3c2 in Explain_basic_join::print_explain_json_interns(Explain_query*, Json_writer*, bool) /10.2/sql/sql_explain.cc:1014
 
    #7 0xbcb841 in Explain_select::print_explain_json(Explain_query*, Json_writer*, bool) /10.2/sql/sql_explain.cc:913
 
    #8 0xbc7057 in Explain_query::print_explain_json(select_result_sink*, bool) /10.2/sql/sql_explain.cc:224
 
    #9 0xbc69dc in Explain_query::send_explain(THD*) /10.2/sql/sql_explain.cc:167
 
    #10 0x842800 in execute_sqlcom_select /10.2/sql/sql_parse.cc:6236
 
    #11 0x830e03 in mysql_execute_command(THD*) /10.2/sql/sql_parse.cc:3531
 
    #12 0x84b03e in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /10.2/sql/sql_parse.cc:7739
 
    #13 0x827724 in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /10.2/sql/sql_parse.cc:1831
 
    #14 0x82484f in do_command(THD*) /10.2/sql/sql_parse.cc:1385
 
    #15 0xb5c820 in do_handle_one_connection(CONNECT*) /10.2/sql/sql_connect.cc:1336
 
    #16 0xb5c204 in handle_one_connection /10.2/sql/sql_connect.cc:1241
 
    #17 0x1d26907 in pfs_spawn_thread /10.2/storage/perfschema/pfs.cc:1869
 
    #18 0x7ff43b80d6b9 in start_thread (/lib/x86_64-linux-gnu/libpthread.so.0+0x76b9)
 
    #19 0x7ff43aca241c in clone (/lib/x86_64-linux-gnu/libc.so.6+0x10741c)
 
 
 
0x6190000e6128 is located 168 bytes inside of 1100-byte region [0x6190000e6080,0x6190000e64cc)
 
freed by thread T27 here:
 
    #0 0x7ff43c9432ca in __interceptor_free (/usr/lib/x86_64-linux-gnu/libasan.so.2+0x982ca)
 
    #1 0x1eec22b in free_memory /10.2/mysys/safemalloc.c:279
 
    #2 0x1eeb85a in sf_free /10.2/mysys/safemalloc.c:197
 
    #3 0x1ebb45d in my_free /10.2/mysys/my_malloc.c:218
 
    #4 0x1e9c92c in free_root /10.2/mysys/my_alloc.c:400
 
    #5 0x93b1bc in free_tmp_table(THD*, TABLE*) /10.2/sql/sql_select.cc:18195
 
    #6 0x916ff1 in JOIN::cleanup(bool) /10.2/sql/sql_select.cc:12371
 
    #7 0x916410 in JOIN::join_free() /10.2/sql/sql_select.cc:12264
 
    #8 0x93cd9b in do_select /10.2/sql/sql_select.cc:18457
 
    #9 0x8dcca2 in JOIN::exec_inner() /10.2/sql/sql_select.cc:3633
 
    #10 0x8daa35 in JOIN::exec() /10.2/sql/sql_select.cc:3428
 
    #11 0x8dde4a 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:3828
 
    #12 0x7be153 in mysql_derived_fill(THD*, LEX*, TABLE_LIST*) /10.2/sql/sql_derived.cc:1097
 
    #13 0x7bcf68 in mysql_derived_optimize(THD*, LEX*, TABLE_LIST*) /10.2/sql/sql_derived.cc:917
 
    #14 0x7b8dd0 in mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int) /10.2/sql/sql_derived.cc:198
 
    #15 0x8c713c in JOIN::optimize_inner() /10.2/sql/sql_select.cc:1400
 
    #16 0x8c42f4 in JOIN::optimize() /10.2/sql/sql_select.cc:1113
 
    #17 0x8ddc5e 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:3814
 
    #18 0x8bcf17 in handle_select(THD*, LEX*, select_result*, unsigned long) /10.2/sql/sql_select.cc:373
 
    #19 0x842588 in execute_sqlcom_select /10.2/sql/sql_parse.cc:6224
 
    #20 0x830e03 in mysql_execute_command(THD*) /10.2/sql/sql_parse.cc:3531
 
    #21 0x84b03e in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /10.2/sql/sql_parse.cc:7739
 
    #22 0x827724 in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /10.2/sql/sql_parse.cc:1831
 
    #23 0x82484f in do_command(THD*) /10.2/sql/sql_parse.cc:1385
 
    #24 0xb5c820 in do_handle_one_connection(CONNECT*) /10.2/sql/sql_connect.cc:1336
 
    #25 0xb5c204 in handle_one_connection /10.2/sql/sql_connect.cc:1241
 
    #26 0x1d26907 in pfs_spawn_thread /10.2/storage/perfschema/pfs.cc:1869
 
    #27 0x7ff43b80d6b9 in start_thread (/lib/x86_64-linux-gnu/libpthread.so.0+0x76b9)
 
 
 
previously allocated by thread T27 here:
 
    #0 0x7ff43c943602 in malloc (/usr/lib/x86_64-linux-gnu/libasan.so.2+0x98602)
 
    #1 0x1eeb217 in sf_malloc /10.2/mysys/safemalloc.c:118
 
    #2 0x1ebab47 in my_malloc /10.2/mysys/my_malloc.c:101
 
    #3 0x1e9b9f6 in alloc_root /10.2/mysys/my_alloc.c:242
 
    #4 0x978c1a in Field::operator new(unsigned long, st_mem_root*) /10.2/sql/field.h:710
 
    #5 0x92c9b4 in Item::create_tmp_field(bool, TABLE*, unsigned int) /10.2/sql/sql_select.cc:16322
 
    #6 0x6474dc in Item::create_tmp_field(bool, TABLE*) /10.2/sql/item.h:1868
 
    #7 0x92cf05 in create_tmp_field_from_item /10.2/sql/sql_select.cc:16386
 
    #8 0x92e1c1 in create_tmp_field(THD*, TABLE*, Item*, Item::Type, Item***, Field**, Field**, bool, bool, bool, bool) /10.2/sql/sql_select.cc:16606
 
    #9 0x930d52 in create_tmp_table(THD*, TMP_TABLE_PARAM*, List<Item>&, st_order*, bool, bool, unsigned long long, unsigned long long, char const*, bool, bool) /10.2/sql/sql_select.cc:16994
 
    #10 0x8d64bd in JOIN::create_postjoin_aggr_table(st_join_table*, List<Item>*, st_order*, bool, bool, bool) /10.2/sql/sql_select.cc:2968
 
    #11 0x8d29c8 in JOIN::make_aggr_tables_info() /10.2/sql/sql_select.cc:2570
 
    #12 0x8cf0e1 in JOIN::optimize_inner() /10.2/sql/sql_select.cc:2241
 
    #13 0x8c42f4 in JOIN::optimize() /10.2/sql/sql_select.cc:1113
 
    #14 0x7bcd41 in mysql_derived_optimize(THD*, LEX*, TABLE_LIST*) /10.2/sql/sql_derived.cc:897
 
    #15 0x7b8dd0 in mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int) /10.2/sql/sql_derived.cc:198
 
    #16 0x8c713c in JOIN::optimize_inner() /10.2/sql/sql_select.cc:1400
 
    #17 0x8c42f4 in JOIN::optimize() /10.2/sql/sql_select.cc:1113
 
    #18 0x8ddc5e 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:3814
 
    #19 0x8bcf17 in handle_select(THD*, LEX*, select_result*, unsigned long) /10.2/sql/sql_select.cc:373
 
    #20 0x842588 in execute_sqlcom_select /10.2/sql/sql_parse.cc:6224
 
    #21 0x830e03 in mysql_execute_command(THD*) /10.2/sql/sql_parse.cc:3531
 
    #22 0x84b03e in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /10.2/sql/sql_parse.cc:7739
 
    #23 0x827724 in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /10.2/sql/sql_parse.cc:1831
 
    #24 0x82484f in do_command(THD*) /10.2/sql/sql_parse.cc:1385
 
    #25 0xb5c820 in do_handle_one_connection(CONNECT*) /10.2/sql/sql_connect.cc:1336
 
    #26 0xb5c204 in handle_one_connection /10.2/sql/sql_connect.cc:1241
 
    #27 0x1d26907 in pfs_spawn_thread /10.2/storage/perfschema/pfs.cc:1869
 
    #28 0x7ff43b80d6b9 in start_thread (/lib/x86_64-linux-gnu/libpthread.so.0+0x76b9)
 
 
 
Thread T27 created by T0 here:
 
    #0 0x7ff43c8e1253 in pthread_create (/usr/lib/x86_64-linux-gnu/libasan.so.2+0x36253)
 
    #1 0x1d26cf4 in spawn_thread_v1 /10.2/storage/perfschema/pfs.cc:1919
 
    #2 0x5fe758 in inline_mysql_thread_create /10.2/include/mysql/psi/mysql_thread.h:1246
 
    #3 0x613529 in create_thread_to_handle_connection(CONNECT*) /10.2/sql/mysqld.cc:6514
 
    #4 0x613c4d in create_new_thread /10.2/sql/mysqld.cc:6584
 
    #5 0x614cad in handle_connections_sockets() /10.2/sql/mysqld.cc:6859
 
    #6 0x612a11 in mysqld_main(int, char**) /10.2/sql/mysqld.cc:6133
 
    #7 0x5fd065 in main /10.2/sql/main.cc:25
 
    #8 0x7ff43abbb82f in __libc_start_main (/lib/x86_64-linux-gnu/libc.so.6+0x2082f)
 
 
 
SUMMARY: AddressSanitizer: heap-use-after-free /10.2/sql/item.cc:7365 Item_field::print(String*, enum_query_type)
 
Shadow bytes around the buggy address:
 
  0x0c3280014bd0: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 
  0x0c3280014be0: 00 00 00 00 00 00 00 00 00 00 04 fa fa fa fa fa
 
  0x0c3280014bf0: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
 
  0x0c3280014c00: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
 
  0x0c3280014c10: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
 
=>0x0c3280014c20: fd fd fd fd fd[fd]fd fd fd fd fd fd fd fd fd fd
 
  0x0c3280014c30: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
 
  0x0c3280014c40: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
 
  0x0c3280014c50: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
 
  0x0c3280014c60: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
 
  0x0c3280014c70: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
 
Shadow byte legend (one shadow byte represents 8 application bytes):
 
  Addressable:           00
 
  Partially addressable: 01 02 03 04 05 06 07 
  Heap left redzone:       fa
 
  Heap right redzone:      fb
 
  Freed heap region:       fd
 
  Stack left redzone:      f1
 
  Stack mid redzone:       f2
 
  Stack right redzone:     f3
 
  Stack partial redzone:   f4
 
  Stack after return:      f5
 
  Stack use after scope:   f8
 
  Global redzone:          f9
 
  Global init order:       f6
 
  Poisoned by user:        f7
 
  Container overflow:      fc
 
  Array cookie:            ac
 
  Intra object redzone:    bb
 
  ASan internal:           fe
 
==11546==ABORTING
 
----------SERVER LOG END-------------

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

Such queries would always fail with ANALYZE FORMAT=JSON when there is no PARTITION BY clause or ORDER BY clause in the window function.

Currently when both PARTITION BY and ORDER BY clauses are empty then we create a Item with the first field
in the select list and sort with that field. Currently window function execution depends on the fact that we read the records from the result of filesort.

Comment by Sergei Petrunia [ 2020-06-03 ]

Ok to push after review input is addressed.

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