[MDEV-32415] Nested window funcs? SEGV at /mariadb-11.3.0/sql/item_func.h:771 Created: 2023-10-10  Updated: 2023-12-05

Status: Confirmed
Project: MariaDB Server
Component/s: Optimizer - Window functions, Server
Affects Version/s: 10.4, 10.5, 10.6, 10.9, 10.10, 10.11, 11.0, 11.1, 11.2, 11.3.0
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2

Type: Bug Priority: Major
Reporter: Xin Wen Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Ubuntu 20.04

Issue Links:
Duplicate
is duplicated by MDEV-32413 Segmentation fault at /mariadb-11.3.0... Closed

 Description   

Run these queries in release build:

CREATE TABLE t0 ( c30 DECIMAL ( 13 ) NOT NULL ) ;
 
INSERT INTO t0 VALUES ( 40 ) , ( -88 ) ;
 
CREATE UNIQUE INDEX i0 ON t0 ( c30 ) ;
 
INSERT INTO t0 VALUES ( -104 ) , ( 108 ) ;
 
( SELECT c30 NOT IN ( DEGREES ( 94 < -30 AND 76 > -6 ) IS NOT NULL = SUM( t0 . c30 IN ( SELECT t0 . c30 AS c43 FROM t0 ) ) - AVG ( c30 ) OVER ( PARTITION BY t0 . c30 , LTRIM ( RTRIM ( t0 . c30 ) / EXP ( RAND ( t0 . c30 NOT IN ( VAR_SAMP( 110 NOT REGEXP 51.925892 | NULLIF ( t0 . c30 , 74 ) ) OVER ( PARTITION BY t0 . c30 ) , -12 , -95 ) ) + EXISTS ( SELECT t2 . c30 AS c61 FROM t0 JOIN t0 AS t1 LEFT OUTER JOIN t0 AS t2 ON TRUE ON t2 . c30 = t1 . c30 ) ) NOT LIKE REPLACE ( -12 , CONCAT ( t0 . c30 , 'wIqh36p$XE]6W3l/0A~RXY&r#NQ-ht(7H=1]4_1ITo' ) ^ COS ( MOD ( 123 , -75 ) SOUNDS LIKE IFNULL ( -40 , SUBSTRING( t0 . c30 , '1D,>N{I\\;M|W).)J>0WW^22,w@=Xd4%\'Y#C<(rgm[Q yETmw{05%jQH}<3-F^' ) >= 2883848735260353512 ) ) , '|z' ) ) ) , 119 , ACOS ( -47 ) | + + FLOOR ( 74 ) / EXP ( 105 ) NOT LIKE - ROUND ( -3120512921002175577 , -47.901984 ) ) AS c19 FROM t0 GROUP BY c30 , c30 ) ;

Will trigger Segmentation fault.
GDB info:

Thread 16 "mariadbd" received signal SIGSEGV, Segmentation fault.
 
[Switching to Thread 0x7fffd242e300 (LWP 3479)]
 
0x00000000013fdab4 in Item_handled_func::val_str (this=0x6290000be500, to=0x7fffd2429d60) at /home/wx/mariadb-11.3.0/sql/item_func.h:771
 
771	    return m_func_handler->val_str(this, to);
 
(gdb) p m_func_handler
 
$42 = (const Item_handled_func::Handler *) 0x0
 
 
 
#0  0x00000000013fdab4 in Item_handled_func::val_str (this=0x6290000c3590, to=0x7fffd2429d60) at /home/wx/mariadb-11.3.0/sql/item_func.h:771
 
#1  0x00000000013e4148 in Regexp_processor_pcre::compile (this=<optimized out>, item=<optimized out>, send_error=<optimized out>) at /home/wx/mariadb-11.3.0/sql/item_cmpfunc.cc:6083
 
#2  0x00000000013e5a65 in Regexp_processor_pcre::recompile (this=<optimized out>, item=0x6290000c3590) at /home/wx/mariadb-11.3.0/sql/item_cmpfunc.h:3024
 
#3  Item_func_regex::val_int (this=0x6290000c3650) at /home/wx/mariadb-11.3.0/sql/item_cmpfunc.cc:6218
 
