[MDEV-21610] Different query results from 10.4.11 to 10.4.12 - Jira

SuperC created issue - 2020-01-30 16:05

Elena Stepanova added a comment - 2020-01-30 16:21

Could you please also attach or paste your config file(s)?

Elena Stepanova added a comment - 2020-01-30 16:21 Could you please also attach or paste your config file(s)?

Elena Stepanova made changes - 2020-01-30 16:21

Field	Original Value	New Value
Labels		need_feedback

SuperC made changes - 2020-01-30 16:35

Attachment

settings.txt [ 50371 ]

SuperC added a comment - 2020-01-30 16:36

Relevant server settings attached

SuperC added a comment - 2020-01-30 16:36 Relevant server settings attached

Elena Stepanova made changes - 2020-01-30 16:39

Assignee

Elena Stepanova [ elenst ]

Elena Stepanova made changes - 2020-01-30 16:39

Labels

need_feedback

Elena Stepanova added a comment - 2020-01-30 16:43

Thanks. Meanwhile, for a workaround, you can try to either change join_cache_level to 4 or lower, or remove mrr settings from the optimizer switch. I think either should help, but please try (you can do it locally in a connection before applying it globally through the config file).

Elena Stepanova added a comment - 2020-01-30 16:43 Thanks. Meanwhile, for a workaround, you can try to either change join_cache_level to 4 or lower, or remove mrr settings from the optimizer switch. I think either should help, but please try (you can do it locally in a connection before applying it globally through the config file).

Elena Stepanova added a comment - 2020-01-30 17:26

The failure appeared in 10.4 branch after this commit:

commit 4de32015be82d0f484a7b49a427853ea7b6da5fd

Author: Igor Babaev

Date:   Sat Jan 18 13:26:03 2020 -0800

    MDEV-21356 ERROR 1032 Can't find record when running simple, single-table query

Exact same test case as attached, only in MTR-friendly form.

--source include/have_innodb.inc

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);

set optimizer_switch = 'mrr=on,mrr_sort_keys=on,optimize_join_buffer_size=on';

set join_cache_level= 6;

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)

drop table filt, acei, acli;

Actual result
id

Result before the change
id
3080602882609775594

Elena Stepanova added a comment - 2020-01-30 17:26 The failure appeared in 10.4 branch after this commit: commit 4de32015be82d0f484a7b49a427853ea7b6da5fd Author: Igor Babaev Date: Sat Jan 18 13:26:03 2020 -0800 MDEV-21356 ERROR 1032 Can't find record when running simple, single-table query Exact same test case as attached, only in MTR-friendly form. --source include/have_innodb.inc 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); set optimizer_switch = 'mrr=on,mrr_sort_keys=on,optimize_join_buffer_size=on' ; set join_cache_level= 6; 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) ; drop table filt, acei, acli; Actual result id Result before the change id 3080602882609775594

Elena Stepanova made changes - 2020-01-30 17:26

Fix Version/s		10.4 [ 22408 ]
Assignee	Elena Stepanova [ elenst ]	Igor Babaev [ igor ]
Labels		regression
Priority	Blocker [ 1 ]	Critical [ 2 ]

Igor Babaev (Inactive) made changes - 2020-02-11 06:30

Priority

Critical [ 2 ]

Major [ 3 ]

Igor Babaev (Inactive) made changes - 2020-02-11 06:35

Comment

[ SuperC,

Please provide output of EXPLAIN EXTENDED for your query. ]

Igor Babaev (Inactive) made changes - 2020-02-11 06:35

Comment

[ SuperC

Please provide output of EXPLAIN EXTENDED for your query.
]

Igor Babaev (Inactive) made changes - 2020-02-11 06:48

Status

Open [ 1 ]

In Progress [ 3 ]

Igor Babaev (Inactive) made changes - 2020-02-11 07:17

Comment

[ Hi SuperC,

Please provide output of EXPLAIN EXTENDED for your query. ]

SuperC made changes - 2020-02-11 09:00

Attachment

explain.txt [ 50457 ]

SuperC added a comment - 2020-02-11 09:10

Just for the record, the plan is identical in both versions

SuperC added a comment - 2020-02-11 09:10 Just for the record, the plan is identical in both versions

Igor Babaev (Inactive) added a comment - 2020-02-11 23:08

This problem can be already seen with the following settings and query

set optimizer_switch = 'mrr=on';

set join_cache_level=6;

select t.id, 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);

MariaDB [test]> set optimizer_switch='rowid_filter=on';                                                                 Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> set optimizer_switch = 'mrr=on';

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> set optimizer_switch='rowid_filter=off';

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> select t.id, 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);

+---------------------+------+

| id                  | clid |

+---------------------+------+

| 3080602882609775594 |    1 |

+---------------------+------+

1 row in set (0.002 sec)

MariaDB [test]> set optimizer_switch='rowid_filter=on';

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> select t.id, 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);

Empty set (0.003 sec)

