Bug #111893 cannot update null value when using UPDATE JOIN JSON_TABLE

• Submitted: 26 Jul 2023 10:12
• Modified: 27 Jul 2023 2:48
• Reporter: Nam Dang
• Status: Not a Bug
• Category: MySQL Server: JSON
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Other (azure mysql single server)
• CPU Architecture: Any
• Tags: json_table, UPDATE ERROR, update join fail

[26 Jul 2023 10:12] Nam Dang

Description:
I try to update null value into column "latest_engaged_product_value" of table "leads" by using value from json object. But I got error as below

Error Code: 3156. Invalid JSON value for CAST to DECIMAL from column latest_engaged_product_value at row 1

I think it's a bug when using join json_table, I also try join with other table and It work fine. 

Here is my code:

UPDATE `leads` src 
JOIN JSON_TABLE("[{\"id\":600178,\"latest_engaged_product_value\":null}]",'$[*]' COLUMNS 
(`id` int(11) PATH '$.\"id\"',
`latest_engaged_product_value` decimal(11,2) PATH '$.\"latest_engaged_product_value\"' NULL ON EMPTY)) as target 
ON src.`id` = target.`id` 

SET 
src.`latest_engaged_product_value` = null
where src.id = 600178;

 

How to repeat:
1. create table leads with column type decimal(11,2) and try update with json value: 

UPDATE `leads` src 
JOIN JSON_TABLE("[{\"id\":600178,\"latest_engaged_product_value\":null}]",'$[*]' COLUMNS 
(`id` int(11) PATH '$.\"id\"',
`latest_engaged_product_value` decimal(11,2) PATH '$.\"latest_engaged_product_value\"' NULL ON EMPTY)) as target 
ON src.`id` = target.`id` 

SET 
src.`latest_engaged_product_value` = null
where src.id = 600178;

This code is work fine (no json table) : 

UPDATE `leads` src 
JOIN  line_users as target 
ON src.`line_user_id` = target.`line_user_id` 

SET 
src.`latest_engaged_product_value` = null
where src.id = 600178

[26 Jul 2023 12:39] MySQL Verification Team

Hi Mr. Dang,

Thank you for your bug report.

However, this is not a bug.

SQL standard prohibits inserting a value of the data type that is different from the column type.

Hence, this is not a bug.

Thank you for your interest in MySQL.

[27 Jul 2023 2:48] Nam Dang

But I try to update the nullable filed to be NULL. NULL is also a valid value for this filed, right?

I can update this field to NULL by this command: 

UPDATE `leads` src 
JOIN  line_users as target ON src.`line_user_id` = target.`line_user_id` 
SET src.`latest_engaged_product_value` = null
WHERE src.id = 600178

The result had no error. 

But It's error when I try to update the SAME value to the SAME column by using JOIN JSON_TABLE.

So what wrong with the data type?