#4  0x00000000010d6cd1 in Type_handler_int_result::Item_val_bool (this=<optimized out>, item=0x0) at /home/wx/mariadb-11.3.0/sql/sql_type.cc:5082
 
#5  0x00000000013a5961 in Item_func_not::val_int (this=0x6290000c37d0) at /home/wx/mariadb-11.3.0/sql/item_cmpfunc.cc:203
 
#6  0x000000000145d518 in Item_int_func::val_real (this=0x0) at /home/wx/mariadb-11.3.0/sql/item_func.cc:753
 
#7  0x00000000015fed26 in Item_sum_variance::add (this=0x6290000c3890) at /home/wx/mariadb-11.3.0/sql/item_sum.cc:2295
 
#8  0x0000000001138a9c in Frame_cursor::add_value_to_items (this=<optimized out>) at /home/wx/mariadb-11.3.0/sql/sql_window.cc:1166
 
#9  Frame_scan_cursor::compute_values_for_current_row (this=<optimized out>) at /home/wx/mariadb-11.3.0/sql/sql_window.cc:2273
 
#10 0x0000000001127500 in Cursor_manager::notify_cursors_partition_changed (this=0x603000107230, rownum=0) at /home/wx/mariadb-11.3.0/sql/sql_window.cc:1229
 
#11 compute_window_func (thd=<optimized out>, window_functions=..., cursor_managers=..., tbl=<optimized out>, filesort_result=<optimized out>) at /home/wx/mariadb-11.3.0/sql/sql_window.cc:2907
 
#12 0x00000000011286a4 in Window_func_runner::exec (this=<optimized out>, thd=<optimized out>, tbl=<optimized out>, filesort_result=<optimized out>) at /home/wx/mariadb-11.3.0/sql/sql_window.cc:3039
 
#13 0x0000000001128aca in Window_funcs_sort::exec (this=<optimized out>, join=join@entry=0x6290000ce9d0, keep_filesort_result=false) at /home/wx/mariadb-11.3.0/sql/sql_window.cc:3067
 
#14 0x000000000112ad0d in Window_funcs_computation::exec (this=<optimized out>, join=0x6290000ce9d0, keep_last_filesort_result=true) at /home/wx/mariadb-11.3.0/sql/sql_window.cc:3196
 
#15 0x0000000000c9d259 in AGGR_OP::end_send (this=<optimized out>, this@entry=0x62d0000d3fe8) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:32300
 
#16 0x0000000000c388f1 in sub_select_postjoin_aggr (join=0x6290000ce9d0, join_tab=0x6290000d3898, end_of_records=false) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:23128
 
#17 0x0000000000c4536c in do_select (join=0x6290000ce9d0, procedure=<optimized out>) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:22963
 
#18 JOIN::exec_inner (this=0x6290000ce9d0) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:4941
 
#19 0x0000000000c428e9 in JOIN::exec (this=this@entry=0x6290000ce9d0) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:4718
 
#20 0x0000000000be5128 in mysql_select (thd=<optimized out>, thd@entry=0x62b00016c218, tables=<optimized out>, fields=..., conds=<optimized out>, og_num=<optimized out>, order=<optimized out>, group=0x629000094a90, having=0x0, proc_param=0x0, select_options=<optimized out>, result=0x6290000ce9a0, unit=0x62b0001704a8, select_lex=0x629000091980) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:5249
 
#21 0x0000000000be4596 in handle_select (thd=thd@entry=0x62b00016c218, lex=<optimized out>, lex@entry=0x62b0001703c8, result=<optimized out>, result@entry=0x6290000ce9a0, setup_tables_done_option=<optimized out>, setup_tables_done_option@entry=0) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:628
 
#22 0x0000000000b3df18 in execute_sqlcom_select (thd=0x62b00016c218, all_tables=<optimized out>) at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:6013
 
#23 0x0000000000b2cd51 in mysql_execute_command (thd=0x62b00016c218, is_called_from_prepared_stmt=<optimized out>) at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:3912
 
#24 0x0000000000b1fe79 in mysql_parse (thd=thd@entry=0x62b00016c218, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>, parser_state@entry=0x7fffd242ca80) at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:7734
 
