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

Window functions with is null function in query crashes server

Details

    • 10.2.7-1

    Description

      The following window function expression crashes the server. The original goal is to have it run in a view but the select alone fails. This is related to MDEV-12840 as that bug was my attempt to rewrite the query in a more sane fashion but the following is technically valid and it may be broader scope.

      create table t1(dt datetime);
      		 
      insert into t1 values ('2017-05-17'), ('2017-05-18');
      		 
       
      		 
      select dt,
      		 
          -> case when (max(dt) over (order by dt rows between 1 following and 1 following) is null)
      		 
          -> then '9999-12-31 12:00:00'
      		 
          -> else max(dt) over (order by dt rows between 1 following and 1 following)
      		 
          -> end x
      		 
          -> from t1;
      		 
      ERROR 2006 (HY000): MySQL server has gone away
      		 
      No connection. Trying to reconnect...
      		 
      Connection id:    8
      		 
      Current database: genesis
      		 
       
      		 
      ERROR 2013 (HY000): Lost connection to MySQL server during query

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-12840 view with ifnull around window function error

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13552 Bug: Server Shutsdown when using LEAD Window Function within a CASE Function

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13683 crash in Item_window_func::update_used_tables

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            dthompson David Thompson (Inactive) created issue -
            dthompson David Thompson (Inactive) made changes -
            Field Original Value New Value
            elenst Elena Stepanova made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            elenst Elena Stepanova added a comment -

            Here is a simpler test case, but please check the original one as well after fixing:

            create table t1(i int);
            		 
            insert into t1 values (1),(2);
            		 
            select max(i) over (order by i) is null from t1;

            10.2 f9069a3dc0a957191587f3809ca6a444ec22c89f

            		 
            #3  <signal handler called>
            		 
            #4  0x00007fb5cf33205d in Item_field::used_tables (this=0x7fb5ac012670) at /data/src/10.2/sql/item.cc:2869
            		 
            #5  0x00007fb5cf500d5f in Item_window_func::update_used_tables (this=0x7fb5ac012838) at /data/src/10.2/sql/item_windowfunc.cc:64
            		 
            #6  0x00007fb5cf36a9b2 in Item_func_isnull::update_used_tables (this=0x7fb5ac012918) at /data/src/10.2/sql/item_cmpfunc.h:1807
            		 
            #7  0x00007fb5cf36a8f9 in Item_func_isnull::fix_length_and_dec (this=0x7fb5ac012918) at /data/src/10.2/sql/item_cmpfunc.h:1776
            		 
            #8  0x00007fb5cf38cf59 in Item_func::fix_fields (this=0x7fb5ac012918, thd=0x7fb5ac000b00, ref=0x7fb5ac0129e0) at /data/src/10.2/sql/item_func.cc:236
            		 
            #9  0x00007fb5cf044946 in setup_fields (thd=0x7fb5ac000b00, ref_pointer_array=..., fields=..., mark_used_columns=MARK_COLUMNS_READ, sum_func_list=0x7fb5ac0134b0, allow_sum_func=true) at /data/src/10.2/sql/sql_base.cc:7053
            		 
            #10 0x00007fb5cf0e9f2c in JOIN::prepare (this=0x7fb5ac013198, tables_init=0x7fb5ac012a88, 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=0x7fb5ac004d18, unit_arg=0x7fb5ac0045e0) at /data/src/10.2/sql/sql_select.cc:806
            		 
            #11 0x00007fb5cf0f41b3 in mysql_select (thd=0x7fb5ac000b00, tables=0x7fb5ac012a88, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fb5ac013178, unit=0x7fb5ac0045e0, select_lex=0x7fb5ac004d18) at /data/src/10.2/sql/sql_select.cc:3644
            		 
            #12 0x00007fb5cf0e8c12 in handle_select (thd=0x7fb5ac000b00, lex=0x7fb5ac004518, result=0x7fb5ac013178, setup_tables_done_option=0) at /data/src/10.2/sql/sql_select.cc:373
            		 
            #13 0x00007fb5cf0b4f1d in execute_sqlcom_select (thd=0x7fb5ac000b00, all_tables=0x7fb5ac012a88) at /data/src/10.2/sql/sql_parse.cc:6431
            		 
            #14 0x00007fb5cf0aaf5c in mysql_execute_command (thd=0x7fb5ac000b00) at /data/src/10.2/sql/sql_parse.cc:3448
            		 
            #15 0x00007fb5cf0b88f0 in mysql_parse (thd=0x7fb5ac000b00, rawbuf=0x7fb5ac012348 "select max(i) over (order by i) is null from t1", length=47, parser_state=0x7fb5c2e27200, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:7874
            		 
            #16 0x00007fb5cf0a69c8 in dispatch_command (command=COM_QUERY, thd=0x7fb5ac000b00, packet=0x7fb5ac0dfd11 "select max(i) over (order by i) is null from t1", packet_length=47, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:1812
            		 
            #17 0x00007fb5cf0a5338 in do_command (thd=0x7fb5ac000b00) at /data/src/10.2/sql/sql_parse.cc:1362
            		 
            #18 0x00007fb5cf1f0085 in do_handle_one_connection (connect=0x7fb5d1842b40) at /data/src/10.2/sql/sql_connect.cc:1354
            		 
            #19 0x00007fb5cf1efe12 in handle_one_connection (arg=0x7fb5d1842b40) at /data/src/10.2/sql/sql_connect.cc:1260
            		 
            #20 0x00007fb5cf609c46 in pfs_spawn_thread (arg=0x7fb5d1788e60) at /data/src/10.2/storage/perfschema/pfs.cc:1862
            		 
            #21 0x00007fb5ce6c6494 in start_thread (arg=0x7fb5c2e28700) at pthread_create.c:333
            		 
            #22 0x00007fb5cca4993f in clone () from /lib/x86_64-linux-gnu/libc.so.6

            elenst Elena Stepanova added a comment - Here is a simpler test case, but please check the original one as well after fixing: create table t1(i int ); insert into t1 values (1),(2); select max (i) over ( order by i) is null from t1; 10.2 f9069a3dc0a957191587f3809ca6a444ec22c89f #3 <signal handler called> #4 0x00007fb5cf33205d in Item_field::used_tables (this=0x7fb5ac012670) at /data/src/10.2/sql/item.cc:2869 #5 0x00007fb5cf500d5f in Item_window_func::update_used_tables (this=0x7fb5ac012838) at /data/src/10.2/sql/item_windowfunc.cc:64 #6 0x00007fb5cf36a9b2 in Item_func_isnull::update_used_tables (this=0x7fb5ac012918) at /data/src/10.2/sql/item_cmpfunc.h:1807 #7 0x00007fb5cf36a8f9 in Item_func_isnull::fix_length_and_dec (this=0x7fb5ac012918) at /data/src/10.2/sql/item_cmpfunc.h:1776 #8 0x00007fb5cf38cf59 in Item_func::fix_fields (this=0x7fb5ac012918, thd=0x7fb5ac000b00, ref=0x7fb5ac0129e0) at /data/src/10.2/sql/item_func.cc:236 #9 0x00007fb5cf044946 in setup_fields (thd=0x7fb5ac000b00, ref_pointer_array=..., fields=..., mark_used_columns=MARK_COLUMNS_READ, sum_func_list=0x7fb5ac0134b0, allow_sum_func=true) at /data/src/10.2/sql/sql_base.cc:7053 #10 0x00007fb5cf0e9f2c in JOIN::prepare (this=0x7fb5ac013198, tables_init=0x7fb5ac012a88, 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=0x7fb5ac004d18, unit_arg=0x7fb5ac0045e0) at /data/src/10.2/sql/sql_select.cc:806 #11 0x00007fb5cf0f41b3 in mysql_select (thd=0x7fb5ac000b00, tables=0x7fb5ac012a88, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fb5ac013178, unit=0x7fb5ac0045e0, select_lex=0x7fb5ac004d18) at /data/src/10.2/sql/sql_select.cc:3644 #12 0x00007fb5cf0e8c12 in handle_select (thd=0x7fb5ac000b00, lex=0x7fb5ac004518, result=0x7fb5ac013178, setup_tables_done_option=0) at /data/src/10.2/sql/sql_select.cc:373 #13 0x00007fb5cf0b4f1d in execute_sqlcom_select (thd=0x7fb5ac000b00, all_tables=0x7fb5ac012a88) at /data/src/10.2/sql/sql_parse.cc:6431 #14 0x00007fb5cf0aaf5c in mysql_execute_command (thd=0x7fb5ac000b00) at /data/src/10.2/sql/sql_parse.cc:3448 #15 0x00007fb5cf0b88f0 in mysql_parse (thd=0x7fb5ac000b00, rawbuf=0x7fb5ac012348 "select max(i) over (order by i) is null from t1", length=47, parser_state=0x7fb5c2e27200, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:7874 #16 0x00007fb5cf0a69c8 in dispatch_command (command=COM_QUERY, thd=0x7fb5ac000b00, packet=0x7fb5ac0dfd11 "select max(i) over (order by i) is null from t1", packet_length=47, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:1812 #17 0x00007fb5cf0a5338 in do_command (thd=0x7fb5ac000b00) at /data/src/10.2/sql/sql_parse.cc:1362 #18 0x00007fb5cf1f0085 in do_handle_one_connection (connect=0x7fb5d1842b40) at /data/src/10.2/sql/sql_connect.cc:1354 #19 0x00007fb5cf1efe12 in handle_one_connection (arg=0x7fb5d1842b40) at /data/src/10.2/sql/sql_connect.cc:1260 #20 0x00007fb5cf609c46 in pfs_spawn_thread (arg=0x7fb5d1788e60) at /data/src/10.2/storage/perfschema/pfs.cc:1862 #21 0x00007fb5ce6c6494 in start_thread (arg=0x7fb5c2e28700) at pthread_create.c:333 #22 0x00007fb5cca4993f in clone () from /lib/x86_64-linux-gnu/libc.so.6
            elenst Elena Stepanova made changes -
            Fix Version/s 10.2 [ 14601 ]
            Assignee Vicentiu Ciorbaru [ cvicentiu ]
            Priority Major [ 3 ] Critical [ 2 ]
            dthompson David Thompson (Inactive) added a comment -

            A possible workaround is to separate the window function in a subquery and apply the expression in the outer query:

            select dt, 
            ifnull(dtnext, '9999-12-31 12:00:00') dtnext 
            from ( 
              select dt, 
              lead(dt) over (order by dt) dtnext from t1
            		 
            ) t;
            		 
            +---------------------+---------------------+
            		 
            | dt                  | dtnext              |
            		 
            +---------------------+---------------------+
            		 
            | 2017-05-17 00:00:00 | 2017-05-18 00:00:00 |
            		 
            | 2017-05-18 00:00:00 | 9999-12-31 12:00:00 |
            		 
            +---------------------+---------------------+

            dthompson David Thompson (Inactive) added a comment - A possible workaround is to separate the window function in a subquery and apply the expression in the outer query: select dt, ifnull(dtnext, '9999-12-31 12:00:00') dtnext from ( select dt, lead(dt) over (order by dt) dtnext from t1 ) t; +---------------------+---------------------+ | dt | dtnext | +---------------------+---------------------+ | 2017-05-17 00:00:00 | 2017-05-18 00:00:00 | | 2017-05-18 00:00:00 | 9999-12-31 12:00:00 | +---------------------+---------------------+
            ratzpo Rasmus Johansson (Inactive) made changes -
            Sprint 10.2.7-1 [ 171 ]
            cvicentiu Vicențiu Ciorbaru made changes -
            Summary case with window functions query crashes server Window functions with is null function in query crashes server
            cvicentiu Vicențiu Ciorbaru made changes -
            Fix Version/s 10.2.7 [ 22543 ]
            Fix Version/s 10.2 [ 14601 ]
            Resolution Fixed [ 1 ]
            Status Confirmed [ 10101 ] Closed [ 6 ]
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 80860 ] MariaDB v4 [ 152182 ]

            People

              cvicentiu Vicențiu Ciorbaru
              dthompson David Thompson (Inactive)
              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.