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`)

(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',

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`->>"$.test_enum",
  `test_dt` = CAST(`meta`->>"$.test_dt" AS DATETIME);

Row 2 will cause an error: Data truncated for column 'test_enum'. This seems to be because both meta->>"$.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`->>"$.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`->>"$.test_enum" 
WHERE JSON_TYPE(JSON_EXTRACT(`meta`, "$.test_enum")) != 'NULL';

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


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?

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

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.

  • 1
    @Akina Actually the datetime is nullable: ADD COLUMN test_dt DATETIME NULL. So it should allow NULL but gets null back from the JSON_EXTRACT(). This is exactly my problem: I know the expected behaviors, but am not getting those!
    – Robert K
    Commented Apr 3, 2019 at 13:54

2 Answers 2


I think it is a bug. I cannot find any such bug report in MySQL developer zone so you should report it.


The string "2019-03-28 15:43:59" looks and feels and acts like a DATETIME. Don't bother using CAST().

Isn't NULL the NULL value, but "NULL" a 4-letter string? That is, you are working too hard on the null cell?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.