Roel Van de Paar added a comment - 2020-07-14 10:01 - edited Original testcase still crashes latest 10.5.5 (optimized+debug) 10.5.5 30e7a0a866dce530d8328c6d614e48d39a264f9b (Optimized) Core was generated by `/test/MD140720-mariadb-10.5.5-linux-x86_64-opt/bin/mysqld --no-defaults --core-'. Program terminated with signal SIGSEGV, Segmentation fault. #0 __pthread_kill (threadid=<optimized out>, signo=signo@entry=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:57 [Current thread is 1 (Thread 0x14d66bab7700 (LWP 1255655))] (gdb) bt #0 __pthread_kill (threadid=<optimized out>, signo=signo@entry=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:57 #1 0x00005632ce06bbd7 in my_write_core (sig=sig@entry=11) at /test/10.5_opt/mysys/stacktrace.c:518 #2 0x00005632cda3601a in handle_fatal_signal (sig=11) at /test/10.5_opt/sql/signal_handler.cc:330 #3 <signal handler called> #4 0x00005632cda3368c in sortlength (allow_packing_for_sortkeys=<synthetic pointer>, sort_keys=0x14d6478d2da0, thd=0x14d647812018) at /test/10.5_opt/sql/filesort.cc:2237 #5 filesort (thd=thd@entry=0x14d647812018, table=table@entry=0x14d64794dc30, filesort=filesort@entry=0x14d6478d20c8, tracker=0x14d6478d2350, join=join@entry=0x14d647849bb8, first_table_bit=<optimized out>) at /test/10.5_opt/sql/filesort.cc:250 #6 0x00005632cd889e55 in create_sort_index (thd=thd@entry=0x14d647812018, join=join@entry=0x14d647849bb8, tab=tab@entry=0x14d64784c858, fsort=0x14d6478d20c8) at /test/10.5_opt/sql/sql_select.cc:23875 #7 0x00005632cd9a7187 in Window_funcs_sort::exec (this=0x14d6478d1fc8, join=join@entry=0x14d647849bb8, keep_filesort_result=keep_filesort_result@entry=false) at /test/10.5_opt/sql/sql_window.cc:2998 #8 0x00005632cd9a7a7a in Window_funcs_computation::exec (this=<optimized out>, join=join@entry=0x14d647849bb8, keep_last_filesort_result=<optimized out>) at /test/10.5_opt/sql/sql_window.cc:3131 #9 0x00005632cd895ce3 in AGGR_OP::end_send (this=0x14d64784cfd8) at /test/10.5_opt/sql/sql_select.cc:28896 #10 0x00005632cd895ece in sub_select_postjoin_aggr (join=0x14d647849bb8, join_tab=0x14d64784c858, end_of_records=<optimized out>) at /test/10.5_opt/sql/sql_select.cc:20351 #11 0x00005632cd89d89d in do_select (procedure=<optimized out>, join=0x14d647849bb8) at /test/10.5_opt/sql/sql_select.cc:20177 #12 JOIN::exec_inner (this=this@entry=0x14d647849bb8) at /test/10.5_opt/sql/sql_select.cc:4450 #13 0x00005632cd89dca7 in JOIN::exec (this=this@entry=0x14d647849bb8) at /test/10.5_opt/sql/sql_select.cc:4231 #14 0x00005632cd89bfd2 in mysql_select (thd=thd@entry=0x14d647812018, tables=0x14d647847e08, fields=@0x14d6478472b0: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x14d647847718, last = 0x14d647847d98, elements = 2}, <No data fields>}, conds=0x14d647848eb0, og_num=<optimized out>, order=0x0, group=0x14d647849240, having=0x0, proc_param=0x0, select_options=2147748608, result=0x14d647849b90, unit=0x14d647815e70, select_lex=0x14d647847160) at /test/10.5_opt/sql/sql_select.cc:4655 #15 0x00005632cd89c991 in handle_select (thd=thd@entry=0x14d647812018, lex=lex@entry=0x14d647815da8, result=result@entry=0x14d647849b90, setup_tables_done_option=setup_tables_done_option@entry=0) at /test/10.5_opt/sql/sql_select.cc:417 #16 0x00005632cd843351 in execute_sqlcom_select (thd=thd@entry=0x14d647812018, all_tables=0x14d647847e08) at /test/10.5_opt/sql/sql_parse.cc:6209 #17 0x00005632cd83fa21 in mysql_execute_command (thd=thd@entry=0x14d647812018) at /test/10.5_opt/sql/sql_parse.cc:3931 #18 0x00005632cd84646c in mysql_parse (thd=0x14d647812018, rawbuf=<optimized out>, length=98, parser_state=0x14d66bab6430, is_com_multi=<optimized out>, is_next_command=<optimized out>) at /test/10.5_opt/sql/sql_parse.cc:7993 #19 0x00005632cd83b755 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x14d647812018, packet=packet@entry=0x14d64783a019 "", packet_length=packet_length@entry=98, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /test/10.5_opt/sql/sql_parse.cc:1866 #20 0x00005632cd839a94 in do_command (thd=0x14d647812018) at /test/10.5_opt/sql/sql_parse.cc:1347 #21 0x00005632cd92f191 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x14d6688338f8, put_in_cache=put_in_cache@entry=true) at /test/10.5_opt/sql/sql_connect.cc:1411 #22 0x00005632cd92f4f4 in handle_one_connection (arg=arg@entry=0x14d6688338f8) at /test/10.5_opt/sql/sql_connect.cc:1313 #23 0x00005632cdc9fdea in pfs_spawn_thread (arg=0x14d66884f218) at /test/10.5_opt/storage/perfschema/pfs.cc:2201 #24 0x000014d66aa306db in start_thread (arg=0x14d66bab7700) at pthread_create.c:463 #25 0x000014d669e2ea3f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95 Debug: 10.5.5 30e7a0a866dce530d8328c6d614e48d39a264f9b (Debug) mysqld: /test/10.5_dbg/sql/filesort.cc:604: Sort_keys* Filesort::make_sortorder(THD*, JOIN*, table_map): Assertion `pos->field != __null || pos->item != __null' failed. 10.5.5 30e7a0a866dce530d8328c6d614e48d39a264f9b (Debug) Core was generated by `/test/MD140720-mariadb-10.5.5-linux-x86_64-dbg/bin/mysqld --no-defaults --core-'. Program terminated with signal SIGABRT, Aborted. #0 __pthread_kill (threadid=<optimized out>, signo=signo@entry=6) at ../sysdeps/unix/sysv/linux/pthread_kill.c:57 [Current thread is 1 (Thread 0x145db7bd4700 (LWP 1255802))] (gdb) bt #0 __pthread_kill (threadid=<optimized out>, signo=signo@entry=6) at ../sysdeps/unix/sysv/linux/pthread_kill.c:57 #1 0x000055fa8020c4d7 in my_write_core (sig=sig@entry=6) at /test/10.5_dbg/mysys/stacktrace.c:518 #2 0x000055fa7f9c69ba in handle_fatal_signal (sig=6) at /test/10.5_dbg/sql/signal_handler.cc:330 #3 <signal handler called> #4 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:51 #5 0x0000145db5e6a8b1 in __GI_abort () at abort.c:79 #6 0x0000145db5e5a42a in __assert_fail_base (fmt=0x145db5fe1a38 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=assertion@entry=0x55fa8051bcd0 "pos->field != __null || pos->item != __null", file=file@entry=0x55fa8051bab0 "/test/10.5_dbg/sql/filesort.cc", line=line@entry=604, function=function@entry=0x55fa8051c960 <Filesort::make_sortorder(THD*, JOIN*, unsigned long long)::__PRETTY_FUNCTION__> "Sort_keys* Filesort::make_sortorder(THD*, JOIN*, table_map)") at assert.c:92 #7 0x0000145db5e5a4a2 in __GI___assert_fail (assertion=assertion@entry=0x55fa8051bcd0 "pos->field != __null || pos->item != __null", file=file@entry=0x55fa8051bab0 "/test/10.5_dbg/sql/filesort.cc", line=line@entry=604, function=function@entry=0x55fa8051c960 <Filesort::make_sortorder(THD*, JOIN*, unsigned long long)::__PRETTY_FUNCTION__> "Sort_keys* Filesort::make_sortorder(THD*, JOIN*, table_map)") at assert.c:101 #8 0x000055fa7f9bed2d in Filesort::make_sortorder (this=this@entry=0x145d9397b698, thd=thd@entry=0x145d93815088, join=join@entry=0x145d93876c40, first_table_bit=first_table_bit@entry=1) at /test/10.5_dbg/sql/filesort.cc:604 #9 0x000055fa7f9c2cd7 in filesort (thd=thd@entry=0x145d93815088, table=table@entry=0x145d9396e0a0, filesort=filesort@entry=0x145d9397b698, tracker=0x145d9397b928, join=join@entry=0x145d93876c40, first_table_bit=1) at /test/10.5_dbg/sql/filesort.cc:211 #10 0x000055fa7f7715a8 in create_sort_index (thd=thd@entry=0x145d93815088, join=join@entry=0x145d93876c40, tab=tab@entry=0x145d938798e0, fsort=0x145d9397b698) at /test/10.5_dbg/sql/sql_select.cc:23875 #11 0x000055fa7f912c86 in Window_funcs_sort::exec (this=0x145d9397b590, join=join@entry=0x145d93876c40, keep_filesort_result=<optimized out>) at /test/10.5_dbg/sql/sql_window.cc:2998 #12 0x000055fa7f9133a7 in Window_funcs_computation::exec (this=0x145d9397b570, join=join@entry=0x145d93876c40, keep_last_filesort_result=keep_last_filesort_result@entry=false) at /test/10.5_dbg/sql/sql_window.cc:3131 #13 0x000055fa7f778d2c in AGGR_OP::end_send (this=this@entry=0x145d9387a060) at /test/10.5_dbg/sql/sql_select.cc:28896 #14 0x000055fa7f7791c8 in sub_select_postjoin_aggr (join=0x145d93876c40, join_tab=0x145d938798e0, end_of_records=<optimized out>) at /test/10.5_dbg/sql/sql_select.cc:20351 #15 0x000055fa7f75ffef in sub_select (join=join@entry=0x145d93876c40, join_tab=join_tab@entry=0x145d93879538, end_of_records=end_of_records@entry=true) at /test/10.5_dbg/sql/sql_select.cc:20586 #16 0x000055fa7f7605e0 in sub_select_cache (join=0x145d93876c40, join_tab=0x145d93879538, end_of_records=<optimized out>) at /test/10.5_dbg/sql/sql_select.cc:20418 #17 0x000055fa7f75ffef in sub_select (join=0x145d93876c40, join_tab=0x145d93879190, end_of_records=<optimized out>) at /test/10.5_dbg/sql/sql_select.cc:20586 #18 0x000055fa7f79a594 in do_select (procedure=<optimized out>, join=0x145d93876c40) at /test/10.5_dbg/sql/sql_select.cc:20177 #19 JOIN::exec_inner (this=this@entry=0x145d93876c40) at /test/10.5_dbg/sql/sql_select.cc:4450 #20 0x000055fa7f79a8fd in JOIN::exec (this=this@entry=0x145d93876c40) at /test/10.5_dbg/sql/sql_select.cc:4231 #21 0x000055fa7f798bf9 in mysql_select (thd=thd@entry=0x145d93815088, tables=<optimized out>, fields=@0x145d93874320: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x145d93874788, last = 0x145d93874e20, elements = 2}, <No data fields>}, conds=0x145d93875f38, og_num=1, order=<optimized out>, group=0x145d938762c8, having=0x0, proc_param=0x0, select_options=2147748608, result=0x145d93876c18, unit=0x145d938190a0, select_lex=0x145d938741d0) at /test/10.5_dbg/sql/sql_select.cc:4655 #22 0x000055fa7f798f28 in handle_select (thd=thd@entry=0x145d93815088, lex=lex@entry=0x145d93818fd8, result=result@entry=0x145d93876c18, setup_tables_done_option=setup_tables_done_option@entry=0) at /test/10.5_dbg/sql/sql_select.cc:417 #23 0x000055fa7f7217e2 in execute_sqlcom_select (thd=thd@entry=0x145d93815088, all_tables=0x145d93874e90) at /test/10.5_dbg/sql/sql_parse.cc:6209 #24 0x000055fa7f71a8b6 in mysql_execute_command (thd=thd@entry=0x145d93815088) at /test/10.5_dbg/sql/sql_parse.cc:3931 #25 0x000055fa7f727752 in mysql_parse (thd=thd@entry=0x145d93815088, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x145db7bd3350, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /test/10.5_dbg/sql/sql_parse.cc:7993 #26 0x000055fa7f714204 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x145d93815088, packet=packet@entry=0x145d93867089 "", packet_length=packet_length@entry=98, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /test/10.5_dbg/sql/sql_parse.cc:1866 #27 0x000055fa7f7129de in do_command (thd=0x145d93815088) at /test/10.5_dbg/sql/sql_parse.cc:1347 #28 0x000055fa7f86ec3b in do_handle_one_connection (connect=<optimized out>, connect@entry=0x145d96cc7808, put_in_cache=put_in_cache@entry=true) at /test/10.5_dbg/sql/sql_connect.cc:1411 #29 0x000055fa7f86f357 in handle_one_connection (arg=arg@entry=0x145d96cc7808) at /test/10.5_dbg/sql/sql_connect.cc:1313 #30 0x000055fa7fcd2ca8 in pfs_spawn_thread (arg=0x145db4846508) at /test/10.5_dbg/storage/perfschema/pfs.cc:2201 #31 0x0000145db6b4d6db in start_thread (arg=0x145db7bd4700) at pthread_create.c:463 #32 0x0000145db5f4ba3f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Roel Van de Paar added a comment - 2020-07-14 10:13 - edited Unique bug ID's so far SIGSEGV|sortlength|filesort|create_sort_index|Window_funcs_sort::exec # Original testcase, optimized, 10.5.5 at 30e7a0a866dce530d8328c6d614e48d39a264f9b SIGSEGV|sortlength|filesort|create_sort_index|st_join_table::sort_table # Second testcase above, optimized 10.5.5 at e1013725ce0f3f947e728491eef75d9985e8db2f SIGSEGV|sortlength|filesort|create_sort_index|st_join_table::sort_table # MDEV-22852 testcase, optimized, 10.5.5 at e1013725ce0f3f947e728491eef75d9985e8db2f pos->field != __null || pos->item != __null|SIGABRT|Filesort::make_sortorder|filesort|create_sort_index|Window_funcs_sort::exec # Original testcase, debug, 10.5.5 at 30e7a0a866dce530d8328c6d614e48d39a264f9b pos->field != __null || pos->item != __null|SIGABRT|Filesort::make_sortorder|filesort|create_sort_index|st_join_table::sort_table # Second testcase above, debug 10.5.5 at e1013725ce0f3f947e728491eef75d9985e8db2f pos->field != __null || pos->item != __null|SIGABRT|Filesort::make_sortorder|filesort|create_sort_index|st_join_table::sort_table # MDEV-22852 testcase, debug, 10.5.5 at e1013725ce0f3f947e728491eef75d9985e8db2f Based on all findings, it would seem that the second testcase would have fitted better with MDEV-22852 , is now resolved, but that the original issue, for which a patch was created also, but not pushed yet, still exists.