#25 0x0000000000b19069 in dispatch_command (command=<optimized out>, thd=0x62b00016c218, packet=<optimized out>, packet_length=<optimized out>, blocking=<optimized out>) at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:1893
 
#26 0x0000000000b20b71 in do_command (thd=0x62b00016c218, blocking=true) at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:1406
 
#27 0x0000000000f03476 in do_handle_one_connection (connect=<optimized out>, put_in_cache=<optimized out>) at /home/wx/mariadb-11.3.0/sql/sql_connect.cc:1445
 
#28 0x0000000000f02eb9 in handle_one_connection (arg=arg@entry=0x608001e1f1b8) at /home/wx/mariadb-11.3.0/sql/sql_connect.cc:1347
 
#29 0x0000000001a00c1b in pfs_spawn_thread (arg=0x617000005118) at /home/wx/mariadb-11.3.0/storage/perfschema/pfs.cc:2201
 
#30 0x00007ffff79f7609 in start_thread () from /lib/x86_64-linux-gnu/libpthread.so.0
 
#31 0x00007ffff770f133 in clone () from /lib/x86_64-linux-gnu/libc.so.6



 Comments   
Comment by Alice Sherepa [ 2023-10-23 ]

Thank you for the report!
I repeated on 10.4-11.2.
!:please check the initial test case after the fix

CREATE TABLE t0 ( i int) ;
 
INSERT INTO t0 VALUES ( 40 ) , ( -88 ) , ( -104 ) , ( 108 ) ;
 
 
 
SELECT AVG(i) OVER ( PARTITION BY i,  i IN ( var_samp(i) OVER () )) FROM t0 GROUP BY i ;


mysqld: /10.4/src/sql/item.cc:3255: virtual double Item_field::val_real(): Assertion `fixed == 1' failed.
 
231023 15:11:39 [ERROR] mysqld got signal 6 ;
 
 
 
Server version: 10.4.32-MariaDB-debug-log source revision: babd833685e1fd1da4411a0874ba1c98bb0b631d
 
 
 
sql/item.cc:3256(Item_field::val_real())[0x55a4af46c073]
 
sql/item_sum.cc:2271(Item_sum_variance::add())[0x55a4af689109]
 
sql/sql_window.cc:1164(Frame_cursor::add_value_to_items())[0x55a4af207cbb]
 
sql/sql_window.cc:2274(Frame_scan_cursor::compute_values_for_current_row())[0x55a4af20c5e2]
 
sql/sql_window.cc:2231(Frame_scan_cursor::next_partition(unsigned long long))[0x55a4af20c246]
 
sql/sql_window.cc:1228(Cursor_manager::notify_cursors_partition_changed(unsigned long long))[0x55a4af2084b0]
 
sql/sql_window.cc:2907(compute_window_func(THD*, List<Item_window_func>&, List<Cursor_manager>&, TABLE*, SORT_INFO*))[0x55a4af2033bc]
 
sql/sql_window.cc:3030(Window_func_runner::exec(THD*, TABLE*, SORT_INFO*))[0x55a4af203b78]
 
sql/sql_window.cc:3058(Window_funcs_sort::exec(JOIN*, bool))[0x55a4af203dc6]
 
sql/sql_window.cc:3185(Window_funcs_computation::exec(JOIN*, bool))[0x55a4af204bce]
 
sql/sql_select.cc:29661(AGGR_OP::end_send())[0x55a4aedcd5ea]
 
sql/sql_select.cc:20601(sub_select_postjoin_aggr(JOIN*, st_join_table*, bool))[0x55a4aed8a03b]
 
sql/sql_select.cc:20847(sub_select(JOIN*, st_join_table*, bool))[0x55a4aed8ab36]
 
sql/sql_select.cc:20425(do_select(JOIN*, Procedure*))[0x55a4aed89236]
 
sql/sql_select.cc:4605(JOIN::exec_inner())[0x55a4aed16c78]
 
sql/sql_select.cc:4388(JOIN::exec())[0x55a4aed142a8]
 
sql/sql_select.cc:4828(mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x55a4aed18484]
 
sql/sql_select.cc:442(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55a4aece8f7c]
 
sql/sql_parse.cc:6475(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55a4aec54d80]
 
sql/sql_parse.cc:3978(mysql_execute_command(THD*))[0x55a4aec424f7]
 
sql/sql_parse.cc:8012(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55a4aec5e25b]
 
sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55a4aec34681]
 
sql/sql_parse.cc:1378(do_command(THD*))[0x55a4aec311ac]
 
sql/sql_connect.cc:1420(do_handle_one_connection(CONNECT*))[0x55a4af03f56d]
 
sql/sql_connect.cc:1325(handle_one_connection)[0x55a4af03ee11]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55a4afce9d8a]
 
nptl/pthread_create.c:478(start_thread)[0x7f066e4f7609]
 
 
 
Query (0x62b0000a1290): SELECT AVG(i) OVER ( PARTITION BY i,  i IN ( var_samp(i) OVER () )) FROM t0 GROUP BY i

with other window functions:

10.4/src/sql/item.cc:3255: virtual double Item_field::val_real(): Assertion `fixed == 1' failed.
 
