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

;