MySQL Bugs: #74820: Warning for explain breaks explain extended

Bug #74820	Warning for explain breaks explain extended
Submitted:	12 Nov 2014 14:24	Modified:	13 Nov 2014 5:37
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	5.6.16, 5.6.23	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
explain can cause errors and/or warnings. This is unexpected and causes issues with the explain extended feature. It also causes errors in mysql enterprise monitor agent 3.0.

How to repeat:
create table t1 with some data;
create table t2 with a column d with no default value.

EXPLAIN extended INSERT INTO t2(a, b, c) SELECT a, b, c FROM t1;
EXPLAIN FORMAT=JSON INSERT INTO t2(a, b, c) SELECT a, b, c FROM t1;

mysql> show warnings;
+---------+------+--------------------------------------------------------+
| Level   | Code | Message                                                |
+---------+------+--------------------------------------------------------+
| Warning | 1364 | Field 'xxxx' doesn't have a default value |
+---------+------+--------------------------------------------------------+
1 row in set (0.00 sec)

From the MEM agent:
java.sql.SQLException: Field 'xxxxx' doesn't have a default value
org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [EXPLAIN FORMAT=JSON INSERT INTO
...
SQL state [HY000]; error code [1364]; Field 'xxxx' doesn't have a default value; nested exception is
java.sql.SQLException: Field 'xxxxx' doesn't have a default value

Hello Daniël,

Thank you for the bug report.

Thanks,
Umesh

// 5.6.23

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.6.23                                                  |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.6.23-enterprise-commercial-advanced                   |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | Linux                                                   |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

mysql> create table t1(a int not null, b int not null, c int not null);
Query OK, 0 rows affected (0.06 sec)

mysql> create table t2(a int not null, b int not null, c int not null, d int not null);
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> insert into t1 values(1,1,1),(2,2,2),(3,3,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> EXPLAIN extended INSERT INTO t2(a, b, c) SELECT a, b, c FROM t1;
ERROR 1364 (HY000): Field 'd' doesn't have a default value
mysql>
mysql> EXPLAIN FORMAT=JSON INSERT INTO t2(a, b, c) SELECT a, b, c FROM t1;
ERROR 1364 (HY000): Field 'd' doesn't have a default value
mysql>

// 5.7.6 is not affected

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.7.6                                                   |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.7.6-m16-enterprise-commercial-advanced                |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | Linux                                                   |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

mysql> create table t1(a int not null, b int not null, c int not null);
Query OK, 0 rows affected (0.03 sec)

mysql> create table t2(a int not null, b int not null, c int not null, d int not null);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values(1,1,1),(2,2,2),(3,3,3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> EXPLAIN extended INSERT INTO t2(a, b, c) SELECT a, b, c FROM t1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | INSERT      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | NULL  |
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
2 rows in set, 1 warning (0.03 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------------+
| Level   | Code | Message                                                           |
+---------+------+-------------------------------------------------------------------+
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release. |
+---------+------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN FORMAT=JSON INSERT INTO t2(a, b, c) SELECT a, b, c FROM t1\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.60"
    },
    "table": {
      "insert": true,
      "table_name": "t2",
      "access_type": "ALL"
    },
    "insert_from": {
      "table": {
        "table_name": "t1",
        "access_type": "ALL",
        "rows_examined_per_scan": 3,
        "rows_produced_per_join": 3,
        "filtered": 100,
        "cost_info": {
          "read_cost": "1.00",
          "eval_cost": "0.60",
          "prefix_cost": "1.60",
          "data_read_per_join": "48"
        },
        "used_columns": [
          "a",
          "b",
          "c"
        ]
      }
    }
  }
}
1 row in set (0.00 sec)