MySQL Bugs: #89003: Dropping Index on JSON virtual column with default sql

Bug #89003	Dropping Index on JSON virtual column with default sql_mode is not working
Submitted:	21 Dec 2017 9:45	Modified:	21 Dec 2017 11:22
Reporter:	Oli Sennhauser	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.7.20	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	INDEX, json, SQL_MODE, virtual column

Description:
See also Bug https://bugs.mysql.com/bug.php?id=89002

When we have a table with json and virtual columns and we worked around the problem in #89002 we cannot drop the index any more.

How to repeat:
SELECT @@version, @@version_comment;
+------------+------------------------------+
| @@version  | @@version_comment            |
+------------+------------------------------+
| 5.7.20-log | MySQL Community Server (GPL) |
+------------+------------------------------+

SHOW GLOBAL VARIABLES LIKE 'sql_mode'\G
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

CREATE TABLE `BACKEND_INFO_DEVICE` (
  `ID` VARCHAR(255) NOT NULL,
  `BOX_ID` VARCHAR(255) NOT NULL,
  `DOC` JSON NOT NULL,
  `CREATED_AT` TIMESTAMP NULL DEFAULT NULL,
  `MODIFIED_AT` TIMESTAMP NULL DEFAULT NULL,
  `LAST_REQUEST_UTC` TIMESTAMP AS ((case when isnull(json_unquote(json_extract(`DOC`,'$.last_request'))) then '1980-01-01 00:00:00' else str_to_date(substring_index(json_unquote(json_extract(`DOC`,'$.last_request')),'+',1),'%Y-%m-%dT%H:%i:%s') end)) VIRTUAL,
PRIMARY KEY (`ID`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;
Query OK, 0 rows affected (0.08 sec)

ALTER TABLE `BACKEND_INFO_DEVICE` ADD INDEX `ix_bid_boxid` (`BOX_ID` ASC);
ERROR 1067 (42000): Invalid default value for 'LAST_REQUEST_UTC'

SET SESSION sql_mode='NO_ZERO_IN_DATE,NO_ZERO_DATE';

ALTER TABLE `BACKEND_INFO_DEVICE` ADD INDEX `ix_bid_boxid` (`BOX_ID` ASC);
show warnings;
+---------+------+-----------------------------------------------------------+
| Level   | Code | Message                                                   |
+---------+------+-----------------------------------------------------------+
| Warning | 1264 | Out of range value for column 'LAST_REQUEST_UTC' at row 1 |
+---------+------+-----------------------------------------------------------+

set session sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

ALTER TABLE `BACKEND_INFO_DEVICE` DROP INDEX `ix_bid_boxid`;
ERROR 1067 (42000): Invalid default value for 'LAST_REQUEST_UTC'

Suggested fix:
Dropping and index should always be possible.

Hello Oli,

Thank you for the report and test case.

Thanks,
Umesh

Posted by developer:
 
Changing bug category since the bug can be reproduced without using JSON:

mysql> CREATE TABLE t(x INT, ts TIMESTAMP AS (NULL));
Query OK, 0 rows affected (0,01 sec)

mysql> ALTER TABLE t ADD INDEX idx (x);
ERROR 1067 (42000): Invalid default value for 'ts'

mysql> SET SESSION sql_mode='NO_ZERO_IN_DATE,NO_ZERO_DATE';
Query OK, 0 rows affected, 2 warnings (0,00 sec)

mysql> ALTER TABLE t ADD INDEX idx (x);
Query OK, 0 rows affected, 1 warning (0,00 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> SET SESSION sql_mode=DEFAULT;
Query OK, 0 rows affected (0,00 sec)

mysql> ALTER TABLE t DROP INDEX idx;
ERROR 1067 (42000): Invalid default value for 'ts'

The problem does not seem to be reproducible when the server is started with --explicit-defaults-for-timestamp=ON, which is the default from MySQL 8.0.2.

Posted by developer:
 
Also reproducible without a generated column. For example like this with MySQL 5.7:

mysql> SET sql_mode='';
Query OK, 0 rows affected, 1 warning (0,00 sec)

mysql> CREATE TABLE t (x INT, ts TIMESTAMP DEFAULT '0000-00-00 00:00:00');
Query OK, 0 rows affected (0,01 sec)

mysql> ALTER TABLE t ADD INDEX idx (x);
Query OK, 0 rows affected (0,01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SET sql_mode=DEFAULT;
Query OK, 0 rows affected (0,00 sec)

mysql> ALTER TABLE t DROP INDEX idx;
ERROR 1067 (42000): Invalid default value for 'ts'