Bug #74462 EXPLAIN FORMAT=JSON produces ordering_operation when no ordering takes place.

• Submitted: 20 Oct 2014 19:26
• Modified: 1 Nov 2014 0:57
• Reporter: Sergey Petrunya
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.6.21
• OS: Any
• CPU Architecture: Any

[20 Oct 2014 19:26] Sergey Petrunya

Description:
MySQL optimizer is able to remove ORDER BY when the results are guaranteed to be ordered.
However, EXPLAIN FORMAT=JSON will still show "ordering_operation".

How to repeat:
create table t1 (a int, b int); 
insert into t1 values (1,1),(2,1),(3,1);
explain format=json select * from t1 where b=1 order by b\G

In this query, the optimizer recognizes that "where b=1" means that all output rows have the same value of b, and therefore "order by b" is automatically satisfied, without any sorting, etc.

However, EXPLAIN FORMAT=JSON shows:

*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "ordering_operation": {
      "using_filesort": false,
      "table": {
        "table_name": "t1",
        "access_type": "ALL",
        "rows": 3,
        "filtered": 100,
        "attached_condition": "(`test2`.`t1`.`b` = 1)"
      }
    }
  }
}

I think it's a bug, because no ordering takes place here.

[20 Oct 2014 20:11] MySQL Verification Team

Thank you for the bug report.

[1 Nov 2014 0:57] Sergey Petrunya

Another example:

explain format=json select max(col1) from t10 group by col1 order by null\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "ordering_operation": {
      "using_filesort": false,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": false,
        "table": {
          "table_name": "t10",
          "access_type": "ALL",
          "rows": 896709,
          "filtered": 100
        }
      }
    }
  }
}

"GROUP BY ... ORDER BY null" is the official recommended way to prevent the optimizer from sorting the output.  Yet, we still see an ordering_operation which is not doing any ordering.