Sergei Petrunia added a comment - 2023-01-20 11:37 SELECT a, RANK() OVER ( ORDER BY SUM ( DISTINCT i1)) FROM t1, t2 WHERE t2.i2 = t1.i1 GROUP BY a; This query can only be executed as follows: join t1, t2 put into temp.table sort by the GROUP BY criteria read the sorted file compute SUM(DISTINCT) // this can only use one-group-after-another algorithm due to DISTINCT write output into temptable #2.   window function processing starts; sort by window-func-criteria-1; // WE CRASH HERE read the output and compute window function; The crash happens, because the sorting code can't see where it can find the value of SUM(DISTINCT i1) .

Sergei Petrunia added a comment - 2023-01-20 11:38 - edited Note that EXPLAIN doesn't show the "sort by the GROUP BY criteria" step: (EDIT: incorrect, it does show it but in the wrong place) { "query_block" : { "select_id" : 1, "filesort" : { "sort_key" : "t1.a" , "window_functions_computation" : { "sorts" : { "filesort" : { "sort_key" : "sum(distinct t1.i1)" } }, "temporary_table" : { "table" : { "table_name" : "t1" , "access_type" : "ALL" , "rows" : 3, "filtered" : 100 }, "block-nl-join" : { "table" : { "table_name" : "t2" , "access_type" : "ALL" , "rows" : 7, "filtered" : 100 }, "buffer_type" : "flat" , "buffer_size" : "256Kb" , "join_type" : "BNL" , "attached_condition" : "t2.i2 = t1.i1" } } } } } }

Sergei Petrunia added a comment - 2023-01-20 13:15 - edited If I modify the query slightly, it doesn't crash. But in debugger I observe that window function computation is done before GROUP BY operation is done! explain format=json SELECT a, COUNT ( DISTINCT i1), RANK() OVER ( ORDER BY a) FROM t1, t2 WHERE t2.i2 = t1.i1 GROUP BY a; The result is incorrect: +------+--------------------+--------------------------+ | a | COUNT(DISTINCT i1) | RANK() OVER (ORDER BY a) | +------+--------------------+--------------------------+ | 1 | 1 | 3 | | 2 | 1 | 4 | | 3 | 1 | 4 | +------+--------------------+--------------------------+ RANK() should be 1, 2, 3 ...