[MDEV-10844] EXPLAIN FORMAT=JSON doesn't show order direction for filesort Created: 2016-09-20  Updated: 2017-12-30  Resolved: 2017-12-30

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.2
Fix Version/s: 10.2.12

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Varun Gupta (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Sprint: 10.2.12

 Description    

create table t1 (a int);
 
insert into t1 values (1),(3),(2);

Compare 

explain format=json select * from t1 order by a;
 
EXPLAIN
 
{
 
  "query_block": {
 
    "select_id": 1,
 
    "read_sorted_file": {
 
      "filesort": {
 
        "sort_key": "t1.a",
 
        "table": {
 
          "table_name": "t1",
 
          "access_type": "ALL",
 
          "rows": 3,
 
          "filtered": 100
 
        }
 
      }
 
    }
 
  }
 
}

with

explain format=json select * from t1 order by a desc;
 
EXPLAIN
 
{
 
  "query_block": {
 
    "select_id": 1,
 
    "read_sorted_file": {
 
      "filesort": {
 
        "sort_key": "t1.a",
 
        "table": {
 
          "table_name": "t1",
 
          "access_type": "ALL",
 
          "rows": 3,
 
          "filtered": 100
 
        }
 
      }
 
    }
 
  }
 
}

It would be nice to print ASC or DESC somehow. Maybe, only print DESC, because ASC is default so for the sake of brevity it should be omitted.

 Comments   
Comment by Varun Gupta (Inactive) [ 2017-12-20 ]

psergey What should we do when we have multiple sort keys and multiple orders associated with them

An example would be 

create table t1 ( a int , b int);
 
insert into t1 values (1,2) , (3,4) , (2,3);
 
select * from t1 order by a asc, b desc;

Should we attach the ASC/DESC associated with the first key in the sort order?
Attaching ASC/DESC for all sort keys does not make sense to me

Comment by Sergei Petrunia [ 2017-12-22 ]

I think, the output should be like 

      "filesort": {
 
        "sort_key": "t1.a desc, t1.b",
 
        ...

Comment by Sergei Petrunia [ 2017-12-27 ]

Ok to push.

Generated at Thu Feb 08 07:45:21 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.