[MDEV-9935] Window functions: assertion failure with empty OVER() clause - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Blocker
Resolution: Fixed
Affects Version/s: 10.2(EOL)
Fix Version/s: 10.2.2
Component/s: Optimizer - Window functions
Labels:
None

Sprint:
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

MDEV-10878 Properly fix empty OVER() clause in window functions

Open

duplicates

MDEV-10184 Query with ROW_NUMBER() hangs indefinitely if ORDER or PARTITION not specified

Closed

is part of

MDEV-6115 window functions as in the SQL standard

Closed

Activity

Ascending order - Click to sort in descending order

Sergei Petrunia created issue - 2016-04-17 11:42

Sergei Petrunia made changes - 2016-04-17 11:42

Field	Original Value	New Value
Link		This issue is part of ~~MDEV-6115~~ [ ~~MDEV-6115~~ ]

Sergei Petrunia made changes - 2016-04-17 11:42

Summary

Window functions: assertion failure when neither order_list nor partition expression is present

Window functions: assertion failure when neither order_list nor partition expression are present

Sergei Petrunia added a comment - 2016-04-17 11:44

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.

Sergei Petrunia added a comment - 2016-04-17 11:44 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.

Elena Stepanova made changes - 2016-04-17 20:35

Affects Version/s		10.2 [ 14601 ]
Assignee		Sergei Petrunia [ psergey ]

Elena Stepanova made changes - 2016-05-21 16:14

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:

{noformat}
create table t40 (a int);
select row_number() over () from t40;
{noformat}

{noformat}
  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
{noformat}

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

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:

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

{noformat}
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 ;
{noformat}
{noformat}
{noformat}
  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
{noformat}

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

Elena Stepanova made changes - 2016-05-21 16:14

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:

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

{noformat}
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 ;
{noformat}
{noformat}
{noformat}
  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
{noformat}

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

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:

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

{noformat}
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 ;
{noformat}
{noformat}
  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
{noformat}

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

Elena Stepanova made changes - 2016-06-09 09:49

Link

This issue duplicates ~~MDEV-10184~~ [ ~~MDEV-10184~~ ]

Elena Stepanova made changes - 2016-09-02 08:50

Priority

Major [ 3 ]

Blocker [ 1 ]

Elena Stepanova added a comment - 2016-09-02 08:51

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

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

Rasmus Johansson (Inactive) made changes - 2016-09-14 13:16

Sprint

10.2.2-4 [ 96 ]

Elena Stepanova added a comment - 2016-09-21 17:32

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

Elena Stepanova added a comment - 2016-09-21 17:32 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.)

Vicențiu Ciorbaru made changes - 2016-09-22 12:22

Summary

Window functions: assertion failure when neither order_list nor partition expression are present

Window functions: assertion failure with empty OVER() clause

Vicențiu Ciorbaru made changes - 2016-09-23 14:47

Link

This issue causes MDEV-10878 [ MDEV-10878 ]

Vicențiu Ciorbaru made changes - 2016-09-24 13:29

Assignee

Sergei Petrunia [ psergey ]

Vicentiu Ciorbaru [ cvicentiu ]

Vicențiu Ciorbaru added a comment - 2016-09-24 13:32

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

The proper fix required is monitored under MDEV-10878

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

Vicențiu Ciorbaru made changes - 2016-09-24 13:32

Fix Version/s		10.2.2 [ 22013 ]
Fix Version/s	10.2 [ 14601 ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:42

Workflow

MariaDB v3 [ 74855 ]

MariaDB v4 [ 150337 ]

People

Assignee:: Vicențiu Ciorbaru

Reporter:: Sergei Petrunia

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2016-04-17 11:42

Updated:: 2016-09-24 13:32

Resolved:: 2016-09-24 13:32

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration