Bug #108605 How to convert json null to sql null using json_table?

• Submitted: 25 Sep 2022 2:12
• Modified: 26 Sep 2022 12:14
• Reporter: Dimpal Bindas
• Status: Not a Bug
• Category: MySQL Server: JSON
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any

[25 Sep 2022 2:12] Dimpal Bindas

Description:
Getting error for below query ---
ERROR - 
Error Code: 3156. Invalid JSON value for CAST to INTEGER from column c1 at row 1

QUERY -
CREATE TEMPORARY TABLE temp_table
    SELECT *
       FROM
        JSON_TABLE(
		'[ {"c1": 3, "c2":"Demo1"},{"c1": null, "c2" : null},  {"c2": "Demo2"}]',
           '$[*]' COLUMNS( 
           c1 int(11) PATH '$.c1'
           ,c2 varchar(11) PATH '$.c2'
           )
         ) as jt;
select * from temp_table;

When we run only select query it will work correctly, but at the time of insert getting error, If somehow we will be able to convert json null to mysql null then problem will be solved.

How to repeat:
CREATE TEMPORARY TABLE temp_table
    SELECT *
       FROM
        JSON_TABLE(
		'[ {"c1": 3, "c2":"Demo1"},{"c1": null, "c2" : null},  {"c2": "Demo2"}]',
           '$[*]' COLUMNS( 
           c1 int(11) PATH '$.c1'
           ,c2 varchar(11) PATH '$.c2'
           )
         ) as jt;
select * from temp_table;

[26 Sep 2022 12:14] MySQL Verification Team

Hi Mr. Bindas,

Thank you for your bug report.

However ........

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

For details on getting support for MySQL products see http://www.mysql.com/support/
You can also check our forums (free) at http://forums.mysql.com/

Thank you for your interest in MySQL.