Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8840

ANALYZE FORMAT=JSON produces wrong data with BKA

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.1.8
    • 10.1.27
    • Optimizer
    • None
    • 10.1.9-1, 10.1.9-2, 10.2.4-1, 10.2.4-2, 10.1.22

    Description

      ANALYZE FORMAT=JSON may produce wrong incorrect runtime information for queries using BKA.

      create table t1(a int);
      insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
       
      create table t2(a int);
      insert into t2 select A.a + B.a* 10 + C.a * 100 from t1 A, t1 B, t1 C;
       
      create table t3(a int, b int);
      insert into t3 select a,a from t1;
       
      create table t4(a int, b int, c int, filler char(100), key (a,b));
      insert into t4 select a,a,a, 'filler-data' from t2;

      set optimizer_switch='mrr=on';
      set join_cache_level=6;
      explain
      select * from t3,t4 where t3.a=t4.a and (t4.b+1 <= t3.b+1);
      +------+-------------+-------+------+---------------+------+---------+---------+------+------------------------------------------------------------------------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref     | rows | Extra                                                                              |
      +------+-------------+-------+------+---------------+------+---------+---------+------+------------------------------------------------------------------------------------+
      |    1 | SIMPLE      | t3    | ALL  | NULL          | NULL | NULL    | NULL    |   10 | Using where                                                                        |
      |    1 | SIMPLE      | t4    | ref  | a             | a    | 5       | j2.t3.a |    1 | Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan |
      +------+-------------+-------+------+---------------+------+---------+---------+------+------------------------------------------------------------------------------------+

       {
        "query_block": {
          "select_id": 1,
          "r_loops": 1,
          "r_total_time_ms": 3.0276,
          "table": {
            "table_name": "t3",
            "access_type": "ALL",
            "r_loops": 1,
            "rows": 10,
            "r_rows": 10,
            "r_total_time_ms": 0.5097,
            "filtered": 100,
            "r_filtered": 100,
            "attached_condition": "(t3.a is not null)"
          },
          "block-nl-join": {
            "table": {
              "table_name": "t4",
              "access_type": "ref",
              "possible_keys": ["a"],
              "key": "a",
              "key_length": "5",
              "used_key_parts": ["a"],
              "ref": ["j2.t3.a"],
              "r_loops": 0,
              "rows": 1,
              "r_rows": null,
              "r_total_time_ms": 0.8301,
              "filtered": 100,
              "r_filtered": null,
              "index_condition_bka": "((t4.b + 1) <= (t3.b + 1))"
            },
            "buffer_type": "flat",
            "buffer_size": "256Kb",
            "join_type": "BKA",
            "mrr_type": "Rowid-ordered scan",
            "r_filtered": 100
          }
        }
      }

      Note that the second table has r_rows=null and r_filtered=null. We also get r_loops=0 (which means "never executed") although "r_total_time_ms": 0.8301, which means we spent time in this node.

      Attachments

        Activity

          People

            varun Varun Gupta (Inactive)
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.