MySQL Bugs: #111992: cannot update null value into nullable field when using UPDATE JOIN JSON

Bug #111992	cannot update null value into nullable field when using UPDATE JOIN JSON_TABLE
Submitted:	8 Aug 2023 3:30	Modified:	8 Aug 2023 12:45
Reporter:	Nam Dang	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: JSON	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any (azure mysql single server)
Assigned to:		CPU Architecture:	Any
Tags:	json_table, UPDATE ERROR, update join fail

Description:
I raise this case again because last time I didn't tell you that the problem field is nullable. Why I can't update null to nullable field by using UPDATE... JOINT JASON_TABLE ?

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. My code is:

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) NULLABLE 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;

Hi Mr. Dang,

Thank you for your bug report.

Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

The original bug is:

https://bugs.mysql.com/bug.php?id=111893

Do note that your latest comments do not change anything. You can update a column of one type, only with column of the same type. Sometimes, MySQL can go beyond SQL standard and convert from one type to another, like from DATE to DATETIME or similar. However, what you are trying to achieve is far beyond what SQL standard stipulates.

Thank you for your interest in MySQL.