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

crash on lag() over() and order not working

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.2.14, 10.2(EOL), 10.3(EOL), 10.4(EOL)
    • N/A
    • Optimizer - Window functions
    • None
    • OpenSuse Leap 15

    Description

      MariaDB server crashes. To reproduce this error, please execute the following script:

      create or replace database BugDB;
      		 
      use BugDB;
      		 
      create or replace table lead_lag_order_bug (
      		 
      	id int(11) NOT NULL AUTO_INCREMENT,
      		 
      	TnNr int(11) not null,
      		 
      	Victorys int(11) not null default 0,
      		 
      	Points int(11) not null default 0,
      		 
      	Dv64 bit(64) default b'0',	/*indicate witch TnNr (BitNr is TnNr) has beaten (Direct comparison)*/
      		 
      	PRIMARY KEY (`id`)
      		 
      )
      		 
      ;
      		 
       
      		 
      insert into lead_lag_order_bug(TnNr,Victorys,Points,Dv64) values
      		 
      (1,2,10,b'0'),
      		 
      (2,2,10,b'1'),
      		 
      (3,3,20,b'0'),
      		 
      (4,3,20,b'100')
      		 
      ;
      		 
       
      		 
       
      		 
       
      		 
      /*bug number one -> order not working*/
      		 
      select bin(Dv64) bitSet,
      		 
      	lag(TnNr) OVER (partition by Victorys, Points order by Victorys desc, Points desc, TnNR) as DvTnNr,
      		 
      	TnNr,
      		 
      	Victorys,
      		 
      	Points,
      		 
      	(Dv64 >> (lag(TnNr) OVER (partition by Victorys, Points order by Victorys desc, Points desc,TnNr)-1)) & 1 as Dv
      		 
      from lead_lag_order_bug t
      		 
      order by Victorys desc, Points desc, Dv desc, TnNr
      		 
      ;
      		 
       
      		 
      /*bug number two -> segmentation fault*/
      		 
      select bin(Dv64) bitSet,
      		 
      	lag(TnNr) OVER (partition by Victorys, Points order by Victorys desc, Points desc, TnNR) as DvTnNr,
      		 
      	TnNr,
      		 
      	Victorys,
      		 
      	Points,
      		 
      	(Dv64 >> (lag(TnNr) OVER (partition by Victorys, Points order by Victorys desc, Points desc,TnNr)-1)) & 1 as Dv
      		 
      from lead_lag_order_bug t
      		 
      order by 
      	Victorys desc, 
      	Points desc, 
      	((Dv64 >> (lag(TnNr) OVER (partition by Victorys, Points order by Victorys desc, Points desc,TnNr)-1)) & 1) desc,
      		 
      	TnNr
      		 
      ;

      Attachments

        Issue Links

          is duplicated by

          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
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13649 Server crashes in set_field_to_null_with_conversions or in Field::set_notnull

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15212 server crashed, query using ORDER BY (expression with window function)

          • Major - Major loss of function.
          • Closed

          Activity

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

            Thanks for the report and the the testcase! Reproducible on 10.2, 10.3

             10.2 1cc1d0429da14a041a62
            		 
            Thread 1 (Thread 0x7f165c153700 (LWP 6780)):
            		 
            #0  __pthread_kill (threadid=<optimized out>, signo=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:62
            		 
            #1  0x000055e10d6d0789 in my_write_core (sig=11) at /home/alice/git/10.2/mysys/stacktrace.c:477
            		 
            #2  0x000055e10cf68eed in handle_fatal_signal (sig=11) at /home/alice/git/10.2/sql/signal_handler.cc:305
            		 
            #3  <signal handler called>
            		 
            #4  0x000055e10cf5fdd1 in set_field_to_null_with_conversions (field=0x0, no_conversions=true) at /home/alice/git/10.2/sql/field_conv.cc:204
            		 
            #5  0x000055e10cf8f28a in Item::save_in_field (this=0x7f1610015f38, field=0x0, no_conversions=true) at /home/alice/git/10.2/sql/item.cc:6509
            		 
            #6  0x000055e10cebe631 in save_window_function_values (window_functions=..., tbl=0x7f161012f9a8, rowid_buf=0x7f1610112c40 "\300E\003\020\026\177") at /home/alice/git/10.2/sql/sql_window.cc:2517
            		 
            #7  0x000055e10cebea68 in compute_window_func (thd=0x7f1610000b00, window_functions=..., cursor_managers=..., tbl=0x7f161012f9a8, filesort_result=0x7f1610131e00) at /home/alice/git/10.2/sql/sql_window.cc:2636
            		 
            #8  0x000055e10cebee1c in Window_func_runner::exec (this=0x7f161012dc90, thd=0x7f1610000b00, tbl=0x7f161012f9a8, filesort_result=0x7f1610131e00) at /home/alice/git/10.2/sql/sql_window.cc:2742
            		 
            #9  0x000055e10cebef35 in Window_funcs_sort::exec (this=0x7f161012dc88, join=0x7f1610016620) at /home/alice/git/10.2/sql/sql_window.cc:2767
            		 
            #10 0x000055e10cebf3f7 in Window_funcs_computation::exec (this=0x7f161012dc68, join=0x7f1610016620) at /home/alice/git/10.2/sql/sql_window.cc:2886
            		 
            #11 0x000055e10cd7a2c8 in AGGR_OP::end_send (this=0x7f1610018390) at /home/alice/git/10.2/sql/sql_select.cc:26607
            		 
            #12 0x000055e10cd6548f in sub_select_postjoin_aggr (join=0x7f1610016620, join_tab=0x7f161012c6d0, end_of_records=true) at /home/alice/git/10.2/sql/sql_select.cc:18438
            		 
            #13 0x000055e10cd657b3 in sub_select (join=0x7f1610016620, join_tab=0x7f161012c320, end_of_records=true) at /home/alice/git/10.2/sql/sql_select.cc:18674
            		 
            #14 0x000055e10cd64fcb in do_select (join=0x7f1610016620, procedure=0x0) at /home/alice/git/10.2/sql/sql_select.cc:18269
            		 
            #15 0x000055e10cd3fae7 in JOIN::exec_inner (this=0x7f1610016620) at /home/alice/git/10.2/sql/sql_select.cc:3595
            		 
            #16 0x000055e10cd3ef96 in JOIN::exec (this=0x7f1610016620) at /home/alice/git/10.2/sql/sql_select.cc:3390
            		 
            #17 0x000055e10cd40158 in mysql_select (thd=0x7f1610000b00, tables=0x7f1610014b50, wild_num=0, fields=..., conds=0x0, og_num=4, order=0x7f1610015280, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f1610016600, unit=0x7f16100046d8, select_lex=0x7f1610004e10) at /home/alice/git/10.2/sql/sql_select.cc:3790
            		 
            #18 0x000055e10cd34674 in handle_select (thd=0x7f1610000b00, lex=0x7f1610004610, result=0x7f1610016600, setup_tables_done_option=0) at /home/alice/git/10.2/sql/sql_select.cc:376
            		 
            #19 0x000055e10ccffe58 in execute_sqlcom_select (thd=0x7f1610000b00, all_tables=0x7f1610014b50) at /home/alice/git/10.2/sql/sql_parse.cc:6474
            		 
            #20 0x000055e10ccf5b96 in mysql_execute_command (thd=0x7f1610000b00) at /home/alice/git/10.2/sql/sql_parse.cc:3481
            		 
            #21 0x000055e10cd03bc9 in mysql_parse (thd=0x7f1610000b00, rawbuf=0x7f1610012478 "SELECT bin(Dv64),\nlag(TnNr) OVER (partition BY Victorys, Points ORDER BY Victorys DESC, Points DESC, TnNR),\nTnNr,\nVictorys,\nPoints,\n(Dv64 >> (lag(TnNr) OVER (partition BY Victorys, Points  ORDER BY Vi"..., length=405, parser_state=0x7f165c152200, is_com_multi=false, is_next_command=false) at /home/alice/git/10.2/sql/sql_parse.cc:7995
            		 
            #22 0x000055e10ccf1409 in dispatch_command (command=COM_QUERY, thd=0x7f1610000b00, packet=0x7f16100753d1 "", packet_length=406, is_com_multi=false, is_next_command=false) at /home/alice/git/10.2/sql/sql_parse.cc:1821
            		 
            #23 0x000055e10ccefd65 in do_command (thd=0x7f1610000b00) at /home/alice/git/10.2/sql/sql_parse.cc:1375
            		 
            #24 0x000055e10ce40b77 in do_handle_one_connection (connect=0x55e10f766ac0) at /home/alice/git/10.2/sql/sql_connect.cc:1335
            		 
            #25 0x000055e10ce408f7 in handle_one_connection (arg=0x55e10f766ac0) at /home/alice/git/10.2/sql/sql_connect.cc:1241
            		 
            #26 0x000055e10d66de6e in pfs_spawn_thread (arg=0x55e10f6ca680) at /home/alice/git/10.2/storage/perfschema/pfs.cc:1862
            		 
            #27 0x00007f16637266ba in start_thread (arg=0x7f165c153700) at pthread_create.c:333
            		 
            #28 0x00007f1662bbb41d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

            alice Alice Sherepa added a comment - Thanks for the report and the the testcase! Reproducible on 10.2, 10.3 10.2 1cc1d0429da14a041a62 Thread 1 (Thread 0x7f165c153700 (LWP 6780)): #0 __pthread_kill (threadid=<optimized out>, signo=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:62 #1 0x000055e10d6d0789 in my_write_core (sig=11) at /home/alice/git/10.2/mysys/stacktrace.c:477 #2 0x000055e10cf68eed in handle_fatal_signal (sig=11) at /home/alice/git/10.2/sql/signal_handler.cc:305 #3 <signal handler called> #4 0x000055e10cf5fdd1 in set_field_to_null_with_conversions (field=0x0, no_conversions=true) at /home/alice/git/10.2/sql/field_conv.cc:204 #5 0x000055e10cf8f28a in Item::save_in_field (this=0x7f1610015f38, field=0x0, no_conversions=true) at /home/alice/git/10.2/sql/item.cc:6509 #6 0x000055e10cebe631 in save_window_function_values (window_functions=..., tbl=0x7f161012f9a8, rowid_buf=0x7f1610112c40 "\300E\003\020\026\177") at /home/alice/git/10.2/sql/sql_window.cc:2517 #7 0x000055e10cebea68 in compute_window_func (thd=0x7f1610000b00, window_functions=..., cursor_managers=..., tbl=0x7f161012f9a8, filesort_result=0x7f1610131e00) at /home/alice/git/10.2/sql/sql_window.cc:2636 #8 0x000055e10cebee1c in Window_func_runner::exec (this=0x7f161012dc90, thd=0x7f1610000b00, tbl=0x7f161012f9a8, filesort_result=0x7f1610131e00) at /home/alice/git/10.2/sql/sql_window.cc:2742 #9 0x000055e10cebef35 in Window_funcs_sort::exec (this=0x7f161012dc88, join=0x7f1610016620) at /home/alice/git/10.2/sql/sql_window.cc:2767 #10 0x000055e10cebf3f7 in Window_funcs_computation::exec (this=0x7f161012dc68, join=0x7f1610016620) at /home/alice/git/10.2/sql/sql_window.cc:2886 #11 0x000055e10cd7a2c8 in AGGR_OP::end_send (this=0x7f1610018390) at /home/alice/git/10.2/sql/sql_select.cc:26607 #12 0x000055e10cd6548f in sub_select_postjoin_aggr (join=0x7f1610016620, join_tab=0x7f161012c6d0, end_of_records=true) at /home/alice/git/10.2/sql/sql_select.cc:18438 #13 0x000055e10cd657b3 in sub_select (join=0x7f1610016620, join_tab=0x7f161012c320, end_of_records=true) at /home/alice/git/10.2/sql/sql_select.cc:18674 #14 0x000055e10cd64fcb in do_select (join=0x7f1610016620, procedure=0x0) at /home/alice/git/10.2/sql/sql_select.cc:18269 #15 0x000055e10cd3fae7 in JOIN::exec_inner (this=0x7f1610016620) at /home/alice/git/10.2/sql/sql_select.cc:3595 #16 0x000055e10cd3ef96 in JOIN::exec (this=0x7f1610016620) at /home/alice/git/10.2/sql/sql_select.cc:3390 #17 0x000055e10cd40158 in mysql_select (thd=0x7f1610000b00, tables=0x7f1610014b50, wild_num=0, fields=..., conds=0x0, og_num=4, order=0x7f1610015280, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f1610016600, unit=0x7f16100046d8, select_lex=0x7f1610004e10) at /home/alice/git/10.2/sql/sql_select.cc:3790 #18 0x000055e10cd34674 in handle_select (thd=0x7f1610000b00, lex=0x7f1610004610, result=0x7f1610016600, setup_tables_done_option=0) at /home/alice/git/10.2/sql/sql_select.cc:376 #19 0x000055e10ccffe58 in execute_sqlcom_select (thd=0x7f1610000b00, all_tables=0x7f1610014b50) at /home/alice/git/10.2/sql/sql_parse.cc:6474 #20 0x000055e10ccf5b96 in mysql_execute_command (thd=0x7f1610000b00) at /home/alice/git/10.2/sql/sql_parse.cc:3481 #21 0x000055e10cd03bc9 in mysql_parse (thd=0x7f1610000b00, rawbuf=0x7f1610012478 "SELECT bin(Dv64),\nlag(TnNr) OVER (partition BY Victorys, Points ORDER BY Victorys DESC, Points DESC, TnNR),\nTnNr,\nVictorys,\nPoints,\n(Dv64 >> (lag(TnNr) OVER (partition BY Victorys, Points ORDER BY Vi"..., length=405, parser_state=0x7f165c152200, is_com_multi=false, is_next_command=false) at /home/alice/git/10.2/sql/sql_parse.cc:7995 #22 0x000055e10ccf1409 in dispatch_command (command=COM_QUERY, thd=0x7f1610000b00, packet=0x7f16100753d1 "", packet_length=406, is_com_multi=false, is_next_command=false) at /home/alice/git/10.2/sql/sql_parse.cc:1821 #23 0x000055e10ccefd65 in do_command (thd=0x7f1610000b00) at /home/alice/git/10.2/sql/sql_parse.cc:1375 #24 0x000055e10ce40b77 in do_handle_one_connection (connect=0x55e10f766ac0) at /home/alice/git/10.2/sql/sql_connect.cc:1335 #25 0x000055e10ce408f7 in handle_one_connection (arg=0x55e10f766ac0) at /home/alice/git/10.2/sql/sql_connect.cc:1241 #26 0x000055e10d66de6e in pfs_spawn_thread (arg=0x55e10f6ca680) at /home/alice/git/10.2/storage/perfschema/pfs.cc:1862 #27 0x00007f16637266ba in start_thread (arg=0x7f165c153700) at pthread_create.c:333 #28 0x00007f1662bbb41d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109
            alice Alice Sherepa added a comment -

            test case:

            CREATE TABLE t1 (id int NOT NULL);
            		 
             INSERT INTO t1 VALUES (1), (2), (3), (4);
            		 
             
            SELECT 1 FROM t1 
            ORDER BY lag(id) OVER (order by id) -1;

            alice Alice Sherepa added a comment - test case: CREATE TABLE t1 (id int NOT NULL ); INSERT INTO t1 VALUES (1), (2), (3), (4); SELECT 1 FROM t1 ORDER BY lag(id) OVER ( order by id) -1;
            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

            People

              varun Varun Gupta (Inactive)
              mauerparker mauerparker
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.