[MDEV-21610] Different query results from 10.4.11 to 10.4.12 Created: 2020-01-30  Updated: 2020-02-25  Resolved: 2020-02-25

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery
Affects Version/s: 10.4.12
Fix Version/s: 10.4.13

Type: Bug Priority: Major
Reporter: SuperC Assignee: Sergei Petrunia
Resolution: Fixed Votes: 1
Labels: regression
Environment:

Fedora 31 x86_64

Attachments: File data_definition.sql     Text File explain.txt     Text File settings.txt    

 Description   

After upgrade from 10.4.11 to 10.4.12 the result of a query has changed:
10.4.11 -> returns 1 record
10.4.12 -> returns 0 record!

This is very critical for me as it distorts behavior of entire application.
I've attached scripts to reproduce behavior.

 Comments   
Comment by Elena Stepanova [ 2020-01-30 ]

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

Comment by SuperC [ 2020-01-30 ]

Relevant server settings attached

Comment by Elena Stepanova [ 2020-01-30 ]

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

Comment by Elena Stepanova [ 2020-01-30 ]

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

Comment by SuperC [ 2020-02-11 ]

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

Comment by Igor Babaev [ 2020-02-11 ]

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)

Comment by Igor Babaev [ 2020-02-12 ]

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.

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