UPDATE with CAST(meta->>"$.field") failing due to "null" This issue is affecting MySQL 5.7.25.

So, in a migration I'm moving legacy fields from the table into a JSON column. This part of the process works fine. But, the rollback is failing miserably! At this point, I'm considering moving the JSON field logic out of SQL into PHP.

You can reproduce the issues as follows.

DB Setup

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, '{"test_dt": "2019-03-28 15:43:59", "test_enum": "foo"}'),
(null, '{"test_dt": "2019-03-28 15:44:15", "test_enum": null}'),
(null, '{"test_dt": null, "test_enum": "bar"}');

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 prefer just 1 update query if possible...

-- copy from meta back into legacy fields
UPDATE `test_table` SET
  `test_enum` = `meta`-&gt;&gt;"$.test_enum",
  `test_dt` = CAST(`meta`-&gt;&gt;"$.test_dt" AS DATETIME);


Row 2 will cause an error: Data truncated for column 'test_enum'. This seems to be because both meta-&gt;&gt;"$.test_enum" and JSON_EXTRACT(meta, "$.test_enum") are returning "null" instead of NULL.

Now try again without the enum field update:

-- copy from meta back into legacy fields
UPDATE `test_table` SET
  `test_dt` = CAST(`meta`-&gt;&gt;"$.test_dt" AS DATETIME);


Row 3 now causes an error: Incorrect datetime value: 'null'

I know I can work around the errors by breaking the query into two like:

UPDATE test_table 
SET test_enum = `meta`-&gt;&gt;"$.test_enum" 
WHERE JSON_TYPE(JSON_EXTRACT(`meta`, "$.test_enum")) != 'NULL';

UPDATE test_table 
SET test_dt = `meta`-&gt;&gt;"$.test_dt" 
WHERE JSON_TYPE(JSON_EXTRACT(`meta`, "$.test_dt")) != 'NULL';


Confusion

SELECT-ing the data via CAST works fine, even though the ENUM-related column is invalid due to the "null" value. Why doesn't JSON_EXTRACT() return NULL properly?

SELECT
  `meta`-&gt;&gt;"$.test_enum" AS `test_enum`,
  CAST(`meta`-&gt;&gt;"$.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


Right now, I'm just wondering why MySQL acts this way with its NULL handling in both JSON and the CAST/CONVERT methods. I feel like I'm missing something obvious.
I think it is a bug. I cannot find any such bug report in MySQL developer zone so you should report it.


demo 1
demo 2