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

Window functions: assertion failure with empty OVER() clause

Details

    • 10.2.2-4

    Description

      If a window function is used without specifying either partition or order list (which is valid and makes sense at least with row_number() function), the server fails an assertion:

      create table t40 (a int) engine=InnoDB;
      		 
      select row_number() over () from t40;


      mysqld: /src/10.2/sql/filesort.h:70: Filesort::Filesort(ORDER*, ha_rows, bool, SQL_SELECT*): Assertion `order' failed.
      		 
      160521 19:13:05 [ERROR] mysqld got signal 6 ;


        Program received signal SIGABRT, Aborted.
      		 
        0x00007ffff5f90425 in __GI_raise (sig=<optimized out>) at ../nptl/sysdeps/unix/sysv/linux/raise.c:64
      		 
        64	../nptl/sysdeps/unix/sysv/linux/raise.c: No such file or directory.
      		 
      (gdb) wher
      		 
        #0  0x00007ffff5f90425 in __GI_raise (sig=<optimized out>) at ../nptl/sysdeps/unix/sysv/linux/raise.c:64
      		 
        #1  0x00007ffff5f93b8b in __GI_abort () at abort.c:91
      		 
        #2  0x00007ffff5f890ee in __assert_fail_base (fmt=<optimized out>, assertion=0x5555563e45cb "order", file=0x5555563e4590 "/home/psergey/dev-git/10.2-window-funcs-r10/sql/filesort.h", line=<optimized out>, function=<optimized out>) at assert.c:94
      		 
        #3  0x00007ffff5f89192 in __GI___assert_fail (assertion=0x5555563e45cb "order", file=0x5555563e4590 "/home/psergey/dev-git/10.2-window-funcs-r10/sql/filesort.h", line=70, function=0x5555563e8940 "Filesort::Filesort(ORDER*, ha_rows, bool, SQL_SELECT*)") at assert.c:103
      		 
        #4  0x0000555555b01c16 in Filesort::Filesort (this=0x7fff5c013560, order_arg=0x0, limit_arg=18446744073709551615, sort_positions_arg=true, select_arg=0x0) at /home/psergey/dev-git/10.2-window-funcs-r10/sql/filesort.h:70
      		 
        #5  0x0000555555c2bcd8 in Window_funcs_sort::setup (this=0x7fff5c013518, thd=0x7fff5c000b00, sel=0x0, it=...) at /home/psergey/dev-git/10.2-window-funcs-r10/sql/sql_window.cc:1922
      		 
        #6  0x0000555555c2be0f in Window_funcs_computation::setup (this=0x7fff5c0134f8, thd=0x7fff5c000b00, window_funcs=0x7fff5c004fc8, tab=0x7fff5c0128a0) at /home/psergey/dev-git/10.2-window-funcs-r10/sql/sql_window.cc:1949
      		 
        #7  0x0000555555ac5573 in JOIN::make_aggr_tables_info (this=0x7fff5c011508) at /home/psergey/dev-git/10.2-window-funcs-r10/sql/sql_select.cc:2670
      		 
        #8  0x0000555555ac30b6 in JOIN::optimize_inner (this=0x7fff5c011508) at /home/psergey/dev-git/10.2-window-funcs-r10/sql/sql_select.cc:2068
      		 
        #9  0x0000555555abfc2c in JOIN::optimize (this=0x7fff5c011508) at /home/psergey/dev-git/10.2-window-funcs-r10/sql/sql_select.cc:1063
      		 
        #10 0x0000555555ac7ed0 in mysql_select (thd=0x7fff5c000b00, tables=0x7fff5c010e10, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fff5c0114e8, unit=0x7fff5c004550, select_lex=0x7fff5c004c70) at /home/psergey/dev-git/10.2-window-funcs-r10/sql/sql_select.cc:3521

      The assertion fails here:

        Filesort(ORDER *order_arg, ha_rows limit_arg, bool sort_positions_arg,
      		 
                 SQL_SELECT *select_arg):
      		 
          order(order_arg),
      		 
      ...
      		 
        {
      		 
          DBUG_ASSERT(order);
      		 
        };

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-10878 Properly fix empty OVER() clause in window functions

          • Major - Major loss of function.
          • Open
          duplicates

          Bug - A problem which impairs or prevents the functions of the product. MDEV-10184 Query with ROW_NUMBER() hangs indefinitely if ORDER or PARTITION not specified

          • Major - Major loss of function.
          • Closed
          is part of

          Task - A task that needs to be done. MDEV-6115 window functions as in the SQL standard

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            psergei Sergei Petrunia added a comment -

            Indeed, filesort cannot use an empty criteria. We should support the case where window functions code doesn't need to do sorting.

            It's possible to work around the assert by just putting in a dummy sort expression, but that's not the right way to do it.

            psergei Sergei Petrunia added a comment - Indeed, filesort cannot use an empty criteria. We should support the case where window functions code doesn't need to do sorting. It's possible to work around the assert by just putting in a dummy sort expression, but that's not the right way to do it.
            elenst Elena Stepanova added a comment -

            Reminder: the release build is also affected, in an even worse way, because there the server hangs.

            elenst Elena Stepanova added a comment - Reminder: the release build is also affected, in an even worse way, because there the server hangs.
            elenst Elena Stepanova added a comment -

            Another reminder: it happens with aggregate functions also, e.g. 

            SELECT salary, sum(salary) OVER () FROM empsalary;

            It can't be released like that, it's one of first things people try (because it's one of first queries which appears in various documentation, examples, etc.)

            elenst Elena Stepanova added a comment - Another reminder: it happens with aggregate functions also, e.g. SELECT salary, sum (salary) OVER () FROM empsalary; It can't be released like that, it's one of first things people try (because it's one of first queries which appears in various documentation, examples, etc.)
            cvicentiu Vicențiu Ciorbaru added a comment -

            We have a temporary fix for it with
            https://github.com/MariaDB/server/commit/3dd3a5da0e9fbd8a24a178b2b295b5a385beba5e

            The proper fix required is monitored under MDEV-10878

            cvicentiu Vicențiu Ciorbaru added a comment - We have a temporary fix for it with https://github.com/MariaDB/server/commit/3dd3a5da0e9fbd8a24a178b2b295b5a385beba5e The proper fix required is monitored under MDEV-10878

            People

              cvicentiu Vicențiu Ciorbaru
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.