2

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`->>"$.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';

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`->>"$.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

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.

  • @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 Apr 3 at 13:54
0

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

0

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, privacy policy and cookie policy

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