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

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

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

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.

MariaDB Server

Different query results from 10.4.11 to 10.4.12

Details

Description

Attachments

Attachments

Activity

People

Dates

Git Integration