Igor Babaev (Inactive) added a comment - 2020-02-11 23:08 This problem can be already seen with the following settings and query set optimizer_switch = 'mrr=on' ; set join_cache_level=6; select t.id, 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); MariaDB [test]> set optimizer_switch='rowid_filter=on'; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> set optimizer_switch = 'mrr=on'; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> set optimizer_switch='rowid_filter=off'; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> select t.id, 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); +---------------------+------+ | id | clid | +---------------------+------+ | 3080602882609775594 | 1 | +---------------------+------+ 1 row in set (0.002 sec) MariaDB [test]> set optimizer_switch='rowid_filter=on'; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> select t.id, 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); Empty set (0.003 sec)

Igor Babaev (Inactive) added a comment - 2020-02-12 01:10

If I set a break point in Range_rowid_filter::fill() I see that it's never reached. It means that no rowid filter is ever built though the execution plan shows that such filter has to be used when accessing table fi:

MariaDB [test]> explain extended

    -> select t.id, 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);

+------+-------------+-------+-------------+--------------------------+--------------------+---------+-----------+---------+----------+------------------------------------------------------------------------------------------------+

| id   | select_type | table | type        | possible_keys            | key                | key_len | ref       | rows    | filtered | Extra                                                                                          |

+------+-------------+-------+-------------+--------------------------+--------------------+---------+-----------+---------+----------+------------------------------------------------------------------------------------------------+

|    1 | SIMPLE      | t     | index_merge | PRIMARY,acli_rid,acli_tp | acli_tp,acli_rid   | 2,767   | NULL      | 2       |   100.00 | Using intersect(acli_tp,acli_rid); Using where; Using index                                    |

|    1 | SIMPLE      | a     | ref         | PRIMARY,acei_aclid       | acei_aclid         | 8       | test.t.id | 1       |   100.00 | Using where; Using join buffer (flat, BKA join); Rowid-ordered scan                            |

|    1 | SIMPLE      | fi    | ref|filter  | filt_aceid,filt_fh       | filt_aceid|filt_fh | 8|8     | test.a.id | 1 (15%) |    14.71 | Using where; Using join buffer (incremental, BKA join); Rowid-ordered scan; Using rowid filter |

+------+-------------+-------+-------------+--------------------------+--------------------+---------+-----------+---------+----------+------------------------------------------------------------------------------------------------+

