feat: support SQL window functions

tarantool> 
with q(x) as (values (1), (2), (3)) 
select x, sum(x) over (order by x rows unbounded following)
from q;
---
- metadata:
  - name: X
    type: integer
  - name: COLUMN_4
    type: decimal
  rows:
  - [1, 1]
  - [2, 3]
  - [3, 6]
...

UPD: проблема решена.

This segfaults:

create table t(id int primary key);

with q(x) as (
  select * from t
)
select x, sum(x) over (rows
                        between unbounded preceding
                        and unbounded following)
from q;

Click to expand

Segmentation fault
  code: SEGV_MAPERR
  addr: (nil)
  context: 0x56004acf3dc0
  siginfo: 0x56004acf3ef0
  rax      0x0                0
  rbx      0x0                0
  rcx      0x56004add1f90     94559255994256
  rdx      0x0                0
  rsi      0x0                0
  rdi      0x0                0
  rsp      0x7a662487fe98     134579118145176
  rbp      0x7a662487ff80     134579118145408
  r8       0x0                0
  r9       0x56004add1590     94559255991696
  r10      0x56004af234f0     94559257375984
  r11      0x8a3c4f6fa7db51ff -8485820256896724481
  r12      0x560018c2f120     94558415417632
  r13      0x5600188878bc     94558411585724
  r14      0x405d9098         1079873688
  r15      0x5600188413b4     94558411297716
  rip      0x7a6625d8e61c     134579140224540
  eflags   0x10283            66179
  cs       0x33               51
  gs       0x0                0
  fs       0x0                0
  cr2      0x0                0
  err      0x4                4
  oldmask  0x0                0
  trapno   0xe                14
Current time: 1738764894
Please file a bug at support@picodata.io
Attempting backtrace... Note: since the server has already crashed, 
this may fail as well
#1  0x560018881ed0 in crash_collect+252
#2  0x560018882904 in crash_signal_cb+96
#3  0x7a6625c4c1d0 in ??+0
#4  0x7a6625d8e61c in ??+0
#5  0x5600187ce769 in flattenSubquery+3291
#6  0x5600187d2a6d in sqlSelect.localalias+1985
#7  0x5600187d2fb4 in sqlSelect.localalias+3336
#8  0x5600187714c7 in yy_reduce+3936
#9  0x560018776963 in sqlParser+289
#10 0x5600187d7027 in sqlRunParser+1353
#11 0x5600187b857d in sql_stmt_compile+492
#12 0x5600187619ad in sql_prepare_and_execute+86
#13 0x5600188419ad in lbox_execute+1529
#14 0x5600188eb617 in lj_BC_FUNCC+70
#15 0x5600188f8fff in lua_pcall+965
#16 0x560018860545 in luaT_call+41
#17 0x560018854889 in lua_main+279
#18 0x560018855474 in run_script_f+3026
#19 0x5600185e31aa in fiber_cxx_invoke(int (*)(__va_list_tag*), __va_list_tag*)+30
#20 0x56001888e9ea in fiber_loop+215
#21 0x560018b78002 in coro_init+116

UPD: решил эту проблему.

-- column BAD does not exist
create table if not exists t(id int primary key);
select id, sum(id) over (rows BAD preceding) from t;

tarantool: ./src/box/sql/expr.c:3712: sqlExprCodeTarget: Assertion `pParse->vdbe_field_ref_reg > 0' failed.

UPD: решил эту проблему.

Еще одна подозрительная штука

create table t1(a int primary key, b text, c text);

insert into t1 values
  (1, 'a', 'one'),
  (2, 'b', 'two'),
  (3, 'c', 'three'),
  (4, 'd', 'one'),
  (5, 'e', 'two'),
  (6, 'f', 'three'),
  (7, 'g', 'one');

SELECT a, b, group_concat(b, '.') OVER (
  ORDER BY a ROWS BETWEEN 1 PRECEDING AND 100000 FOLLOWING
) AS group_concat FROM t1;
---
- null
- 'Reached a limit on max executed vdbe opcodes. Limit: 45000'
...

Почему так, если результат не будет меняться, будь в правой границе хоть 10, хоть 100, хоть 1000?

Вот это почему-то частично распарсилось в процессе экспериментов

SELECT a, b, group_concat(b, '.') OVER window (
  ORDER BY a ROWS BETWEEN 1 preceding and 1 FOLLOWING
) FROM t1;
---
- null
- Can’t resolve field 'A'
...

хотя не должно было

sqlite> SELECT a, b, group_concat(b, '.') OVER window (
  ORDER BY a ROWS BETWEEN 1 preceding and 1 FOLLOWING
) FROM t1;
Parse error: near "(": syntax error
  SELECT a, b, group_concat(b, '.') OVER window (   ORDER BY a ROWS BETWEEN 1 pr
                                  error here ---^

А вот это уже нетривиальный результат:

create table t1(a int primary key, b text, c text);

insert into t1 values
  (1, 'a', 'one'),
  (2, 'b', 'two'),
  (3, 'c', 'three'),
  (4, 'd', 'one'),
  (5, 'e', 'two'),
  (6, 'f', 'three'),
  (7, 'g', 'one');

select avg(a) over w from t1 window w as (rows between 1 preceding and 1 following);

#1  0x63f14b8f5ed0 in crash_collect+252
#2  0x63f14b8f6904 in crash_signal_cb+96
#3  0x7ff019c4c1d0 in ??+0

feat: support SQL window functions

Merge request reports

Activity