Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-15212

server crashed, query using ORDER BY (expression with window function)

Details

    Description

      CREATE TABLE t1(i int, j int);
      		 
      INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(10,1),(20,2),(30,3),(20,1),(40,2),(60,3),(30,1),(60,2),(90,3);
      		 
       
      		 
      SELECT SUM(i) OVER (PARTITION BY j ORDER BY i) as k, i, j FROM t1 order by k ;
      		 
       
      		 
      SELECT 1+ (SUM(i) OVER (PARTITION BY j ORDER BY i)) as k,i,j FROM t1 order by k ;  # results are not ordered by k
      		 
       
      		 
      # server crash:
      		 
      SELECT 1+ (SUM(i) OVER (PARTITION BY j ORDER BY i)) as k,i,j FROM t1 order by 1+ (SUM(i) OVER (PARTITION BY j ORDER BY i)) ;


      SELECT SUM(i) OVER (PARTITION BY j ORDER BY i) as k, i, j FROM t1 order by k ;
      		 
      k	i	j
      		 
      1	1	1
      		 
      2	2	2
      		 
      3	3	3
      		 
      11	10	1
      		 
      22	20	2
      		 
      31	20	1
      		 
      33	30	3
      		 
      61	30	1
      		 
      62	40	2
      		 
      93	60	3
      		 
      122	60	2
      		 
      183	90	3
      		 
      SELECT 1+ (SUM(i) OVER (PARTITION BY j ORDER BY i)) as k,i,j FROM t1 order by k ;
      		 
      k	i	j
      		 
      2	1	1
      		 
      184	90	3
      		 
      32	20	1
      		 
      3	2	2
      		 
      63	40	2
      		 
      4	3	3
      		 
      94	60	3
      		 
      12	10	1
      		 
      62	30	1
      		 
      23	20	2
      		 
      123	60	2
      		 
      34	30	3
      		 
      


      Thread 1 (Thread 0x7fcd90066700 (LWP 16924)):
      		 
      #0  __pthread_kill (threadid=<optimized out>, signo=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:62
      		 
      #1  0x0000565343fad403 in my_write_core (sig=11) at /home/alice/git/10.2/mysys/stacktrace.c:477
      		 
      #2  0x000056534383d6f1 in handle_fatal_signal (sig=11) at /home/alice/git/10.2/sql/signal_handler.cc:305
      		 
      #3  <signal handler called>
      		 
      #4  0x0000565343513c2e in Field::set_notnull (this=0x0, row_offset=0) at /home/alice/git/10.2/sql/field.h:1137
      		 
      #5  0x0000565343863782 in Item::save_in_field (this=0x7fcd7417ed90, field=0x0, no_conversions=true) at /home/alice/git/10.2/sql/item.cc:6456
      		 
      #6  0x00005653437926ec in save_window_function_values (window_functions=..., tbl=0x7fcd740605f0, rowid_buf=0x7fcd74066058 "\030\315\002t\315\177") at /home/alice/git/10.2/sql/sql_window.cc:2517
      		 
      #7  0x0000565343792b23 in compute_window_func (thd=0x7fcd74000a98, window_functions=..., cursor_managers=..., tbl=0x7fcd740605f0, filesort_result=0x7fcd74147e60) at /home/alice/git/10.2/sql/sql_window.cc:2636
      		 
      #8  0x0000565343792ed8 in Window_func_runner::exec (this=0x7fcd7406bb68, thd=0x7fcd74000a98, tbl=0x7fcd740605f0, filesort_result=0x7fcd74147e60) at /home/alice/git/10.2/sql/sql_window.cc:2742
      		 
      #9  0x0000565343792ff2 in Window_funcs_sort::exec (this=0x7fcd7406bb60, join=0x7fcd740fceb0) at /home/alice/git/10.2/sql/sql_window.cc:2767
      		 
      #10 0x00005653437934af in Window_funcs_computation::exec (this=0x7fcd74120be0, join=0x7fcd740fceb0) at /home/alice/git/10.2/sql/sql_window.cc:2886
      		 
      #11 0x0000565343650aaa in AGGR_OP::end_send (this=0x7fcd7406ac90) at /home/alice/git/10.2/sql/sql_select.cc:26540
      		 
      #12 0x000056534363bc26 in sub_select_postjoin_aggr (join=0x7fcd740fceb0, join_tab=0x7fcd740697d0, end_of_records=true) at /home/alice/git/10.2/sql/sql_select.cc:18379
      		 
      #13 0x000056534363bf4a in sub_select (join=0x7fcd740fceb0, join_tab=0x7fcd74069420, end_of_records=true) at /home/alice/git/10.2/sql/sql_select.cc:18615
      		 
      #14 0x000056534363b75b in do_select (join=0x7fcd740fceb0, procedure=0x0) at /home/alice/git/10.2/sql/sql_select.cc:18210
      		 
      #15 0x0000565343616185 in JOIN::exec_inner (this=0x7fcd740fceb0) at /home/alice/git/10.2/sql/sql_select.cc:3540
      		 
      #16 0x0000565343615626 in JOIN::exec (this=0x7fcd740fceb0) at /home/alice/git/10.2/sql/sql_select.cc:3335
      		 
      #17 0x00005653436167f6 in mysql_select (thd=0x7fcd74000a98, tables=0x7fcd74115de0, wild_num=0, fields=..., conds=0x0, og_num=1, order=0x7fcd740fc8b0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fcd74010080, unit=0x7fcd74004638, select_lex=0x7fcd74004d90) at /home/alice/git/10.2/sql/sql_select.cc:3735
      		 
      #18 0x000056534360aeff in handle_select (thd=0x7fcd74000a98, lex=0x7fcd74004570, result=0x7fcd74010080, setup_tables_done_option=0) at /home/alice/git/10.2/sql/sql_select.cc:373
      		 
      #19 0x00005653435d6e82 in execute_sqlcom_select (thd=0x7fcd74000a98, all_tables=0x7fcd74115de0) at /home/alice/git/10.2/sql/sql_parse.cc:6456
      		 
      #20 0x00005653435ccc00 in mysql_execute_command (thd=0x7fcd74000a98) at /home/alice/git/10.2/sql/sql_parse.cc:3467
      		 
      #21 0x00005653435da81d in mysql_parse (thd=0x7fcd74000a98, rawbuf=0x7fcd7402bb50 "SELECT 1+ (SUM(i) OVER (PARTITION BY j ORDER BY i)) as k,i,j FROM t1 order by 1+ (SUM(i) OVER (PARTITION BY j ORDER BY i))", length=122, parser_state=0x7fcd900651f0, is_com_multi=false, is_next_command=false) at /home/alice/git/10.2/sql/sql_parse.cc:7897
      		 
      #22 0x00005653435c849f in dispatch_command (command=COM_QUERY, thd=0x7fcd74000a98, packet=0x7fcd74007fe9 "", packet_length=123, is_com_multi=false, is_next_command=false) at /home/alice/git/10.2/sql/sql_parse.cc:1806
      		 
      #23 0x00005653435c6dfb in do_command (thd=0x7fcd74000a98) at /home/alice/git/10.2/sql/sql_parse.cc:1360
      		 
      #24 0x000056534371501f in do_handle_one_connection (connect=0x565345e96d48) at /home/alice/git/10.2/sql/sql_connect.cc:1335
      		 
      #25 0x0000565343714d9f in handle_one_connection (arg=0x565345e96d48) at /home/alice/git/10.2/sql/sql_connect.cc:1241
      		 
      #26 0x0000565343a72bc4 in pfs_spawn_thread (arg=0x565345df6908) at /home/alice/git/10.2/storage/perfschema/pfs.cc:1862
      		 
      #27 0x00007fcd961cf6ba in start_thread (arg=0x7fcd90066700) at pthread_create.c:333
      		 
      #28 0x00007fcd9566441d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

      Attachments

        Issue Links

          duplicates

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14791 Crash with order by expression containing window functions

          • Major - Major loss of function.
          • Closed
          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18965 ORDER BY is incorrect with window functions inside IF

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16736 crash on lag() over() and order not working

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            alice Alice Sherepa added a comment -

            Test case from MDEV-18965:

            CREATE TABLE test (a int , b int);
            		 
            INSERT INTO test VALUES (1,100),(1,90),(2,5),(2,2);
            		 
             
            		 
            SELECT a,
            		 
                   row_number() over (partition by a),
            		 
                   if(row_number() over (partition by a) =1,1,0) as c
            		 
            FROM test
            		 
            ORDER BY c,b; # wrong order
            		 
             
            		 
            SELECT a,
            		 
                   row_number() over (partition by a),
            		 
                   if(row_number() over (partition by a) =1,1,0) as c
            		 
            FROM test
            		 
            ORDER BY if(row_number() over (partition by a) =1,1,0), b;

            10.2 00572a0b0cc81c38f198adcc55

            		 
                #0 0x560af8ef9e08 in Field::set_notnull(long long) /10.2/sql/field.h:1143
            		 
                #1 0x560af96b9949 in Item::save_in_field(Field*, bool) /10.2/sql/item.cc:6414
            		 
                #2 0x560af94c2a75 in save_window_function_values /10.2/sql/sql_window.cc:2517
            		 
                #3 0x560af94c3296 in compute_window_func(THD*, List<Item_window_func>&, List<Cursor_manager>&, TABLE*, SORT_INFO*) /10.2/sql/sql_window.cc:2636
            		 
                #4 0x560af94c390a in Window_func_runner::exec(THD*, TABLE*, SORT_INFO*) /10.2/sql/sql_window.cc:2742
            		 
                #5 0x560af94c3b59 in Window_funcs_sort::exec(JOIN*, bool) /10.2/sql/sql_window.cc:2767
            		 
                #6 0x560af94c4820 in Window_funcs_computation::exec(JOIN*, bool) /10.2/sql/sql_window.cc:2893
            		 
                #7 0x560af91d8e4d in AGGR_OP::end_send() /10.2/sql/sql_select.cc:26671
            		 
                #8 0x560af91a13a4 in sub_select_postjoin_aggr(JOIN*, st_join_table*, bool) /10.2/sql/sql_select.cc:18473
            		 
                #9 0x560af91a1b23 in sub_select(JOIN*, st_join_table*, bool) /10.2/sql/sql_select.cc:18709
            		 
                #10 0x560af91a07b8 in do_select /10.2/sql/sql_select.cc:18304
            		 
                #11 0x560af9142fcb in JOIN::exec_inner() /10.2/sql/sql_select.cc:3623
            		 
                #12 0x560af9140cd1 in JOIN::exec() /10.2/sql/sql_select.cc:3418
            		 
                #13 0x560af914401e in 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*) /10.2/sql/sql_select.cc:3818
            		 
                #14 0x560af91237e4 in handle_select(THD*, LEX*, select_result*, unsigned long) /10.2/sql/sql_select.cc:376
            		 
                #15 0x560af90ab154 in execute_sqlcom_select /10.2/sql/sql_parse.cc:6477
            		 
                #16 0x560af909861a in mysql_execute_command(THD*) /10.2/sql/sql_parse.cc:3535
            		 
                #17 0x560af90b38b8 in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /10.2/sql/sql_parse.cc:8011
            		 
                #18 0x560af908ef6d in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /10.2/sql/sql_parse.cc:1832
            		 
                #19 0x560af908c0ec in do_command(THD*) /10.2/sql/sql_parse.cc:1386
            		 
                #20 0x560af93b3d61 in do_handle_one_connection(CONNECT*) /10.2/sql/sql_connect.cc:1335
            		 
                #21 0x560af93b3769 in handle_one_connection /10.2/sql/sql_connect.cc:1241
            		 
                #22 0x560afa564fd9 in pfs_spawn_thread /10.2/storage/perfschema/pfs.cc:1862
            		 
                #23 0x7f965ccf36b9 in start_thread (/lib/x86_64-linux-gnu/libpthread.so.0+0x76b9)
            		 
                #24 0x7f965c18841c in clone (/lib/x86_64-linux-gnu/libc.so.6+0x10741c)

            alice Alice Sherepa added a comment - Test case from MDEV-18965 : CREATE TABLE test (a int , b int ); INSERT INTO test VALUES (1,100),(1,90),(2,5),(2,2);   SELECT a, row_number() over (partition by a), if (row_number() over (partition by a) =1,1,0) as c FROM test ORDER BY c,b; # wrong order   SELECT a, row_number() over (partition by a), if (row_number() over (partition by a) =1,1,0) as c FROM test ORDER BY if (row_number() over (partition by a) =1,1,0), b; 10.2 00572a0b0cc81c38f198adcc55 #0 0x560af8ef9e08 in Field::set_notnull(long long) /10.2/sql/field.h:1143 #1 0x560af96b9949 in Item::save_in_field(Field*, bool) /10.2/sql/item.cc:6414 #2 0x560af94c2a75 in save_window_function_values /10.2/sql/sql_window.cc:2517 #3 0x560af94c3296 in compute_window_func(THD*, List<Item_window_func>&, List<Cursor_manager>&, TABLE*, SORT_INFO*) /10.2/sql/sql_window.cc:2636 #4 0x560af94c390a in Window_func_runner::exec(THD*, TABLE*, SORT_INFO*) /10.2/sql/sql_window.cc:2742 #5 0x560af94c3b59 in Window_funcs_sort::exec(JOIN*, bool) /10.2/sql/sql_window.cc:2767 #6 0x560af94c4820 in Window_funcs_computation::exec(JOIN*, bool) /10.2/sql/sql_window.cc:2893 #7 0x560af91d8e4d in AGGR_OP::end_send() /10.2/sql/sql_select.cc:26671 #8 0x560af91a13a4 in sub_select_postjoin_aggr(JOIN*, st_join_table*, bool) /10.2/sql/sql_select.cc:18473 #9 0x560af91a1b23 in sub_select(JOIN*, st_join_table*, bool) /10.2/sql/sql_select.cc:18709 #10 0x560af91a07b8 in do_select /10.2/sql/sql_select.cc:18304 #11 0x560af9142fcb in JOIN::exec_inner() /10.2/sql/sql_select.cc:3623 #12 0x560af9140cd1 in JOIN::exec() /10.2/sql/sql_select.cc:3418 #13 0x560af914401e in 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*) /10.2/sql/sql_select.cc:3818 #14 0x560af91237e4 in handle_select(THD*, LEX*, select_result*, unsigned long) /10.2/sql/sql_select.cc:376 #15 0x560af90ab154 in execute_sqlcom_select /10.2/sql/sql_parse.cc:6477 #16 0x560af909861a in mysql_execute_command(THD*) /10.2/sql/sql_parse.cc:3535 #17 0x560af90b38b8 in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /10.2/sql/sql_parse.cc:8011 #18 0x560af908ef6d in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /10.2/sql/sql_parse.cc:1832 #19 0x560af908c0ec in do_command(THD*) /10.2/sql/sql_parse.cc:1386 #20 0x560af93b3d61 in do_handle_one_connection(CONNECT*) /10.2/sql/sql_connect.cc:1335 #21 0x560af93b3769 in handle_one_connection /10.2/sql/sql_connect.cc:1241 #22 0x560afa564fd9 in pfs_spawn_thread /10.2/storage/perfschema/pfs.cc:1862 #23 0x7f965ccf36b9 in start_thread (/lib/x86_64-linux-gnu/libpthread.so.0+0x76b9) #24 0x7f965c18841c in clone (/lib/x86_64-linux-gnu/libc.so.6+0x10741c)
            varun Varun Gupta (Inactive) added a comment -

            Looks like a duplicate of MDEV-14791

            varun Varun Gupta (Inactive) added a comment - Looks like a duplicate of MDEV-14791
            varun Varun Gupta (Inactive) added a comment -

            Fixed by MDEV-14791

            MariaDB [test]> SELECT a,
            		 
                ->        row_number() over (partition by a),
            		 
                ->        if(row_number() over (partition by a) =1,1,0) as c
            		 
                -> FROM test
            		 
                -> ORDER BY c,b; # wrong order
            		 
            +------+------------------------------------+---+
            		 
            | a    | row_number() over (partition by a) | c |
            		 
            +------+------------------------------------+---+
            		 
            |    2 |                                  2 | 0 |
            		 
            |    1 |                                  2 | 0 |
            		 
            |    2 |                                  1 | 1 |
            		 
            |    1 |                                  1 | 1 |
            		 
            +------+------------------------------------+---+
            		 
            4 rows in set (0.01 sec)
            		 
             
            		 
            MariaDB [test]> 
            MariaDB [test]> SELECT a,
            		 
                ->        row_number() over (partition by a),
            		 
                ->        if(row_number() over (partition by a) =1,1,0) as c
            		 
                -> FROM test
            		 
                -> ORDER BY if(row_number() over (partition by a) =1,1,0), b;
            		 
            +------+------------------------------------+---+
            		 
            | a    | row_number() over (partition by a) | c |
            		 
            +------+------------------------------------+---+
            		 
            |    2 |                                  2 | 0 |
            		 
            |    1 |                                  2 | 0 |
            		 
            |    2 |                                  1 | 1 |
            		 
            |    1 |                                  1 | 1 |
            		 
            +------+------------------------------------+---+
            		 
            4 rows in set (0.00 sec)

            varun Varun Gupta (Inactive) added a comment - Fixed by MDEV-14791 MariaDB [test]> SELECT a, -> row_number() over (partition by a), -> if(row_number() over (partition by a) =1,1,0) as c -> FROM test -> ORDER BY c,b; # wrong order +------+------------------------------------+---+ | a | row_number() over (partition by a) | c | +------+------------------------------------+---+ | 2 | 2 | 0 | | 1 | 2 | 0 | | 2 | 1 | 1 | | 1 | 1 | 1 | +------+------------------------------------+---+ 4 rows in set (0.01 sec)   MariaDB [test]> MariaDB [test]> SELECT a, -> row_number() over (partition by a), -> if(row_number() over (partition by a) =1,1,0) as c -> FROM test -> ORDER BY if(row_number() over (partition by a) =1,1,0), b; +------+------------------------------------+---+ | a | row_number() over (partition by a) | c | +------+------------------------------------+---+ | 2 | 2 | 0 | | 1 | 2 | 0 | | 2 | 1 | 1 | | 1 | 1 | 1 | +------+------------------------------------+---+ 4 rows in set (0.00 sec)

            People

              varun Varun Gupta (Inactive)
              alice Alice Sherepa
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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