mysql – UPDATE with CAST (meta – >> "$. field") failing because of "null"

This problem affects MySQL 5.7.25.

So, in a migration, I move the fields inherited from the table into a JSON column. This part of the process works well. But the restoration fails miserably! At this point, I plan to transfer the JSON field logic from SQL to PHP.

You can reproduce the problems as follows.

Configuration of the database

CREATE TABLE `test_table` (
`id` int (11) unsigned NOT NULL AUTO_INCREMENT,
`meta` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

INSERT INTO `test_table` VALUES
(null, & # 39; {"test_dt": "2019-03-28 15:43:59", "test_enum": "foo"} & # 39;),
(null, & # 39; {"test_dt": "2019-03-28 15:44:15", "test_enum": null} & # 39;),
(null, & # 39; {"test_dt": null, "test_enum": "bar"} & # 39;);

ALTER TABLE `test_table`
ADD COLUMN `test_enum` ENUM ('foo', 'bar') NOT NULL DEFAULT 'foo',
ADD COLUMN `test_dt` DATETIME NULL;

The problem

Is there a workaround for the following query? I would just prefer 1 update request if possible …

- Meta copy in inherited fields
UPDATE `test_table` SET
`test_enum` =` meta` - >> "$. test_enum",
`test_dt` = CAST (` meta` - >> "$. test_dt" AS DATETIME);

Row 2 will cause an error: Truncated data for the column 'test_enum & # 39;. This seems to be because both meta - >> "$. test_enum" and JSON_EXTRACT (meta, "$ .test_enum") return "no" instead of NO.

Now try again without updating the enum field:

- Meta copy in inherited fields
UPDATE `test_table` SET
`test_dt` = CAST (` meta` - >> "$. test_dt" AS DATETIME);

Line 3 now causes an error: Invalid date / time value: null & # 39;

I know I can get around mistakes by dividing the query into two, like:

UPDATE test_table
SET test_enum = `meta` - >>" $. Test_enum "
WHERE JSON_TYPE (JSON_EXTRACT (`meta`," $ .test_enum "))! = & # 39; NULL & # 39 ;;

UPDATE test_table
SET test_dt = `meta` - >>" $. Test_dt "
WHERE JSON_TYPE (JSON_EXTRACT (`meta`," $ .test_dt "))! = & # 39; NULL & # 39 ;;

Confusion

The selection of data via CAST works well, even if the column relating to ENUM is invalid because of the "no" value. Why not JSON_EXTRACT () to return NULL correctly?

TO SELECT
`meta` - >>" $. test_enum "AS` test_enum`,
CAST (`meta` - >>" $. Test_dt "AS DATETIME) AS` test_dt`
FROM test_table
"id", "test_enum", "test_dt"
1, "foo", "2019-03-28 15:43:59"
2, "null", "2019-03-28 15:44:15"
3, "bar", NULL

For the moment, I'm just wondering why MySQL is doing this with its NULL handling in the JSON and CAST / CONVERT methods. I have the impression that I am missing something obvious.