sql/item.cc:3256(Item_field::val_real())[0x55d5571b8073]
 
sql/item_sum.cc:1814(Aggregator_simple::arg_val_real())[0x55d5573cf2b1]
 
sql/item_sum.cc:1675(Item_sum_sum::add_helper(bool))[0x55d5573cdc91]
 
sql/item_sum.cc:1603(Item_sum_sum::add())[0x55d5573cce1f]
 
sql/sql_window.cc:1164(Frame_cursor::add_value_to_items())[0x55d556f53cbb]
 
sql/sql_window.cc:1577(Frame_range_current_row_bottom::pre_next_partition(unsigned long long))[0x55d556f563ef]
 
sql/sql_window.cc:1224(Cursor_manager::notify_cursors_partition_changed(unsigned long long))[0x55d556f5440d]
 
sql/sql_window.cc:2907(compute_window_func(THD*, List<Item_window_func>&, List<Cursor_manager>&, TABLE*, SORT_INFO*))[0x55d556f4f3bc]
 
sql/sql_window.cc:3030(Window_func_runner::exec(THD*, TABLE*, SORT_INFO*))[0x55d556f4fb78]
 
sql/sql_window.cc:3058(Window_funcs_sort::exec(JOIN*, bool))[0x55d556f4fdc6]
 
sql/sql_window.cc:3185(Window_funcs_computation::exec(JOIN*, bool))[0x55d556f50bce]
 
sql/sql_select.cc:29661(AGGR_OP::end_send())[0x55d556b195ea]
 
sql/sql_select.cc:20601(sub_select_postjoin_aggr(JOIN*, st_join_table*, bool))[0x55d556ad603b]
 
sql/sql_select.cc:20847(sub_select(JOIN*, st_join_table*, bool))[0x55d556ad6b36]
 
sql/sql_select.cc:20425(do_select(JOIN*, Procedure*))[0x55d556ad5236]
 
sql/sql_select.cc:4605(JOIN::exec_inner())[0x55d556a62c78]
 
sql/sql_select.cc:4388(JOIN::exec())[0x55d556a602a8]
 
sql/sql_select.cc:4828(mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x55d556a64484]
 
sql/sql_select.cc:442(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55d556a34f7c]
 
sql/sql_parse.cc:6475(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55d5569a0d80]
 
sql/sql_parse.cc:3978(mysql_execute_command(THD*))[0x55d55698e4f7]
 
sql/sql_parse.cc:8012(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55d5569aa25b]
 
sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55d556980681]
 
sql/sql_parse.cc:1378(do_command(THD*))[0x55d55697d1ac]
 
sql/sql_connect.cc:1420(do_handle_one_connection(CONNECT*))[0x55d556d8b56d]
 
sql/sql_connect.cc:1325(handle_one_connection)[0x55d556d8ae11]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55d557a35d8a]
 
nptl/pthread_create.c:478(start_thread)[0x7f16e7f17609]


Version: '10.4.32-MariaDB-debug-log'  
231023 15:16:02 [ERROR] mysqld got signal 11 ;
 
 
 
sigaction.c:0(__restore_rt)[0x7f49a4d08420]
 
