[MDEV-9935] Window functions: assertion failure with empty OVER() clause Created: 2016-04-17  Updated: 2016-09-24  Resolved: 2016-09-24

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - Window functions
Affects Version/s: 10.2
Fix Version/s: 10.2.2

Type: Bug Priority: Blocker
Reporter: Sergei Petrunia Assignee: Vicențiu Ciorbaru
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Duplicate
duplicates MDEV-10184 Query with ROW_NUMBER() hangs indefin... Closed
PartOf
is part of MDEV-6115 window functions as in the SQL standard Closed
Problem/Incident
causes MDEV-10878 Properly fix empty OVER() clause in w... Open
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);
  };



 Comments   
Comment by Sergei Petrunia [ 2016-04-17 ]

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.

Comment by Elena Stepanova [ 2016-09-02 ]

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

Comment by Elena Stepanova [ 2016-09-21 ]

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

Comment by Vicențiu Ciorbaru [ 2016-09-24 ]

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

The proper fix required is monitored under MDEV-10878

Generated at Thu Feb 08 07:38:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.