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

EXPLAIN FORMAT=JSON doesn't show order direction for filesort

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.2(EOL)
    • 10.2.12
    • Optimizer
    • None
    • 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.

      Attachments

        Activity

          Descending order - Click to sort in ascending order
          psergei Sergei Petrunia added a comment -

          Ok to push.

          psergei Sergei Petrunia added a comment - Ok to push.
          psergei Sergei Petrunia added a comment - - edited

          I think, the output should be like 

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

          psergei Sergei Petrunia added a comment - - edited I think, the output should be like "filesort": { "sort_key": "t1.a desc, t1.b", ...
          varun Varun Gupta (Inactive) added a comment - - edited

          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

          varun Varun Gupta (Inactive) added a comment - - edited 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

          People

            varun Varun Gupta (Inactive)
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.