sql/field.h:1211(Field::set_notnull(long long))[0x555622b57243]
 
sql/item.cc:6720(Item::save_int_in_field(Field*, bool))[0x555623640773]
 
sql/sql_type.cc:3847(Type_handler_int_result::Item_save_in_field(Item*, Field*, bool) const)[0x555623376d9c]
 
sql/item.cc:6726(Item::save_in_field(Field*, bool))[0x5556236408e7]
 
sql/sql_window.cc:2775(save_window_function_values(List<Item_window_func>&, TABLE*, unsigned char*))[0x5556233bba2c]
 
sql/sql_window.cc:2929(compute_window_func(THD*, List<Item_window_func>&, List<Cursor_manager>&, TABLE*, SORT_INFO*))[0x5556233bc4fc]
 
sql/sql_window.cc:3030(Window_func_runner::exec(THD*, TABLE*, SORT_INFO*))[0x5556233bcb78]
 
sql/sql_window.cc:3058(Window_funcs_sort::exec(JOIN*, bool))[0x5556233bcdc6]
 
sql/sql_window.cc:3185(Window_funcs_computation::exec(JOIN*, bool))[0x5556233bdbce]
 
sql/sql_select.cc:29661(AGGR_OP::end_send())[0x555622f865ea]
 
sql/sql_select.cc:20601(sub_select_postjoin_aggr(JOIN*, st_join_table*, bool))[0x555622f4303b]
 
sql/sql_select.cc:20847(sub_select(JOIN*, st_join_table*, bool))[0x555622f43b36]
 
sql/sql_select.cc:20425(do_select(JOIN*, Procedure*))[0x555622f42236]
 
sql/sql_select.cc:4605(JOIN::exec_inner())[0x555622ecfc78]
 
sql/sql_select.cc:4388(JOIN::exec())[0x555622ecd2a8]
 
sql/sql_select.cc:4828(mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x555622ed1484]
 
sql/sql_select.cc:442(handle_select(THD*, LEX*, select_result*, unsigned long))[0x555622ea1f7c]
 
sql/sql_parse.cc:6475(execute_sqlcom_select(THD*, TABLE_LIST*))[0x555622e0dd80]
 
sql/sql_parse.cc:3978(mysql_execute_command(THD*))[0x555622dfb4f7]
 
sql/sql_parse.cc:8012(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x555622e1725b]
 
sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x555622ded681]
 
sql/sql_parse.cc:1378(do_command(THD*))[0x555622dea1ac]
 
sql/sql_connect.cc:1420(do_handle_one_connection(CONNECT*))[0x5556231f856d]
 
sql/sql_connect.cc:1325(handle_one_connection)[0x5556231f7e11]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x555623ea2d8a]
 
nptl/pthread_create.c:478(start_thread)[0x7f49a4cfc609]
 
 
 
Query (0x62b0000a1290): SELECT AVG(i) OVER ( PARTITION BY i,  i IN ( row_number() OVER () )) FROM t0 GROUP BY i

On non-debug:

Version: '10.11.5-MariaDB'  
231023 15:18:16 [ERROR] mysqld got signal 11 ;
 
 
 
Server version: 10.11.5-MariaDB source revision: 7875294b6b74b53dd3aaa723e6cc103d2bb47b2c
 
 
 
mysys/stacktrace.c:216(my_print_stacktrace)[0x555a716a0b6e]
 
sql/signal_handler.cc:241(handle_fatal_signal)[0x555a7107e4c7]
 
sigaction.c:0(__restore_rt)[0x7f9080c45420]
 
sql/field.h:1392(Field::is_null(long long) const)[0x555a710919cb]
 
sql/item_sum.cc:2297(Item_sum_variance::add())[0x555a7116282c]
 
sql/sql_list.h:441(base_list_iterator::next_fast())[0x555a70fe6751]
 
sql/sql_list.h:441(base_list_iterator::next_fast())[0x555a70fe2421]
 
sql/sql_list.h:441(base_list_iterator::next_fast())[0x555a70fe3cb9]
 
sql/sql_window.cc:3059(Window_funcs_sort::exec(JOIN*, bool))[0x555a70fe3dee]
 
