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

Bug #89002	Index on JSON virtual column with default sql_mode is evaluated badly
Submitted:	21 Dec 2017 9:41	Modified:	21 Dec 2017 11:18
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:
When we add an Index on an empty table we get the following error:

ERROR 1067 (42000): Invalid default value for 'LAST_REQUEST_UTC'

Which IMHO should not be the case because the table does not contain any row.

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'

Suggested fix:
This should just not happen...

Workaround:
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 |
+---------+------+-----------------------------------------------------------+

Possibly there is something wrong in JSON and/or virtual columns:

SELECT case when isnull(json_unquote(json_extract(NULL,'$.last_request'))) then '1980-01-01 00:00:00' else str_to_date(substring_index(json_unquote(json_extract(NULL,'$.last_request')),'+',1),'%Y-%m-%dT%H:%i:%s') end;
1 row in set (0.00 sec)

SELECT case when isnull(json_unquote(json_extract('{}','$.last_request'))) then '1980-01-01 00:00:00' else str_to_date(substring_index(json_unquote(json_extract('{}','$.last_request')),'+',1),'%Y-%m-%dT%H:%i:%s') end;
1 row in set (0.00 sec)

SELECT case when isnull(json_unquote(json_extract('','$.last_request'))) then '1980-01-01 00:00:00' else str_to_date(substring_index(json_unquote(json_extract('','$.last_request')),'+',1),'%Y-%m-%dT%H:%i:%s') end;
ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_extract: "The document is empty." at position 0.

Hello Oli,

Thank you for the report and test case.

Thanks,
Umesh

Posted by developer:
 
This bug seems not to be related to JSON, as it also reproduces with this smaller test case that doesn't use JSON:

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

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

Note that the bug only reproduces when the server is started with --explicit-defaults-for-timestamp=OFF. If the server is started with --explicit-defaults-for-timestamp=ON (which is the default, starting with MySQL 8.0.2), the ALTER TABLE statement does not fail.

Be aware that the TIMESTAMP column isn't explicitly defined as nullable.