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

Analyze format=json gives incorrect value for r_limit inside a dependent subquery when ORDER BY is present

    XMLWordPrintable

Details

    Description

      The test is

      --source include/have_innodb.inc
      create table t1(a int,  key(a))engine=innodb;
      insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
      create table t2(
      id int primary key,
      key1 int,
      col1 int,
      key(key1))engine=innodb;
      insert into t2 select A.a + B.a*10 + C.a*100 + D.a* 1000,A.a + 10*B.a, 123456
      from t1 A, t1 B, t1 C, t1 D;
       
      alter table t2 add key2 int;
      update t2 set key2=key1;
      alter table t2 add key(key2);
      analyze table t2;
      flush tables;
      analyze table t2;
      flush tables;
       
      analyze format=json select
      (SELECT
      concat(id, '-', key1, '-', col1)
      FROM t2
      WHERE t2.key1 = t1.a
      ORDER BY t2.key2 ASC limit 1)
      from
      t1;
      

      The output for analyze format=json is

      ANALYZE
      {
        "query_block": {
          "select_id": 1,
          "r_loops": 1,
          "r_total_time_ms": 852.7,
          "table": {
            "table_name": "t1",
            "access_type": "index",
            "key": "a",
            "key_length": "5",
            "used_key_parts": ["a"],
            "r_loops": 1,
            "rows": 10,
            "r_rows": 10,
            "r_total_time_ms": 2.819,
            "filtered": 100,
            "r_filtered": 100,
            "using_index": true
          },
          "subqueries": [
            {
              "expression_cache": {
                "r_loops": 10,
                "r_hit_ratio": 0,
                "query_block": {
                  "select_id": 2,
                  "r_loops": 10,
                  "r_total_time_ms": 848.11,
                  "outer_ref_condition": "t1.a is not null",
                  "read_sorted_file": {
                    "r_rows": 1,
                    "filesort": {
                      "sort_key": "t2.key2",
                      "r_loops": 10,
                      "r_total_time_ms": 844.61,
                      "r_limit": 0,
                      "r_used_priority_queue": true,
                      "r_output_rows": 2,
                      "table": {
                        "table_name": "t2",
                        "access_type": "ref",
                        "possible_keys": ["key1"],
                        "key": "key1",
                        "key_length": "5",
                        "used_key_parts": ["key1"],
                        "ref": ["test.t1.a"],
                        "r_loops": 10,
                        "rows": 48,
                        "r_rows": 100,
                        "r_total_time_ms": 831.09,
                        "filtered": 100,
                        "r_filtered": 1,
                        "attached_condition": ""
                      }
                    }
                  }
                }
              }
            }
          ]
        }
      }
      

      So main concern is that r_limit=0 in filesort, inside the subquery block.
      The r_loops=10 for filesort block shows that we execute the subquery 10 times and perform filesort each time but somehow the value for r_limit is not set correctly.

      Attachments

        Activity

          People

            varun Varun Gupta (Inactive)
            varun Varun Gupta (Inactive)
            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.