ANALYZE FORMAT=JSON for the query confirms that no rowid filter is built:

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| {

  "query_block": {

    "select_id": 1,

    "r_loops": 1,

    "r_total_time_ms": 1.3081,

    "table": {

      "table_name": "t",

      "access_type": "index_merge",

      "possible_keys": ["PRIMARY", "acli_rid", "acli_tp"],

      "key_length": "2,767",

      "index_merge": {

        "intersect": {

          "range": {

            "key": "acli_tp",

            "used_key_parts": ["tp"]

},

          "range": {

            "key": "acli_rid",

            "used_key_parts": ["rid"]

},

      "r_loops": 1,

      "rows": 2,

      "r_rows": 3,

      "r_total_time_ms": 0.1324,

      "filtered": 100,

      "r_filtered": 100,

      "attached_condition": "t.tp = 121 and t.rid = 'B5FCC8C7111E4E3CBC21AAF5012F59C2'",

      "using_index": true

},

    "block-nl-join": {

      "table": {

        "table_name": "a",

        "access_type": "ref",

        "possible_keys": ["PRIMARY", "acei_aclid"],

        "key": "acei_aclid",

        "key_length": "8",

        "used_key_parts": ["aclid"],

        "ref": ["test.t.id"],

        "r_loops": 1,

        "rows": 1,

        "r_rows": 3,

        "r_total_time_ms": 0.196,

        "filtered": 100,

        "r_filtered": 100

},

      "buffer_type": "flat",

      "buffer_size": "8Kb",

      "join_type": "BKA",

      "mrr_type": "Rowid-ordered scan",

      "attached_condition": "a.atp = 1",

      "r_filtered": 100

},

    "block-nl-join": {

      "table": {

        "table_name": "fi",

        "access_type": "ref",

        "possible_keys": ["filt_aceid", "filt_fh"],

        "key": "filt_aceid",

        "key_length": "8",

        "used_key_parts": ["aceid"],

        "ref": ["test.a.id"],

        "rowid_filter": {

          "range": {

            "key": "filt_fh",

            "used_key_parts": ["fh"]

},

          "rows": 5,

          "selectivity_pct": 14.706,

          "r_rows": 0,

          "r_selectivity_pct": 0,

          "r_buffer_size": 0,

          "r_filling_time_ms": 0

},

        "r_loops": 1,

        "rows": 1,

        "r_rows": 0,

        "filtered": 14.706,

        "r_filtered": 100

},

      "buffer_type": "incremental",

      "buffer_size": "603",

      "join_type": "BKA",

      "mrr_type": "Rowid-ordered scan",

      "attached_condition": "fi.fh in (6311439873746261694,-397087483897438286,8518228073041491534,-5420422472375069774)",

      "r_filtered": null

} |

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

In the above JSON expression I don't see any sub-expression related to filter building.
This is another bug and in 10.4.12 after the patch for ~~MDEV-21356~~ this bug manifests itself by returning wrong result set for the above query.

Igor Babaev (Inactive) added a comment - 2020-02-12 01:10 If I set a break point in Range_rowid_filter::fill() I see that it's never reached. It means that no rowid filter is ever built though the execution plan shows that such filter has to be used when accessing table fi: MariaDB [test]> explain extended -> select t.id, 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); +------+-------------+-------+-------------+--------------------------+--------------------+---------+-----------+---------+----------+------------------------------------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+-------------+--------------------------+--------------------+---------+-----------+---------+----------+------------------------------------------------------------------------------------------------+ | 1 | SIMPLE | t | index_merge | PRIMARY,acli_rid,acli_tp | acli_tp,acli_rid | 2,767 | NULL | 2 | 100.00 | Using intersect(acli_tp,acli_rid); Using where; Using index | | 1 | SIMPLE | a | ref | PRIMARY,acei_aclid | acei_aclid | 8 | test.t.id | 1 | 100.00 | Using where; Using join buffer (flat, BKA join); Rowid-ordered scan | | 1 | SIMPLE | fi | ref|filter | filt_aceid,filt_fh | filt_aceid|filt_fh | 8|8 | test.a.id | 1 (15%) | 14.71 | Using where; Using join buffer (incremental, BKA join); Rowid-ordered scan; Using rowid filter | +------+-------------+-------+-------------+--------------------------+--------------------+---------+-----------+---------+----------+------------------------------------------------------------------------------------------------+ ANALYZE FORMAT=JSON for the query confirms that no rowid filter is built: +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 1.3081, "table": { "table_name": "t", "access_type": "index_merge", "possible_keys": ["PRIMARY", "acli_rid", "acli_tp"], "key_length": "2,767", "index_merge": { "intersect": { "range": { "key": "acli_tp", "used_key_parts": ["tp"] }, "range": { "key": "acli_rid", "used_key_parts": ["rid"] } } }, "r_loops": 1, "rows": 2, "r_rows": 3, "r_total_time_ms": 0.1324, "filtered": 100, "r_filtered": 100, "attached_condition": "t.tp = 121 and t.rid = 'B5FCC8C7111E4E3CBC21AAF5012F59C2'", "using_index": true }, "block-nl-join": { "table": { "table_name": "a", "access_type": "ref", "possible_keys": ["PRIMARY", "acei_aclid"], "key": "acei_aclid", "key_length": "8", "used_key_parts": ["aclid"], "ref": ["test.t.id"], "r_loops": 1, "rows": 1, "r_rows": 3, "r_total_time_ms": 0.196, "filtered": 100, "r_filtered": 100 }, "buffer_type": "flat", "buffer_size": "8Kb", "join_type": "BKA", "mrr_type": "Rowid-ordered scan", "attached_condition": "a.atp = 1", "r_filtered": 100 }, "block-nl-join": { "table": { "table_name": "fi", "access_type": "ref", "possible_keys": ["filt_aceid", "filt_fh"], "key": "filt_aceid", "key_length": "8", "used_key_parts": ["aceid"], "ref": ["test.a.id"], "rowid_filter": { "range": { "key": "filt_fh", "used_key_parts": ["fh"] }, "rows": 5, "selectivity_pct": 14.706, "r_rows": 0, "r_selectivity_pct": 0, "r_buffer_size": 0, "r_filling_time_ms": 0 }, "r_loops": 1, "rows": 1, "r_rows": 0, "filtered": 14.706, "r_filtered": 100 }, "buffer_type": "incremental", "buffer_size": "603", "join_type": "BKA", "mrr_type": "Rowid-ordered scan", "attached_condition": "fi.fh in (6311439873746261694,-397087483897438286,8518228073041491534,-5420422472375069774)", "r_filtered": null } } } | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ In the above JSON expression I don't see any sub-expression related to filter building. This is another bug and in 10.4.12 after the patch for MDEV-21356 this bug manifests itself by returning wrong result set for the above query.

Sergei Petrunia made changes - 2020-02-14 08:03

Assignee

Igor Babaev [ igor ]

Sergei Petrunia [ psergey ]

Sergei Petrunia made changes - 2020-02-14 08:03

Status

In Progress [ 3 ]

In Review [ 10002 ]

Sergei Petrunia made changes - 2020-02-25 11:12

Assignee	Sergei Petrunia [ psergey ]	Igor Babaev [ igor ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Sergei Petrunia made changes - 2020-02-25 11:12

Fix Version/s		10.4.13 [ 24223 ]
Fix Version/s	10.4 [ 22408 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Petrunia made changes - 2020-02-25 11:13

Assignee

Igor Babaev [ igor ]

Sergei Petrunia [ psergey ]

Sergei Golubchik made changes - 2021-12-06 21:50

Workflow

MariaDB v3 [ 103533 ]

MariaDB v4 [ 157266 ]

MariaDB Server

Different query results from 10.4.11 to 10.4.12

Details

Description

Attachments

Attachments

Activity

People

Dates

Git Integration