Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-15180

server crashed with NTH_VALUE()

Details

    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

      Attachments

        Issue Links

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-20592 window functions crash

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-19064 Inconsistent behavior while using nested window functions (first_value, last_value, lead,lag)

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            alice Alice Sherepa added a comment -

            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)

            alice Alice Sherepa added a comment - 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)
            alice Alice Sherepa added a comment - - edited

            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)

            alice Alice Sherepa added a comment - - edited 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)
            alice Alice Sherepa added a comment -

            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);

            alice Alice Sherepa added a comment - 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);
            varun Varun Gupta (Inactive) added a comment - - edited

            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

            varun Varun Gupta (Inactive) added a comment - - edited 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
            varun Varun Gupta (Inactive) added a comment -

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

            varun Varun Gupta (Inactive) added a comment - Patch https://github.com/mariadb/server/commit/d527daa499d26e61cbc96b6a11a8d4281de1f6a3
            psergei Sergei Petrunia added a comment -

            Ok to push.

            psergei Sergei Petrunia added a comment - Ok to push.

            People

              varun Varun Gupta (Inactive)
              alice Alice Sherepa
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.