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

ANALYZE FORMAT=JSON SELECT .. UNION SELECT doesn't print r_rows for union output.

    XMLWordPrintable

    Details

      Description

      create table t0 (a int) engine=myisam;
      INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
       
      create table t1 (a int) engine=myisam;
      INSERT INTO t1 select * from t0;

      MariaDB [j2]> analyze (select * from t1 A where a<5) union (select * from t1 B where a in (2,3));
      +------+--------------+------------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
      | id   | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra       |
      +------+--------------+------------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
      |    1 | PRIMARY      | A          | ALL  | NULL          | NULL | NULL    | NULL |   10 |  10.00 |   100.00 |      50.00 | Using where |
      |    2 | UNION        | B          | ALL  | NULL          | NULL | NULL    | NULL |   10 |  10.00 |   100.00 |      20.00 | Using where |
      | NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL | NULL |   5.00 |     NULL |       NULL |             |
      +------+--------------+------------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+

      So far, ok. Note that we can see how many rows were in the union's result: r_rows=5. Now, let's try to get even more info and run ANALYZE FORMAT=JSON:

      MariaDB [j2]> analyze format=json (select * from t1 A where a<5) union (select * from t1 B where a in (2,3))\G
      *************************** 1. row ***************************
      ANALYZE: {
        "query_block": {
          "union_result": {
            "table_name": "<union1,2>",
            "access_type": "ALL",
            "query_specifications": [
              {
                "query_block": {
                  "select_id": 1,
                  "r_loops": 1,
                  "r_total_time_ms": 0.584,
                  "table": {
                    "table_name": "A",
                    "access_type": "ALL",
                    "r_loops": 1,
                    "rows": 10,
                    "r_rows": 10,
                    "r_total_time_ms": 0.4224,
                    "filtered": 100,
                    "r_filtered": 50,
                    "attached_condition": "(A.a < 5)"
                  }
                }
              },
              {
                "query_block": {
                  "select_id": 2,
                  "r_loops": 1,
                  "r_total_time_ms": 0.382,
                  "table": {
                    "table_name": "B",
                    "access_type": "ALL",
                    "r_loops": 1,
                    "rows": 10,
                    "r_rows": 10,
                    "r_total_time_ms": 0.3193,
                    "filtered": 100,
                    "r_filtered": 20,
                    "attached_condition": "(B.a in (2,3))"
                  }
                }
              }
            ]
          }
        }
      }

      we can't see r_rows for union result.

        Attachments

          Activity

            People

            Assignee:
            psergey Sergei Petrunia
            Reporter:
            psergey Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: