Mysql – UPDATE with CAST(meta->>“$.field”) failing due to “null”

castjsonMySQLmysql-5.7

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.

Best Answer

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

Related Solutions

Mysql – #1093 – You can’t specify target table ‘R’ for update in FROM clause

Based on the previous question and this, the following should do what you want. As with Pieter's update it doesn't assume that the ID's are in sequential order, rather it uses the ReqTime to determine order.

UPDATE Visits R
  INNER JOIN (
    SELECT ID,
      TIME_TO_SEC(TIMEDIFF(
        (SELECT B.ReqTime
         FROM Visits B
         WHERE B.ReqTime > A.ReqTime
         ORDER BY B.ReqTime
         LIMIT 1
         )
        ,A.ReqTime)) Duration
    FROM Visits A
   ) N ON R.ID = N.ID
SET R.Duration = N.Duration
WHERE Duration IS NULL;

Postgresql – Use trigger to synchronize columns with fields in json column on insert or update

Actually, this is all you need:

NEW := jsonb_populate_record(NEW, NEW.json);

Per documentation:

jsonb_populate_record(base anyelement, from_json jsonb)
Expands the object in from_json to a row whose columns match the record type defined by base (see note below).

What's not documented: The row provided as first argument retains all values that are not overwritten (no matching key in the json value). I see no reason why this should change, but you cannot fully rely on it unless it's documented.

One thing to note - you wrote:

Setting the column to NULL if the corresponding json field does not exist is fine.

This retains all values with no matching key in the JSON value, which should be even better.

If "undocumented" is too uncertain for you, use the hstore operator #= doing exactly the same.

NEW := (NEW #= hstore(jsonb_populate_record(NEW, NEW.json)));

The hstore module should be installed in most systems anyway. Instructions:

Assign to NEW by key in a Postgres trigger

Both solution can be derived from my answer that Daniel already referenced:

How to set value of composite variable field using dynamic SQL

Function code

CREATE OR REPLACE FUNCTION json_fn()
  RETURNS TRIGGER AS
$func$
BEGIN
   NEW := jsonb_populate_record(NEW, NEW.json); -- or hstore alternative
   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

Everything else in your setup looks right, just add a PK to testy:

CREATE TABLE testy (
    id   int   PRIMARY KEY REFERENCES testy_index
  , data jsonb NOT NULL
);

Tested in pg 9.4 and it works for me as advertised. I doubt that the PLv8 function can rival performance and simplicity.

Set other columns to NULL

As per comment:

CREATE OR REPLACE FUNCTION json_fn()
  RETURNS TRIGGER AS
$func$
DECLARE
  _j jsonb := NEW.json;  -- remember the json value
BEGIN
   NEW := jsonb_populate_record(NULL::testy, _j);
   NEW.json := _j;   -- reassign
   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

Obviously, you need to make sure that the column name or your jsonb column does not appear as key name in the JSON value. And i wouldn't use json as column name, since it's a data type name and that can get confusing.