[MDEV-16736] crash on lag() over() and order not working Created: 2018-07-12  Updated: 2019-05-04  Resolved: 2019-05-04

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - Window functions
Affects Version/s: 10.2.14, 10.2, 10.3, 10.4
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: mauerparker Assignee: Varun Gupta (Inactive)
Resolution: Duplicate Votes: 0
Labels: None
Environment:

OpenSuse Leap 15

Attachments: Text File mysqld.log    
Issue Links:
Duplicate
is duplicated by MDEV-14791 Crash with order by expression contai... Closed
Relates
relates to MDEV-13649 Server crashes in set_field_to_null_w... Closed
relates to MDEV-15212 server crashed, query using ORDER BY ... Closed

 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
 
;



 Comments   
Comment by Alice Sherepa [ 2018-07-12 ]

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

Comment by Alice Sherepa [ 2018-07-13 ]

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;

Comment by Varun Gupta (Inactive) [ 2019-04-12 ]

Looks like a duplicate of MDEV-14791

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