Bug #72031 Strange explain behaviour
Submitted: 13 Mar 2014 8:22 Modified: 13 Mar 2014 11:14
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.6.16 OS:Any
Assigned to: CPU Architecture:Any
Tags: explain

[13 Mar 2014 8:22] Daniël van Eeden
Description:
Sometimes the first explain for a query works, but the second one fails.

mysql> EXPLAIN FORMAT=JSON INSERT INTO t1(c1,c2) SELECT a1.c1, a1.c2 FROM t1 a1 LEFT JOIN t1 a2 ON a1.c1=a2.c1 and a1.c1=@foo JOIN t2 on t2.c1=@foo WHERE t2.c1=@bar\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "buffer_result": {
      "using_temporary_table": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "a1",
            "access_type": "ALL",
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "a2",
            "access_type": "ref",
            "possible_keys": [
              "c1"
            ],
            "key": "c1",
            "used_key_parts": [
              "c1"
            ],
            "key_length": "5",
            "ref": [
              "test.a1.c1"
            ],
            "rows": 1,
            "filtered": 100,
            "using_index": true,
            "attached_condition": "<if>(is_not_null_compl(a2), (`test`.`a1`.`c1` = (@`foo`)), true)"
          }
        },
        {
          "table": {
            "table_name": "t2",
            "access_type": "index",
            "key": "c1",
            "used_key_parts": [
              "c1"
            ],
            "key_length": "5",
            "rows": 1,
            "filtered": 100,
            "using_index": true,
            "using_join_buffer": "Block Nested Loop",
            "attached_condition": "((`test`.`t2`.`c1` = (@`bar`)) and (`test`.`t2`.`c1` = (@`foo`)))"
          }
        }
      ]
    }
  }
}
1 row in set (0.00 sec)

mysql> EXPLAIN FORMAT=JSON INSERT INTO t1(c1,c2) SELECT a1.c1, a1.c2 FROM t1 a1 LEFT JOIN t1 a2 ON a1.c1=a2.c1 and a1.c1=@foo JOIN t2 on t2.c1=@foo WHERE t2.c1=@bar\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "table": {
      "message": "Impossible WHERE noticed after reading const tables"
    }
  }
}
1 row in set (0.00 sec)

How to repeat:
CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  KEY `c1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `t2` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  KEY `c1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

EXPLAIN FORMAT=JSON INSERT INTO t1(c1,c2) SELECT a1.c1, a1.c2 FROM t1 a1 LEFT JOIN t1 a2 ON a1.c1=a2.c1 and a1.c1=@foo JOIN t2 on t2.c1=@foo WHERE t2.c1=@bar;
[13 Mar 2014 9:32] Umesh Shastry
Hello Daniel,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh
[13 Mar 2014 11:14] Daniël van Eeden
added explain tag