sql/sql_window.cc:3186(Window_funcs_computation::exec(JOIN*, bool))[0x555a70fe3e6a]
 
sql/sql_select.cc:30992(AGGR_OP::end_send())[0x555a70e8e7ac]
 
sql/sql_select.cc:21835(sub_select_postjoin_aggr(JOIN*, st_join_table*, bool))[0x555a70e8e9b1]
 
sql/sql_select.cc:21671(JOIN::exec_inner())[0x555a70eac162]
 
sql/sql_select.cc:4633(JOIN::exec())[0x555a70eac5a3]
 
sql/sql_select.cc:5114(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x555a70eaa7ee]
 
sql/sql_select.cc:598(handle_select(THD*, LEX*, select_result*, unsigned long long))[0x555a70eab054]
 
sql/sql_parse.cc:6290(execute_sqlcom_select(THD*, TABLE_LIST*))[0x555a70cd19e6]
 
sql/sql_parse.cc:3959(mysql_execute_command(THD*, bool))[0x555a70e3b9d0]
 
sql/sql_parse.cc:8035(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x555a70e3dd8b]
 
sql/sql_parse.cc:1953(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x555a70e401c8]
 
sql/sql_parse.cc:1409(do_command(THD*, bool))[0x555a70e416f3]
 
sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x555a70f4d5e7]
 
sql/sql_connect.cc:1324(handle_one_connection)[0x555a70f4d884]
 
perfschema/pfs.cc:2204(pfs_spawn_thread)[0x555a712d0d6c]
 
nptl/pthread_create.c:478(start_thread)[0x7f9080c39609]
 
 
 
Query (0x7f8ff4010c20): SELECT AVG(i) OVER ( PARTITION BY i,  i IN ( var_samp(i) OVER () )) FROM t0 GROUP BY i

Comment by Sergei Petrunia [ 2023-11-09 ]

Looking at Alice's testcase:

CREATE TABLE t0 ( i int) ;
 
INSERT INTO t0 VALUES ( 40 ) , ( -88 ) , ( -104 ) , ( 108 ) ;
 
 
SELECT 
  AVG(i) OVER ( PARTITION BY i,  i IN ( var_samp(i) OVER () )) 
FROM t0 
GROUP BY i ;

So it's 

window_func_1()  OVER (PARTITION BY .... window_func_2() over ...)

does the standard allow one to use "nested" window functions?

Comment by Sergei Petrunia [ 2023-11-09 ]

jasoncu, could you check the above? If this is about not allowing nested window functions, then we should pass it over to the Runtime team.

Comment by Jason (Inactive) [ 2023-11-21 ]

Regarding the above question:

does the standard allow one to use "nested" window functions?

It seems the SQL Standard does not allow it. The answer is in the grammar. The SQL Standard's <window partition clause> appears to be a simple column list. I interpret this to mean that while MariaDB and other DBMSes allow a more general expression in the window partition clause, this is an extension to the standard, which is okay as any DBMS is free to extend the standard as needed, but it means that the standard will not tell us what to do with it.

This is the relevant grammar from the Foundations:

<window partition clause> ::=
 
  PARTITION BY <window partition column reference list>
 
 
 
<window partition column reference list> ::=
 
  <window partition column reference>
 
      [ { <comma> <window partition column reference> }... ]
 
 
 
<window partition column reference> ::=
 
  <column reference> [ <collate clause> ]
 
 
 
...
 
 
 
<column reference> ::=
 
    <basic identifier chain>
 
  | MODULE <period> <qualified identifier> <period> <column name>

Maybe also relevant is that MySQL also extends the standard to allow expressions in the window partition clause, but (at least according to their documentation, I did not test it myself) they do not allow nested window functions: https://dev.mysql.com/doc/refman/8.0/en/window-function-restrictions.html

MySQL does not support these window function features:
 
 
 
- DISTINCT syntax for aggregate window functions.
 
 
 
- Nested window functions.
 
 
 
- Dynamic frame endpoints that depend on the value of the current row.

Comment by Sergei Petrunia [ 2023-11-29 ]

Ok, so the problem seems to be in the parser. It allows nested window functions, while it shouldn't. Passing over to the Runtime team.

Generated at Thu Feb 08 10:31:11 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.