CREATE TABLE acli (
  id bigint(20) NOT NULL,
  rid varchar(255) NOT NULL,
  tp smallint(6) NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY acli_rid (rid),
  KEY acli_tp (tp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into acli(id,rid,tp) values (184929059698905997,'ABABABABABABABABAB',103);
insert into acli(id,rid,tp) values (184929059698905998,'ABABABABABABABABAB',121);
insert into acli(id,rid,tp) values (283586039035985921,'00000000000000000000000000000000',103);
insert into acli(id,rid,tp) values (2216474704108064678,'020BED6D07B741CE9B10AB2200FEF1DF',103);
insert into acli(id,rid,tp) values (2216474704108064679,'020BED6D07B741CE9B10AB2200FEF1DF',121);
insert into acli(id,rid,tp) values (3080602882609775593,'B5FCC8C7111E4E3CBC21AAF5012F59C2',103);
insert into acli(id,rid,tp) values (3080602882609775594,'B5FCC8C7111E4E3CBC21AAF5012F59C2',121);
insert into acli(id,rid,tp) values (3080602882609776594,'B5FCC8C7111E4E3CBC21AAF5012F59C2',121);
insert into acli(id,rid,tp) values (3080602882609777595,'B5FCC8C7111E4E3CBC21AAF5012F59C2',121);
insert into acli(id,rid,tp) values (4269412446747236214,'SCSCSCSCSCSCSCSC',103);
insert into acli(id,rid,tp) values (4269412446747236215,'SCSCSCSCSCSCSCSC',121);
insert into acli(id,rid,tp) values (6341490487802728356,'6072D47E513F4A4794BBAB2200FDB67D',103);
insert into acli(id,rid,tp) values (6341490487802728357,'6072D47E513F4A4794BBAB2200FDB67D',121);


CREATE TABLE acei (
  id bigint(20) NOT NULL,
  aclid bigint(20) NOT NULL DEFAULT 0,
  atp smallint(6) NOT NULL DEFAULT 0,
  clus smallint(6) NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY acei_aclid (aclid),
  KEY acei_clus (clus)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into acei(id,aclid,atp,clus) values (184929059698905999,184929059698905997,0,1);
insert into acei(id,aclid,atp,clus) values (184929059698906000,184929059698905997,0,1);
insert into acei(id,aclid,atp,clus) values (184929059698906001,184929059698905997,1,1);
insert into acei(id,aclid,atp,clus) values (184929059698906002,184929059698905998,1,1);
insert into acei(id,aclid,atp,clus) values (283586039035985922,283586039035985921,1,1);
insert into acei(id,aclid,atp,clus) values (2216474704108064684,2216474704108064678,0,1);
insert into acei(id,aclid,atp,clus) values (2216474704108064685,2216474704108064678,0,1);
insert into acei(id,aclid,atp,clus) values (2216474704108064686,2216474704108064678,1,1);
insert into acei(id,aclid,atp,clus) values (2216474704108064687,2216474704108064679,1,1);
insert into acei(id,aclid,atp,clus) values (3080602882609775595,3080602882609775593,0,1);
insert into acei(id,aclid,atp,clus) values (3080602882609775596,3080602882609775593,0,1);
insert into acei(id,aclid,atp,clus) values (3080602882609775597,3080602882609775593,1,1);
insert into acei(id,aclid,atp,clus) values (3080602882609775598,3080602882609775594,1,1);
insert into acei(id,aclid,atp,clus) values (3080602882609776595,3080602882609776594,1,1);
insert into acei(id,aclid,atp,clus) values (3080602882609777596,3080602882609777595,1,1);
insert into acei(id,aclid,atp,clus) values (4269412446747236216,4269412446747236214,0,1);
insert into acei(id,aclid,atp,clus) values (4269412446747236217,4269412446747236214,0,1);
insert into acei(id,aclid,atp,clus) values (4269412446747236218,4269412446747236214,1,1);
insert into acei(id,aclid,atp,clus) values (4269412446747236219,4269412446747236215,1,1);
insert into acei(id,aclid,atp,clus) values (6341490487802728358,6341490487802728356,0,1);
insert into acei(id,aclid,atp,clus) values (6341490487802728359,6341490487802728356,0,1);
insert into acei(id,aclid,atp,clus) values (6341490487802728360,6341490487802728356,1,1);
insert into acei(id,aclid,atp,clus) values (6341490487802728361,6341490487802728357,1,1);


CREATE TABLE filt (
  id bigint(20) NOT NULL,
  aceid bigint(20) NOT NULL DEFAULT 0,
  clid smallint(6) NOT NULL DEFAULT 0,
  fh bigint(20) NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY filt_aceid (aceid),
  KEY filt_clid (clid),
  KEY filt_fh (fh)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert into filt(id,aceid,clid,fh) values (184929059698905999,184929059698905999,1,8948400944397203540);
insert into filt(id,aceid,clid,fh) values (184929059698906000,184929059698906000,1,-3516039679025944536);
insert into filt(id,aceid,clid,fh) values (184929059698906001,184929059698906001,1,-3516039679025944536);
insert into filt(id,aceid,clid,fh) values (184929059698906002,184929059698906001,1,2965370193075218252);
insert into filt(id,aceid,clid,fh) values (184929059698906003,184929059698906001,1,8948400944397203540);
insert into filt(id,aceid,clid,fh) values (184929059698906004,184929059698906002,1,2478709353550777738);
insert into filt(id,aceid,clid,fh) values (283586039035985922,283586039035985922,1,5902600816362013271);
insert into filt(id,aceid,clid,fh) values (2216474704108064686,2216474704108064684,1,8948400944397203540);
insert into filt(id,aceid,clid,fh) values (2216474704108064687,2216474704108064685,1,-7244708939311117030);
insert into filt(id,aceid,clid,fh) values (2216474704108064688,2216474704108064686,1,-7244708939311117030);
insert into filt(id,aceid,clid,fh) values (2216474704108064689,2216474704108064686,1,7489060986210282479);
insert into filt(id,aceid,clid,fh) values (2216474704108064690,2216474704108064686,1,8948400944397203540);
insert into filt(id,aceid,clid,fh) values (2216474704108064691,2216474704108064687,1,-3575268945274980038);
insert into filt(id,aceid,clid,fh) values (3080602882609775595,3080602882609775595,1,8948400944397203540);
insert into filt(id,aceid,clid,fh) values (3080602882609775596,3080602882609775596,1,-5420422472375069774);
insert into filt(id,aceid,clid,fh) values (3080602882609775597,3080602882609775597,1,-5420422472375069774);
insert into filt(id,aceid,clid,fh) values (3080602882609775598,3080602882609775597,1,8518228073041491534);
insert into filt(id,aceid,clid,fh) values (3080602882609775599,3080602882609775597,1,8948400944397203540);
insert into filt(id,aceid,clid,fh) values (3080602882609775600,3080602882609775598,1,6311439873746261694);
insert into filt(id,aceid,clid,fh) values (3080602882609776595,3080602882609776595,1,-661101805245999843);
insert into filt(id,aceid,clid,fh) values (3080602882609777596,3080602882609777596,1,-661101805245999843);
insert into filt(id,aceid,clid,fh) values (3080602882609777597,3080602882609777596,1,2216865386202464067);
insert into filt(id,aceid,clid,fh) values (4269412446747236216,4269412446747236216,1,8948400944397203540);
insert into filt(id,aceid,clid,fh) values (4269412446747236217,4269412446747236217,1,-1143096194892676000);
insert into filt(id,aceid,clid,fh) values (4269412446747236218,4269412446747236218,1,-1143096194892676000);
insert into filt(id,aceid,clid,fh) values (4269412446747236219,4269412446747236218,1,5313391811364818290);
insert into filt(id,aceid,clid,fh) values (4269412446747236220,4269412446747236218,1,8948400944397203540);
insert into filt(id,aceid,clid,fh) values (4269412446747236221,4269412446747236219,1,7624499822621753835);
insert into filt(id,aceid,clid,fh) values (6341490487802728358,6341490487802728358,1,8948400944397203540);
insert into filt(id,aceid,clid,fh) values (6341490487802728359,6341490487802728359,1,8141092449587136068);
insert into filt(id,aceid,clid,fh) values (6341490487802728360,6341490487802728360,1,8141092449587136068);
insert into filt(id,aceid,clid,fh) values (6341490487802728361,6341490487802728360,1,1291319099896431785);
insert into filt(id,aceid,clid,fh) values (6341490487802728362,6341490487802728360,1,8948400944397203540);
insert into filt(id,aceid,clid,fh) values (6341490487802728363,6341490487802728361,1,6701841652906431497);


-- query
-- 10.4.11 -> 1 result: 3080602882609775594
-- 10.4.12 -> 0 result !

select
	id
from (
	select
		t.id,
		max(a.clus) AS clus,
		fi.clid
	from
		(acli t inner join acei a on a.aclid = t.id) inner join filt fi on a.id = fi.aceid
	where
		t.rid = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and
		t.tp = 121 and
		a.atp = 1 and
		fi.fh in (6311439873746261694,-397087483897438286,8518228073041491534,-5420422472375069774)
	group by
		t.id, fi.clid
) AS b
group by id
having
	count(clid)>=max(clus)
