[MDEV-29228] Odd EXPLAIN FORMAT=JSON output for SELECT DISTINCT ... GROUP BY query Created: 2022-08-01  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-23809 Server crash in JOIN_CACHE::free or i... Closed

 Description    

create table t2 (a int, b int);
 
insert into t2 select seq/100, seq from seq_1_to_1000;
 
explain format=json select distinct 1+max(b) from t2 group by a;

gives

  "query_block": {
 
    "select_id": 1,
 
    "filesort": {
 
      "sort_key": "t2.a",
 
      "duplicate_removal": {
 
        "temporary_table": {
 
          "temporary_table": {
 
            "nested_loop": [
 
              {
 
                "table": {
 
                  "table_name": "t2",
 
                  "access_type": "ALL",
 
                  "rows": 1000,
 
                  "filtered": 100
 
                }
 
              }
 
            ]
 
          }
 
        }
 
      }
 
    }
 
  }
 
}

note the 

        "temporary_table": {
 
          "temporary_table": {

part.

This doesn't allow one to understand the purpose of the temporary tables.

 Comments   
Comment by Sergei Petrunia [ 2022-08-01 ]

ANALYZE FORMAT=JSON output:

{
 
  "query_block": {
 
    "select_id": 1,
 
    "r_loops": 1,
 
    "r_total_time_ms": 10.79523996,
 
    "filesort": {
 
      "sort_key": "t2.a",
 
      "r_loops": 1,
 
      "r_total_time_ms": 0.021792863,
 
      "r_used_priority_queue": false,
 
      "r_output_rows": 11,
 
      "r_buffer_size": "448",
 
      "r_sort_mode": "sort_key,rowid",
 
      "duplicate_removal": {
 
        "temporary_table": {
 
          "temporary_table": {
 
            "nested_loop": [
 
              {
 
                "table": {
 
                  "table_name": "t2",
 
                  "access_type": "ALL",
 
                  "r_loops": 1,
 
                  "rows": 1000,
 
                  "r_rows": 1000,
 
                  "r_table_time_ms": 8.281793658,
 
                  "r_other_time_ms": 2.431022542,
 
                  "filtered": 100,
 
                  "r_filtered": 100
 
                }
 
              }
 
            ]
 
          }
 
        }
 
      }
 
    }
 
  }
 
}

Generated at Thu Feb 08 10:06:53 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.